Real-World Case Studies¶

Theory tells you what analytics is. Case studies show you how it works when the data is messy, the stakeholder is skeptical, and the deadline is tomorrow. Three detailed case studies follow — read them as if you are the analyst in the room.

Learning Objectives¶

Apply the analytics lifecycle to realistic, end-to-end business problems
Recognize the investigative pattern in diagnostic analytics
Connect analysis to recommendation to outcome
Practice the thinking before you practice the tools

How to Read These Case Studies¶

Each case follows the same structure:

The situation — business context and the triggering event
The question — what the stakeholder actually asked
The investigation — how the analyst thought through it, what data they pulled, what they found
The finding — the specific, evidence-backed conclusion
The recommendation — what action the analyst suggested
The outcome — what happened (and what the analyst learned)

Case Study 1: E-commerce — The Conversion Rate Drop¶

The Situation¶

It is a Wednesday morning. You work as a data analyst at an online retailer that sells home goods. The VP of Digital walks into a sync meeting and leads with:

"Something is very wrong. Our conversion rate dropped from 3.2% to 2.1% overnight. We lost about $80K in revenue yesterday versus what we'd expect. What happened?"

The website had a redesign deployed the prior Saturday. Engineering says the deployment went smoothly. Marketing says their spend is unchanged. The VP wants answers by the afternoon standup.

The Question¶

Before pulling a single query, the analyst asks three clarifying questions: 1. "When exactly did the drop start — Saturday night at deployment, or did it trend down over several days?" 2. "Is the conversion rate drop uniform across device types, channels, and product categories?" 3. "Did we change anything else last week besides the redesign — pricing, promotions, email campaigns?"

The answers: The drop started sharply on Saturday at 11:43 PM (exactly when the redesign deployed). It appears uniform across the board from the top-line dashboard. There was a promotional email sent Sunday — but that was planned and is on the same schedule as prior weeks.

The Investigation¶

Step 1: Confirm the magnitude and timing

-- Hourly conversion rate around the deployment window
SELECT
    DATE_FORMAT(session_start, '%Y-%m-%d %H:00') AS hour,
    COUNT(DISTINCT session_id)                    AS sessions,
    COUNT(DISTINCT CASE WHEN converted = 1
                        THEN session_id END)      AS conversions,
    ROUND(COUNT(DISTINCT CASE WHEN converted = 1
                              THEN session_id END) /
          COUNT(DISTINCT session_id) * 100, 2)    AS conv_rate
FROM sessions
WHERE session_start BETWEEN '2024-09-21 00:00' AND '2024-09-25 23:59'
GROUP BY 1
ORDER BY 1;

Result confirms: conversion rate was 3.1–3.3% Friday and Saturday until 11:43 PM, then collapsed to 2.0–2.2% from Sunday onward.

Step 2: Segment by device type

This is the diagnostic instinct — the aggregate number hides the story.

-- Conversion rate by device type before and after deployment
SELECT
    device_type,
    CASE WHEN session_start < '2024-09-21 23:00' THEN 'Before' ELSE 'After' END AS period,
    COUNT(DISTINCT session_id)  AS sessions,
    COUNT(DISTINCT CASE WHEN converted = 1 THEN session_id END) AS conversions,
    ROUND(COUNT(DISTINCT CASE WHEN converted = 1 THEN session_id END) /
          COUNT(DISTINCT session_id) * 100, 2) AS conv_rate
FROM sessions
WHERE session_start BETWEEN '2024-09-19 00:00' AND '2024-09-24 23:59'
GROUP BY 1, 2
ORDER BY 1, 2;

Result:

Device	Before	After	Change
Desktop	3.4%	3.3%	-0.1% (normal variation)
Mobile	2.9%	1.2%	-1.7%
Tablet	3.1%	3.0%	-0.1% (normal variation)

Mobile conversion collapsed. Desktop and tablet are fine.

Step 3: Identify where in the funnel mobile users are dropping off

-- Funnel drop-off by device and period
SELECT
    device_type,
    CASE WHEN session_start < '2024-09-21 23:00' THEN 'Before' ELSE 'After' END AS period,
    SUM(visited_homepage)     AS s1_homepage,
    SUM(viewed_product)       AS s2_product,
    SUM(added_to_cart)        AS s3_cart,
    SUM(started_checkout)     AS s4_checkout,
    SUM(completed_purchase)   AS s5_purchase,
    ROUND(SUM(started_checkout) / NULLIF(SUM(added_to_cart), 0) * 100, 1) AS cart_to_checkout_pct
