Product Analytics and Metrics

Mark module complete Expand all

7.1 Product analytics fundamentals

Product analytics is the practice of measuring how people use a product so that teams can find problems, size opportunities, test changes and verify outcomes.

Every useful metric needs five parts defined — otherwise two people will calculate it differently and argue:

7.2 Choosing KPIs

A KPI (key performance indicator) is a metric chosen to assess progress toward an important goal. Not every metric is a KPI.

7.2.1 The North Star Metric and metric trees

A North Star Metric (NSM) is a single measure of the recurring value users get from the product that also supports sustainable business results. It focuses teams; it never replaces guardrails.

How to choose an NSM — a checklist

1. Does it reflect value delivered to the user (not just value captured by the company)?
2. Is it tied to a repeatable behaviour (not a one-off)?
3. Is it sensitive enough that a good team can move it in a quarter?
4. Can it be measured reliably today?
5. Does it predict long-term business health (retention, revenue)?
6. Can you name two guardrails that stop gaming it?

7.3 AARRR (Pirate Metrics)

AARRR examines the path from discovering a product to recommending it: Acquisition → Activation → Retention → Revenue → Referral.

7.3.1 Acquisition and activation

• Acquisition metrics: visits, sign-ups, installs, cost per acquisition by channel, share of traffic by channel. Always compare channels on quality (activation and retention), not just volume.
• Activation: pick an event that correlates with long-term retention and represents real value. Test it by comparing retention of users who did vs did not reach it — remembering correlation is not causation (see below).
• Time-to-value: how long from sign-up to the activation event. Shortening it usually raises activation.

7.4 Funnel analysis and drop-off

A funnel counts how many distinct users (or sessions) get through an ordered series of steps. Step conversion compares each step with the previous one; overall conversion compares with the top.

Funnel pitfalls

• Sessions vs users: counting sessions can inflate steps for repeat visitors. State which one you use.
• Strict vs loose order: must a user hit steps in order, within X minutes?
• Time window: a user who adds to cart today and buys tomorrow — is that a conversion?
• Segmenting is essential: an overall funnel can hide that mobile-web users convert at half the rate of app users.

7.5 Engagement: DAU, WAU, MAU and stickiness

“Active” must mean a meaningful action (e.g., viewed a menu or placed an order), not merely “opened the app”.

7.6 Retention, cohorts and churn

Retention measures how many users in a defined cohort are still active in a later window. A cohort groups users by a shared starting event (e.g., signup week).

7.6.1 Reading a cohort heatmap

The heatmap below uses the synthetic dataset cohort_retention_weekly.csv: each row is a signup-week cohort; each column is the share of that cohort that placed a delivered order k weeks after signup.

How to read it: (1) Across a row — the shape of decay and whether it flattens. (2) Down a column — are newer cohorts better after a product change? (3) Diagonals show calendar effects (holiday, outage). Small cohorts produce noisy cells.

7.7 Monetisation metrics: GMV, AOV, ARPU, ARPPU

7.7.1 Decomposing AOV (the quick-commerce case)

To raise AOV, a team can raise items per order (relevant add-on suggestions, bundles, free-delivery thresholds) or price per item (assortment, premium options). The course case compared a quick-commerce AOV with a competitor's.

7.8 Unit economics: contribution margin, CAC, LTV, payback

Unit economics looks at the money earned and spent for one unit — an order, a customer, a subscription.

7.9 Experiment design and A/B testing

An A/B test randomly assigns eligible users to control (current experience) and variant (change), then compares a pre-agreed metric. Random assignment is what makes the groups comparable, so differences can be attributed to the change.

When not to A/B test: too little traffic; irreversible or safety/legal changes; strong network effects (a marketplace treatment can contaminate control — consider geo or switchback tests); changes whose value only appears over months.

7.10 Hypothesis testing, p-values and confidence intervals

7.10.1 The idea

• Null hypothesis (H₀): there is no real difference between control and variant.
• Alternative (H₁): there is a difference.
• p-value: if H₀ were true, how often would you see a difference at least this large purely by chance? It is not the probability that the variant works, and it does not measure the size or importance of the effect.
• Significance level α: the threshold you fix in advance (commonly 0.05). If p < α, you reject H₀.
• Confidence interval (CI): a range of effect sizes consistent with the data; a 95% CI is produced by a procedure that captures the true effect in 95% of repeated experiments. It tells you how big the effect might be.

