用SQL和tableau做数据--O2O优惠券线下使用数据阿里天池数据集

本文以‘o2o优惠券线下核销数据集’的分析全过程为例，展示数据分析的全过程

数据清洗工具：jupyter notebook

数据分析工具：datagrip,Excel

可视化图表工具：tableau

分析类型：描述性分析，诊断分析

分析方法：漏斗分析，RFM用户价值分析，AARRR分析，假设检验分析。

目录

一.数据清洗

1.1数据集来源

1.2数据说明

1.3数据导入

1.4查看缺失值

1.5 数据处理编辑

1.6 保存文件编辑

二.将文件导入datagrip中用SQL分析

2.1查询各列数据为空值的数量 

三.总体情况统计分析

3.1 数据总体情况 

3.2 优惠券每月核销情况统计

3.3 每月新增用户数

四.用户分析

4.1 获客：每日新增用户情况

4.2 转化及流失情况

4.3 用户行为转化漏斗计算

 4.4 用户留存情况

4.5 用户价值分析

4.5.1 先查询用户最近的消费时间间隔及消费频次

4.5.2  分别查询R,F的最大值，最小值以及各值数量分布情况

 4.5.3 根据R,F值最大值和最小值的区间设计本次的打分标准，计算R,F的值

 4.5.4 查询R,F值的平均值

 4.5.5 根据平均值和用户分类规则表对用户分类

4.5.6 查询各分层用户数量

五.商户分析

5.1 每月有消费商户统计

5.2 一周内每天有消费的商户数量

5.3 商户整体核销率，核销率在0.25以下的商户占比

5.6 商户分层

六.优惠券分析

 6.1 优惠券每日领取，每日核销情况

6.2 优惠券平均核销时间

6.3 不同距离下优惠券的领取量及核销率

七. 相关分析

7.1 门店距离与优惠券核销率的相关性

7.2 优惠券折扣与核销率的相关性

 八.Tableau可视化图表

九.数据分析

---

一.数据清洗

1.1数据集来源

阿里天池：O2O优惠券使用预测数据集_Tianchi Datasets (aliyun.com)

1.2数据说明

该数据集提供2016年1月1日至2016年6月30日的真实线上和线下用户消费数据。研究人员预计将预测客户在收到优惠券后 15 天内兑换优惠券的概率。

线下消费及优惠券表（offline_train.csv.zip）

1.3数据导入

1.4查看缺失值

#通过查看缺失值发现Coupon_id，Discount_rate及Date_received的缺失值数量一样，Coupon_id表示优惠券id，当它为null时，说明没有这张优惠券，那么Discount_rate，Date_received也没有意义，所以存在三者同时为null的情况是合理的。
#用0替代Coupon_id，Discount_rate的缺失值，用fillna()对缺失值进行填充
#Distance出现缺失值可能是用户关闭了定位地理位置授权，由于Distance的取值为【0-10】,所以可选12作为缺失值填充

1.5 数据处理

1.6 保存文件

二.将文件导入datagrip中用SQL分析

2.1查询各列数据为空值的数量 

输出：

三.总体情况统计分析

3.1 数据总体情况 

输出：

分析：领券总人数1053282人，但领券消费人数仅有75382人，核销率7.16%，优惠券使用率低。

3.2 优惠券每月核销情况统计

输出：

3.3 每月新增用户数

 输出：

分析：月新增用户呈波段式增加，1，3，5月份新增用户增加明显，考虑是当月优惠券的发放优化、商户促销活动、渠道推广等因素影响

四.用户分析

4.1 获客：每日新增用户情况

输出：

4.2 转化及流失情况

 2016-01-01至2016~06-30指标情况：

 输出：

4.3 用户行为转化漏斗计算

输出：

 4.4 用户留存情况

输出：

4.5 用户价值分析

因为数据源中没有涉及消费金额，因此只从R和F来对客户价值进行评分,不考虑M维度。

4.5.1 先查询用户最近的消费时间间隔及消费频次

