If you’ve been in email marketing for any length of time, you’ll know the stomach-churning panic of sending an email to the wrong segment of customers. How many complaints will we get? How many unsubscribes? Will we lose revenue? Was it my fault?
However, considering the impact such a mistake can have, it’s actually relatively simple to avoid. Here we show you how to check you are sending to the right segments by using negative database queries. It requires a little bit of basic math and logic, so we’ve put together an example to walk you through the process.
The Example
A multi-channel clothing company wants to send a holiday email, so they decide to target everyone who purchased a swimsuit in the last three months. However, as they are also running an automated reactivation campaign, they need to exclude anyone who hasn’t opened or clicked in the last year.
The Solution
The principle for the checking process uses two fundamental mathematical ideas – Venn diagrams (remember them?) and algebra. The database is easily split into three overlapping categories defined by the targeting criteria for the holiday email:
Total Mailable – anyone on the database who can be emailedTotal Engaged – anyone who has opened or clicked in the last 12 months
Total Purchasers – everyone who purchased a swimsuit in the last three months
Again, it’s easy to see that the database query needed to target the right segment for the holiday email is the intersection between all three categories, Query 1 below – customers who can be mailed, who have purchased within three months and who have opened or clicked in the last year.
But how do you check that the query has returned the correct customers and volumes?
You start with your known values, in this case the number of customers in the three categories: Total Mailable (M), Total Engaged (E) and Total Purchasers (P).
You then need to create queries for options that can be validated by these known values. So, if you look at the diagram below, the Mailable customers category can be made up of a combination of different queries, as can the Engaged category.
These are called negative queries and they are the key to checking your initial query.
The Negative QueriesHere are all the queries we ran in the diagram above to check the numbers for the targeting of the holiday email:Positive query (the segment being targeted with holiday email):Query1 – Customers who are mailable and have opened /clicked and have purchased
Negative queries (for checking):
Query 2 – Customers who opened /clicked but did not purchase
Query 3 – Customers who are mailable and have purchased
Query 4 – Customers who are mailable but did not purchase
Query 5 – Customers who are mailable and purchased but did not open/click
Query 6 – Customers who are mailable but did not purchase and did not open/click
Query 7 – Customers who are no longer mailable but purchased and have opened /clickedAs you can see in the diagram above, some of these negative queries are subsets of each other – e.g. Q1 = Q3 – Q5. Pretty obvious but this makes the assumption that Q3 & Q5 are correct in the first place. You would need to check Q3 by validating it against one of the known values, in this case the Mailable category (M): Q3 = M-Q4.
This is where the algebra comes in. In this instance there are 11 different equations using either M or E that you could run to check all the queries.
Q1 = M-(Q4+Q5)Q1 = E-Q2Q2 = E-Q1-Q7Q2 = E+M-(Q2+Q6+Q7)Q3 = M-Q4Q3 = Q5+(E-Q2-Q7)Q4 = M-Q3Q4 = M-Q1-Q5Q5 = M-Q1-Q4Q5= Q3-Q1Q6 = E+M-(Q2+Q3+Q7)
In practice you only need a few of these to validate each chosen query.Validating each queryHere are the equations (or sums) we ran to check the numbers for the targeting of the holiday email:
Query 1 ≤ Query 3 (this is a very basic initial check. We recommend having at least one of these at the beginning to quickly highlight any major problems)
Query 3 + Query 4 = Total Mailable Audience (this validates Q3 & Q4 against M)
Query 1 + Query 5 = Query 3 (as Q1 & Q3 are already validated, this validates Q5)
Query 1 + Query 2 + Query 5 + Query 6 + Query 7 = Total Mailable Audience + Previously Engaged Non-Mailable Audience (this final check not only validates the remaining Q6, it also validates against both known values, E & M)
And that’s it! If all of these checks work, then you know you have the right segment and volumes to press send with confidence. Do you agree with our method? Or do you have your own method of validating queries? Let us know.