FROM session_funnel_events
GROUP BY 1, 2;

Result: On mobile, cart-to-checkout dropped from 61% to 23%. Users are adding items to cart — the redesign did not break product discovery. But they are abandoning at the checkout initiation step.

Step 4: Investigate the mobile checkout flow

The analyst pulls a screen recording tool review (Hotjar/FullStory) of mobile checkout sessions post-deployment. Finding: the new "Proceed to Checkout" button on mobile is positioned below the fold and partially obscured by a new promotional banner. Many users are scrolling past without seeing it.

The Finding¶

"The conversion drop is entirely driven by mobile users (65% of our traffic). Desktop conversion is unchanged. The root cause is a UX issue in the redesigned cart page: the checkout button is hidden below the fold on screens smaller than 768px. Users are abandoning at the cart page, not during checkout itself. This single bug is costing approximately $80K/day in lost revenue."

The Recommendation¶

"Immediate fix: engineering needs to reposition the checkout button above the fold on mobile within the next 2 hours. Secondary: add a sticky checkout button to the cart page on mobile as a permanent improvement. Estimated revenue recovery: full, within 24 hours of fix."

The Outcome¶

Engineering deployed the fix at 4 PM. Mobile conversion recovered to 2.8% by end of day (slightly below baseline — the residual was users who abandoned mid-day and did not return). Full recovery within 48 hours.

What the analyst learned: - Always segment before concluding. The aggregate masked the story. - Device type is one of the first segmentation cuts for any conversion analysis. - Quantifying the business impact ($80K/day) got the engineering fix prioritized over other tickets.

Case Study 2: SaaS — The Churn Spike¶

The Situation¶

You are an analyst at a B2B SaaS company that sells project management software. Monthly churn has been stable at 2.1–2.4% for 18 months. In August, it jumped to 4.8%. The CEO asks the analytics team for an explanation by Friday.

The Question¶

The metric is clear — monthly churn rate nearly doubled. But the question of why requires decomposition. The analyst starts by defining exactly what "churn" means in the company's data model (an important step that varies company to company):

Definition used: A customer churns in month M if they had an active subscription at the start of month M and did not renew or were cancelled by the end of month M.

The Investigation¶

Step 1: Is this one big customer, or a broad-based trend?

-- Count and revenue concentration of August churned accounts
SELECT
    COUNT(DISTINCT account_id)  AS churned_accounts,
    SUM(arr)                    AS churned_arr,
    AVG(arr)                    AS avg_arr_churned,
    MAX(arr)                    AS max_arr_churned
FROM subscriptions
WHERE status = 'cancelled'
  AND DATE_FORMAT(cancelled_date, '%Y-%m') = '2024-08';

Result: 47 accounts churned, total ARR impact = $680K. Average ARR = $14.5K. Not concentrated — this is not one large customer. It is a broad-based increase.

Step 2: Cohort analysis — when did these customers sign up?

The hypothesis: if a specific acquisition cohort is churning, something went wrong at the time of their acquisition (bad-fit customers, overpromised during sales, poor onboarding).

-- Churned accounts in August by signup cohort
SELECT
    DATE_FORMAT(s.signed_up_date, '%Y-%m') AS signup_cohort,
    COUNT(DISTINCT c.account_id)            AS churned_accounts,
    AVG(DATEDIFF(c.cancelled_date, s.signed_up_date)) AS avg_days_to_churn
FROM subscriptions c
JOIN accounts s ON c.account_id = s.account_id
WHERE c.status = 'cancelled'
  AND DATE_FORMAT(c.cancelled_date, '%Y-%m') = '2024-08'
GROUP BY 1
ORDER BY 1;

Result:

Signup Cohort	Churned Accounts	Avg Days to Churn
2023-01 through 2024-02	5 accounts total	280–400 days
2024-03	31 accounts	150 days
2024-04	9 accounts	125 days
2024-05 and later	2 accounts	90 days

The March 2024 cohort is massively over-represented. 31 of 47 churned accounts signed up in March.

Step 3: What was different about March 2024?

The analyst cross-references the company's CRM and internal records for March 2024: - Q1 ended March 31 — the sales team was on a push to close quota before quarter end - A promotional pricing campaign ran in March (30% discount for annual plan) - New onboarding flow was not yet live — March signups used the old, manual onboarding

Step 4: Validate with product usage data

