如何解决问题

确定业务目标
- 从业务目标去分析额问题，跳出问题本身
将目标拆解成详细问题
- 拆解问题之后，能够分析我们现有问题的位置，对现有问题的有关系的问题或者是上下游的问题会有一个比较清楚的认知，对我们问题的形成有一个系统的认知。
对问题做优先级排序
- 对问题的重要程度、紧急程度、解决问题的投入成本来进行排序。
制定问题解决方案
- 对确定优先级之后的问题，指定解决方案，做成可以落地的方案
搜集数据分析重要问题
- 搜集历史及数据分析方案可行性
- 提前布置监控体系，监控方案运行效果
汇总研究成果
- 对现有方案作总结，分析方案效果，验证方案的有效性，为项目汇报准备论据。
梳理论据逻辑，形成报告
- 将准备的论据按照业务逻辑梳理，形成案例报告。
复盘
- 在报告汇报之后需要收集各方反馈，对项目做进一步复盘。

目标确认与拆解

运用解决问题思路来达到领导要求的目标；主要运用前四点，从确定目标到解决方案。

通过历史统计数据得出现在的销售额和销售预期目标。

目标：根据领导要求9月的任务为8月的70%。

目标拆解：AARRR->ARARR

目标是提升转化
拉新、裂变、激活、留存、转化

一个产品如果久久体验不到核心功能，那么用户流失的就越快，从注册开始就得优化产品的使用路径。

目标拆解的原则，使用MECE法则，相互独立，完全穷尽。

教育专项问题解决思路

select
    A.date,
    count(distinct A.user_id) as new_user,
    count(distinct T_Start.user_id) as pay_new_user,
    count(distinct T_Start.user_id) / count(distinct A.user_id)
from
    (
        select
            date,
            user_id
        from
            event_ok
        where
            `$is_first_day` = 1
            and EVENT = '$AppStart'
        group by
            date,
            user_id
    ) as A
    inner join (
        select
            date,
            user_id
        from
            event_ok
        where
            EVENT = 'ReceiveVipFreely'
            and share_user_id is not null
    ) as B on A.date = B.date
    and A.user_id = B.user_id
    left join (
        select
            date,
            user_id
        from
            event_ok
        where
            EVENT = 'PayOrderSucceed'
            and actual_amount > 200
        group by
            date,
            user_id
    ) as T_Start on A.user_id = T_Start.user_id
    and T_Start.Date > A.date
    and DATE_SUB(T_Start.Date, Interval 14 DAY) <= A.date
where
    A.Date in('2020-08-17')
group by
    A.date;

运营活动效果分析

汇报阶段，一般是三个阶段但是这个时候是四个阶段。

实际操作，需要先执行

