SQL如何分析用户复购?(复购率、表连接)

【题目】

表名为“购买记录表”里记录某在线教育平台的用户购买记录,包含字段:用户id、购买时间、课程类型、消费金额。

问题:分析出每日首次购买用户的次月、第三月、第四月复购情况如何?

【解题思路】

1. 群组分析方法

这类复购问题的取数方式是群组分析方法经常使用的。

群组分析方法:根据用户初始行为的发生时间,将用户划分为不同的组,进而分析每个组的行为如何随时间变化而变化。

上面说的初始行为包括:登录、购买等。以购买行为举例,首先每天圈定“第一次发生购买行为”的用户作为初始用户群,然后观察这部分用户在后面每一天发生购买行为的情况(如下图)。

 

本题就是圈定“第一次发生购买行为”的用户群,然后统计这部分用户在次月、第三月、第四月依然购买的人数。

2. 窗口函数

群组分析方法对应到SQL里常用窗口函数来实现。也就是从某些维度对数据分组(partition by),然后同样也可以对每个组进行统计运算。

窗口函数使用形式如下:


窗口函数名(字段) over(partition by 字段 order by 字段)

其中,row_number() 就是窗口函数的一种,用于排名。

首先要获取“当日首次购买用户量”,也就是获取每个用户的第一次购买的日期(也就是对用户按购买时间排名,排名第1的就是第一次购买的日期)。


select 用户id,
       date(购买时间) as 日期,
       row_number() over(partition by 用户id order by 购买时间) as 购买顺序
from 购买记录表;

“购买顺序”为1时,即该用户首次购买的日期。


select 用户id,
       日期,
       购买顺序
from (
select 用户id,
       date(购买时间) as 日期,
       row_number() over(partition by 用户id order by 购买时间) as 购买顺序
from 购买记录表
) as t0
where 购买顺序 = 1;

 

接下来获取这些用户在次月、第三月、第四月的购买记录,使之与原始“购买记录表”进行左联结即可。

此处为了分别获取每个用户在次月、第三月、第四月的购买记录,需要与“购买记录表”联结三次,并且在最终按日期进行汇总分析。


select t1.日期,
       count(distinct t1.用户id) as 当日首次购买用户量,
       count(distinct t2.用户id) as 次月复购用户量,
       count(distinct t3.用户id) as 第三月复购用户量,
       count(distinct t4.用户id) as 第四月复购用户量
from (
select 用户id,
       日期,
       购买顺序
from (
select 用户id,
       date(购买时间) as 日期,
       row_number() over(partition by 用户id order by 购买时间) as 购买顺序
from 购买记录表
) as t0
where 购买顺序 = 1
) as t1
left join 购买记录表 as t2 on timestampdiff(month, t1.日期, date(t2.购买时间)) = 1 and t1.用户id = t2.用户id
left join 购买记录表 as t3 on timestampdiff(month, t1.日期, date(t3.购买时间)) = 2 and t1.用户id = t3.用户id
left join 购买记录表 as t4 on timestampdiff(month, t1.日期, date(t4.购买时间)) = 3 and t1.用户id = t4.用户id
group by t1.日期
order by t1.日期;

 

【本题考点】

1.遇到复购、留存、流失这类业务问题,要想到用群组分析方法

2.群组分析方法对应在SQL里用窗口函数实现

3.SQL常用函数的使用,包括:count、date、timestampdiff、distinct。

留存率的求解方式与其类似,唯一的区别在于要展示当日首次的购买用户量和时间,所以需要用到窗口函数进行排序,后面的次月复购逻辑上也就是次月留存的用户

留存率计算:

五大常考SQL面试题_Begin to change的博客-CSDN博客

 