-- Product engagement in first 30 days: March cohort vs prior cohorts
SELECT
    DATE_FORMAT(a.signed_up_date, '%Y-%m') AS signup_cohort,
    AVG(e.logins_day1_30)                  AS avg_logins_30d,
    AVG(e.features_activated)              AS avg_features_activated,
    AVG(e.projects_created)                AS avg_projects_created
FROM accounts a
JOIN early_engagement e ON a.account_id = e.account_id
WHERE DATE_FORMAT(a.signed_up_date, '%Y-%m') IN
    ('2024-01', '2024-02', '2024-03', '2024-04')
GROUP BY 1;

Result: March 2024 cohort had 40% fewer logins in the first 30 days, 55% fewer features activated, and 60% fewer projects created compared to the January and February cohorts. They never got value from the product.

The Finding¶

"August churn is almost entirely driven by the March 2024 signup cohort — 31 of 47 churned accounts. March was a promotion-heavy, quota-driven month with manual onboarding. These customers likely signed up for the discount, never achieved activation, and churned at their annual renewal. This is not a product problem — it is an acquisition and onboarding problem from 5 months ago. The good news: cohorts before and after March show normal churn behavior."

The Recommendation¶

Three recommendations, in order of urgency:

Immediate: Pull the April–May 2024 cohorts (signed up under similar conditions) and proactively reach out to low-engagement accounts before their annual renewal. Assign a CSM to each. Target: prevent $280K in additional churn risk.
Short-term: Implement an automated "onboarding health score" that flags accounts with low 30-day engagement for CS intervention. Cost of CS intervention is far lower than cost of churn.
Long-term: Require minimum engagement thresholds before offering renewal discounts. "Discount for loyalty, not for inactivity."

The Outcome¶

The CS team reached out to 22 at-risk April–May accounts. 14 had low engagement due to confusion about a specific feature. A 30-minute onboarding call resolved most cases. 8 of those 14 renewed. Estimated revenue saved: $115K.

What the analyst learned: - Cohort analysis is the right starting point for any churn or retention investigation. - Churn in month M is often caused by something that happened months earlier. - Data without action is just information. The $115K saved came from routing findings to the CS team.

Case Study 3: Retail — The Inventory Overstock¶

The Situation¶

You are an analyst at a mid-size retail chain. It is October. The merchandising team is looking at a warehouse full of summer apparel that did not sell — $2.1M in inventory that will have to be marked down 60–70% to move. The VP of Merchandising asks: "How do we make sure this never happens again?"

The Question¶

This is not a diagnostic question — the cause of this specific overstock is known (a warm April triggered aggressive buying that the cool summer did not justify). This is a prescriptive question: what process change prevents recurrence?

The analyst reframes: 1. (Diagnostic) "What specifically caused this overstock — was it a forecasting failure or a buying decision failure?" 2. (Predictive) "Can we build a demand forecast that would have given earlier signals?" 3. (Prescriptive) "What decision rules or process changes would reduce overstock risk?"

The Investigation¶

Step 1: Reconstruct the buying decision

The buyer placed a purchase order in February based on: - Prior year summer sales - A warm April weather pattern (anecdotal, not modeled) - A 20% increase in projected sales from the sales team's annual plan

The analyst pulls the actual February forecast vs what was bought:

import pandas as pd
import matplotlib.pyplot as plt

# Reconstruct the buy quantity vs forecast vs actual sales
data = {
    "sku": ["SKU-4421", "SKU-4422", "SKU-4423", "SKU-4424"],
    "category": ["Swimwear", "Swimwear", "Summer Tops", "Shorts"],
    "py_sales_units": [1400, 890, 2200, 1800],
    "forecast_units": [1680, 1068, 2640, 2160],  # +20% plan uplift
    "units_bought": [2100, 1400, 3200, 2600],     # buyer added additional buffer
    "units_sold": [1050, 620, 1890, 1540],        # actual
}
df = pd.DataFrame(data)

df["overstock_units"] = df["units_bought"] - df["units_sold"]
df["overstock_pct"] = df["overstock_units"] / df["units_bought"] * 100
df["forecast_error"] = (df["forecast_units"] - df["units_sold"]) / df["units_sold"] * 100

print(df[["sku", "category", "units_bought", "units_sold",
          "overstock_units", "overstock_pct", "forecast_error"]])

Result: The buyers purchased 25–50% above even the already-optimistic forecast. The overstock problem has two layers: (1) the forecast was too high, and (2) buyers added discretionary buffer on top.

Step 2: Was there early warning data that was available but not acted on?

