数仓分层架构

用户行为数据

ODS层

设备主题宽表（4个指标）设备唯一标识用户标识程序版本号程序版本名系统语言渠道号安卓系统版本区域手机型号手机品牌SDKVersiongmail屏幕宽高客户端日志产生的时间网络模式经度维度首次活跃时间（date）末次活跃时间（date）当日活跃次数（count）累计活跃天数（count）

启动日志ods_start_log

1）全量商品维度表优惠券信息表活动维度表

2）特殊地区维度表时间维度表

事件日志ods_event_log

DWS层

统计当天各主题宽表，服务于层DWT

（预留）每日活动统计（2个指标）活动编号活动名称活动类型开始时间结束时间创建时间下单次数（count）支付次数（count）

商品主题宽表（32个指标）sku_idspu_id最近30日被下单次数（count）最近30日被下单件数（count）最近30日被下单金额（amount）累计被下单次数（count）累计被下单件数（count）累计被下单金额（amount）最近30日被支付次数（count）最近30日被支付件数（count）最近30日被支付金额（amount）累计被支付次数（count）累计被支付件数（count）累计被支付金额（amount）最近30日退款次数（count）最近30日退款件数（count）最近30日退款金额（amount）累计退款次数（count）累计退款件数（count）累计退款金额（amount）最近30日被加入购物车次数（count）最近30日被加入购物车件数（count）累计被加入购物车次数（count）累计被加入购物车件数（count）最近30日被收藏次数（count）累计被收藏次数（count）最近30日好评数（count）最近30日中评数（count）最近30日差评数（count）最近30日默认评价数（count）累计好评数（count）累计中评数（count）累计差评数（count）累计默认评价数（count）

2）周期型加购表收藏表

会员主题1）会员主题信息活跃会员数新增会员数新增消费会员数总付付费会员数总会员数会员活跃率会员付费率会员新鲜度=当天新增会员数/当天活跃会员数2）漏斗分析总访问人数加入购物车的人数访问到加入购物车转化率加入购物车到下单转化率加入购物车到支付转换率

以分析主题推向为建模驱动，基于上层的应用和产品的指标需求，构建主题对象的全量宽表

商品点击表商品详情页表商品列表页表广告表消息通知表收藏表评论表错误日志表点赞表用户后台活跃表

增量表（6张）订单详情表支付流水表订单状态表商品评论表退单表活动订单关联表

事实表（8张）

1）保持数据原貌2）压缩LZO3）创建分区表

全量表（12张）SKU商品表SPU商品表商品一级分类表商品二级分类表商品三级分类表品牌表加购表收藏表优惠券表活动表优惠规则表编码字典表

每日购买行为（18个字段）用户id商品id用户性别用户年龄用户等级商品价格商品名称品牌id商品三级品类id商品二级品类id商品一级品类id商品三级品类名称商品二级品类名称商品一级品类名称商品spu购买个数（count）当日下单单数（count）当日下单金额（amount）

业务数据（23张表）

各个主题的指标分析

会员主题宽表（16个指标）用户id首次登陆时间（date）末次登陆时间（date）累计登陆天数（count）最近30天登陆天数（count）首次下单时间（date）末次下单时间（date）累计下单次数（count）累计下单金额（amount）最近30日下单次数（count）最近30日下单金额（amount）首次支付时间（date）末次支付时间（date）累计支付次数（count）累计支付金额（amount）最近30日支付次数（count）最近30日支付金额（amount）

设备主题1）活跃设备数    日活    周活    月活2）每日新增设备   首次活跃时间=今天3）沉默用户数    定义：安装当天启动，启动时间是七天前  首次启动时间=末次启动时间 AND  末次启动时间 <=七天前4）本周回流用户  定义：本周活跃，上周未活跃，且不是新用户   5）流失用户数    定义：最近七天没有活跃6）留存率  定义：某天新增用户，经过一个周期后，还继续活跃的部分7）最近连续三周活跃（难点）    本周活跃  AND 上周活跃  AND 上上周活跃  （本周活跃 union all   上周活跃 union all    上上周活跃 ）  having  count(*) = 38)最近七天内连续三天活跃用户数（难难难点）     活跃日期-rank    group by mid         count(*)>=3        //注意去重  

营销主题1）下单数目统计每日下单数下单金额下单用户数2）支付信息统计每日支付金额支付人数支付商品数支付笔数下单到支付的平均时长3）复购率（每日购买行为表）每个用户购买每个商品（品牌）的复购率

特殊表(2张)省份表地区表

每日设备行为（1个指标）设备唯一标识用户标识程序版本号程序版本名系统语言渠道号安卓系统版本区域手机型号手机品牌SDKVersiongmail屏幕宽高客户端日志产生的时间网络模式经度维度活跃次数（count）

3）累积型优惠券领用表订单表

DWD层

增量及更新（3张）订单表用户表优惠券领用表

（预留）优惠券主题宽表（6个指标）优惠券id当日领用次数（count）当日下单使用次数（count）当日支付使用次数（count）累计领用次数（count）累计下单使用次数（count）累计支付使用次数（count）

ADS层

维度表（6张）

每日会员行为（7个指标）用户id登录次数（count）加入购物车次数（count）加入购物车金额（amount）下单次数（count）下单金额（amount）支付次数（count）支付金额（amount）

每日商品行为（16个指标）sku_id被下单次数（count）被下单件数（count）被下单金额（amount）被支付次数（count）被支付件数（count）被支付金额（amount）被退款次数（count）被退款件数（count）被退款金额（amount）被加入购物车次数（count）被加入购物车件数（count）被收藏次数（count）好评数（count）中评数（count）差评数（count）默认评价数（count）

启动日志

1）数据清洗2）脱敏3）维度退化4)  压缩LZO5)  列式存储parquet6)  对用户行为数据解析

1）事务型订单明细表支付表退款表评价表

商品主题1）商品个数信息    SKU个数    SPU个数2）商品销量排名3）商品收藏排名4）商品加入购物车排名4）商品退款率排名（最近30天）     =退款商品数/支付商品数5）商品差评率

DWT层

（预留）每日优惠券统计（3个指标）优惠券id购物券名称购物券类型满额数满件数活动编号减金额(amount)折扣创建时间范围类型商品id品牌id品类id最多领用次数使用（下单）次数（count）使用（支付）次数（count）

（预留）活动主题宽表（4个指标）活动id活动名称当日下单次数（count）当日支付次数（count）累计下单次数（count）累计支付次数（count）

3）拉链表用户维度表