#配置内部内存临时表的大小
SET GLOBAL  tmp_table_size =1024*1024*1024*2;
#配置内存分配大小
SET GLOBAL innodb_buffer_pool_size=67108864;
set global log_bin_trust_function_creators=TRUE;
DROP FUNCTION IF EXISTS insert_拉新;
DROP FUNCTION IF EXISTS insert_下载渠道付费转化率;
DROP FUNCTION IF EXISTS insert_用户活跃留存;
DROP TABLE IF EXISTS temp_riqi;
# 获得埋点数据日期列表
create table temp_riqi
select date from  event_ok group by date
order by date;
#  生成插入拉新数据自定义函数
CREATE FUNCTION insert_拉新(riqi Date)  RETURNS int
BEGIN
DELETE FROM 用户拉新 where install_date in (DATE_SUB(riqi, Interval 1 DAY),DATE_SUB(riqi, Interval 7 DAY),DATE_SUB(riqi, Interval 14 DAY));
insert 用户拉新
select *,
sum(u_cnt) over( PARTITION by install_date) as u_cnt_total,
sum(retention_u_cnt) over( PARTITION by install_date) as retention_u_cnt_total,
if(sum(u_cnt) over( PARTITION by install_date)=0,0,sum(retention_u_cnt) over( PARTITION by install_date)/sum(u_cnt) over( PARTITION by install_date)) as rention_pp_modi,
sum(retention_u_cnt_7) over( PARTITION by install_date) as retention_u_cnt_total_7,
if(sum(u_cnt) over( PARTITION by install_date)=0,0,sum(retention_u_cnt_7) over( PARTITION by install_date)/sum(u_cnt) over( PARTITION by install_date)) as rention_pp_modi_7,
sum(retention_u_cnt_14) over( PARTITION by install_date) as retention_u_cnt_total_14,
if(sum(u_cnt) over( PARTITION by install_date)=0,0,sum(retention_u_cnt_14) over( PARTITION by install_date)/sum(u_cnt) over( PARTITION by install_date)) as rention_pp_modi_14
from(
select install_date,downloadchannel,count(distinct user_id) as u_cnt,
COUNT(distinct if(DATEDIFF(action_date,install_date)=1,user_id,Null)) as retention_u_cnt,
if(count(distinct user_id) =0,0,COUNT(distinct if(DATEDIFF(action_date,install_date)=1,user_id,Null))/ count(distinct user_id)) as rention_pp,
COUNT(distinct if(DATEDIFF(action_date,install_date)=7,user_id,Null)) as retention_u_cnt_7,
if(count(distinct user_id)=0,0,COUNT(distinct if(DATEDIFF(action_date,install_date)=7,user_id,Null)) / count(distinct user_id)) as rention_pp_7,
COUNT(distinct if(DATEDIFF(action_date,install_date)=14,user_id,Null)) as retention_u_cnt_14,
if(count(distinct user_id)=0,0,COUNT(distinct if(DATEDIFF(action_date,install_date)=14,user_id,Null))/count(distinct user_id)) as rention_pp_14
from(
SELECT distinct T_Install.user_id,
T_Install.downloadchannel, T_Install.date as install_date, T_Start.Date action_date
from (
select * from event_ok where `event` = 'AppInstall'
  -- 时间字段
    and date in (riqi,DATE_SUB(riqi, Interval 1 DAY),DATE_SUB(riqi, Interval 7 DAY),DATE_SUB(riqi, Interval 14 DAY))  ) as T_Install -- 待补日期
left join (
select * from event_ok where `event` = '$AppStart'
  -- 时间字段
    and date >= DATE_SUB(riqi, Interval 14 DAY) and date<= riqi) as T_Start  -- 待补日期
on T_Install.user_id = T_Start.user_id and 
( DATE_SUB(T_Start.Date, Interval 1 DAY) = T_Install.date or DATE_SUB(T_Start.Date, Interval 7 DAY)  = T_Install.date or
DATE_SUB(T_Start.Date, Interval 14 DAY) = T_Install.date)) as  T_Install_Start
group by Install_date,downloadchannel) as add_date_sta;
RETURN  ROW_COUNT();
END ;
#调用函数插入8月15号以后拉新数据
select  insert_拉新(date)  from temp_riqi where date >'2020-08-15';
# 生成插入用户活跃留存数据自定义函数
CREATE FUNCTION insert_用户活跃留存(riqi Date)  RETURNS int
begin
delete from 用户活跃_用户活跃留存  
where install_date in(DATE_SUB(riqi, Interval 1 DAY),DATE_SUB(riqi, Interval 7 DAY),DATE_SUB(riqi, Interval 14 DAY));
insert  用户活跃_用户活跃留存 
select install_date,count(distinct user_id) as dau,
count(distinct case when first_flag = 1  then  user_id else null end) as first_dau,
if(count(distinct user_id) =0,0,count(distinct case when first_flag = 1  then  user_id else null end)/count(distinct user_id)) as first_PP,
COUNT(distinct if(DATEDIFF(action_date,install_date)=1,user_id,Null)) as retention_dau_1,
if(count(distinct user_id)=0,0,COUNT(distinct if(DATEDIFF(action_date,install_date)=1,user_id,Null))/ count(distinct user_id)) as retention_pp_1,
COUNT(distinct if(DATEDIFF(action_date,install_date)=7,user_id,Null)) as retention_dau_7,
if(count(distinct user_id) =0,0,COUNT(distinct if(DATEDIFF(action_date,install_date)=7,user_id,Null)) / count(distinct user_id)) as rention_pp_7,
COUNT(distinct if(DATEDIFF(action_date,install_date)=14,user_id,Null)) as retention_dau_14,
if(count(distinct user_id) =0,0,COUNT(distinct if(DATEDIFF(action_date,install_date)=14,user_id,Null))/count(distinct user_id)) as rention_pp_14
from(
SELECT distinct T_Action.user_id,T_Action.date as install_date, T_Action.$is_first_day as first_flag,T_Start.Date action_date
from (
select user_id,$is_first_day,Date from event_ok where `event` = '$AppStart'
  -- 时间字段
and date in (riqi,DATE_SUB(riqi, Interval 1 DAY),DATE_SUB(riqi, Interval 7 DAY),DATE_SUB(riqi, Interval 14 DAY)) -- 待补日期
group by user_id,$is_first_day,Date
) as T_Action
left join (
select * from event_ok where `event` = '$AppStart'
  -- 时间字段
  and  date >= DATE_SUB(riqi, Interval 14 DAY) and date<= riqi) as T_Start  -- 待补日期 ,第二个日期为第一个日期加14天
on T_Action.user_id = T_Start.user_id and 
( DATE_SUB(T_Start.Date, Interval 1 DAY) = T_Action.date or DATE_SUB(T_Start.Date, Interval 7 DAY)  = T_Action.date or
DATE_SUB(T_Start.Date, Interval 14 DAY) = T_Action.date)) as  T_Action_Start
group by Install_date;
RETURN  ROW_COUNT();
END ;
#调用函数插入8月15号以后用户活跃留存数据
select  insert_用户活跃留存(date)  from temp_riqi where date >'2020-08-15';
#补充8月15日之后的数据到用户在线时长表
insert  用户活跃_用户在线时长  
select 
Install_date,
count(distinct T_Action.user_id) as dau,
sum(duration) as total_duration,
if(count(distinct T_Action.user_id)=0,0,sum(duration)/count(distinct T_Action.user_id)) as duration_mean
from 
(
select user_id,Date as install_date  from event_ok 
where `event` = '$AppStart'
  -- 时间字段
  and date > '2020-08-15' -- 待补日期，当天日期
group by user_id,Date
) as T_Action
left join 
(
select user_id, date as end_date, sum($event_duration) as duration  from event_ok 
where `event` = '$AppEnd'
  -- 时间字段
   and date > '2020-08-15' -- 待补日期，当天日期
group by user_id, date
) as T_end
on T_Action.user_id = T_end.user_id and install_date = end_date
group by Install_date;
#补充不为8月15的数据到总收入统计
insert 产品收入_总收入统计 
select
Install_date,
count(distinct user_id) as pay_user,
sum(actual_amount_total) as total_amount,
if(count(distinct user_id) =0,0,sum(actual_amount_total)/count(distinct user_id)) as amount_mean,
#训练营
count(distinct case when income_type =1 then user_id else null end) as pay_user_xly,
sum(case when income_type =1 then actual_amount_total else 0 end) as total_amount_xly,
if(count(distinct case when income_type =1 then  user_id else null end) =0,0,coalesce(sum(case when income_type =1 then actual_amount_total else 0 end)/count(distinct case when income_type =1 then  user_id else null end),0)) as amount_mean_xly,
#专栏
count(distinct case when income_type =2 then  user_id else null end) as pay_user_zl,
sum(case when income_type =2 then actual_amount_total else 0 end) as total_amount_zl,
if(count(distinct case when income_type =2 then  user_id else null end)=0,0,coalesce(sum(case when income_type =2 then actual_amount_total else 0 end)/count(distinct case when income_type =2 then  user_id else null end),0)) as amount_mean_zl,
#会员
count(distinct case when income_type =3 then  user_id else null end) as pay_user_member,
sum(case when income_type =3 then actual_amount_total else 0 end) as total_amount_member,
if(count(distinct case when income_type =3 then  user_id else null end) <> 0,sum(case when income_type =3 then actual_amount_total else 0 end)/count(distinct case when income_type =3 then  user_id else null end),0) as amount_mean_member
from
(
    select
    user_id,
    date as Install_date,
    case when actual_amount > 200 and (order_type = '课程' or order_type is null) then 1
         when actual_amount >=0 and actual_amount <= 200 and (order_type = '课程' or order_type is null) then 2
         when order_type = '会员' then 3
         else -1 end as income_type,
    sum(actual_amount) as actual_amount_total
    from event_ok
    where event = 'PayOrderSucceed'
      -- 时间字段
         and date != '2020-08-15'   -- 待补时间，当天日期
    group by 1,3,2
) PayOrderSucceed
where actual_amount_total>0
group by
Install_date
order by
Install_date;
##补充不为8月15的数据到训练营收入统计 
insert 产品收入_训练营收入统计 
-- 神策数据
select
    exp.*,
    if(exp.exp_xly_cnt =0,0,exp.exp_xly_pv/exp.exp_xly_cnt) as exp_xly_step_mean,
    clk.clk_xly_cnt,
    clk.clk_xly_pv,
    -- 点击率
    if(exp.exp_xly_cnt=0,0,clk.clk_xly_cnt/exp.exp_xly_cnt) as xly_ctr_uv,
    -- app 用户数，点击率
    exp_1.xly_sx_cnt_app,
    if(clk.clk_xly_cnt=0,0,exp_1.xly_sx_cnt_app/clk.clk_xly_cnt) as clk2sx_pp,
    exp_1.xly_sx_cnt,
    clk_1.xly_appointment_cnt,
    if(exp_1.xly_sx_cnt=0,0,clk_1.xly_appointment_cnt/exp_1.xly_sx_cnt) as sx2appointment_pp,
    pay.pay_user_xly_cnt,
    if(clk_1.xly_appointment_cnt=0,0,pay.pay_user_xly_cnt/clk_1.xly_appointment_cnt) as clk2pay_rate_xly,
    pay.pay_xly_order_amount,
    if(pay.pay_user_xly_cnt =0,0,pay.pay_xly_order_amount/pay.pay_user_xly_cnt) as xly_arppu,
    if(exp_1.xly_sx_cnt =0,0,pay.pay_xly_order_amount/exp_1.xly_sx_cnt) as value_per_sx_uv_xly