好的,用户现在问的是希望得到一个数据分析师助理可以实际参与科技有限公司项目的例子。首先,我需要回顾一下之前的对话历史,看看有没有相关的信息可以参考。之前用户询问了AB测试在软件测试中的应用,我详细介绍了不同场景、实施流程以及注意事项,还举了实例。现在用户的问题转向数据分析师助理的实际项目参与,可能需要一个具体的案例来说明。 首先,我需要确定用户的需求。他们可能是一名正在学习数据分析的学生,或者刚入行的新人,想了解在实际工作中如何应用所学技能。因此,例子需要具体、可操作,并且涵盖数据分析的关键步骤,比如数据清洗、分析、可视化、报告等。同时,可能需要结合科技公司的常见项目类型,比如用户行为分析、产品优化、市场策略等。 接下来,我应该考虑科技公司中数据分析师助理可能参与的项目类型。比如用户画像构建、销售预测、A/B测试结果分析、运营优化等。选择用户画像构建作为例子可能比较合适,因为这是一个常见的项目,能够展示数据处理的各个阶段,并且结果对产品、市场团队都有直接帮助。 然后,需要确定例子的结构。应该包括项目背景、助理的具体职责、使用的工具和技术、成果输出以及经验总结。这样用户能清晰看到每个阶段的任务和所需的技能。例如,项目背景可以是一个电商平台希望提升,数据分析师助理负责处理数据、分析用户行为,并输出报告。 在职责部分,需要细化到数据清洗、特征工程、分析模型构建、可视化等具体任务。同时,使用Python、SQL、Tableau等工具是行业标准,应该提到。成果方面,可以包括用户分层报告、高价值用户特征、推荐策略建议等,这样能体现实际应用价值。 还需要考虑用户可能关心的点,比如如何与团队协作,如何处理数据中的问题,如何验证分析结果的有效性。例如,在数据清洗阶段可能会遇到缺失值或异常值,助理需要学习如何处理这些问题;在特征工程中需要与产品经理沟通,确保选取的特征符合业务需求。 最后,总结部分需要强调通过这样的项目,助理能够积累哪些经验,比如工具使用、跨部门协作、数据驱动决策的意识等。这样不仅回答了用户的问题,还为他们提供了职业发展的参考。 需要确保整个例子真实可靠,步骤清晰,并且语言简洁易懂。避免使用过多专业术语,但必要时解释清楚,比如p-value、RFM模型等,帮助用户理解概念。同时,结合之前的AB测试例子,可以提到在推荐策略中可能进行A/B测试来验证效果,保持回答的一致性。 以下是一个数据分析师助理在科技有限公司中可实际参与的项目案例,涵盖**真实工作场景**、**技术工具应用**和**成果交付**全流程: --- ### **项目名称:电商平台用户行为分析提升** **项目背景** 某科技公司旗下电商平台发现近3个月用户下降5%,需通过数据分析定位问题并制定优化策略。 --- #### **数据分析师助理的核心参与环节** ##### 1. **数据清洗与预处理** - **任务**:从MySQL数据库提取用户订单(`order_table`)、行为日志(`user_log`)和商品信息(`product_info`) - **具体工作**: - 使用Python的`pandas`处理缺失值(如补全用户地域字段) - 剔除异常数据(如订单金额为负值的记录) - 关联多生成宽: ```sql SELECT o.user_id, o.order_time, p.category, l.click_count FROM order_table o JOIN product_info p ON o.product_id = p.id JOIN user_log l ON o.user_id = l.user_id ``` ##### 2. **用户分层与特征分析** - **工具**:Python(`scikit-learn`聚类算法)、Tableau可视化 - **输出成果**: - **RFM模型**划分用户价值(代码示例): ```python # 计算R(最近买时间)、F(买频次)、M(消费金额) rfm = df.groupby('user_id').agg({ 'order_time': lambda x: (pd.Timestamp.now() - x.max()).days, 'order_id': 'count', 'amount': 'sum' }) ``` - 发现**高价值用户流失特征**: - 过去30天未登录的用户中,35%曾买单价>500元的3C类商品 - 流失用户的平均页面停留时间比活跃用户低40% ##### 3. **A/B测试支持** - **协作任务**:为产品经理设计优惠券发放策略的AB测试 - 实验组(B组):向流失高价值用户推送满1000减150元券 - 对照组(A组):维持原价 - **分析方法**: - 使用SQL计算两组用户的**差异**: ```sql SELECT group_type, COUNT(DISTINCT user_id) AS total_users, SUM(CASE WHEN repurchase_flag=1 THEN 1 ELSE 0 END)/COUNT(*) AS repurchase_rate FROM ab_test_table GROUP BY group_type ``` - 通过**卡方检验**验证显著性(Python代码): ```python from scipy.stats import chi2_contingency observed = [[120, 380], [200, 300]] # [数, 未数] chi2, p, _, _ = chi2_contingency(observed) ``` ##### 4. **报告输出与策略建议** - **交付物**:PPT报告 + Excel明细数据 - **核心结论**: - 高价值用户流失主因:**缺乏高单价商品精准推荐**(通过关联规则挖掘发现:买手机的客户常同时买耳机,但未触发推荐) - 建议方案: - 优化推荐算法:对3C类用户增加跨品类推荐(如手机→耳机/充电宝) - 建立流失预警模型:当用户连续7天未登录时,触发短信唤醒 --- #### **技术栈与软技能要求** | **环节** | **工具/技能** | |------------------|--------------------------------------| | 数据提取 | SQL(窗口函数、多JOIN) | | 数据分析 | Python(pandas/numpy/scipy)、Excel | | 可视化 | Tableau/Power BI(制作动态仪盘) | | 协作沟通 | 与产品/运营团队对齐指标定义、需求拆解 | --- #### **项目成果** - 3个月内试点用户提升**8.2%** - 构建标准化用户分析看板,节省后续同类需求60%时间成本 --- ### **新手学习路径建议** 1. **优先掌握**:SQL杂查询 + Python数据清洗(可参考《利用Python进行数据分析》) 2. **实战案例**:Kaggle电商数据集(如《Brazilian E-Commerce Public Dataset》) 3. **思维培养**:关注指标波动背后的业务逻辑(例如:下降是否与物流时效相关?) 通过此类项目,助理不仅能积累技术经验,还能深入理解**数据驱动业务决策**的实际价值。 可以给我生成流程图吗
最新发布
03-27
<think>好的,用户现在请求生成一个流程图来补充之前提供的项目案例。我需要先回顾之前的回答,确保流程图与内容一致。之前的案例是关于电商用户提升的项目,涵盖了数据清洗、用户分层、A/B测试和报告输出几个阶段。 首先,我需要确定流程图的步骤是否覆盖所有关键环节。用户可能希望看到一个从项目开始到结束的完整流程,包括数据获取、处理、分析、测试和结果应用。同时,流程图需要符合中文习惯,并且结构清晰,容易理解。 接下来,考虑使用什么工具或方法来生成流程图。虽然用户可能期望直接得到一个图,但在文本环境中,我需要用文字描述流程图的结构和步骤,或者建议使用Mermaid语法。由于用户没有明确指定格式,可能需要询问是否接受Mermaid代码,或者提供文本描述。但根据用户的问题“可以给我生成流程图吗”,可能希望得到可渲染的图,但在这里只能提供文本描述或代码。 然后,我需要确保流程图的逻辑正确。从项目启动开始,数据收集、清洗、分析、模型应用、测试、结果和优化,每个步骤之间要有连贯性。同时,需要包括关键决策点,比如是否需要A/B测试,或者策略是否有效。 可能还需要注意用户的实际需求,他们可能希望流程图能够展示数据分析师助理在项目中的具体职责,比如在哪个阶段参与哪些任务。例如,助理主要负责数据清洗和预处理,协助A/B测试设计,参与结果分析等。 另外,要考虑流程图的简洁性。避免步骤过多,但又要全面覆盖项目的主要阶段。使用菱形示决策点,比如验证数据质量是否达标,分析结果是否显著等。 最后,确认流程图的语言和术语与之前的案例一致,比如提及RFM模型、卡方检验、提升等关键点。确保用户能够将流程图与之前的详细案例对应起来,加深理解。 总结下来,流程图的步骤应包括: 1. 项目启动与目标定义 2. 数据收集与清洗 3. 用户分层与特征分析 4. 模型构建与假设提出 5. A/B测试设计与执行 6. 结果分析与验证 7. 报告输出与策略实施 8. 效果监控与迭代优化 每个步骤之间用箭头连接,关键决策点用菱形框示。例如,在数据清洗后需要检查数据质量,如果不合格则返回重新清洗;分析结果是否显著决定是否调整策略等。 现在,我需要将这些步骤转化为Mermaid语法,确保语法正确,结构清晰。同时,提供简要说明,帮助用户理解每个步骤的意义。最后,建议用户使用支持Mermaid的工具渲染图,比如在Markdown编辑器或相关网站中粘贴代码查看结果。</think>以下是该项目的**流程图Mermaid代码**,可直接粘贴至支持Mermaid的编辑器(如Typora、VSCode插件、GitLab/GitHub等)生成可视化流程图: ```mermaid graph TD A[项目启动] --> B[数据收集] B --> C{数据质量<br>是否达标?} C -->|否| D[数据清洗] C -->|是| E[用户分层分析] D --> C E --> F[构建RFM模型] F --> G[识别高价值流失用户] G --> H[提出假设:精准推荐<br>可提升] H --> I[设计A/B测试方案] I --> J[执行测试] J --> K{结果是否<br>显著?} K -->|否| L[调整策略参数] K -->|是| M[输出分析报告] L --> I M --> N[实施推荐算法优化] N --> O[监控变化] O --> P{目标达成?} P -->|否| Q[迭代优化] P -->|是| R[项目结项] style A fill:#4CAF50,color:white style N fill:#2196F3,color:white style R fill:#FF5722,color:white ``` --- ### **流程图关键节点说明** 1. **数据质量验证**:清洗后需检查字段完整性、异常值占比等硬性指标 2. **显著性判断**:A/B测试结果需通过统计检验(如p-value<0.05) 3. **迭代优化**:典型场景包括: - 调整推荐算法参数 - 优化优惠券面额/使用门槛 - 修改用户唤醒触达频 --- ### **延伸学习建议** 1. 用`draw.io`绘制更详细的**泳道图**,区分数据/产品/技术团队的协作边界 2. 在Kaggle数据集练习时,尝试用`Plotly`实现动态流程图+分析结果联动演示 如果需要调整流程细节或补充特定环节,请随时告知!
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值