输出：

4.5.2  分别查询R,F的最大值，最小值以及各值数量分布情况

 输出：

 4.5.3 根据R,F值最大值和最小值的区间设计本次的打分标准，计算R,F的值

 输出：

 4.5.4 查询R,F值的平均值

输出：

 4.5.5 根据平均值和用户分类规则表对用户分类

 输出：

4.5.6 查询各分层用户数量

 输出：

五.商户分析

5.1 每月有消费商户统计

输出：

5.2 一周内每天有消费的商户数量

输出：

分析：用户消费在周末相比其他时间更活跃

5.3 商户整体核销率，核销率在0.25以下的商户占比

 输出：

5.4 各商户持券到店消费的人数

输出：

5.5 持券到店消费人数在500以上的商家,链接顾客到店平均距离和平均折扣力度

 输出：

 相关性分析：

 分析：到店持券消费用户与优惠券的折扣率、到店距离均呈负相关，相关系数|r|＜0.3，说明相关程度较弱。

5.6 商户分层

 A级商户：核销数量>100

 B级商户：50<核销数量<100, C级商户：核销数量<50（查询方法如上图）

输出：

分析：核销数量＞100的A级商户数量为801家，但销量占比高达80.39%，头部商户明显。

六.优惠券分析

 6.1 优惠券每日领取，每日核销情况

输出：

分析：优惠券每日的领取及核销量随着月份变动，领券及核销主要集中在1，3，5月份.

6.2 优惠券平均核销时间

 输出：

6.3 不同距离下优惠券的领取量及核销率

输出：

分析：无门槛及小额优惠券的核销率较高，持券消费用户主要集中在核销距离500米内。

七. 相关分析

7.1 门店距离与优惠券核销率的相关性

 输出：

分析：门店距离与优惠券核销率呈负相关，相关系数0.3＜|r|＜0.6，说明相关程度较高。

7.2 优惠券折扣与核销率的相关性

输出：

分析：优惠券折扣与核销率呈负相关，相关系数|r|＞0.6，说明相关程度高。

 八.Tableau可视化图表

九.数据分析

优惠券领取总人数未1053282人，领券消费总人数75382人，核销率仅为7.16%，造成优惠券核销率低的因素可能与商户门店距离、满减门槛、产品种类、商户服务等因素有关，由于数据源表数据有限，因此将从每月领券核销情况、用户消费日期、门店距离、商户消费情况、折扣率方面进行分析。

9.1 从商户角度分析：

①一周内每天有消费的商户数量来看，整体差别不大，但周末消费用户更活跃；

②每月有消费的商户整体呈上升趋势，根据核销数量进一步对商户进行分层，核销数量>100的A级商户商家占比为9.52%，销量占比为80.39%，头部商户明显。

③筛选持券到店消费人数在500以上的商家,链接顾客到店平均距离和平均折扣力度，发现到店持券消费用户与优惠券的折扣率、到店距离均呈负相关，相关程度较弱，说明优惠券折扣和距离不是门店吸引顾客核销的主要原因，可能与门店服务、商品种类或品质、促销活动等因素有关。

9.2 从优惠券角度分析：

①从每月的优惠券领取及核销数据来看，领取优惠券的人数很多，但实际使用优惠券购买商品的人却很少，优惠券的个性化投放可加强。

②优惠券各月的发放量波动大，3，6月份的消费总量比1，2月份高很多，考虑是各大重大节假日或是商户的营销策略等原因导致的。

③优惠券核销与优惠券折扣呈高度负相关，无门槛优惠券及小额满减核销率较高。

④优惠券平均核销时间为7.8天，核销时间长。

⑤优惠券核销率与门店距离呈高度负相关，门店距离越小核销率越高，门店距离在500米内的优惠券更容易被核销。

9.3 从用户角度来看：

①无门槛及小额优惠券更受用户青睐，说明大部分用户对优惠券不敏感。

这篇好文章是转载于：学新通技术网