7.10.2 Type I and Type II errors, and power

• Power is the probability of detecting a real effect of a given size. Conventional target: 80%.
• MDE (minimum detectable effect) is the smallest effect worth detecting; smaller MDE → much bigger sample.

7.10.3 Sample size

7.10.4 Practical rules for trustworthy experiments

• Fix the sample size or use a sequential method designed for peeking. Stopping the moment p < 0.05 inflates false positives (the “peeking problem”).
• Run full weekly cycles to cover weekday/weekend behaviour.
• Check sample ratio mismatch (SRM): if you planned 50/50 and observed 52/48 with large counts, assignment may be broken and results untrustworthy.
• Pre-register one primary metric; treat others as secondary. Testing many metrics or segments without correction creates false positives (multiple comparisons).
• Separate statistical from practical significance. A tiny, “significant” lift may not justify the cost; a larger, “non-significant” one may merit a bigger test.
• Novelty effects: an early lift may fade. Where stakes are high, hold out a group longer.

7.11 Correlation versus causation

Two things moving together does not mean one causes the other. Causes of misleading correlation: confounding (a third factor drives both), reverse causation, selection bias, and Simpson's paradox.

7.12 Segmentation in analytics

Always ask “for whom?” Useful cuts: acquisition channel, platform/app version, geography, new vs returning, plan/tier, cohort, device class, behavioural segments (power users vs casual). Use the synthetic data to see it:

7.13 Event instrumentation and data quality

Instrumentation is adding tracking so product behaviour becomes data. Do it from a tracking plan written before development.

A well-formed event (illustrative):

{
  "event": "checkout_started",
  "timestamp": "2026-03-14T18:22:05+05:30",
  "anonymous_id": "a1b2-c3d4",
  "properties": { "cart_value": 520, "item_count": 3, "platform": "ios" }
}

Data quality checklist

• Naming convention (object_action, snake_case) and a single owner of the schema.
• Test in staging before release; compare dashboard totals with the authoritative system (orders table).
• Deduplicate (retries, double fires) and handle bots/internal traffic.
• Time zones and clock skew: define the reporting zone.
• Late and lost events: mobile events batch and can arrive late; define a lag window.
• Privacy: minimise personal data; never put card details, emails or phone numbers into analytics events; honour consent and retention rules.

7.14 SQL for Product Managers

Many PM interviews and day-to-day questions need basic SQL. These queries run against the synthetic dataset (users, events, orders). They use portable SQL; date functions differ by database (SQLite uses strftime, PostgreSQL uses DATE_TRUNC, BigQuery uses DATE_TRUNC / FORMAT_DATE).

1 · Basics: GMV, AOV and counts (delivered orders only)

SELECT COUNT(*)                 AS delivered_orders,
       SUM(amount_inr)          AS gmv_inr,
       ROUND(AVG(amount_inr),2) AS aov_inr
FROM orders
WHERE status = 'delivered';

Result: 320 | 122874 | 383.98. (GMV here excludes cancelled orders — a definition you must state.)

2 · Cancellation rate

SELECT COUNT(*) AS orders,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
       ROUND(100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*), 2) AS cancel_pct
FROM orders;

Result: 334 | 14 | 4.19.

3 · Monthly active buyers and GMV (GROUP BY)

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(DISTINCT user_id)       AS active_buyers,
       SUM(amount_inr)               AS gmv_inr
FROM orders
WHERE status = 'delivered'
GROUP BY month
ORDER BY month;

Result: 2026-01 | 55 | 23128, 2026-02 | 107 | 45400, 2026-03 | 136 | 54346.

4 · Funnel by distinct users

SELECT event_name, COUNT(DISTINCT user_id) AS users
FROM events
GROUP BY event_name
ORDER BY users DESC;

Result: app_open 792 → view_menu 716 → add_to_cart 423 → checkout_start 322 → payment_success 284. Step conversions: 716/792 = 90.4%, 423/716 = 59.1%, 322/423 = 76.1%, 284/322 = 88.2%. This counts users who ever reached a step, so it is not a strict ordered funnel.

5 · JOIN: which acquisition channels produce buyers?

