Quantium Chips Category Review – Data Analytics Case Study (Forage)

This repository contains my end-to-end solution for the Quantium Data Analytics Virtual Experience, focused on the chips (snacks) category. The project simulates a real engagement with a retail client, where the goal is to understand who buys chips, evaluate a new store layout trial, and provide data-driven recommendations for the Category Manager.

---

1. Business Problem

A large retailer wants to grow sales in the chips category. The Category Manager Julia needs to know:

• Which customer segments drive chips revenue and volume.
• How different life stages and affluence levels (Budget, Mainstream, Premium) behave.
• Whether a new shelf layout trial in three stores actually increased sales, beyond normal seasonal trends.

My role in this case study was to act as a data analyst–consultant hybrid: clean and analyze the data, run an uplift experiment, and translate the findings into clear commercial recommendations for Julia.

---

2. Data & Tools

Data sources

• QVI_data.csv – transaction-level chips sales data (store, customer, product, date, revenue, quantity).
• Derived features from product descriptions: brand, pack size.

Tools & libraries

• Python: pandas, numpy, matplotlib for data prep, analysis and plotting.
• PowerPoint / PDF for the final client-facing report.

---

3. Task 1 – Customer Analytics

3.1 Data preparation

Steps:

• Loaded the merged chips dataset using pandas.read_csv.
• Performed basic data checks: head, shape, missing values, data types.
• Parsed dates and verified numeric fields (TOT_SALES, PROD_QTY).
• Engineered additional fields:
  • PACK_SIZE – extracted numeric grams from product name.
  • BRAND – parsed brand names from product descriptions (e.g., Kettle, Smiths, Pringles).
• Removed outliers or invalid records where necessary (e.g., negative quantities).

3.2 Segment definitions

Customers are segmented along two dimensions:

• Life stage: Young Singles/Couples, Young Families, Midage Singles/Couples, Older Families, Older Singles/Couples, Retirees.
• Affluence: Budget, Mainstream, Premium.

This gives up to 18 customer segments (6 × 3).

3.3 Metrics and analysis

Key aggregated metrics:

• Total sales (TOT_SALES) by LIFESTAGE × PREMIUM_CUSTOMER.
• Average spend per transaction for each segment.
• Total quantity (PROD_QTY) purchased by segment.
• Top 10 brands by purchase frequency.

Example outputs (from grouped summaries):

• Older Families – Budget: TOT_SALES ≈ 168,363 and very high quantity, making them one of the biggest revenue drivers.
• Young Singles/Couples – Mainstream: TOT_SALES ≈ 157,622 with high purchase frequency but smaller pack sizes.
• Premium customers have slightly higher average spend per transaction (around 7.4 vs 7.2), but contribute less to overall volume than Budget and Mainstream.
• Top brands by frequency include Kettle (41,288 purchases), Smiths (28,860), Pringles (25,102), followed by Doritos and Thins.

3.4 Insights from Task 1

• Key revenue segments: Older Families (Budget) and Young Singles/Couples (Mainstream) are the top chip-buying segments by total sales and quantity.
• Behavior differences:
  • Families prefer larger pack sizes and buy in bulk.
  • Young Singles/Couples prefer smaller packs but purchase more frequently.
• Affluence: Premium customers spend more per trip but account for a smaller share of volume; Mainstream shoppers dominate volume and total revenue.
• Brand strategy: Kettle, Smiths, and Pringles have strong loyalty and should receive prime shelf space and promotional focus.

These insights feed directly into targeting, assortment, and promotional strategies for the chips category.

---

4. Task 2 – Experimentation & Uplift Testing

4.1 Objective

Evaluate whether a new shelf layout trial in three stores (77, 86, 88) led to a statistically meaningful increase in sales, customers, or purchase frequency, compared with similar control stores.

4.2 Monthly store-level metrics

Using QVI_data.csv, monthly store features were built as:

monthly = df.groupby(['STORE_NBR', 'MONTH']).agg(
    TOT_SALES=('TOT_SALES', 'sum'),
    NUM_CUSTOMERS=('LYLTY_CARD_NBR', 'nunique'),
    NUM_TXNS=('TXN_ID', 'nunique')
).reset_index()

monthly['AVG_TXN_PER_CUST'] = monthly['NUM_TXNS'] / monthly['NUM_CUSTOMERS']

Metrics per store per month: Total sales revenue. Number of unique loyalty customers. Average transactions per customer (intensity of purchasing).

4.3 Control store matching

To isolate the trial impact from seasonality and broader market shifts, each trial store needed a control store with similar pre-trial dynamics. A helper function was written:

