Revology Analytics Case Study series in Outcome-Based Analytics™

A $1.5B Consumer Packaged Goods manufacturer wanted to drive Market Share gains and increase Promotional ROI by building an in-house Promotion Effectiveness and Optimization platform.

The Company, a leading producer of branded and private-label Frozen Desserts, was losing market share and profitability as niche competitors proliferated the marketplace.

Compounding these adverse market trends, large global Consumer Packaged Goods (CPG) competitors continued to exert downward pricing and promotional pressure in store aisles. National competitors were investing heavily in price, squeezing out the Company in both shelf space and revenues.

The Company historically invested ~ 15% of its Gross Revenues in price promotions and shopper marketing, which it raised to ~ 20% over 2-3 years. Despite a 33% relative increase in promotional spending, there was little corresponding volume lift, while Gross Margins eroded both on a % and $ basis.

To grow both Profitability and Market Share, the Company wanted to understand the effectiveness of trade promotions and deploy an in-house, flexible solution that provided the following:

1. Clarity: A clear view of the micro (promo event-level) and macro (broader insights by Retailer, product group, promotion type) promotional ROIs.

2. Optimization: Predictive and prescriptive analytics capabilities that optimized promotional plans and price investments for the Sales and Revenue Management teams.

Pains

Three key pain points resulted from this Problem:

1. Sub-Optimal Planning: Sellers could not effectively plan for price promotions and shopper marketing events as part of their Annual Sales Planning process.

   They didn't know the base and promoted price elasticities, display, and feature support multipliers, the impact seasonality, or the impact of competitive pricing behavior.  

2. Increasing Price Investments, Declining Gross Profit: It caused the Company to lose Gross Margins because they were not deploying promotional dollars in the best way.

   There was no solid empirical analysis of historical promotional ROIs or scenario analysis capabilities of what could happen given specific pricing and promotion investments.

3. Declining Category Leadership: It stymied growth with the top 10 retailers, allowing other companies to take Category captain positions.

   The Company could not demonstrate their price investments' impact on Retailer Category growth or make a convincing argument for more in-store Display or Feature support.

The Promotion Analytics and Workflow Gap

The lack of data and insights-driven legacy planning and promo execution processes resulted in millions of wasted promotional dollars each year and stagnant growth for their anchor brands.

There was no Pricing or Trade Analytics platform to measure campaign performance down to the promotion or shopper marketing event level or across product categories and brands.

Sales, Demand Planning, and Finance teams were using disparate processes to evaluate the impact of price investments or promotional events. Disparate Excel spreadsheets and ad-hoc processes were the norms, often duplicating the same promotional analysis with widely different results, depending on whether the Finance, Sales, or Pricing team did them.

The Company had a homegrown system that worked well for basic, shipment-based Sales Planning and administrative Trade Promotion Management (TPM). Still, it lacked the necessary components for robust Promotion Analytics. 

 

1. There was no data-driven framework or strategic guidance on deploying price investments for distributor rebates, in-store price promotions, or shopper marketing across their three main channels.

2. Retailer or distributor sell-in data were not integrated with Point-of-Sale sellout data, despite both Retailer and Distributor sellout data being available from various syndicated sources (Nielsen, IRI, NPD).

3. A legacy culture rewarded relationship- vs. insights-based selling, with the deepest discounts often given to retailers with the lowest ROI and highest cost-to-serve.

Background

The Company operated three primary channels:

Why Revology Analytics?

The Company needed a partner who understood all the different elements required for Price and Promotion Effectiveness and Optimization.

✔      Deep technical expertise, including solid experience with analytics platforms and data engineering fundamentals.

✔      CPG & Retail Revenue Management expertise and Commercial leadership experience

✔      Strong applied Data Science experience in Retail and CPG, especially in modeling Base and Promoted Price Elasticities for various channels, customers, and products

✔      A firm understanding of when to build new/enhance existing tools vs. invest/buy a turnkey TPX solution

✔      Organizational empathy toward the sales organization to drive change management and solution adoption

The Company also wanted to implement a ~ 90% total value realization solution from start to finish in ~ 4 months.

