电商数据梳理

1. 电商数据种类

1.1 用户行为数据

也称为埋点数据,用户在使用产品过程中,与客户端产品交互过程中产生的数据,比如页面浏览、点击、停留、评论、点赞、收藏等。用户行为数据通常存储在日志文件中。
用户行为数据举例图

1.2 业务数据

指的是各行业在处理事务过程中产生的数据。比如用户在电商网站中登录、下单、支付等过程中产生的数据就是业务数据。业务数据通常存储在MySQL、Oracle等数据库中。
业务数据举例图

2. 用户行为数据定义

2.1 基本格式

  1. 公共字段:基本所有安卓手机都包含的字段
  2. 业务字段:埋点上报的字段,有具体的业务类型
    埋点数据一般不是用户在页面点击一条传一条,而是每隔几分钟批量上传。

2.2 客户端发送数据格式

json
{
"ap":"xxxxx",//项目数据来源 app pc
"cm": {  //公共字段
		"mid": "",  // (String) 设备唯一标识
        "uid": "",  // (String) 用户标识
        "vc": "1",  // (String) versionCode,程序版本号
        "vn": "1.0",  // (String) versionName,程序版本名
        "l": "zh",  // (String) language系统语言
        "sr": "",  // (String) 渠道号,应用从哪个渠道来的。
        "os": "7.1.1",  // (String) Android系统版本
        "ar": "CN",  // (String) area区域
        "md": "BBB100-1",  // (String) model手机型号
        "ba": "blackberry",  // (String) brand手机品牌
        "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"
                }
            }
        ]
}

2.3 服务器接收数据格式

其中开头的是接收数据的时间戳,可以用来分析客户端上传到接收的耗时:

json
1540934156385|{
    "ap": "gmall", 
    "cm": {
        "uid": "1234", 
        "vc": "2", 
        "vn": "1.0", 
        "la": "EN", 
        "sr": "", 
        "os": "7.1.1", 
        "ar": "CN", 
        "md": "BBB100-1", 
        "ba": "blackberry", 
        "sv": "V2.2.1", 
        "g": "abc@gmail.com", 
        "hw": "1620x1080", 
        "t": "1506047606608", 
        "nw": "WIFI", 
        "ln": 0
    }, 
        "et": [
            {
                "ett": "1506047605364",  //客户端事件产生时间
                "en": "display",  //事件名称
                "kv": {  //事件结果,以key-value形式自行定义
                    "goodsid": "236",
                    "action": "1",
                    "extend1": "1",
                    "place": "2",
                    "category": "75"
                }
            },{
		        "ett": "1552352626835",
		        "en": "active_background",
		        "kv": {
			         "active_source": "1"
		        }
	        }
        ]
    }
}

3. 用户行为数据分类

下面是各类埋点日志:
Alt text

3.1 商品列表页

事件名称:loading

标签 含义
action 动作:开始加载=1,加载成功=2,加载失败=3
loading_time 加载时长:计算下拉开始到接口返回数据的时间,(开始加载报0,加载成功或加载失败才上报时间)
loading_way 加载类型:1-读取缓存,2-从接口拉新数据 (加载成功才上报加载类型)
extend1 扩展字段 Extend1
extend2 扩展字段 Extend2
type 加载类型:自动加载=1,用户下拽加载=2,底部加载=3(底部条触发点击底部提示条/点击返回顶部加载)
type1 加载失败码:把加载失败状态码报回来(报空为加载成功,没有失败)

3.2 商品点击(display)

事件标签:display

标签 含义
action 动作:曝光商品=1,点击商品=2,
goodsid 商品ID(服务端下发的ID)
place 顺序(第几条商品,第一条为0,第二条为1,如此类推)
extend1 曝光类型:1 - 首次曝光 2-重复曝光
category 分类ID(服务端定义的分类ID)

3.3 商品详情页(newsdetail)

事件标签:newsdetail

