Weekly Top Posts automated email digest for WordPress

Ever wanted to set up a weekly or monthly mailing list for your blog?

FeedBurner is good for a daily email dispatch which is attainable by just “burning” your feed and activating mail service for it.

But how about less frequent mailings?
And how about selecting only the best articles to include in those rare mails?

It’s easy. Better yet, it can be automated with standard components for your WordPress installation.

To set up a Weekly (or Monthly) Top Posts mailing list just follow these 5 simple steps:

  1. Install a plugin to count post views on the by-post basis.
    The plugin must support non-resettable (cumulative) views counters. We’ll assume Daily Top 10 Posts plugin for this writing.
    Refer to plugin’s docs on which code to add to single.php to make it work (count hits, that is).
  2. Create special category, say “Weekly Top Posts” and note its numeric ID.
  3. Add the following job to your crontab (you’ll need a cron privilege on your host machine for that). This is the heart of the whole setup.

    /usr/bin/mysql -e "
    INSERT INTO wp_post2cat (post_id, category_id)
    SELECT wp_posts.ID, Top_Posts_category_ID
    FROM wp_posts, wp_dailytoptenall
    WHERE wp_posts.ID=wp_dailytoptenall.postnum
    AND post_date BETWEEN DATE_SUB(NOW(),INTERVAL 1 WEEK) AND NOW()
    ORDER BY postcount DESC
    LIMIT 3;
    "
    -pYour_WP_MySQL_Password
    -u Your_WP_MySQL_Username
    -h mysql_host__often_localhost wp_DB_Name

    Notes:

    • Top_Posts_category_ID is the numeric category ID noted above.
    • INTERVAL 1 WEEK may be changed as per your design, e.g. to INTERVAL 1 MONTH or INTERVAL 100 DAY
    • LIMIT 3 indicates the number of top posts returned (and inserted into the periodic feed delivery), change according to your posts volume.
    • Adjust tables and columns names as needed (if your WP setup differs).
    • The code (mysql invocation) must fit on a single line, however long it’ll be – no newlines in crontab recommended.
    • You might also wish to fiddle about the TimeZone adjustment as MySQL server’s default is likely to differ from your WP setup.
  4. Sign up for a FeedBurner account if you haven’t done so yet.
    Then burn your new category’s feed, which should have a URL like this:
    http://your.blog/category/weekly-top-posts/feed/
    and activate email subscription for it (you’ll find instructions on that and also on how to use the subscription form in your FeedBurner home page).
  5. (optional) You might choose to hide the Top Posts category from the categories menu by adding proper parameters to wp_list_cats() function call in your theme files.
1 Star2 Stars3 Stars4 Stars5 Stars


Leave a Reply

Your email address will not be published.