They chose Revology Analytics given our Revenue Management and Data Science expertise and our specialized focus on developing fast, flexible, and affordable in-house Revenue Growth Analytics solutions.

While other firms had turnkey TPX solutions emphasizing Ml and AI, Revology Analytics focused on delivering a simple-to-use and flexible solution that both analytical and frontline sales team members could leverage to drive weekly pricing decisions. We used technologies that the Company was already familiar with and had as part of its existing tech stack.

Popular tech stack used to build and deploy the TPE/TPO solution:

The Solution Timeline

I. Understand (Weeks 1-2)

We spent the first couple of weeks diving deep into the Promotion Effectiveness problem to understand drivers, quantify the benefits and align on the essential outcomes.

We aligned with the project's Executive Sponsor (the EVP of Sales) and built a Core team of IT, Sales, Finance, Revenue Management, and Demand Planning leaders.

Ideal business outcomes within 12 months of capability launch:

1. Grow $ market share by 5% for anchor brands by gaining a disproportionate share of high ROI promotional events.

2. Increase Promotional ROIs from the industry standard of 80% to 100% (Incremental Gross Profit $ = Promotional Investment $).

3. Improve Gross Profit $ by 10% through base price increases and surgical promotion investments.

 

II. Research (Weeks 2-3)

After conducting root cause diagnostics and estimating the business impacts, we aligned with the Core team to focus on the below activities.

We zeroed in on the Grocery and Convenience channels and excluded Foodservice from the scope, given its stable growth and high profitability thanks to a growing Private Label business.

Phase 1: Establish the Foundations

1. Model price elasticities, including promotional effects for the Grocery channel, by Customer (Retailer) and Product. The resulting price elasticity coefficients will be a crucial input for the Promotion Optimization solution, allowing for robust scenario analysis by the Sales and Demand Planning teams.

2. Build a specialized Pricing & Promotions data warehouse in MS Azure SQL to house order-level sales and profitability data (sell-in data) and detailed syndicated data (Retailer sellout data based on IRI and Nielsen data extracts).

3. Create standard hierarchies  (Customer and Product) for sell-in and sellout data to seamlessly integrate for promotion analytics.

Phase 2: Drive Promotional ROIs for the Grocery Channel

1. Build a Promotion Analytics Platform in Tableau server, accessible to the entire organization, with the primary audience being the Sales teams. There should be three components to the dynamic Tableau dashboards:

• Event-level promo ROI analysis that would enable Sales and Finance teams to see Promo event ROIs for the Company and its Retail customers, respectively. It should also have Account Manager stack rankings to understand individual Sales Rep opportunities for improvement

• Macro-level promotional analytics to understand ROI trends over time at various hierarchical levels (by Retailer, by Brand, etc.). Analysts in Revenue Management, Finance, and Sales Ops would have a multi-dimensional view of what worked and what didn't, informing Annual Sales Planning inputs.

• Scenario Analysis capabilities that empowered the Sales org to model the impact of Price investments by Retailer and Product and have empirical conversations with their Retail buyers (their clients). Instead of saying "yes" to most buyer requests for promotions, the robust "what-if" capabilities allowed the Company to have substantially better, more transparent, and insights-driven conversations with Retailers. Account Managers would advise their Buyers on how Price Promotions should be structured and when/where/how they should be deployed to maximize the benefits for both the Company and the Retailer.

Phase 3: Drive Profitable Growth for the Convenience Channel

1. Restructure the legacy Rebate program for Convenience channel distributors into a Pay-For-Performance scheme. Create a selling story for Sales Directors and Managers to implement with their distributor partners.

Side note: The legacy Rebate program had been unchanged for the past decade, paying Rebates to Distributors based on annual sales volume. Over time, ~ 50% of distributors migrated to the highest volume threshold (primarily due to inflation alone), receiving the maximum Program rebates. Restructuring it based on a combination of Annual Volume and Growth would drive a higher upside to the top-performing Distributors. It would also ensure Operating Profit preservation by not wasting unnecessary dollars on average Distribution partners who are not contributing to sales growth. We will dive into the details of the Convenience Channel rebate rationalization exercise in a separate case study.

