Back to Details

segment-users

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese
Read your database schema, generate queries for every behavioral segment, and give you the exact action to take with each group. No analytics platform required.
读取你的数据库模式，为每个行为分群生成查询语句，并为你提供针对每个用户组的确切行动方案。无需使用分析平台。

Phase 1: Read the Schema

阶段1：读取数据库模式

Check the codebase for the user model. The signals you need:
  • Usage metric — What counts as activity? Credits consumed, API calls, generations, logins, content created. Find the field.
  • Timestamps — 
    created_at
    , 
    last_active_at
    , 
    last_login_at
    . Any date fields on the user.
  • Plan / billing — Free vs paid, plan name, subscription status.
  • ORM — Prisma, Drizzle, Mongoose, raw SQL? Match your output to what's already in the codebase.
If any of the three signals are missing (usage, timestamps, plan), tell the user before generating queries. Don't fabricate fields that don't exist in the schema.
检查代码库中的用户模型。你需要关注以下信号：
  • 使用指标 —— 什么算作活跃行为？消耗的积分、API调用次数、生成内容数、登录次数、创建的内容。找到对应的字段。
  • 时间戳 —— 
    created_at
    、
    last_active_at
    、
    last_login_at
    。用户表中的任何日期字段。
  • 套餐/计费信息 —— 免费版 vs 付费版、套餐名称、订阅状态。
  • ORM —— Prisma、Drizzle、Mongoose还是原生SQL？输出内容要与代码库中已有的技术栈匹配。
如果三个核心信号（使用指标、时间戳、套餐信息）中有任何一个缺失，请在生成查询前告知用户。不要编造模式中不存在的字段。

Phase 2: Define the Segments

阶段2：定义用户分群

Use whatever signals are available. Don't force a segment if the data isn't there.
使用所有可用的信号。如果数据不支持，不要强行创建分群。

Usage-Based (requires usage metric)

基于使用情况（需要使用指标）

Power Users — top 10% by usage. These people love the product. Treat them differently than everyone else.
Active — used in the last 7 days, above median usage.
Casual — used in the last 30 days, below median usage.
Dormant — no usage in 7–30 days. The clock is ticking.
Churned — no usage in 30+ days. Harder to recover, not impossible.
核心用户 —— 使用量排名前10%的用户。这些用户是产品的忠实爱好者，需要区别对待。
活跃用户 —— 过去7天内有使用行为，且使用量高于中位数。
普通用户 —— 过去30天内有使用行为，且使用量低于中位数。
休眠用户 —— 7-30天内无使用行为。流失风险正在上升。
流失用户 —— 30天以上无使用行为。挽回难度较大，但并非不可能。

Lifecycle (requires 
created_at
)

基于生命周期（需要
created_at
字段）

New — signed up < 7 days ago. Haven't formed a habit yet.
Onboarding — 7–14 days, hasn't hit the "aha moment" (define this as hitting a specific usage threshold based on the product).
Established — 14–60 days, regular usage pattern.
Veteran — 60+ days, consistent activity. The most valuable free-tier users.
新用户 —— 注册时间少于7天。尚未形成使用习惯。
待引导用户 —— 注册7-14天，尚未达到「惊喜时刻」（根据产品定义为达到特定使用阈值）。
稳定用户 —— 注册14-60天，有规律的使用模式。
资深用户 —— 注册60天以上，活跃度稳定。是免费版中最有价值的用户群体。

Revenue (requires plan data)

基于收入（需要套餐数据）

Free Tier — never paid.
Paying — active subscription or purchased credits.
At Risk — paying but usage declining in the last 14 days. The most urgent segment.
Churned Paid — was paying, subscription ended. Revenue already lost.
免费版用户 —— 从未付费。
付费用户 —— 有活跃订阅或已购买积分。
高风险用户 —— 付费用户，但过去14天内使用量下降。这是最需要关注的分群。
已流失付费用户 —— 曾是付费用户，现已取消订阅。已造成收入损失。

Phase 3: Generate the Queries

阶段3：生成查询语句

For each applicable segment, generate both SQL and ORM. Adapt to the actual schema — no placeholder column names.
sql
-- Power Users: top 10% by credits consumed
SELECT *, (initial_credits - credits) AS credits_used
FROM users
ORDER BY credits_used DESC
LIMIT (SELECT COUNT(*) / 10 FROM users);

-- Dormant: was active, silent for 7-30 days
SELECT * FROM users
WHERE last_active_at < NOW() - INTERVAL '7 days'
  AND last_active_at > NOW() - INTERVAL '30 days';

-- At Risk: paying user, usage dropped below half their average
SELECT * FROM users
WHERE plan != 'free'
  AND (initial_credits - credits) < (
    SELECT AVG(initial_credits - credits) * 0.5
    FROM users WHERE plan != 'free'
  );
typescript
// Prisma: dormant users
const dormant = await prisma.user.findMany({
  where: {
    lastActiveAt: {
      lt: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000),
      gt: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000),
    },
  },
});
Always include a count query so the user can see the full distribution immediately:
sql
SELECT 'power_users' AS segment, COUNT(*) AS count FROM users WHERE ...
UNION ALL
SELECT 'active'      AS segment, COUNT(*) AS count FROM users WHERE ...
UNION ALL
SELECT 'dormant'     AS segment, COUNT(*) AS count FROM users WHERE ...
UNION ALL
SELECT 'churned'     AS segment, COUNT(*) AS count FROM users WHERE ...
为每个适用的分群同时生成SQL和ORM查询语句。适配实际的数据库模式——不要使用占位符列名。
sql
-- 核心用户：积分消耗排名前10%
SELECT *, (initial_credits - credits) AS credits_used
FROM users
ORDER BY credits_used DESC
LIMIT (SELECT COUNT(*) / 10 FROM users);

