数据分析项目 --SQL 的用户消费分析

本贴最后更新于 2018 天前,其中的信息可能已经时移世改

1、导入数据
方法 1 直接导入 CSV 数据,不用建表做关联,如下图
image.png
方法 2 建表做关联后导入数据
建表,订单表(订单编号,用户 ID,付款情况,价格,订单付款时间)

create table orderform( orderid varchar(20) null, userid varchar(10) null, paid varchar(10) null, price varchar(10) null, payment_time datetime null)

建表,客户信息表

create table userlist( userid varchar(10) null,gender varchar(5) null,birth datetime null)

1.1 需要更新下列名,更新列名的语句如下,不过还是表设计里面改起来最快
exec sp_rename 'order_info_utf.column1','order_info_utf.orderid'

2、用户消费行为分析相关
有关用户消费行为的统计指标,主要的分析内容主要是:
销量和消费金额分析、消费的时间节点、复购率回购率分析、用户分层、用户生命周期和消费周期等,具体可以看看

2.1 可以统计不同月份订单数、用户数、下单总金额

select convert(varchar(6),payment_time,112) as year_month, count(orderid) as order_num , count(distinct userid) as user_num,sum(price) as sum from order_info_utf where paid='已支付' group by convert(varchar(6),payment_time,112) order by year_month

image.png

2.2 统计用户复购率、回购率(这两个不一样的)
a. 复购率 当月消费一次以上(叫复购)人数的占比情况(消费次数 >1 的人数 ÷ 消费总人数)

这里遇到了一个难点,就是 SQL 转换成带 % 号的比率,折磨人,计算完毕的比率是 float 型,% 是字符型,所以拼接是要将 float 转换后再加 %,然后是 char 和 varchar 的区别导致的,也可以用 convert 转换

select t.*,concat(cast(round((cast(t.repeat_user as float)/t.user_num)*100,2)as varchar),'%') as repeat_rate from (select year_month,count(distinct userid)as user_num, sum(case when b.frequency>1 then 1 else 0 end) as repeat_user from (select convert(varchar(6),payment_time,112) as year_month , userid,count(userID) as frequency from order_info_utf where paid='已支付' group by convert(varchar(6),payment_time,112), userID) b group by year_month) t order by year_month

image.png

image.png

b.回购率,回购率一般以月为单位区分,上月购买过的人在本月又买了,先找出本月购买的

select a.year_month1 年月,a.购买人数,b.回购人数 from (select convert(varchar(6),payment_time,112) as year_month1,count( DISTINCT userid) 购买人数 from order_info_utf where paid='已支付' group by convert(varchar(6),payment_time,112))a left join (select convert(varchar(6),payment_time,112) year_month2,count (DISTINCT a.userid )回购人数 from order_info_utf a where a.paid='已支付' and a.userid in (select userid from order_info_utf B where paid='已支付' and convert(varchar(6),B.payment_time,112)=convert(varchar(6),a.payment_time,112)-1) group by convert(varchar(6),payment_time,112))b on a.year_month1=b.year_month2 order by a.year_month1

image.png

2.3.统计男女的消费频次是否有差异,一般用户分层可以从性别、年龄段来着手
a.从性别分层找差异

select u.gender,sum(b.frenquency) as sum_frequency,count(distinct u.userid), round(convert(float,sum(b.frenquency))/convert(float,count(distinct u.userid)),2) as avg_frequency from user_info_utf u left join (select userid,count(userid) as frenquency from order_info_utf where paid='已支付' group by userid) b on u.userid=b.userid where gender is not null group by gender

image.png

结论: 可见在性别方面,平均频次相差不大,那么可以顺便分析下不同性别的消费总额与平均额

select u.gender,sum(b.expenditure) as sum_expenditure,count(distinct u.userid), round(convert(float,sum(b.expenditure))/convert(float,count(distinct u.userid)),2) as avg_expenditure from user_info_utf u left join (select userid,sum(price) as expenditure from order_info_utf where paid='已支付' group by userid)b on u.userid=b.userid where u.gender is not null group by u.gender

image.png

b.年龄分层,从年龄段来分析,先将年龄分层,最小的 3 岁,最大的 1947,显然大于 119 的基本属于异常值,可以将其剔除