from
    (
    select date,count(distinct user_id) as exp_xly_cnt,count(1) as exp_xly_pv
    from event_ok
    -- 运营位曝光    新增埋点
    where event = 'ViewOperatingPosition'
    and operating_type='训练营'
      -- 时间字段
    and date > '2020-08-15'   -- 待补时间，当天日期
    group by date
    ) exp
left outer join
    (
      select date, count(distinct user_id) as clk_xly_cnt, count(user_id) as clk_xly_pv
      from event_ok
       -- 运营位点击
      where event = 'ClickOperatingPosition'  and operating_type='训练营'
      -- 时间字段
      and date > '2020-08-15'   -- 待补时间，当天日期
      group by date
    ) clk
    on exp.date = clk.date 
-- 训练营商详用户数
left  join
    (
      select date,  count(distinct user_id) as xly_sx_cnt,
      count(distinct case when platform_type in ('Android','iOS') then user_id else null end) as xly_sx_cnt_app
      from event_ok
      where event = 'WebView'
      and page_id in ('1pb3','1pb4','1pu4','1pu5','1pxi','1qb3','1qea','1qeb','1qg1','1qhg','1qjk','1qph','1qzq')
      -- 时间字段
      and date > '2020-08-15'   -- 待补时间，当天日期
      group by
      date
    ) exp_1
    on exp.date = exp_1.date
-- 训练营预约用户数（报名）
left outer join
    (
      select date,count(distinct user_id) as xly_appointment_cnt
      from event_ok
    -- web点击
      where event = 'WebButtonClick'
      and page_id in ('1pb3','1pb4','1pu4','1pu5','1pxi','1qb3','1qea','1qeb','1qg1','1qhg','1qjk','1qph','1qzq')
      -- 时间字段
    and date != '2020-08-15'   -- 待补时间，当天日期
      group by  date
    ) clk_1
    on clk.date = clk_1.date
-- 训练营付费
left outer join
    (
      select date , count(distinct user_id) as pay_user_xly_cnt, sum(actual_amount) as pay_xly_order_amount
      from event_ok
    -- 支付成功
      where event = 'PayOrderSucceed'
      and actual_amount > 200
      and order_type != '会员'
      -- 时间字段
      and date != '2020-08-15'   -- 待补时间，当天日期
      group by date 
    ) pay
    on exp.date  = pay.date
