电商项目_ads层建设

四、ADS层建设—-应用数据层表的设计与实现

电商类型常见计算指标

流量相关

网站流量统计分析是指获得网站访问量基础数据的悄况下，对有关数据进行统计、分析，以了解网站当前的访问效果和访问用户行为，以发现当前网络普销活动中存在的间題．监控异常情况，为一进修正或重新制定网络营销策略提供依据，支撑活动安排，维持流量的稳定和增长。

# 独立访问数： UV即Unique Visitor，独立访客数，指一天内访问某站点的人数。1天内同一访客的多次访问只记录为一个访客。通过IP和cookie是判断UV值的两种方式。
用Cookie分析UV值：当客户端第一次访问某个网站服务器的时候，网站服务器会给这个客户端的电脑发出一个Cookie，通常放在这个客户端电脑的C盘当中。在这个Cookie中会分配一个独一无二的编号，这其中会记录一些访问服务器的信息，如访问时间，访问了哪些页面等等。当你下次再访问这个服务器的时候，服务器就可以直接从你的电脑中找到上一次放进去的Cookie文件，并且对其进行一些更新，但那个独一无二的编号是不会变的。
使用IP即独立IP数，指一天内使用不同IP地址的用户访问网站数量，同一IP无论访问了几个页面，独立的IP数均为1.但是假如说两台机器访问而使用的是同一个IP，那么只能算是一个IP的访问。
IP和UV之间的数据不会有太大的差异，通常UV量和比IP量高出一点，每个UV相对于每个IP更准确地对应一个实际的浏览者。

# 页面访问数：PV即Page View，网站浏览量，指页面浏览的次数，用以衡量网站用户访问的网页数量。用户每次打开一个页面便记录1次PV，多次打开同一页面则浏览量累计。
一般来说，PV与来访者的数量成正比，但是PV并不直接决定页面的真实来访者数量，如同一个来访者通过不断的刷新页面，也可以制造出非常高的PV。具体的说，PV值就是所有访问者在24小时（0点到24点）内看了某个网站多少个页面或某个网页多少次。PV是指页面刷新的次数，每一次页面刷新，就算做一次PV流量。
度量方法就是从浏览器发出一个对网络服务器的请求（Request），网络服务器接到这个请求后，会将该请求对应的一个网页（Page）发送给浏览器，从而产生了一个PV。那么在这里只要是这个请求发送给了浏览器，无论这个页面是否完全打开（下载完成），那么都是应当计为1个PV。

# 跳出率：
定义：只浏览了一个页面便离开了网站的访问次数占总的访问次数的百分比，即只浏览了一个页面的访问次数 / 全部的访问次数汇总。
跳出率是非常重要的访客黏性指标，它显示了访客对网站的兴趣程度：跳出率越低说明流量质量越好，访客对网站的内容越感兴趣，这些访客越可能是网站的有效用户、忠实用户。该指标也可以衡量网络营销的效果，指出有多少访客被网络营销吸引到宣传产品页或网站上之后，又流失掉了，可以说就是煮熟的鸭子飞了。比如，网站在某媒体上打广告推广，分析从这个推广来源进入的访客指标，其跳出率可以反映出选择这个媒体是否合适，广告语的撰写是否优秀，以及网站入口页的设计是否用户体验良好。

# 页面访问时长：
平均每次访问在网站上的停留时长，即平均访问时长等于总访问时长与访问次数的比值。
平均访问时间越长则说明访客停留在网页上的时间越长：如果用户对网站的内容不感兴趣，则会较快关闭网页，那么平均访问时长就短;如果用户对网站的内容很感兴趣，在网站停留了很长时间，平均访问时长就长。

转化率相关

了解各环节转化情况，分析异常或不合理情况并进行调整以提升各环节转化率。

# 店铺转化率，所有到达店铺并产生购买行为的人数和所有到达你的店铺的人数的比率。
计算方法为：转化率=（产生购买行为的客户人数 / 所有到达店铺的访客人数）× 100%。

# 访问到下单转化率，所有访问商品并购买的人数和所有访问该商品人数的比率。

