Day 02 Part 2 — Business Analytics¶
Tools are nothing without frameworks. Business analytics is the discipline of applying structured methods — funnels, cohorts, segmentation, KPI trees — to turn data into decisions. This session covers the frameworks that separate an analyst who runs queries from one who drives strategy.
Learning Objectives¶
- Apply KPI frameworks and OKRs to measure what matters
- Build and interpret funnel analysis
- Perform cohort analysis for retention
- Implement RFM customer segmentation
- Connect analytics to business decisions
KPI Frameworks and OKRs¶
KPIs (Key Performance Indicators) measure performance against goals. OKRs (Objectives and Key Results) set ambitious goals and the measurable results that prove progress.
Objective: Become the most trusted marketplace in our category
Key Result 1: Increase NPS from 32 to 50
Key Result 2: Reduce average delivery time from 12 to 7 days
Key Result 3: Grow repeat purchase rate from 3% to 8%
A good KPI is SMART, actionable, and tied to a decision. (See KPI & Metrics for depth.)
Leading vs lagging indicators
Pair lagging indicators (revenue, churn — what happened) with leading indicators (pipeline, engagement, NPS — what's coming). Lagging tells you the score; leading lets you change it.
Funnel Analysis¶
A funnel tracks how users move through sequential stages, revealing where they drop off.
Stage Users Conversion Drop-off
Visit 10,000 100% —
Product View 6,200 62% 38%
Add to Cart 2,600 42% (of view) 58% ← biggest drop
Checkout 1,900 73% 27%
Purchase 1,500 79% 21%
Overall conversion: 15%
The analytical move: find the biggest drop-off (Product View → Add to Cart) and focus improvement there. A 5-point improvement at the widest leak beats optimising a stage that's already converting well.
-- Funnel in SQL using conditional counts
SELECT
COUNT(DISTINCT user_id) AS visited,
COUNT(DISTINCT CASE WHEN viewed_product THEN user_id END) AS viewed,
COUNT(DISTINCT CASE WHEN added_to_cart THEN user_id END) AS carted,
COUNT(DISTINCT CASE WHEN purchased THEN user_id END) AS purchased
FROM user_events
WHERE event_date >= '2024-01-01';
Cohort Analysis¶
A cohort is a group that shares a start point (e.g., everyone who signed up in January). Cohort analysis tracks how each group behaves over time — the clearest way to measure retention.
Acquisition Month 0 Month 1 Month 2 Month 3
Jan cohort 100% 62% 45% 38%
Feb cohort 100% 65% 48% —
Mar cohort 100% 58% — —
Reading it: each row is a cohort; each column is months since acquisition. A healthy product shows retention curves that flatten (a stable core of loyal users) rather than declining to zero. Improving cohorts over time (Feb retains better than Jan) signals product improvements are working.
RFM Segmentation¶
RFM scores customers on three behaviours to enable targeted action:
| Dimension | Question | Score |
|---|---|---|
| Recency | How recently did they buy? | 1-5 (recent = high) |
| Frequency | How often do they buy? | 1-5 (frequent = high) |
| Monetary | How much do they spend? | 1-5 (high spend = high) |
Resulting segments and actions:
| Segment | Profile | Action |
|---|---|---|
| Champions | High R, F, M | Reward, VIP treatment |
| Loyal | High F | Upsell, referral programmes |
| At Risk | Low R, was high F | Win-back campaign |
| New | High R, low F | Onboard, nurture to 2nd purchase |
| Lost | Low R, low F | Reactivation or let go |
(Full SQL implementation in Projects/E-commerce-Analytics/sql-analysis.)
Other Key Frameworks¶
Pareto (80/20): typically 80% of revenue comes from 20% of customers/products. Identify and protect the vital few.
Unit economics: does each customer/order make money after all costs? CAC vs CLV is the core ratio. (See Interview-Preparation/kpi-metrics.)
A/B testing: the gold standard for measuring whether a change actually works. (See Week-01/Day-05-Part-2-Statistics-for-Analytics/05-statistics-for-business.)
Practice Exercises¶
Warm-up 1. Define one Objective and three Key Results for an e-commerce business. 2. Given funnel counts (10000 → 6000 → 2500 → 1800 → 1400), calculate the conversion rate at each step and identify the biggest drop-off. 3. List the RFM segment and recommended action for a customer who: bought yesterday, has 15 lifetime orders, and spends £200/order.
Main 4. Build a funnel analysis in SQL from an events table. 5. Construct a cohort retention table from order data (signup month × months-since-signup). 6. Implement RFM scoring on a customer dataset and assign segments.
Interview Questions¶
[Beginner] What is a conversion funnel and what does it tell you?
[Mid-level] What is cohort analysis and why is it better than a single overall retention number?
[Mid-level] Explain RFM segmentation and give an example of an action for each segment.
[Senior] A funnel shows a 60% drop between "add to cart" and "checkout." Walk me through how you'd diagnose and address it.