SQL入門-淘寶電商數(shù)據(jù)報告

1、淘寶用戶行為數(shù)據(jù)分析報告（SQL）目錄一、背景和目的1二、理解數(shù)據(jù)1三、分析思路1四、數(shù)據(jù)淸洗2五、分析過程6六、結(jié)論19一、背景和目的電商行業(yè)如今已經(jīng)走向了精細(xì)化經(jīng)營的一步。從全本的粗放式發(fā)展到今天的使用大數(shù)據(jù) 指導(dǎo)營銷戰(zhàn)略。如今電商甚至?xí)亩嗑S度多方而來分析消費者行為，從而來幫助自己更好地 理解用戶的需求以及尋找目標(biāo)客戶?；谶@個背景下，我們會針對來自阿里巴巴的消費者 行為數(shù)據(jù)，來進(jìn)行數(shù)據(jù)分析。二、理解數(shù)據(jù)數(shù)據(jù)來源于：本數(shù)據(jù)集包含了 2017年口月25日至2017年12月3日之間，有行為的約一百萬隨機 用戶的所有行為（行為包括點擊、購買、加購、喜歡）°數(shù)據(jù)集的組織形式和Movie

2、Lens-20M 類似，即數(shù)據(jù)集的每一行表示一條用戶行為，由用戶ID、商品ID、商品類目ID、行為類型 和時間戳組成，并以逗號分隔。貝中用戶ID、商品ID和品類ID都是整數(shù)類型，而行為類型是字符串，枚舉類型，且 分為pv （點擊商品詳情頁）、buy （商品購買）、cart （是否加入購物車）和fav （是否收藏 商品）整個數(shù)據(jù)集數(shù)量條數(shù)達(dá)到987, 994條，用戶數(shù)量又4,162,024,涵蓋的商品類目數(shù)疑 達(dá)到9.439,所有行為數(shù)量達(dá)到100,150,807.數(shù)據(jù)分析的途徑將是由navicat鏈接mysql，導(dǎo) 入10萬多條數(shù)據(jù)，且導(dǎo)入過程中將class、itemid和timestamps

3、作為了主鍵，來確保導(dǎo)入的 時候不岀現(xiàn)重復(fù)值。三、分析思路基于之前了解到的電商環(huán)境和數(shù)據(jù)集合提供的字段，本次的數(shù)據(jù)分析可以運用多維度, 如產(chǎn)品緯度、用戶緯度和行為緯度者三個緯度來進(jìn)行分析。產(chǎn)品您基于實際數(shù)據(jù)意義，而提出以下問題：淘寶主推產(chǎn)品是否有帶動足夠的銷量？是否有效為店鋪其他產(chǎn)品進(jìn)行了引流？ 哪幾天的銷量最高？什么時間段用戶最活躍？用戶的購買疑是否與時間和促銷活動 直接相關(guān)？淘寶用戶從點擊瀏覽到購買的一系列過程中，哪一步驟是造成轉(zhuǎn)化率低的最重要要 原因針對分析口的，采用多維度拆解分析方法對問題進(jìn)行拆解，并在分析過程中使用 假設(shè)檢驗分析法、AARRR分析法和RFM模型分析方法對用戶使用流程及具

4、體業(yè) 務(wù)指標(biāo)中的問題進(jìn)行分析。四、數(shù)據(jù)清洗數(shù)據(jù)清洗步驟1磐子集s致化處理6數(shù)據(jù)排序7異當(dāng)值處理a. 選擇子集由于數(shù)據(jù)量過大,本篇分析報告只導(dǎo)入10萬條數(shù)據(jù)，導(dǎo)入式將class, itemid, timestamps 設(shè)為主鍵，保證導(dǎo)入時無重復(fù)值。通過navicat將數(shù)據(jù)集導(dǎo)入MySQL,整個報告分析也是居 于Navicat來進(jìn)行。b. 列名重命名由于導(dǎo)入的數(shù)據(jù)沒有列名，因此為各字段分別添加英文列名c. 刪除重復(fù)值將class, itemid, timestamps三個字段設(shè)置為主鍵，保證導(dǎo)入的數(shù)據(jù)沒有重復(fù)值d. 缺失值處理將各字段設(shè)置為“notnull”，確保每個字段中都沒有缺失值，并且利用c

