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 zettaflow.tech 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:
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.
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.