Marketers often execute promotions where they give $XX off when you purchase $YY or more. Recently a client of mine executed such a promotion and believed it provided a positive return. However, upon close examination, it was actually a negative return, and we halted the campaign immediately.
I would like to provide the methodology to calculate the return on investment.
Example Campaign: $25 off for all orders above $175
What needs to be measured in the period prior to campaign start date (say 2-4 weeks timeframe) and during campaign (let's say 2 weeks).
First we need to calculate the following measures; AOV stands for Average Order Value
|AOV> 175||$ 1,036,162||3,102||$ 334.03||7.70%|
|AOV||$ 2,443,252||37,168||$ 65.74||92.30%|
|$ 3,479,414||40,270||$ 86.40|
|AOV> 175||$ 1,212,523||4,676||$ 259.31||8.92%|
|AOV||$ 3,209,304||47,730||$ 67.24||91.08%|
|$ 4,421,827||52,406||$ 84.38|
For such a campaign, we need to know whether the distribution of order values have changed before and during the campaign.
In the above example, you can see that prior to the campaign, we had 7.7% of the orders that were greater than $175 and after the campaign this percentage went up to 8.92%.
What this means is that 8.92% - 7.7% = 1.22% of the 8.92% of orders were incremental, which means 1.22%/8.92% = 13.66% of the campaign period orders were incremental.
That 1.22% translates into (1.22% /8.92%)* 4,676 = 639 orders.
Now we need to make an assumption. There is no other way. We need to make an assumption about where these orders came from. A few options are
1) These orders came from people who were not going to purchase at all, and they purchased solely because of this campaign
2) These orders came from people who were going to spend the AOV similar to people who are below $175 in AOV (which for this group AOV = $67.24), but instead they spent the AOV for the group who took the offer (AOV>175 group), which is $259.31
3) People were going to spend 20% less than $175 = ~$140, and they increased their spend to above $175, which has an AOV of $259.31
Each of these assumptions can be made, some more optimistic (like 1) and some are more pessimistic (like 3). I personally like assumption 3 or something close to that.
Now we need to calculate the incremental lift and the cannibilization.
Incremental Lift using assumption 2:
Incremental revenue due to campaign: 639 Orders * $192.07 = $122,763
Incremental margin, assuming a 50% gross margin on products is
$122,763 *50% = $61,381
The cannibilization is the $s we lost on orders that we were going to get at full margin, but we decided to give $25 off.
Since 639 of the 4676 orders were incremental, we would have already received 4,037 orders even if we didn't do any campaign. For each of these orders we gave away $25 too. So the lost margin on these orders are
4,037 * $25 = $100,920
So the true incremental margin contribution of this campaign is $61,381- $100,920 = (-$39,539)