Marketing and Growth Analytics Questions

Goal and hypothesis

• Goal: Reduce 30-day churn among new users by redesigning onboarding.
• Hypothesis (H1): The redesigned onboarding will reduce 30-day churn rate vs. control (current onboarding).
• Null (H0): No difference in 30-day churn.

Primary and secondary metrics

• Primary metric: 30-day churn rate (binary: churned within 30 days).
• Secondary metrics: 7-day activation rate, time-to-first-key-action, NPS at day 14.
• Guardrail metrics (safety): 7-day retention (to detect short-term harm), conversion to paid (if relevant), customer support contacts per user, completion rate of onboarding flow.

Unit of randomization

• User-level randomization at account/new-signup ID (first session) to avoid cross-contamination. Randomize at first-touch to assign persistent bucket.

Sample-size estimation approach

• Baseline churn (example): p1 = 40% (0.40). Target MDE: absolute 4 percentage points (p2 = 36%) → 10% relative reduction.
• Power 80%, alpha 0.05, two-sided. Using two-proportion formula:
  Zα/2=1.96, Zβ=0.84.
  n ≈ [(1.96*√(2p(1-p)) + 0.84*√(p1(1-p1)+p2(1-p2)))^2] / (p1−p2)^2
  With p≈0.40, delta=0.04 → n ≈ 2,336 per arm.
• Adjust for:
  • Expected attrition/invalids: +20% → ~2,800 per arm.
  • Multiple segments/subgroup analysis: inflate further or pre-specify stratified tests.
• If baseline churn is unknown, run short baseline measurement (1–2 weeks) to estimate p and re-calc.

Randomization and stratification

• Stratify randomization on important covariates (e.g., platform iOS/Android/web, geo, paid vs free cohort) to balance and reduce variance.
• Persist assignment in user profile to enforce consistent experience across devices/sessions.

Monitoring and stopping rules

