Data Analysis and Insight Generation Questions

Ability to convert raw data into clear, evidence based business insights and prioritized recommendations. Candidates should demonstrate end to end analytical thinking including data cleaning and validation, exploratory analysis, summary statistics, distributions, aggregations, pivot tables, time series and trend analysis, segmentation and cohort analysis, anomaly detection, and interpretation of relationships between metrics. This topic covers hypothesis generation and validation, basic statistical testing, controlled experiments and split testing, sensitivity and robustness checks, and sense checking results against domain knowledge. It emphasizes connecting metrics to business outcomes, defining success criteria and measurement plans, synthesizing quantitative and qualitative evidence, and prioritizing recommendations based on impact feasibility risk and dependencies. Practical communication skills are assessed including charting dashboards crafting concise narratives and tailoring findings to non technical and technical stakeholders, along with documenting next steps experiments and how outcomes will be measured.

MediumTechnical

81 practiced

Design an RFM (Recency, Frequency, Monetary) segmentation for customer marketing. Explain how you would compute each component, choose cutoffs (quantiles, k-means), validate segments, and propose one targeted business action per segment along with the metric to measure success.

Sample Answer

Approach (overview)- Compute R, F, M per customer from transaction history, normalize into comparable scores, create segments using either quantile-based RFM score (1–5 each) or clustering (k-means on scaled R,F,M). Validate segments with behavioral & business metrics, then map targeted actions and success metrics.

Compute each component- Recency: days since last purchase (use reference date = analysis date). Lower = better. Cap at sensible max (e.g., 365d) to limit skew.- Frequency: count of purchases in lookback window (e.g., 12 months) or transactions per active period.- Monetary: sum of spend in lookback window (or avg order value if you want purchase-level focus). Use log-transform if skewed.

Choosing cutoffs- Simple: score each metric into quintiles (1 = worst, 5 = best), combine as RFM = R_score*100 + F_score*10 + M_score to get ~125 possible combos; aggregate into 5–8 business-friendly segments.- Advanced: scale (z-score or min-max), run k-means (k=5–8), choose k via elbow/silhouette. Clustering handles non-linear distributions; quantiles give interpretability.

Validation- Internal: silhouette score, cluster stability across bootstrap samples, profile each segment on holdout period (next 3–6 months) for actual repeat rate, average CLV, churn.- Business: check segments align with known cohorts (e.g., VIPs), sample manual review, estimate incremental ROI for proposed actions via A/B tests.

Sample segments, action, success metric1) Champions (R=5,F=5,M=5)- Action: VIP program + early access promotions- Metric: retention rate and avg monthly revenue per customer (+% vs control)

2) Loyal (high F/M, good R)- Action: cross-sell campaigns with personalized bundles- Metric: incremental purchase rate and AOV lift

3) Potential (recent, low F/M — new customers)- Action: onboarding series with product education + time-limited discount- Metric: 30/90-day repeat purchase rate

4) At-risk (low R, mid F/M)- Action: win-back offer + targeted email with reason-to-return incentives- Metric: reactivation rate within 30 days and reduced churn

5) Need Attention / Low-Value (low R/F/M)- Action: cost-effective re-engagement (retargeting ads) or sunset segmentation- Metric: cost per reactivation and CPA; if >LTV threshold, sunset

Implementation notes- Automate RFM scoring in SQL/ETL; refresh weekly/monthly.- Always A/B test actions on a segment sample before full rollout and track CLV uplift over 3–6 months.

HardTechnical

62 practiced

The CFO asks for marketing ROI across channels accounting for attribution lag and cannibalization. Describe data sources you would use, difference between last-touch, multi-touch, and Marketing Mix Modeling (MMM), how to account for channel interactions and conversion lag, and how you'd present uncertainty and assumptions to the CFO.

Sample Answer

Situation: The CFO wants accurate marketing ROI by channel but is concerned about attribution lag and cannibalization. As a BI Analyst I’d deliver a defensible, actionable analysis combining granular event data with aggregate causal models.

Data sources I'd use:- Digital touch data: ad impressions/clicks, UTM-tagged sessions, ad platform spend (Google, Meta, DSPs)- Web/app events and funnel: pageviews, product views, add-to-cart, purchases (from analytics or event pipeline)- CRM / order data: transaction timestamps, customer IDs, LTV, returns- Offline/OOH/TV: media schedules and spend by GRP/flight (media vendor reports)- Customer master: device id/user id joins, cohort/backfilling tables- Cost/financials: media cost, creative production, agency fees- External: seasonality (holidays), economic indicators, competitor activity proxies

