Today’s post has nothing to do with solar, except in a sort of meta sense. You see, the process of creating these posts is not really driven by some Grand Plan but rather what is going on now or at least strikes us as interesting. But of course what we think is interesting is in no way guaranteed to interest You, the reader. Every blogger, heck, every writer wants to create content that readers will want to read. Finding that match is the key – but how?
We came across an interesting article on point the other day thanks to SEOMoz. The article, titled: Two Amazing Bar Charts: % Content Consumption, % Share of Search, introduced us to a geeky way of attacking the question of what content connects with our readers. The author, Avinash Kaushik, suggested that you could gain some useful insight into how well you were doing by combining data from two sources: your website analytics provider (Google Analytics in our case) and your content management system (in our case, b2Evolution).
By summarizing your posts by categories and then totaling unique page views for those categorized posts, you could see how much content you are creating in each category and how much those posts are driving traffic. If you are on track, there should be a direct correlation – categories where you are writing more are getting the most visits. But are they? We decided to run the analysis for one year’s worth of posts, from last July through the end of June.
Seems simple enough, here’s where it gets geeky. (Note: if you just want to see the results, feel free to skip down to the cool chart at the end of all the geek-speak.)
Talk to me, B2!
Way back when we started this blog we had to decide what software to use. For better or worse, we chose b2Evolution, although most of what you see out there seems to be on WordPress. One of our biggest complaints about b2Evo is that it provides pretty meager statistics – and nothing like the data summary that we needed. Of course the database itself must have what we needed, it was just a question of figuring out where. Oh, and how would be important too!
The b2Evo database contains 52 separate tables – which one(s) have the information that we need? After a fair amount of poking around and a couple of false starts, we determined that a query combining two tables would allow us to retrieve the data that we wanted: a new table that would list the URL of each post during the time period combined with the name of the primary category under which the post was filed. (We frequently associate multiple categories with a given post, but each post has a primary category and that is what we were seeking for this analysis.) For the mySQL geeks out there, here is the query we (finally) concocted:
SELECT post_ID, post_datecreated, post_main_cat_ID, post_urltitle, cat_name FROM evo_items__item
INNER JOIN evo_categories ON (post_main_cat_ID = cat_ID)
WHERE post_status = ‘published’ AND
post_datecreated > ‘2012-06-30 23:59:59′ AND
post_datecreated < ‘2013-07-01=””>
ORDER BY post_datecreated DESC
We were then able to export the results table to a text (CSV) file for importing into Excel. Now we needed to get our analytics data.
Google Analytics (GA) is a much easier platform from which to extract lots and lots of data. For this analysis what we needed was to filter by our one year start and end dates and look at the Content – Overview report. That provided more than what we needed for this analysis, but significantly it gave us the URL of the viewed page (within the runonsun.com domain) and unique pageviews. We opted for unique pageviews to filter out those instances where someone visits the same page within a session as what we were after was the post-reader connection and unique pageviews is the best handle on that relationship.
Once again, we were able to export the data into a text file for importing into Excel.
Excelling at Posting
So after importing the two data files into separate tabs of our Excel workbook we were ready to start pairing things up. But of course, things are never that easy. For starters, the manner in which our URLs appeared from b2Evo and from GA were not at all the same and Excel’s look-up functionality could not resolve the difference. Take this example:
To match them up, we wanted to slice off everything to the right of the last ‘/’ and use that to do our matching. Turns out that is a more complicated task than you might imagine. A little probing on the Internet turned up this, somewhat counter-intuitive solution:
=RIGHT(A2, LEN(A2) – FIND(“|”,SUBSTITUTE(A2,”/”,”|”,LEN(A2) – LEN(SUBSTITUTE(A2,”/”,””)))))
(I warned you this was going to be a geekfest!)
A new column was created in the GA data table and populated with the results of that lovely formula and voilà, we had URLs in both tables that would yield a match. Now our b2Evo data could include a value for the unique pageviews from each post that was written during the previous twelve months. From there it was a simple matter of creating a pivot table based on categories, counting the number of posts in each category (listed as a percentage of the column total) and summing the unique pageviews in each category (again, displayed as a percentage of the column total). We filtered for the top ten categories (by pageviews) and that accounted for 110 separate posts and 13,659 unique pageviews.
Put all that into a chart and here is what you get.
(Please click on this and view the full size image to do this justice!)
So having gone through this exercise, what does this tell us? Well, my two largest categories for posts – Solar News and Ranting (which is where this post is categorized) – are also my two largest categories for views – so that is consistent with what we would like to see. (Ok, perhaps a little less ranting – good luck with that!)
On the other end of the scale, we are clearly writing more about both Commercial Solar and Energy Storage than our present viewership supports. That’s ok, we are looking to build readers in those vital categories so we are willing to be leading a bit there.
But look at the two major outliers: CSI 2012 and SCE. The CSI 2012 category only had four posts – the three part data analysis series of CSI data that we did last year, plus a teaser post – yet it drove more than 15% of our views! Perhaps rather than doing this once a year, which is what we have done the past two years, we should do this every six months? That’s a lot more work – those posts are very labor intensive and require rigorous editing – but in terms of what our audience is reading, it would seem to be time well spent.
The SCE category is more interesting still since it really doesn’t involve as much effort as the CSI category does. There were three posts in that category yet they accounted for more than 14% of all views! Wow – didn’t see that coming! Looks like it is time for us to spend a little more time looking at SCE and putting informative posts out there for our readers. Message received!
Perhaps the most surprising result from this analysis was that we were surprised by the results. We like to think that we have a pretty good handle on what our audience wants to see – and for the most part the above graph suggests that we do – but still there were surprises. Like any other business metric, knowing what your customers want from your blog – or the products that you are selling – is key to being successful.
If you are a solar company writing a blog and you are serious about better connecting with your audience – colleagues and potential clients alike – you might want to try performing a similar analysis and see where it leads you.
Original Article on The Founder’s Blog