Purge old mailing data so searching open and click through rates works better

Powerbase collects a massive amount of statistics for every message sent via CiviMail - every contact that was sent each message, every contact in which an open or a link click through was detected, every contact whose email bounced, etc.

If you send a newsletter to about 10,000 contacts four times a year, this data does not amount to much and the rest of this article does not apply to you!

On the other hand, suppose you send a weekly email to 30,000 or more people, and you have been doing it for several years. With a schedule like this, your data will build up and you may experience very slow searches when you are trying to find people have recently opened an email or clicked through a link.

Furthermore, given the frequency with which people change email addresses, only data from the last 1 - 2 years is typically helpful with these searches. If this experience sounds familiar, you may want to ask support to enable and configure the Archive Mailing extension for you.

Note: For database with over 10 million recipient records, PTP will automatically purge email records older than three years.

What is different with this extension?

Two new custom fields in your database for each contact record.

These fields allow you to use the Advanced Search to find people who are engaging with your email messages.

Unlike the Mailing search fields (see below) these new custom fields only search in the contacts tables, making them fast and efficient. Furthermore, the last opened and last clicked fields retain historic data even after old mailing statistics have been purged.

More effective searches using the core Mailing fields

You still have access to the traditional advanced search mailing fields:


  • The search will only apply to mailings sent in the last three years because the statistics for older mailings will have been purged.
  • By purging old Mailing stats, these searches will return more quickly

What does "Archiving" a mailing mean?

With or without this extension, the list of "Sent or Scheduled" mailings has an archive option. When you click this option, the mailing is moved from the "Sent or Scheduled" list of mailings to the "Achived Mailings" list. However, no statistical data is purged.

When the Archive Mailing extension is enabled, we will automatically find mailings that have not been archived and are more than 3 years old and both purge the old statistics and mark the mailing as archived.

As a result:

  1. You will still be able to find all your old mailings, see the content of the mail, the groups it was sent to and even a summary of the opens, bounces, etc.
  2. The reports that list the individual contacts who were sent a mailing, opened it, clicked on a link, etc will be empty.
  3. If you archive a mailing before it is three years old, you will protect it's statistical data from being purged.

The Jobs

The Archive Mailing extension provides two optional jobs that must be manually enabled.

Record Email Engagement Job

This job typically runs on a daily basis. It searches for recent email opens and click throughs and records them in two new custom fields.

Archive Mailing Job

Optionally, the Archive Mailing job can also be enabled. When enabling the Archive Mailing job, you must indicate how old a Mailing must be in order to be automatically archived (e.g. 1000 days old). It is typically run every day.

When a mailing is archived by this job, it both is marked as "Archived" in the Mailing dashboard (so it does not show up unless you specifically indicate that you want to see archived jobs) and more importantly, the statistics indicating who received the email, opened it, etc are stored in aggregate, and then purged.

Email and Postal Mailings