Attribution approaches (short):- Last-touch: assign conversion to most recent touch. Simple, real-time, low data needs; biased toward channels late in funnel.- Multi-touch: rules-based or data-driven (e.g., time decay, position-based, Shapley). Distributes credit across touches; better for journey view but sensitive to measurement gaps.- Marketing Mix Modeling (MMM): aggregate, econometric/casual model using time-series (weekly/market-level) estimating sales response to spend by channel, controlling for seasonality, promotions, and external factors. Good for long-term brand/OOH/TV effects and cross-channel interactions; limited granularity and slower to update.

Accounting for channel interactions & cannibalization:- Combine methods: use MMM to estimate baseline and long-run cross-channel elasticities (including negative/positive interaction terms), and use data-driven multi-touch or incrementality tests for digital short-term impact.- In MMM include interaction terms (spend_channelA * spend_channelB) or hierarchical models by geography to capture substitution.- Run Holdout/A-B tests or geo-experiments where feasible to quantify causal cannibalization.

Handling conversion lag:- Use distributed lag models (adstock/exponential decay) in MMM to model carryover and lag structure per channel.- In multi-touch, apply time-decay windows aligned to observed touch→convert distributions (empirical conversion curves).- Use survival analysis or hazard models to estimate probability of conversion as function of time since touch.

Presenting uncertainty and assumptions to the CFO:- Deliver a one-page executive summary + dashboard with: - Point estimates (ROAS, incremental revenue) and 90% confidence intervals - Key assumptions: attribution window, adstock half-life, interaction terms included, granularity (weekly/geo) - Sensitivity analysis: show ROI under alternative plausible assumptions (e.g., short vs long adstock) - Validation: holdout/test results, goodness-of-fit, backtest vs realized revenue - Actionable recommendations: reallocate X% from low-incrementality channels, budget for experiments, and steps to improve measurement (better ID joins, more geo-tests)Frame findings as ranges with clear caveats; recommend deployment of incremental testing program to reduce uncertainty over next 3–6 months.

MediumTechnical

55 practiced

Weekly traffic shows strong seasonality. Describe how you would decompose the time series into trend, seasonality, and residual components, which libraries or tools you'd use (e.g., STL, Prophet, statsmodels), and how decomposition helps both forecasting and anomaly detection in BI dashboards.

EasyTechnical

52 practiced

List common SQL anti-patterns that make BI reports slow or inaccurate (name at least five) and for each provide a brief fix or alternative. Focus on patterns relevant to large analytical datasets used for dashboards and scheduled reports.

MediumTechnical

60 practiced

Write a SQL query that returns month-over-month (MoM) percentage growth in revenue per product category for the last 12 months. Use PostgreSQL and include null handling so months with zero revenue show `NULL` growth rather than division-by-zero errors. Provide the expected output columns and a brief explanation of your approach.

Sample Answer

Approach: generate a month series for the last 12 months, aggregate revenue by product_category and month, then use LAG() to get prior-month revenue. Return NULL for MoM growth when prior month is NULL or zero to avoid division-by-zero.

sql

WITH months AS (
  -- last 12 calendar months (inclusive of current month)
  SELECT date_trunc('month', (current_date - interval '11 months')) + (interval '1 month' * generate_series(0,11)) AS month_start
),
cat_month_rev AS (
  SELECT
    c.product_category,
    m.month_start,
    COALESCE(SUM(o.amount), 0) AS revenue  -- assume orders.amount stores revenue
  FROM months m
  CROSS JOIN (SELECT DISTINCT product_category FROM products) c
  LEFT JOIN orders o
    ON date_trunc('month', o.order_date) = m.month_start
    AND o.product_category = c.product_category
  GROUP BY c.product_category, m.month_start
),
with_lag AS (
  SELECT
    product_category,
    month_start,
    revenue,
    LAG(revenue) OVER (PARTITION BY product_category ORDER BY month_start) AS prev_revenue
  FROM cat_month_rev
)
SELECT
  product_category,
  month_start,
  revenue,
  prev_revenue,
  CASE
    WHEN prev_revenue IS NULL OR prev_revenue = 0 THEN NULL
    ELSE ROUND((revenue - prev_revenue) / prev_revenue * 100.0, 2)
  END AS mom_pct_change
FROM with_lag
ORDER BY product_category, month_start;

Expected output columns:- product_category (text)- month_start (date) — first day of month- revenue (numeric)- prev_revenue (numeric)- mom_pct_change (numeric, percent) — NULL when prior month is NULL or zero

Notes:- Adjust table/column names (orders.amount, order_date, products.product_category) to your schema.- Using CROSS JOIN with distinct categories ensures months with zero revenue appear; COALESCE(…,0) produces zero revenue rows while the CASE returns NULL growth when prev_revenue = 0.

Unlock Full Question Bank

Get access to hundreds of Data Analysis and Insight Generation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.