5、ount函數(shù)統(tǒng)汁每一 列有多少行數(shù)據(jù)，各字段數(shù)據(jù)均顯示為10萬個，說明沒有缺失數(shù)據(jù)。SQL代碼和結(jié)果顯示如下：查詢創(chuàng)建工貝查詢編輯器select COUNT (use rid) r COUNT (itemidl r COUNT (cat ego ry id) r COUNT (behav io r) r COUNT (t imes t amp) from UserBehavior;結(jié)果COUNT(userid)COUNT(itemid)COUNT(categor. COUNT(behavior) COUNT(timestamp)104857610485761048576104857610485

6、76e. 異常值處理由于導(dǎo)入數(shù)據(jù)時timestamps字段的數(shù)據(jù)類型選擇為varchar,數(shù)據(jù)出現(xiàn)亂碼，因此利用sql 語句修改這一列為日期。代碼和結(jié)果如下：ALTER TABLE userbehavior ADD date VARCHAR(255);ALTER TABLE userbehavior ADD time VARCHAR(255);UPDATE userbehaviorSET date = FROM_UNIXTIME(timestamp/,%Y-%m-%dl),time = FROM_UNIXTIME(timestamp；%H:%i:%s,);F* I 睦 IMt值處S shop

7、(11)Q I Tn UserBehavior $hop (11)useriditemidcategoryidbehaviortimestampdatetime13050592520771汝151:9119302017-11-2907：32：10113231893524510pv15121494352017-12-0201：30：3511338525149192pv15117732142017-11-2717：00：14113409224690421pv15120412602017-11-3019：27：40116310362920476PV15117337322017-11-2706：02：

8、12120284344801426pv15122242482017-12-0222:17:28120410564801426pv15121875432017-12-0212：05：43120873572131531pv15119751422017-11-3001：05：42120873572131531pv15120046682017-11-3009：16：08121044834756105pv15121948302017-12-0214：07：10122665674145813PV15117414712017-11-2708：11：11122683182520377p/p>

9、017-11-2501：21：10122786033002561pv15122515222017-12-0305：52：02122865742465336pv15117971672017-11-2723：39：271230380411153pv15116449422017-11-2605：22：22123333462520771pv15116617332017-11-2506：15：3312576651149192DV15115728852017-11-2509：21：25檢查毎一天數(shù)據(jù)是否都發(fā)生在2017.11.25至2018.05.11之間，查詢導(dǎo)入數(shù)據(jù)日期的最大值 和最小值，發(fā)現(xiàn)最小值早

10、于日期范帀的最小值select min (date) fmax(date) from userbehavior;2017-09-112018-08-28查詢出不符合要求的記錄共有60條，并將其刪除。刪除后，再次驗證日期，最 后的結(jié)果才算符合要求。1 #刪除小于2017-11-25#2 select *from userbehavior4 where date<f2017-11-25' delete from userbehavior6 where date<*2017-11-251; select min(date)rmax(date) from userbehavior;

11、9#刪除大于2018-05-1110 select * from userbehavior12 where date>*2018-05-111; delete from userbehavior14 where dato12018-05-111; select min(date)Fmax(date) from userbehavior;25：13min(date)max(date)2017-11-252017-12-04五、分析過程首先我們通過研究用戶的行為轉(zhuǎn)化情況來看。用戶行為我們可以看到，該店鋪的用戸點擊次數(shù)遠(yuǎn)高于加入購物車和購買次數(shù)，至少說明了該店 鋪的轉(zhuǎn)化率非常的低，從點擊到最后

12、購買只有2%.而要回答這一個問題，就可以從以下兩個假設(shè)著手，同時我們也可以運用邏輯樹、對比分析 法和AARRR方法來逐一分析。假設(shè)一：淘寶主推產(chǎn)品未推動用戶購買1首先先從數(shù)據(jù)庫中調(diào)取用戶點擊的產(chǎn)品數(shù)據(jù)2.再調(diào)取出用戶購買的產(chǎn)品list代碼如下：#用戶點擊前10類別#select categoryid, count(categoryid) as 用戶點擊 from UserBehaviorwhere behavior = 'pv*group by categoryid order by用戸點擊desc limit 10;#用戶購買前10名類別#select categoryid, cou

13、nt(categoryid) as 用戶購買 from UserBehaviorwhere behavior = 'buy*group by category idorder by用戶購買desc limit 10;具體分析：1.1點擊量高的商品類別并不能給店鋪帶來直觀的效益首先從觀察店鋪內(nèi)點擊量為店鋪前20需的商品類別中，我們可以看到整體店鋪的點擊 量非?？捎^，各個類別的點擊數(shù)量也都相對均衡，英中"4746105的產(chǎn)品類別最受歡迎。用戶點擊量前10而當(dāng)我們看到店鋪中銷量前10名的柱形圖時，發(fā)現(xiàn)整體的購買總量僅僅是百位數(shù)，且 購買的產(chǎn)品類別也與點擊量不一致，且并不能成為正比。

14、初步看來，兩邊表格展現(xiàn)出來的產(chǎn) 品類別有個別一致。用戶購買量前20通過2張柱形圖并不能直觀得看出來，我們通過篩選兩表之間相同的產(chǎn)品類別，并制作 兩分圖來展現(xiàn)。如下圖所示，其中點擊量高的并不能帶來直觀的購買疑，也就是說大部分點 擊進(jìn)來的用戶，最后也都沒有購買那個吸引他點擊進(jìn)入的店鋪的產(chǎn)品類別。于此，我們可以 基本得出一個結(jié)論，說明店鋪最吸引的用戶的產(chǎn)品類別并沒有給店鋪帶來成正比的購買量。用戶購買戢 用丿一點擊量184 hhhhhhhhhhhhhHI 3002501 295273206914552196801.2低點擊轉(zhuǎn)化和低匝復(fù)購買率造成購買量和點擊量不成正比雖然我們直接能從二分圖得出結(jié)論，看得

15、出來，點擊量沒有給店鋪帶來直觀的效益。但 是為了更深層次得挖掘到具體的商品，找到那些受關(guān)注髙但是低購買率的商品，才算是找到 了低轉(zhuǎn)化率的源頭。所以，我們通過SQL語句將用戶點擊和用戶購買前3名的前3名查找出來，并一對一 進(jìn)行對比。代碼如下：#用戶點擊第1名商品#select itemid,count(itemid) as 點擊次數(shù)from userbehaviorwhere behavior='pv, and categoryid = 4756105group by itemidorder by點擊次數(shù)desc;#用戶點擊第2名商品#select itemid,count(itemid

16、) as 點擊次數(shù)from userbehaviorwhere behavior='pv, and categoryid = 2355072group by itemidorder by點擊次數(shù)desc;#用戶點擊第3需商品#select itemid,count(itemid) as 點擊次數(shù)from userbehaviorwhere behavior='pv, and categoryid = 4145813group by itemidorder by點擊次數(shù)desc;#用戶購買第1名商品#select itemid,count(itemid) as 購買次數(shù)from

17、userbehaviorwhere behavior='buy, and categoryid = 2735466group by itemidorder by購買次數(shù)desc;#用戶購買第2名商品#select itemid,count(itemid) as 購買次數(shù)from userbehaviorwhere behavior='buy, and categoryid = 1464116group by itemidorder by購買次數(shù)desc;#用戶購買第3冬商品#select itemid,count(itemid) as 購買次數(shù)from userbehavior

18、where behavior='buy, and categoryid = 4145813group by itemidorder by購買次數(shù)desc;在excel中通過vlookup對所有査找到的數(shù)據(jù)進(jìn)行對比，比方說，在所有點擊疑第一的 產(chǎn)品類別“4756105”中，我們找到這個類別下所有被點擊了和被購買的商品，并且從被購買 的商品一一查找這些商品的點擊量。通過一一對比，我們發(fā)現(xiàn)，只有點擊量前兩名的商品，都沒有購買量。只有產(chǎn)品類別為 "4145813"的名下有10,468個商品被點擊，其中有300個被購買，且購買數(shù)雖:為332件，點 擊到購買的轉(zhuǎn)化率為3%。這3

19、00個商品的總點擊疑是3,262,占總點擊量的10%。換句話說，就是說該店鋪點擊量前三的產(chǎn)品類別下，只有這第三劣的產(chǎn)品類別中，3% 的用戶在點擊后進(jìn)行了購買，店鋪點擊的轉(zhuǎn)化率為3%。另外我們再深入進(jìn)行挖掘，刨去購買數(shù)量為1的商品，并研究購買量超過2的用戶id 和時間。代碼如下：select classjtemidateountfitemid) as 商品購買數(shù)量from userbehaviorwherebehavior='buy,anditemidin(4438708,3582107,3233904,322)563032,2376866,5019212,3761497,4283735

