转化率异动归因之双因素拆解法
背景：
RF月报观察各分层人群变化比较吃力，主要是RF分层各人群占比差异很大。当时主要是因业务要求，需要集中资源到高频用户。如此高频环比变化容易被观察，但是低频用户转化率对大盘影响很容易被忽略，然而因这部分人群占比高，一丁点变化都对大盘影响比较严重，所以还是需要监控起来，本文引用异动归因之双因素拆解法
目的：使用贡献度来直观体现低频、中频用户变化对大盘影响。
制作步骤：先使用RF制作用户分层，并给用户打上标签。运用SQL计算，计算逻辑如下：
分项指标波动贡献=（本期比率-上期比率）*上期占比
分项占比波动贡献=上期比率*（本期占比-上期占比）
代码见链接：
create table if not exists dws.rf_month_report_contribute
(
month string comment '日期',
rftag string comment 'RF人群标签',
residentcitylevel_qujian string comment '常驻地归属',
sex_group string comment '性别',
age_group string comment '年龄',
d0_cunuid_num bigint comment '本期存量用户数',
d0_orderuid_num bigint comment '本期存量下单用户数',
d0_rate decimal(14,4) comment '本期存量用户转化率',
d1_cunuid_num bigint comment '上期存量用户数',
d1_orderuid_num bigint comment '上期存量下单用户数',
d1_rate decimal(14,4) comment '上期存量用户转化率',
d0_sum_cunuid_num bigint comment '本期_大盘存量用户数',
d0_sum_orderuid_num bigint comment '本期_大盘存量下单用户数',
d0_sum_rate decimal(14,4) comment '本期_大盘存量用户转化率',
d1_sum_cunuid_num bigint comment '上期_大盘存量用户数',
d1_sum_orderuid_num bigint comment '上期_大盘存量下单用户数',
d1_sum_rate decimal(14,4) comment '上期_大盘存量用户转化率',
contribute_efficiency decimal(14,6) comment '分项指标波动贡献',
contribute_Proportion decimal(14,6) comment '分项占比波动贡献',
contribute decimal(14,6) comment '贡献度'
)
PARTITIONED by
(
pdate string
)
;
insert overwrite table dws.rf_month_report_contribute partition(pdate = '{today-1,yyyy-MM-dd}')
select
substr(a.pdate,1,7) month,
a.rftag,
a.residentcitylevel_qujian,
a.sex_group,
a.age_group,
a.cun_usernum as d0_cunuid_num,
a.cun_xiadan_usernum as d0_orderuid_num,
a.zhuanhua_rate as d0_rate,--本期比例
b.cun_usernum as d1_cunuid_num,
b.cun_xiadan_usernum as d1_orderuid_num,
b.zhuanhua_rate as d1_rate,--上期比例
c.sum_cun_usernum as d0_sum_cunuid_num,
c.sum_cun_xiadan_usernum as d0_sum_orderuid_num,
c.sum_zhuanhua_rate as d0_sum_rate,
d.sum_cun_usernum as d1_sum_cunuid_num,
d.sum_cun_xiadan_usernum as d1_sum_orderuid_num,
d.sum_zhuanhua_rate as d1_sum_rate,
(a.zhuanhua_rate-b.zhuanhua_rate)*(b.cun_usernum/d.sum_cun_usernum) as contribute_efficiency,
b.zhuanhua_rate*(a.cun_usernum/c.sum_cun_usernum-b.cun_usernum/d.sum_cun_usernum) AS contribute_Proportion,
(a.zhuanhua_rate-b.zhuanhua_rate)*(b.cun_usernum/d.sum_cun_usernum)+(b.zhuanhua_rate*(a.cun_usernum/c.sum_cun_usernum-b.cun_usernum/d.sum_cun_usernum)) as contribute
from
(
select
pdate,
rftag,
residentcitylevel_qujian,
sex_group,
age_group,
cun_usernum,
cun_xiadan_usernum,
zhuanhua_rate
from dws.rf_memberid_month_dimension
where pdate=last_day(add_months('{today-1,yyyy-MM-dd}', 0))
)a
left join
(
select
pdate,
rftag,
residentcitylevel_qujian,
sex_group,
age_group,
cun_usernum,
cun_xiadan_usernum,
zhuanhua_rate
from dws.rf_memberid_month_dimension
where pdate=last_day(add_months('{today-1,yyyy-MM-dd}', -1))
)b on a.rftag=b.rftag and a.residentcitylevel_qujian=b.residentcitylevel_qujian and a.sex_group=b.sex_group and a.age_group=b.age_group
left join
(
select
pdate,
sum(cun_usernum) as sum_cun_usernum,
sum(cun_xiadan_usernum) as sum_cun_xiadan_usernum,
sum(cun_xiadan_usernum)/sum(cun_usernum) as sum_zhuanhua_rate
from dws.rf_memberid_month_dimension
where pdate=last_day(add_months('{today-1,yyyy-MM-dd}', 0))
group by pdate
)c on a.pdate=c.pdate
left join
(
select
pdate,
sum(cun_usernum) as sum_cun_usernum,
sum(cun_xiadan_usernum) as sum_cun_xiadan_usernum,
sum(cun_xiadan_usernum)/sum(cun_usernum) as sum_zhuanhua_rate
from dws.rf_memberid_month_dimension
where pdate=last_day(add_months('{today-1,yyyy-MM-dd}', -1))
group by pdate
)d on b.pdate=d.pdate
呈现方式：
思考：纯贡献度和直观分析优劣：
- 直观看数据，对于RF人群环比变化敏感度高，容易略微分层占比的影响。因为RF人群分层不均衡，对于占比大但是变化小维度观察比较费力。
- 贡献度呈现相反特性，很容易受占比大维度指标影响，对于占比量小的维度指标很难看到影响。
所以在报表中将两者进行结合呈现。
关注公众号「水沐教育科技」，在手机上阅读所有教程，随时随地都能学习。内含一款搜索神器，免费下载全网书籍和视频。
微信扫码关注公众号