Marketing and Growth Analytics Questions

Approach: create mutually exclusive segments by converting each R, F, M into ordinal bins (e.g., quintiles) then map combined RFM patterns into prioritized, non-overlapping customer buckets with clear business actions and measurable hypotheses.

Cutoffs (practical method):

• Use data-driven quantiles (20% quintiles) to bin R, F, M into scores 1–5 (5 = best). Quintiles handle skew; you can use domain overrides (e.g., revenue thresholds) if business rules require.
  Example SQL to compute quintiles and RFM score:

WITH rfm AS (
  SELECT customer_id,
         DATEDIFF(day, MAX(order_date), CURRENT_DATE) as recency,
         COUNT(*) as frequency,
         SUM(amount) as monetary
  FROM orders GROUP BY customer_id
),
quantiles AS (
  SELECT customer_id,
         NTILE(5) OVER (ORDER BY recency ASC) as r_score,
         NTILE(5) OVER (ORDER BY frequency DESC) as f_score,
         NTILE(5) OVER (ORDER BY monetary DESC) as m_score
  FROM rfm
)
SELECT customer_id, r_score, f_score, m_score, (r_score*100 + f_score*10 + m_score) as rfm_code
FROM quantiles;

Mutually exclusive segments (example mapping, highest to lowest priority):

1. Champions (R>=4, F>=4, M>=4) — high-value, target with premium upsell & loyalty offers.
2. Loyalists (F>=4, R 2–4, M 3–4) — cross-sell/renewal campaigns.
3. At-risk big spenders (R 1–2, M>=4) — win-back with high-value incentives.
4. New potential (R=5, F=1–2, M>=3) — onboarding/up-sell educational flow.
5. Occasional (F 2–3, M 2) — retention discounts, increase frequency.
6. Dormant/Churned (R=1, F low) — low-cost reacquisition or sunset.

Business actions: map each segment to channel, offer intensity, creative, and budget weight (e.g., Champions = maintain, Loyalists = 25% higher contact, At-risk big spenders = targeted high-value offers).

Validation plan:

• Run randomized controlled trials per segment (A/B or holdout) measuring incremental revenue and cost over a test period.
• Key metrics: incremental revenue per customer, conversion lift, cost per incremental dollar, ROI = (incremental_revenue - campaign_cost)/campaign_cost.
• Statistical tests: difference-in-means with sufficient power; track lift over baseline cohort.
• Operational checks: monitor segment stability over time, reassess quantiles monthly/quarterly, and rerun mapping if distribution shifts.

Why this works: quantile binning normalizes skew; explicit mapping ensures mutual exclusivity; RCTs provide causal evidence of ROI; iterative recalibration aligns segmentation with changing behavior.

Framework: treat this as an acquisition economics problem — compute incremental customer value vs incremental cost over 12 months, then test sensitivity.

Step 1 — define scope & data:

• Time window: campaign start → 12 months forward for each signup cohort.
• Datasets: ad spend by day/campaign, click/impression, user-level acquisition source, signup date, subscription/payment events, refunds/churn, ARPU components.

Step 2 — attribution & incrementality:

• Use last-touch and multi-touch rules for baseline; run a causal check (geo or time-based holdout if available) or uplift model to estimate incremental signups attributable to campaign.
• Choose attribution windows: 7/30/90 days for first conversion events; justify based on sales cycle.

Step 3 — cohort LTV estimation:

• Build weekly/monthly cohorts by acquisition source.
• For each cohort, compute cumulative revenue per user over 12 months (paid conversions, repeat payments).
• Model retention curve (e.g., Kaplan-Meier or exponential decay) to fill censored tails if <12 months of data.
• Estimate gross margin per revenue to get contribution.

Step 4 — discounting & ROI calc:

• Discount future cash flows to present value using company WACC or 10–15% annual discount (pro rata monthly).
• Per cohort: PV(LTV) = sum_t (expected net revenue_t / (1+ r)^{t/12}).
• Campaign ROI = (Sum incremental PV(LTV) - incremental ad spend) / incremental ad spend.

Step 5 — sensitivity & robustness:

• Vary key assumptions: attribution lift (±20–50%), retention decay rates, conversion rates, gross margin, discount rate.
• Produce scenario outputs: conservative/base/optimistic and tornado chart showing which assumptions drive ROI.
• Run bootstrap confidence intervals for LTV and permutation tests for incremental impact.

Deliverables & visualization:

• Cohort dashboards (acquisition source, CPA vs 12-month PV LTV), ROI table by channel, sensitivity charts, and recommended action (continue/optimize/stop).

