轉載請在文章起始處注明出處，謝謝。
數據來源CDNow網站的用戶購買明細。一共有客戶ID，購買日期，購買數量，購買金額四個字段。原數據是txt的格式，將它改為csv的格式導入進mysql，由于userid有重復，所以不設主鍵，只是用作分析練習。
加載數據：
paidtime表示購買日期，products表示購買數量，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：
觀測下整個數據，先對整個數據按單筆消費金額amount進行降序排序
消費最高的一筆是由userId為8830的用戶產生的，消費了1286.01，隨后單筆消費的金額迅速降低至最高金額的一半。
將數據進行升序的排序：
發(fā)現有些用戶消費為0，且都是在1,2,3月份的。因為一個userId有可能產生多筆消費，所以我們觀察下這些用戶在其他月份有沒有產生消費。
select *
from data.cdnowtest
where id in(select id
from(
select * from data.cdnowtest
order by amount) t
where amount = 0)
觀察表格，我們可以發(fā)現，有些用戶確實一次未消費，有些用戶如：2703，在1月份未消費，但在二月份消費了一次，隨后就再沒消費過，可能是活動促銷等原因造成的一次性消費。
我們根據這張表將一次未消費的用戶過濾出來：
部分篩選結果如上表所示，所以我們可以向這些用戶詢問或者反饋，統計未消費的原因并針對性得找出改善的措施等。
對于整張表，我們大致可將客戶分類為不活躍客戶（消費次數為0），一般價值客戶（消費1次），回頭客（消費2-3次），忠實客戶（消費3次以上），分別計算下它們的占比。
不活躍客戶我們已經由上表得出，總數為68。
計算一般價值客戶數為：
select count(id) as'一般價值客戶'
from(
select id,count(amount) ca
from data.cdnowtest
where amount != 0
group by id
having ca=1) t
同樣，回頭客數量為：
忠實客戶為：
總客戶數為：23570
由上述查詢出的數據可知：
不活躍客戶數最少為68，占比約為0.3%
一般價值客戶數最多為11843，占比約為50.2%
回頭客數為6294，占比約為26.7%
忠實客戶數為5365，占比約為22.8%
接下來我們根據用戶進行分組，分析每個用戶的產品購買數量和購買金額：
select max(sum_p) '最大消費數',
min(sum_p) '最小消費數',
avg(sum_p)'平均消費數',
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張，最大的消費金額達到了近一萬四，應該是屬于忠實粉絲，用戶的平均消費金額（客單價）為106元
接下來按月維度進行分析：
select pmonth,sum(products) sp,round(sum(amount),0) sa
from data.cdnowtest
group by pmonth
根據聚合的結果可知，cd銷量前三個月都非常高漲，后期則下降明顯且呈現相對平穩(wěn)狀態(tài)。
我們再將數據按用戶分組，觀察第一次的消費時間，再按月份進行分組，統計消費次數。
select minm,count(id) '月消費總次數'
from(
select id,min(pmonth) as minm
from data.cdnowtest
group by id) t
group by minm
由查詢表可知，所有用戶的第一次消費都是集中在前三個月的，這也就不難解釋為什么cd前三個月的銷量和銷售額都非常高漲。
接下來分析消費中的復購率和回購率
首先求復購率，復購率的定義是在某時間窗口內消費兩次及以上的用戶在總消費用戶中占比。這里的時間窗口是月，如果一個用戶在同一天下了兩筆訂單，這里也將他算作復購用戶。
分別查詢出各月消費總人數和各月復購的人數。
各月復購人數：
各月消費人數：
將兩表連接，求出復購率：
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
由表可知：看出復購率在早期，因為大量新用戶加入的關系，新客的復購率并不高，最低的一月份只有10%。而在后期，在忠實客戶的影響下，復購率逐漸上升且趨于穩(wěn)定，在20%左右。
接下來計算回購率。回購率是某一個時間窗口內消費的用戶，在下一個時間窗口仍舊消費的占比。
首先先按用戶id和pmonth進行分組，篩選出每個用戶在每個月的消費記錄，再將表進行自連接，產生笛卡爾積效應，篩選出前后相差一個月的記錄：
再根據篩選出的結果按t1的月份進行分組，分別得出當月消費總人數和回購人數，最后求得回購率
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%左右。最后項目數據為0，是由于并沒有接下來7月份的數據，所以無法得出。綜合復購率和回購率兩張表格分析，新用戶不管是從回購率還是復購率看都不及老客戶
接下來分析用戶質量，首先根據用戶分組，計算并按消費額排序出每個用戶的消費總額：
根據消費的二八法則，我們需要知道消費的top20%的客戶貢獻了多少的額度，根據前面的查詢結果，我們知道，此次我們調查的用戶共有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%的用戶貢獻的額度及貢獻率，結果如下：
根據統計的結果，我們可以得出：消費排名前20%的用戶貢獻了近70%的消費總額，消費排名前40%的用戶貢獻了約85%的消費總額，確實呈現了28的傾向，所以維護好這批排名靠前的用戶顯得尤為重要！
接下來簡單計算下用戶生命周期，這里定義第一次消費至最后一次消費為整個用戶生命。
首先根據用戶進行分組，篩選出消費次數大于1的用戶和消費的最早時間和最晚時間，然后計算出相差的天數。
有消費間隔為0的，是指當天消費了兩次或以上，因為我們已經將消費大于1次的過濾了出來。
接著，我們觀察下deltaday的一些相關數據，結果如下：
maxd表示最長用戶的生命周期是544天，
mind表示最短的是0天，
avgd平均生命周期約為273天。
接下來，我們根據用戶的生命周期大致劃分下等級：
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
運行結果如下：
可以看出：leve在0-2之間的也就是生命周期在0-200天的，平均消費頻次在2-3之間，隨著生命周期的增長，消費頻次逐漸變高。level=6的忠實客戶的值最高，平均消費頻次在12次左右。
隨后，我們根據level標簽觀察下這批多次消費用戶的分布情況（二次消費的總人數為11662），統計結果如下：
cid表示各個level消費的人數，rate是指占多次消費人數的比例
我們將兩張表格結合起來分析下，結果如下：
觀察表格，我們可以發(fā)現：占比圖的數值呈一個雙峰的趨勢，消費頻次數值顯示呈上升趨勢，且越往后上升越快。消費頻次高的用戶，集中在level 5,6兩段，所占比率為30%，這已經是屬于忠實的客戶。level 0,1的用戶雖然平均消費次數也有兩次及以上，但卻很難持續(xù)，應該在消費后這段時間盡量引導，其占比也達到近25%，這是不容忽視的。中間段則相對來說平穩(wěn)，無論是從占比還是從消費頻次上來說。
總結：
1、統計出的未消費的用戶已在內容中呈現，可針對性的制定改善的措施。
2、一次性消費的用戶群體居多占總消費總用戶的一半。
3、銷量前三個月高漲，后期下降明顯且呈現相對平穩(wěn)狀態(tài)。
4、復購率受一次性客戶影響，平均穩(wěn)定在20%左右；回購率則穩(wěn)定在30%左右。
5、整個消費狀況呈現二八傾向，維護好金字塔尖部的客戶至關重要！
6、用戶生命周期平均在273天，也收到了極端值的影響。各階段用戶占比呈雙峰趨勢，隨之生命周期的增長，平均消費頻次也在提高