III. Align (Weeks 4-9)

As is customary with Revology Analytics projects, we engaged in several iterative stakeholder alignment sessions and executive roadshows. These are critical to ensure that the analytics solution solves the Problem your stakeholders and customers care about. Stakeholder alignment drives maximum adoption and results by bringing critical people along the journey, creating a sense of clarity and shared ownership.

Our alignment sessions followed the below timeline:

Week 4-5: Stakeholder Alignment

Worked with the Core working team and Executive Champions across Sales, Finance, Category Management, and IT to align the Concept for the Promotion Effectiveness & Optimization platform. For each of the key stakeholders, we wanted to know the essential questions they wanted to be answered and the behaviors they wanted to drive:

• Sales: what do they want to see in the Promotion Analytics platform to have better conversations with their Retailers and drive more favorable Promotional slots? How should the insights be structured to provide seamless inputs into their Annual Sales Planning process? How should we structure essential Promotional Effectiveness templates that can be easily copied/pasted into Monthly Sales Reviews? What are the 2-3 critical questions that a Sales Director vs. a Sales Rep may ask about Pricing & Promotions?

• Finance: what do they need to help better plan for promotional investments in the Grocery channel? What metrics do they want to evaluate to determine whether Promotional Investments are paying off?

• Category Management: how can we structure the insights to provide category-level Promotion Effectiveness capabilities they can share with Retailers? How to best demonstrate whether the Company's promotions drove Retailer Category growth?

• Revenue Management: how should we structure Pricing and Promotional investment scenario analysis capabilities in the tool? What key metrics help the RevMan team make better pricing decisions? What are other key Pricing and Promotion questions that the Revenue Management team needs rapid answers to from the Promotion Analytics platform?

• IT: what resources do we need to rapidly migrate essential data from slow on-prem servers to the Cloud? Can we handle the data harmonization between shipment and sellout data internally, or do we need expert contractors? At what level of detail do we perform data integration for our Promo Effectiveness & Optimization solution?

 

Weeks 5-8: Design the User Journey + Pricing & Promo Data Warehouse Design

Once we aligned on the Concept, we collaborated with the Core Team on the Promotion Analytics Tool Solution design. It included mapping out the data elements and creating a data architecture for measuring Promotion Effectiveness.

We also designed the user journey and high-level UI for data visualizations in Tableau.

We aligned with the Revenue Management, Sales, and Finance teams on the detailed methodology for evaluating Promotional ROIs for the Company and Retail partners, including a list of Key Promotional Effectiveness metrics.

The below displays the critical Promotional Effectiveness metrics we designed to capture at the Customer-Product-Week level. The chart also shows which data components came from internal (shipment) vs. external (consumption) data sources.

Weeks 8-9: Design Endorsement

After finalizing the Concept & Design with the relevant stakeholders, we did a final review with the Core team and Executive sponsors. We also engaged Buyer teams from a couple of leading Retail clients in the Concept & Design review phase to ensure that the Retailers also extracted valuable insights using the new Promotion Analytics platform.

IV.    Minimum Viable Analytics Solution (MVAS) (Weeks 5-21)

Weeks 5-12: Pricing & Promotions Data Warehouse Buildout

The required data elements for a robust Pricing & Promotion Analytics platform resided in various environments across both internal and external data:

• Company promotional and shopper marketing details were stored in Oracle data warehouse tables

• Internal shipment, financial and operational data resided in Microsoft SQL Server tables

• Consumption (Grocery, Mass Merch, Club, and Convenience retailers) was also syndicated data in the form of aggregated Point-of-Sale information and accessible through Nielsen and IRI.

Custom Azure Warehouse: We collaborated with IT and a 3rd party IT consulting firm to deploy a cloud-based and purpose-built Pricing & Promotions (P&P) data warehouse in MS Azure SQL.

The P&P data warehouse contained all the foundational data elements needed for our Promotion Effectiveness and Optimization Capability. It integrated the key internal data elements from Oracle and MSFT SQL. It used a combination of APIs and data extract automation from web portals (with written vendor permission) to ingest the external, syndicated data.