Risk & caveats:

• If no holdout, incremental estimates may be biased — recommend A/B or geo experiments for next campaign.
• Account for seasonality and one-time promotions when fitting retention/ARPU.

Approach overview:
Build attribution from a user-level touchstream: ordered events per user (channel, timestamp, event_type, conversion_flag, value). Clean/normalize channel taxonomy, dedupe rapid-fire touches, and define conversion window.

Required data:

• User_id, touch_timestamp, channel (source/medium/campaign), touch_type (click/impression), session_id
• Conversion records: user_id, conversion_timestamp, value
• Optional: cost per touch, campaign metadata
  A touchstream table should allow joining touches to conversions within a lookback window (e.g., 30 days).

Models (what they do, implementation sketch, trade-offs):

1. Last-Touch

• Assigns 100% credit to the last touch before conversion.
• SQL: find max(touch_timestamp) per conversion and attribute.
• Pros: simple, easy to explain, fast.
• Cons: biases channels that appear late (retargeting), ignores assistive channels.

2. Linear

• Splits credit evenly across all touches in the conversion window.
• SQL: count touches per conversion and divide credit equally.
• Pros: simple, fairer than last-touch.
• Cons: treats all touches equally though influence varies.

3. Time-Decay

• Weigh touches by recency (e.g., exponential decay: weight = exp( -lambda * age_hours)).
• Implementation: compute age = conversion_time - touch_time, apply weight, normalize per conversion.
• Pros: balances recency with assists; tunable half-life.
• Cons: requires choosing decay parameter; still heuristic.

4. Algorithmic — Markov Chain (or Shapley/causal uplift)

• Build states = channels + start/end. Estimate transition probabilities from sequences. Compute removal effect: difference in conversion probability when a channel is removed (attribution = decrease in conversion prob).
• Implementation steps:
  • Aggregate touch sequences per user into ordered lists up to conversion/no-conversion.
  • Fit transition matrix P(channel_i → channel_j).
  • Compute absorbing probabilities to conversion; for each channel, remove its transitions, recompute conversion probability; marginal effect = attributed credit.
• Pros: captures positional and sequential importance, grounded in probability, handles assists.
• Cons: assumes Markov property (memoryless), heavy compute, requires many sequences for stable estimates. Alternatives: Shapley value (computationally expensive but handles synergy), causal inference/Uplift modeling (best for causal claims but needs experiments).

Implementation at scale:

• Small-medium: SQL + window functions and UDFs
  • Example: compute linear attribution in SQL

WITH touches AS (
  SELECT user_id, conversion_id, channel, touch_ts,
         ROW_NUMBER() OVER (PARTITION BY conversion_id ORDER BY touch_ts) as rn,
         COUNT(*) OVER (PARTITION BY conversion_id) as cnt
  FROM touchstream JOIN conversions USING (user_id)
  WHERE touch_ts BETWEEN conversion_ts - interval '30 days' AND conversion_ts
)
SELECT channel, SUM(1.0/cnt) as attributed_conversions
FROM touches
GROUP BY channel;

• Large scale: ETL pipeline (Spark/Databricks) to group sequences, compute decay weights, and run Markov fits. Use map-reduce to build transition counts, then do linear algebra (matrix inversion, absorbing prob.). Store results to analytics DB for dashboards. Batch compute daily; incremental streaming possible but complex.

Business implications & trade-offs:

• Simplicity vs accuracy: Last-touch is easy but can misallocate budget; algorithmic models are more accurate but harder to explain and maintain.
• Actionability: Linear/time-decay provide intuitive CPA/CPL by channel; Markov/Shapley better for strategic budget shifts because they reveal assistive value.
• Data requirements & stability: Algorithmic models need large sample sizes and consistent channel taxonomy; small or new channels will have noisy credits.
• Causality: None of these inherently prove causation. For causal claims run randomized experiments (holdout groups) or use uplift models.
• Recommendation: Start with last-touch or linear for operational reporting; run periodic algorithmic analyses (Markov/Shapley) to inform budget reallocation and validate heuristics. Where possible, complement with holdout experiments to measure true incremental lift.

Approach (framework): clarify goal → gather/clean data → exploratory diagnostics by channel → attribution & lift analysis → compute ROAS → recommendations.

1. Clarify goals & timeframe

• Confirm campaign dates, target KPI (revenue, LTV, new users), geo/segments, and what “traffic” includes (organic vs paid).

2. Data collection

