Reference

Analytics Method Library: Common Recipes

Practical, repeatable analysis recipes for common business learning questions — churn drivers, funnel leaks, feature impact, cohort lifetime comparisons, attribution diagnostics, and holdout/experiment patterns. Each recipe includes required inputs, a stepwise procedure, standard SQL pseudocode, validation checks, common confounders, and suggested visualizations to make results reproducible and comparable across teams.

Analytics Method Library — Common Recipes

This reference collects short, reproducible analysis recipes you can apply, adapt, and document so results are comparable across teams and projects. Each recipe is intentionally practical: required data inputs, a stepwise method, standard SQL pseudocode (illustrative), validation checks, typical confounders to consider, and suggested visualizations. Use these as starting templates — always adapt metric definitions and time windows to your business context and register experiments or analyses so others can reproduce your work.

How to use a recipe: pick the recipe that matches your learning question; confirm you have the required inputs; run lightweight validation checks; record assumptions (cohort definitions, measurement windows, exclusions); and publish findings with suggested visualizations and an explicit recommended next action (experiment, fix, monitor).

Recipe: Churn Drivers (why customers leave)

Purpose: Identify behavioral segments, product signals, or events associated with elevated churn risk so you can target interventions or tests.

Required inputs (minimum):

  • User/customer identifier
  • Observation period and churn definition (e.g., 60 days without activity or subscription cancellation date)
  • Event or feature usage logs (time-stamped)
  • Key attributes: signup date, plan type, cohort, acquisition channel

Stepwise recipe:

  1. Define churn precisely for your product (binary label with measurement window).
  2. Create a cohort of users active at baseline (e.g., users with at least one event in month 0).
  3. Aggregate predictors in a pre-churn window (counts, recency, frequency, last event type).
  4. Estimate simple risk differences and odds ratios by predictor, then use a multivariate model (logistic regression or survival analysis) to control for confounders such as signup cohort and plan.
  5. Validate model stability across cohorts and time slices; report effect sizes with confidence intervals and sample sizes.
SQL (pseudocode):
-- build features SELECT user_id, signup_date, plan, COUNT(CASE WHEN event_time BETWEEN t0 AND t1 THEN 1 END) AS events_30d, MAX(event_time) AS last_event, CASE WHEN last_event <= churn_cutoff THEN 1 ELSE 0 END AS churn_label FROM events WHERE event_time <= analysis_date GROUP BY user_id

Validation checks:

  • Confirm churn label prevalence and cohort sizes by segment.
  • Check feature missingness and extreme values.
  • Run the model on two disjoint time slices to test stability.

Typical confounders:

  • Different onboarding experiences by acquisition channel.
  • Plan or contract expirations aligning with churn windows.
  • Seasonal effects or product outages.

Suggested visualizations:

  • Bar chart of churn rate by segment (with sample size).
  • SHAP or coefficient plot from a multivariate model to show effect sizes.
  • Survival curve by high/low-risk segments.

Recipe: Funnel Leak / Conversion Drop Analysis

Purpose: Find where users fall out of a multi-step funnel and quantify the relative contribution of each step to overall conversion loss.

Required inputs:

  • Event stream with standardized event names for each funnel step
  • User/session identifiers and timestamps
  • Session attribution (optional but helpful: channel, campaign)

Stepwise recipe:

  1. Define clear ordered funnel steps and the observation window for step completion.
  2. For each user (or session), mark whether each step was completed in order within the window.
  3. Compute step-to-step conversion rates and overall funnel conversion with counts and confidence intervals.
  4. Segment by important attributes (device, channel, cohort) to reveal differing leak points.
  5. Where relevant, inspect session recordings/heatmaps or instrument A/B tests at suspected leak points.
SQL (pseudocode):
WITH steps AS ( SELECT user_id, MAX(CASE WHEN event = 'step_1' THEN 1 ELSE 0 END) AS s1, MAX(CASE WHEN event = 'step_2' THEN 1 ELSE 0 END) AS s2, MAX(CASE WHEN event = 'step_3' THEN 1 ELSE 0 END) AS s3 FROM events WHERE event_time BETWEEN start_date AND end_date GROUP BY user_id ) SELECT SUM(s1) AS step1_count, SUM(s2) AS step2_count, SUM(s3) AS step3_count FROM steps;

Validation checks:

  • Confirm event naming and deduplicate bot traffic.
  • Check for instrumentation gaps (sudden drops in event counts).

Typical confounders:

  • Different user intent across channels (e.g., organic vs. paid).
  • Sessionization rules that merge or split user journeys incorrectly.

Suggested visualizations:

  • Funnel chart with step counts and step-to-step conversion percentages.
  • Segmented stacked bar charts to show where specific cohorts drop.

Recipe: Feature Impact — A/B (randomized) analysis

Purpose: Measure causal impact of a product change using randomized assignment (A/B test) and guardrails for reliable inference.

Required inputs:

  • Randomized assignment logs (treatment/control flags tied to user or session id)
  • Pre-specified primary and secondary metrics with calculation SQL
  • Sample size / power calculations or minimum detectable effect (MDE)
  • Experiment start/end dates and eligibility criteria

Stepwise recipe:

  1. Define hypotheses, primary metric, and MDE before peeking at results.
  2. Confirm randomization integrity: balance checks on key covariates between groups.
  3. Preprocess outcomes according to pre-specified rules (censoring, winsorizing) and apply the same logic to both groups.
  4. Compute treatment effect with confidence intervals using difference-in-means, and consider regression adjustment to improve precision.
  5. Run sequential testing only if an appropriate stopping rule was pre-registered; otherwise use final fixed-horizon analysis.