order by exp.date;
#向专栏收入统计插入日期不为8月15的数据
insert  产品收入_专栏收入统计 
select
    vop.date,
    exp_zl_cnt,
    exp_zl_pv,
    if(exp_zl_cnt=0,0,exp_zl_pv/exp_zl_cnt) as exp_zl_step_mean,
    clk_zl_cnt,
    clk_zl_pv,
    if(exp_zl_cnt=0,0,clk_zl_cnt/exp_zl_cnt) as zl_ctr_uv,
    zl_sx_uv,
    zl_sx_uv_app,
    -- t21.zl_sx_uv_app/t2.clk_zl_cnt as clk2open_pp,
    zl_create_order_cnt,
    if(zl_sx_uv=0,0,zl_create_order_cnt/zl_sx_uv) as open2create_order_pp,
    pay_user_zl_cnt,
    if(zl_create_order_cnt=0,0,pay_user_zl_cnt/zl_create_order_cnt) as clk2pay_rate_zl,
    pay_zl_order_amount,
    if(pay_user_zl_cnt=0,0,pay_zl_order_amount/pay_user_zl_cnt) as zl_arppu,
    if(zl_sx_uv=0,0,pay_zl_order_amount/zl_sx_uv) as value_per_sx_uv_zl
from
(
    select date, count(distinct user_id) as exp_zl_cnt, count(1) as exp_zl_pv 
    from event_ok
    -- 运营位曝光
    where event = 'ViewOperatingPosition' and operating_type='课程列表页'
      -- 时间字段
       and date != '2020-08-15' -- 待补日期，当天日期
    group by date
    ) vop
left  join
    (
      select date , count(distinct user_id) as clk_zl_cnt,count(user_id) as clk_zl_pv
      from event_ok
      where event = 'ClickOperatingPosition' and operating_type='课程列表页'
      -- 时间字段
       and date != '2020-08-15' -- 待补日期，当天日期
      group by date 
    ) cop
on vop.date = cop.date
left  join
    (
      select
      date,count(distinct user_id) as zl_sx_uv,
      count(distinct case when platform_type in ('Android','iOS') then user_id else null end) as zl_sx_uv_app
      from event_ok
    -- 浏览售前课程详情页
      where event = 'ViewCourseDetail'
      -- 时间字段
        and date != '2020-08-15' -- 待补日期，当天日期
      group by date
    ) vcd
on vop.date = vcd.date
left  join
    (
      select date , count(distinct user_id) as zl_create_order_cnt
      from event_ok
    -- 创建订单
      where event = 'CreateOrder' and order_amount < 200
      -- 时间字段
         and date != '2020-08-15' -- 待补日期，当天日期
      group by date
    ) co
on vop.date = co.date
-- 专栏付费用户
left outer join
    (
      select date, count(distinct user_id) as pay_user_zl_cnt, sum(actual_amount) as pay_zl_order_amount
      from event_ok
    -- 支付成功
      where event = 'PayOrderSucceed' and actual_amount <= 200 and actual_amount>0
      -- 时间字段
         and date != '2020-08-15' -- 待补日期，当天日期
      group by date
    ) pos
on vop.date = pos.date
order by vop.date;
# 向会员收入统计插入日期不为8月15的数据
insert  产品收入_会员收入统计
select
    rvf.date,
    pv_k.member_sx_cnt,-- 会员商详用户数
    rvf.receive_vip_user_cnt,-- 领取会员用户数
    if(pv_k.member_sx_cnt =0,0,rvf.receive_vip_user_cnt/pv_k.member_sx_cnt) as receive_pp, -- 领取转化率
    member_clk_cnt,-- 会员点击支付用户数
    if(member_sx_cnt=0,0,member_clk_cnt/member_sx_cnt)  as clk_try2pay_pp, -- 点击转化率
    coalesce(pay_user_hy_cnt,0) as pay_user_hy_cnt, -- 购买会员用户数
    if(member_clk_cnt=0,0,coalesce(pay_user_hy_cnt/member_clk_cnt,0) )as try2scucess_pp, -- 支付转化率
    coalesce(pay_hy_order_amount,0) as pay_hy_order_amount, -- 购买总金额
    if(pay_user_hy_cnt=0,0,coalesce(pay_hy_order_amount/pay_user_hy_cnt,0)) as arppu_hy -- 人均消费 
from
(
    select date ,count(distinct user_id) as receive_vip_user_cnt
    from event_ok
    -- 领取会员成功
    where event = 'ReceiveVipFreely'
      -- 时间字段 
    and date != '2020-08-15'  -- 待补时间，当天日期
    group by date
    ) rvf 
-- 进入会员详细页
left  join
    (
    select date,count(distinct user_id) as member_sx_cnt
    from event_ok
    where event = '$pageview' and $url like '%kaiwu.lagou.com/member%'
      -- 时间字段
    and date != '2020-08-15'  -- 待补时间，当天日期
    group by date
    -- https://kaiwu.lagou.com/member/index
    ) pv_k
on rvf.date = pv_k.date
-- 会员点击支付按钮
left  join
    (
    select date, count(distinct user_id) as member_clk_cnt
    from event_ok
    where event = '$WebClick'  
    -- and $url in ('https://kaiwu.lagou.com/member/index','https://kaiwu.lagou.com/member/index?appType=C&lagoufrom=ios&appVersion=7.0.1&randomize=704021827')
    and $url like '%kaiwu.lagou.com/member%'
    and $element_content = '¥688 开通365天会员'
      -- 时间字段
    and date != '2020-08-15'  -- 待补时间，当天日期
    group by date
    ) wc_k
