If you want to track your WordPress blog performance – collecting and visualizing data might be a great help.
We have already described the benefits of Visual Dashboards for performance tracking in this article Why Your Business Needs Dashboards.
Unfortunately, WordPress does not offer native tools to create visual dashboards, and those few plugins supporting visualizations, normally are able to visualize only the focus data that they process.
In this article, I will describe how you can build a dashboard based on the data stored in the database of your WordPress site. Some basic knowledge of SQL might be required.
How to visualize
To begin with, you need to login into the overfetch platform with your Gmail or GitHub account (no charges).
After that, you should add a new working space by clicking the ‘New space’ button.
Then press ‘New data source’ to connect to the database of your site. Choose Type as MySQL and fill in the required data.
After the database is connected, you may view it by selecting it from the menu on the left side.
Now you are ready to write your SQL queries. Click ‘New data’ and go ahead.
When you are satisfied with the result of your query, click ‘New visualization’, in the field Data you need to select created data, chose a type of visualization out of Bar, Histogram, Line, Pie, Scale-up Number, or Scatter, also to fill remaining fields.
This way you may prepare desired visualizations and arrange them in a dashboard. Click ‘New dashboard’.
Press the ‘Add’ button, choose the proper item, and adjust the position and size.
Your dashboard is ready. Now you may get and share the link with others (click the ‘Share’ button).
WordPress blog dashboard
Using the same process I built a dashboard for this blog.
It shows the total number of posts (as well by authors) and media files, most viewed and commented posts, post views, number of registered users, and number of comments. The dashboard is real-time.
Just sharing some SQL queries that might be helpful.
To build the ‘Total Posts’ line chart I used the following query:
WITH t AS ( SELECT DATE_FORMAT(post_date, '%Y') * 12 + DATE_FORMAT(post_date, '%m') AS post_year_month, COUNT(*) AS posts FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' GROUP BY post_year_month ) SELECT post_year_month, posts, ( SELECT SUM(t2.posts) FROM t as t2 WHERE t2.post_year_month <= t.post_year_month ) as posts2 FROM t ORDER BY post_year_month DESC LIMIT 12
For the ‘Most viewed in 90 days’ visualization (having WordPress Popular Posts plugin installed) the query used is:
SELECT post_title, COUNT(pageviews) AS views FROM wp_posts INNER JOIN wp_popularpostssummary ON wp_popularpostssummary.postid = wp_posts.ID WHERE post_type = 'post'AND view_date >= CURDATE() - INTERVAL 90 DAY GROUP BY post_title ORDER BY views DESC LIMIT 5
If you are willing to fetch the data from your WordPress site to the spreadsheets you may check the following article: How to Build a Real-Time Dashboard with Google Sheets or Excel Using WordPress Data.
If any help is required – contact us via Discord.