Standardized Hierarchies: Revology Analytics worked with a core team of Company experts to create standardized hierarchies (e.g., internal Cost Centers to IRI/Nielsen Retailers) needed to harmonize internal shipment and external consumption data. Integrating internal and external data elements enabled us to capture critical Promotional Effectiveness metrics for the Company.

From start to finish, the P&P Data Warehouse was deployed in 8 weeks and served as the foundation for the Company's Promotion Effectiveness & Optimization platform and the central data repository for all things Pricing Analytics.

Weeks 12-20: Solution Buildout (including Price Elasticity Modeling)

We built a robust Demand Model to serve as the basis for the in-house Promotion Effectiveness & Optimization capability. We used a combination of Multiplicative Regressions (log-log) and Random Forest, a popular tree-based ensemble machine learning model for the Demand Model.

We leveraged the Demand Model for two critical analytics foundations for this project:

1. Price Elasticities: Create Customer-Product level Base Price Elasticities, Promotional Discounts, and Promotional Lift coefficients for Display, Feature, and Shopper Marketing.

2. Baseline Performance: We used the Price Elasticity and Promotional Lift coefficients to estimate Baseline Unit sales for each Customer-Product combination.

Price Elasticity coefficients were foundational to Pricing and Promotional Scenario Analysis capability buildout, while Baseline Units allowed us to calculate Incremental Units for each Customer and Product.

We built the TPE / TPO solution using Tableau server as the front-end for Data Visualizations, Pricing and Promotion KPI reporting, and Pricing Scenario Analyses. The key Promotion Effectiveness metrics were calculated using a mix of internal and external measures and enabled the user to evaluate Promotion Effectiveness at a micro- (down to the Promotion event) and macro-level (i.e., by Channel-Brand or Customer-Brand level trends over time):

1.    Promotional ROI: How much extra Gross Profit $ am I generating for each $1 of Promoted Price Investment?

• Calculated as the ratio between Incremental Gross Profit $ and Total Promotion Event Spending. Incremental Gross Profit $ is the product between Incremental Consumer Units (from syndicated, IRI, or Nielsen data) and the Company's internal GP$ per Unit (for a particular Customer-Product combination).

2.    Promotional Lift %:  What is the % Net Sales or Unit Lift due to Promotions?

• Calculated as the ratio of Incremental Sales $ (or Units) and Baseline Sales $ (or Units). Recall, Baseline Units are a statistical estimate of how many Units the Company would sell under regular or baseline prices.

3.    Cost per Incremental Unit: How much $ am I spending to sell one incremental unit to a Consumer?

• Measured as Total Promotional Event Spending / Incremental Units

4.    Promotion Pass Through %: How much of my Promotional Investment is passed on to the End Customer by the Distributor or the Retailer?

• ((Base Price – Promo Price in market) x Total Units) / Total Promotional Event Spending 

We built out the Scenario Analytics capabilities so a beginner user can quickly run specific Pricing and Promotions scenarios to understand the business impact. We also customized the Promotion Effectiveness and Scenario Analysis modules to provide greater simplicity and fewer choices for the Sales Team and more complexity/options for the Revenue Management teams.

As part of the Predictive Analytics modules, we enabled critical what-if scenarios, such as:

1. What is the impact on Company Unit Sales, Net Revenue, and Gross Profit given specific Base Price Changes? What is the effect on consumer sell-through by the Retailer?

2. What happens if we make certain Promotional Investments or changes (deeper discounts, reduced frequency, different levels of Display or Feature support by the Customer)?

3. How do the business impacts for the Company and its B2B Customers (primarily Retailers) change given different levels of Promotional Pass-Throughs?

4. What would the Company's YTD Pro-Forma results look like if the bottom 50th percentile Promotion ROI Retailers caught up to the median performer? What would results look like if only 25% or 50% of the "bad performers" improved on their Promotional Support behavior?

Dashboard example: Which Price Promotion Events drove the best returns for our Retail Partners and us? Each bubble represents a specific Price Promotion event. Users can hover over for more information (Retailer, Product, Time, Price Investment, etc.) or select one or more promo events to view detailed Promotion Effectiveness metrics below. Promo Effectiveness metrics are displayed for the Company (Manufacturer) and their Retail partners, significantly enhancing Joint Business Planning and Buyer discussions.

