Exploratory Analysis

• Center: mean, median
• Spread: standard deviation, IQR
• Shape: skew, outliers
• Relationships: correlation (Pearson/Spearman)

• I can compute robust summaries (median, IQR) and explain skew.
• I can segment metrics and interpret differences with context.
• I can spot and handle outliers without losing signal.
• I can explain correlation vs. causation and avoid traps.
• I can build a clear EDA report with recommendations.

Goal: Understand order_value distribution and spot outliers.

-- Bucket order values into ranges to see shape quickly
WITH b AS (
  SELECT
    CASE
      WHEN order_value < 10 THEN '0-10'
      WHEN order_value < 50 THEN '10-50'
      WHEN order_value < 100 THEN '50-100'
      ELSE '100+'
    END AS bucket
  FROM orders
  WHERE order_date >= DATE '2024-01-01'
)
SELECT bucket, COUNT(*) AS n, ROUND(100.0*COUNT(*)/SUM(COUNT(*)) OVER(), 1) AS pct
FROM b
GROUP BY bucket
ORDER BY CASE bucket WHEN '0-10' THEN 1 WHEN '10-50' THEN 2 WHEN '50-100' THEN 3 ELSE 4 END;

Interpretation tips: Large 100+ share suggests heavy-tails. Consider log-transform or robust stats.

Goal: Compare metrics by acquisition_channel.

SELECT acquisition_channel,
       COUNT(*) AS users,
       AVG(first_order_value) AS avg_first_order,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY first_order_value) AS median_first_order
FROM users
GROUP BY acquisition_channel
ORDER BY users DESC;

If your SQL dialect lacks PERCENTILE_CONT, compute median in Python after extracting grouped data.

import pandas as pd
# df includes numerical columns: sessions, orders, revenue, days_active
corr = df[["sessions","orders","revenue","days_active"]].corr(method="pearson")
print(corr.round(2))

# Quick pair check to avoid spurious correlation due to size
size = df["sessions"]
rate = (df["orders"] / df["sessions"]).fillna(0)
print("corr(sessions, orders)=", size.corr(df["orders"]).round(2))
print("corr(sessions, conversion_rate)=", size.corr(rate).round(2))

Interpretation: A high sessions-orders correlation may just reflect scale. Check rate metrics too.

import numpy as np
q1 = df["order_value"].quantile(0.25)
q3 = df["order_value"].quantile(0.75)
iqr = q3 - q1
lower = q1 - 1.5*iqr
upper = q3 + 1.5*iqr
outliers = df[(df["order_value"] < lower) | (df["order_value"] > upper)]
print(len(outliers), "outliers")

Action: Investigate top outliers for data errors vs. legitimate big orders before trimming or winsorizing.

Goal: Compute step-through rates for signup → verify → purchase within 14 days.

WITH s AS (
  SELECT user_id, MIN(event_time) AS signup_ts
  FROM events WHERE event = 'signup'
  GROUP BY user_id
), v AS (
  SELECT user_id, MIN(event_time) AS verify_ts
  FROM events WHERE event = 'verify_email'
  GROUP BY user_id
), p AS (
  SELECT user_id, MIN(event_time) AS purchase_ts
  FROM events WHERE event = 'purchase'
  GROUP BY user_id
)
SELECT
  COUNT(s.user_id) AS signups,
  COUNT(v.user_id) AS verified,
  COUNT(p.user_id) AS purchasers,
  ROUND(COUNT(v.user_id)*1.0/COUNT(s.user_id), 3) AS verify_rate,
  ROUND(COUNT(p.user_id)*1.0/COUNT(v.user_id), 3) AS purchase_given_verify
FROM s
LEFT JOIN v ON v.user_id = s.user_id AND v.verify_ts <= s.signup_ts + INTERVAL '14 day'
LEFT JOIN p ON p.user_id = s.user_id AND p.purchase_ts <= s.signup_ts + INTERVAL '14 day';

Note: Use time windows to keep comparisons fair.

# df has columns: user_id, first_month (YYYY-MM), activity_month (YYYY-MM)
pivot = (df
  .assign(cohort_age=lambda d: (
      pd.PeriodIndex(d["activity_month"], freq="M") -
      pd.PeriodIndex(d["first_month"],   freq="M")).astype(int))
  .groupby(["first_month","cohort_age"])['user_id']
  .nunique()
  .unstack(fill_value=0))

sizes = df.groupby('first_month')['user_id'].nunique()
retention = (pivot.div(sizes, axis=0).round(3))
print(retention.head())

Interpretation: Compare early-month retention across cohorts to see if quality is improving.

• Recalculate metrics with a tiny sample by hand to verify logic.
• Cross-check SQL vs. Python results on the same subset.
• Use COUNT(DISTINCT ...) carefully; confirm key uniqueness first.
• Plot or bucket numeric fields; shape often reveals data issues quickly.

• All metrics have clear definitions and time windows.
• At least one outlier investigation included.
• Findings are actionable (suggested experiments or product changes).
• Assumptions and data limitations are stated.

1. Context & questions
2. Data audit
3. Distributions & segments
4. Relationships
5. Funnels & cohorts
6. Hypotheses
7. Takeaways & actions