标签 含义
entry 页面入口来源:应用首页=1、push=2、详情页相关推荐=3
action 动作:开始加载=1,加载成功=2(pv),加载失败=3, 退出页面=4
goodsid 商品ID(服务端下发的ID)
show_style 商品样式:0、无图、1、一张大图、2、两张图、3、三张小图、4、一张小图、5、一张大图两张小图
news_staytime 页面停留时长:从商品开始加载时开始计算,到用户关闭页面所用的时间。若中途用跳转到其它页面了,则暂停计时,待回到详情页时恢复计时。或中途划出的时间超过10分钟,则本次计时作废,不上报本次数据。如未加载成功退出,则报空。
loading_time 加载时长:计算页面开始加载到接口返回数据的时间 (开始加载报0,加载成功或加载失败才上报时间)
type1 加载失败码:把加载失败状态码报回来(报空为加载成功,没有失败)
category 分类ID(服务端定义的分类ID)

3.4 广告(ad)

事件名称:ad

标签 含义
entry 入口:商品列表页=1 应用首页=2 商品详情页=3
action 动作: 广告展示=1 广告点击=2
contentType Type: 1 商品 2 营销活动
displayMills 展示时长 毫秒数
itemId 商品id
activityId 营销活动id

3.5 消息通知(notification)

事件标签:notification

标签 含义
action 动作:通知产生=1,通知弹出=2,通知点击=3,常驻通知展示(不重复上报,一天之内只报一次)=4
type 通知id:预警通知=1,天气预报(早=2,晚=3),常驻=4
ap_time 客户端弹出时间
content 备用字段

3.6 用户后台活跃(active_background)

事件标签: active_background

标签 含义
active_source 1=upgrade,2=download(下载),3=plugin_upgrade

3.7 评论(comment)

描述:评论表

提示

有些公司文档是表的形式

序号 字段名称 字段描述 字段类型 长度 允许空 缺省值
1 comment_id 评论表 int 10,0
2 userid 用户id int 10,0 √ 0
3 p_comment_id 父级评论id(为0则是一级评论,不为0则是回复) int 10,0 √
4 content 评论内容 string 1000 √
5 addtime 创建时间 string √
6 other_id 评论的相关id int 10,0 √
7 praise_count 点赞数量 int 10,0 √ 0
8 reply_count 回复数量 int 10,0 √ 0

3.8 收藏(favorites)

描述:收藏

序号 字段名称 字段描述 字段类型 长度 允许空 缺省值
1 id 主键 int 10,0
2 course_id 商品id int 10,0 √ 0
3 userid 用户ID int 10,0 √ 0
4 add_time 创建时间 string √

3.9 点赞(praise)

描述:所有的点赞表

序号 字段名称 字段描述 字段类型 长度 允许空 缺省值
1 id 主键id int 10,0
2 userid 用户id int 10,0 √
3 target_id 点赞的对象id int 10,0 √
4 type 点赞类型
1问答点赞
2问答评论点赞
3 文章点赞数
4 评论点赞
int 10,0 √
5 add_time 添加时间 string √

3.10 错误日志

标签 含义
errorBrief 错误摘要
errorDetail 错误详情

3.11 启动日志数据

标签 含义
entry 入口: push=1,widget=2,icon=3,notification=4, lockscreen_widget =5
open_ad_type 开屏广告类型: 开屏原生广告=1, 开屏插屏广告=2
action 状态:成功=1 失败=2
loading_time 加载时长:计算下拉开始到接口返回数据的时间,(开始加载报0,加载成功或加载失败才上报时间)
detail 失败码(没有则上报空)
extend1 失败的message(没有则上报空)
en 日志类型start
json
{
    "action":"1",
    "ar":"MX",
    "ba":"HTC",
    "detail":"",
    "en":"start",
    "entry":"2",
    "extend1":"",
    "g":"43R2SEQX@gmail.com",
    "hw":"640*960",
    "l":"en",
    "la":"20.4",
    "ln":"-99.3",
    "loading_time":"2",
    "md":"HTC-2",
    "mid":"995",
    "nw":"4G",
    "open_ad_type":"2",
    "os":"8.1.2",
    "sr":"B",
    "sv":"V2.0.6",
    "t":"1561472502444",
    "uid":"995",
    "vc":"10",
    "vn":"1.3.4"
}