on rvf.date = wc_k.date
-- 付费成功
left  join
    (
    select date , count(distinct user_id) as pay_user_hy_cnt, sum(actual_amount) as pay_hy_order_amount
    from event_ok
    where event = 'PayOrderSucceed' and actual_amount>0 and order_type = '会员'
      -- 时间字段
    and date != '2020-08-15'  -- 待补时间，当天日期
    group by date
    ) pay
on rvf.date = pay.date
order by rvf.date;
#定义插入某天付费转化函数
create FUNCTION insert_下载渠道付费转化率(riqi date) RETURNS  int
begin
insert 下载渠道付费转化率 
select
start_date,
end_date,
channel,num,pay_user,
if(num=0,0,pay_user/num) as pay_rate,total_amount,amount_mean
from
(
select if(DownloadChannel is null ,'iOS',DownloadChannel) as channel, count(distinct user_id) num
from event_ok
where event = 'AppInstall'
  -- 时间字段
 and date = DATE_SUB(riqi, Interval 14 DAY) -- 待补日期，当天日期
group by 1
) appinstall
#安装并付费的用户信息
left join
(
    select
    sub_AI.channel_2,
    count(distinct sub_AI.user_id) as pay_user,
    sum(pay.actual_amount_total) as total_amount,
    if(count(distinct sub_AI.user_id)  =0,0,    sum(pay.actual_amount_total)/count(distinct sub_AI.user_id)) as amount_mean,
    max(pay.Install_date) as end_date,
    min(pay.Install_date) as start_date
    from
        (
        select user_id, if(DownloadChannel is null ,'iOS',DownloadChannel) as channel_2,date as Install_date
        from event_ok
        where event = 'AppInstall'
  -- 时间字段
    and date = DATE_SUB(riqi, Interval 14 DAY) -- 待补日期，当天日期
        group by 1, 2,3
        ) sub_AI
        left join
         (
        select user_id, date as Install_date,
        case when actual_amount > 200 and order_type = '课程' then 1
                 when actual_amount >0 and actual_amount <= 200 and order_type = '课程' then 2
                 when order_type = '会员' then 3
                 else -1 end as income_type,
        sum(actual_amount) as actual_amount_total
        from event_ok
        where event = 'PayOrderSucceed'
  -- 时间字段
    and date >= DATE_SUB(riqi, Interval 14 DAY) and date <= riqi -- 待补日期   两周转化周期
        group by 1,3,2
        ) pay on sub_AI.user_id = pay.user_id
        where pay.actual_amount_total>0
        group by  1
        order by 2 desc
  )temp 
    on temp.channel_2 = appinstall.channel
