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

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

2. 用户行为数据定义
2.1 基本格式
- 公共字段:基本所有安卓手机都包含的字段
- 业务字段:埋点上报的字段,有具体的业务类型
埋点数据一般不是用户在页面点击一条传一条,而是每隔几分钟批量上传。
2.2 客户端发送数据格式
json{
"ap":"xxxxx",
"cm": {
"mid": "",
"uid": "",
"vc": "1",
"vn": "1.0",
"l": "zh",
"sr": "",
"os": "7.1.1",
"ar": "CN",
"md": "BBB100-1",
"ba": "blackberry",
"sv": "V2.2.1",
"g": "",
"hw": "1620x1080",
"t": "1506047606608",
"nw": "WIFI",
"ln": 0,
"la": 0
},
"et": [
{
"ett": "1506047605364",
"en": "display",
"kv": {
"goodsid": "236",
"action": "1",
"extend1": "1",
"place": "2",
"category": "75"
}
}
]
}
2.3 服务器接收数据格式
其中开头的是接收数据的时间戳,可以用来分析客户端上传到接收的耗时:
json1540934156385|{
"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": {
"goodsid": "236",
"action": "1",
"extend1": "1",
"place": "2",
"category": "75"
}
},{
"ett": "1552352626835",
"en": "active_background",
"kv": {
"active_source": "1"
}
}
]
}
}
3. 用户行为数据分类
下面是各类埋点日志:

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

4.2 电商常识(SKU、SPU)
SKU=Stock Keeping Unit(库存量基本单位)。现在已经被引申为产品统一编号的简称,每种产品均对应有唯一的SKU号。
SPU(Standard Product Unit):是商品信息聚合的最小单位,是一组可复用、易检索的标准化信息集合。
例如:iPhoneX手机就是SPU。一台银色、128G内存的、支持联通网络的iPhoneX,就是SKU。
SPU表示一类商品。好处就是:可以共用商品图片,海报、销售属性等。
5.电商业务表结构
本电商数仓系统涉及到的业务数据表结构关系。这24个表以订单表、用户表、SKU商品表、活动表和优惠券表为中心,延伸出了优惠券领用表、支付流水表、活动订单表、订单详情表、订单状态表、商品评论表、编码字典表退单表、SPU商品表等,用户表提供用户的详细信息,支付流水表提供该订单的支付详情,订单详情表提供订单的商品数量等情况,商品表给订单详情表提供商品的详细信息。
电商表结构一览:

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)
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 |
| 标签 |
含义 |
| 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 |
创建时间 |
评论