【SQL数据分析 | 手把手教你做淘宝用户分析！】

2023-09-22 09:35:54

SQL也能做分析？当然！

常见的数据清洗，预处理，数据分类，数据筛选，分类汇总，以及数据透视等操作，用SQL一样可以实现（除了可视化，需要放到Excel里呈现）。SQL不仅可以从数据库中读取数据，还能通过不同的SQL函数语句直接返回所需要的结果，从而大大提高了自己在客户端应用程序中计算的效率。

但是，这个过程需要很熟练掌握SQL！

本文就利用提取MySQL的数据，通过写SQL的数据处理方式，来对一份淘宝数据进行用户分析。

01

数据来源及说明（现在我们调用数据基本上以API接口的形式去获取封装的）

本文从数据集中选取2014年11月18日至12月18日之间，8477名随机用户共1048575条行为数据（感兴趣的小伙伴们可以拿近几年的数据练手），数据集的每一行表示一条用户行为，共6列。

列字段包含以下：

user_id：用户身份item_id：商品IDbehavior_type：用户行为类型（包含点击、收藏、加购物车、购买四种行为，分别用数字1、2、3、4表示）user_geohash：地理位置（有空值）item_category：品类ID（商品所属的品类）time：用户行为发生的时间

02

提出问题

1. 整体用户的购物情况

pv（总访问量）、日均访问量、uv（用户总数）、有购买行为的用户数量、用户的购物情况、复购率分别是多少？

2. 用户行为转化漏斗

点击— 加购物车— 收藏— 购买各环节转化率如何？购物车遗弃率是多少，如何提高？

3. 购买率高和购买率为 0 的人群有什么特征

4. 基于时间维度了解用户的行为习惯

5. 基于RFM模型的用户分析

03

数据清洗

1. 导入数据

由于数据量有100多万，通过数据库管理工具 Navicat 将数据集导入 MySQL 数据库会表较慢，我这里使用ETL工具kettle进行导数，能够提高导数效率，也方便后续实现报表自动化处理，数据库的表名为user。

图片

2. 缺失值处理

item_category 列表示地理位置信息，由于数据存在大量空值，且位置信息被加密处理，难以研究，因此后续不对item_category列进行分析。

图片

3. 数据一致化处理

由于 time 字段的时间包含（年-月-日）和小时，为了方便分析，将该字段分成 2 个字段，一个日期列（date）和一个小时列（time）。

{!-- PGC_COLUMN --}

图片

由于 behavior_type 列的四种行为类型分别用 1，2，3，4 表示点击、收藏、加购物车、购买四种行为，为了方便查看数据，将1，2，3，4替换为 ‘pv'、’fav‘，’cart'，‘buy' 。

图片

通过查询表结构，可以看到 date 列日期列不是日期类型：

图片

将date 列改成 date 类型：

图片

04

构建模型和分析问题

1. 总体用户购物情况

图片

  • pv（总访问量）



  •  

图片

图片

  • 日均访问量


  • uv（用户总数）

  • 有购买行为的用户数量

图片

  • 用户的购物情况



  •  

图片

  • 复购率：产生两次或两次以上购买的用户占购买用户的比例



  •  

2. 用户行为转化漏斗

在购物环节中收藏和加入购物车两个环节没有先后之分，所以将这两个环节可以放在一起作为购物环节的一步。最终得到用户购物行为各环节转化率，如下：

图片

图片

图片


不同的行业转化率会有差异，据2012年的一项研究表明，在整个互联网范围内，平均转化率为2.13%（数据来源于《精益数据分析》），图中所示购买行为的转化率为1.04%，与行业平均值存在较大差异，淘宝移动端用户行为的转化率还有很大的增长空间。

3. 购买率高和购买率为低的人群有什么特征

购买率高用户特征：

图片

图片

由以上结果可以看出，购买率高的用户点击率反而不是最多的，这些用户收藏数和加购物车的次数也很少，一般不点击超过5次就直接购买，由此可以推断出这些用户为理智型消费者，有明确的购物目标，属于缺啥买啥型，很少会被店家广告或促销吸引。

购买率为低用户特征：

图片

由以上结果可以看出，购买率为低用户分为两类，

第一类是点击次数少的，一方面的原因是这类用户可能是不太会购物或者不喜欢上网的用户，可以加以引导，另一方面是从商品的角度考虑，是否商品定价过高或设计不合理；

第二类用户是点击率高、收藏或加购物车也多的用户，此类用户可能正为商家的促销活动做准备，下单欲望较少且自制力较强，思虑多或者不会支付，购物难度较大。

4. 基于时间维度了解用户的行为习惯

(1)一天中用户的活跃时段分布

图片

图片