4. 电商业务梳理

4.1 电商业务流程

电商的业务流程可以以一个普通用户的浏览足迹为例进行说明,用户点开电商首页开始浏览,可能会通过分类查询也可能通过全文搜索寻找自己中意的商品,这些商品无疑都是存储在后台的管理系统中的。
当用户寻找到自己中意的商品,可能会想要购买,将商品添加到购物车后发现需要登录,登录后对商品进行结算,这时候购物车的管理和商品订单信息的生成都会对业务数据库产生影响,会生成相应的订单数据和支付数据。
订单正式生成之后,还会对订单进行跟踪处理,直到订单全部完成。
电商的主要业务流程包括用户前台浏览商品时的商品详情的管理,用户商品加入购物车进行支付时用户个人中心&支付服务的管理,用户支付完成后订单后台服务的管理,这些流程涉及到了十几个甚至几十个业务数据表,甚至更多。
Alt text

4.2 电商常识(SKU、SPU)

SKU=Stock Keeping Unit(库存量基本单位)。现在已经被引申为产品统一编号的简称,每种产品均对应有唯一的SKU号。
SPU(Standard Product Unit):是商品信息聚合的最小单位,是一组可复用、易检索的标准化信息集合。
例如:iPhoneX手机就是SPU。一台银色、128G内存的、支持联通网络的iPhoneX,就是SKU。
Alt text SPU表示一类商品。好处就是:可以共用商品图片,海报、销售属性等。

5.电商业务表结构

本电商数仓系统涉及到的业务数据表结构关系。这24个表以订单表、用户表、SKU商品表、活动表和优惠券表为中心,延伸出了优惠券领用表、支付流水表、活动订单表、订单详情表、订单状态表、商品评论表、编码字典表退单表、SPU商品表等,用户表提供用户的详细信息,支付流水表提供该订单的支付详情,订单详情表提供订单的商品数量等情况,商品表给订单详情表提供商品的详细信息。
电商表结构一览:
Alt text

5.1 订单表(order_info)

标签 含义
id 订单编号
consignee 收货人
consignee_tel 收件人电话
final_total_amount 总金额
order_status 订单状态
user_id 用户id
delivery_address 送货地址
order_comment 订单备注
out_trade_no 订单交易编号(第三方支付用)
trade_body 订单描述(第三方支付用)
create_time 创建时间
operate_time 操作时间
expire_time 失效时间
tracking_no 物流单编号
parent_order_id 父订单编号
img_url 图片路径
province_id 地区
benefit_reduce_amount 优惠金额
original_total_amount 原价金额
feight_fee 运费金额

5.2 订单详情表(order_detail)

标签 含义
id 订单编号
order_id 订单号
sku_id 商品id
sku_name sku名称(冗余)
img_url 图片名称(冗余)
order_price 商品价格(下单时sku价格)
sku_num 商品数量
create_time 创建时间

5.3 商品表(sku_info)

标签 含义
id skuId
spu_id spuid
price 价格
sku_name 商品名称
sku_desc 商品描述
weight 重量
tm_id 品牌id
category3_id 品类id
sku_default_img 默认显示图片(冗余)
create_time 创建时间

5.4 用户表(user_info)

标签 含义
id 用户id
login_name 用户名称
nick_name 用户昵称
passwd 用户密码
name 姓名
phone_num 手机号
email 邮箱
head_img 头像
user_level 用户级别
birthday 生日
gender 性别M男,F女
create_time 创建时间
operate_time 操作时间

5.5 商品一级分类表(base_category1)

标签 含义
id id
name 名称

5.6 商品二级分类表(base_category2)

标签 含义
id id
name 名称
category1_id 一级品类id

