Customer and Marketing Performance Analytics Questions
Covers the end to end use of quantitative analysis to track, interpret, and act on business performance across accounts and campaigns. Candidates should be fluent in account level metrics such as customer retention rate, net revenue retention, annual recurring revenue, net promoter score, customer health scores, and customer lifetime value, as well as marketing and acquisition metrics such as click through rate, conversion rate, customer acquisition cost, return on advertising spend, and attribution model outcomes. Expect discussion of data sources and instrumentation, cohort and funnel analysis, segmentation, anomaly detection, attribution approaches, and calculating return on investment for initiatives. Candidates should be able to describe how they used analytics tools and queries, dashboards, and experiments or A B tests to identify at risk accounts or underperforming campaigns, prioritize actions, optimize strategies, and measure the impact of initiatives. Strong answers explain concrete metrics chosen, analysis methods, tools used, how results informed decisions, and how success was measured over time.
HardTechnical
44 practiced
Design a geo-based holdout experiment to measure incremental Return on Ad Spend (ROAS) for a new paid channel. Specify how you would select treatment and control geographies, determine pre/post measurement windows, compute incremental revenue and costs, test for statistical significance, handle spillover and seasonality, and practical deployment constraints such as creative rollout and budget allocation.
Sample Answer
Requirements & objective:- Measure incremental ROAS of a new paid channel (causal lift = incremental revenue / incremental ad spend).- Use geo-based randomized holdout to avoid user-level targeting issues.1) Geo selection (treatment vs control)- Unit = contiguous DMAs/metro areas with sufficient sample size.- Pre-filter geos by historical revenue volume, population, and homogeneity (exclude tiny/noisy or outlier geos).- Pair-match geos on pre-period revenue, trend, seasonality, and demographics using propensity-score matching or Mahalanobis distance; then randomly assign one of each pair to Treatment or Control to balance covariates.- Aim for >= 20 geos per arm if possible; power calculation (see below) sets minimum.2) Pre/post measurement windows- Pre-period: 8–12 weeks to estimate baseline and match; longer if business is highly seasonal.- Test (post) period: 6–12 weeks minimum to capture learning and ad fatigue; use daily/weekly granularity.- Allow a short ramp (1–2 weeks) excluded from measurement or modeled separately.3) Compute incremental revenue and costs- For each geo, compute observed revenue_post and predicted baseline revenue_post_hat (extrapolated from pre-period using time-series model or ANCOVA adjusting for pre-period revenue).- Incremental revenue = sum_treatment (revenue_post - revenue_post_hat) - sum_control (revenue_post - revenue_post_hat) if controls adjust for common shocks, or compute difference-in-differences (DiD): Incremental = (mean_rev_T_post - mean_rev_T_pre) - (mean_rev_C_post - mean_rev_C_pre)- Ad costs = actual spend in treatment geos (and any spend leakage into control). Incremental ROAS = Incremental revenue / Incremental ad spend.4) Statistical testing & significance- Primary: hierarchical (geo-level) DiD regression: Revenue_it = α + β1*Treatment_i + β2*Post_t + β3*(Treatment_i * Post_t) + γ*PreControls_i + ε_it. β3 is incremental effect.- Cluster standard errors at geo level.- Bootstrap geo-level treatment assignment (block bootstrap) to get confidence intervals for incremental ROAS.- Pre-test balance and run placebo tests by assigning fake treatment dates or placebo geos.- Use minimum detectable effect and power analysis before experiment; aim for 80% power.5) Handle spillover and seasonality- Exclude adjacent geos likely to receive spillover (or mark them as buffer and not used for primary inference).- Track cross-geo user movement and conversions (if available) and measure any conversion lift in control near borders.- Model seasonality using seasonal terms (week/month fixed effects) or include control geo trends as covariates.- If large national events occur, extend test or use synthetic control methods.6) Practical deployment constraints- Creative rollout: ensure identical creative/landing behavior across treatment geos; stagger rollouts only if intentional and modeled.- Budget allocation: set spend caps per-geo to hit target CPM/CPC and avoid saturating small geos; monitor pacing to keep spend proportional across treatment geos.- Measurement latency: agree on attribution window (e.g., 7/14/28 days) and consistently apply across arms.- Tracking & QA: verify impression/attribution tagging, dedupe events across platforms, and log spend by geo daily.- Governance: pre-register analysis plan, hold blameless monitoring, and avoid mid-test major product changes.7) Deliverables & dashboarding- BI dashboard showing geo-level trends (pre/post), cumulative incremental revenue, spend, ROAS, CI bands, and diagnostics (balance, placebo tests, spillover indicators).- Automated alerting if spend deviates or significance flips.Key trade-offs:- More geos vs larger geos: prioritize balance and power; synthetic control helps when few geos available.- Longer test reduces noise but increases cost/time; include ramp and saturation checks.This design yields an interpretable incremental ROAS with proper causal controls, statistical rigor, and operational safeguards for rollout.
MediumSystem Design
39 practiced
You need to combine CRM contacts, ad-platform click logs, and product event streams to compute user-level metrics. Describe how you would define a canonical user ID and join strategy: deterministic matches (email, user id), probabilistic matching across devices, deduplication rules, handling partial identifiers, and privacy considerations including GDPR. Discuss trade-offs between precision and coverage.
Sample Answer
Requirements & goal: create a single “canonical user” layer that lets BI join CRM (high-quality person records), ad click logs (device/ad identifiers, sometimes hashed emails), and product event streams (user_id when logged-in, cookie/session, device IDs). Deliver reliable user-level metrics while respecting privacy and traceability.Canonical ID strategy- CanonicalUserId = internal UUID assigned in the identity-layer. Never derived directly from PII. Map external identifiers to it via a mapping table (identity graph).- Store mapping: canonical_id, id_type (crm_user_id, email_hash, phone_hash, device_adid, cookie_id), id_value_hash, source, last_seen, confidence_score, first_seen.Deterministic matching (high precision)- Exact matches when available: CRM user_id, verified email, phone number. Normalize (lowercase, strip chars), then hash with a per-environment salt. Deterministic rules have highest priority and set confidence=1.0.- Logged-in events with user_id => direct map to CRM canonical_id.Probabilistic matching (increase coverage)- When deterministic is absent, use probabilistic device/behavioral linking: - Device graph: link ad IDs (GAID/IDFA), fingerprinting hashes, IP+UA+time windows. - Behavior signals: sequence similarity, overlapping sessions, geo/time proximity. - Use scoring model (logistic regression / gradient boosting) producing a match probability p. - Apply thresholds: p>0.95 => auto-merge; 0.7–0.95 => attach as “soft link” (do not change canonical primary mapping but allow attribution with weight); p<0.7 => no link.- Maintain audit trail: which edges caused a merge and score.Deduplication rules & conflict resolution- Source priority ordering: CRM_verified > product_logged_in > ad_platform_attributed > session_cookie.- When multiple deterministic IDs conflict, prefer CRM verified record; if two CRM records appear to be same person, flag for reconciliation (do not auto-merge without business rules).- Time-based rules: prefer mappings with recent activity; store last_seen to expire stale cookie/device mappings.- Soft vs hard dedupe: hard merges change canonical_id mapping (only for deterministic/high-confidence); soft links allow aggregated metrics but preserve separate identities.Handling partial identifiers- Treat partial identifiers as low-confidence signals. Examples: - Hashed email fragment or truncated phone: store as partial_id with low initial confidence. - Cookie_id/session_id: ephemeral; map with TTL (e.g., 30 days) unless they later map to a persistent id. - Ad click logs containing email_hash: deterministic if same hash and same salt; if different salting by partner, attempt normalization via partner mapping or probabilistic match.- Keep provenance and TTL so BI queries can choose to include/exclude ephemeral links.Privacy & GDPR- Pseudonymize: never store plain PII in analytics layer. Hash PII with HMAC-SHA256 and environment-specific key; store keys separately with strict access controls.- Consent-first: only map or retain identifiers if user consent permits the processing/purpose. Respect signal from CMPs and do not merge or use identifiers for individuals who opted out.- Right to be forgotten: identity graph supports delete/purge by canonical_id—cascade removals or mark as deleted and remove from downstream BI tables within SLA.- Data minimization & retention: drop ephemeral identifiers after TTL; retain only aggregated metrics beyond retention window.- DPIA & logging: record processing purpose, maintain audit logs for merges and queries that can identify individuals; run periodic privacy impact assessments.Trade-offs: precision vs coverage- Precision favors deterministic-only joins: low false merges, high trust for user-level KPIs, but misses cross-device behavior (lower coverage).- Coverage (include probabilistic links) captures more user activity across devices—useful for attribution and lifetime metrics—but increases false positives.- Recommended BI approach: expose both views - Conservative metrics (deterministic-only) for official reporting/financial KPIs. - Expanded metrics (deterministic + soft probabilistic with weights) for marketing attribution and product engagement analysis, with confidence intervals.- Provide confidence score per aggregated metric or use weighted attribution to reflect uncertainty.Operational notes- Implement identity graph in a scalable store (e.g., Snowflake table + incremental ELT, or dedicated graph store if real-time merging needed).- Build tooling to surface suspicious merges and let data stewards override.- Document mapping logic and thresholds in metadata so analysts choose appropriate cohorts.This design balances accuracy, coverage, and compliance by separating deterministic primary links from probabilistic soft links, keeping provenance and confidence, and enforcing privacy safeguards.
MediumTechnical
39 practiced
Your instrumentation team reports a two-week period of missing events due to a tracking bug. For historical dashboards and model training, explain strategies to handle this missing data: imputation (how and when), exclusion, adding flags to models, or rebuilding models. Discuss trade-offs and how you would communicate the chosen approach and its impact to stakeholders.
Sample Answer
Situation: Two weeks of events are missing due to a tracking bug; this affects historical dashboards and any models trained on that window.Approach (options, when to use, and trade-offs)- Exclusion: Drop the two-week window from datasets used for modeling and reporting when those weeks are not critical for seasonal cycles or when imputing risks bias. Trade-off: loses recent history and reduces sample size; dashboards will show a gap unless handled visually.- Imputation: - Simple methods: forward/backward fill or replace with rolling mean (useful for short gaps and stable metrics). - Seasonal/ratio scaling: for metrics with weekly seasonality, impute by scaling the same period from previous weeks/months using growth factors. - Model-based: time-series forecasting (ARIMA/Prophet) or regression using correlated signals (server logs, related events) for more accurate estimates. Trade-offs: Imputation introduces assumptions; model built on imputed data may inherit bias if the imputation is wrong.- Add flags/features: For any model trained on data spanning the gap, include a binary "imputed_period" or "low_quality" flag so downstream models can learn different behavior; this preserves information and reduces silent errors.- Rebuild models: Best when models are sensitive to distribution shifts (e.g., production prediction accuracy drops) or if imputation uncertainty is high. Re-train excluding the bad window or re-train on corrected raw data once available.Operational steps1. Triage: quantify affected metrics and find alternate signals (server logs, payment records) to validate imputation.2. Choose method per-metric: e.g., conversion rate -> use ratio scaling from prior weeks; raw counts -> model-based if correlated signals exist.3. Implement imputation with provenance: store both original (null) and imputed values and the method used.4. Add flags to datasets and retrain models with/without imputed data to measure sensitivity.5. Build monitoring: compare model performance pre/post and run A/B style checks where possible.Communication to stakeholders- Notify impacted teams immediately with clear scope: affected metrics, time window, and likely impact.- Present recommended approach, alternatives, and rationale (bias vs data completeness).- Share expected uncertainty: confidence intervals from imputation or model degradation estimates.- Update dashboards: visually mark imputed data (dashed lines, tooltips) and add a short note explaining the issue and date fixed.- Offer action plan: short-term (impute + flags), medium-term (retrain if needed), long-term (instrumentation fixes, alerts to prevent recurrence).- Provide a summary report after remediation: what was done, validation results, and whether models were rebuilt.This balances analytic integrity and business continuity: prefer conservative imputations with clear flags, validate against alternate signals, and only rebuild models when evidence shows significant impact.
HardSystem Design
38 practiced
Design an end-to-end analytics pipeline for near-real-time dashboards with sub-minute latency for key metrics while also supporting historical backfills. Cover event ingestion (SDKs, gateway), raw event store, stream processing (Kafka/Flink/Spark Streaming), incremental aggregation, OLAP storage, materialized views, caching layer, dashboard API, and how to ensure consistency, handle schema evolution, and manage backfill reprocessing.
Sample Answer
Requirements:- Sub-minute end-to-end latency for key metrics (near-real-time dashboards)- Support historical backfills and reprocessing- Accurate, consistent aggregates and low cost for ad-hoc queriesHigh-level architecture:1. Event ingestion- SDKs (mobile/web/backend) emit events with schema, event_id, user_id, timestamp, version.- Optional edge gateway (NGINX + auth) for rate limiting, enrichment (IP → geo), and schema validation.- Ingest to a highly-available message bus (Kafka) partitioned by tenant/key to preserve order.2. Raw event store- Persist raw events to immutable storage (S3 / GCS) via Kafka Connect (parquet/Avro) for audit and backfill. Also write compacted Kafka topic for near-real-time replays.3. Stream processing / incremental aggregation- Use Flink (or Spark Structured Streaming) reading Kafka to compute incremental aggregates (windowed, tumbling/sliding) and maintain exactly-once state with checkpointing.- Produce two outputs: a) Low-latency incremental deltas into an OLAP store (ClickHouse/ClickHouse Materialized Views or BigQuery streaming inserts). b) Materialized view change events back to Kafka for cache invalidation.4. OLAP storage & materialized views- OLAP chosen for fast slice-and-dice (ClickHouse or Snowflake with materialized views). Store pre-aggregated cubes (minute/hour/day) and fine-grained partitions keyed by date and entity.- Materialized views maintained by stream jobs and periodic batch jobs to reconcile.5. Caching & Dashboard API- A dashboard API (GraphQL/REST) queries cache (Redis) for hot keys; if miss, query OLAP. Cache invalidation triggered by materialized-view change events from Kafka.- Push updates via WebSocket/Server-Sent Events for live dashboards.Consistency, schema evolution, backfill:- Exactly-once semantics: use Kafka + Flink with checkpointing and idempotent sinks (or upserts) into OLAP. Use event_id and dedupe logic in stream processing.- Schema evolution: use Avro/Protobuf with schema registry. Consumers handle backward/forward-compatible changes; validation at gateway; enrichment functions tolerate missing fields.- Backfill/reprocessing: keep raw events in S3; support replay by producing to Kafka topics or running batch Flink jobs that read S3 and write to OLAP using temporal partitioned upserts. Use job-run metadata to mark backfill ranges and run reconciliation: compare pre-backfill aggregates to post-backfill, emit compensating deltas if needed.- Testing & monitoring: data-quality tests (Great Expectations), row-count checks, SLA alerts, and lineage metadata (Data Catalog) to trace anomalies.Trade-offs:- ClickHouse for low-latency OLAP vs BigQuery for managed scale. Flink gives low latency and stateful exactly-once; simpler Spark if teams prefer.This design balances sub-minute latency via streaming incremental aggregates and correctness via immutable raw storage + controlled backfills and schema registry.
HardTechnical
34 practiced
Explain uplift modeling (heterogeneous treatment effect modeling): how it differs from a standard response model, data requirements (randomized treatment labels or strong quasi-experimental design), experiment allocation considerations for validation, modeling approaches (two-model, transformed outcome, causal forest), evaluation metrics such as Qini or uplift_at_k, and how this technique is used to maximize incremental response under budget constraints.
Sample Answer
Uplift modeling (heterogeneous treatment effect modeling) predicts the incremental effect of an action (treatment) on an individual’s outcome versus no action — i.e., E[Y | X, treat=1] - E[Y | X, treat=0]. This differs from a standard response model that predicts outcome probability given treatment (P[Y|X]) without isolating causal lift; a high response score doesn't imply you caused the response.Data requirements- Prefer randomized treatment assignment (A/B test) so differences are causal.- If not randomized, need strong quasi-experimental design (propensity overlap, credible instrument, careful covariate balance) and diagnostics for unconfoundedness.- Sufficient treated and control sample sizes across subgroups to estimate heterogeneous effects.Experiment allocation & validation- Allocate a representative holdout control and treatment (often 50/50 for training/validation tradeoffs) or use stratified randomization to ensure coverage across important segments (e.g., high-value customers).- Reserve a pure randomized test set for final evaluation to avoid overfitting to modeled uplift.Modeling approaches- Two-model (separate models): fit model_treat for P[Y|X,1] and model_control for P[Y|X,0]; uplift = model_treat - model_control. Simple but can double variance.- Transformed outcome / doubly robust: transform labels to directly estimate individual treatment effects and use regression; often lower bias and can incorporate propensity scores.- Meta-learners (T-learner, S-learner, X-learner): frameworks combining base learners depending on data balance.- Causal forest / generalized random forest: nonparametric ensemble designed to estimate heterogeneous treatment effects with built-in honesty and variance estimates — good for BI insights and subgroup discovery.Evaluation metrics- Qini curve: cumulative incremental responses when targeting top-ranked individuals by predicted uplift; Qini coefficient is area between model and random targeting.- uplift_at_k (or uplift@k): incremental response (treated minus expected control) among top k% of targeted population; used to rank models by business value under targeting constraints.- A/B test of model-driven policy: deploy targeting policy vs random or business-as-usual and measure incremental lift in an experiment (gold standard).Using uplift to maximize incremental response under budget constraints- Score population by predicted uplift; sort descending.- Given budget B (e.g., number of contacts or cost cap), compute cumulative cost and select top-scoring individuals until budget exhausted.- If treatment cost varies, use uplift per-cost (profit-aware uplift) and solve knapsack-like selection or threshold by uplift/cost.- In BI dashboards, present: predicted uplift distribution, expected incremental conversions/revenue at different spend levels, uplift_at_k tables, and confidence intervals from causal forests so stakeholders can choose budget thresholds and see expected ROI.Practical tips for BI analysts- Surface interpretable segments (trees or SHAP on uplift models) for operational targeting.- Always validate model policy with a randomized holdout before full rollout.- Monitor treatment effects over time (decay, novelty), and include metrics in dashboards: Qini, uplift_at_k, actual incremental conversions from test vs predicted.
Unlock Full Question Bank
Get access to hundreds of Customer and Marketing Performance Analytics interview questions and detailed answers.
