《数据需求处理场景》超强带入感，还有谁！

1.目的

作为一线战斗的民工, 这次给大家带来: 用户行为数据的"四十二章经", 浓缩后烘干送你跟前, 来取经儿吧！为了方便理解，我抽象出用户在app的主要数据信息, 争取用最简单的数据, 表达尽可能多的数据需求场景, 比如: 日活, 周活, 月活，日留存，uv, pv, source，rfm，用户画像等

1.1 阅读的直接收获

你能感受到实际工作中常见的场景，并学会基本的应对

1.1 如果你能实操下

二八原理，你能get到常规数据需求处理的80%，满意吗？

知易行难，建议还是了解下，实际厂里是怎么数据赋能业务的，先扔一个数分训练营，了解数据分析如何赋能业务，高手绕行，小白查收~

2.数据源

第一张表：可以当做阉割版的用户画像表, 因为我们就只放了用户的基本属性信息

第二张表：可以当做用户行为表, 比如: 用户从那个渠道浏览那个页面, 浏览开始和结束时间. 这个页面可能是购物页面? 知乎问答页面? 淘宝店铺页面? 直播房间页面? anyway, 所想即所得

第三张表：可以当做用户关键行为转化表, 这里我们假设付费是关键行为, 当然也可以把点击, 关注, 加购物车, 评论等等当做关键行为, 具体要看业务场景和关注点

以上几个简单的表, 可以映射常规的业务场景, 比如:

电商平台浏览购物?
游戏平台打怪充值?
视频用户购买会员?
直播观看者打赏?
信息量广告位点击?
微信文章阅读赞赏? (真是一种奢求... 哈哈) ... ...

怎么样? 开心不? 是不是有种踏破拖鞋无觅处, 读完前奏就想赞的冲动?

为了这篇文章，我专门造了一批数据

如果想练习，就直接在我提供的数据环境下运行就中, 没有申请环境申请，加微信留言(姓名+行业+申请数据需求练习环境)。

3.数据需求来袭

ok, 我们开始! 先给大家介绍几位同学

一个周二早晨, 在一间狭小的会议室里, 小营和大品拉着你在策划一场大型会员营销

小营: 上周我们策划营销方案, 老板很满意, 准备先烧1个亿, 走一波, 所以大家一起聊下, 然后安排排期大品: 我和小营已经对过活动方案, 产品交互已经ready, 业务开发逻辑跟小马对过没问题, 这次主要跟小数过下埋点
小数: ok, 先明确下目的, 然后让小马帮忙在web或后端埋点, 再上线前, 我来验证埋点, 一切顺利的话, 活动结束后, 数据我及时反馈大家
小营: 这次预算充足, 我们选择了多个渠道来引流, 需要监测各渠道引流效果, 前期我们希望了解下历史那个渠道引流效果好, 然后预算向优质渠道倾斜
小数: 这个好办, 我先记下, 没有新渠道吗? 如果都是老渠道, 旧埋点是支持的, 就不需要麻烦小马加埋点了
大品: 有新的渠道, 但是不清楚效果如何, 所以我建议全量上线前, 测试下, 找出优质渠道, 再分配预算
小数: 确实很有必要, 所以还需要麻烦小马把新渠道的埋点数据添加到我们 user_action.source 字段中
小马: 没问题, 回头埋点名称和渠道对应关系, 我会更新到埋点wiki文档, 再邮件发给大家确认
大品: 还有个需要小数帮忙, 我们的付费产品, 但这次大促会极低价格销售, 我希望可以跟踪这些用户.再大促后1个月内复购情况, 希望了解什么特征的用户, 复购率比较高, 再通过历史平均用户生命周期的价值, 来评估这次营销的短期和长期成本回收, 如果效果好, 年底奖金就有着落咯
小数: 哈哈怒赞啊我复述你们的需求, 看是不是理解ok哈:

评估现有渠道价值, 我可能会从各渠道日活分布, 拉新成本, 用户生命周期价值来衡量
评估新渠道价值, 这个需要新渠道投放后, 计算各渠道平均拉新成本, 然后计算注册一周内的ARPU值, 对比原来渠道用户开始一周的ARPU值
活动期间各渠道新老用户购买金额, 活动后1个月内新老用户复购次数和金额

4.搞定数据需求

4.1 Are You Ready？

对接完需求, 小数就开始数据提取的工作, 我们看看小数在做事情之前, 都需要ready的知识

数据处理: sql, shell, excel, others
业务数据: 数据存放的表结构, 每个字段代表的含义, 以及协调新数据埋点
理解需求: 这个很重要, 需要清楚需求方的目的, 明确那些数据上可以支持, 哪些没有数据暂时无法支持, 而不是一股脑让业务方带坑里去, 最后把需求的目的转化为数据指标
统计口径: 每个数据指标如何计算的