select c.age_leval,count(c.userid) as sum_frequency ,sum(a.expenditure) as sum_expenditure, round(sum(a.expenditure)/count(c.userid),4) as avg_expenditure from (select b.userid,age_leval=(case when age <=17 then '0-17岁' when age between 18 and 25 then '18-25岁'when age between 26 and 35 then '26-35岁' when age between 36 and 45 then '36-45岁' when age between 46 and 55 then '46-55岁' else '56岁以上' end) from (select userid,age=datediff(yy,convert(varchar(10),birth,23),GETDATE()) from user_info_utf where birth is not null and datediff(yy,convert(varchar(10),birth,23),GETDATE())<119) b )c left join (select userid,sum(price) as expenditure from order_info_utf where paid='success' group by userid) a on c.userid=a.userid group by c.age_leval

image.png
可以看出 26-35 和 36-45 年龄段的消费热刺和消费额都遥遥领先,但是在人均方面优势并不是十分明显,只有 36-45 年龄段的人均比较高,总消费额和总消费频次最多的 26-35 年龄段,人均消费和 56 岁以上年龄段的人均消费相差无几。

2.4.统计多次消费的用户,第一次和最后一次消费时间的间隔

select distinct userid,count(userid)as frequency, convert(varchar(10),min(payment_time),120) as first_payment_time, convert(varchar(10),max(payment_time),120) as last_payment_time, datediff(dd,min(payment_time),max(payment_time)) as time_lag from order_info_utf where paid='已支付' group by userid having count(userid)>=2 order by frequency desc

image.png

2.5.消费黄金法则,二八原则,订单数量最多的 20% 客户,创造了多少的消费额,占总消费额的比例
以及订单额最多的客户,占到 80% 的订单额的用户的数占比总用户数的比率

select count(userid),sum(user_price),c.sum_user,c.sum_price,round(sum(b.user_price)/c.sum_price,2) as rate from (select top 20 percent userid,count(userid) as user_frequency,sum(price) as user_price from order_info_utf where paid='success' group by userid order by count(userid) desc) b , (select count(distinct userid) as sum_user ,sum(price) as sum_price from order_info_utf where paid='success') c group by c.sum_price,c.sum_user

image.png

以上,符合二八法则,关键用户满足,如果比率小于 0.8,可以着重发展先大客户

  • SQL
    129 引用 • 396 回帖 • 3 关注

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...

