面试系列六 之 用户行为数据分析

关注我的公众号【宝哥大数据】,更多干货等着你

面试系列六 之 用户行为数据分析_第1张图片

1.1、数仓分层架构

面试系列六 之 用户行为数据分析_第2张图片

分层优点:复杂问题简单化、清晰数据结构(方便管理)、增加数据的复用性、隔离原始数据(解耦)

层级 功能
ods 原始数据层 存放原始数据,保持原貌不做处理
dwd 明细数据层 对ods层数据清洗(去除空值,脏数据,超过极限范围的数据)
dws 服务数据层 轻度聚合
ads 应用数据层 具体需求

数仓中各层建的表都是外部表

1.2、埋点行为数据基本格式(基本字段)

公共字段:基本所有安卓手机都包含的字段
业务字段:埋点上报的字段,有具体的业务类型
下面就是一个示例,表示业务字段的上传。
行为数据启动日志/事件日志表关键字段:

{
"ap":"xxxxx",//项目数据来源 app pc
"cm": {  //公共字段
        "mid": "",  // (String) 设备唯一标识
        "uid": "",  // (String) 用户标识
        "vc": "1",  // (String) versionCode,程序版本号
        "vn": "1.0",  // (String) versionName,程序版本名
        "l": "zh",  // (String) 系统语言
        "sr": "",  // (String) 渠道号,应用从哪个渠道来的。
        "os": "7.1.1",  // (String) Android系统版本
        "ar": "CN",  // (String) 区域
        "md": "BBB100-1",  // (String) 手机型号
        "ba": "blackberry",  // (String) 手机品牌
        "sv": "V2.2.1",  // (String) sdkVersion
        "g": "",  // (String) gmail
        "hw": "1620x1080",  // (String) heightXwidth,屏幕宽高
        "t": "1506047606608",  // (String) 客户端日志产生时的时间
        "nw": "WIFI",  // (String) 网络模式
        "ln": 0,  // (double) lng经度
        "la": 0  // (double) lat 纬度
    },
"et":  [  //事件
            {
                "ett": "1506047605364",  //客户端事件产生时间
                "en": "display",  //事件名称  启动和事件日志是根据事件名称的不同
                "kv": {  //事件结果,以key-value形式自行定义
                    "goodsid": "236",
                    "action": "1",
                    "extend1": "1",
"place": "2",
"category": "75"
                }
            }
        ]
}

根据事件标签的不同可以分成不同的日志表

1.3、各个层的表介绍

1.3.1、ods层

1)ods_start_log 启动日志表

  • 只有一个字段 line(保存着json),按照日期dt分区,表的格式:lzo

2)ods_event_log 事件日志表(格式同启动日志表)

  • 只有一个字段 line ,按照日期dt 分区,表的格式:lzo

1.3.2、dwd层

1)dwd_start_log 启动表

  • 关键字段:mid_id,user_id,dt(分区字段,按照日期分区) (其实这是启动表和事件表的公共字段)
  • 从ods_start_log中的line用get_json_object(line,'$.mid') mid_id的方式获取字段

1.3.2.1、自定义UDF/UDTF(项目中的应用)

  • 自定义UDF函数(解析公共字段,一进一出)
  • 自定义UDTF函数(解析具体事件字段,一进多出)

    • 自定义UDF:继承UDF,重写evaluate方法
    • 自定义UDTF:继承自GenericUDTF,重写3个方法:initialize(自定义输出的列名和类型),process(将结果返回forward(result)),close
  • 为什么要自定义UDF/UDTF,因为自定义函数,可以自己埋点Log打印日志,出错或者数据异常,方便调试。

1.3.2.2、事件日志基础明细表

dwd_base_event_log 事件日志基础明细表

  • 1)关键字段:

    • 公共字段:mid_id,user_id,dt(分区字段)以及event_name、event_json、server_time
  • 2)从 ods_event_log的line 中用 UDF 获取 公共字段 和 server_time,用UDTF 获取 event_name , event_json。

1.3.2.3、商品点击表

