Visualize WordPress Blog Data

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. 

Queries 

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.