数据预测——用户流失收入预测思路与实操

前提
第一次收到与数据预测相关的需求，可以说与“数据挖掘”沾了点边，而不再是常规的取数、查数、导出复盘，所以还是比较有动力和兴趣去尝试实现这个需求。因为没有系统地学习过数据挖掘或者数据预测的知识，有的只是曾经作为一线营销人员的实际经验和自身日常对数据关系的思考及关注，此次数据预测完全按照个人直觉分析开展，故记录下分析思路及实现过程。

工具：Hadoop、Hive、shell、ETL工具
使用HiveSQL处理数据，写入shell脚本，最后通过ETL工具进行调度，周期化运行脚本，每日进行预测。

目标： 通过历史数据预测当月用户流失收入income_loss

总体思路：指标拆分，能直接计算则直接计算，不能直接计算参考历史数据，按时间进度、历史流失收入产生占比、当前已产生的流失收入，反推当月流失收入总和。

第一步：指标分析
通过分析以往数据，发现对流失用户的定义包含不同类型：

1. 当月已注销账户的用户 cancel_cust
2. 欠费时间达到指定值（ overdue_time）的用户overdue_cust
3. 当月申请注销账户的用户 apply_cancel_cust

当月流失收入等于三类用户流失收入之和

income_loss = loss_cancel_cust + loss_overdue_cust + loss_apply_cancel_cust

其中欠费用户overdue_cust 可直接从数据库中筛选得到，可进行直接相关计算；已注销用户cancel_cust 和申请注销用户apply_cancel_cust 随时都在产生，无法直接找出两者。

第二步：分类处理

一、处理可明确筛选的欠费用户 overdue_cust

1. 从数据库中筛选出欠费时间将在本月大于overdue_time的用户；
2. 从数据库中找到欠费用户overdue_cust在欠费之前最新的消费数据，作为该用户流失收入的预测值loss_predict；
3. 将所有欠费用户的流失收入求和即得到 loss(overdue_cust);

代码demo如下：

--筛选欠费时间大于overdue_time的用户 overdue_cust
CREATE VIEW view_overdue_cust AS
SELECT user_id  
FROM datebase_name
WHERE condition1='欠费' AND condition2 > overdue_time;


--取得欠费前最新的所有用户消费数据 loss_predict
CREATE VIEW view_consume_latest AS
SELECT 
   user_id,
   consumption  --消费数据
FROM database_name
WHERE time1= current_time1 - overdue_time ;--欠费前最新的时间


 --欠费用户欠费前消费总和作为流失收入预测值
SELECT sum(b.consumption) AS loss_overdue_cust --预测值
FROM 
   view_overdue_cust a 
   INNER JOIN
   view_consume_latest b
   ON a.user_id = b.user_id;

二、处理不可明确筛选的已注销用户 cancel_cust 和申请注销用户 apply_cancel_cust
前提条件（来自个人灵感假设并通过历史数据得到验证）：每个月在同等时间进度条件下产生的流失收入占整月流失收入的比例是相同的。比如：每个月1号至20号产生整月80%的流失收入。但是，每个月的数据存在波动，所以通过多个历史月份数据进度平均值来抵消波动的影响。结果如下图所示（红色曲线为进度平均值回归曲线）：

1、注销用户流失收入预测 loss_cancel_cust
（1）从数据库中取得历史月份每天注销用户流失收入 hist_loss_cancel_cust；
（2）计算各历史月份同等时间进度下产生的注销用户流失收入占全月流失收入比例的平均值。比如当前为15号，则计算各历史月份1-15号产生的注销用户流失收入占全月注销用户流失收入的比例，然后求各月比例的平均值avg_rate_cancel_cust;
（3）从数据库中取得本月已产生的注销用户流失收入 now_loss_cance_cust;
（4）计算注销用户流失收入预测值，loss_cancel_cust = now_loss_cancel_cust / avg_rate_cancel_cust;

代码demo如下

--从数据库中取得历史月份每天注销用户流失收入 hist_loss_cancel_cust
CREATE VIEW view_hist_cancel_cust AS
SELECT 
   'cancel_cust' AS cust_type,
   month1,
   sum(case when a.day_time < '指定日期' then a.loss 
       else 0
       end) AS hist_same_day_loss,     --历史同等时间进度下的注销用户流失收入
   sum(a.loss) AS hist_all_month_loss
FROM database_name a
WHERE 
   month1 in ( hist_month1,hist_month2,hist_month3......) --历史月份自行选定
   AND condition1 = '注销用户'                            --限定注销用户
GROUP BY month1;

