Categories
Geek Net

Blog stats for this year

I’ve seen Google and Yahoo put out reports on the top search terms of the year, and we’re only a week into December. (Happy Hannukah by the way.)

Seems a bit odd to do that before the actual end of the year, but what the hell, I’ll jump on that bandwagon. Here are some stats for my blog, for the year up to midnight Tuesday.

Total posts for the year: 279

Total comments for the year: 2014

Average comments per post: 7.2

Most frequent post categories:

What do I end up writing most about? Posts in multiple categories counted twice.

Most popular categories by comments:

What do people like commenting on most? (average comments per post per category)

Least popular? Dreams 0.5. Not really surprised at that; more than any other category, my occasional posts about my dreams are for my benefit, not anybody else’s!

Top popular posts by comment:

And what have been the posts that people have commented on the most?

Top ten frequent commenters:

Who’s left the most comments?

Quickest comments (minutes between post and comment):

Thanks to the web, and particularly with RSS feeds, people can see what you’ve written within seconds of it being posted. Who’s been quickest off the mark to comment? Figures in minutes and seconds between the blog post and the first comment.

Some comments come in weeks after the post, and probably nobody notices them except me. Comments are normally closed 90 days after the post date.

Finding these stats

Most of the above extracts came out of the database for the blogging software, WordPress. For those who want to try this for themselves (and for my own future reference if I try this again next year), here’s the SQL I used.

(Pah — Skip this geeky stuff and go to the comments)

Total posts for the year:

SELECT count(*)
FROM wp_posts
where post_status = 'publish'
and post_date >= '2006-12-05' and post_date < '2007-12-05'

Total comments for the year:

SELECT count(*)
FROM wp_comments
where comment_approved = '1'
and comment_date >= '2006-12-05' and comment_date < '2007-12-05'

Frequent post categories:

SELECT c.cat_name, count(*) as cat_count
FROM wp_posts p, wp_post2cat pc, wp_categories c
WHERE p.post_status = 'publish'
and p.post_date >= '2006-12-05' and p.post_date < '2007-12-05' and p.ID = pc.post_id and pc.category_id = c.cat_id GROUP BY c.cat_name ORDER BY cat_count desc

Popular categories by comment:

This will only give totals. I matched this to the above then worked out the average comments per post in each category.

SELECT c.cat_name, count(*) as comment_count
FROM wp_posts p, wp_post2cat pc, wp_categories c, wp_comments co
WHERE p.post_status = 'publish'
and p.post_date >= '2006-12-05' and p.post_date < '2007-12-05' and p.ID = pc.post_id and pc.category_id = c.cat_id and p.ID = co.comment_post_ID GROUP BY c.cat_name ORDER BY comment_count desc

Popular posts by comment:

SELECT p.post_date, p.post_title, count(*) as comment_count
FROM wp_posts p, wp_comments c
WHERE p.post_status = 'publish' and c.comment_approved = '1'
and p.post_date >= '2006-12-05' and p.post_date < '2007-12-05' and p.ID = c.comment_post_id GROUP BY p.post_date, p.post_title ORDER BY comment_count desc

Frequent commenters:

SELECT comment_author, comment_author_email, count(*) as comment_count FROM `wp_comments`
where comment_approved = '1'
and comment_date >= '2006-12-05' and comment_date < '2007-12-05' group by comment_author, comment_author_email order by comment_count desc

Quickest commenters:

SELECT p.post_date, p.post_title, c.comment_author, timediff(c.comment_date, p.post_date) as TimeComment
FROM wp_posts p, wp_comments c
WHERE p.post_status = 'publish' and c.comment_approved = '1'
and p.post_date >= '2006-12-05' and p.post_date < '2007-12-05' and p.ID = c.comment_post_id ORDER BY TimeComment

By Daniel Bowen

Transport blogger / campaigner and spokesperson for the Public Transport Users Association / professional geek.
Bunurong land, Melbourne, Australia.
Opinions on this blog are all mine.

3 replies on “Blog stats for this year”

Daniel
Regarding your astute observation that commentaries about “the year that was” have already started, I recall TV programs in late December 2004 showing highlights of that year, omitting the most amazing natural disaster that happened on our door-step (the tsunami).
Rog.
PS I’m honoured to be your most faithful correspondent, although my wife thinks people who respond to blogs are only marginally less weird than the people who write them.

Thank you for this lovely summary of stats! It is awesome, and just what I’d expect from a “geek” ;) LOL Seriously though, I think it hilarious I’m a frequent responder to your posts, considering we’ve never met! Ah well, amazing thing, the Internet, isn’t it? Making the world a smaller place, in some ways.

LOL @ the last line of Roger’s comment above mine. What on earth would she think of me?

Comments are closed.