def find_best_control(trial_store, metric='TOT_SALES', trial_start='2019-02'):
    trial_data = monthly[(monthly['STORE_NBR'] == trial_store) &
                         (monthly['MONTH'] < trial_start)]
    scores = {}
    for store in monthly['STORE_NBR'].unique():
        if store == trial_store:
            continue
        control_data = monthly[(monthly['STORE_NBR'] == store) &
                               (monthly['MONTH'] < trial_start)]
        merged = pd.merge(trial_data, control_data, on='MONTH',
                          suffixes=('_trial', '_control'))
        if not merged.empty:
            correlation = merged[f'{metric}_trial'].corr(
                merged[f'{metric}_control'])
            scores[store] = correlation
    if scores:
        best_match = max(scores, key=scores.get)
        return best_match, scores[best_match]
    return None, None

This function:

Compares pre-trial trends (before Feb 2019) between the trial store and all other stores. Computes Pearson correlation on chosen metric (e.g., TOT_SALES). Selects the store with the highest correlation as the control.

Example result:

Store 77 matched with Store 233 with 0.93 correlation, indicating extremely similar pre-trial performance.

4.4 Trial period analysis

For each trial–control pair, performance during the trial period (Feb–Aug 2019) was compared: Trend charts of monthly total sales for trial vs control store.

Comparison of:

• Total revenue.
• Number of unique customers.
• Average transactions per customer.

Key observations:

Store 77 vs 233

• Store 77 shows a noticeable and sustained increase in total sales during the trial, while Store 233 remains broadly flat.
• Uplift is mainly driven by more unique purchasing customers, indicating the new layout attracted additional shoppers. Stores 86 and 88
• Store 86 exhibits modest improvement, but not as strong or consistent as Store 77.
• Store 88 shows little to no change versus its control store, suggesting the layout had minimal effect there.

4.5 Conclusions from Task 2

• Control stores successfully isolate the effect of the layout by accounting for seasonality and wider market changes.
• The layout trial is clearly successful in Store 77, with strong sales uplift and increased customer count.
• Results are mixed in Stores 86 and 88, indicating store context (location, demographics, competition) likely influences layout effectiveness.

---

5. Task 3 – Final Report & Recommendations

• Using results from Tasks 1 and 2, an executive-style slide deck was prepared for Julia, structured using the Pyramid Principle: recommendation first, then supporting insight and detail.

5.1 Core recommendations

• Roll out the new layout to stores similar to Store 77
  • Store 77 demonstrated clear, sustained uplift with more unique customers and stable control store performance.
• Prioritize high-value segments in chips strategy
  • Focus on Older Families (Budget) and Young Singles/Couples (Mainstream) as primary revenue drivers.
  • Tailor pack sizes and promotions to match their behavior (large packs for families, frequent small-pack deals for young singles).
• Optimize brand and pack-size assortment
  • Ensure strong representation and visibility for Kettle, Smiths, Pringles, the most frequently purchased brands.
  • Promote larger pack sizes for family segments to increase basket value.
• Leverage loyalty data for personalization
  • Use loyalty card data to target offers by segment (e.g., upsell mainstream customers into premium brands).
• Expand and monitor future trials
  • Repeat the layout trial in additional stores with similar profiles and continue using control store methodology to validate impact over time.

5.2 Deliverables

• Task 1 code & outputs – Python notebook / script exported as PDF explaining segment analysis and key tables.
• Task 2 code & plots – Python script implementing control store matching and trial analysis, with example time-series visualizations.
• Final slide deck – “Chips Category Analysis & Store Trial Report” summarizing insights and recommendations for the Category Manager.

---

6. Repository Structure

quantium-chips-case-study/
├── data/
│   └── QVI_data.csv              
├── notebooks/
│   ├── 01_customer_analytics.ipynb
│   └── 02_trial_uplift_analysis.ipynb
├── src/
│   ├── data_prep.py              
ure engineering
│   ├── segment_analysis.py       # Life stage × affluence analysis
│   └── control_store_matching.py # Trial vs control logic
├── reports/
│   ├── Task-1-Data-Analyst-Chips-analysis.pdf
│   ├── Data-Analyst-Task-2.pdf
│   └── Chips_Category_Final_Report.pdf
└── README.md                     

---

7. What This Project Demonstrates

• Ability to clean and engineer features from real retail data.
• Strong segmentation and descriptive analytics skills to understand customer behavior.
• Application of experimental design and control store methodology to measure causal impact.
• Skill in translating analysis into commercial recommendations and presenting them in a client-friendly way.

https://forage-uploads-prod.s3.amazonaws.com/completion-certificates/32A6DqtsbF7LbKdcq/NkaC7knWtjSbi6aYv_32A6DqtsbF7LbKdcq_KnWM7DW8jmBQptTWD_1752553416661_completion_certificate.pdf