菜单
Build and analyze conversion funnels for product analytics. Covers funnel construction, drop-off diagnosis, segmented funnel analysis, and conversion optimization frameworks. Includes SQL funnel patterns, Python visualization, and interview approach for funnel questions.
用 Codex 或 Claude 帮你安装 复制这段 Prompt，粘贴到 Codex、Claude 或其他助手里，让它检查 Skill 页面并帮你完成安装。
基于 SOC 职业分类
| name | funnel-analysis-conversion |
| description | Build and analyze conversion funnels for product analytics. Covers funnel construction, drop-off diagnosis, segmented funnel analysis, and conversion optimization frameworks. Includes SQL funnel patterns, Python visualization, and interview approach for funnel questions. |
| license | MIT |
| metadata | {"skill-author":"ds-interview-mcp"} |
Funnel analysis tracks users through a sequence of ordered steps toward a goal (signup, purchase, activation). It answers the most fundamental product question: "Where are we losing users, and why?" Every product analytics interview includes a funnel question, and every product team relies on funnels to prioritize work.
Key concepts:
Use this skill when:
| Product | Funnel Steps |
|---|---|
| E-commerce | Visit → Product View → Add to Cart → Checkout → Purchase |
| SaaS | Landing Page → Signup → Onboarding → Activation → Subscription |
| Social | App Install → Signup → Profile Setup → First Post → D7 Retained |
| Marketplace | Search → View Listing → Contact Seller → Transaction |
| Content | Page View → Scroll 50% → Scroll 100% → Share/Save |
-- Users must complete each step within 24 hours of the previous step
WITH step1 AS (
SELECT user_id, MIN(event_time) AS t1
FROM events
WHERE event_name = 'landing_page_view'
AND event_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY user_id
),
step2 AS (
SELECT s1.user_id, MIN(e.event_time) AS t2
FROM step1 s1
JOIN events e ON s1.user_id = e.user_id
AND e.event_name = 'signup_complete'
AND e.event_time > s1.t1
AND e.event_time < s1.t1 + INTERVAL '24 hours'
GROUP BY s1.user_id
),
step3 AS (
SELECT s2.user_id, MIN(e.event_time) AS t3
FROM step2 s2
JOIN events e ON s2.user_id = e.user_id
AND e.event_name = 'onboarding_complete'
AND e.event_time > s2.t2
AND e.event_time < s2.t2 + INTERVAL '24 hours'
GROUP BY s2.user_id
),
step4 AS (
SELECT s3.user_id, MIN(e.event_time) AS t4
FROM step3 s3
JOIN events e ON s3.user_id = e.user_id
AND e.event_name = 'first_value_action'
AND e.event_time > s3.t3
AND e.event_time < s3.t3 + INTERVAL '7 days'
GROUP BY s3.user_id
)
SELECT
'Landing Page' AS step, COUNT(*) AS users FROM step1 UNION ALL
SELECT 'Signup', COUNT(*) FROM step2 UNION ALL
SELECT 'Onboarding', COUNT(*) FROM step3 UNION ALL
SELECT 'Activation', COUNT(*) FROM step4;
-- How long does each step take? Slow steps → friction → drop-off
WITH funnel_times AS (
SELECT
user_id,
MIN(CASE WHEN event_name = 'signup_start' THEN event_time END) AS signup_start,
MIN(CASE WHEN event_name = 'signup_complete' THEN event_time END) AS signup_complete,
MIN(CASE WHEN event_name = 'onboarding_done' THEN event_time END) AS onboarding_done
FROM events
GROUP BY user_id
)
SELECT
PERCENTILE_CONT(0.50) WITHIN GROUP (
ORDER BY EXTRACT(EPOCH FROM signup_complete - signup_start)
) AS median_signup_seconds,
PERCENTILE_CONT(0.90) WITHIN GROUP (
ORDER BY EXTRACT(EPOCH FROM signup_complete - signup_start)
) AS p90_signup_seconds,
PERCENTILE_CONT(0.50) WITHIN GROUP (
ORDER BY EXTRACT(EPOCH FROM onboarding_done - signup_complete) / 3600.0
) AS median_onboarding_hours
FROM funnel_times
WHERE signup_complete IS NOT NULL;
import pandas as pd
import matplotlib.pyplot as plt
def plot_funnel(steps: list[str], users: list[int], title: str = "Conversion Funnel") -> None:
"""Plot a horizontal funnel chart with conversion rates.
Args:
steps: List of step names in order
users: List of user counts at each step
title: Chart title
"""
fig, ax = plt.subplots(figsize=(10, 6))
# Compute metrics
overall_conv = [100.0 * u / users[0] for u in users]
step_conv = [100.0] + [100.0 * users[i] / users[i-1] for i in range(1, len(users))]
# Horizontal bar chart
y_pos = range(len(steps) - 1, -1, -1)
colors = plt.cm.Blues([(0.3 + 0.7 * c / 100) for c in overall_conv])
bars = ax.barh(y_pos, users, color=colors, edgecolor="white", height=0.7)
# Annotate bars
for i, (bar, step, count, oc, sc) in enumerate(
zip(bars, steps, users, overall_conv, step_conv)
):
label = f"{count:,} ({oc:.1f}% overall"
if i > 0:
label += f", {sc:.1f}% from prev"
label += ")"
ax.text(bar.get_width() + max(users) * 0.01, bar.get_y() + bar.get_height() / 2,
label, va="center", fontsize=10)
ax.set_yticks(y_pos)
ax.set_yticklabels(steps, fontsize=12)
ax.set_xlabel("Users")
ax.set_title(title)
ax.set_xlim(0, max(users) * 1.5)
plt.tight_layout()
plt.show()
def funnel_comparison(segments: dict[str, list[int]], steps: list[str]) -> pd.DataFrame:
"""Compare funnel conversion across segments.
Args:
segments: Dict mapping segment name to list of user counts per step
steps: List of step names
Returns:
DataFrame with step-to-step and overall conversion per segment
"""
rows = []
for segment, users in segments.items():
for i, step in enumerate(steps):
rows.append({
"segment": segment,
"step": step,
"users": users[i],
"overall_pct": round(100.0 * users[i] / users[0], 1),
"step_pct": round(100.0 * users[i] / users[i-1], 1) if i > 0 else 100.0,
})
return pd.DataFrame(rows)
When a funnel step has an unexpectedly high drop-off:
| Funnel Stage | Common Causes |
|---|---|
| Landing → Signup | Unclear value prop, too many fields, no social login |
| Signup → Onboarding | Onboarding too long, unclear next steps, permission walls |
| Onboarding → Activation | User doesn't reach "aha moment", no personalization |
| Activation → Conversion | Pricing friction, no urgency, unclear upgrade path |
For a funnel interview question ("Instagram stories views are down — diagnose this"):
Design, analyze, and interpret A/B tests and controlled experiments. Calculate sample sizes, run power analysis, detect common pitfalls (peeking, multiple comparisons, network effects), and apply advanced techniques like CUPED variance reduction and switchback experiments. For product experimentation, feature launches, and causal inference.
Structured frameworks and strategies for behavioral interviews at top tech companies. Covers STAR method, story banking, leadership principles mapping, and common behavioral question categories. Tailored for data analyst and product analytics roles at Meta, Google, Amazon, Airbnb, Netflix, and similar companies.
Churn analysis and prediction for product analytics. Covers churn definition frameworks, survival analysis, RFM segmentation, predictive modeling, and retention intervention strategies. Includes SQL churn queries, Python survival curves, and feature engineering for churn prediction models.
Perform cohort analysis and retention measurement for product analytics. Build retention curves, triangle retention tables, heatmaps, and survival analysis. Covers SQL retention queries, Python visualization, and frameworks for diagnosing and improving retention across different product types.
Define, measure, and optimize product metrics. Apply AARRR pirate metrics, HEART framework, North Star metrics, and Goal-Signal-Metric (GSM) process. For product analytics, growth measurement, feature launch evaluation, and KPI design at tech companies.
Frameworks and structured approaches for product sense interview questions. Covers CIRCLES, metric definition, root cause analysis, feature prioritization, and product case study approaches. Essential for PM and product analytics interviews at Meta, Google, Airbnb, and other top tech companies.