dwd_display_log 商品点击表

  • 关键字段:公共字段 + 特有字段
  • 从dwd_base_event_log中直接获取公共字段和server_time,从 dwd_base_event_log的 event_json中获取特有字段,where event_name = "display"
  • get_json_object(event_json,'$.kv.action') action

1.3.2.4、其他的具体事件明细表

类似

表明 表注释
dwd_newsdetail_log 商品详情页表
dwd_loading_log 商品列表页表
dwd_ad_log 广告表
dwd_notification_log 消息通知表
dwd_active_foreground_log 用户前台活跃表
dwd_active_background_log 用户后台活跃表
dwd_comment_log 评论表
dwd_favorites_log 收藏表
dwd_praise_log 点赞表
dwd_error_log 错误日志表

从一张事件基础明细表dwd_base_event_log一共可以获得11张具体事件明细表

二、需求解析

2.1、用户活跃主题

2.1.1、DWS层日活明细表

每日活跃设备分析
面试系列六 之 用户行为数据分析_第3张图片

2.1.2、DWS层周活明细表

每周活跃设备分析
面试系列六 之 用户行为数据分析_第4张图片

2.1.3、DWS层月活明细表

每月活跃设备分析
面试系列六 之 用户行为数据分析_第5张图片

2.1.4、ADS层日周月活跃设备数表

活跃设备分析

面试系列六 之 用户行为数据分析_第6张图片

2.2、用户新增主题

2.2.1、DWS层日新增明细表

面试系列六 之 用户行为数据分析_第7张图片

面试系列六 之 用户行为数据分析_第8张图片

2.2.2、ADS层每日新增设备数表

面试系列六 之 用户行为数据分析_第9张图片

2.3、用户留存主题

2.3.1、用户留存介绍

面试系列六 之 用户行为数据分析_第10张图片

2.3.2、用户留存率分析

面试系列六 之 用户行为数据分析_第11张图片

2.3.3、DWS层日留存明细表

面试系列六 之 用户行为数据分析_第12张图片

2.3.4、ADS层留存用户数表

面试系列六 之 用户行为数据分析_第13张图片

2.3.5、ADS层留存用户率表

面试系列六 之 用户行为数据分析_第14张图片

2.4、沉默用户

面试系列六 之 用户行为数据分析_第15张图片

2.5、本周回流用户数

面试系列六 之 用户行为数据分析_第16张图片

2.6、流失用户数

面试系列六 之 用户行为数据分析_第17张图片

2.7、最近连续3周活跃用户数

面试系列六 之 用户行为数据分析_第18张图片

2.8、最近七天内连续三天活跃用户数

面试系列六 之 用户行为数据分析_第19张图片

2.9、需求逻辑

2.9.1 如何分析用户活跃?

在启动日志中统计不同设备id出现次数。

2.9.2 如何分析用户新增?

用活跃用户表 left join 用户新增表,用户新增表中mid为空的即为用户新增。

2.9.3 如何分析用户1天留存?

留存用户=前一天新增 join 今天活跃
用户留存率=留存用户/前一天新增

2.9.4 如何分析沉默用户?

(登录时间为7天前,且只出现过一次)
按照设备id对日活表分组,登录次数为1,且是在一周前登录。

2.9.5 如何分析本周回流用户?

本周活跃left join本周新增 left join上周活跃,且本周新增id和上周活跃id都为null

2.9.6 如何分析流失用户?

(登录时间为7天前)
按照设备id对日活表分组,且七天内没有登录过。

2.9.7 如何分析最近连续3周活跃用户数?

按照设备id对周活进行分组,统计次数大于3次。

2.9.8 如何分析最近七天内连续三天活跃用户数?

  • 1)查询出最近7天的活跃用户,并对用户活跃日期进行排名
  • 2)计算用户活跃日期及排名之间的差值
  • 3)对同用户及差值分组,统计差值个数
  • 4)将差值相同个数大于等于3的数据取出,然后去重(去的是什么重???),即为连续3天及以上活跃的用户

你可能感兴趣的