Scott on Writing

Analyzing Your .Text Blog

I'll be the first to admit it - I love statistics.  I like seeing pretty graphs and numbers and sums and averages and calculations and standard deviations and variances and forecasts and trends.  Anyway, one thing that annoys me with blogs is that it's impossible to know how many people read it.  Yes, there are Web site logs, which say that I had X total Web page requests per day, and Y unique sessions per day, but these numbers, when it comes to blogs, are terribly misleading.  The #1 requested page, for instance, is the syndication feed, /rss.aspx.  One person, who leaves their aggregator running 24-7, might request this page 12, 24, or 48 times per day.  Fine, you say, take the number of requests to the syndication feed and divide by, say, 15, to get a rough estimate on those who read the blog via aggregators.  But even that number could be way off.  For example, BlogLines might hit my syndication feed 48 times a day, but there could be dozens, hundreds, or even thousands of readers who read the blog from that single requestor.

So I've resigned myself to the fact that I won't ever be able to get a truly accurate guage on my blog readership, but what I can get a handle on is the comments.  As the default .Text interface shows, my blog has had - as of the time of writing this - 91 entries and 311 comments.  This morning my curiosity got the better of me and I wondered how those comments were distributed.  That is, do blog entries that occur on Mondays generate more comments on average than those blog entires made on Friday?  Also, when is the best time (relative to my time zone) to make a blog entry?  Midnight?  In the morning?  Around lunchtime?  In the afternoon?  After dinner?  Again, the “best time” I am interested in if posting at a certain time is more likely to generate more comments. (Another metric worth looking at is trackbacks (seeing if the date/time an entry is made increases/decreases the likelihood of folks using trackbacks), but I'll save that anaysis for another day.)

Here are the statistics for my blog, as of March 27, 2004:

The black lines in the graphs are trendlines. As you can see, the later in the week a blog entry is made, the less likely it is to receive comments. However, the hour of the day the entry is posted makes little difference on the number of comments it attracts. Of course, the strongest correlation between a blog entry and the number of comments it receives is likely the quality of the blog entry, but, assuming a "comment-provoking" blog entry is as likely to be made on one day of the week versus any other, the data shows that it's best to make that blog entry earlier in the week than later.

Interested in running these statistical reports on your own .Text blog? If you have access to run queries on the SQL box your .Text blog uses, you can get these reports with just a single SQL statement. To get the comments by the day of week the blog entry was posted, use the following query:

SELECT DayOfWeek, 
       AVG(CONVERT(decimal(5,0),PostCount)) as AvgPosts, 
       SUM(PostCount) as TotalComments, 
       COUNT(*) as BlogEntries
FROM
  (SELECT DATEPART(dw, DateAdded) as DayOfWeek, 
          (SELECT COUNT(*) FROM blog_content bc2 
           WHERE bc2.posttype = 3 AND bc2.blogid = 0 AND
                 bc2.ParentID = bc.ID) as PostCount
   FROM blog_content bc
   WHERE posttype = 1 AND blogid = 0) as SOWBlog
GROUP BY DayOfWeek
WITH ROLLUP

To get the comments by hour the blog entry was posted use:

SELECT HourAdded, 
       AVG(CONVERT(decimal(5,0),PostCount)) as AvgPosts, 
       SUM(PostCount) as TotalComments, 
       COUNT(*) as BlogEntries
FROM
  (SELECT DATEPART(hh, DateAdded) as HourAdded, 
          (SELECT COUNT(*) FROM blog_content bc2 
           WHERE bc2.posttype = 3 AND bc2.blogid = 0 AND
                 bc2.ParentID = bc.ID) as PostCount
   FROM blog_content bc
   WHERE posttype = 1 AND blogid = 0) as SOWBlog
GROUP BY HourAdded
WITH ROLLUP

If enough people run (and publish) these queries, and the day of the week / hour of the day are standardized to some standard time zone, like GMT, we could determine, globally, when the best time to make a blog entry was for maximum commenting. :-) Another interesting statistic to help ascertain when folks were reading your blog would be to determine the average number of comments made per given day of the week / per given hour of the day. (Recall that the above statistics look at the comment count based on the day of the week / hour of the day the blog entry was made.)

posted on Saturday, March 27, 2004 11:30 AM