5.1.4 Fundamental Investment Analysis Using Data Boutique’s Data: Consumer Packaged Goods (CPG)

Updated by Andrea Squatrito

Fundamental Investment Analysis Using Data Boutique’s Data: Consumer Packaged Goods (CPG)

Consumer Packaged Goods (CPG) companies operate in a competitive, price-sensitive market where inflation, brand loyalty, and seasonal demand play significant roles. This article covers how to use Data Boutique’s E0001 schema to track price changes, analyze promotional strategies, and assess brand retention. These insights are essential for investment analysts evaluating revenue potential and risk in the CPG sector.

Overview of E0001 Schema Fields for CPG

The E0001 schema provides key fields for analyzing CPG data, including:

  • Website name: The retailer or eCommerce website listing the product.
  • Competence date: The date of data capture.
  • Country Code: The country where the product is listed.
  • Currency Code: The currency for the product price.
  • Brand: The brand of the CPG product.
  • Category 1, Category 2, Category 3: Categorization for hierarchical product organization.
  • Product code: Unique identifier for each product.
  • Product title: Name of the product.
  • Full price in local currency: The product’s original price before any discounts.
  • Discounted price in local currency: Discounted price if a promotion is applied.
  • Full price in EUR: Original price converted to EUR.
  • Discounted price in EUR: Discounted price in EUR.
  • Flag discounted: Boolean indicating if a discount is applied (1 = discounted, 0 = not discounted).

1. Inflation Tracking Through Price Monitoring

Purpose

Track price changes across essential CPG items over time to measure the impact of inflation on consumer spending and brand performance.

Step 1: Analyzing Price Increases on Core Items

Use SQL to calculate the percentage change in prices over time, focusing on items that are critical to consumers, such as food staples and household essentials. This analysis reveals how inflation may affect demand for non-discretionary products.

SQL Example

SELECT
              product_code,
              brand,
              competence_date,
              full_price_in_eur AS current_price_eur,
              LAG(full_price_in_eur) OVER (PARTITION BY product_code ORDER BY competence_date) AS previous_price_eur,
              ROUND((full_price_in_eur - LAG(full_price_in_eur) OVER (PARTITION BY product_code ORDER BY competence_date)) / 
                    LAG(full_price_in_eur) OVER (PARTITION BY product_code ORDER BY competence_date) * 100, 2) AS price_change_percent
          FROM
              E0001_data
          ORDER BY
              product_code, competence_date;

Step 2: Aggregating Price Changes by Brand and Category

Group the data to calculate average price changes for each brand and product category over time, allowing for an assessment of how inflation impacts different segments of CPG.

2. Brand Loyalty and Demand Elasticity

Purpose

Evaluate brand loyalty by analyzing price elasticity for high-demand products, especially during periods of price increases. Strong loyalty often correlates with consistent pricing, even in competitive markets.

Step 1: Tracking Price Elasticity

Use SQL to calculate how frequently brands adjust prices and offer discounts. Products with lower discount frequency may indicate higher brand loyalty and reduced price sensitivity.

SQL Example

SELECT
              product_code,
              brand,
              COUNT(CASE WHEN flag_discounted = 1 THEN 1 END) AS discount_frequency,
              AVG((full_price_in_eur - discounted_price_in_eur) / full_price_in_eur * 100) AS avg_discount_percentage
          FROM
              E0001_data
          GROUP BY
              product_code, brand;

Step 2: Comparing Brand Pricing Consistency

  • Price Consistency Analysis: Visualize how consistently brands maintain full prices compared to competitors, highlighting potential areas of strong brand loyalty.

3. Promotional Strategy Analysis

Purpose

Analyze discount patterns to understand brand strategies for customer acquisition and retention. Frequent discounting may indicate price sensitivity or targeted promotional efforts to boost demand.

Step 1: Analyzing Discount Patterns by Product and Brand

Calculate the frequency and depth of discounts for each brand and product, which can reveal trends in promotional activity and demand generation.

SQL Example

SELECT
              brand,
              COUNT(CASE WHEN flag_discounted = 1 THEN 1 END) AS discount_count,
              AVG((full_price_in_eur - discounted_price_in_eur) / full_price_in_eur * 100) AS avg_discount_depth
          FROM
              E0001_data
          GROUP BY
              brand;

Step 2: Visualizing Promotional Patterns in BI Tools

  • Discount Heatmaps: Use BI tools like Looker or Power BI to create heatmaps of discount frequency and depth by brand, showing which brands rely more heavily on promotions.
  • Monthly Discount Trend Analysis: Track discounts over time to identify patterns, such as end-of-quarter promotions or season-specific discounts.

4. Product Availability and Demand Forecasting

Purpose

Monitor inventory levels and product availability to forecast demand patterns and assess supply chain efficiency. Stockouts may signal high demand, while consistent availability could indicate stable supply chains.

Step 1: Tracking Stock Availability by Product

Use SQL to monitor stock levels across CPG products, especially for essentials. Frequent stockouts could indicate demand surges, while stable inventory levels may reflect supply chain reliability.

SQL Example

SELECT
              product_code,
              brand,
              competence_date,
              COUNT(CASE WHEN full_price_in_eur IS NULL THEN 1 END) AS stockouts
          FROM
              E0001_data
          GROUP BY
              product_code, brand, competence_date;

Create visualizations in BI tools to identify patterns in product availability across seasons, helping predict demand fluctuations, such as increased sales around holidays.

Conclusion and Investment Insights

Data Boutique’s E0001 schema for CPG provides critical data points that allow investment analysts to measure inflation effects, assess brand loyalty, and understand promotional strategies. Monitoring pricing trends, discount frequency, and stock availability offers insights into demand elasticity and consumer behavior, supporting more informed investment decisions in the CPG sector.

How did we do?