On May 24 of this year, CAPTCHA was added to the commenting area as a means of protecting against the onslaught of comment spam and helping the good reCAPTCHA cause. At the time this blog was receiving over 1,000 comment spams a day, no doubt increased because we weren’t and still aren’t using rel=nofollow on links left in comments.

After reCAPTCHA immediately was cut down to less than 10 comment spams per day. From 1,000+ to less than 10 per day on average? From a blog publisher standpoint that’s a win. It’s a timesaver, but what about you, dear readers?

Shortly after activating the reCAPTCHA on May 24, and on a reader’s suggestion (see the comments on that post), I made it so that the CAPTCHA would disappear after commenters left at least five approved comments. It was never my goal for the CAPTCHA to be a nuisance for commenters, which is one major gripe with blogs that use CAPTCHA.

The other claim is that those blogs who use CAPTCHA will have fewer comments. I’m a strong believer in numbers not lying, so I decided to see how many approved comments have been left in the 174 days since the CAPTCHA have been activated.

Before getting to the numbers it’s important to note that the amount of traffic this blog receives isn’t the same. While the number of RSS subscriptions has increased, web traffic to this blog has gone down a little bit over the time range since 2006 which would further impact the actual results.

Wordpress MySQL database queries to count number of comments since date
Below in blockquote you’ll find queries you can use on your blog. If you use a table prefix, be sure to change the wp_comments to TABLEPREFIX_comments instead. For example, let’s say you used myblog as a table prefix then replace wp_comments with myblog_comments in the SQL statements below.

SELECT count(comment_ID) from wp_comments WHERE comment_approved=’1′ and comment_date > ‘2007-05-24′;

Result: 1,739

Now to check the comments in the days before the CAPTCHA was implemented, first the query to use in shell (my favorite) or your favorite MySQL editor (like PHPmyAdmin):

SELECT count(comment_ID) from wp_comments WHERE comment_approved=’1′ and comment_date > ‘2006-12-01′ and comment_date < ‘2007-05-24′;

Result: 1,932. That’s a 10% decrease in comment activity. Now before saying aha! let’s examine a query of the same 174 day period from the previous year.

SELECT count(comment_ID) from wp_comments WHERE comment_approved=’1′ and comment_date > ‘2006-05-24′ and comment_date < ‘2006-11-15′;

Result: 2,198. Ouch, now say aha! That’s a decrease of 21% (rounded) in comment activity over the same period of time a year earlier.

Just for fun, let’s go back one year.

SELECT count(comment_ID) from wp_comments WHERE comment_approved=’1′ and comment_date > ‘2005-05-24′ and comment_date < ‘2005-11-15′;

Result: 1,227. This query result doesn’t mean much because comment activity actually increased significantly in 2006 over previous years at this blog. In 2004 over the same time period the result was 352 comments and this blog was created on July 4, 2003 so the same period can’t be reviewed in 2003.

Bottom line findings: although I replied to reader A. Brinck this morning that comment activity hadn’t decreased and even Twittered about it, I was basing this off a flawed initial query. Further focus revealed I wasn’t using the correct number of days (163 instead of 174) for my past query, which is my bad. Detailed review reveals that indeed, comment activity has decreased by 10% at this blog since adding CAPTCHA. Again, it’s important to note that there has been a decrease in traffic, so the actual amount of impact on blog commenting is less than 10% doing real numbers.

Does CAPTCHA reduce the number of unique commenters?
While the above queries answer the title question of CAPTCHA impact on the overall number of comments, it doesn’t speak about the unique number of commenters. We need to refine our MySQL query to get these results for the research.

With the current setup it is new commenters being inconvenienced the most. I decided to run a query to check the percentage of commenters since adding the CAPTCHA versus the 174 days before. First let’s look at the most recent period and let’s remove trackbacks from the results (comment_type != ‘pingback’):

SELECT count(comment_ID), comment_author from wp_comments WHERE comment_approved=’1′ and comment_type != ‘pingback’ and comment_date > ‘2007-05-24′ group by comment_author;

Result: 431 unique commenters over the last 174 days. Compared to the preceding 174 days?

SELECT count(comment_ID), comment_author from wp_comments WHERE comment_approved=’1′ and comment_type != ‘pingback’ and comment_date > ‘2006-12-01′ and comment_date < ‘2007-05-24′ group by comment_author;

Result: 528 unique commenters, a reducton of 18.4% unique commenters. Ouch. Now let’s check the same 174 days time frame in 2006.

SELECT count(comment_ID), comment_author from wp_comments WHERE comment_approved=’1′ and comment_type != ‘pingback’ and comment_date > ‘2006-05-24′ and comment_date < ‘2006-11-15′ group by comment_author;

Result: 599 unique commenters, 28.1% less unique commenters since adding CAPTCHA over the same time period in 2006.

Get rid of the CAPTCHA or not?
This is another chance for readers to weigh in on the use of CAPTCHA in the comments. Would you like it to go away completely or should the number be reduced to one or two approved comments not to show the CAPTCHA instead of five?

I’m leaning toward reducing the number of comments left as opposed to eliminating CAPTCHA altogether this time but it’s not off the table. Frankly losing 10% of the comments over the same period of time versus having 990+ comment spams a day could be a worthwhile tradeoff. Less time sifting through BS to be able to work on posts, promotion and conversing with those who are leaving comments.

What do you think?

Related Posts

• reCAPTCHA helps improve OCR for internet library
• How to identify and remove Wordpress comment spam MySQL queries
• Dude, where’s my comments?
• [site news] CAPTCHA works
• No Google juice for nofollow attribute, will this negatively impact legitimate comment activity?
• Trackbacks now inline, mixed with comments