Skip to content

shisuidata/ss-data-skills / skills/funnel-analysis

funnel-analysis

Verified

Use when analyzing conversion funnels across ordered business steps, such as registration, activation, payment, signup, onboarding, or checkout. Rebuilds strict ordered funnels from event data with a bundled zero-dependency script, decomposes period-over-period change into steps and into segment mix vs rate effects, detects tracking and truncation issues, and renders an HTML funnel report. 中文触发词:漏斗分析、转化漏斗、哪一步流失、转化率下降拆解。

FileKindSize
SKILL.mdskill md7.8 KB
assets/example_summary.jsonconfig975 B
references/cli.mdmarkdown1.4 KB
references/funnel-sql.mdmarkdown3.8 KB
references/input-schema.mdmarkdown2.8 KB
references/pitfalls.mdmarkdown2.3 KB
scripts/funnel.pyscript37.6 KB
scripts/sskit_io.pyscript14.9 KB
scripts/sskit_viz.pyscript36.1 KB

references/funnel-sql.md

View on GitHub
# 漏斗 SQL 模板

数据量大时,在仓库里算出有序漏斗人数,再把结果写成汇总 JSON 交给脚本。

## 最常见的错误写法

```sql
-- 错误:每一步独立去重,不要求先完成上一步
SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'register_success' THEN user_id END) AS step1,
  COUNT(DISTINCT CASE WHEN event_name = 'product_view'     THEN user_id END) AS step2,
  COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart'      THEN user_id END) AS step3
FROM dwd_user_event_d
WHERE dt BETWEEN '2026-05-01' AND '2026-05-14';
```

问题:

- 老用户、没有注册事件的用户也会计入后续步骤,后一步人数甚至可能多于前一步
- 没有顺序:先加购后浏览的用户也被算作“浏览后加购”
- 按事件日期而不是入口日期切时期:5 月 14 日注册、5 月 15 日支付的用户,支付被漏掉

## 通用写法(Spark SQL / Hive / PostgreSQL,按引擎调整时间运算)

思路:先找每个用户的入口时间,再逐步找“上一步之后、窗口之内”的最早事件。

```sql
WITH s1 AS (                                    -- 入口:按入口时间划分时期
  SELECT user_id, MIN(event_time) AS t1
  FROM dwd_user_event_d
  WHERE event_name = 'register_success'
    AND dt BETWEEN '2026-05-01' AND '2026-05-14'
  GROUP BY user_id
),
s2 AS (
  SELECT s1.user_id, s1.t1, MIN(e.event_time) AS t2
  FROM s1
  JOIN dwd_user_event_d e
    ON e.user_id = s1.user_id
   AND e.event_name = 'product_view'
   AND e.event_time >= s1.t1
   AND e.event_time <= s1.t1 + INTERVAL 7 DAYS  -- 转化窗口(PostgreSQL: INTERVAL '7 days')
  WHERE e.dt BETWEEN '2026-05-01' AND '2026-05-21' -- 分区上界 = 时期结束 + 窗口
  GROUP BY s1.user_id, s1.t1
),
s3 AS (
  SELECT s2.user_id, s2.t1, MIN(e.event_time) AS t3
  FROM s2
  JOIN dwd_user_event_d e
    ON e.user_id = s2.user_id
   AND e.event_name = 'add_to_cart'
   AND e.event_time >= s2.t2
   AND e.event_time <= s2.t1 + INTERVAL 7 DAYS
  WHERE e.dt BETWEEN '2026-05-01' AND '2026-05-21'
  GROUP BY s2.user_id, s2.t1
)
SELECT
  u.channel_id,
  COUNT(s1.user_id) AS step1,
  COUNT(s2.user_id) AS step2,
  COUNT(s3.user_id) AS step3
FROM s1
LEFT JOIN s2 ON s2.user_id = s1.user_id
LEFT JOIN s3 ON s3.user_id = s1.user_id
LEFT JOIN dim_user u ON u.user_id = s1.user_id
GROUP BY u.channel_id;                         -- 去掉 GROUP BY 得到整体行
```

注意:

- 每一步都要带分区过滤,否则全表扫描
- 时期内最晚进入的用户可能还没走完窗口:要么只统计 `t1 <= 数据截止时间 - 窗口` 的用户,要么等窗口结束再算
- 统计对象是企业、设备时,把 `user_id` 换成对应主键

## ClickHouse:windowFunnel

```sql
SELECT
  level,
  count() AS users
FROM (
  SELECT
    user_id,
    minIf(event_time, event_name = 'register_success') AS entry_time,
    windowFunnel(7 * 86400)(
      event_time,
      event_name = 'register_success',
      event_name = 'product_view',
      event_name = 'add_to_cart',
      event_name = 'order_submit',
      event_name = 'pay_success'
    ) AS level
  FROM dwd_user_event_d
  WHERE dt BETWEEN '2026-05-01' AND '2026-05-21'
  GROUP BY user_id
)
WHERE level > 0
  AND entry_time >= '2026-05-01 00:00:00'
  AND entry_time <  '2026-05-15 00:00:00'
GROUP BY level
ORDER BY level;
```

`level` 是用户最远到达的步骤。到达第 k 步的人数 = `level >= k` 的用户数之和,转换成 `counts` 时要做一次逆向累加。窗口单位与时间列类型一致(DateTime 为秒)。

Doris、StarRocks 也提供 `window_funnel` 函数,但参数顺序和条件写法与 ClickHouse 不同,使用前以对应版本的官方文档为准。

## 转成汇总 JSON

```json
{"period": "本期", "counts": [step1, step2, step3]}
{"period": "本期", "segment": {"channel_id": "channel_a"}, "counts": [ ... ]}
```

完整格式见 [输入格式](input-schema.md)。
slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack."Stars: 198kUpdated Claude CodeCodexVerifiedInstall
ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt.Stars: 194kUpdated Claude CodeCodexVerifiedInstall