-- Weekly sell-through rate by week (units sold / units available)
SELECT
    week_number,
    sku,
    units_sold,
    units_on_hand,
    ROUND(units_sold / NULLIF(units_on_hand, 0) * 100, 1) AS sell_through_rate,
    -- Flag if sell-through is below the pace needed to clear inventory by season end
    CASE WHEN (units_sold / NULLIF(units_on_hand, 0)) <
              (1.0 / (16 - week_number))  -- weeks remaining in season
         THEN 'Below Pace'
         ELSE 'On Pace' END AS inventory_pace_flag
FROM weekly_inventory
WHERE season = 'Summer 2024'
  AND category = 'Summer Apparel'
ORDER BY week_number, sku;

Finding: By week 6 of the season (early June), 78% of SKUs were already flagged as "Below Pace." No automated alert existed. The merchandising team did not notice until mid-July — two months later.

The Finding¶

"The overstock has two root causes. First, the demand forecast was +20% above prior year based on plan assumption rather than leading indicators. Second, buyers added 25–50% additional buffer above the forecast. Together, the buy was 50–80% above what the market supported.

Critically, sell-through data as early as week 6 showed the inventory was not pacing to clear — but no alert mechanism existed. By the time merchandising took action in mid-July, the markdown window was too short to clear inventory at reasonable margins."

The Recommendation¶

Short-term (this season): - Implement a weekly automated sell-through pace report with traffic-light alerts (red = below pace, yellow = at risk, green = on pace) - Run a markdown simulation: what price point clears inventory by October 31 while preserving maximum margin?

# Markdown simulation: find the price that clears inventory by season end
def simulate_clearance(units_remaining, weeks_remaining, price_elasticity=-1.8):
    """
    Simple clearance price simulation.
    Finds the markdown % that achieves sell-through by season end.
    """
    baseline_weekly_demand = units_remaining / (weeks_remaining * 2)  # conservative: need 2x pace

    results = []
    for markdown_pct in range(0, 75, 5):
        # Price elasticity: % change in demand / % change in price
        demand_multiplier = 1 + (price_elasticity * (-markdown_pct / 100))
        weekly_demand = baseline_weekly_demand * demand_multiplier
        total_demand = weekly_demand * weeks_remaining
        results.append({
            "markdown_pct": markdown_pct,
            "projected_units_sold": round(total_demand),
            "clears_inventory": total_demand >= units_remaining
        })

    return pd.DataFrame(results)

print(simulate_clearance(units_remaining=42000, weeks_remaining=8))

Long-term: - Build a demand forecast that incorporates weather forecasts, competitor pricing, and search trend data (not just prior year + plan) - Implement a buyer approval workflow: any buy > 110% of statistical forecast requires VP sign-off with documented rationale - Create a post-season debrief template: for every overstock >$100K, document what signals were available and when, and whether they were visible

The Outcome¶

The markdown simulation identified a 40% discount as the clearing price. The merchandising team ran the markdown in early October. They cleared 87% of inventory by October 31 (vs the 100% they needed, but better than the 45% they were projected to clear without action).

The sell-through pace dashboard was built and went live for the Fall 2024 season. In week 4 of Fall, it flagged two outerwear SKUs as below pace — three months earlier than the equivalent signal in Summer. The team re-allocated floor space and pulled forward a modest markdown. Overstock at season end was 12% vs 38% for the Summer season.

What the analyst learned: - The most valuable analytics is often the alert that triggers action, not the post-mortem that explains what went wrong. - A well-timed, simple dashboard prevented more loss than a perfect attribution model after the fact. - Quantify the cost of acting vs not acting. "$2.1M overstock" is what got leadership's attention. "$300K in incremental margin loss from delayed action" is what got the dashboard prioritized.

Pattern Recognition Across All Three Cases¶

Look at what the three investigations have in common:

Segmentation was the key move — the aggregate number was misleading. Device type, signup cohort, and SKU-level detail revealed the real story.
Timing was always informative — when the problem started pointed to the cause.
The finding was specific — not "mobile conversion dropped" but "the checkout button is hidden below the fold on screens under 768px."
The recommendation was actionable — something specific someone could do by a specific date.
The analyst stayed involved after delivery — the $115K saved in Case 2 and the Fall 2024 improvement in Case 3 happened because the analyst followed through.

The pattern every case shares

Aggregate → Segment → Hypothesize → Test → Quantify → Recommend → Follow up. This is the diagnostic analytics loop. Internalize it. Apply it to every investigation.

Previous: 04-analytics-workflow | Next: 06-practice-exercises