20、,4536262,374294 3,2732505,3114069,3756418,784500,232919,654637,4691387,4826279,3617007,4101922,1823728,2130461,1040482)group by itemidlassate order by商品購買數(shù)量desc; 以重復(fù)購買率和死忠客戶可謂說是幾乎沒有。I 對険 ：二 UserBehavior shop (11)Oj 富：復(fù)購買 shop(11)直詢創(chuàng)建工貝直詢編輯器select userid,itemid,date,count(itemid) as 商品購買 from userbeh

21、aviorwhere behavior1 buy1 and itemid in (4438768,35821073233904322,1563032,2376866,50192123761497,4283735! group by itemid, use rid, dateorder by商品購買數(shù) desc;useriditemiddate商品購買數(shù)11533748262792017-12-01211362746913872017-12-0321074072329192017-12-01210278850192122017-11-302101047815630322017 1*1272100

22、73353222017-12-012mn-yn-yoon A GT134：5最后看到有24款商品有銷量超過1件，但僅有這6款商品被6位用戶購買了 2件。所通過以上的分析我們可以看岀，該店鋪的轉(zhuǎn)化率低的原因是店鋪主推的商品無法直接帶 動用戶的購買。只有3%的用戶通過點擊并進(jìn)行了購買，其余大部分的用戶都是通過店鋪主 推產(chǎn)品吸引并購買了其他產(chǎn)品。苴次，哪怕是用戸點擊進(jìn)行購買的時候，購買的數(shù)疑也非常 之少，且沒有重復(fù)購買?？梢哉f商鋪展現(xiàn)的主推商品和維護客戶的機制都有問題，導(dǎo)致哪怕 產(chǎn)品吸引了消費者進(jìn)入了店鋪，購買了非主推產(chǎn)品，但是購買力也非常貧乏。假設(shè)二：用戶收藏到最后購買的轉(zhuǎn)化率低根據(jù)用戶購物的行為

23、習(xí)慣，用戶購買流程通常有以下幾種： 點擊T購買 點擊T加入購物車T購買 點擊T收藏T購買 點擊T收藏T加入購物車T購買通過假設(shè)一我們已經(jīng)了解到點擊是無法對宜接購買產(chǎn)生實際影響，也就是說淘寶的推送 機制不完善。然而到了用戶緯度，我們同樣也需要研究各個業(yè)務(wù)流程的流失率，來看到是否 店鋪維系客戶機制是否有改善的可能。查詢創(chuàng)猛工貝查詢編輯器假設(shè)二中通過對所有行為賦值并分組，運用AARRR模型、樹形圖和漏斗圖來可視化所 有數(shù)據(jù)。首先先進(jìn)行對每類用戶行為的查找和統(tǒng)計，并創(chuàng)建視圖：1234567select userid,ltemid,100038723820010003877041100038992951

24、000406413510004088601000418548210004236281100042401341000440266510004417931然后根據(jù)用戶行為層層遞進(jìn)去査找: A.總點擊量sumfcase when behaviors'pv忠失 693443/93% then 1 else 0 end) as 'pv*, sum(cd$e when bchavior='cart * then 1 else :' end) d$ *cart sumfcase when behavior*fav* then 1 else 0 end) as 'fav