可以看出，每日0点到5点用户活跃度快速降低，降到一天中的活跃量最低值，6点到10点用户活跃度快速上升，10点到18点用户活跃度较平稳，17点到23点用户活跃度快速上升，达到一天中的最高值。

（2）一周中用户活跃时段分布

由于第一周和第五周的数据不全，因此这两周的数据不考虑到此次数据分析中。

图片

图片

由以上结果可以看出，每周用户活跃度较稳定，每周五活跃度会有小幅降低，但是周末会慢慢回升。其中周五用户活跃度突增，这是由双十二电商大促销活动引起。

5. 基于 RFM 模型找出有价值的用户

RFM模型是衡量客户价值和客户创利能力的重要工具和手段，其中由3个要素构成了数据分析最好的指标，分别是：

  • R-Recency（最近一次购买时间）

  • F-Frequency（消费频率）

  • M-Money（消费金额）

由于数据源没有相关的金额数据，暂且通过 R 和 F 的数据对客户价值进行打分。

（1）计算R-Recency

由于数据集包含的时间是从2014年11月18日至2014年12月18日，这里选取2014年12月19日作为计算日期，统计客户最近发生购买行为的日期距离2014年12月19日间隔几天，再对间隔时间进行排名，间隔天数越少，客户价值越大，排名越靠前。

图片

（2）计算F-Frequency

先统计每位用户的购买频率，再对购买频率进行排名，频率越大，客户价值越大，排名越靠前。

图片

（3）对用户进行评分

对4330名有购买行为的用户按照排名进行分组，共划分为四组，对排在前四分之一的用户打4分，排在前四分之一到四分之二（即二分之一）的用户打3分，排在前四分之二到前四分之三的用户打2分，剩余的用户打1分，按照这个规则分别对用户时间间隔排名打分和购买频率排名打分，最后把两个分数合并在一起作为该名用户的最终评分。

计算脚本如下：

'''SELECT r.user_id,r.recent,r.recent_rank,f.frequency,f.freq_rank,CONCAT(  --  对客户购买行为的日期排名和频率排名进行打分CASE WHEN r.recent_rank <= (4330/4) THEN '4'WHEN r.recent_rank > (4330/4) AND r.recent_rank <= (4330/2) THEN '3'WHEN r.recent_rank > (4330/2) AND r.recent_rank <= (4330/43) THEN '2'ELSE '1' END,CASE WHEN f.freq_rank <= (4330/4) THEN '4'WHEN f.freq_rank > (4330/4) AND f.freq_rank <= (4330/2) THEN '3'WHEN f.freq_rank > (4330/2) AND f.freq_rank <= (4330/43) THEN '2'ELSE '1' END) AS user_valueFROM--  对每位用户最近发生购买行为的间隔时间进行排名（间隔天数越少，客户价值越大）(SELECT a.,(@rank := @rank + 1) AS recent_rankFROM  --  统计客户最近发生购买行为的日期距离'2014-12-19'间隔几天(SELECT user_id,DATEDIFF('2014-12-19',MAX(date)) AS recentFROM userWHERE behavior_type = 'buy'GROUP BY user_idORDER BY recent) AS a,(SELECT @rank := 0) AS b)AS r,-- 对每位用户的购买频率进行排名（频率越大，客户价值越大）(SELECT a.,(@rank2 := @rank2 + 1) AS freq_rankFROM   --  统计每位用户的购买频率(SELECT user_id,COUNT(behavior_type) AS frequencyFROM userWHERE behavior_type = 'buy'GROUP BY user_idORDER BY frequency DESC) AS a,(SELECT @rank2 := 0) AS b)AS fWHERE r.user_id = f.user_id;'''

图片

通过打分可以了解每位顾客的特性，从而实现差异化营销。比如对于 user_value = 44 的用户，为重点用户需要关注；对于user_value = 41 这类忠诚度高而购买能力不足的，可以可以适当给点折扣或捆绑销售来增加用户的购买频率。

（4）kettle制作报表自动化

为了实现监测每个月用户的评分变化，推测客户消费的异动状况，结合数据库使用ETL工具kettle实现定时输出评分结果：

图片

这里kettle实现过程不做详细描述。

05

结 论

1. 总体转化率只有 1%，用户点击后收藏和加购物车的转化率在 5% ，需要提高用户的购买意愿，可通过活动促销、精准营销等方式。

2. 购买率高且点击量少的用户属于理智型购物者，有明确购物目标，受促销和广告影响少；而购买率低的用户可以认为是等待型或克制型用户群体，下单欲望较少且自制力较强，购物难度较大。

3. 大部分用户的主要活跃时间在10点到23点，在19点到23点达到一天的顶峰。每周五的活跃度有所下降，但周末开始回升。可以根据用户的活跃时间段精准推送商家的折扣优惠或促销活动，提高购买率。

