Marketing and Growth Analytics Questions

High-level summary: Attribution decides how credit for conversions is assigned across touchpoints. As a BI analyst you must balance simplicity, actionability, and accuracy given data limitations (cookie deletion, cross-device gaps).

Last-touch

• Strengths: Simple, stable, easy to implement in dashboards and SQL; aligns with final conversion-focused KPIs.
• Weaknesses: Overweights bottom-funnel channels; ignores upper-funnel influence; biased toward channels with the last click.
• Use when: Quick, explainable channel reports or when optimizing last-click conversion efficiency.
• Practical caution: Cookie deletion can drop earlier touches; cross-device journeys often misattribute mobile-to-desktop conversions to last desktop touch.

First-touch

• Strengths: Highlights acquisition channels and which channels start journeys; simple to explain.
• Weaknesses: Neglects nurturing and closing activities; can over-credit awareness efforts.
• Use when: Evaluating acquisition campaigns or CAC; planning top-of-funnel spend.
• Practical caution: Suffers similarly from cookie loss—if the first touch was on a different device, it’s lost.

Multi-touch linear

• Strengths: Distributes credit evenly across all recorded touches; fairer than single-touch; easy to implement and visualize in BI tools.
• Weaknesses: Assumes equal impact per touch (often false); can dilute actionable signal.
• Use when: You want balanced channel-level views and to avoid extremes of single-touch models.
• Practical caution: Still limited by incomplete touch capture across devices and session-level cookie expiry.

Algorithmic (data-driven) attribution

• Strengths: Learns channel impact from data (e.g., Markov, logistic regression, Shapley); can reflect non-linear, incremental effects; best for optimizing ROI.
• Weaknesses: Requires sufficient, clean data, computational resources, and ongoing validation; results can be complex to explain to stakeholders.
• Use when: You have rich cross-channel event logs, significant volume, and need more accurate ROI estimates for budget allocation.
• Practical caution: Even algorithmic models are biased if tracking gaps exist—cookie deletion and disconnected devices create missing data that models can’t fully recover. Consider augmenting with: user-ID stitching (logged-in behavior), probabilistic matching, server-side tracking, CRM joins, and lift/experimentation (geo or holdout tests) to validate modeled attribution.

Recommendation:

• Start with simple models (linear or last/first) for reporting and transparency; run algorithmic attribution in parallel for deeper budget decisions. Surface uncertainty in dashboards (confidence intervals, data coverage metrics) and track % of conversions attributed via cross-device/user-ID vs anonymous cookies so stakeholders understand limitations.

Situation: Our product team suspected trial-to-paid conversion was declining but lacked a clear view across marketing, product, and billing data.

Task: As the BI Analyst, I was asked to design a dashboard that would identify where users dropped off, quantify impact, and inform prioritization for fixes.

Action:

• Engaged stakeholders: Product manager, Growth marketer, Billing ops, and an engineering lead to align goals and data sources.
• Defined key metrics: trial starts, activation events (first key action), trial-to-paid conversion, time-to-activation, cohort retention, and revenue per cohort. Mapped metrics to upstream events in our analytics schema.
• Built visuals in Tableau: funnel with conversion rates by segment, cohort retention charts, time-series of conversion, and a heatmap of activation events by user type.
• Fixed data issues: discovered duplicate trial records and missing user_id on some activation events. Wrote SQL dedup scripts in Snowflake, patched ingestion to populate user_id, and added a validation check to the ETL.
• Delivered dashboard and presented recommended actions: prioritize reducing friction in activation flow and target high-value cohorts with onboarding emails.

Result: The team implemented two product fixes and a targeted onboarding campaign. Within 8 weeks, trial-to-paid conversion improved from 6.5% to 7.7% (an 18% relative increase), driving an estimated $210K annual recurring revenue uplift. The dashboard became the single source of truth for ongoing experiments.

Short explanation:
Last-click attribution gives all credit to the final touch before conversion. Paid search often appears as the last touch because it’s targeted to high-intent queries, so last-click overstresses its role and ignores upper-funnel influences (brand ads, organic, email) that helped earlier. That inflates paid search ROI and can mislead budget decisions.

Two analyses/experiments to quantify true incremental impact:

1. Randomized holdout (user- or geo-level RCT)

• Design: randomly withhold paid-search ads for a test group (users or matched geos) while keeping everything else constant.
• Measure: compare conversion rate, revenue per user, and CPA between test and control over the campaign window.
• Advantages: causal estimate of incremental conversions and ROAS. Needs adequate sample size and run-length to smooth seasonality.
• Implementation notes: ensure no spillover (frequency caps, deduplication), log exposures and conversions, pre-register metric.

2. Matched observational lift (difference-in-differences + propensity scoring)

• Design: build a propensity model for the probability of seeing paid-search (based on past behavior, demographics, device, daypart), match exposed users to similar unexposed users, then apply diff-in-diff comparing pre/post periods.
• Measure: incremental conversions and revenue, validate with sensitivity checks.
• Advantages: usable when RCT infeasible; controls for selection bias. Watch for unobserved confounders.
• Supplement: run a regression-based incremental response curve (ad spend → incremental conversions) controlling for seasonality, brand lift, and other channels.

Optional: hybrid approach — run smaller geo-RCTs to validate observational models, then scale using modeled incrementality. These methods yield causal or quasi-causal estimates you can use to reallocate budget more effectively.

High-level approach: convert the 3% lift in sign-ups and the 4% drop in AOV into dollar changes over an appropriate horizon (monthly/annual) using cohort/LTV so you capture lifetime effects and behavioral differences between new vs. existing users.

Datasets needed