-- 休眠用户：曾活跃，7-30天无行为
SELECT * FROM users
WHERE last_active_at < NOW() - INTERVAL '7 days'
  AND last_active_at > NOW() - INTERVAL '30 days';

-- 高风险用户：付费用户，使用量低于平均水平的一半
SELECT * FROM users
WHERE plan != 'free'
  AND (initial_credits - credits) < (
    SELECT AVG(initial_credits - credits) * 0.5
    FROM users WHERE plan != 'free'
  );
typescript
// Prisma：休眠用户查询
const dormant = await prisma.user.findMany({
  where: {
    lastActiveAt: {
      lt: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000),
      gt: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000),
    },
  },
});
务必包含统计查询，让用户可以立即查看各分群的分布情况：
sql
SELECT 'power_users' AS segment, COUNT(*) AS count FROM users WHERE ...
UNION ALL
SELECT 'active'      AS segment, COUNT(*) AS count FROM users WHERE ...
UNION ALL
SELECT 'dormant'     AS segment, COUNT(*) AS count FROM users WHERE ...
UNION ALL
SELECT 'churned'     AS segment, COUNT(*) AS count FROM users WHERE ...

Phase 4: Recommend Actions

阶段4：推荐行动方案

One action per segment. Concrete, not vague.
Power Users — upgrade offer or exclusive early access. They're already bought in. A personal email from the founder converts here better than any automated campaign.
Active — surface features they haven't used. In-app tooltip or email with one specific feature. Don't pitch; educate.
Casual — re-engage with the one thing they got value from. Remind them what worked.
Dormant — win-back email. One specific hook: what's changed, what's new. Not "we miss you."
Churned — short survey, then an incentive. Find out why before trying to recover them.
New — onboarding sequence. Define the "aha moment" threshold and build the first 3 days around reaching it.
At Risk — personal outreach before cancellation. A direct message from a human beats automation here.
每个分群对应一个具体行动，避免模糊表述。
核心用户 —— 提供升级优惠或专属提前访问权限。他们已经认可产品，创始人的个人邮件比任何自动化营销活动的转化率都更高。
活跃用户 —— 展示他们尚未使用的功能。通过应用内提示或邮件介绍一个具体功能，重点是教育而非推销。
普通用户 —— 用他们之前从中获得价值的功能重新吸引他们。提醒他们产品曾经带来的帮助。
休眠用户 —— 发送赢回邮件。聚焦一个具体的吸引点：产品有哪些更新、新增了什么功能。不要用「我们想念你」这类泛泛的表述。
流失用户 —— 先进行简短调研，再提供激励措施。在尝试挽回前先了解他们流失的原因。
新用户 —— 引导序列。定义「惊喜时刻」的阈值，并围绕达成该阈值设计前3天的引导流程。
高风险用户 —— 在用户取消订阅前进行人工沟通。人工直接消息的效果远好于自动化流程。

Phase 5: Output Format

阶段5：输出格式

Print distribution, then queries, then actions.
USER SEGMENTS — [project name]
════════════════════════════════════
CURRENT DISTRIBUTION
────────────────────────────────────
Power Users:    [N] users  ([X]%)
Active:         [N] users  ([X]%)
Casual:         [N] users  ([X]%)
Dormant:        [N] users  ([X]%)
Churned:        [N] users  ([X]%)
────────────────────────────────────
Total:          [N] users
════════════════════════════════════
If 
/ship-email
is installed, offer to generate email templates for each segment after the queries.
先输出分群分布，再输出查询语句，最后是行动方案。
用户分群 —— [项目名称]
════════════════════════════════════
当前分群分布
────────────────────────────────────
核心用户:    [N] 位用户  ([X]%)
活跃用户:         [N] 位用户  ([X]%)
普通用户:         [N] 位用户  ([X]%)
休眠用户:        [N] 位用户  ([X]%)
流失用户:        [N] 位用户  ([X]%)
────────────────────────────────────
总计:          [N] 位用户
════════════════════════════════════
如果已安装
/ship-email
，在生成查询语句后可主动提出为每个分群生成邮件模板。

Verify

验证清单

[ ] User model read from codebase — no invented fields
[ ] Usage metric identified and defined
[ ] Only segments with available signals generated
[ ] SQL + ORM queries output for each segment
[ ] Count queries included so distribution is visible
[ ] At Risk segment flagged if any paying users exist
[ ] Action recommendation per segment — specific, not generic
[ ] All column names in queries match the actual schema
See references/guide.md for RFM scoring, cohort retention analysis, and churn prediction patterns.
[ ] 已从代码库读取用户模型 —— 未编造字段
[ ] 已识别并定义使用指标
[ ] 仅生成了有可用数据支持的分群
[ ] 为每个分群输出了SQL + ORM查询语句
[ ] 包含统计查询，可查看分群分布
[ ] 若存在付费用户，已标记高风险分群
[ ] 每个分群的行动推荐具体明确，而非泛泛而谈
[ ] 查询语句中的所有列名与实际数据库模式匹配
查看 references/guide.md 了解RFM评分、 cohort留存分析和流失预测模式。