• Pull time-series at daily granularity: sessions, users, new users, conversions, revenue, avg order value (AOV), channel, campaign, landing page, device, country.
• Control period baseline (pre-campaign) and seasonality adjustments.

3. Diagnostic analysis (by channel)

• Compare traffic vs revenue by channel: %∆ sessions, conversions, revenue, AOV.
• Conversion rate = conversions / sessions; Revenue per session = revenue / sessions.
• Identify channels with high traffic lift but low conversion lift (e.g., display/paid social driving low-intent visits).
• Cohort/LTV check: are new users converting later? Look 7/30/90-day revenue for users acquired during campaign.

4. Attribution & incrementality

• Use last-click and multi-touch attribution to see contribution differences.
• Run uplift test or difference-in-differences if a holdout/control is available (e.g., geo holdouts) to measure incremental revenue.
• If no holdout, use matched historical controls (propensity-score matching) to estimate incremental lift.

5. ROAS calculation

• ROAS = revenue_attributable_to_campaign / ad_spend.
• If using attribution model: sum revenue attributed to campaign (or incremental revenue from uplift test).
• Example: ad_spend = $200k; incremental revenue = $400k → ROAS = 2.0 (i.e., $2 revenue per $1 spent).
• Also compute CAC and payback using LTV if conversions expected later.

6. Additional analyses

• Funnel analysis by landing page, device, browser; check page load times and bounce rates.
• Segment by new vs returning, AOV distribution, promo usage, discount codes (are discounts increasing orders but not revenue).

Data to request from Marketing & Product

• Exact ad_spend by channel/campaign and creative-level identifiers.
• Targeting criteria (audience segments, bids), UTM parameters mapping.
• Landing page variants and A/B test assignments.
• Promotions/discounts active during campaign and attribution rules for promo codes.
• Product availability/pricing changes, checkout funnel changes, and site performance logs (page load, errors).
• CRM/LTV data to attribute future revenue to current acquisitions.

Outcome: this workflow isolates whether traffic was low-quality, conversions delayed, cannibalization, or poor attribution — then compute ROAS on incremental revenue and recommend reallocating spend, optimizing creatives/landing pages, or running controlled experiments.

Framework: run a cohort-based, causal-aware comparison that answers “which channel delivers higher long-term LTV after adjusting for initial quality & demographics?”

Data requirements:

• User-level data: acquisition channel, timestamp, source campaign, demographics (age, country, device), signup funnel metrics, first-week behavior (sessions, purchases), cost-per-acquisition.
• Outcome: revenue/time per user (daily/weekly), churn flag, lifetime up to N days (30/90/365).
• Business metadata: currency, campaign spend, attribution windows.
• Sufficient sample sizes per channel and time span.

Analysis plan:

1. Exploratory cohort analysis

• Plot raw retention curves and cumulative revenue (Kaplan–Meier-style survival and mean cumulative function) by cohort and channel (day 1/7/30/90).
• Cohort heatmaps and LTV curves (mean revenue per user over time).

2. Control for confounding

• Build propensity scores: logistic regression / gradient-boosted tree predicting channel membership from demographics + initial-quality features (first-week activity).
• Match users (1:1 or weighted) or inverse-propensity weighting to create balanced samples. Check covariate balance (standardized mean differences).

3. Statistical tests & models

• Compare survival curves with log-rank test (unadjusted).
• Use Cox proportional hazards to model time-to-churn controlling for covariates.
• Model revenue/LTV with regression (e.g., Tobit or gamma GLM for skewness) including channel + covariates, or use doubly robust AIPW for causal average treatment effect on LTV at key horizons.
• Bootstrap confidence intervals for mean LTV differences and ROI (LTV – CAC).

4. Robustness & segmentation

• Sensitivity analyses: different matching methods, excluding outliers, examine heterogeneity by cohort (country, device).
• Test proportional hazards assumption and model fit.

Presentation & recommendations:

• Executive summary: adjusted LTVs at 30/90/365, statistical significance, ROI per channel.
• Visuals: side-by-side retention & adjusted LTV curves, balance diagnostics, cohort heatmaps.
• Actionable items: scale channel A if adjusted LTV>CAC and sustainable; optimize creative targeting for channel B in segments where it performs well; pause/experiment if differences vanish after adjustment.
• Next steps: run an A/B test or randomized experiment for causal confirmation; track updated cohorts and automate reporting.

Why this approach: combines descriptive cohort insights with causal adjustment so decisions reflect true long-term value, not initial user-quality or demographic mix.