5.1.1 Fundamental Investment Analysis Using Data Boutique’s Data: Retail and Consumer Goods

Updated by Andrea Squatrito

Fundamental Investment Analysis Using Data Boutique’s Data: Retail and Consumer Goods

Data Boutique provides eCommerce datasets under the E0001 schema that are ideal for investment analysts performing fundamental analysis on retail and consumer goods companies. This guide will walk you through using Data Boutique’s E0001 data to track inventory levels, pricing power, and discounting strategies, which are useful indicators of a brand's market positioning, demand trends, and revenue potential.

Overview of E0001 Schema Fields

The E0001 schema includes the following key fields, which are essential for conducting analyses in the retail and consumer goods sector:

  • Website name: The retailer or eCommerce website name.
  • Competence date: The date the data was captured.
  • Country Code: Two-letter code representing the country.
  • Currency Code: The currency in which prices are displayed.
  • Brand: The brand name of the product.
  • Category 1, Category 2, Category 3: Product categorization fields for hierarchical organization.
  • Product code: Unique product identifier.
  • Product title: Name or title of the product.
  • Product page URL: URL to the product’s page on the website.
  • Full price in local currency: Original price before any discounts.
  • Discounted price in local currency: Price after discounts, if applicable.
  • Full price in EUR: Original price converted to EUR.
  • Discounted price in EUR: Discounted price converted to EUR.
  • Flag discounted: A Boolean field indicating if the product is discounted (1 = discounted, 0 = not discounted).

1. Real-Time Sales and Demand Forecasting

Purpose

Monitor real-time pricing and discount patterns across products and categories to gauge demand strength or inventory stress in retail. Observing frequent discounts or stock fluctuations can signal revenue trends, potential demand spikes, or supply constraints.

Use SQL to calculate the percentage change in prices and assess demand by tracking which products frequently go on sale or see price changes.

SQL Example
SELECT
    product_code,
    brand,
    category_1,
    competence_date,
    full_price_in_eur AS current_price,
    LAG(full_price_in_eur) OVER (PARTITION BY product_code ORDER BY competence_date) AS previous_price,
    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,
    flag_discounted
FROM
    E0001_data
WHERE
    website_name = 'specific_website'
ORDER BY
    product_code, competence_date;

This query uses LAG() to calculate the percentage price change for each product over time, with flag_discounted indicating whether the product is on sale.

Use the GROUP BY function to calculate average price changes monthly or quarterly for each category.

SELECT
    category_1,
    DATE_TRUNC('month', competence_date) AS month,
    AVG(price_change_percent) AS avg_monthly_price_change
FROM
    ( /* Insert previous query here as subquery */ )
GROUP BY
    category_1, DATE_TRUNC('month', competence_date)
ORDER BY
    month;

2. Brand Market Share and Pricing Power

Purpose

Evaluate a brand’s positioning and pricing power by monitoring SKU growth, price consistency, and discount behavior.

Step 1: Tracking SKU Growth by Brand

Track the number of unique product listings by brand over time to gauge brand expansion or contraction.

SQL Example
SELECT
    brand,
    category_1,
    COUNT(DISTINCT product_code) AS sku_count,
    DATE_TRUNC('month', competence_date) AS month
FROM
    E0001_data
GROUP BY
    brand, category_1, month
ORDER BY
    month, brand;

Step 2: Assessing Price Stability Across Brands

Analyze the frequency and consistency of discounts, which may reflect a brand’s ability to maintain price levels despite competitive pressures.

3. Competitive Landscape and Pricing Comparison

Purpose

Analyze competitive positioning by comparing prices and discounts across brands within a category.

Step 1: Benchmarking Prices and Discounts

Compare average prices and discount levels between brands in the same category.

SQL Example
SELECT
    product_code,
    brand,
    category_1,
    AVG(full_price_in_eur) AS avg_price_eur,
    AVG(discounted_price_in_eur) AS avg_discounted_price_eur,
    AVG(flag_discounted) * 100 AS discount_frequency_percent
FROM
    E0001_data
WHERE
    category_1 = 'consumer goods'
GROUP BY
    product_code, brand, category_1;

Step 2: Visualizing Competitive Benchmarks

In a BI tool like Looker, create a Time Series Chart for price trends across brands to identify price leaders and discounting strategies. Heatmaps can help visualize inventory and price differences.

4. Discount Patterns and Promotional Strategies

Purpose

Identify brands or categories with frequent discounts, indicating potential demand softness or promotional strategies.

Step 1: Calculating Discount Frequency and Depth

Use flag_discounted to calculate how often products are discounted and analyze the depth of these discounts.

SQL Example
SELECT
    product_code,
    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
WHERE
    website_name = 'specified_website'
GROUP BY
    product_code;

Step 2: Analyzing Discount Patterns in BI Tools

  • Frequency of Discounts by Category: Visualize discount frequency to highlight categories that rely heavily on promotions.
  • Average Discount Depth: Display average discount percentages to reveal categories or brands with significant markdowns.

Conclusion and Investment Insights

By leveraging the E0001 schema’s eCommerce data, investment analysts can uncover valuable insights into retail and consumer goods companies. Monitoring inventory trends, pricing power, and discounting behavior provides a view of market dynamics and brand health, helping analysts forecast revenue, gauge competitive positioning, and identify potential risks or opportunities.

How did we do?