order by total_amount desc;
return row_count();
end;
#调用函数，插入每天的付费转化率
select insert_下载渠道付费转化率(date) from temp_riqi;
# 向日报表中插入除8月15以外的数据
insert 日报 
select
    -- 用户拉新表内
    用户活跃_用户活跃留存.install_date as '日期',
    u_cnt_total  as '激活人数',
    retention_u_cnt_total as '新用户次日留存人数',
    if(u_cnt_total =0,0,retention_u_cnt_total/u_cnt_total) as "新用户次日留存率",
    retention_u_cnt_total_7 as  "新用户7日留存人数",
    if(u_cnt_total=0,0,retention_u_cnt_total_7/u_cnt_total)  as "新用户7日留存率",
    retention_u_cnt_total_14 as "新用户14日留存人数",
    if(u_cnt_total=0,0,retention_u_cnt_total_14/u_cnt_total)  as "新用户14日留存率",
    -- 用户活跃_用户活跃留存 内
    用户活跃_用户活跃留存.dau as "DAU",
    first_dau as "首次活跃人数",
    if(用户活跃_用户活跃留存.dau =0,0,first_dau/用户活跃_用户活跃留存.dau) as "首次活跃用户占比",
    retention_dau_1 as  "活跃用户次日留存人数",
    if(用户活跃_用户活跃留存.dau=0,0,retention_dau_1/用户活跃_用户活跃留存.dau) as "活跃用户次日留存率",
    retention_dau_7 as  "活跃用户7日留存人数",
    if(用户活跃_用户活跃留存.dau=0,0,retention_dau_7/用户活跃_用户活跃留存.dau) as "活跃用户7日留存率",
    retention_dau_14 as "活跃用户14日留存人数",
    if(用户活跃_用户活跃留存.dau=0,0,retention_dau_14/用户活跃_用户活跃留存.dau) as "活跃用户14日留存率",
    -- 用户活跃_用户在线时长
    if(用户活跃_用户在线时长.dau=0,0,total_duration/用户活跃_用户在线时长.dau/60) as "平均单日使用时长（分）",
    -- 产品收入_总收入统计
    pay_user as "总付费人数",
    total_amount as "总付费金额",
    if(pay_user=0,0,total_amount/pay_user) as "客单价",
    pay_user_xly as "付费人数_训练营",
    total_amount_xly as "付费金额_训练营",
    if(pay_user_xly<>0,coalesce(total_amount_xly/pay_user_xly,0),0) as  "客单价_训练营",
    pay_user_zl as "付费人数_专栏",
    total_amount_zl as "付费金额_专栏",
    if(pay_user_zl=0,0,coalesce(total_amount_zl/pay_user_zl,0)) as "客单价_专栏",
    pay_user_member as "付费人数_会员",
    total_amount_member as "付费金额_会员",
    if(pay_user_member <> 0,total_amount_member/pay_user_member,0) as "客单价_会员",
    -- 产品收入_训练营收入统计
    exp_xly_cnt as  "曝光人数_训练营",
    exp_xly_pv as  "曝光次数_训练营",
    if(exp_xly_cnt<>0, exp_xly_pv/exp_xly_cnt,0) as  "平均曝光次数_训练营",
    clk_xly_cnt as "点击人数_训练营_app",
    clk_xly_pv as "点击次数_训练营_app",
    if(exp_xly_cnt<>0, clk_xly_cnt/exp_xly_cnt,0) as  "点击率_训练营_uv",
    if(exp_xly_pv<>0, clk_xly_pv/exp_xly_pv,0) as  "点击率_训练营_pv",
    -- 产品收入_训练营收入统计
    xly_sx_cnt as "浏览商详人数_训练营",
    xly_sx_cnt_app as "浏览商详人数_训练营_app",
    xly_appointment_cnt as "预约人数_训练营",
    if(xly_sx_cnt<>0,xly_appointment_cnt/xly_sx_cnt,0) as "浏览商详-预约转化率_训练营",
    if(xly_appointment_cnt<>0,pay_user_xly_cnt/xly_appointment_cnt,0) as "预约-付费转化率_训练营",
    if(xly_sx_cnt<>0,pay_xly_order_amount/xly_sx_cnt,0) as "单商详uv价值_训练营",
    -- 产品收入_专栏收入统计
    exp_zl_cnt as "曝光人数_专栏",
    exp_zl_pv as "曝光次数_专栏",
    if(exp_zl_cnt<>0,exp_zl_pv/exp_zl_cnt,0) as "人均曝光次数_专栏",
    clk_zl_cnt/7 as "点击人数_专栏" ,
    if(exp_zl_cnt<>0,clk_zl_cnt/exp_zl_cnt,0) as "点击率_专栏_uv",
    if(exp_zl_pv<>0,clk_zl_pv/exp_zl_pv,0) as "点击率_专栏_pv",
    zl_sx_uv as "浏览商详人数_专栏" ,
    zl_sx_uv_app as "浏览商详人数_专栏_app",
    zl_create_order_cnt as "下单人数_专栏",
    if(zl_sx_uv<>0,zl_create_order_cnt/zl_sx_uv,0) as "浏览商详-下单转化率_专栏",
    if(zl_create_order_cnt<>0,pay_user_zl_cnt/zl_create_order_cnt,0) as "下单-付费转化率_专栏",
    if(zl_sx_uv<>0,pay_zl_order_amount/zl_sx_uv,0) as "单商详uv价值_专栏",
    -- 产品收入_会员收入统计
    member_sx_cnt  as "浏览商详人数_会员",
    receive_vip_user_cnt as "领取会员人数",
    if(member_sx_cnt <> 0, receive_vip_user_cnt / member_sx_cnt,0) as "浏览商详-领取会员转化率_会员",
    member_clk_cnt as "点击支付人数_会员"
from 用户活跃_用户活跃留存
    left join (select DISTINCT install_date,
    min(u_cnt_total) as u_cnt_total, -- 日安装用户总数
    min(retention_u_cnt_total) as retention_u_cnt_total, -- 次日留存用户总数
    min(retention_u_cnt_total_7) as retention_u_cnt_total_7 , -- 日留存用户总书
    min(retention_u_cnt_total_14) as retention_u_cnt_total_14 -- 日留存用户总书
    from 用户拉新 group by install_date) as  新用户
    on  用户活跃_用户活跃留存.Install_date = 新用户.install_date
    left join 用户活跃_用户在线时长  on  用户活跃_用户在线时长.Install_date = 新用户.install_date
    left join 产品收入_总收入统计  on 产品收入_总收入统计.Install_date = 新用户.install_date
    left join 产品收入_训练营收入统计 on 产品收入_训练营收入统计.date = 新用户.install_date
    left join 产品收入_专栏收入统计 on 产品收入_专栏收入统计.date = 新用户.install_date
    left join 产品收入_会员收入统计 on 产品收入_会员收入统计.date = 新用户.install_date
