Analytics & Testing, Content Marketing, WordPress

MySQL Query for Pulling Categories in WordPress

Recently, it appeared that the posts that I wrote about my home life appeared to get more page views than some of my other topics. It would support that the personal aspect of blogging is what attracts more readers so I wanted to find out. Any of my posts that touch on my personal life, I add a specific query to. The rest of the categories are applied based on content. I did this on purpose so that I could eventually report on it. That time has come!

WordPress Query

It’s not as easy as you might think to figure out, though. The entire process took me a few hours from data to report! The first challenge was pulling the data out of my blog database. In WordPress, it requires a nice join query between three tables, posts, posts2cats, and categories. If you’d like to do this, here’s the query:

SELECT `post_date`, `cat_name` FROM `wp_posts` LEFT JOIN `wp_post2cat` ON `wp_posts`.ID=`wp_post2cat`.post_id LEFT JOIN `wp_categories` ON `wp_categories`.cat_ID=`wp_post2cat`.category_id

Note that you’ll actually get more than one record back per post if you’ve selected multiple categories on a post. That’s okay, I actually deal with that in my analysis.

Google Analytics

Google makes it pretty easy to pull the data by date that you need and export it as a CSV file. I simply pulled the identical date range and number of page views. I then merged both of the sources, blog posts and categories and the associated page views. Fun stuff!

Analysis

The next step is the fun one! There’s a series of queries and steps you have to go through (I don’t want to go into that much detail here) but the basic output is that I want to calculate the number of pageviews divided by the number of posts in each category. I then calculated the average views per post across the entire blog and compare the results.

What you see below is an analysis of the Index of Page Views by Category. Click on the image if you’d like to see it full size. An Index of 100 is the average. An index of 200 means that the category had twice the hits of the average post. An Index of 50 is half the average.

Blog Category Index

Conclusions

Not quite what I expected, but I think some of it makes sense. On the very low end of the scale (right), we see some saturated topics, don’t we? Politics, Technology, Business, Blogging, etc. We also see some very, very niche topics like Google Maps. Since it’s not a primary topic of my blog, It’s doubtful I’m attracting a lot of attention for that.

Homefront was virtually dead-center! I thought it would index higher but the fact that it doesn’t under-index tells me that it’s in no way hurting my blog. Is it helping? Perhaps in retention, but not straight page views.

What really roars to the top are areas of expertise that I have. Analytics… wow! I think this is a topic area that is screaming for help. There’s not too many web analytics blogs out there! People want to know how to use analytics, how to implement it, and then how to report and make changes based on it (like this post!).

The other interesting item is my “Daily Reads”. I thought for sure that those would be middle of the road, but they actually rank very high. People are interested in what I’m reading and recommending to them! That feels pretty good. Every day I read through hundreds of feeds and sites and I try to pull back unique stories that people would appreciate. Many times, these are links to other blogs that I find interesting and want to pass on. It appears the camaraderie involved in this pays off!

There you have it! A years worth of readership data! I would really like to make it much easier to do this analysis next time. I really want to work on automating the categories into my analytics reports so that I can keep an eye on them closer.

3 Comments

  1. 1

    What I find interesting about the Daily Reads number is I rarely, if ever, read link list posts. But I almost always find myself scanning yours.

    I thought it was because we have similar interests, and that may be so. But it looks like it hits with a lot of other readers as well.

    I’ve found some great stuff from your Daily Reads. Maybe you just have a knack for filtering through the junk for us 🙂

    • 2

      That’s definitely what I’m after on that, Tony. Very rarely do I select a link that’s outside the topic area of my blog… and I really do read those articles and like them before posting them!

      I find them from a [ridiculously] huge number of places, including Search Engine alerts, blogs, social bookmarking sites, etc.

      Thanks, Tony!

Leave a Reply