SELECT u.acquisition_channel,
       COUNT(DISTINCT u.user_id) AS users,
       COUNT(DISTINCT o.user_id) AS buyers,
       ROUND(100.0 * COUNT(DISTINCT o.user_id) / COUNT(DISTINCT u.user_id), 1) AS buyer_pct
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id AND o.status = 'delivered'
GROUP BY u.acquisition_channel
ORDER BY buyer_pct DESC;

Result: organic 370/142 (38.4%), search_ads 130/49 (37.7%), referral 106/32 (30.2%), paid_social 194/51 (26.3%). A LEFT JOIN keeps users with no orders; an INNER JOIN would silently drop them and overstate conversion.

6 · Repeat purchase (subquery)

SELECT COUNT(*) AS buyers,
       SUM(CASE WHEN n >= 2 THEN 1 ELSE 0 END) AS repeat_buyers,
       ROUND(100.0 * SUM(CASE WHEN n >= 2 THEN 1 ELSE 0 END) / COUNT(*), 1) AS repeat_pct
FROM (SELECT user_id, COUNT(*) AS n
      FROM orders WHERE status = 'delivered' GROUP BY user_id);

Result: 274 | 44 | 16.1.

7 · Window function: rank customers by spend

SELECT user_id, SUM(amount_inr) AS spend,
       RANK() OVER (ORDER BY SUM(amount_inr) DESC) AS rnk
FROM orders WHERE status = 'delivered'
GROUP BY user_id
ORDER BY rnk
LIMIT 5;

Result: U0648 (1,229), U0483 (1,210), U0246 (1,160), U0665 (1,040), U0751 (1,039).

SQL habits PMs should build: state the metric definition in a comment; check row counts before and after JOINs (duplicates inflate sums); filter to the right status; be explicit about time zones; never SELECT * on personal data; sanity-check totals against an authoritative dashboard.

7.15 Excel for product analytics

Excel is enough for quick funnels, cohorts and A/B maths.

Build a tracking sheet per experiment: hypothesis, dates, n, conversions, z, p, CI, guardrails, decision. It doubles as a decision log.

7.16 Dashboards and tools: Power BI, Mixpanel, Amplitude, GA4

A simple Power BI measure (DAX), to show the idea:

Conversion Rate = DIVIDE( DISTINCTCOUNT(Orders[user_id]), DISTINCTCOUNT(CheckoutStarts[user_id]) )

Dashboard design rules: start with the decision; one headline metric per question; show the trend and the target; include definitions and “last refreshed”; put guardrails next to the metric they protect; avoid pie charts for more than 3–4 parts; annotate releases and incidents.

7.17 Experiment readout template (worked example)

7.18 Source-flagged corrections

• The lecture's 20–30% “ideal” activation and referral claims are not universal benchmarks.
• A single page-load time cannot by itself establish API latency; browser rendering, network and other components also contribute.
• A preference poll is not an A/B test; confidence level is not the probability a variant will work.
• A confidence interval is not a probability statement about a single experiment's true value (frequentist interpretation).
• GMV ≠ revenue ≠ profit. Define each.

7.19 Exercises with model answers

7.20 Interview perspective

7.21 Key takeaways

• A metric is only as good as its definition (event, numerator, denominator, window, exclusions).
• Use leading + lagging metrics, an NSM with a metric tree, and guardrails.
• Funnels, cohorts and segmentation show where things change; research and experiments show why and whether it works.
• GMV ≠ revenue ≠ profit. Check contribution margin and CAC payback before celebrating growth.
• Experiments need pre-registered metrics, adequate power, clean randomisation and guardrails. p-values are not the probability a variant works; CIs show effect size.
• Correlation is not causation — Simpson's paradox is the standard warning.
• Instrument from a tracking plan, validate data quality, and keep personal data out of analytics events.

7.22 Check your understanding

7.23 Recommended resources

• Google Analytics Help — events, key events, privacy.
• Mixpanel Docs · Amplitude Docs — product analytics concepts and setup.
• Power BI documentation — dashboards and DAX.
• Evan Miller: A/B testing tools and sample-size calculator — practical references for sample size and sequential testing.
• Kohavi, Tang, Xu — Trustworthy Online Controlled Experiments — the standard book on online experimentation.
• SQLBolt — free interactive SQL lessons.