SQL (pseudocode):
SELECT assignment, AVG(primary_metric) AS mean_metric, COUNT(*) AS n FROM experiment_results WHERE experiment_id = 'exp_123' AND date BETWEEN start_date AND end_date GROUP BY assignment;

Validation checks:

  • Balance table for covariates (age, country, cohort).
  • Check for logging gaps and differential attrition.

Typical confounders:

  • Non-compliance or cross-over between groups.
  • Interference between users (network effects).
  • Multiple overlapping experiments altering the same metric.

Suggested visualizations:

  • Mean metric with 95% CI by assignment.
  • Cumulative lift plot over time to inspect stability.

Recipe: Cohort Lifetime Comparison (retention & LTV)

Purpose: Compare lifetime behavior across cohorts (signup week, acquisition channel) to see how retention and revenue evolve.

Required inputs:

  • User signup date or cohort key
  • Standardized events indicating activity or revenue attribution
  • Consistent time buckets (days/weeks/months since cohort)

Stepwise recipe:

  1. Create cohort table with cohort_date and user_id.
  2. For each cohort, compute retention or cumulative revenue in fixed time buckets (e.g., week 0..52).
  3. Normalize by cohort size and report both absolute and relative retention.
  4. Compare cohorts visually and test differences at key horizons (e.g., 30/90/365 days).
SQL (pseudocode):
SELECT cohort_week, weeks_since_signup, COUNT(DISTINCT CASE WHEN activity_date BETWEEN signup_date AND signup_date + interval '7 days' * weeks_since_signup THEN user_id END) / cohort_size AS retention_rate FROM cohort_activity GROUP BY cohort_week, weeks_since_signup;

Validation checks:

  • Ensure cohort assignment is based on first valid event and not later corrected values.
  • Flag cohorts with small sample sizes before comparing.

Typical confounders:

  • Changes in onboarding or product packaging over time.
  • Data backfills or delayed event ingestion.

Suggested visualizations:

  • Retention heatmap (cohort vs. weeks) with color scale showing rates.
  • Cumulative revenue per user curve by cohort with confidence bands.

Recipe: Attribution & Diagnostic (why a metric moved)

Purpose: When a KPI changes, decompose the drivers (volume, mix, price, conversion) to find the dominant causes.

Required inputs:

  • Time-series of KPI and underlying components (users, conversion rates, avg revenue per user)
  • Segmentation dimensions (channel, cohort, geography)

Stepwise recipe:

  1. Break KPI into multiplicative components (example: revenue = users * conversion * AOV).
  2. Compute percentage change in each component for the period of interest.
  3. Allocate the overall KPI change to components using a decomposition method (additive or multiplicative log decomposition).
  4. Drill into the largest components by segment to find concentrated causes.
SQL (pseudocode):
-- compute components by channel SELECT date, channel, COUNT(DISTINCT user_id) AS users, SUM(conversions)/COUNT(DISTINCT user_id) AS conv_rate, SUM(revenue)/SUM(conversions) AS avg_order_value FROM events GROUP BY date, channel;

Validation checks:

  • Check for outlier days (campaign launches, outages) that skew comparisons.
  • Verify numerator and denominator alignment (same user definition across components).

Typical confounders:

  • Attribution window changes or delayed conversions.
  • Large one-off transactions or refunds.

Suggested visualizations:

  • Waterfall chart decomposing KPI change into component effects.
  • Small-multiples of components by channel to spot concentrated trends.

Recipe: Holdout / Observational Causal Checks (when randomization isn't available)

Purpose: Use quasi-experimental patterns — time-based holdouts, matched cohorts, or instrumented variation — to gain causal insight when randomization is infeasible.

Required inputs:

  • Clear treatment definition and timing
  • Pre-treatment covariates and outcome time series
  • Potential instruments or natural experiments (if available)

Stepwise recipe:

  1. Start with simple before/after comparisons and then add a comparison group if possible (difference-in-differences).
  2. Consider matching on covariates or propensity score trimming to improve comparability.
  3. Check parallel trends pre-treatment for DiD suitability.
  4. Report sensitivity analyses: how large an unobserved confounder would need to be to overturn conclusions.
SQL (pseudocode):
-- aggregate outcomes by group and week SELECT week, group_label, AVG(outcome) AS mean_outcome FROM outcomes GROUP BY week, group_label;

Validation checks:

  • Test pre-treatment trend equivalence for groups.
  • Check robustness to alternative matching specifications and windows.

Typical confounders:

  • Time-varying confounders coinciding with treatment rollout.
  • Selection into treatment correlated with unobserved factors.

Suggested visualizations:

  • Time-series lines with pre- and post-treatment highlighted; include counterfactual from model.
  • Balance table heatmap for matched covariates.

Cross-recipe hygiene and reproducibility checklist

  • Always document metric definitions (SQL), cohort windows, and exclusions near the analysis output.
  • Publish sample sizes, mean and variance, and confidence intervals — not only p-values.
  • Record any data quality issues, backfills, or instrumentation changes that could affect interpretation.
  • Prefer pre-registration of experiments and pre-specification of primary metrics for A/B tests.
  • Encourage peer review or a lightweight replication check before acting on high-impact findings.

Next steps and recommended platform improvements

These recipes are designed to be portable and repeatable. To accelerate adoption across teams consider packaging them as interactive templates (parameterized SQL, pre-flight validation checks, and an experiment/analysis registry) and linking them to canonical metric definitions and dashboards.

Discuss Ask the Librarian

Discussion

Comments and conversation will live here.