人-货-场指标体系建设之SQL案例分析
1. 用户指标体系（人）
基础指标体系 + RFM模型分析
1.1 基础指标（uv/pv/留存率）
表结构
1.1.1 搭建数据表，读取数据并进行预处理：
此处需注意日期的格式，年-月-日 时间显示是12或24位进制，24进制显示%H，12小时进制显示%h。
1.1.2 根据数据，计算uv、pv以及流量深度，拆解问题步骤如下：
第一步：用behavior=1（用户行为为1记录为用户浏览行为），按照日期进行分组统计，计算pv值；
第一步：统计user_id的数量，按照日期进行分组统计，注意去重，计算uv值；
第三步：浏览深度计算方式为 pv/uv。
结果显示
1.1.3 留存率统计之前，先计算出当日活跃用户数以及1-7日，15日和30日留存数，问题拆解步骤如下：
第一步：将用户表作自连接，右表日期>=左表；
第二步：计算当日活跃用户数，注意去重；
第三步：计算1-7日，15日和30日留存数，注意去重；
第四部：利用视图进行封装，方便后续留存率计算。
结果显示
1.1.4 根据之前得出的留存数，计算留存率：
此处使用cast转换函数，用decimal精确数字长度（使用round函数也可）
结果显示
1.2 RFM模型
本次数据中通过最近消费（R）和消费频率（F）建⽴RFM模型
重要⾼价值客户：指最近⼀次消费较近⽽且消费频率较⾼的客户；
重要唤回客户：指最近⼀次消费较远且消费频率较⾼的客户；
重要深耕客户：指最近⼀次消费较近且消费频率较低的客户；
重要挽留客户：指最近⼀次消费较远且消费频率较低的客户；
1.2.1 建立R指标，根据每个用户的最近一次购买时间，给出相应的评分
第一步：获取每个用户的最近购买时间，建立视图；
第二步：计算每个用户最近购买时间距离2019-12-18相差几天，根据相差天数给予一定的评分（<=2 5;<=4 4;<=6 3; <=8 2; 其他 1），建立视图。
结果显示
1.2.2 建立F指标，求出每个用户购买次数，给出相应的评分
第一步：求出各个用户的购买次数，建立视图；
第二步：根据购买次数给予一定的评分（<=2 1; <=4 2;<=6 3,<=8 4; 其他 5）
结果显示
1.2.3 整合数据
第一步：算出R指标和F指标的均值；
第二步：拿到每个人的R指标和F指标（两表关联），与相应均值对比。
R指标和F指标的均值
每个人的指标与均值对比
结果显示
2. 商品指标体系（货）
2.1 商品的点击量、收藏量、加购量、购买次数、购买转化
各种指标的计算也可使用 count(if(behavior_type=*,user_id,null)) 来计算
结果显示
2.2 对应商品品类的点击量、加购量、购买次数、购买转化
只需要将上述语句中的item_id替换为item_category。
3. 平台指标体系（场）
3.1 行为指标
平台当日的点击次数、收藏次数、加购次数、购买次数、购买转化，与上述情况也一 样，只需要将语句中的item_category替换为dates。
3.2 行为路径分析
第一步：用户行为拼接准备，使用偏移函数和窗口函数将行为拆分为5步，使用rank函数根据时间的倒序进行排序，筛选出排行为1，behavior_type=2的数据，创建视图；
第二步：拼接行为路径；
第三步：针对行为路径进行分组count统计。
结果显示
4. 结论
4.1 用户分析
UV周环比对比：日常周环比数据大多数大于0，说明用户数量呈现上升趋势，当UV或UV周环比出现数据异常，需要注意：
内部问题：产品BUG（⽹站bug）、策略问题（周年庆活动结束了）、营销问题（代⾔⼈换了）等；
外部问题：竞品活动问题（其他平台⼤酬宾），政治环境问题（进⼝商品限制），舆情⼝碑问题（平台商品爆出质量问题）等；
4.2 用户精细化运营
通过RFM模型中的⽤户最近⼀次购买时间、⽤户消费频次分析，分拆得到以下重要⽤户。 可以在后续精细化运营场景中直接使⽤细分⽤户，做差异化运营：
对⾼价值客户做VIP服务设计，增加⽤户粘性同时通过设计优惠券提升客户消费；
对深耕客户做⼴告、推送刺激，提升消费频次；
对挽留客户做优惠券、签到送礼策略，增加挽留⽤户粘性；
对唤回客户做定向⼴告、短信召回策略，尝试召回⽤户。
4.3 商品分析
通过商品/商品品类的点击量、收藏量、加购量、购买次数、购买转化，筛选出相对转化较低的产品/品类，是否为某些品类特性导致的转化率较低，比如说，非当季商品、非必需品、奢侈品等。
4.4 行为路径分析
通过用户行为路径分析，可以查看用户购买的行为中，是否某个行为点击率相对较低，比如用户大多选择直接购买的方式，而加入购物车/收藏行为相对较少，后续需要根据产品的加购/收藏功能结合更多数据做出优化方案。
