高端用户研究
QY
2016年11月2日
整体模型框架
高端用户的定义
我们这里讨论的是希望找出一批高端的用户，这里所谓的高端本身就有一定的模糊性和复杂性。我们很难通过一个简单的逻辑而准确无误的判断高端的用户。
我们试图且应该做的是找到高端用户的一个大闭包，尽可能的将高端用户包含在内。当然找到最小的闭包且100%包含是理想的情况。
在定义我们的高端用户之前，我们先接单的了解一下之前已有的一些成果和表格： 高价值用户：这里的高价值用户，之前的定义是讲用户的购买金额求和，客单价计算。 app_cmo_user_buyview_value_da 这个表其中里面计算了每个pin在三级类目下的累计购买金额和客单价，并且按照9/10,1/4,1/2,3/4将用户分为5个级别。
关于高端的用户和高价值的用户，其实两者的含义具有一定模糊性（可以认为相同？）。两者必然存在相互交叉的关系。
一个极端的情况是用户买的多，但是每次都是买些便宜的东西。订单中SKU数量多，所以订单客单价会偏高（换成SKU客单价会好?)
关于高端用户的一个思路，当然这只是一个思路： 我们将用户所购买的所有SKU分级，按照三级类目来看。SKU采用京东价，按照10%，30%，50%，70%，90%分为ABCDEF6个级别。
这里SKU的京东价在全年会有变化，我们可以忽略其中的变化，采用SKU全年最低京东价。 从结果来看，很多SKU价格异常，包括很多0.1元，10亿元的有效订单。所以讲数据中sku价格超过20万的剔除。小于10元的剔除。三级类目中总SKU数量不足100个的剔除。还有一些其他异常情况（包括测试sku，图书类目等）
一个更好的方法是由三级部门采销经理按照自己的逻辑去进行划分，这样可以很好的规避脏数据，这种收集的成本会很高。
通过上述方法，我们可以给出每个三级类目SKU的价格层（caodalong_skufenlei_0510）。 通过表caodalong_skufenlei_0510关联用户购买的sku，可以得到三及类目下的用户分段购买统计（caodalong_yonghu_0511），进一步的可以得到用户每个ABCDEF的sku购买比例。 到此，我们可以简单的定义每个三级类目的高端用户了，可以按照比例，例如三级类目下该用户购买A的比例30%以上，购买次数10单以上这样的用户。这里我们可以用R的雷达图来进行描述： 
雷达图可以看出，我们组的mika相对其他几个人比较高端一些（哈哈她从不关注优惠 可能是另一个出发点）。后来证明她确实高端。 但是要得到全站的高端用户，不是简单的将上面的数据进行聚合这么简单。我们看下面一个例子：
很显然，更加科学的说，礼品箱包在判断一个用户是否是高端用户的时候显然要比一个食品饮料类类目的权重大。（有些人会认为买148的坚果也是高端，不置可否）。 这里需要每个类目引入权重的概念。
简单的来看，如果这个三级类目的Expect，Variance比较大，那么他的权重应该大。我们想合理的给用户找到这么一个函数F(x(ABCDEF),VAR,E)，这个函数在对VAR,E是增函数。
\(F= \Sigma g(y_{i})E_{y_{i}}log(e+\sigma_{y_{i}})\)
\(g(y_{i}) = \frac{y_{i}下AB分级数量}{y_{i}下总购买数量}\)
高端用户评分
按照之前的逻辑，我们可以给出每个三级类目的评分，三级类目按照他的均值和标准差得分。总评分越高表示该三级类目越能区分出用户的高端性。 取前几个总评分的类目如下：
其中加粗的是鄙人认为相对高端的东西。 我们同时看下各个1级类目总分值的分布。
珠宝首饰，家用电器，家具，电脑办公相对得分较高。 与业务方讨论后，他们给出了几个认为高端的品牌，我们计算了AB分段的sku占比如下：
可以看到出了老鬼和OAKELEY占比略低外，其他品牌AB类SKU比例高。
其次我们按照每个人每个类目下的ABsku的比例，来计算这个用户在该类目的得分。例如该用户只购买一个A或者B，那么他可以得到该类目全部的分数。如果购买了个A，一个C，那么只得该类目的一半分。
当然，这个算法还是有些误差的。比如AB分类的SKU差距特别大，例如上面的无人机，1148和5999差距过大。
其次用户在一些非重要类目如果分值较高的话可能户影响分值，或者该用户在低端类目买购买的类目数过多会影响。
该方法的一个优点是如果这个类目本身评分较低，用户是不会得到较高分数，用户在CDEF下单再多也不会得分。我们来看一下这个分布：
在进行营销的时候可以酌情选取分数段。该表格(caodalong_youhuipingfen_0520)
注意：很多账号是大家公用的，选择用户的时候最好卡订单在10单到300单之前的用户（1年）。超过300单则有问题。
高端用户评分实测
首先我们假设高端的用户购买奢侈品的比例会高，换句话也就是说分值越高的话用户购买的可能性越高。
方案： 5月26日APP首焦营销，会有一个APP端的奢侈品的活动页面，我们想通过三个用户包的不同比例来测试一下该分组的可靠程度。
处理方式： 总共750万用户pin，分成3个组，每个250万，来观测点击UV和转化率。
首先取APP最近3天的登陆用户的，按照登陆次数排名。大概2000万左右。
其次去除购买超过300单以上的用户pin，按照分数降序排列。
取top250万作为包1，bot250万作为bao3，剩下的随机抽取250万作为包2.
结果：
通过简单的buket的实验，可以看出高端的用户包是有优势的。从而一个侧面反映出我们的打分是合理的，可以接受的。当然不是100%精准的。
相关的Code
SQL
根据修改过的类目信息，重新导入；
use dev;
create
table han_gdyh_cate_level_0926
(
item_third_cate_cd string,
bili double
)
row format delimited fields terminated by '\t' stored as textfile；用户简单聚合
use dev;
drop table han_gdyh_juhe_0926;
create table han_gdyh_juhe_0926 as
select
m.item_first_cate_cd ,
min(m.item_first_cate_name) item_first_cate_name ,
m.item_second_cate_cd ,
min(m.item_second_cate_name) item_second_cate_name,
m.item_third_cate_cd ,
min(m.item_third_cate_name) item_third_cate_name ,
m.user_log_acct ,
level , ----6个level
count(distinct item_sku_id) / skunum bili ,---每个level的比例
count(distinct item_sku_id) level_sku_num ,---每个level的数量
skunum ,---每个类目购买的总sku数
user_sum_amt ----用户15年至今的用户贡献金额
from
(
select
item_first_cate_cd ,
min(item_first_cate_name) item_first_cate_name ,
item_second_cate_cd ,
min(item_second_cate_name) item_second_cate_name,
item_third_cate_cd ,
min(item_third_cate_name) item_third_cate_name ,
user_log_acct ,
item_sku_id ,
case when after_prefr_amount >= prc_a then 'level_1'
when after_prefr_amount >= prc_b and
after_prefr_amount < prc_a then
'level_2' when after_prefr_amount >=
prc_c and after_prefr_amount <
prc_b then 'level_3' when
after_prefr_amount >= prc_d and
after_prefr_amount < prc_c then
'level_4' when after_prefr_amount >=
prc_e and after_prefr_amount <
prc_d then 'level_5' when
after_prefr_amount < prc_e then
'level_6' else null end as level,
sale_qtty
from
(
select * from dev.x_gaoduan_zongdingdan_0829
)
a
join
(
select
p.*
from
(
select * from
app.app_cmo_user_gdyh_cate_da #各三级类目价格分位数和SKU数量
)
p
join
(
select * from
dev.han_gdyh_cate_level_0926
)
q
on
p.item_gen_third_cate_id =
q.item_third_cate_cd
)
b on a.item_third_cate_cd = b.item_gen_third_cate_id
group by
item_first_cate_cd ,
item_second_cate_cd,
item_third_cate_cd ,
user_log_acct ,
item_sku_id ,
case when after_prefr_amount >= prc_a then 'level_1'
when after_prefr_amount >= prc_b and
after_prefr_amount < prc_a then
'level_2' when after_prefr_amount >=
prc_c and after_prefr_amount <
prc_b then 'level_3' when
after_prefr_amount >= prc_d and
after_prefr_amount < prc_c then
'level_4' when after_prefr_amount >=
prc_e and after_prefr_amount <
prc_d then 'level_5' when
after_prefr_amount < prc_e then
'level_6' else null end,
sale_qtty
)
m
join
(
select
item_first_cate_cd ,
min(item_first_cate_name) item_first_cate_name ,
item_second_cate_cd ,
min(item_second_cate_name) item_second_cate_name,
item_third_cate_cd ,
min(item_third_cate_name) item_third_cate_name ,
user_log_acct ,
count(distinct item_sku_id) skunum
from
dev.x_gaoduan_zongdingdan_0829
group by
item_first_cate_cd ,
item_second_cate_cd,
item_third_cate_cd ,
user_log_acct
)
k
on
m.item_third_cate_cd = k.item_third_cate_cd
and m.user_log_acct = k.user_log_acct
join
(
select
user_log_acct,
sum(after_prefr_amount) user_sum_amt
from
dev.x_gaoduan_zongdingdan_0829
group by
user_log_acct
)
t
on
m.user_log_acct = t.user_log_acct
group by
m.item_first_cate_cd ,
m.item_second_cate_cd,
m.item_third_cate_cd ,
m.user_log_acct ,
level ,
skunum ,
user_sum_amt宽式变列式
—按sku数，列式变宽式
use dev;
create table han_gdyh_juhe_fushi_0926V2 as
select
item_first_cate_cd ,
min(item_first_cate_name) item_first_cate_name ,
item_second_cate_cd ,
min(item_second_cate_name) item_second_cate_name ,
item_third_cate_cd ,
min(item_third_cate_name) item_third_cate_name ,
user_log_acct ,
case when level = 'level_1' then bili else 0 end as level_1,
case when level = 'level_2' then bili else 0 end as level_2,
case when level = 'level_3' then bili else 0 end as level_3,
case when level = 'level_4' then bili else 0 end as level_4,
case when level = 'level_5' then bili else 0 end as level_5,
case when level = 'level_6' then bili else 0 end as level_6
from
dev.han_gdyh_juhe_0926
group by
item_first_cate_cd ,
item_second_cate_cd ,
item_third_cate_cd ,
user_log_acct ,
case when level = 'level_1' then bili else 0 end,
case when level = 'level_2' then bili else 0 end,
case when level = 'level_3' then bili else 0 end,
case when level = 'level_4' then bili else 0 end,
case when level = 'level_5' then bili else 0 end,
case when level = 'level_6' then bili else 0 end
指标筛选
use dev;
drop table han_gdyh_juhe_0926V3;
create table han_gdyh_juhe_0926V3 as
select
k.item_first_cate_cd ,
min(k.item_first_cate_name) item_first_cate_name ,
k.item_second_cate_cd ,
min(k.item_second_cate_name) item_second_cate_name,
k.item_third_cate_cd ,
min(k.item_third_cate_name) item_third_cate_name ,
k.user_log_acct ,
level_1 ,
level_2 ,
level_3 ,
level_4 ,
level_5 ,
level_6 ,
top2_bili ,
-- 每个用户在三级类目下level_1和level_2一起的比例
level_1_cate_bili, --- 用户有level_1的三级类目数占总三级类目的比例
catenum , ----用户总共购买过的三级类目数
user_sum_amt ----用户至今贡献金额
from
(
select * from dev.han_gdyh_juhe_fushi_0926V2
)
k
left outer join
(
select
item_first_cate_cd ,
min(item_first_cate_name) item_first_cate_name ,
item_second_cate_cd ,
min(item_second_cate_name) item_second_cate_name,
item_third_cate_cd ,
min(item_third_cate_name) item_third_cate_name ,
user_log_acct ,
sum(level_1 + level_2) top2_bili
from
dev.han_gdyh_juhe_fushi_0926V2
group by
item_first_cate_cd ,
item_second_cate_cd,
item_third_cate_cd ,
user_log_acct
)
t
on
t.user_log_acct = k.user_log_acct
and t.item_third_cate_cd = k.item_third_cate_cd
left outer join
(
select
a.user_log_acct,
catenum ,
catenum_level_1 / catenum level_1_cate_bili
from
(
select
user_log_acct,
count(distinct item_third_cate_name)
catenum
from
dev.han_gdyh_juhe_fushi_0926V2
group by
user_log_acct
)
a
left outer join
(
select
user_log_acct,
count(distinct item_third_cate_name)
catenum_level_1
from
dev.han_gdyh_juhe_fushi_0926V2
where
level_1 > 0
group by
user_log_acct
)
b
on
a.user_log_acct = b.user_log_acct
)
m on t.user_log_acct = m.user_log_acct
group by
k.item_first_cate_cd ,
k.item_second_cate_cd,
k.item_third_cate_cd ,
k.user_log_acct ,
level_1 ,
level_2 ,
level_3 ,
level_4 ,
level_5 ,
level_6 ,
top2_bili , ----每个用户在三级类目下level_1和level_2一起的比例
level_1_cate_bili ,
catenum ,
user_sum_amt
剔除用户
---筛选购买sku数在5-500之间的
use dev;
drop table han_gdyh_juhe_0926V4;
create table han_gdyh_juhe_0926V4 as
select
item_first_cate_cd ,
item_first_cate_name ,
item_second_cate_cd ,
item_second_cate_name,
item_third_cate_cd ,
item_third_cate_name ,
a.user_log_acct ,
level_1 ,
level_2 ,
level_3 ,
level_4 ,
level_5 ,
level_6 ,
top2_bili ,
level_1_cate_bili ,
catenum ,
user_sum_amt ,
c.skunum
from
(
select * from han_gdyh_juhe_0926v3
)
a
join
(
select
user_log_acct,
sum(skunum) skunum
from
(
select
item_first_cate_cd ,
item_first_cate_name ,
item_second_cate_cd ,
item_second_cate_name,
item_third_cate_cd ,
item_third_cate_name ,
user_log_acct ,
skunum
from
dev.han_gdyh_juhe_0926
group by
item_first_cate_cd ,
item_first_cate_name ,
item_second_cate_cd ,
item_second_cate_name,
item_third_cate_cd ,
item_third_cate_name ,
user_log_acct ,
skunum
)
a
group by
user_log_acct
having
sum(skunum) >= 5
and sum(skunum) <= 1000
)
c
on
a.user_log_acct = c.user_log_acct
group by
item_first_cate_cd ,
item_first_cate_name ,
item_second_cate_cd ,
item_second_cate_name,
item_third_cate_cd ,
item_third_cate_name ,
a.user_log_acct ,
level_1 ,
level_2 ,
level_3 ,
level_4 ,
level_5 ,
level_6 ,
top2_bili ,
level_1_cate_bili ,
catenum ,
user_sum_amt ,
c.skunum
加入类目调整比例
use dev;
create table han_gdyh_juhe_0926V5 as
select
item_first_cate_cd ,
min(item_first_cate_name) item_first_cate_name ,
item_second_cate_cd ,
min(item_second_cate_name) item_second_cate_name,
a.item_third_cate_cd ,
min(item_third_cate_name) item_third_cate_name ,
user_log_acct ,
level_1 *(1 + bili) level_1 ,
level_2 ,
level_3 ,
level_4 ,
level_5 ,
level_6 ,
top2_bili *(1 + bili) top2_bili ,
level_1_cate_bili ,
skunum ,
catenum ,
user_sum_amt
from
(
select * from dev.han_gdyh_juhe_0926V4
)
a
join
(
select * from dev.han_gdyh_cate_level_0926
)
b
on
a.item_third_cate_cd = b.item_third_cate_cd
group by
item_first_cate_cd ,
item_second_cate_cd ,
a.item_third_cate_cd ,
user_log_acct ,
level_1 *(1 + bili) ,
level_2 ,
level_3 ,
level_4 ,
level_5 ,
level_6 ,
top2_bili *(1 + bili),
level_1_cate_bili ,
skunum ,
catenum ,
user_sum_amt
数据指标结果提取&为分批提取数据做准备
drop table han_gdyh_juhe_0926V6;
create table han_gdyh_juhe_0926V6 as
select
user_log_acct ,
top1_bili ,
top2_bili ,
level_1_cate_bili,
catenum ,
skunum ,
user_sum_amt ,
row_number() over(partition BY 1 order by top1_bili desc) AS rn
from
(
select
user_log_acct ,
avg(level_1) top1_bili ,
avg(top2_bili) top2_bili,
level_1_cate_bili ,
skunum ,
catenum ,
user_sum_amt
from
dev.han_gdyh_juhe_0926V5
where
level_1_cate_bili > 0
and level_1 > 0
group by
user_log_acct ,
level_1_cate_bili,
skunum ,
catenum ,
user_sum_amt
)
a
group by
user_log_acct ,
top1_bili ,
top2_bili ,
level_1_cate_bili,
catenum ,
skunum ,
user_sum_amt用户总购买金额
use dev;
create table han_all_user_amt_0927 as
select
user_log_acct ,
sum(valid_ord_num) ordnum,
sum(valid_sale_amt) amt
from
app.app_cmo_user_buyview_full_di
where
dt = '2016-08'
and dp = 'ago'
and valid_ord_num > 0
and valid_sale_amt > 0
group by
user_log_acct
用户购买最贵sku：
use dev;
create table han_gdyh_best_sku_0928 as
select
*
from
(
select
user_log_acct ,
item_sku_id ,
item_first_cate_name ,
item_second_cate_name,
item_third_cate_name ,
after_prefr_amount ,
row_number() over(partition by user_log_acct order by
after_prefr_amount desc) as high_level_price
from
x_gaoduan_zongdingdan_0829
group by
user_log_acct ,
item_sku_id ,
item_first_cate_name ,
item_second_cate_name,
item_third_cate_name ,
after_prefr_amount
) a
where
high_level_price<= 2
最后指标聚合
use dev;
drop table han_gdyh_result_0929;
create table han_gdyh_result_0929 as
select
a.user_log_acct ,
a.top1_bili ,
b.item_sku_id ,
b.sku_jiage ,
c.1st_sale_ord_dt,
a.user_sum_amt ,
a.fenlei
from
(
select * from dev.han_gdyh_result_0928_v1
union all
select * from dev.han_gdyh_result_0928_v2
union all
select * from dev.han_gdyh_result_0928_v3
union all
select * from dev.han_gdyh_result_0928_v4
union all
select * from dev.han_gdyh_result_0928_v51
union all
select * from dev.han_gdyh_result_0928_v52
)
a
left outer join
(
select
user_log_acct,
item_sku_id ,
after_prefr_amount sku_jiage
from
dev.han_gdyh_best_sku_0928
where
high_level_price = 1
)
b
on
a.user_log_acct = b.user_log_acct
left outer join
(
select
user_log_acct,
min(1st_sale_ord_dt) 1st_sale_ord_dt
from
gdm.gdm_m01_user_first_cate_da
where
dt = sysdate( - 1)
and 1st_all_yn = 1
and 1st_sale_ord_id is not null
group by
user_log_acct
)
c
on
a.user_log_acct = c.user_log_acct
group by
a.user_log_acct ,
a.top1_bili ,
b.item_sku_id ,
b.sku_jiage ,
c.1st_sale_ord_dt,
a.user_sum_amt ,
a.fenlei