《用户订阅转化率跟踪与数据分析：实战案例解析》

《用户订阅转化率跟踪与数据分析：实战案例解析》案例描述项目要求项目目标第一步：子查询第二步：主查询第三步：数据洞察免费转付费转化率指标注册后首次参与的平均持续时间首次参与后首次购买的平均持续时间其他分析视角（1）注册人数最多的前3个月份以及对应的注册数（2）每周的每一天（星期一到星期日）的注册人数分布（3）每个月的注册累积数和增长速率（4）创建一个透视表来分析每个月份（5）计算注册后当天、1-7天、7-30天和30天以上的观看比例（6）计算观看后当天、1-7天、7-30天和30天以上的付费转化比例（7）每个月转化的累积数和增长速率

案例描述

通过分析平台上观看视频的学生中，有多少人在开始观看课程后最终购买了订阅，即免费转付费的转化率。

数据包括学生注册日期、参与日期以及购买订阅的日期。

通过这个项目，你将深入了解学生行为模式，并为公司提供有价值的洞察和建议。

项目要求

MySQL Workbench 8.0
Python

项目目标

在平台上观看了讲座的学生的免费转付费转化率是多少？
注册日期和学生首次观看讲座的日期（首次参与日期）之间的平均间隔时间是多少？
首次观看讲座的日期和学生首次购买订阅的日期之间的平均间隔时间是多少？
我们如何解释这些结果，它们的含义是什么？

第一步：子查询

导入db_course_conversions数据库

然后，创建一个新的结果数据集，包括以下列：

student_id – (int) 学生的唯一标识
date_registered – (date) 学生在平台上注册的日期
first_date_watched – (date) 首次参与日期
first_date_purchased – (date) 首次购买日期（如果没有购买则为NULL）
date_diff_reg_watch – (int) 注册日期和首次参与日期之间的天数差
date_diff_watch_purch – (int) 首次参与日期和首次购买日期之间的天数差（如果没有购买则为NULL）

提示：研究MySQL中的DATEDIFF函数。

注意以下的Venn图

你检索到的结果集应该包括进入图中阴影区域的学生ID。此外，你的目标是确定已观看过讲座的学生的转化率。

因此，过滤你的结果集，使得首次参与讲座日期早于（或等于）首次购买日期。

检查：结果集中的记录数应为20,255。

首先，记得导入db_course_conversions数据库，并刷新。使用USE关键字将命名数据库用作默认（当前）数据库。

使用MIN聚合函数找到首次参与和购买日期。
应用DATEDIFF函数查看各自天数的差异。


1
SELECT 
2
    ???,
3
    ???,
4
    MIN(???) AS first_date_watched,
5
    MIN(???) AS first_date_purchased,
6
    DATEDIFF(???) AS days_diff_reg_watch,
7
    DATEDIFF(???) AS days_diff_watch_purch

考虑如何将三个表格连接起来，检索出Venn图中突出显示的记录


xxxxxxxxxx
6
1
FROM
2
    student_engagement e
3
        ???
4
    student_info i ON ???
5
        ???
6
    student_purchases p ON ???

应用MIN聚合函数需要适当地对结果进行分组


xxxxxxxxxx
14
1
SELECT 
2
    ???,
3
    ???,
4
    MIN(???) AS first_date_watched,
5
    MIN(???) AS first_date_purchased,
6
    DATEDIFF(???) AS days_diff_reg_watch,
7
    DATEDIFF(???) AS days_diff_watch_purch
8
FROM
9
    student_engagement e
10
        ???
11
    student_info i ON ???
12
        ???
13
    student_purchases p ON ???
14
GROUP BY ???;

过滤数据(having)
1. 记得保留从未购买过的学生
2. 排除首次参与日期晚于首次购买日期的记录


xxxxxxxxxx
15
1
SELECT 
2
    ???,
3
    ???,
4
    MIN(???) AS first_date_watched,
5
    MIN(???) AS first_date_purchased,
6
    DATEDIFF(???) AS days_diff_reg_watch,
7
    DATEDIFF(???) AS days_diff_watch_purch
8
FROM
9
    student_engagement e
10
        ???
11
    student_info i ON ???
12
        ???
13
    student_purchases p ON ???
14
GROUP BY ???
15
HAVING ???;

总结