推荐标签 标签

  • SMTP

    SMTP(Simple Mail Transfer Protocol)即简单邮件传输协议,它是一组用于由源地址到目的地址传送邮件的规则,由它来控制信件的中转方式。SMTP 协议属于 TCP/IP 协议簇,它帮助每台计算机在发送或中转信件时找到下一个目的地。

    4 引用 • 18 回帖 • 630 关注
  • 周末

    星期六到星期天晚,实行五天工作制后,指每周的最后两天。再过几年可能就是三天了。

    14 引用 • 297 回帖 • 1 关注
  • 笔记

    好记性不如烂笔头。

    310 引用 • 794 回帖
  • Visio
    1 引用 • 2 回帖
  • flomo

    flomo 是新一代 「卡片笔记」 ,专注在碎片化时代,促进你的记录,帮你积累更多知识资产。

    6 引用 • 143 回帖 • 1 关注
  • Solidity

    Solidity 是一种智能合约高级语言,运行在 [以太坊] 虚拟机(EVM)之上。它的语法接近于 JavaScript,是一种面向对象的语言。

    3 引用 • 18 回帖 • 434 关注
  • Lute

    Lute 是一款结构化的 Markdown 引擎,支持 Go 和 JavaScript。

    29 引用 • 202 回帖 • 28 关注
  • GitLab

    GitLab 是利用 Ruby 一个开源的版本管理系统,实现一个自托管的 Git 项目仓库,可通过 Web 界面操作公开或私有项目。

    46 引用 • 72 回帖 • 1 关注
  • Spring

    Spring 是一个开源框架,是于 2003 年兴起的一个轻量级的 Java 开发框架,由 Rod Johnson 在其著作《Expert One-On-One J2EE Development and Design》中阐述的部分理念和原型衍生而来。它是为了解决企业应用开发的复杂性而创建的。框架的主要优势之一就是其分层架构,分层架构允许使用者选择使用哪一个组件,同时为 JavaEE 应用程序开发提供集成的框架。

    947 引用 • 1460 回帖
  • JRebel

    JRebel 是一款 Java 虚拟机插件,它使得 Java 程序员能在不进行重部署的情况下,即时看到代码的改变对一个应用程序带来的影响。

    26 引用 • 78 回帖 • 676 关注
  • React

    React 是 Facebook 开源的一个用于构建 UI 的 JavaScript 库。

    192 引用 • 291 回帖 • 370 关注
  • CSDN

    CSDN (Chinese Software Developer Network) 创立于 1999 年,是中国的 IT 社区和服务平台,为中国的软件开发者和 IT 从业者提供知识传播、职业发展、软件开发等全生命周期服务,满足他们在职业发展中学习及共享知识和信息、建立职业发展社交圈、通过软件开发实现技术商业化等刚性需求。

    14 引用 • 155 回帖 • 1 关注
  • OnlyOffice
    4 引用 • 25 关注
  • OpenCV
    15 引用 • 36 回帖 • 7 关注
  • JavaScript

    JavaScript 一种动态类型、弱类型、基于原型的直译式脚本语言,内置支持类型。它的解释器被称为 JavaScript 引擎,为浏览器的一部分,广泛用于客户端的脚本语言,最早是在 HTML 网页上使用,用来给 HTML 网页增加动态功能。

    730 引用 • 1281 回帖 • 3 关注
  • TensorFlow

    TensorFlow 是一个采用数据流图(data flow graphs),用于数值计算的开源软件库。节点(Nodes)在图中表示数学操作,图中的线(edges)则表示在节点间相互联系的多维数据数组,即张量(tensor)。

    20 引用 • 19 回帖
  • Firefox

    Mozilla Firefox 中文俗称“火狐”(正式缩写为 Fx 或 fx,非正式缩写为 FF),是一个开源的网页浏览器,使用 Gecko 排版引擎,支持多种操作系统,如 Windows、OSX 及 Linux 等。

    7 引用 • 30 回帖 • 385 关注
  • SpaceVim

    SpaceVim 是一个社区驱动的模块化 vim/neovim 配置集合,以模块的方式组织管理插件以
    及相关配置,为不同的语言开发量身定制了相关的开发模块,该模块提供代码自动补全,
    语法检查、格式化、调试、REPL 等特性。用户仅需载入相关语言的模块即可得到一个开箱
    即用的 Vim-IDE。

    3 引用 • 31 回帖 • 111 关注
  • TextBundle

    TextBundle 文件格式旨在应用程序之间交换 Markdown 或 Fountain 之类的纯文本文件时,提供更无缝的用户体验。

    1 引用 • 2 回帖 • 84 关注
  • Outlook
    1 引用 • 5 回帖 • 5 关注
  • WebSocket

    WebSocket 是 HTML5 中定义的一种新协议,它实现了浏览器与服务器之间的全双工通信(full-duplex)。

    48 引用 • 206 回帖 • 287 关注
  • Vim

    Vim 是类 UNIX 系统文本编辑器 Vi 的加强版本,加入了更多特性来帮助编辑源代码。Vim 的部分增强功能包括文件比较(vimdiff)、语法高亮、全面的帮助系统、本地脚本(Vimscript)和便于选择的可视化模式。

    29 引用 • 66 回帖 • 3 关注
  • ReactiveX

    ReactiveX 是一个专注于异步编程与控制可观察数据(或者事件)流的 API。它组合了观察者模式,迭代器模式和函数式编程的优秀思想。

    1 引用 • 2 回帖 • 182 关注
  • 宕机

    宕机,多指一些网站、游戏、网络应用等服务器一种区别于正常运行的状态,也叫“Down 机”、“当机”或“死机”。宕机状态不仅仅是指服务器“挂掉了”、“死机了”状态,也包括服务器假死、停用、关闭等一些原因而导致出现的不能够正常运行的状态。

    13 引用 • 82 回帖 • 74 关注
  • App

    App(应用程序,Application 的缩写)一般指手机软件。

    91 引用 • 384 回帖
  • Bootstrap

    Bootstrap 是 Twitter 推出的一个用于前端开发的开源工具包。它由 Twitter 的设计师 Mark Otto 和 Jacob Thornton 合作开发,是一个 CSS / HTML 框架。

    18 引用 • 33 回帖 • 650 关注
  • Sandbox

    如果帖子标签含有 Sandbox ,则该帖子会被视为“测试帖”,主要用于测试社区功能,排查 bug 等,该标签下内容不定期进行清理。

    435 引用 • 1238 回帖 • 592 关注