where 用户活跃_用户活跃留存.install_date != '2020-08-15';
#重写周报所有数据
delete  from  周报;
insert 周报 
select
-- 用户拉新表内
    DATE_SUB(用户活跃_用户活跃留存.install_date,INTERVAL WEEKDAY(用户活跃_用户活跃留存.install_date) DAY) as '当周周一',
    sum(u_cnt_total)/7  as '激活人数',
    sum(retention_u_cnt_total)/7 as '新用户次日留存人数',
    if(sum(u_cnt_total)=0,0,sum(retention_u_cnt_total)/sum(u_cnt_total)) as "新用户次日留存率",
    sum(retention_u_cnt_total_7)/7 as  "新用户7日留存人数",
    if(sum(u_cnt_total)=0,0,sum(retention_u_cnt_total_7)/sum(u_cnt_total))  as "新用户7日留存率",
    sum(retention_u_cnt_total_14)/7 as "新用户14日留存人数",
    if(sum(u_cnt_total)=0,0,sum(retention_u_cnt_total_14)/sum(u_cnt_total))  as "新用户14日留存率",
    -- 用户活跃_用户活跃留存 内
    sum(用户活跃_用户活跃留存.dau)/7 as "DAU",
    sum(first_dau)/7 as "首次活跃人数",
    if(sum(用户活跃_用户活跃留存.dau)=0,0,sum(first_dau)/sum(用户活跃_用户活跃留存.dau)) as "首次活跃用户占比",
    sum(retention_dau_1)/7 as  "活跃用户次日留存人数",
    if(sum(用户活跃_用户活跃留存.dau) =0,0,sum(retention_dau_1)/sum(用户活跃_用户活跃留存.dau)) as "活跃用户次日留存率",
    sum(retention_dau_7)/7 as  "活跃用户7日留存人数",
    if(sum(用户活跃_用户活跃留存.dau)=0,0,sum(retention_dau_7)/sum(用户活跃_用户活跃留存.dau)) as "活跃用户7日留存率",
    sum(retention_dau_14)/7 as "活跃用户14日留存人数",
    if(sum(用户活跃_用户活跃留存.dau)=0,0,sum(retention_dau_14)/sum(用户活跃_用户活跃留存.dau)) as "活跃用户14日留存率",
    -- 用户活跃_用户在线时长
    if(sum(用户活跃_用户在线时长.dau)=0,0,sum(total_duration)/sum(用户活跃_用户在线时长.dau)/60) as "平均单日使用时长（分）",
    -- 产品收入_总收入统计
    sum(pay_user)/7 as "总付费人数",
    sum(total_amount)/7 as "总付费金额",
    if(sum(pay_user)=0,0,sum(total_amount)/sum(pay_user)) as "客单价",
    sum(pay_user_xly)/7 as "付费人数_训练营",
    sum(total_amount_xly) /7 as "付费金额_训练营",
    if(sum(pay_user_xly)=0,0,coalesce(sum(total_amount_xly)/sum(pay_user_xly),0)) as  "客单价_训练营",
    sum(pay_user_zl)/7 as "付费人数_专栏",
    sum(total_amount_zl)/7 as "付费金额_专栏",
    if(sum(pay_user_zl)=0,0,coalesce(sum(total_amount_zl)/sum(pay_user_zl),0)) as "客单价_专栏",
    sum(pay_user_member)/7 as "付费人数_会员",
    sum(total_amount_member)/7 as "付费金额_会员",
    if(sum(pay_user_member) <> 0,sum(total_amount_member)/sum(pay_user_member),0) as "客单价_会员",
    -- 产品收入_训练营收入统计
    sum(exp_xly_cnt)/7 as  "曝光人数_训练营",
    sum(exp_xly_pv)/7 as  "曝光次数_训练营",
    if(sum(exp_xly_cnt)<>0, sum(exp_xly_pv)/sum(exp_xly_cnt),0) as  "平均曝光次数_训练营",
    sum(clk_xly_cnt)/7 as "点击人数_训练营_app",
    sum(clk_xly_pv)/7 as "点击次数_训练营_app",
    if(sum(exp_xly_cnt)<>0, sum(clk_xly_cnt)/sum(exp_xly_cnt),0) as  "点击率_训练营_uv",
    if(sum(exp_xly_pv)<>0, sum(clk_xly_pv)/sum(exp_xly_pv),0) as  "点击率_训练营_pv",
    -- 产品收入_训练营收入统计
    sum(xly_sx_cnt)/7 as "浏览商详人数_训练营",
    sum(xly_sx_cnt_app)/7 as "浏览商详人数_训练营_app",
    sum(xly_appointment_cnt)/7 as "预约人数_训练营",
    if(sum(xly_sx_cnt)<>0,sum(xly_appointment_cnt)/sum(xly_sx_cnt),0) as "浏览商详-预约转化率_训练营",
    if(sum(xly_appointment_cnt)<>0,sum(pay_user_xly_cnt)/sum(xly_appointment_cnt),0) as "预约-付费转化率_训练营",
    if(sum(xly_sx_cnt)<>0,sum(pay_xly_order_amount)/sum(xly_sx_cnt),0) as "单商详uv价值_训练营",
    -- 产品收入_专栏收入统计
    sum(exp_zl_cnt)/7 as "曝光人数_专栏",
    sum(exp_zl_pv)/7 as "曝光次数_专栏",
    if(sum(exp_zl_cnt)<>0,sum(exp_zl_pv)/sum(exp_zl_cnt),0) as "人均曝光次数_专栏",
    sum(clk_zl_cnt)/7 as "点击人数_专栏" ,
    if(sum(exp_zl_cnt)<>0,sum(clk_zl_cnt)/sum(exp_zl_cnt),0) as "点击率_专栏_uv",
    if(sum(exp_zl_pv)<>0,sum(clk_zl_pv)/sum(exp_zl_pv),0) as "点击率_专栏_pv",
    sum(zl_sx_uv)/7 as "浏览商详人数_专栏" ,
    sum(zl_sx_uv_app)/7 as "浏览商详人数_专栏_app",
    sum(zl_create_order_cnt)/7 as "下单人数_专栏",
    if(sum(zl_sx_uv)<>0,sum(zl_create_order_cnt)/sum(zl_sx_uv),0) as "浏览商详-下单转化率_专栏",
    if(sum(zl_create_order_cnt)<>0,sum(pay_user_zl_cnt)/sum(zl_create_order_cnt),0) as "下单-付费转化率_专栏",
    if(sum(zl_sx_uv)<>0,sum(pay_zl_order_amount)/sum(zl_sx_uv),0) as "单商详uv价值_专栏",
    -- 产品收入_会员收入统计
    sum(member_sx_cnt)/7 as "浏览商详人数_会员",
    sum(receive_vip_user_cnt)/7 as "领取会员人数",
    if(sum(member_sx_cnt) <> 0,sum(receive_vip_user_cnt)/sum(member_sx_cnt),0) as "浏览商详-领取会员转化率_会员",
    sum(member_clk_cnt)/7 as "点击支付人数_会员"