导入db_course_conversions数据库并刷新模式面板
使用USE关键字选择命名数据库作为默认数据库
逐一检索任务中列出的列
使用MIN聚合函数找到首次参与和购买日期
应用DATEDIFF函数查看日期之间的差异
考虑如何连接三个表以检索Venn图中突出显示的记录
在应用MIN聚合函数时适当地对结果进行分组
过滤数据，排除首次参与日期晚于首次购买日期的记录，但保留从未购买过的学生

第二步：主查询

应该使用你创建的子查询，计算以下三个指标：

免费转付费转化率：计算已观看过讲座的学生中购买了订阅的比例。
平均注册后首次观看时间：计算学生注册后首次观看讲座的平均时间。
平均观看后首次购买时间：计算学生首次观看讲座后首次购买订阅的平均时间。
免费转付费转化率：
- 这个指标衡量了在平台上观看讲座的学生中，选择购买订阅以获得完整课程访问权限的比例。它通过以下比率计算：
  - 购买订阅日期与观看讲座日期相同或之后的学生人数。
  - 所有观看讲座的学生人数。
- 将结果转换为百分比，并将字段命名为conversion_rate。
注册后首次参与的平均持续时间：
- 这个指标衡量了注册日期和首次参与日期之间的平均持续时间。它告诉我们学生注册后平均多长时间才会观看讲座。该指标通过以下比率计算：
  - 所有这些持续时间的总和。
  - 这些持续时间的数量，或者更简单地说，观看了讲座的学生数量。
- 将字段命名为av_reg_watch。
首次参与后首次购买的平均持续时间：
- 这个指标衡量了个人在观看讲座后订阅平台的平均时间。它通过以下比率计算：
  - 所有这些持续时间的总和。
  - 这些持续时间的数量，或者更简单地说，已经购买了订阅的学生数量。
- 将字段命名为av_watch_purch。

这些指标帮助我们了解学生在平台上的行为模式，并可以指导我们在吸引学生、提高转化率以及改善用户体验方面采取何种措施。

任务：将你在前一部分（创建子查询）中创建的子查询用括号括起来，并给它一个别名，比如a。（当然你可以使用cte）

计算刚刚描述的三个指标。结果四舍五入到两位小数，不要忘记将conversion_rate指标转换为百分比。


xxxxxxxxxx
11
1
SELECT 
2
    ROUND(COUNT(???) / COUNT(???),
3
            2) AS conversion_rate,
4
    ROUND(SUM(???) / COUNT(???),
5
            2) AS av_reg_watch,
6
    ROUND(SUM(???) / COUNT(???),
7
            2) AS av_watch_purch
8
FROM
9
    (
10
        -- Subquery
11
    ) a;

你现在应该已经检索到了已开始观看讲座的学生的免费转付费转化率，注册日期和首次参与日期之间的平均持续时间，以及首次参与日期和首次购买日期之间的平均持续时间~

第三步：数据洞察

当我们谈论免费转付费转化率时，我们其实是在说有多少人在观看了课程后决定花钱购买订阅。这个比例告诉我们有多少人愿意为全面课程访问付费。我们可以把这个数字拿去跟行业标准或历史数据做比较，看看我们的表现如何。

然后，我们看到了注册后多久学生第一次观看课程。

如果这段时间很短——比如在注册后的同一天或第二天观看——这可能意味着注册过程和课程体验很好，所以学生很快就开始了学习。
但如果这段时间很长，可能意味着学生对开始学习感到犹豫或遇到了一些问题。

最后，我们观察到学生在观看了课程后多久才决定购买订阅。

如果这个时间很短，可能是因为课程内容很吸引人，或者有一些有效的销售策略。
但如果这段时间很长，可能是因为学生在等待产品以折扣价格提供。

我们可以使用Python等工具计算了一些额外的指标，比如中位数和众数，帮助我们更好地理解数据的分布情况，以及其中可能存在的模式或异常值。

免费转付费转化率指标

免费转付费转化率	注册后首次参与的平均持续时间	首次参与后首次购买的平均持续时间
11.29%	3.42	26.25

从观看讲座的学生中购买月度、季度或年度订阅的比例约为11%

也就是说，对于每100个来到平台的学生，大约有11个会购买订阅。乍一看，这个数字似乎相对较低，但让我们深入挖掘一下。