# 下单到支付转化率，支付订单的人数和所有下单人数的比率。

# 广告投放订单转化率，广告投放产生的订单数和广告投放总数据量的比率

复购相关

# 通过对复购的监控分析，发现问题、细化原因，为运营策略调整提供支持，辅助达到稳定、提高复购率的效果。

重复购买率指消费者对某店铺、品牌、产品或者服务的重复购买比例，重复购买率越高，反映出消费者对店铺、品牌、产品、服务的忠诚度越高，客户黏性越强，反之则越差。
（1）以客户为对象的重复购买率在一定的时间范围内，重复购买率=重复购买的客户数/购买客户数。比如店铺今年内一共有10000个客户在店铺消费，其中2000个客户消费次数（今年内）大于2次（交易按天合并处理）。
公式
今年重复购买率=2000/10000=20%
同样的公式，如果去掉了时间范围的限制就变成了历史重复购买率，分子就变成了回头客：
公式
重复购买率=回头客/所有客户
比如，店铺的回头客有“黄仙”、“李毅”、“张永凤”3人，而总客户数有6人，所以该店铺以客户为对象的重复购买率为50%。
（2）以订单为对象的重复购买率在一定的时间范围内，重复购买率=重复购买的订单数/购买订单数。比如店铺今年有10000个订单（交易不合并），其中2000个客户购买了第2笔订单，这2000人中的1000人又购买了第3笔订单。
公式
今年重复购买率=（购买2次的订单+购买3次的订单+购买N次的订单）/所有订单=（1000+2000）/10000=30%

流失相关

# 监督流失情况、分析流失用户结构从而了解公司的流失状况，尽量进行调整。
顾客流失率又称客户流失率，是顾客流失的定量表述，是判断顾客流失的主要指标，直接反映了企业经营与管理的现状。
顾客流失率有绝对顾客流失率和相对顾客流失率之分，因而顾客流失率有两种计算方法：
1、绝对顾客流失率：(流失的顾客数量/全部顾客数量)×100%
2、相对顾客流失率：[(流失的顾客数量/全部顾客数量)×流失顾客的相对购买额]×100%
如果一家银行的顾客数量从500减少到475，那么它流失的顾客数量为 25，绝对顾客流失率即为25/500*100%=5%。

同比和环比

# 同比简介
　　同比一般情景下是今年第n月与去年第n月比。
　　本期2月比去年2月，本期6月比去年6月等。
　　其计算公式为：同比发展速度=本期发展水平去年同期水平×100%;同比增长速度=(本期发展水平-去年同期水平)去年发展水平×100%。
　　在实际工作中，经常使用这个指标，如某年、某季、某月与上年同期比较计算的发展速度，就是同比发展速度。

# 环比简介
　　环比，表示连续2个统计周期(比如连续两月)内的量的变化比。
　　环比增长率=(本期数-上期数)/上期数×100%。反映本期比上期增长了多少;环比发展速度，一般是指报告期水平与前一时期水平之比，证明现象逐期的发展速度。

流量类指标：

ADS层—平台统计表

ods_nshop.ods_02_customer , dws_nshop.dws_nshop_ulog_view

人均页面访问数：
	平均访问页数是用户访问网站的平均浏览页数。平均访问页数=浏览量/访问次数。平均访问页数很少，说明访客进入你的网站后访问少数几个页面就离开了.

create external table if not exists ads_nshop.ads_nshop_platform_flow_stat(
  customer_gender TINYINT  COMMENT '性别：1男 0女',
  age_range string  COMMENT '年龄段',
  customer_natives string  COMMENT '所在地区',
  visit_count int comment '访问量',
  visit_avg_counts int comment '人均页面访问数'
) partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as textfile
location '/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_platform_flow_stat/'

ADS层—平台流量统计P/UV表

dws_nshop.dws_nshop_ulog_view

名字解释：

独立访客数（UV）：
	指来到网站或页面的用户总数，这个用户是独立的，同一用户不同时段访问网站只算作一个独立访客，不会重复累计，通常以PC端的Cookie数量作为统计依据，APP端的设备号作为统计依据。