from 用户活跃_用户活跃留存
left join
    (select DISTINCT install_date,
    u_cnt_total, -- 日安装用户总数
    retention_u_cnt_total, -- 次日留存用户总数
    retention_u_cnt_total_7, -- 日留存用户总书
    retention_u_cnt_total_14 -- 日留存用户总书
    from 用户拉新) as  新用户  on  用户活跃_用户活跃留存.Install_date = 新用户.install_date
    left join 用户活跃_用户在线时长  on  用户活跃_用户在线时长.Install_date = 新用户.install_date
    left join 产品收入_总收入统计  on 产品收入_总收入统计.Install_date = 新用户.install_date
    left join 产品收入_训练营收入统计 on 产品收入_训练营收入统计.date = 新用户.install_date
    left join 产品收入_专栏收入统计 on 产品收入_专栏收入统计.date = 新用户.install_date
    left join 产品收入_会员收入统计 on 产品收入_会员收入统计.date = 新用户.install_date
group by 1;

执行时间较长。

活动吸引的新用户数以及新用户留存

#分享拉新用户及次日，7日，14日留存
select A.date as 日期, count(distinct a.user_id) as 拉新数,count(distinct
a.user_id)*20 as 活动支出,
COUNT(distinct if(DATEDIFF(T_Start.Date,A.Date)=1,A.user_id,Null)) as 次日留存,
COUNT(distinct if(DATEDIFF(T_Start.Date,A.Date)=7,A.user_id,Null)) as 7日留存,
COUNT(distinct if(DATEDIFF(T_Start.Date,A.Date)=14,A.user_id,Null)) as 14日留存
from
    (
        select
            date,
            user_id
        from
            event_ok
        where
            `$is_first_day` = 1
            and EVENT = '$AppStart'
        group by
            date,
            user_id
    ) as A
    inner join (
        select
            date,
            user_id
        from
            event_ok
        where
            EVENT = 'ReceiveVipFreely'
            and share_user_id is not null
    ) as B -- 结论 ： 两次活动留存对比 ， 第二次活动留存情况较差 。 由于7日 、 14 日留存的时间点比较特殊 ， 扩展留存曲线观察拉新用户留存效果 。 
        on A.date = B.date
    and A.user_id = B.user_id
    left join (
        select
            date,
            user_id
        from
            event_ok
        where
            EVENT = '$AppStart'
        group by
            date,
            user_id
    ) as T_Start on A.user_id = T_Start.user_id
    and (
        DATE_SUB(T_Start.Date, Interval 1 DAY) = A.date
        or DATE_SUB(T_Start.Date, Interval 7 DAY) = A.date
        or DATE_SUB(T_Start.Date, Interval 14 DAY) = A.date
    )
where
    A.Date in('2020-08-17', '2020-09-23')
group by
    A.date

执行结果

活动的K因子

select
    a.date,
    count(distinct a.user_id),
    count(DISTINCT share_user_id)
from
    (
        select
            distinct user_id,
            date
        from
            event_ok
        where
            `$is_first_day` = 1
            and EVENT = '$AppStart'
            and date in('2020-08-17', '2020-09-23')
    ) as A
    INNER JOIN (
        select
            distinct user_id,
            date,
            share_user_id
        from
            event_ok
        where
            EVENT = 'ReceiveVipFreely'
            and date in('2020-08-17', '2020-09-23')
            and share_user_id is not null
    ) as B on a.user_id = b.user_id
    and a.date = b.date
group by
    A.date

执行结果

分析活动的转化情况

#19天内的转化
select
    A.date,
    count(distinct T_Start.user_id)
from
    (
        select
            date,
            user_id
        from
            event_ok
        where
            `$is_first_day` = 1
            and EVENT = '$AppStart'
        group by
            date,
            user_id
    ) as A
    inner join (
        select
            date,
            user_id
        from
            event_ok
        where
            EVENT = 'ReceiveVipFreely'
            and share_user_id is not null
    ) as B on A.date = B.date
    and A.user_id = B.user_id
    left join (
        select
            date,
            user_id
        from
            event_ok
        where
            EVENT = 'PayOrderSucceed'
            and actual_amount > 200
        group by
            date,
            user_id
    ) as T_Start on A.user_id = T_Start.user_id
    and DATE_SUB(T_Start.Date, Interval 20 DAY) < A.date
    and T_Start.Date >= A.date
where
    A.Date in('2020-08-17', '2020-09-23')
group by
    A.date

执行结果

异常数据分析

通过图表分析可以得到异常的数据波动，首先要确定异常点的数据是计算异常还是数据的异常，正常情况下需要一层层做数据验证，从底层即数据库到应用层即用户。

然后分析这个波动的点是不是异常点，是不是已知的问题导致的。

其次确认这个点是不是人为的还是自发的，人为指的是公司活动引导导致，自发指的是用户行为导致的。如果是公司异常一般能从埋点发现，反之是自发。

最后详细梳理异常数据产生的原因。

最后异常解决及回顾

数据异常通过统计学方法时间序列分析进行预测并计算。

数据分析学习之路

3）任务二/教育运营专项分析

如何解决问题

目标确认与拆解

教育专项问题解决思路

运营活动效果分析

活动吸引的新用户数以及新用户留存

活动的K因子

分析活动的转化情况

异常数据分析

最后异常解决及回顾