Interactive PowerBI: Promotion Analytics
The business problem: Promotional coupons are one of the most expensive and least understood tools in a brand's commercial toolkit. Most teams can track redemptions — but few can accurately quantify whether that spend drove incremental sales, or simply discounted purchases that would have happened anyway.
What this solves: This tool simulates the financial impact of a coupon event prior to launch — quantifying incrementality, net margin impact, sales lift, and redemption efficiency based on elasticity rates. It enables trade marketing and commercial teams to evaluate promotional trade-offs and optimize spend decisions before committing budget.
What follows: A step-by-step walkthrough of how to use the model — from inputting coupon parameters to interpreting the financial and volume outputs.
The coupon input report is meant to simulate both a financial breakdown as well as an analytical understanding of the impact of sales in the market.
Step 1 - Select the LOB (Line of Business) or product that is being sold. In the default example, we are selecting Bubbletime water.
Step 2 - Input the parameters of this coupon, including the value, the fee of the coupon provider, the budget, and purchase requirement, and selected dates.
The incrementality rate is derived from high-level elasticity rates - it’s the additional impact of the coupon. In this model, we only care about the added lift. If someone was already going to buy the water without the coupon then the coupon is completely decremental. An incrementality rate of 25% for example means that for every 4 people who redeem the coupon 1 person is net new, meaning the presence of the coupon incentivizes 1 new person out of 4 to redeem. The other 3 would have already bought the water without the coupon itself. An incrementality rate of 100% means that all redemptions are from people who are net new. The higher the coupon value the higher the incrementality rate. For example, a $5 coupon for a $30 cake is much more enticing for customers than a $1 coupon for a $30 cake. The incrementality rate will auto-default and adjust based on the relative size of the coupon compared to the retail price, but users can override that with the slider.
Given the inputs in steps 1 & 2, the analysis shows the step by step calculation and how to interpret the results.
The breakdown graph at the bottom left shows the impact of the parameters on Gross Margin, Incrementality, Sales Lift, and Units Sold. The key highlight is that the sales lift and units sold go down as you increase the coupon value, since it equates to less overall coupons to be redeemed given a fixed budget. However, this is partially offset by the velocity of sales (not in model) - where business owners may prioritize a quicker spike in sales in the short-term versus longer term efficiency.