离线指标分析

一、DWS层

主要分为5个主题
设备 用户 商品 活动 地区
在这里插入图片描述

二、ADS层

我主要负责 设备 商品 活动(营销)主题的指标

1、设备主题

在这里插入图片描述
① 活跃设备数,包括日活、周活、月活

– 1. 什么是活跃设备
a、打开应用的用户即为活跃用户,不考虑用户的使用情况。
b、每天一台设备打开多次会被计为一个活跃用户
– 2. 需求:
‘日活’:当日活跃的设备数
‘周活’:当周活跃的设备数,在这一周内,多次活跃也计算为1次
‘月活’:当月活跃的设备数,在这一月内,多次活跃也计算为1次

思路:从dws层设备主题宽表获取数据
求日活:对当天设备数求count,where login_date_last=‘2020-06-14’
求周活:使用next_day()函数,本周的下周一减7就是这周星期一,下周一减1就是这周的星期天
where login_date_last>=date_add(next_day(‘2020-06-14’,‘MO’),-7)
and login_date_last<= date_add(next_day(‘2020-06-14’,‘MO’),-1)
‘月活’ 使用date_format(‘2020-06-14’,‘yyyy-MM’) 求出本月日期,最后登录日期等于本月日期的都要求count
where date_format(login_date_last,‘yyyy-MM’)=date_format(‘2020-06-14’,‘yyyy-MM’)

② 留存率,数据来源于uv表
– 1. 什么是留存率?
a、某段时间内的新增用户,经过一段时间后,仍然使用应用的被认作是留存用户;这部分用户占当时新增用户的比例即是留存率。
b、例如,5月份新增用户200,这200人启动情况:
6月份:启动人数为100人,5月份新增用户一个月后的留存率是50%
7月份:启动人数为80人,5月份新增用户二个月后的留存率是40%
8月份:启动人数为100人,5月份新增用户三个月后的留存率是50%
c、留存用户一般是统计留存率,同时必须有两个参数:哪个月份的几月的留存率
– 2. 本案例需要统计的指标是:
计算每天的1、2、3日留存率
– 3. 实现方式
第一步:统计当天所有的活跃用户
第二步:统计昨天的1日留存率,求出昨天的新用户但是今天上线的用户/昨天的新用户
第三步:统计前天的2日留存率,求出前天的新用户但是今天上线的用户/前天的新用户
所以需要统计的数量有:
1. 昨天的新用户但是今天上线的用户
2. 昨天的新用户
3. 前天的新用户但是今天上线的用户
4. 前天的新用户

③ 沉默用户,数据来源于uv表
– 1. 什么是沉默用户?
只在安装当天启动过,且启动时间是在7天前
– 2. 实现过程

  1. 统计首次活跃时间 = 最后末次活跃时间,且最后活跃时间在7天前的用户
    where login_date_first=login_date_last
    and login_date_last < date_add(‘2020-06-25’,-7);

④ 本周回流用户数,数据来源于用户详情表dws_uv_detail_daycount
– 1. 什么是本周回流用户?
上周未活跃,本周活跃的设备,且不是本周新增设备,即
本周回流=本周活跃-本周新增-上周活跃
– 2. 实现步骤:
第一步:获取本周活跃的用户-本周新增的用户
第二步:获取上周的活跃的用户
第三步:第一步的值 - 本周活跃老用户数

⑤ 流失用户
– 1. 什么是流失用户
最近7天未活跃的设备
– 2. 实现步骤
第一步:获取最近活跃时间小于7天
最后登录的日期小于当天减去7
where login_date_last < date_add(‘2020-06-25’,-7)

⑥ 最近连续三周活跃用户数
– 1. 实现步骤
第一步: 从dws层获取前一周、前两周以及当前周的所有活跃的用户
第二步: 然后进行内连接,能连接上的,则说明这连续的3周都活跃了,最后按照用户进行分组去重后求count。

本周:这周的下周一减7
where dt >= date_add(next_day(‘2020-06-25’,‘mo’),-7)
上一周:
where dt >= date_add(next_day(‘2020-06-25’,‘mo’),-14)
and dt < date_add(next_day(‘2020-06-25’,‘mo’),-7)
上两周:
where dt >= date_add(next_day(‘2020-06-25’,‘mo’),-21)
and dt < date_add(next_day(‘2020-06-25’,‘mo’),-14)

2、商品主题

在这里插入图片描述
① 商品个数信息
– 1. 需求分析:
sku_num string COMMENT ‘sku个数’,
统计到目前为止的sku数量
spu_num string COMMENT ‘spu个数’
统计到目前为止的spu数量

从sku主题表中分别求sku个数和spu个数,count(*)

② 商品销量排名
– 1. 商品销量排名:

  1. 是按照什么规则进行排名?本需求是按照当天的产品的支付金额的大小进行排名

数据来源于用户行为表,按支付金额排序,取前10
……
from dws_sku_action_daycount
where dt = ‘2020-06-25’
order by payment_amount desc
limit 10

③ 商品收藏排名
数据来源于用户行为表,按当天收藏总数排序,取前10

④ 商品加入购物车排名
数据来源于用户行为表,按当天加购总数排序,取前10

⑤ 商品退款率排名(近30天)
近30天退款 / 近30天付款 x 100%,再按退款率排序
refund_last_30d_count/payment_last_30d_count*100 refund_ratio
from dwt_sku_topic
order by refund_ratio desc
limit 10;