25、', sumfcase when behavior=*buy* then 1 else 0 end) as *buy' from userbehaviorj group by userid,itemid;旦判select sum(pv) as 總點擊屋 from process;1：1總點擊量939537B.根拯AARRR模型和樹形邏輯來查找各個階段的數(shù)據(jù):査詢創(chuàng)建工具查詢編輯器G 二無標(biāo)H shop (11)對 ft N kviorsho. process shop (11) I 乜用戶行為邏輯樹 shfrom processwhere pv>0 and cart=0

26、and fav=0 and buy=0; select count(userid) as '點擊后未流失 from processwhere pv>o and cart>0 or fav>fl or buy>0; select count (userid) as 點擊后購買' from processwhere pv>；* and buy>0 and fav=0 and cart=0; select count(userid) as 點擊后收ST from process12 where pv>0 and fav>0;select

27、 count(userid) as '點擊后加購物車.14 from processwhere pv>0 and cart>0 and fav=0;|161733： 15信息結(jié)果1結(jié)果2 結(jié)果3 結(jié)果4 結(jié)果5點擊后流失693443幀 9W132%BQ 實 138/0.02* tt入跡方 833/0.11%未ifi矢 48923/7%總點擊量742366/100%一東軒 695/0.00% iftS 12180/1.64%克E人韌事炙酥 104KJ/1.4O%277V037%點擊:10i點擊后收藏/加入購物車/收就又加入購物車：6.59財買：0.37最后總結(jié)出以上的樹形邏輯