--从数据库中取得本月已产生的注销用户流失收入 now_loss_cance_cust;
CREATE VIEW view_now_cancel_cust AS
SELECT 
   'cancel_cust' AS cust_type,
   sum(a.loss) AS now_loss  --当月已产生的注销用户流失收入
FROM  database_name a
WHERE a.day_time < '当天';

--计算注销用户流失收入预测值 loss_cancel_cust
SELECT
b.now_loss / a.avg_rate AS loss_cancel_cust
FROM
    (SELECT
        cust_type,
        avg(hist_same_day_loss/ hist_all_mont_loss) AS avg_rate --各月同时间进度下的比例的平均值
     FROM view_hist_cancel_cust) a
     INNER JOIN
     (SELECT 
          cust_type,
          now_loss
      FROM view_now_cancel_cust) b
      ON a.cust_type = b.cust_type;
         

2、申请注销用户流失收入预测 loss_apply_cancel_cust
（1）从数据库中取得历史月份每天申请注销用户 流失收入 hist_loss_apply_cancel_cust；
（2）计算各历史月份同等时间进度下产生的申请注销用户流失收入占全月流失收入比例的平均值。比如当前为15号，则计算各历史月份1-15号产生的申请注销用户流失收入占全月申请注销用户流失收入的比例，然后求各月比例的平均值avg_rate_apply_cancel_cust;
（3） 从数据库中取得本月已产生的申请注销用户流失收入 now_loss_apply_cancel_cust;
（4） 计算申请注销用户预测值，
loss_apply_cancel_cust = now_loss_apply_cancel_cust / avg_rate_apply_cancel_cust;

代码demo如下

--从数据库中取得历史月份每天注销用户流失收入 hist_loss_cancel_cust
CREATE VIEW view_hist_apply_cancel_cust AS
SELECT 
   'apply_cancel_cust' AS cust_type,
   month1,
   sum(case when a.day_time < '指定日期' then a.loss 
       else 0
       end) AS hist_same_day_loss,     --历史同等时间进度下的注销用户流失收入
   sum(a.loss) AS hist_all_month_loss
FROM database_name a
WHERE 
   month1 in ( hist_month1,hist_month2,hist_month3......) --历史月份自行选定
   AND condition1 = '申请注销用户'                            --限定注销用户
GROUP BY month1;

--从数据库中取得本月已产生的注销用户流失收入 now_loss_cance_cust;
CREATE VIEW view_now_apply_cancel_cust AS
SELECT 
   'apply_cancel_cust' AS cust_type,
   sum(a.loss) AS now_loss  --当月已产生的注销用户流失收入
FROM  database_name a
WHERE a.day_time < '当天';

--计算注销用户流失收入预测值 loss_apply_cancel_cust
SELECT
b.now_loss / a.avg_rate AS loss_apply_cancel_cust
FROM
    (SELECT
        cust_type,
        avg(hist_same_day_loss/ hist_all_mont_loss) AS avg_rate --各月同时间进度下的比例的平均值
     FROM view_hist_apply_cancel_cust) a
     INNER JOIN
     (SELECT 
          cust_type,
          now_loss
      FROM view_now_apply_cancel_cust) b
      ON a.cust_type = b.cust_type;
         

第三步：计算总和
将三个预测值相加即可
income_loss = loss_cancel_cust + loss_overdue_cust + loss_apply_cancel_cust

第四步：收尾
将代码适当改写，写入shell脚本，建立ETL调度流程，周期性运行。

实际效果图：

注意事项

1. 历史月份的选取有所甄别：取具有相似阶段的月份
   （1）如果当月无特殊事件，在选择历史月份时也应避开比如双十一、春节之类的特殊时间点。
   （2）如果当月是特殊时间，选择对应历史月份，比如双十一，就选历史双十一的数据。

可改进：
历史月份数据存在少量的数据缺失，此次未对此类情况进行处理，最好在实践过程中对缺失值采取一定的填充处理，提高预测精度。

难点：

1. 转岗时间略短，对现有数据库表了解不足，需要从繁多的表中找到需要的表，研究表结构及表字段，虽然有同事可以请教咨询，但是依然极度耗费时间；
2. 此次采用HiveSQL处理数据，其语法及相关函数不熟，调试测试耗费大量时间；
3. 此前未接触shell编程，将HiveSQL语句写入脚本后，调测工作耗费大量时间；

收获与感想：
此次需求实现断断续续持续了三周，大部分在业余时间进行。极好地锤炼了编写SQL的能力，也迸发出了许多不可言说的灵感和想法，还是很开心的。需求完成后，翻看了《数据挖掘导论》，某种程度上，我此次的思路就是 将流失收入当作时间序列 相关，根据历史时间进度的数据来预测当前值。多看书有益身心健康。