页面访客数：
	页面访问次数，即当前页面的被访问的次数，即浏览量PV；举例：首页，访问次数，1000次。

人均页面访问数：
	平均访问页数是用户访问网站的平均浏览页数。平均访问页数=浏览量/访问次数。平均访问页数很少，说明访客进入你的网站后访问少数几个页面就离开了.

create external table if not exists ads_nshop.ads_nshop_flowpu_stat(
  uv bigint comment '独立访客数',
  pv bigint comment '页面访客数',
  pv_avg double comment '人均页面访问数'
) partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as textfile
location '/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_flow/'

ADS层—平台搜索热词统计表

ods_nshop.ods_02_customer , dwd_nshop.dwd_nshop_actlog_pdtsearch

create external table if not exists ads_nshop.ads_nshop_search_keys(
  search_keys string comment '搜索内容',
  gender string comment '性别',
  age_range string comment '年龄段',
  os string comment '手机系统',
  manufacturer string comment '手机制造商',
  area_code string comment '地区编码',
  search_users int comment '此搜索内容用户数',
  search_records int comment '此搜索内容查询次数',
  search_orders string comment '查询排序',
  search_targets int comment '查询目标数量'
) partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as textfile
location '/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_search_keys/'

用户类指标：

ADS层—用户类启动情况表

dws_nshop_ulog_launch

--以设备角度出发
create external table if not exists ads_nshop.ads_nshop_actlog_launch_gsets(
  os string comment '手机系统',
  manufacturer string comment '手机制造商',
  carrier string comment '电信运营商',
  network_type string comment '网络类型',
  area_code string comment '地区编码',
  user_count int comment '用户数',
  launch_count int comment '启动次数'
) partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as textfile
location '/shujia/bigdata17/data/nshop/ads/actlog/ads_nshop_actlog_launch_gsets/'

-- 以用户角度出发
create external table if not exists ads_nshop.ads_nshop_actlog_launch_user(
  os string comment '手机系统',
  manufacturer string comment '手机制造商',
  carrier string comment '电信运营商',
  network_type string comment '网络类型',
  area_code string comment '地区编码',
  user_count int comment '用户数',
  launch_count int comment '启动次数'
) partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as textfile
location '/shujia/bigdata17/data/nshop/ads/actlog/ads_nshop_actlog_launch_user/'

ADS层—用户类指标统计表

dws_nshop_ulog_launch

启动算做活跃

ods_02_customer

create external table if not exists ads_nshop.ads_nshop_customer_stat(
  customer_gender TINYINT COMMENT '性别：1男 0女',
  age_range string COMMENT '年龄段',
  customer_natives string  COMMENT '所在地区',
  total_counts int comment '总用户数',
  add_counts int comment '新增用户数',
  active_counts int comment '活跃用户数'
) partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as textfile
location '/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_customer_stat/'

ADS层—总体运营指标统计表

ods_02_customer, dwd_nshop_orders_details, dim_pub_product, dws_nshop_ulog_view

create external table if not exists ads_nshop.ads_nshop_oper_stat(
  customer_gender TINYINT  COMMENT '性别：1男 0女',
  age_range string  COMMENT '年龄段',
  customer_natives string   COMMENT '所在地区',
  product_type int comment '商品类别',
  order_counts int comment '订单数',
  order_rate double comment '下单率',
  order_amounts int comment '销售总金额',
  order_discounts int comment '优惠总金额',
  shipping_amounts int comment '运费总金额',
  per_customer_transaction int comment '客单价'
) partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as textfile
location '/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_oper_stat/'

风控类指标：

ADS层—风控类指标统计表

dwd_nshop_orders_details, ods_02_customer, dim_pub_product

create external table if not exists ads_nshop.ads_nshop_risk_mgt(
  customer_gender TINYINT  COMMENT '性别：1男 0女',
  age_range string  COMMENT '年龄段',
  customer_natives string  COMMENT '所在地区',
  product_type int comment '商品类别',
  start_complaint_counts int comment '发起投诉数',
  complaint_rate float comment '投诉率'
) partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as textfile
location '/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_risk_mgt/'