4. 通过 R 和 F 的数据对用户行为进行打分，对每位用户进行精准化营销，还可以通过对R 和 F 的数据监测，推测客户消费的异动状况，挽回流失客户。

以上，就是整个淘宝用户分析实操的过程。

更多推荐

C与C++字符串方法示例

C和C++中的字符串处理方法有所不同。在C语言中，字符串被表示为字符数组，使用字符数组来处理字符串。C语言提供了许多用于处理字符串的库函数，如strcpy，strcat，strcmp等。这些函数可以用于复制，连接和比较字符串等操作。在C++中，字符串被表示为字符串对象，使用字符串对象来处理字符串。C++中的字符串类提供

hadoop HDFS分布式计算概述，MapReduce概述，YARN概述

1、分布式计算概述1.1、什么是（数据）计算我们一直在提及：分布式计算，分布式暂且不论，“计算”到底是指什么呢？大数据体系内的计算，举例：销售额统计、区域销售占比、季度销售占比利润率走势、客单价走势、成本走势品类分析、消费者分析、店铺分析等等一系列，基于数据得出的结论。这些就是我们所说的计算。1.2、分布式（数据）计算

Spire.OCR for .NET 1.9.0 Crack

Spire.OCRfor.NET是一个专业的OCR库，用于从JPG、PNG、GIF、BMP和TIFF格式的图像中读取文本。开发人员可以轻松地在C#和VB.NET的.NET应用程序中添加OCR功能。它支持常用的图像格式，并提供从图像中​​读取多个字符和字体、粗体和斜体样式、扫描整个图像等功能。Spire.OCRfor.N

轮转数组：解决数组元素向右轮转的高效算法

轮转数组：解决数组元素向右轮转的高效算法leetcode189.轮转数组在计算机编程中，经常会遇到数组操作的问题，其中之一就是将数组中的元素向右轮转k个位置。这篇技术博客将详细介绍这个问题，探讨解决方案，并提供实际的Python代码来解决这个问题。问题描述给定一个整数数组nums，需要将数组中的元素向右轮转k个位置，其

【Java 基础篇】Java Lambda表达式详解

Lambda表达式是Java编程语言中引入的一个强大的特性，它使得编写更加简洁、可读性更强的代码变得更容易。本文将详细介绍Lambda表达式的概念、语法、用法以及示例，以帮助基础的Java开发者理解和应用Lambda表达式。什么是Lambda表达式Lambda表达式，也称为闭包，是一种匿名函数，它可以传递到方法作为参数

固态继电器的用途概述

固态继电器已成为现代电子电气系统中不可或缺的组件。这些设备以其可靠性和多功能性而闻名，这使得它们在广泛的应用中至关重要。在本文中，我们将探讨固态继电器的功能和应用。固态继电器：简介固态继电器(也称为SSR，固态式开关，或固态开关)是一种集成的无触点式的电子开关设备，由集成电路(C)和分立元件封装到一个紧凑的固态结构中。

如何判断自己的服务器是不是中了勒索病毒，勒索病毒解密数据恢复

勒索病毒日益横行，给企业的生产经营和个人数据造成了极大的影响。但是对单一的企业和个人而言，遭受勒索病毒攻击毕竟是低频率事件，这也导致很多人中招之后，无法判断出具体的问题，进而没有及时采取应对措施，导致事态和损失进一步扩大。接下来云天数据恢复中心将告诉大家如何判断自己的服务器是不是中了勒索病毒。1.所有文件扩展名都发生了

金蝶云星空与聚水潭对接集成物料查询连通商品上传（新）(物料主数据同步策略)

金蝶云星空与聚水潭对接集成物料查询连通商品上传（新）(物料主数据同步策略)数据源系统:金蝶云星空金蝶K/3Cloud结合当今先进管理理论和数十万家国内客户最佳应用实践，面向事业部制、多地点、多工厂等运营协同与管控型企业及集团公司，提供一个通用的ERP服务平台。K/3Cloud支持的协同应用包括但不限于：集中/分散销售、

广告牌安全监测系统，用科技护航大型广告牌安全

城市的街头巷尾，处处可见高耸的广告牌，它们以各种形式和颜色吸引着行人的目光。然而，作为城市景观的一部分，广告牌的安全性常常被我们所忽视。广告牌量大面大，由于设计、材料、施工方法的缺陷，加上后期的检查、维护和不定期检测的关系，在天气环境影响下，就有可能造成脱落事故的发生。2023年8月6日，福建一重达7吨的牌坊发生坠落，

【PyTorch攻略（1/7）】 张量基本语法

