One of the most common types of support requests we get is about how to make your mailings build faster.
One common root problem of slow mailing builds is often that the underlying reports themselves are slow. Using caching when appropriate can often help, but the biggest wins come from improving the reports themselves.
Maybe you’re using some reports like these in your mailing targeting:
- When did the user last receive a mailing?
- When was the user’s last open?
- When was the user’s last click?
- When was the user’s last action?
- When was the user’s last donation?
- How many actions has this user taken in the last 30 days?
These are common reports, and they’re great questions to ask about your users! They’re great ways of measuring engagement and figuring out who your most committed supporters are.
Unfortunately, these reports can often be pretty slow, too.
And if you’re using slow query reports in your mailings, your mailings are going to take a long time to build their targeting, which means it takes longer to get proofs or get your mailings out the door.
Why it’s slow
The most common reason these reports (and therefore the mailing builds) are slow is these reports are looking through large database tables with millions or hundreds of millions of records. And calculating those results can take a lot of time!
Some of the slowest tables to run reports on include:
- core_action (all actions ever taken by users)
- core_open (all mailings ever opened by users)
- core_usermailing (all mailings ever received by users)
In fact, that’s exactly why we created the summary_user table, which has all of the data you’re commonly looking for in these types of engagement queries, like:
- When did this user last take action?
- When did this user last take action from a mailing?
- When did they last open a mailing?
- When did they last click a mailing?
- When were they last subscribed to the default mailing list?
- When was their last donation?
- When did we email them last?
- How many actions have they taken in the last 30 days? 60? 90? 180? 270? 365?
Why this matters
Since we don’t have to look through the entire history of actions and donations and mailings and opens and clicks and so on, every single time, for every single user — because we do all that heavy lifting behind the scenes and keep summary_user regularly up to date, your reports will run SO fast and your mailings will build SO much more quickly.
In my tests, I found that smaller clients will see a speedup of 400X faster mailing builds when using summary_user compared to calculating the same results on other (slow) tables.
For larger clients, the benefits are even greater: we’re talking 3000X faster. So a report that you might have been waiting several minutes or even up to 30 minutes to complete, now completes in seconds when using summary_user.
Here’s how
To get started, you’ll need to edit your query reports to use the summary_user table whenever possible. This will require some custom SQL, but the good news is your queries will be much more straightforward, easier to read and understand, and of course, faster.
Here is an example of some code generated by the point-and-click query builder to calculate the number of actions a user has taken in the last 90 days:
If you look closely, you’ll notice that this query joins to the core_action table, which is large and therefore can be pretty slow.
This next bit of SQL code does the exact same thing, but has been rewritten to use the summary_user table. Notice that it’s much more straightforward and easy to read — and of course, it’s much faster too:
Refer to the database reference on summary_user to get started on transforming your queries.
For best results, start with optimizing a query that is very slow, or a query that is used very frequently in your targeting: go for the biggest, easiest wins first.
But summary_user doesn’t have _________!
Now if you’re thinking “this sounds great, but summary_user doesn’t have this particular stat we really care about” — custom user fields and user updaters are here for you.
These allow you to store whatever information you like about your users in custom user fields (like for instance: how much money did this user give in calendar year 2020?) and keep them up to date with user updaters.
Check out a video version of this blog post, plus our feature spotlight blog post and videos on custom user fields and user updaters for more about how to get started using these powerful features to speed up your mailing builds even more!
Interested in scheduling a demo with ActionKit? Let us know!