交易支付类指标：

ADS层—支付类指标统计表

dwd_nshop_orders_details, ods_02_customer, ods_02_orders_pay_records

create external table if not exists ads_nshop.ads_nshop_pay_stat_gsets(
  customer_gender TINYINT COMMENT '性别：1男 0女',
  age_range string COMMENT '年龄段',
  customer_natives string COMMENT '所在地区',
  pay_type string  COMMENT '支付类型',
  pay_nettype string COMMENT '支付网络方式',
  pay_amounts int comment '支付金额',
  pay_success int comment '支付成功数',
  pay_fail int comment '支付失败数',
  pay_order_counts int comment '支付订单数',
  pay_user_counts int comment '支付用户数',
  pay_product_counts int comment '支付商品数',
  order_pay_amount_rate float comment '下单-支付金额转换率',
  order_pay_user_rate float comment '下单-支付买家数转换率'
) partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as textfile
location '/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_pay_stat_gsets/'

ADS层—支付统计TopN表

ods_02_customer, ods_02_orders_pay_records

create external table if not exists ads_nshop.ads_nshop_pay_stat_topn(
  pay_type string  COMMENT '支付类型',
  customer_area_code string COMMENT '所在地区',
  pay_count int COMMENT '支付数量',
  pay_amounts int comment '支付金额'
) partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as textfile
location '/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_pay_stat_topn/'

