{"id":291,"date":"2020-08-03T14:12:00","date_gmt":"2020-08-03T14:12:00","guid":{"rendered":"https:\/\/zettaflow.tech\/blog\/?p=291"},"modified":"2025-07-13T19:51:55","modified_gmt":"2025-07-13T19:51:55","slug":"how-to-build-a-real-time-dashboard-with-google-sheets-or-excel-using-wordpress-data","status":"publish","type":"post","link":"https:\/\/tapegraph.io\/blog\/how-to-build-a-real-time-dashboard-with-google-sheets-or-excel-using-wordpress-data\/","title":{"rendered":"How to Build a Real-Time Dashboard with Google Sheets or Excel Using WordPress Data."},"content":{"rendered":"
\n
\"Zettaflow<\/figure>\n<\/div>\n\n\n\n\n\n\n

Good news! <\/p>\n\n\n\n

This post is not about the next WordPress plugin. <\/p>\n\n\n\n

This post is not a guide to getting started with the WordPress API.<\/p>\n\n\n\n

We do not mess with that.<\/p>\n\n\n\n

We will connect directly to the WordPress database in order to get the data that we need using SQL queries. In case you are just starting with SQL, you may use the statements provided in this post, as they were written for the standard MySQL database. <\/p>\n\n\n\n

Then we will link the data flows to the spreadsheets: Google Sheets and Excel (you may stick with what you like best). <\/p>\n\n\n\n

Spreadsheets have a wonderful user interface and a powerful toolset. So, we will utilize them to visualize the data and to create a real-time dashboard that can be easily shared with anyone. <\/p>\n\n\n\n

See how simple is that! To do the trick I am using the tapegraph.io<\/a><\/strong> free version.<\/p>\n\n\n\n

In order to connect to the database<\/strong>, you need to have its credentials.<\/p>\n\n\n\n

The next step is to create the SQL queries <\/strong>we need. <\/p>\n\n\n

\n
\"Zettaflow<\/figure>\n<\/div>\n\n\n

To retrieve the latest posts and their freshness, we can use the following query:<\/p>\n\n\n\n

<\/path><\/svg><\/div>
SELECT post_title, post_date\nFROM wp_posts\nWHERE post_type = 'post' AND post_status = 'publish'\nORDER BY post_date DESC\nLIMIT 5\n<\/pre><\/div>\n\n\n
\n
\"Zettaflow<\/figure>\n<\/div>\n\n\n
To get the total number of posts:<\/p>\n\n\n\n
<\/path><\/svg><\/div>
SELECT post_status, COUNT(*) AS posts\nFROM wp_posts\nWHERE post_type = 'post' \n    AND (post_status = 'publish' OR  post_status = 'draft')\nGROUP BY post_status\nORDER BY post_status DESC\n<\/pre><\/div>\n\n\n\n
To check the latest 3 months posting:<\/p>\n\n\n\n
<\/path><\/svg><\/div>
SELECT DATE_FORMAT(post_date, '%Y-%m') AS post_year_month, \n        COUNT(*) AS posts\nFROM wp_posts\nWHERE post_status = 'publish' AND post_type = 'post'\nGROUP BY post_year_month\nORDER BY post_year_month DESC\nLIMIT 3\n<\/pre><\/div>\n\n\n\n
And if we want to see the authors contribution, we can use this SQL query: <\/p>\n\n\n\n
<\/path><\/svg><\/div>
SELECT display_name, COUNT(*) AS posts\nFROM wp_posts\nINNER JOIN wp_users \n    ON wp_users.ID = wp_posts.post_author\nWHERE \n    post_type = 'post' AND post_status = 'publish'\nGROUP BY post_author\nORDER BY display_name<\/pre><\/div>\n\n\n\n
To check any kind of attachments: <\/p>\n\n\n\n
<\/path><\/svg><\/div>
SELECT post_mime_type, COUNT(*) AS files\nFROM wp_posts\nWHERE post_type = 'attachment'\nGROUP BY post_mime_type\nORDER BY post_mime_type\n<\/pre><\/div>\n\n\n\n
Such a result we are getting after running the query:<\/p>\n\n\n
\n
\"Zettaflow<\/figure>\n<\/div>\n\n\n
Connect to Google Sheets<\/h3>\n\n\n\n
To be able to load the data into spreadsheets we need to create the Data URLs <\/strong>first. And this is what we are getting:<\/p>\n\n\n
\n
\"Zettaflow<\/figure>\n<\/div>\n\n\n
We need to manually format dates in the Google Sheets. See how it looks before and after formatting.<\/p>\n\n\n
\n
\"Zettaflow<\/figure>\n<\/div>\n\n\n
After pasting all the formulas you may surely create charts <\/strong>and arrange everything in an appropriate way. <\/p>\n\n\n\n
This is how we are getting a real-time dashboard on Google Sheets<\/strong>. Now we may share it with anyone. <\/p>\n\n\n\n
See what we have got for Tapegraph blog, just click desktop<\/a> <\/strong>or mobile<\/strong><\/a>.\u00a0<\/p>\n\n\n\n
Connect to Excel<\/h3>\n\n\n\n
Should you prefer Excel<\/strong>, then get the CSV URLs <\/strong>from the Data URLs<\/strong>. Such as this one<\/a>. <\/p>\n\n\n\n
To load it into Excel <\/strong>you need to open a new file, on the ribbon select \u2018Data\u2019, click \u2018From Web\u2019 or \u2018Get Data\u2019 -> \u2018From Web\u2019, paste the CSV URL and click \u2018OK\u2019. In the opened window adjust the delimiter if required and click \u2018Load\u2019. <\/p>\n\n\n
\n
\"Zettaflow<\/figure>\n<\/div>\n\n
\n
\"Zettaflow<\/figure>\n<\/div>\n\n\n
Now we load all data, create charts<\/strong>, and arrange them. <\/p>\n\n\n\n
Our real-time dashboard on Excel <\/strong>based on data from the WordPress blog is ready. Have a look at it<\/a><\/strong>. <\/p>\n\n\n\n
If you are willing to include in your dashboards comments information <\/strong>you may use the following statements: <\/p>\n\n\n\n
Latest comments with the author, date, and post link<\/p>\n\n\n\n
<\/path><\/svg><\/div>
SELECT comment_content, comment_author, comment_date, guid AS post_url\nFROM wp_comments\nINNER JOIN wp_posts ON wp_comments.comment_post_ID = wp_posts.ID\n WHERE comment_approved = 1\nORDER BY comment_date DESC\nLIMIT 10<\/pre><\/div>\n\n\n\n
Number of comments per month<\/p>\n\n\n\n
<\/path><\/svg><\/div>
SELECT DATE_FORMAT(comment_date, '%Y-%m') AS comment_year_month, \n        COUNT(*) AS comments\nFROM wp_comments\nWHERE comment_approved = 1\nGROUP BY comment_year_month\nORDER BY comment_year_month DESC\nLIMIT 10\n<\/pre><\/div>\n\n\n\n
Total number of comments (categorized as posted, spam, or trash)<\/p>\n\n\n\n
<\/path><\/svg><\/div>
SELECT comment_approved AS comment_type, COUNT(*) AS comments\nFROM wp_comments\nGROUP BY comment_approved\n<\/pre><\/div>\n\n\n\n
Users Email List<\/p>\n\n\n\n
<\/path><\/svg><\/div>
SELECT ID, display_name, user_email\nFROM wp_users\nLIMIT 200\n<\/pre><\/div>\n\n\n\n
You can also enrich your dashboards by generating more data in your WordPress database. For this, you will need to install a plugin<\/a> recommended by WordPress. <\/p>\n\n\n\n
Another possible option (which we prefer more) is to pull data from Google Analytics right into your dashboard. We will describe it in the following posts. <\/p>\n\n\n\n
Stay tuned! <\/p>\n","protected":false},"excerpt":{"rendered":"
Real-Time Dashboard with WordPress Data<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[29,14,28,16],"tags":[40,9,5,6,42,39,41,13,7,3,8],"class_list":["post-291","post","type-post","status-publish","format-standard","hentry","category-dashboard-examples","category-visualization","category-how-to-guides","category-tech","tag-dashboards","tag-data","tag-excel","tag-googlesheets","tag-how-to","tag-intermediate","tag-real-time-data","tag-skills","tag-sql","tag-visualization","tag-wp"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/posts\/291","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/comments?post=291"}],"version-history":[{"count":53,"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/posts\/291\/revisions"}],"predecessor-version":[{"id":1019,"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/posts\/291\/revisions\/1019"}],"wp:attachment":[{"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/media?parent=291"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/categories?post=291"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tapegraph.io\/blog\/wp-json\/wp\/v2\/tags?post=291"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}