Weeks 20-21: Power User Training for Sales, Revenue Management, Demand Planning, Finance, and Category Management teams

We conducted training in 3 phases over the course of two weeks:

1. Trained all the functional support teams (Revenue Management, Demand Planning, Finance, and Category Management)  with two dedicated training sessions per team.

2. Trained the Grocery and Convenience Sales Team leaders (VPs and Directors).

3. Finally, trained 3-5 Sales Power Users (Business Development Managers) from each Sales Region.

Each training session lasted 4 hours, broken into a 60-minute Pricing & Promotional Analytics overview, followed by a 2-hour case study style training on the Promo Effectiveness & Optimization platform. We asked training attendees to form working groups and reserved the last hour of our training for a real-world case study presentation. It was beneficial for Sales Power Users and Category Management team members, as it allowed them to analyze historical Promotional Events with both a Manufacturer and Retailer lens and recommend adjustments to improve both Company and Customer results.

V.     Launch (Week 21+)

We launched the final production version in Tableau server, with minor changes based on both Core Team and Power User feedback.

The new Promotion Effectiveness and Optimization capability was successfully deployed to a 150-member Sales team, with all the Pricing & Promotion insights and Scenario Analytics capabilities available on Laptops and iPads.

Dashboard example: Which Retailers drove the best returns for my Promotional Investments? Tabular display of Promotion Effectiveness stats by Retail Customer. While 5-10% of promotions (based on Pct of Units) yielded at least a Profit Break-Even investment, there is much opportunity for improvement, with overall Promotion ROIs in the ~ 45-50% range (Company losing $0.50 for every $1 invested in Price Promotions).

VI.    Assess

Ninety days after our launch, we evaluated Promotional ROI and Incremental Gross Profit $ results and conducted a post-mortem with key Stakeholders.

We typically use this engagement phase to ensure that our Revenue Growth Analytics solution delivers on the initially outlined outcomes. Based on this 90-day post-mortem, we adjust any existing process, analytics, or machine learning approach as needed.

 

Promotion Analytics Platform Delivery with Built-In Continuity

Revology Analytics ensured that we seamlessly handed off the Promotion Effectiveness & Optimization solution to IT with the ability to maintain and improve over time.

We also cross-trained Data Scientists in the Revenue Management team on maintaining and enhancing the Price Elasticity models. These efforts created significant long-term savings for the Company by not having to spend on expensive vendor or expert consulting support.

30x Return on Solution Investment

After six months, the Company had a sophisticated and easy-to-use solution to measure the effectiveness of its Price Investments, along with robust scenario analysis and trade optimization capabilities.

The Sales teams had much more robust, insights-driven conversations with Retail buyers about pricing and promotion performance. On sales calls, the Account Managers frequently used mobile Tableau dashboards to run real-time scenario analyses on price investments.

A big driver of project success was an excellent CIO and IT team. They partnered with Sales, Planning, and Revenue Management to deploy a purpose-built Pricing & Promotions data warehouse in MS Azure SQL. This data warehouse was the foundation of the Company's new TPE / TP capabilities, including harmonized shipment (sell-in) and sellout data.

Trade Investment ROI improved from 80% to 92%

While the Company still lost $0.08 for every $1 invested in price promotions, it was a 15% performance improvement from prior years. At a 92% ROI, it was also substantially better than the industry average, which hovered at ~ 85% ROI (based on a Nielsen study).

Gross Profit improved by 8% overall and 13% for anchor brands

The Company's anchor brands, which drove ~ 50% of revenues, saw the most significant Promotional ROI impact. Their price promotions skewed heavily toward Display and Feature support in favor of price discount-only promotions.

+6% Unit Volume and +4% $ Market Share Gain in 1 year

Sales improved as promotional guidelines focused on deeper price investments in fewer but more impactful promo and shopper marketing events. The augmented analytics capabilities also drove more impactful customer conversations and stickiness, which helped win Category Captainship positions with several Retailers.