Copyright © 2017 CPGAnalytics101
What is my products or competitors promotional impact to category dollars?
Based on a hypothetical scenario we want to see what a products promotional impact is to category total dollars. In this instance we will use quality merchandising (any combination of Features and Displays, NO TPRs) for Flakes 32oz and Bob's 32oz items. To do this analysis, we need to look at weekly periods, and any periods larger will dilute the data and not give a clear picture of events. We also want to pick down to the item level since brand level and higher will again dilute the true picture since they are varying sizes, and types included in a brand that may not be lined price or promote at different intervals.
Flakes 32oz has a larger share of category dollars, beating out Bob's 32oz by 2 share pts (9.4% vs. 7.4% respectively), but did Flakes 32oz quality promotions drive category sales when promoted with quality merchandising at a threshold of 80% or greater in support? The short answer is NO, Flakes 32oz did not drive category sales higher during quality weeks vs. non quality weeks based on our benchmark. Flakes 32oz actually had a (-6.4%) negative impact to average category sales during all quality events where Flakes 32oz had at least 80% or greater ACV quality support in the chain.
You can set the %ACV Support to any number you want, BUT I always set the threshold at least above 50%. The higher it is…the better. The reason for this is to draw product promotion exclusivity with a high threshold. This will help ensure that the event was more exclusive to our product with the data being more actionable.
So back to Flakes 32oz, it did not drive category sales higher when promoted vs. non promoted weeks, BUT Bob's 32oz did. In fact Bob's 32oz quality events positively impacted average category sales by +3.5% when it promoted with quality merchandising. Consumers resonated more to Bob's 32oz events and made it a win-win situation for the brand and the category. The reason it is a win-win is Bob's 32oz not only drove average category sales, but it was also beneficial in itself as these events also had a positive impact to the item. This would not always be the case if the product deep discounted to a point where margins were slim to none and be a loss leader.
See table below.
To follow-up, why was Flakes 32oz non-performing during its events?
-You might want to look at the timing of events and deal pricing.
-Did Flakes 32oz shift their promotional schedule, did the retailer?
-Was deal pricing for Flakes 32oz higher or lower vs. YAG?
-Did Flakes 32oz promote less frequently?
-Did Flakes 32oz receive less %ACV Quality support vs. YAG?
-Did Flakes 32oz lose %ACV distribution, and is not offered in a select zone?
-Did Flakes 32oz change merchandising tactics? Was there increased support behind TPRs?
-Was there negative media press, or product shortage causing out of stock conditions?
-What was Flakes 32oz promotional impact to the category YAG? More, Less, or flat?
The methodology is fairly simple. We averaged the weeks of category dollar sales where Flakes 32oz had 80% or more ACV quality support (promoted weeks) vs. averaging the category sales for the remaining weeks where Flakes 32oz had less than 80% quality support (Non promoted weeks). We then took the difference of the two scenarios as a percent to find out if Flakes 32oz drove category sales higher or lower sales when promoted.
A snippet of the data is provided below to give you a visual.
In the table above, I have set my Quality promotional ACV Support at 80% under F4. In cells A11 thru A20 are my weekly periods where we have category dollars in column B followed by Quality %ACV in columns D & E.
Columns F – H returns the category dollars if your product meets the %ACV benchmark. So cell F11 has a formula that states if D11 is => F4, then give me B11 (category $) otherwise leave the cell blank. This is done down all the rows for each product analyzed. This is considered to be the products weekly promoted dollar impact to category dollars.
Columns K - M returns the category dollars if your product does not meet the %ACV benchmark, this is considered the products non-promoted weekly dollar impact to category dollars. The formula states if F11 = blank then give me B11, otherwise return blank. This too is done down all the rows for each product analyzed.
The yellow highlights in Row 10 are the averages based on the data returned, down all the rows. So if cell F10, we are using the average formula by looking for the average dollars for cells F11:F62 (we want all 52 weekly periods, but is not shown in the example). Column F (average promoted $) correlates with Column K (average no promo $) because they are both for the Flakes 32oz product. Columns G & L refer to Bob’s 32oz product.
Once this is done, we then take the difference in the form of a percent. The category impact formula used for Flakes 32oz as example is:
(F10 / K10)-1 or (Avg $ Promo / Avg $ wo Promo)-1 or ($153,722 / $164,302)-1 = (-6.4%)
because Flakes 32oz quality promotions did not drive average category dollars during its promotional weeks vs. non promoted weeks based on the 80% or greater benchmark.