很多人只是出于好奇而注册了我们的平台，但大部分人却没有真正利用我们的计划。其中一个原因可能是我们的目标用户群比较广泛，而不是专门针对那些渴望开始数据科学之旅的人。

其次，因为我们的平台是为初学者设计的，所以学生可能不清楚应该从哪里开始学习。他们可能会纠结于应该先学习哪种编程语言，比如Python，还是应该学习查询语言，比如SQL，或者是学习数据可视化软件，比如Tableau？

而且每种工具学起来是否存在前置依赖呢？我们应该通过一个合理的学习顺序来简化学生的学习路径，为每个学生创建一个个性化的学习计划，这样他们就可以清楚地知道从哪里开始学习以及如何继续学习。尽管如此，一些用户可能需要更多的时间才能开始他们的数据科学之旅。他们可能是刚开始考试的大学生，或者是不能花太多时间在学习上的职场人士。

最后，我们必须考虑到一些用户可能不喜欢我们的平台，而选择在其他地方开始他们的数据科学学习之旅。无论原因是什么，与用户沟通是非常重要的，找出任何问题并努力改进我们的产品。

注册后首次参与的平均持续时间

免费转付费转化率	注册后首次参与的平均持续时间	首次参与后首次购买的平均持续时间
11.29%	3.42	26.25

第二个指标的结果显示，平均而言，学生在注册平台后大约需要三到四天才开始观看课程。

理想情况下，一个新学生应该在第一天就开始观看课程。平台提供的其他内容（考试、项目、职业发展路径）需要更多关注，而课程却是很容易了解的。因此，值得进一步深入分析这个结果。

但是平均值很有可能受到异常值影响，这是因为平均值是所有数据点的总和除以数据点的数量，而异常值通常是远离数据集其他值的极端值。当存在异常值时，它们的值会显著影响总和，从而导致平均值偏离数据集的大部分。

举例来说，考虑一个小班级的学生年龄数据集：
学生年龄：18,19,20,21,22,23,24,25,45
在这个数据集中，大多数学生的年龄都集中在18到25岁之间，除了一个异常值45岁。如果计算这个数据集的平均年龄，将所有年龄相加并除以学生人数：
平均年龄=(18+19+20+21+22+23+24+25+45)/9=23
但是，由于异常值45岁的存在，平均年龄被推高到了23岁，而这并不代表大多数学生的真实年龄。因此，异常值会使平均值失真，不再反映数据集的中心趋势。
这就是为什么在分析数据时，除了平均值，还要考虑中位数和众数等指标，以更全面地理解数据的分布情况。

中位数告诉我们数据集中间的数字是什么——假设它是有序的，而众数是数据集中出现频率最高的数字。在SQL中计算这些并不像直观，所以可以用Python

当然用MySQL计算也不是不行（咱们刷题里面有这个计算中位数的题）


xxxxxxxxxx
15
1
SELECT AVG(days_diff_reg_watch) AS median_reg_watch -- 选择结果的平均值作为中位数
2
FROM (
3
    SELECT days_diff_reg_watch -- 选择数据表中的 days_diff_reg_watch 列作为子查询的结果
4
    FROM (
5
        SELECT 
6
            days_diff_reg_watch, -- 选择数据表中的 days_diff_reg_watch 列
7
            @rownum:=@rownum+1 AS row_num, -- 为数据表中的每一行添加一个序号，@rownum 用于记录序号，每次递增1
8
            (SELECT COUNT(*) FROM tmp) AS total_rows -- 计算数据表中的总行数，并将其存储为 total_rows
9
        FROM 
10
            (SELECT @rownum:=0) AS init, -- 初始化变量 @rownum 为 0，用于记录行号
11
            (SELECT days_diff_reg_watch FROM tmp ORDER BY days_diff_reg_watch) AS sorted_data -- 从数据表中选择 days_diff_reg_watch 列，并按照该列的值进行排序
12
    ) AS numbered_rows -- 将上述结果命名为 numbered_rows
13
    WHERE 
14
        row_num IN (total_rows/2, (total_rows+1)/2) -- 选取 numbered_rows 中行号为 total_rows/2 或 (total_rows+1)/2 的行，即中位数所在的行或其前后的行
15
) AS median_subquery; -- 将上述结果命名为 median_subquery