⑥ 商品差评率
当天差评个数 / 当天好评数 + 中评数 + 差评数 + 默认数
appraise_bad_count/(appraise_good_count+appraise_mid_count+appraise_bad_count+appraise_default_count) appraise_bad_ratio
from
dws_sku_action_daycount
where
dt=‘2020-06-25’
order by appraise_bad_ratio desc
limit 10;

3、营销主题

在这里插入图片描述
① 下单数目统计
对当天用户行为表中的下单笔数、下单金额、下单用户数进行求sum
insert into ads_order_daycount
select
‘2020-06-25’ dt ,
sum(order_count) order_count, --单日下单笔数
sum(order_amount ) order_amount ,–单日下单金额
sum(if(order_count > 0 , 1 , 0)) --单日下单用户数
from dws_user_action_daycount
where dt = ‘2020-06-25’

② 支付信息统计
数据来源:用户行为表、商品表、订单表
‘2020-06-25’ dt , --统计日期
sum(payment_count) order_count, --单日支付笔数
sum(payment_amount) order_amount,–单日支付金额
sum(if(order_count > 0 , 1 , 0)) payment_user_count --单日支付人数

sum(if(payment_amount > 0,1,0)) payment_sku_count --单日支付商品数

sum(unix_timestamp(payment_time)-unix_timestamp(create_time))/count(*)/60 payment_avg_time --下单到支付的平均时长,取分钟数
from dwd_fact_order_info
where dt=‘2020-06-25’
and payment_time is not null

③ 品牌复购率(月复购)(难)
主题:品牌信息、是哪个一级品类

统计的是一个品牌在当前月的复购率
复购率:商品被重复购买的频率
单词复购率:两次及以上购买人数 / 购买过的总人数
多次复购率:三次及以上购买人数 / 购买过的总人数

难点:

  1. 需要进行列转行,把用户当天购买的每个商品的明细炸开

– 使用侧写的方式,将一个用户当天购买的每个商品的明细(数组)进行侧写,形成多行
from dws_user_action_daycount lateral view explode(order_detail_stats) tmp as order_stats_struct

  1. 求一次购买、二次购买、三次购买
    sum(if(order_count>=1,1,0)) buycount,
    sum(if(order_count>=2,1,0)) buyTwiceLast,
    sum(if(order_count>=2,1,0))/sum( if(order_count>=1,1,0)) buyTwiceLastRatio,
    sum(if(order_count>=3,1,0)) buy3timeLast ,
    sum(if(order_count>=3,1,0))/sum( if(order_count>=1,1,0)) buy3timeLastRatio ,
    date_format(‘2020-06-25’ ,‘yyyy-MM’) stat_mn,
    ‘2020-06-25’ stat_date

  2. sum(order_count) order_count – 购买同一品牌数量
    from tmp_order
    join tmp_sku
    on tmp_order.sku_id=tmp_sku.id
    – 按照用户 + 1级品类 + 品牌id分组,得到一个用户购买某一个1级品类的某一品牌的个数
    group by tmp_order.user_id,tmp_sku.category1_id,tmp_sku.category1_name,tmp_sku.tm_id
    )tmp
    – 按照品牌进行 +
    group by tm_id, category1_id, category1_name;

### 滴滴订单离线数据分析与可视化技术方案 #### 1. 数据存储与管理 为了有效地管理和处理滴滴订单的离线数据,建议采用分布式文件系统如HDFS,并利用Apache Hive作为数据仓库工具。这不仅能够高效地存储大规模的数据集,还提供了SQL接口用于复杂查询和分析[^1]。 ```sql CREATE TABLE IF NOT EXISTS orders ( order_id STRING, passenger_id INT, driver_id INT, pickup_time TIMESTAMP, dropoff_time TIMESTAMP, fare DOUBLE ) PARTITIONED BY (date STRING); ``` #### 2. 数据预处理 在进行任何深入分析之前,必须对原始日志或交易记录执行必要的清理工作,包括但不限于去除重复项、填补缺失值以及转换字段格式等操作。Python库Pandas在此阶段非常有用,它能简化这些任务并提高效率[^2]。 ```python import pandas as pd data = pd.read_csv('orders.csv') cleaned_data = data.drop_duplicates().dropna() ``` #### 3. 分析模型构建 针对特定业务需求设计相应的统计学方法或者机器学习算法来进行预测性建模;比如通过时间序列分解了解乘客出行模式随季节变化的趋势,或是运用聚类分析识别不同类型的司机群体特征。 对于订单指标分析部分,可以计算平均响应时间和完成率等关键绩效指标(KPI),以此评估平台服务质量水平[^3]。 ```python from sklearn.cluster import KMeans kmeans = KMeans(n_clusters=5).fit(cleaned_data[['fare', 'duration']]) labels = kmeans.labels_ ``` #### 4. 可视化展示 最后一步就是将所得结论直观呈现出来供决策者参考。推荐使用Apache Superset这样的开源BI工具来创建交互式的仪表板页面,支持多种图表类型(柱状图、折线图、地图标记等),便于多维度探索海量信息背后隐藏的价值所在。 此外,还可以考虑集成其他高级特性如Visual Explain Plan, MongoDB Log Parser等功能模块辅助开发人员更好地理解底层逻辑结构及其性能表现情况[^4]。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值