5.7 商品三级分类表(base_category3)

标签 含义
id id
name 名称
Category2_id 二级品类id

5.8 支付流水表(payment_info)

标签 含义
id 编号
out_trade_no 对外业务编号
order_id 订单编号
user_id 用户编号
alipay_trade_no 支付宝交易流水编号
total_amount 支付金额
subject 交易内容
payment_type 支付类型
payment_time 支付时间

5.9 省份表(base_province)

标签 含义
id id
name 省份名称
region_id 地区ID
area_code 地区编码
iso_code 国际编码

5.10 地区表(base_region)

标签 含义
id 大区id
region_name 大区名称

5.11 品牌表(base_trademark)

标签 含义
tm_id 品牌id
tm_name 品牌名称

5.12 订单状态表(order_status_log)

标签 含义
id 编号
order_id 订单编号
order_status 订单状态
operate_time 操作时间

5.13 SPU商品表(spu_info)

标签 含义
id 商品id
spu_name spu商品名称
description 商品描述(后台简述)
category3_id 三级分类id
tm_id 品牌id

5.14 商品评论表(comment_info)

标签 含义
id 编号
user_id 用户名称
sku_id 商品id
spu_id spu_id
order_id 订单编号
appraise 评价 1 好评 2 中评 3 差评
comment_txt 评价内容
create_time 创建时间

5.15 退单表(order_refund_info)

标签 含义
id 编号
order_id 订单编号
sku_id skuid
refund_type 退款类型
refund_amount 退款金额
refund_reason_type 原因类型
refund_reason_txt 原因内容
create_time 创建时间

5.16 加购表🛒(cart_info)

标签 含义
id 编号
user_id 用户id
sku_id SKU商品
cart_price 放入购物车时价格
sku_num 数量
img_url 图片文件
sku_name sku名称 (冗余)
create_time 创建时间
operate_time 修改时间
is_ordered 是否已经下单
order_time 下单时间

5.17 商品收藏表(favor_info)

标签 含义
id 编号
user_id 用户名称
sku_id 商品id
spu_id spu_id
is_cancel 是否已取消 0 正常 1 已取消
create_time 创建时间
cancel_time 修改时间

5.18 优惠券领用表(coupon_use)

标签 含义
id 编号
coupon_id 购物券ID
user_id 用户ID
order_id 订单ID
coupon_status 购物券状态
get_time 领券时间
using_time 使用时间
used_time 支付时间
expire_time 过期时间

5.19 优惠券表(coupon_info)

标签 含义
id 购物券编号
coupon_name 购物券名称
coupon_type 购物券类型 1 现金券 2 折扣券 3 满减券 4 满件打折券
condition_amount 满额数
condition_num 满件数
activity_id 活动编号
benefit_amount 减金额
benefit_discount 折扣
create_time 创建时间
range_type 范围类型 1、商品 2、品类 3、品牌
spu_id 商品id
tm_id 品牌id
category3_id 品类id
limit_num 最多领用次数
operate_time 修改时间
expire_time 过期时间

5.20 活动表(activity_info)

标签 含义
id 活动id
activity_name 活动名称
activity_type 活动类型
activity_desc 活动描述
start_time 开始时间
end_time 结束时间
create_time 创建时间

5.12 活动订单关联表(activity_order)

标签 含义
id 编号
activity_id 活动id
order_id 订单编号
create_time 发生日期

5.22 优惠规则表(activity_rule)

标签 含义
id 编号
activity_id 活动id
condition_amount 满减金额
condition_num 满减件数
benefit_amount 优惠金额
benefit_discount 优惠折扣
benefit_level 优惠级别

5.23 编码字典表(base_dic)

标签 含义
dic_code 编号
dic_name 编码名称
parent_code 父编号
create_time 创建日期
operate_time 修改日期

5.24 活动参与商品表(activity_sku)

标签 含义
id 编号
activity_id 活动id
sku_id 满减金额
create_time 创建时间

评论