一、说明Tensor是一种特殊的数据结构，与数组和矩阵非常相似。在PyTorch中，我们使用张量对模型的输入和输出以及模型的参数进行编码。张量类似于NumPy和ndarray，除了张量可以在GPU或其他硬件加速器上运行。事实上，张量和NumPy数组通常可以共享相同的底层内存地址，具有称为桥接到np标签的功能，这消除了复

VR全景展示的功能有哪些？你了解多少？

VR全景展示作为一种全新的视觉体验技术，能够为人们带来强烈的视觉效果以及沉浸式的观感，在旅游、房地产、车展、博物馆等都有着十分广泛的应用。这种富媒体技术，具有很好的交互性和沉浸感，能够带给大家更好的体验，那么VR全景展示的功能有哪些呢？1、沉浸式交互体验VR全景展示为观众提供了更加真实、更加具有沉浸式的体验，以720°

热文推荐
  1. TypeScript入门

    目录一：语言特性二：TypeScript安装NPM安装TypeScript三：TypeScript基础语法第一个TypeScript程序四：TypeScript保留关键字空白和换行TypeScript区分大小写TypeScript注释TypeScript支持两种类型的注释五：TypeScript与面向对象六：TypeS

  2. 如何使用ChatGPT，而不是生成默认风格的八股文

    现在我每天都使用ChatGPT来执行多项任务，包括但不限于内容创建。无论是编写文本还是与我讨论我的业务目标，ChatGPT总是会时不时的用到。但与所有强大的工具一样，ChatGPT和类似的大型语言模型(LLM)也有其局限性。在我从事人工智能工作的过程中，我多次偶然发现它们。如果您在业务中依赖ChatGPT而不了解其局限

  3. 正则表达式 - 语法

    目录正则表达式-语法普通字符测试工具非打印字符特殊字符限定符定位符选择以下列出?=、?<=、?!、?反向引用实例实例正则表达式-语法正则表达式是一种用于匹配和操作文本的强大工具，它是由一系列字符和特殊字符组成的模式，用于描述要匹配的文本模式。正则表达式可以在文本中查找、替换、提取和验证特定的模式。例如：runoo+b，

  4. 125. 验证回文串 【简单题】

    题目如果在将所有大写字符转换为小写字符、并移除所有非字母数字字符之后，短语正着读和反着读都一样。则可以认为该短语是一个回文串。字母和数字都属于字母数字字符。给你一个字符串s，如果它是回文串，返回true；否则，返回false。示例1：输入:s="Aman,aplan,acanal:Panama"输出：true解释："a

  5. amlogic 机顶盒关闭DLNA 后，手机还能搜到盒子

    S905L3带有投屏的功能，并通过com.droidlogic.mediacenter.dlna.MediaCenterService服务的启动和停止来开启和关闭DLNA功能，但是在测试中发现机顶盒关闭DLNA后，手机还能搜索到盒子。我在复测中发现关闭后有时很难很久搜索到盒子，有时却很容易搜索到。通过查看日志，发现打开

  6. C语言每日一题(10)：无人生还

    文章主题：无人生还🔥所属专栏：C语言每日一题📗作者简介：每天不定时更新C语言的小白一枚，记录分享自己每天的所思所想😄🎶个人主页：[₽]的个人主页🏄🌊目录前言编程起因项目介绍情节简介讨论内容找出凶手设计思路1.整体逻辑方法一方法二2.具体逻辑方法一方法二代码展示方法一：依次假设法（最容易想到的方法）方法二：逻

  7. 【ABAP】如何理解SAP中的CLIENT (客户端)

    💂作者简介：THUNDER王，阿里云社区专家博主，华为云·云享专家，腾讯云社区认证作者，CSDNSAP应用技术领域优质创作者。在学习工作中，我通常使用偏后端的开发语言ABAP，SQL进行任务的完成，对SAP企业管理系统，SAPABAP开发和数据库具有较深入的研究。💅文章概要：MANDT集团永远是无数SAP入门人员无

  8. 【STM32】SDIO—SD 卡读写01

    基于stm32f103基于零死角玩转STM32—F103指南者简介1.SD卡总共有8个寄存器，用于设定或表示SD卡信息。2.SD卡的寄存器不能像STM32那样访问，而是利用命令访问，SDIO定义了64个命令。SD卡接收到命令后，根据命令要求对SD卡内部寄存器进行修改，程序控制中只需要发送组合命令就可以实现SD卡的控制以

  9. 认识微服务、服务拆分和远程调用

    文章目录1.认识微服务2.微服务架构的特征3.SpringCloud4.服务拆分5.远程调用6.提供者与消费者1.认识微服务单体架构：将业务的所有功能集中在一个项目中开发，打成一个包部署分布式架构：根据业务功能对系统做拆分，每个业务功能模块作为独立项目开发，称为一个服务架构优点缺点单体架构架构简单，部署成本低，耦合度高