轉(zhuǎn)載請在文章起始處注明出處，謝謝峭竣。
數(shù)據(jù)來源CDNow網(wǎng)站的用戶購買明細膏斤。一共有客戶ID，購買日期邪驮，購買數(shù)量莫辨，購買金額四個字段。原數(shù)據(jù)是txt的格式毅访，將它改為csv的格式導入進mysql沮榜，由于userid有重復，所以不設(shè)主鍵喻粹，只是用作分析練習蟆融。
加載數(shù)據(jù)：
paidtime表示購買日期，products表示購買數(shù)量守呜，amounts表示購買金額
對表進行優(yōu)化：新增一列month對paidtime只取年月和第一天型酥，如：1997-01-12只取成1997-01-01，方便后續(xù)分析查乒。生成新的表格cdnowtest弥喉，并修改cdnowtest中month的類型為date。
create table data.cdnowtest
as
select * from (
select *,date_format(paidtime,'%Y-%m-01') as pmonth
from data.cdnow) t
cdnowtest：
觀測下整個數(shù)據(jù)玛迄，先對整個數(shù)據(jù)按單筆消費金額amount進行降序排序
消費最高的一筆是由userId為8830的用戶產(chǎn)生的由境，消費了1286.01，隨后單筆消費的金額迅速降低至最高金額的一半。
將數(shù)據(jù)進行升序的排序：
發(fā)現(xiàn)有些用戶消費為0虏杰，且都是在1,2,3月份的讥蟆。因為一個userId有可能產(chǎn)生多筆消費，所以我們觀察下這些用戶在其他月份有沒有產(chǎn)生消費纺阔。
select *
from data.cdnowtest
where id in(select id
from(
select * from data.cdnowtest
order by amount) t
where amount = 0)
觀察表格瘸彤，我們可以發(fā)現(xiàn)，有些用戶確實一次未消費笛钝，有些用戶如：2703钧栖，在1月份未消費，但在二月份消費了一次婆翔，隨后就再沒消費過拯杠，可能是活動促銷等原因造成的一次性消費。
我們根據(jù)這張表將一次未消費的用戶過濾出來：
部分篩選結(jié)果如上表所示啃奴，所以我們可以向這些用戶詢問或者反饋潭陪，統(tǒng)計未消費的原因并針對性得找出改善的措施等。
對于整張表最蕾，我們大致可將客戶分類為不活躍客戶（消費次數(shù)為0）依溯，一般價值客戶（消費1次），回頭客（消費2-3次）瘟则，忠實客戶（消費3次以上）黎炉，分別計算下它們的占比。
不活躍客戶我們已經(jīng)由上表得出醋拧，總數(shù)為68慷嗜。
計算一般價值客戶數(shù)為：
select count(id) as'一般價值客戶'
from(
select id,count(amount) ca
from data.cdnowtest
where amount != 0
group by id
having ca=1) t
同樣，回頭客數(shù)量為：
忠實客戶為：
總客戶數(shù)為：23570
由上述查詢出的數(shù)據(jù)可知：
不活躍客戶數(shù)最少為68丹壕，占比約為0.3%
一般價值客戶數(shù)最多為11843庆械，占比約為50.2%
回頭客數(shù)為6294，占比約為26.7%
忠實客戶數(shù)為5365菌赖，占比約為22.8%
接下來我們根據(jù)用戶進行分組缭乘，分析每個用戶的產(chǎn)品購買數(shù)量和購買金額：
select max(sum_p) '最大消費數(shù)',
min(sum_p) '最小消費數(shù)',
avg(sum_p)'平均消費數(shù)',
max(sum_m)'最大消費額',
min(sum_m)'最小消費額',
avg(sum_m)'平均消費額'
from(
select id,sum(products) sum_p,
round(sum(amount),2) sum_m
from data.cdnowtest
group by id) t
從客戶角度看，每位客戶平均購買了7張CD琉用，最多的用戶購買了1033張堕绩，最大的消費金額達到了近一萬四，應(yīng)該是屬于忠實粉絲邑时，用戶的平均消費金額（客單價）為106元
接下來按月維度進行分析：
select pmonth,sum(products) sp,round(sum(amount),0) sa
from data.cdnowtest
group by pmonth
根據(jù)聚合的結(jié)果可知奴紧，cd銷量前三個月都非常高漲，后期則下降明顯且呈現(xiàn)相對平穩(wěn)狀態(tài)刁愿。
我們再將數(shù)據(jù)按用戶分組绰寞，觀察第一次的消費時間到逊，再按月份進行分組铣口，統(tǒng)計消費次數(shù)滤钱。
select minm,count(id) '月消費總次數(shù)'
from(
select id,min(pmonth) as minm
from data.cdnowtest
group by id) t
group by minm
由查詢表可知，所有用戶的第一次消費都是集中在前三個月的脑题，這也就不難解釋為什么cd前三個月的銷量和銷售額都非常高漲件缸。
接下來分析消費中的復購率和回購率
首先求復購率，復購率的定義是在某時間窗口內(nèi)消費兩次及以上的用戶在總消費用戶中占比叔遂。這里的時間窗口是月他炊，如果一個用戶在同一天下了兩筆訂單，這里也將他算作復購用戶已艰。
分別查詢出各月消費總?cè)藬?shù)和各月復購的人數(shù)痊末。
各月復購人數(shù)：
各月消費人數(shù)：
將兩表連接，求出復購率：
select a.pmonth,a.mbackc,b.mtotalc,(a.mbackc/b.mtotalc) as rate
from(select pmonth,count(cid) mbackc
from(
select pmonth,id,count(id) cid
from data.cdnowtest
group by pmonth,id) t1
where cid>=2
group by pmonth) a
join (select pmonth,count(cid) mtotalc
from(
select pmonth,id,count(id) cid
from data.cdnowtest
group by pmonth,id) t2
group by pmonth) b
on a.pmonth=b.pmonth
由表可知：看出復購率在早期哩掺，因為大量新用戶加入的關(guān)系凿叠，新客的復購率并不高，最低的一月份只有10%嚼吞。而在后期盒件，在忠實客戶的影響下，復購率逐漸上升且趨于穩(wěn)定舱禽，在20%左右炒刁。
接下來計算回購率√苤桑回購率是某一個時間窗口內(nèi)消費的用戶翔始，在下一個時間窗口仍舊消費的占比。
首先先按用戶id和pmonth進行分組里伯，篩選出每個用戶在每個月的消費記錄绽昏，再將表進行自連接，產(chǎn)生笛卡爾積效應(yīng)俏脊，篩選出前后相差一個月的記錄：
再根據(jù)篩選出的結(jié)果按t1的月份進行分組全谤，分別得出當月消費總?cè)藬?shù)和回購人數(shù)，最后求得回購率
select *,(ct2m/ct1m) rate
from(
select t1.pmonth,count(t1.pmonth) ct1m,count(t2.pmonth) ct2m
from(
select id,pmonth from data.cdnowtest
where amount <> 0
group by id,pmonth) t1
left join(
select id,pmonth from data.cdnowtest
where amount <> 0
group by id,pmonth) t2
on t1.id=t2.id and t1.pmonth=date_sub(t2.pmonth,interval 1 month)
group by t1.pmonth) t3
從表中可以看出爷贫，用戶的回購率是高于復購率的认然，波動性也較強，早期受新客戶的影響漫萄，用戶的回購率為15%左右卷员，后期穩(wěn)定在30%左右。最后項目數(shù)據(jù)為0腾务，是由于并沒有接下來7月份的數(shù)據(jù)毕骡，所以無法得出。綜合復購率和回購率兩張表格分析，新用戶不管是從回購率還是復購率看都不及老客戶
接下來分析用戶質(zhì)量未巫，首先根據(jù)用戶分組窿撬，計算并按消費額排序出每個用戶的消費總額：
根據(jù)消費的二八法則，我們需要知道消費的top20%的客戶貢獻了多少的額度叙凡，根據(jù)前面的查詢結(jié)果劈伴，我們知道，此次我們調(diào)查的用戶共有23570位握爷，那么取前20%也就是4714位跛璧，所以我們有：
select t1.top20,t2.total,(t1.top20/t2.total) rate
from(
(select round(sum(suma)) top20
from(
select id,round(sum(amount),2) suma
from data.cdnowtest
group by id
order by suma desc
limit 4714) t) t1,
(select round(sum(suma)) total
from(
select id,sum(amount) suma
from data.cdnowtest
group by id) t) t2
)
同理，我們可以再篩選出前40%的用戶貢獻的額度及貢獻率新啼，結(jié)果如下：
根據(jù)統(tǒng)計的結(jié)果追城，我們可以得出：消費排名前20%的用戶貢獻了近70%的消費總額，消費排名前40%的用戶貢獻了約85%的消費總額燥撞，確實呈現(xiàn)了28的傾向漓柑，所以維護好這批排名靠前的用戶顯得尤為重要！
接下來簡單計算下用戶生命周期叨吮，這里定義第一次消費至最后一次消費為整個用戶生命辆布。
首先根據(jù)用戶進行分組，篩選出消費次數(shù)大于1的用戶和消費的最早時間和最晚時間茶鉴，然后計算出相差的天數(shù)锋玲。
有消費間隔為0的，是指當天消費了兩次或以上涵叮，因為我們已經(jīng)將消費大于1次的過濾了出來惭蹂。
接著，我們觀察下deltaday的一些相關(guān)數(shù)據(jù)割粮，結(jié)果如下：
maxd表示最長用戶的生命周期是544天盾碗，
mind表示最短的是0天，
avgd平均生命周期約為273天舀瓢。
接下來廷雅，我們根據(jù)用戶的生命周期大致劃分下等級：
level=0或1 是指生命周期為0-100天的用戶標簽
level=2 是100-200，以此類推
level=3 200-300
level=4 300-400
level=5 400-500
level=6 500+
先計算出下這些用戶的平均消費頻次：
由于用了多次嵌套京髓，代碼顯得稍有些復雜
select level,avg(cid) avgcid
from(
select t3.level,t3.id,count(id) cid
from(
select cd.*,t2.level
from data.cdnowtest cd
join(
select id,ceil(deltaday/100) as level
from(
select id,datediff(maxm,minm) deltaday
from(
select id,min(paidtime) minm,max(paidtime) maxm
from data.cdnowtest
group by id
having count(id)>1) t) t1) t2
on cd.id=t2.id)t3
group by t3.level,t3.id) t4
group by level
運行結(jié)果如下：
可以看出：leve在0-2之間的也就是生命周期在0-200天的航缀，平均消費頻次在2-3之間，隨著生命周期的增長堰怨，消費頻次逐漸變高芥玉。level=6的忠實客戶的值最高，平均消費頻次在12次左右备图。
隨后灿巧，我們根據(jù)level標簽觀察下這批多次消費用戶的分布情況（二次消費的總?cè)藬?shù)為11662）赶袄，統(tǒng)計結(jié)果如下：
cid表示各個level消費的人數(shù)，rate是指占多次消費人數(shù)的比例
我們將兩張表格結(jié)合起來分析下抠藕，結(jié)果如下：
觀察表格饿肺，我們可以發(fā)現(xiàn)：占比圖的數(shù)值呈一個雙峰的趨勢，消費頻次數(shù)值顯示呈上升趨勢幢痘，且越往后上升越快唬格。消費頻次高的用戶家破，集中在level 5,6兩段颜说，所占比率為30%，這已經(jīng)是屬于忠實的客戶汰聋。level 0,1的用戶雖然平均消費次數(shù)也有兩次及以上门粪，但卻很難持續(xù)，應(yīng)該在消費后這段時間盡量引導烹困，其占比也達到近25%玄妈，這是不容忽視的。中間段則相對來說平穩(wěn)髓梅，無論是從占比還是從消費頻次上來說拟蜻。
總結(jié)：
1枯饿、統(tǒng)計出的未消費的用戶已在內(nèi)容中呈現(xiàn)酝锅，可針對性的制定改善的措施。
2奢方、一次性消費的用戶群體居多占總消費總用戶的一半搔扁。
3、銷量前三個月高漲蟋字，后期下降明顯且呈現(xiàn)相對平穩(wěn)狀態(tài)稿蹲。
4、復購率受一次性客戶影響鹊奖，平均穩(wěn)定在20%左右苛聘；回購率則穩(wěn)定在30%左右。
5忠聚、整個消費狀況呈現(xiàn)二八傾向焰盗，維護好金字塔尖部的客戶至關(guān)重要！
6咒林、用戶生命周期平均在273天熬拒，也收到了極端值的影響。各階段用戶占比呈雙峰趨勢垫竞，隨之生命周期的增長澎粟，平均消費頻次也在提高