28、圖和pipeline.我們可以看到整個業(yè)務(wù)流程中：2大部分用戶（93%）首先就在點擊之后流失了，最后也僅有1.32%的購買率，可見直接流 失率非常高。2.剩下的7%點擊進(jìn)來的用戶，對產(chǎn)品進(jìn)行了收藏、加入購物車等行為，到最后購買的轉(zhuǎn)化 率也遠(yuǎn)遠(yuǎn)比加入購物車或者收藏的轉(zhuǎn)化率要低得多。從AARRR漏斗模型庫可以看到，從用 戶點擊到用戶收藏/加入購物車等行為就已經(jīng)丟失了 93%3加入購物車的用戶，遠(yuǎn)比直接購買和收藏的比例要多?？偨Y(jié)可以得出以下結(jié)論： 淘寶推送存在漏洞 用戶仔細(xì)瀏覽了產(chǎn)品.進(jìn)行收藏和加入購物車后，足以說明用戸是已經(jīng)考慮了購買，店 鋪的產(chǎn)品或者運營機制都沒有刺激到用戶的最終購買。-1234

29、567890結(jié)杲1datec(njnt(behavior)2017-11-30994102017-12-011026132017-12-021294742017-12-031288102017-12-041300025002000150010005000假設(shè)三：用戶活躍度與時間和促銷活動相關(guān) 首先查找點擊一年內(nèi)12個月的點擊和購買數(shù)量里閔助連丄貝 select date,count(behavior) from userbehavior where behavior=*pv' group by date order by date asc;select date.count(behav

30、ior) from userbehavior where behavior=*buy' group by date order by date asc;19：1O通過以上圖表發(fā)現(xiàn)，用戶日活在12/02和12/03這兩天達(dá)到峰值，而12/02和12/03兩 天是周末。主要原因是在周末用戶通常都擁有更多的空閑時間，因此周末的日活顯著高于工 作日的日活。說明用戶會選擇相對悠閑的周末進(jìn)行購物。而通過RFM模型，幫助我們根據(jù)用戶購買時間和頻率，來泄義用戶的層次，幫助店鋪 更好地左位到重要客戶。根據(jù)之前的數(shù)據(jù)觀察和分析我們對RFM的各個指標(biāo)做了以下左 義：R：根據(jù)用戶最近一次的購買時間到2017年