• Pre-specify fixed-horizon test duration based on reaching required sample size; do NOT peek continuously.
• If interim looks necessary, pre-specify alpha spending (e.g., O'Brien–Fleming) with 1–2 interim analyses to control Type I error.
• Safety stopping rules (continuous monitoring allowed for harms):
  • Stop early for harm if a guardrail metric (e.g., 7-day retention or conversion) shows statistically significant degradation beyond a pre-specified threshold (e.g., absolute drop >3 pp with p < 0.01).
  • Stop for futility if conditional power < 20% at interim.
• Use ITT as primary analysis; run per-protocol and complier analyses as secondary.

Analysis plan and metrics calculation

• Primary analysis: compare 30-day churn proportion (two-sided z-test / logistic regression) adjusting for stratification covariates.
• Report absolute and relative lift, 95% CIs, and p-values.
• Pre-register subgroups and multiplicity correction (e.g., Holm-Bonferroni) for multiple secondary metrics.

Treatment of ineligible users and cross-exposure

• Ineligible users: define eligibility before randomization (e.g., internal staff, bots). Exclude them from randomization pipeline. If discovered post-randomization, keep them in ITT analysis but flag and run sensitivity excluding them.
• Users exposed to other experiments:
  • Prefer to prevent overlap by excluding users in conflicting experiments or use orthogonal/factorial designs when possible.
  • If overlap occurs, log exposures and include experiment-exposure indicators in regression to adjust for contamination; run interaction tests.
  • If a high proportion of overlap exists, mark as contamination and consider pausing or redesigning assignment.

Operational considerations (BI role)

• Implement an experiment tracking table: user_id, assignment, timestamp, exposures to other experiments, eligibility flags, and key metric events.
• Build dashboards to monitor metric time series, daily sample counts, balance diagnostics, and guardrail alerts.
• Automate alerts for sample-size achieved, imbalance, or guardrail breaches.
• Document pre-analysis plan and share with stakeholders before launch.

Expected outputs and decision criteria

• If primary metric shows statistically significant reduction in 30-day churn with no meaningful negative impact on guardrails, rollout the redesign.
• If no significant effect or harms observed, iterate on design or revert to control and run follow-up tests.

Immediate metrics I'd check

Leading indicators (early signal of change)

• Impression share, CPM, CTR by channel (shows reach and ad quality)
• Clicks, click-to-site conversion rate, post-click engagement (bounce rate, pages/session)
• Cost per click (CPC) and cost per landing-page visit
• Search/impression trends for branded queries (awareness lift)
• Spend pacing vs. plan (to ensure changes actually deployed)

Lagging indicators (business impact)

• Cost per acquisition (CPA) and conversion volume by channel
• Return on ad spend (ROAS) and incremental revenue
• Lifetime value (LTV) of cohorts exposed vs. control
• Channel contribution to overall revenue, retention, and repeat purchase rate

Time windows to analyze

• 0–7 days: immediate delivery and technical issues (ads serving, tracking)
• 8–30 days: early user behavior and conversion-rate shifts
• 31–60 days: short-term revenue impact and cohort-level effects
• Compare to same-period prior weeks and seasonally adjusted baselines; use a 28-day rolling view and week-over-week and week-over-year comparisons

How I'd analyze to reduce noise

• Segment by campaign, creative, audience, and device
• Use control groups or holdout campaigns if available; otherwise use synthetic control / matched cohorts
• Check data-quality (tagging, attribution windows, duplicate conversions)

How I'd communicate preliminary findings

• Present top-line bullets: what changed, early positive/negative signals, and data confidence level
• Show key charts: spend vs. impressions, CTR/CVR trends, CPA/ROAS over time, and cohort revenue curves
• State caveats explicitly: two months is short for definitive LTV/retention conclusions; attribution lag and seasonality can bias results; small-sample campaigns have high variance
• Recommend next steps: continue monitoring for another full conversion cycle (60–90 days), set up holdout if not present, and run A/B tests where possible

This gives the leader an evidence-based snapshot, clear uncertainty bounds, and actionable next steps.

Requirements:

• Functional: alert on material drops or spikes in revenue, DAU, conversion rate; distinct alerts for data-quality vs product issues.
• Non-functional: <15 min detection-to-notification, <1 false positive/week for exec alerts, auditable escalation.

High-level architecture:

• Data sources → ETL/warehouse → metrics store (pre-aggregates) → anomaly detection & thresholding service → alerting/notification system → incident management (PagerDuty/Slack/Jira).

Choosing thresholds:

• Static thresholds for business rules (e.g., revenue < $X/day) used for contractual/financial guardrails.
• Dynamic baselines for behavioral KPIs (DAU, conversion): rolling-window seasonality-aware models (7/28-day, day-of-week adjustment) or exponential smoothing. Use hybrid: require deviation > max(static_min, z-score threshold) AND absolute delta percent to avoid alerting on tiny volumes.

Alert routing & escalation:

• Triage tiers:
  • Tier 0 (automated recovery/data pipeline): notifications to infra/ETL team via Ops channel; auto-retry jobs.
  • Tier 1 (BI analyst on-call): investigates source-of-truth, data freshness, schema changes; Slack + PagerDuty.
  • Tier 2 (Product/Revenue owners): engaged if business impact confirmed.
• Escalation timeline: 0–15 min initial page to on-call; 15–45 min escalate to team lead; 45+ min page product owner and execs if impact high.

On-call responsibilities (BI Analyst):

• Validate alert (runbook), identify cause (data vs product), document findings in incident ticket, perform or request rollbacks/fixes, update dashboards and stakeholders, close with RCA.

Runbook (triage steps):

1. Confirm alert details (metric, window, baseline, run id).
2. Check data pipeline: job status, last ingestion timestamp, row counts (compare to previous period).
3. Check upstream anomalies: source event logs, schema diffs.
4. Recompute metric on raw table for sanity check.
5. If data-quality: roll back/stop downstream consumers, trigger ETL re-run, notify infra.
6. If product: correlate with releases, feature flags, external events; notify product/marketing.
7. Document actions, root cause, mitigation, and postmortem.

Noise reduction techniques:

• Require multi-window confirmation (e.g., anomaly persists for 2 consecutive buckets).
• Use anomaly scoring: combine z-score, seasonality residual, and business-impact weight; surface only alerts above composite threshold.
• Deduplicate correlated alerts (group by root-cause hints: same job, same source).
• Suppression during planned events (deploy windows) and maintain maintenance calendar.
• Adaptive sensitivity: higher sensitivity for high-impact metrics, lower for exploratory ones.

KPIs to monitor reporting pipeline:

• Data freshness lag (max and p95)
• Ingest row counts vs expected (delta %)
• Job success rate and runtime
• Metric computation latency
• Alert volume and false positive rate
• Time-to-acknowledge and time-to-resolve incidents
  Track these as dashboards and include SLOs (e.g., 99% jobs succeed daily, freshness < 15 min).

Closing notes:

• Start simple (static + basic rolling baseline), iterate models as you gather alert feedback.
• Maintain clear playbooks and run regular on-call drills and postmortems to tune thresholds and reduce noise.

Churn rate

• Definition & formula: Churn Rate = (Number of customers lost during period) / (Customers at start of period).
• Example: Start 1,000 accounts, lost 50 → Churn = 50/1000 = 5%.
• Use cases: short-term health signal, cohort comparisons, alerting.
• Pitfalls to document: exclude accounts that churned but reactivated; decide treatment of downgrades (do you call downgrade “churn” for product-level churn?); partial-period customers — measure on cohort (e.g., first full month only); handle currency by computing churn in account counts (currency irrelevant) or if revenue churn, normalize to base currency and FX rate date.

Retention rate

• Formula: Retention Rate = (Customers at end of period who were present at start) / (Customers at start).
• Example: 1,000 start, 930 stayed → Retention = 93%.
• Use cases: cohort lifetime analysis, product stickiness, customer success KPIs.
• Pitfalls: define time window (monthly vs. annual); treat upgrades as retained (usually yes); partial-period customers — use cohort aligned to acquisition period.

Annual Recurring Revenue (ARR)

• Formula: ARR = sum(annualized recurring revenue per account) = Σ (MRR_account × 12) or direct annual contract values.
• Example: 100 accounts with $100 MRR → ARR = 100×(100×12) = $120,000.
• Use cases: revenue run-rate, forecasting, valuation metrics.
• Pitfalls: exclude one-time fees; handle mid-period upgrades/downgrades by pro-rating; convert non-USD contracts using consistent FX (document rate and date); decide whether to include churned accounts’ partial contributions (pro-rate or only active-months).

Net Revenue Retention (NRR)

• Formula: NRR = (Starting ARR from cohort + expansions − contractions − churn within period) / Starting ARR.
• Example: Starting ARR $1,000,000; expansions +$150k; contractions −$50k; churn −$100k → NRR = (1,000+150−50−100)/1,000 = 1.00 = 100%.
• Use cases: measures ability to grow revenue from existing base, investor metric.
• Pitfalls: clearly define cohort (start-of-period ARR), treat upgrades/downgrades precisely (expansions vs contractions), exclude new logo ARR, pro-rate intraperiod changes, apply consistent FX and billing normalization.

Customer Lifetime Value (CLV)

• Formula (simple): CLV = ARPA × Gross Margin % × (1 / Customer Churn Rate) — or Sum of discounted future gross profit per account.
• Example: ARPA $1,200/year, gross margin 70%, annual churn 20% → CLV = 1,200×0.7×(1/0.2)= $4,200.
• Use cases: customer acquisition budgeting, segmentation, LTV:CAC decisions.
• Pitfalls: churn instability → use cohort churn; treat upgrades/downgrades by modeling ARPA growth; partial-period customers need proration; include FX normalization for multi-currency customers; choose discount rate if using NPV method.

General documentation recommendations to share with executives

• State precise definitions, cohort windows, and whether metrics are account- or seat-based.
• Explain treatment of upgrades/downgrades (pro-rate and classify as expansion/contraction), partial-period billing (prorate or align to full-period cohorts), cancellations with refunds, and FX policy (reporting currency and FX rate date or use constant currency).
• Provide examples and link to SQL/Looker definitions used so metrics are reproducible. Include sensitivity notes (how metric changes if you alter churn definition).

Approach (overview): RFM ranks customers by how recently they purchased (Recency), how often (Frequency), and how much they spend (Monetary) to prioritize outreach. Use precise, reproducible definitions, score each dimension, validate with holdout tests, then map segments to targeted actions.

Precise calculations:

• Recency: days_since_last_order = reference_date − MAX(order_date) per customer. Lower = better.
• Frequency: count_orders = COUNT(order_id) in the observation window (e.g., last 12 months).
• Monetary: revenue = SUM(order_value) in the same window (use gross or net as business prefers). Optionally use avg_order_value = revenue / count_orders to separate big-ticket buyers.

Binning & scoring strategies:

• Deciles (10 buckets) for finer granularity when you have large customer base; quintiles (5) for simpler operational segments. Compute quantile cutoffs on the distribution of each metric and assign scores 1–10 (or 1–5) — invert Recency so higher = better.
• Alternative: use k-means or Gaussian Mixture Model on standardized RFM vectors to discover natural clusters (works well if distributions highly skewed).
• Combine into composite RFM score (e.g., R_score100 + F_score10 + M_score) or weighted sum if business prioritizes revenue vs retention.

Validation:

• Temporal holdout: split by time (train on months 0–n, test on n+1–n+k). Evaluate whether high-RFM segments in training produce higher revenue, retention, or conversion in test window.
• Predictive modeling: fit a model (logistic/gradient boosting) using R,F,M to predict next-period purchase; use AUC, precision@k, calibration.
• A/B tests / uplift: run targeted campaigns per segment vs control to measure incremental lift (revenue per recipient, conversion rate).
• Metrics: future revenue, repeat purchase rate, CLTV lift, cost per incremental dollar.

Example segments & actions:

1. Champions (High R, High F, High M): Exclusive VIP offers, early access to new products, dedicated account manager or loyalty perks to maximize CLTV.
2. At-risk but valuable (Low R, High M/F historically): Win-back campaign with personalized incentives (time-limited discount, product recommendations based on past high-value purchases), churn prediction model for outreach prioritization.
3. New but low spenders (High R, Low F, Low M): Onboarding series with education, cross-sell low-friction products, small-value coupons to encourage second purchase and increase frequency.

Operational notes:

• Normalize skew (log transform) before clustering; handle outliers and returns.
• Automate RFM refresh cadence (weekly/monthly) and expose segments in BI dashboards for campaign teams.
• Track KPIs per segment over time to iterate weights and thresholds.