数据仓库之创作者分层模型服务构建

前言

打造创作者分级运营，帮助创作者成长的重要前提条件。

通过对数据层面的汇总和计算，可以支撑到后续的若干应用：

\1. 运营在创作者 DB 管理工具进行查询操作；

\2. 不同层级创作者的变化情况分析，以帮助到分层模型的迭代；

\3. 不同层级创作者的实际表现，可以支撑产品和业务业务对实际情况的判断；

目标

创作者分层涉及到的数据散落在各处，主要的信息散布点有：

\1. 创作者后台（关注创作者内容）——Ops标注的数据；

\2. BE后台（关注对创作者服务）——创作者的粉丝总量/第一次登录时间/最后一次登录时间等；

\3. 内容库（提供创作者历史发布的相关信息）——创作者发布的视频数量/第一次发布/最后一次发布等；

\4. 大数据——创作者最近7天增粉数/其他行为类数据

所以，为了完成创作者分层模型的构建和支撑后续的业务应用，需要综合各方数据，打造合理的数据生产方式。

一、数据流程方案

1.数据流程注意事项：

①数据拉取时间范围：T+1；

当大数据从业务库中拉取数据时，需要有时间标识，如：modification_time (修改时间) 字段，拉取规则：：modification_time < T+1；

②在避免影响线上业务情况下拉取数据，建议业务方提供「备用库」作为数据拉取方案中数据来源；

\2. 大数据负责汇集、清洗、转换、计算数据

①数据来源：

内容库、关注服务、ops上传等数据来源；

②数据处理：

数据进行汇集、清洗、轻度聚合、分层计算；

③数据流向：

写入提供的创作者业务库中；

二、数据接入

以“关注创作者表”为例：

\1. 接入方式——拉链表

①采取拉链表原因：数据量比较大，每次只修改表中部分字段，如：deleted，updated 字段，如果每次保留全量数据，则其中很多不变的信息双倍存在，对存储是极大的浪费。

②接入方式：首次全量接入到 tmp_wechat

_creator_follow_detail_di 表中，再到 ods_wechat_creator_follow_hi，中间过程 SQL 如下：（涉及语法下节介绍）

INSERT OVERWRITE TABLE tmp_zapee_creator_follow_detail_di PARTITION(dt = '2020-03-04')SELECT     id    ,userId AS user_id    ,targetUserId AS target_user_id    ,app    ,(CASE WHEN deleted = 1 then 1 WHEN deleted = 0 then 0 else -1 end) deleted    ,creation    ,modificationfrom Follow.FollowWHERE modification < '2020-03-05 00:00:00'MSCK REPAIR TABLE prod_creator.tmp_zapee_creator_follow_detail_di;INSERT OVERWRITE TABLE {0}_creator.ods_zapee_creator_follow_hiSELECT     id        ,user_id        ,target_user_id    ,app    ,deleted    ,creation    ,modification    ,'{1}' AS start_date    ,'9999-12-31' AS end_datefrom prod_creator.tmp_zapee_creator_follow_detail_diWHERE dt = '2020-03-04'

2.接入周期

每日定时接入上一天的「新增」 和 「修改」数据；

其他库表数据接入方式类似，不再赘述

三、数据ETL逻辑

源码如下：

四、创作者分层关联指标

1.历史发布上线内容数total_online_videos总粉丝数 total_followers；

2.最近14天新增粉丝数last_14days_followers

3.最近14天上线内容数 last_14days_online_videos

4.首次上线视频距今天数（取unistatus=true的视频里最早的时间） days_from_first_online_date

5.内容分类 (这里和分层的保持一致即可，top1的分类 top_1_category_group，top2的分类 top_2_category_group)

6.Category_group下的上线内容数

Group by user_id, uni_category, 需要把 uni_category 映射到分组，分组文档在最下面，时间限制为最近 90 天发布视频，

7.历史发布上线内容数：总的发布视频数 （uniStatus=True的视频数） 重复了

8.历史Publish成功的视频数 total_publish_videos

9.总粉丝数：被别人加粉的uv总数量 重复了

10.视频被点赞数：被点赞的次数 pv total_video_likes

11.视频被评论数：被评论的次数 pv total_video_comments

12.视频被分享数：被分享的次数 pv total_video_shares

13.视频被下载数：被下载的次数 pv total_video_downloads

14.视频总播放数：被播放的次数 pv total_video_plays

15.5s+ 视频总播放数 total_video_5s_plays

「作为消费者，送出的赞、评、分享、followee 数」-- 以人维度汇总

16.5s+ 视频总浏览数 total_video_5s_views

17.视频浏览数 total_video_views

18.Follow 总数：follow别人的总量 total_followees

19.总点赞数：点赞视频的次数 pv total_likes

20.总评论数：评论视频的次数 pv total_comments

21.总分享数：分享视频的次数 pv total_shares

22.总下载数：下载视频的次数 pv total_downloads

一个数据人的自留地是一个助力数据人成长的大家庭，帮助对数据感兴趣的伙伴们明确学习方向、精准提升技能。

扫码关注我，带你探索数据的神奇奥秘