31、12月3日之間的差值，來判斷用戶最近一 次消費時間間隔F：將數(shù)據(jù)集中用戶在2017年口月25日至2017年12月3日這九天時間內(nèi)購買的次 數(shù)作為用戶消費的頻率M：數(shù)據(jù)集未提供金額，但是可以通過購買數(shù)量來代替具體金額。1 首先先分別創(chuàng)建RFM的統(tǒng)汁表對魚 UserBehavior shop (11)Q create RFMJable shop (11)查詢倉|：REATE VIEW R_table(useridrdeltatime)2 ASSELECT useridfdatediff(,2017-12-4'rdate) AS deltatime4 FROM (SELECT userid,

32、date,row_number() over ( PARTITION BY userid order by date DESC )AS datel FROM userbehaviorWHERE behavior = 'buy'9 )AS tempWHERE datel = 1;1112 CREATE VIEW F_table(userid,frequency)13 ASSELECT useridrcount(distinet date) AS frequency FROM userbehaviorWHERE behavior = 'buy*17 GROUP BY use

33、rid;CREATE VIEW M_table(userid,quantity)ASSELECT useridfcount(date) AS quantity FROM userbehavior22 WHERE behavior = 'buy'23 GROUP BY userid;2.對RFM三個指標(biāo)制泄評分表，并逐一打分按照價值打分最近一次消費間隔消費頻率消費金額/數(shù)量178天2次以內(nèi)3以內(nèi)25-6天2-3次37個334天34次7-11 個42天之內(nèi)5次以上H個以上1* * Uscrftohavior Qtshop (11 >童詢創(chuàng)azam伽1CREATE VIEW r

34、jx)ints1 2I 35LECT useria,c«Uatlwef1 4(cue1 5v«*ien deltatine >= then 11 6uhpn deltatine x arri dcltat w<s t： then /7ven deltatine >= and deTtatirwe<="then 31 8deltotine >=】ond dcltatinc2 then 41 9eodiIAS r_polntitFROM r_table;125LECT avQ(r_polnt>nFROM rpoints：1 14J

35、15CREATE VIEW f.points1 16AS1 HSELECT userid,frequency.I】8(casei】9v»hen frequency < 2 then 'Sen fregency >= ? and frequency< 3 then 21 21*Oen frequency >= and frcquency< 4 then 3I 22Sen frequency >= : then 5 else 9 endI 23IAS f.point1 24FROM f.table;I 25SELECT avg(f_polnt&

36、gt;I 26FROM f.points；I 27CREATE VIEW n_polnts1 28ASI 295ieCT userid.Qudntity,io$2_KLU«« 3av«(r_port)2 W«93根據(jù)R、F. M打分和平均值的比較，對用戶進(jìn)行分類評價UserBehavior shop (11) | 物無標(biāo)IH shop (11)Q 1create RFM.classify shop (11)亠査詢創(chuàng)建工耳僵曲1CREATE VIEW .classify2AS3SELECT userid,deltatlne.4(case wtien del

37、tatime > 29969 then 禹'else 低'end)AS class5FROM rpoints;6CREATE VIEW 仁classify7AS8SELECT userid,frequency.9(case wtien frequency > 2.0267 then 'J85' else 低'end)AS class10FROM f.points;11CREATE VIEW m_cldssify12AS13SELECT userid,quantity.14(case wtien quantity > 1.5626 the

38、n 高 else 低'end)AS class15FROM «_points;4根據(jù)用戶的分類原則對用戸進(jìn)行分類，并進(jìn)行統(tǒng)計用戶分類規(guī)則用戶分類最近一次消費 時何間隔(R)消費頻率(F)消費金額 (M)1重要價值用戶高高高2.重要發(fā)展用戶低高3.垂要保持用戶低離高 J4.重要挽留用戶低低高5.般價值用戶高低6.般發(fā)展用戶高低低7.般保持用戶低高低8般挽留用戶低低低査詢編輯查詢創(chuàng)建工具Create view user.ciass2 ASSELECT auserid,ar_class.bf_class.cm_c:lass, (CASE5WHENarclass高'andb.fclassandcmclassthen'朿箋價值客戶'6WHENarclass=andb.fclass