How to Build a Real-Time Dashboard with Google Sheets or Excel Using WordPress Data.

Good news! 

This post is not about the next WordPress plugin. 

This post is not a guide to getting started with the WordPress API.

We do not mess with that.

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. 

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

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. 

See how simple is that! To do the trick I am using the overfetch.com free version.

In order to connect to the database, you need to have its credentials.

The next step is to create the SQL queries we need. 

To retrieve the latest posts and their freshness, we can use the following query:

SELECT post_title, post_date
FROM wp_posts
WHERE post_type = 'post' AND post_status = 'publish'
ORDER BY post_date DESC
LIMIT 5

To get the total number of posts:

SELECT post_status, COUNT(*) AS posts
FROM wp_posts
WHERE post_type = 'post' 
    AND (post_status = 'publish' OR  post_status = 'draft')
GROUP BY post_status
ORDER BY post_status DESC

To check the latest 3 months posting:

SELECT DATE_FORMAT(post_date, '%Y-%m') AS post_year_month, 
        COUNT(*) AS posts
FROM wp_posts
WHERE post_status = 'publish' AND post_type = 'post'
GROUP BY post_year_month
ORDER BY post_year_month DESC
LIMIT 3

And if we want to see the authors contribution, we can use this SQL query: 

SELECT display_name, COUNT(*) AS posts
FROM wp_posts
INNER JOIN wp_users 
    ON wp_users.ID = wp_posts.post_author
WHERE 
    post_type = 'post' AND post_status = 'publish'
GROUP BY post_author
ORDER BY display_name

To check any kind of attachments: 

SELECT post_mime_type, COUNT(*) AS files
FROM wp_posts
WHERE post_type = 'attachment'
GROUP BY post_mime_type
ORDER BY post_mime_type

Such a result we are getting after running the query:

Connect to Google Sheets

To be able to load the data into spreadsheets we need to create the Data URLs first. And this is what we are getting:

We need to manually format dates in the Google Sheets. See how it looks before and after formatting.

After pasting all the formulas you may surely create charts and arrange everything in an appropriate way. 

This is how we are getting a real-time dashboard on Google Sheets. Now we may share it with anyone.

See what we have got for Over blog, just click desktop or mobile. 

Connect to Excel

Should you prefer Excel, then get the CSV URLs from the Data URLs. Such as this one. 

To load it into Excel you need to open a new file, on the ribbon select ‘Data’, click ‘From Web’ or ‘Get Data’ -> ‘From Web’, paste the CSV URL and click ‘OK’. In the opened window adjust the delimiter if required and click ‘Load’. 

Now we load all data, create charts, and arrange them.

Our real-time dashboard on Excel based on data from the WordPress blog is ready. Have a look at it. 

If you are willing to include in your dashboards comments information you may use the following statements: 

Latest comments with the author, date, and post link

SELECT comment_content, comment_author, comment_date, guid AS post_url
FROM wp_comments
INNER JOIN wp_posts ON wp_comments.comment_post_ID = wp_posts.ID
 WHERE comment_approved = 1
ORDER BY comment_date DESC
LIMIT 10

Number of comments per month

SELECT DATE_FORMAT(comment_date, '%Y-%m') AS comment_year_month, 
        COUNT(*) AS comments
FROM wp_comments
WHERE comment_approved = 1
GROUP BY comment_year_month
ORDER BY comment_year_month DESC
LIMIT 10

Total number of comments (categorized as posted, spam, or trash)

SELECT comment_approved AS comment_type, COUNT(*) AS comments
FROM wp_comments
GROUP BY comment_approved

Users Email List

SELECT ID, display_name, user_email
FROM wp_users
LIMIT 200

You can also enrich your dashboards by generating more data in your WordPress database. For this, you will need to install a plugin recommended by WordPress. 

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. 

Stay tuned!