4.2 Go！

数据需求结果，用指标和脚本搭配展示，初学者，要好好理解指标表达的业务含义，计算逻辑，怎么算好好理解了呢？最好是先自己手动写一遍，然后再跟我提供的脚本做对比。

4.2.1 渠道价值评估

旧渠道

历史投放成本, 直接服务业务方的拉新成本
各渠道日活分布, 小数帮忙支持

select t1.date, t2.source, count(*) as userNum
    from
    (select date, userid
    from user_view_action
    where date>=20190101 and date<=20190501
    group by date, userid) t1
    left join
    (select userid, source
        from user_profile) t2 on t1.userid=t2.userid
    group by t1.date, t2.source

新渠道

日留存: 即新注册用户, 在注册后每日活跃的用户量

留存的计算, 可以特别注意下, 面试可能会出现，假设新渠道拉新的广告投放在 20190301-20190307 这一周, 计算后期每日留存用户量

select t1.registerday, t2.date, count(t2.userid) as `每日活跃用户数`
from
(select registerday, userid
from user_profile
where date>=20190301 and date<=20190307) t1
left join
(select date, userid
from user_view_action
where date>=20190501
group by date, userid ) t2 on t1.userid=t2.userid
group by t1.registerday, t2.date

转化率: 开始30天内付费率, 开始30天内付费金额

-- 新用户30天内的付费率 =  未来30天内付费用户数 / 当天注册用户数
select t1.registerday, count(1) as registerUserNum,
count(case when datediff(t2.date,t1.registerday)<=30 then t2.userid) as payUserNum,
sum(case when datediff(t2.date,t1.registerday)<=30 then t2.income) as income
from
(select registerday, userid
from user_profile
where date>=20190301 and date<=20190307) t1
(select date, userid, sum(price) as income
from user_key_action
where date>=20190501
and status=1
group by date, userid
) t2 on t1.userid=t2.userid
group by t1.registerday

4.2.2 用户价值衡量

小数帮忙确定算法, 并提供数据，衡量方法可以DIY, 但要合理:

简单粗暴的算法

计算不同渠道全部用户的平均订单金额, 以此来衡量渠道价值

简单逻辑的算法

我们只看最近3个月各渠道过来的用户, 订单转化情况? 毕竟时间越近数据越有参考价值

温柔精细的算法

目的是衡量渠道用户价值, 至于算法, 你们定哈, 怎么快怎么简单怎么来, 甚至只拉一个月前一周注册用户, 各渠道留存, 订单转化率, 购买频次就可以? 甚至金额都可以暂时不体现

严肃较真儿算法

以上三种算法都可以看下, 但还希望看到更细分的维度, 比如: 每个渠道, 按终端, 留存率, 单次活跃时长, 活跃天数, 是否付费, 付费次数, 付费金额等等, 多方面来评估用户价值

先列这么多?

篇幅不想太长，这里只抛出简单粗暴算法的SQL:

select t1.source, sum(t2.avg)/count(*) as avgUserValue
    from
    (select source, userid
        from user_profile) t1
    left join
    (select userid, sum(price) as avg
    from user_key_action
    where status=1  --注释: 表示支付成功
    group by userid) t2 on t1.userid=t2.userid
    group by t1.source

其他几种自己试试写下? 最好在我提供给小白的数据环境跑一下, 如果想搞又搞不定, 直接加我微信, 过来人的经验不用多浪费...

4.2.3 活动后期追踪

活动期间各渠道新老用户购买金额, 活动后1个月内新老用户复购次数和金额

(鉴于篇幅, sql就不贴在文章里了, 其实逻辑上和前面类似, 如果有兴趣, 微信我应该是必回，但可能不太及时)

4.3 面试，你猜我问什么

如果要每天更新一个用户画像表, 你会添加哪些维度, 请写出对应SQL
如果让你定义一个用户活跃度的指标, 你会怎么定义?
如果对用户价值分层, 有有什么建议?
如果希望搭建用户流失预警的模型, 有会从哪里着手?
如果运营希望提升用户付费转化率, 你从数据上会提供哪些支持?

最后扔一个初级入门数分训练营，可以了解数据分析如何赋能业务，如果你是圈里有经验的人，请绕行，如果是想入门数据、业务知识，建议查收~

关于经哥

帝都北五环外，码农集聚村，回龙观的一位数据老民工，欢迎加v唠嗑、吐槽

2019年开始写写数据民工那些大白话，定期带来一些数据民工专属干货，如果你有其他行业的数据干货，欢迎也晒给大家伙儿！集思广益，普惠于民工汪洋大世界！！

人走赞留，江湖再见，蟹蟹！