Analyzing Business Data

YAML
---
name: analyzing-business-data
description: Provides expert-level SQL analytics, statistical hypothesis testing, cohort/funnel analysis, and BI dashboard design. Use when extracting insights from business datasets, writing analytical SQL (window functions, CTEs, retention cohorts), designing KPI frameworks, running A/B test significance calculations, or building executive data visualizations.
---

# Analyzing Business Data

Expert data analysis combining analytical SQL, statistical rigor, and business metrics (cohorts, funnels, LTV, retention) to produce decision-grade insights.

1. Cohort Retention Analysis

SQL
WITH user_cohorts AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_timestamp)) AS cohort_month
  FROM events
  GROUP BY user_id
),
user_activity AS (
  SELECT DISTINCT e.user_id, c.cohort_month,
         DATE_DIFF(DATE_TRUNC('month', e.event_timestamp), c.cohort_month, MONTH) AS month_number
  FROM events e
  JOIN user_cohorts c USING (user_id)
),
cohort_sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_users
  FROM user_cohorts
  GROUP BY cohort_month
)
SELECT a.cohort_month, s.cohort_users, a.month_number,
       COUNT(a.user_id) AS active_users,
       ROUND(100.0 * COUNT(a.user_id) / NULLIF(s.cohort_users, 0), 2) AS retention_pct
FROM user_activity a
JOIN cohort_sizes s USING (cohort_month)
GROUP BY 1, 2, 3
ORDER BY 1, 3;

2. Multi-Step Funnel Conversion

SQL
WITH funnel_events AS (
  SELECT user_id,
    MIN(CASE WHEN event_name = 'view_item' THEN event_timestamp END) AS step_1_view,
    MIN(CASE WHEN event_name = 'add_to_cart' THEN event_timestamp END) AS step_2_cart,
    MIN(CASE WHEN event_name = 'checkout_start' THEN event_timestamp END) AS step_3_checkout,
    MIN(CASE WHEN event_name = 'purchase' THEN event_timestamp END) AS step_4_purchase
  FROM events
  WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT
  COUNT(step_1_view) AS viewed,
  COUNT(CASE WHEN step_2_cart >= step_1_view THEN 1 END) AS added_to_cart,
  COUNT(CASE WHEN step_3_checkout >= step_2_cart THEN 1 END) AS started_checkout,
  COUNT(CASE WHEN step_4_purchase >= step_3_checkout THEN 1 END) AS purchased,
  ROUND(100.0 * COUNT(CASE WHEN step_4_purchase >= step_1_view THEN 1 END)
        / NULLIF(COUNT(step_1_view), 0), 2) AS overall_conversion_pct
FROM funnel_events;

3. A/B Test Significance & Confidence Interval

Python
import numpy as np
from scipy import stats

def evaluate_ab_conversion(control_visitors, control_conv, treat_visitors, treat_conv, alpha=0.05):
    p_c = control_conv / control_visitors
    p_t = treat_conv / treat_visitors
    p_pool = (control_conv + treat_conv) / (control_visitors + treat_visitors)
    se = np.sqrt(p_pool * (1 - p_pool) * (1 / control_visitors + 1 / treat_visitors))
    z_stat = (p_t - p_c) / se
    p_value = 2 * (1 - stats.norm.cdf(abs(z_stat)))

    se_unpooled = np.sqrt(p_c * (1 - p_c) / control_visitors + p_t * (1 - p_t) / treat_visitors)
    z_crit = stats.norm.ppf(1 - alpha / 2)
    diff = p_t - p_c
    ci_low, ci_high = diff - z_crit * se_unpooled, diff + z_crit * se_unpooled

    return {
        "control_rate": round(p_c, 4),
        "treatment_rate": round(p_t, 4),
        "relative_lift_pct": round((p_t - p_c) / p_c * 100, 2),
        "p_value": round(float(p_value), 5),
        "significant": bool(p_value < alpha),
        "ci_95_abs_lift": (round(float(ci_low), 4), round(float(ci_high), 4)),
    }

• Always restate the business question and data grain before writing SQL.
• Report median (P50), P90, P99 alongside mean for any skewed metric (revenue, latency, session length).
• Segment key aggregates by platform, region, and tenure to catch Simpson's Paradox before reporting a headline trend.
• Anchor cohorts on acquisition/first-event date, not "currently active" status, to avoid survivorship bias.
• For A/B tests, lock sample size/duration upfront; never declare significance from a mid-flight peek.
• Pair every statistical claim with both p-value and effect size/CI — significance without magnitude is not actionable.
• Prefer window functions and CTEs over nested subqueries for readability and correctness in multi-step logic.

• Don't average skewed metrics without checking the distribution — outliers will mislead.
• Don't compute retention/LTV only on users still active today — this silently excludes churned users and inflates results.
• Don't call an A/B test result final based on an early peek — wait for the pre-registered sample size or use sequential testing corrections.
• Don't report an aggregated trend without checking it holds across major segments — reversed sub-trends (Simpson's Paradox) are common.
• Don't confuse statistical significance with business significance — always surface the effect size and confidence interval, not just the p-value.