ADS层—交易类指标表(只要不是退货都算交易成功）

dwd_nshop_orders_details

ods_02_customer

dim_pub_product

dim_pub_supplier

create external table if not exists ads_nshop.ads_nshop_quanju_stat(
  customer_gender TINYINT COMMENT '性别：1男 0女',
  age_range string COMMENT '年龄段',
  customer_area_code string COMMENT '所在地区',
  category_type string COMMENT '商品类别',
  supplier_type string COMMENT '店铺类别',
  busi_succ_orders int comment '交易成功订单数',
  busi_succ_amounts int comment '交易成功金额',
  busi_succ_users int comment '交易成功买家数',
  busi_succ_products int comment '交易成功商品数',
  busi_fail_orders int comment '交易失败订单数',
  busi_fail_amounts int comment '交易失败金额'
) partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as textfile
location '/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_quanju_stat/'

ADS层—交易TopN统计表(根据上面的取）

create external table if not exists ads_nshop.ads_nshop_quanju_stat_topn(
  customer_natives string COMMENT '所在地区',
  category_type string COMMENT '商品类别',
  supplier_type string COMMENT '店铺类别',
  busi_succ_orders int comment '交易成功订单数',
  busi_succ_amounts int comment '交易成功金额',
  busi_succ_users int comment '交易成功买家数',
  busi_succ_products int comment '交易成功商品数',
  busi_fail_orders int comment '交易失败订单数',
  busi_fail_amounts int comment '交易失败金额'
) partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as textfile
location '/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_quanju_stat_topn/' 

营销类指标：

ADS层—广告投放类指标统计

dwd_nshop_releasedatas, ods_02_customer

create external table if not exists ads_nshop.ads_nshop_release_stat(
device_type string comment '设备类型',
os string comment '手机系统',
customer_gender TINYINT comment '性别：1男 0女',
age_range string comment '年龄段',
customer_natives string comment '所在地区',
release_sources string comment '投放渠道',
release_category string comment '投放浏览产品分类',
visit_total_customers int comment '总访客数',
visit_total_counts int comment '总访问次数'
) partitioned by (bdp_day string)
row format delimited fields terminated by ','
stored as textfile
location '/shujia/bigdata17/data/nshop/ads/operation/ads_nshop_release_stat/'

ADS层Hql语句实现

平台统计表 ads_nshop.ads_nshop_platform_flow_stat

ods_nshop.ods_02_customer , dws_nshop.dws_nshop_ulog_view

insert overwrite table ads_nshop.ads_nshop_platform_flow_stat partition(bdp_day='20220630')
select 
a.customer_gender,
a.customer_age_range,
a.customer_natives,
sum(b.view_count),
sum(b.view_count)/count(distinct a.customer_device_num)
from ods_nshop.ods_02_customer a
join dws_nshop.dws_nshop_ulog_view b
on a.customer_device_num=b.device_num
where bdp_day='20220630'
group by 
customer_gender,
customer_age_range,
customer_natives;

平台浏览统计P/UV表

insert overwrite table ads_nshop.ads_nshop_flowpu_stat partition(bdp_day='20220630')
select 
count(device_num),
sum(view_count),
sum(view_count)/count(distinct device_num)
from dws_nshop.dws_nshop_ulog_view 
where bdp_day='20220630';

平台搜索热词统计表

select * from ads_nshop.ads_nshop_search_keys where bdp_day=’20220630′ limit 10;

ods_nshop.ods_02_customer , dwd_nshop.dwd_nshop_actlog_pdtsearch

gender string comment ‘性别’,
age_range string comment ‘年龄段’,
os string comment ‘手机系统’,
manufacturer string comment ‘手机制造商’,
area_code string comment ‘地区编码’,

search_keys string comment ‘搜索内容’,

search_order ‘排序方式’

insert overwrite table ads_nshop.ads_nshop_search_keys partition(bdp_day='20220630')
select 
b.target_keys,
a.customer_gender,
a.customer_age_range,
b.os,
b.manufacturer,
b.area_code,
count(distinct b.device_num),
count(b.device_num),
b.target_order,
count(target_id)
from ods_nshop.ods_02_customer a
join dwd_nshop.dwd_nshop_actlog_pdtsearch b
on a.customer_device_num=b.device_num
where b.bdp_day='20220630'
group by
a.customer_gender,
a.customer_age_range,
b.os,
b.manufacturer,
b.area_code,
b.target_order,
b.target_keys;

用户启动情况表

insert overwrite table ads_nshop.ads_nshop_actlog_launch_gsets partition(bdp_day='20220630')
select 
os,
manufacturer,
carrier,
network_type,
area_code,
count(device_num),
sum(launch_count)
from dws_nshop.dws_nshop_ulog_launch
where bdp_day='20220630'
group by 
os,
manufacturer,
carrier,
network_type,
area_code;

— 以用户角度出发

insert overwrite table ads_nshop.ads_nshop_actlog_launch_user partition(bdp_day='20220630')
select 
os,
manufacturer,
carrier,
network_type,
area_code,
count(distinct user_id),
sum(launch_count)
from dws_nshop.dws_nshop_ulog_launch
where bdp_day='20220630'
group by 
os,
manufacturer,
carrier,
network_type,
area_code;

用户类指标统计表

insert overwrite table ads_nshop.ads_nshop_customer_stat partition(bdp_day='20220630')
select
customer_gender,
customer_age_range,
customer_natives,
count(distinct customer_id),
count(case when from_unixtime(cast(a.customer_ctime/1000 as int),'yyyyMMdd')='20191102' then 1 end),
count(b.device_num)
from ods_nshop.ods_02_customer a
join dws_nshop.dws_nshop_ulog_launch b
on a.customer_device_num=b.device_num
where bdp_day='20220630'
group by
customer_gender,
customer_age_range,
customer_natives;

总体运营指标统计表

insert overwrite table ads_nshop.ads_nshop_oper_stat partition(bdp_day='20220630')
select 
a.customer_gender,
a.customer_age_range,
a.customer_natives,
c.category_code,
count(distinct b.order_id),
sum(d.view_count)/count(distinct b.order_id),
sum(b.payment_money),
sum(b.district_money),
sum(b.shipping_money),
sum(b.payment_money)/count(distinct a.customer_id)
from ods_nshop.ods_02_customer a
join dwd_nshop.dwd_nshop_orders_details b
on a.customer_id=b.customer_id
join ods_nshop.dim_pub_product c
on b.supplier_code=c.supplier_code
join dws_nshop.dws_nshop_ulog_view d
on b.customer_id=d.user_id
where d.bdp_day='20220630'
group by
a.customer_gender,
a.customer_age_range,
a.customer_natives,
c.category_code

风控类指标统计表

insert overwrite table ads_nshop.ads_nshop_risk_mgt partition(bdp_day='20220630')
select 
b.customer_gender,
b.customer_age_range,
b.customer_natives,
c.category_code,
count(case when a.order_status='6' then a.order_id end),
count(case when a.order_status='6' then a.order_id end)/count(distinct order_id)
from dwd_nshop.dwd_nshop_orders_details a
join ods_nshop.ods_02_customer b
on a.customer_id=b.customer_id
join ods_nshop.dim_pub_product c
on a.product_code=c.product_code
where a.bdp_day='20220630'
group by
b.customer_gender,
b.customer_age_range,
b.customer_natives,
c.category_code

支付类指标统计表

insert overwrite table ads_nshop.ads_nshop_pay_stat_gsets partition(bdp_day='20220630')
select
b.customer_gender,
b.customer_age_range,
b.customer_natives,
a.pay_type,
a.pay_nettype,
sum(a.payment_money),
count(case when c.pay_status='1' then 1 end),
count(case when c.pay_status='0' then 1 end),
count(c.pay_id),
count(c.customer_id),
sum(a.product_cnt),
sum(c.pay_amount)/sum(case when c.pay_status='1' then c.pay_amount end),
count(a.order_id)/count(case when c.pay_status='1' then c.pay_id end)
from dwd_nshop.dwd_nshop_orders_details a
join ods_nshop.ods_02_customer b
on a.customer_id=b.customer_id
join ods_nshop.ods_02_orders_pay_records c
on a.order_id=c.order_id
where a.bdp_day='20220630'
group by
b.customer_gender,
b.customer_age_range,
b.customer_natives,
a.pay_type,
a.pay_nettype

统计前100条

insert overwrite table ads_nshop.ads_nshop_pay_stat_topn partition(bdp_day='20220630')
select 
b.pay_type,
a.customer_natives,
count(b.pay_id),
sum(b.pay_amount) as pay_sum_price
from ods_nshop.ods_02_customer a
join ods_nshop.ods_02_orders_pay_records b
on a.customer_id=b.customer_id
group by
a.customer_natives,
b.pay_type order by pay_sum_price desc limit 100;

交易类指标表

insert overwrite table ads_nshop.ads_nshop_quanju_stat partition(bdp_day='20220630')
select 
b.customer_gender,
b.customer_age_range,
b.customer_natives,
c.category_code,
d.supplier_type,
count(case when a.order_status!='7' then a.order_id end),
sum(case when a.order_status!='7' then a.payment_money end),
count(case when a.order_status!='7' then a.customer_id end),
sum(case when a.order_status!='7' then product_cnt end),
count(case when a.order_status='7' then a.order_id end),
sum(case when a.order_status='7' then a.payment_money end)
from dwd_nshop.dwd_nshop_orders_details a
join ods_nshop.ods_02_customer b
on a.customer_id=b.customer_id
join ods_nshop.dim_pub_product c
on a.product_code=c.product_code
join ods_nshop.dim_pub_supplier d
on c.supplier_code=d.supplier_code
where a.bdp_day='20220630'
group by
b.customer_gender,
b.customer_age_range,
b.customer_natives,
c.category_code,
d.supplier_type

广告投放类指标统计

insert overwrite table ads_nshop.ads_nshop_release_stat partition(bdp_day='20220630')
select 
a.device_type,
a.os,
b.customer_gender,
b.customer_age_range,
b.customer_natives,
a.release_sources,
a.release_category,
count(distinct a.device_num),
count(a.device_num)
from dwd_nshop.dwd_nshop_releasedatas a
join ods_nshop.ods_02_customer b
on a.customer_id=b.customer_id
where bdp_day='20220630'
group by
a.device_type,
a.os,
b.customer_gender,
b.customer_age_range,
b.customer_natives,
a.release_sources,
a.release_category