• User table: sign-up date, user_id, acquisition channel
• Orders table: order_id, user_id, order_date, order_value
• Marketing exposure table: who was in the campaign (treatment/control)
• Retention/engagement: user activity over time (sessions, purchases)
• Costs: campaign spend, CAC per channel
• Baseline metrics: historical conversion rates, churn/retention curves, LTV estimates

Calculations to perform

1. Baseline metrics (pre-campaign period):
   • Avg monthly sign-ups S0
   • Avg AOV A0
   • Conversion rate from sign-up → purchaser C0
   • Baseline monthly revenue R0 = S0 * C0 * A0
2. Observed changes:
   • New sign-ups S1 = S0 * (1 + 0.03)
   • New AOV A1 = A0 * (1 - 0.04)
   • If campaign affects conversion/retention, estimate C1, retention1 from treatment cohort
3. Net short-term monthly revenue delta:
   ΔR_month = S1 * C1 * A1 - S0 * C0 * A0
   Or equivalently:
   ΔR_month = (S00.03)C1A1 + S0(C1-C0)A1 + S0C0*(A1-A0)
4. LTV / cohort approach (recommended):
   • Build cohorts by sign-up week/month and compute cumulative revenue per user over time (e.g., 12-month LTV)
   • Estimate LTV0 and LTV1 for baseline vs. campaign cohorts
   • Net LTV impact = (S1 - S0) * LTV1 + S0 * (LTV1 - LTV0)
   • Multiply by time window (e.g., annualized or 3-year)
5. Include campaign cost: Net impact in $ = Net revenue change (LTV-adjusted) - campaign spend

Handling differences between new and existing users

• If AOV decline is measured across all users, separate effect on new users vs. existing purchasers using treatment flags.
• If new users have lower initial AOV but higher retention, capture that in cohort LTV.

Presenting uncertainty and sensitivity

• Show point estimate plus ranges: best/likely/worst scenarios using plausible ranges for conversion and retention (e.g., ±95% CI).
• Bootstrapping: resample cohort revenues to derive confidence intervals for LTV differences.
• One-way sensitivity table: vary key inputs (AOV change, conversion lift, retention decay) and show net impact.
• Tornado chart to highlight which assumptions drive value most.

Presentation to leadership

• Executive slide with single-number net dollar impact (annualized) and ROI (net impact / spend).
• Waterfall chart showing components: incremental sign-up revenue, lost revenue from lower AOV, change from retention/conversion, minus spend → net.
• Cohort LTV table and visualization showing treatment vs control revenue curves.
• Assumptions panel with sensitivity ranges and recommended next steps (e.g., A/B to isolate AOV effect, segment campaign, adjust creative).

Key sanity checks

• Attribution window alignment (make sure revenue tied to acquisition window)
• Leakage: customers exposed in both periods
• Statistical significance of sign-up lift and AOV drop before presenting firm conclusions.

Approach summary

• Use data-driven windows and probabilistic adjustment rather than arbitrary rules. Combine empirical delay-distribution analysis, cohort survival curves, and deterministic + probabilistic matching for cross-device de-duplication.

Step 1 — pick attribution windows

• Compute empirical conversion lag distribution per channel/campaign: for each click/impression cohort, plot time-to-conversion CDF and median/90th percentile.
• Choose primary window at the channel-specific median or 75th percentile for reporting balance (captures majority quickly) and a secondary long window (90th/95th) for full-funnel analysis and backlog reporting.
• Store windowed metrics (e.g., 0–1d, 2–7d, 8–30d) so stakeholders can compare.

Step 2 — adjust for multi-day funnels

• Attribute conversions with multi-touch weighting (decay or data-driven Shapley if resources allow). For single-conversion counts, report both raw last-touch and weighted-attributed counts.
• For delayed conversions outside primary window, report backlog-adjusted forecasts: use survival analysis (Kaplan–Meier) per cohort to estimate expected future conversions and add with confidence intervals.

Step 3 — handle cross-device/incomplete identifiers

• Deterministic join when user IDs present.
• When incomplete, apply probabilistic matching: hashed device fingerprints, IP+user-agent+time proximity, and model-based match scores trained on deterministic-labeled pairs. Convert matches into de-duplication probabilities (p_match).
• For each conversion, adjust counts by (1 - p_duplicate) when summing across devices; for attribution, split credit proportionally to match probabilities.

Trade-offs

• Bias vs variance: tighter windows reduce false positives but undercount delayed conversions; longer windows increase noise and misattribution.
• Probabilistic matching reduces double-counting but can introduce false merges; controlling threshold trades precision for recall.
• Complexity and explainability: advanced methods (Shapley, survival models) are more accurate but harder for stakeholders to understand.
• Privacy and compliance: probabilistic linking must respect privacy laws; prefer aggregated adjustments over user-level joins where required.

Surfacing measurement uncertainty

• Always show multiple metrics: raw counts, windowed counts (0–1d, 2–7d, 8–30d), and backlog-adjusted estimates with 95% CIs.
• Include an “measurement confidence” score per metric driven by identifier completeness, model match precision/recall, and sample sizes.
• Provide sensitivity analysis controls in dashboards (toggle window length, matching threshold, attribution model) so stakeholders see how decisions affect results.
• Document assumptions, data coverage, and known blind spots in a visible metadata pane and annotate reports when windows or models change.

Example outputs for stakeholders

• KPI tile: Conversions (0–7d): 12,300 ± 400 (95% CI), Probabilistic dedup adjustment: -3.2%
• Drill-down: funnel cohort showing expected future conversions (30d forecast) and % captured to date.

This approach balances accuracy and transparency: empirical windowing + survival forecasting captures delayed behavior, probabilistic de-duplication handles incomplete IDs, and explicit uncertainty signals enable informed decisions.