Lately, every time I manually flagged a comment as spam, I started to have the impression that they all were posted on the same post, namely an old announcement for my plugin "Absolute Comments".
An SQL query later, my impression was confirmed: this blog post *is* a spam magnet:
I suspect that some particular keywords in page titles act like pheromones to spammers. These keywords probably include "comments", "guestbook", "feedback" and anything that will make them think there's a form they can spam.
The SQL query I used to list blog posts sorted by the number of spam they get is:
- SELECT COUNT(wp_posts.ID) as spam_count, wp_posts.ID, wp_posts.post_title
- FROM wp_posts, wp_comments
- WHERE wp_comments.comment_approved = 'spam' AND wp_comments.comment_post_ID=wp_posts.ID
- GROUP BY wp_posts.ID
- ORDER BY spam_count DESC
If you want to play with it, I made a quick standalone script that will run the query and output a pretty interactive pie chart. Download spam-magnet.txt, rename as .php and put it in your blog root (where wp-load.php is). Live demo: spam-magnets.php.
Edit: per request, I've made this a plugin too.
If you run it and notice a pattern on your blogs too, share your analysis! I've always enjoyed useless fun stats :)
Want to share or tweet this post? Please use this short URL: http://ozh.in/tf