案例分析：基于MySQL的電商用戶，商品，平臺價(jià)值分析

1 項(xiàng)目背景

隨著電商?業(yè)近?年的迅猛發(fā)展，電?商務(wù)從早些年的粗放式經(jīng)營，逐步轉(zhuǎn)化為精細(xì)化運(yùn)營。隨著平臺數(shù)據(jù)量的不斷積累，通過數(shù)據(jù)分析挖掘消費(fèi)者的潛在需求，消費(fèi)偏好成為平臺運(yùn)營過程中的重要環(huán)節(jié)。本項(xiàng)?基于某電商平臺?戶?為數(shù)據(jù)，在MySQL關(guān)系型數(shù)據(jù)庫，探索?戶?為規(guī)律，尋找?價(jià)值?戶；分析商品特征，尋找?貢獻(xiàn)商品；分析產(chǎn)品功能，優(yōu)化產(chǎn)品路徑

==這里其實(shí)目的就三個(gè)，找高價(jià)值用戶，高貢獻(xiàn)商品，優(yōu)化產(chǎn)品路徑（平臺）

1.1 分析流程

2.使用“人貨場”拆解方式建立指標(biāo)體系

最終結(jié)果：評價(jià)‘’?戶，商品，平臺“三者質(zhì)量，這一步必須提前進(jìn)行。

人貨場：

「?」（?戶) 是整個(gè)運(yùn)營的核?。所有舉動(dòng)都圍繞著，如何讓更多的?有購買?為，讓他們買的更多，買的更貴。所以對?的洞察是?切?為的基礎(chǔ)。?前平臺上的主?消費(fèi)?群有哪些特征，他們對貨品有哪些需求， 他們活躍在哪些場，還有哪些有消費(fèi)?的??前不在平臺上，對這些問題的回答指向了接下來的?動(dòng)。

「貨」就對應(yīng)供給,涉及到了貨品分層，哪些是紅海（銷量高利潤少競爭多），哪些是藍(lán)海（銷量少利潤高競爭少），如何進(jìn)?動(dòng)態(tài)調(diào)整，是要做?營還是平臺，以滿?消費(fèi)者的需求。

「場」就是消費(fèi)者在什么場景下，以什么樣的?式接觸到了這個(gè)商品。早期的導(dǎo)購做的?較簡單，?前的場就?較豐富，但也暴露了淘寶和京東在導(dǎo)購??的?些問題。?如內(nèi)容營銷，?前最好的可能是微信的 KOL ?態(tài)和?紅書，甚?微博，?不在電商??的場。如何做?個(gè)全域的打通，和消費(fèi)者進(jìn)?多觸點(diǎn)的接觸，?如社交和電商聯(lián)動(dòng)，來完成銷售轉(zhuǎn)化，這就是騰訊和阿??直都在講的「全域營銷」。

3.確認(rèn)問題

本次分析的?的是想通過對?戶?為數(shù)據(jù)進(jìn)?分析，為以下問題提供解釋和改進(jìn)建議：

1）基于漏?模型的?戶購買流程各環(huán)節(jié)分析指標(biāo)，確定各個(gè)環(huán)節(jié)的轉(zhuǎn)換率，便于找到需要改進(jìn)的環(huán)節(jié)；

2）商品分析：找出熱銷商品，研究熱銷商品特點(diǎn)；

3）基于RFM模型找出核?付費(fèi)?戶群，對這部分?戶進(jìn)?精準(zhǔn)營銷。

==首先還是漏斗來看看各個(gè)環(huán)節(jié)的轉(zhuǎn)化率

4.準(zhǔn)備工作

4.1 數(shù)據(jù)讀?。ㄓ脩粜袨閿?shù)據(jù)）

表結(jié)構(gòu)

注意這個(gè)behavior_type，這個(gè)是數(shù)字，看一下數(shù)據(jù)，留意一下這個(gè)時(shí)間還有個(gè)小時(shí)，并且是24進(jìn)制的。

4.2 數(shù)據(jù)預(yù)處理

增加新列date_time（datetime）,dates（char，年??），便于后續(xù)時(shí)間維度分析；

==這是上面的一個(gè)小細(xì)節(jié)，上面的時(shí)間數(shù)據(jù)用的是varchar格式，其實(shí)并不好后續(xù)的計(jì)算，這里改成date格式

==上面說了這里加入兩個(gè)新列，這個(gè)是第一個(gè)列，但是最后依據(jù)的str_to_date函數(shù)從沒見過，作用是將上面的str的時(shí)間轉(zhuǎn)為date的時(shí)間。

==str_to_date這個(gè)函數(shù)可以將字符串轉(zhuǎn)為時(shí)間格式，所以其內(nèi)部第一個(gè)參數(shù)填寫time，這個(gè)time是原數(shù)據(jù)中的一列，包含了年月日和小時(shí)，而其第二個(gè)參數(shù)就是time列數(shù)據(jù)的格式。注意要有單引號。

然后就是時(shí)間的進(jìn)制問題了，有12進(jìn)制和24進(jìn)制，%H表示24進(jìn)制，%h表示12進(jìn)制。

==后面的兩段是用來查看數(shù)據(jù)的，在navicat里面直接右擊選擇設(shè)計(jì)表即可直接查看表的格式。

重復(fù)值處理：創(chuàng)建新表a，并插?5W條?重復(fù)數(shù)據(jù)

==第一行中的like是復(fù)制表的結(jié)構(gòu)

==第二行使用select distinct和limit來實(shí)現(xiàn)50000無重復(fù)的要求

5，指標(biāo)體系構(gòu)建

人貨場體系

5.1?戶指標(biāo)體系

基礎(chǔ)指標(biāo)體系（UV/PV/留存率）+ RFM模型分析

5.1.1 基礎(chǔ)指標(biāo)

uv、pv、留存率（按?）統(tǒng)計(jì)

關(guān)于pv的計(jì)算要用的是behavior_type這一列，這是用戶行為列，=1是曝光的意思，滿足pv的計(jì)算意義；然后uv就是用戶id數(shù)量。注意兩者都是按天來分組計(jì)算。

==注意第一個(gè)問題，這里按天來計(jì)算其實(shí)不用day，dates這一新建列已經(jīng)是精確到天的日期了；第二個(gè)問題，這里沒有使用where來進(jìn)行條件判斷，因?yàn)檫@里的uv和pv計(jì)算的條件其實(shí)是不同的，這里直接將條件寫成if放到select的聚合函數(shù)里面了，這種寫法也是我首次看見。

==這一步在實(shí)際操作時(shí)還遇到了以下問題：1 沒有使用if而是使用where來判斷，結(jié)果出錯(cuò)；2 select的幾個(gè)對象間沒有使用逗號，結(jié)果出錯(cuò)；3 if 后面還需要有一個(gè)括號。

注意你select的順序就是你結(jié)果展示時(shí)列的順序。

這里我們先解釋一下留存率是什么意思，下面是我們想展示的界面：

? ? ? ? ? ? ? ? ? ? ? ? 活躍人數(shù)? ? ? ? ? ? 過了1天? ? ? ? ? ? 過了2天

2019-12-25? ? ? ? ? 100? ? ? ? ? ? ? ?90(90%)? ? ? ? ? ?80(80%)

1)我們需要獲得類似上面的一個(gè)結(jié)果集

2)基礎(chǔ)數(shù)據(jù)中的所有日都應(yīng)該進(jìn)行如上運(yùn)算

計(jì)算依據(jù)如下：

? ? ? ? ? ? ? ? ? ? dates? ? ? ? dates_1

xxx用戶 2019-12-28? 2019-12-30 （我們看這個(gè)用戶的下一個(gè)有記錄的日期，如果時(shí)30號有，就是第二天活躍）

xxx用戶 2019-12-28? (dates_1-dates)相差幾天? ? ?若為1：就在過了1天的統(tǒng)計(jì)中+1，用count計(jì)數(shù)

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?若為2：就在過了2天的統(tǒng)計(jì)中+1，用count計(jì)數(shù)

上面的第二段代碼就是為了實(shí)現(xiàn)我們目標(biāo)格式所作的自關(guān)聯(lián)，就是將自己和自己兩個(gè)表合到一起，為了保證第二個(gè)日期比第一個(gè)日期要晚，所有有個(gè)where的條件篩選。

注意distinct雖然也是函數(shù)，但是它不需要括號

然后我們利用視圖或許可以更好的查看數(shù)據(jù)，利用視圖替代子查詢

在代碼的最前方直接加入create??view user_remain as即可創(chuàng)建視圖，然后利用select * from user_remain來直接調(diào)用。

因?yàn)槲覀兦懊娴挠?jì)算是留存數(shù)的計(jì)算不是留存率，這里我們還需要進(jìn)一步操作，如果簡單的將兩列相乘是無法得到一個(gè)百分比數(shù)值的，這里需要使用concat來連接%，并且使用cast轉(zhuǎn)換函數(shù)和decimal來控制小數(shù)點(diǎn)位數(shù)。

5.1.2 RFM模型分析

1.RFM模型：R部分（最近消費(fèi)）

這里建了一個(gè)視圖來分析，其實(shí)看的列還是用戶的id列和最近購買時(shí)間，這里用max來判斷最近一次購買，這里寫的不難，但是一些條件和排序容易被忘記。

創(chuàng)建視圖其作用其實(shí)有些類似子查詢，不過更方便。

第一對比兩個(gè)視圖的制作，理解了其制作流程，在新建視圖之前，要確保其同名視圖不存在，然后用as接上來新建；

第二對于時(shí)間差的計(jì)算，還是datediff()函數(shù)，它的作用就是計(jì)算其內(nèi)部兩個(gè)參數(shù)的差；

第三，類似循環(huán)語句的一個(gè)函數(shù)，使用方法是case when then else end，這個(gè)方法非常類似循環(huán)。

2. RFM模型：F部分（?定時(shí)間內(nèi)的消費(fèi)頻率）

第一，視圖之間是可以不用子查詢直接引用的；第二上述沒有給定時(shí)間，先建立F視圖，確定用戶id列和購買次數(shù)列，再建立等級劃分圖。

3. 整合結(jié)果

本次數(shù)據(jù)中通過最近消費(fèi)（R）和消費(fèi)頻率（F）建?RFM模型，M是消費(fèi)金額，這里沒有給出故不作分析。

我們將按最近?次消費(fèi)的均值和消費(fèi)頻率的均值定?低界限。

這里先找出兩個(gè)均值，這兩個(gè)均值就是后面用來界定的依據(jù)，這里r_value和f_value的值是在上表里面計(jì)算出來的，然后后面常規(guī)進(jìn)行即可。

5.2商品指標(biāo)體系

商品這里是從兩個(gè)維度進(jìn)行分析的，分別是商品維度和商品分類維度

商品的點(diǎn)擊量 收藏量 加購量 購買次數(shù) 購買轉(zhuǎn)化（該商品的所有?戶中有購買轉(zhuǎn)化的?戶?；）

按照商品進(jìn)?分組統(tǒng)計(jì)

首先分別理解它上面給出的5個(gè)定義的含義和計(jì)算方式，其實(shí)前面四個(gè)就是用戶行為中的4個(gè)選項(xiàng)，而第五個(gè)購買轉(zhuǎn)化就是購買的人數(shù)比上所有人數(shù)。

在這里前面說的case函數(shù)還是挺常用的。

對應(yīng)品類的點(diǎn)擊量 收藏量 加購量 購買次數(shù) 購買轉(zhuǎn)化（該商品品類的所有?戶中有購買轉(zhuǎn)化的?戶?；）

這兩個(gè)維度的計(jì)算是非常類似的。

5.3 平臺指標(biāo)體系

從平臺的角度來看用戶行為，這里也分成了兩個(gè)維度

1.?為指標(biāo)：

點(diǎn)擊次數(shù) 收藏次數(shù) 加購物?次數(shù) 購買次數(shù) 購買轉(zhuǎn)化（該平臺當(dāng)?的所有?戶中有購買轉(zhuǎn)化的?戶?）

這里直接按dates來分組，默認(rèn)是每一天，另外case用在sum后else可以為0，但用在count后就必須是null。

2. 行為路徑分析

已購買為基準(zhǔn)，往前推4個(gè)行為節(jié)點(diǎn)，后面的user_count是指多少用戶。

這一步是用來分析行為路徑的，所以lag中的參數(shù)填寫為behavior_type，然后按用戶和物品id來分類（這里的partition中的參數(shù)有兩個(gè)，是同時(shí)按兩個(gè)分組）

這里是需要使用到偏移分析函數(shù)lag()over()，lead()over()，其實(shí)就是窗口函數(shù)

因?yàn)榇翱诤瘮?shù)是每個(gè)窗口都計(jì)算，我們只希望最后一個(gè)是2（購買），所以在最后還加入了一個(gè)rank字段，并且將前面的計(jì)算變成子查詢。

然后將四個(gè)偏移連接起來，為了防止一些值為null，故使用了ifnull這個(gè)函數(shù)，其內(nèi)部有兩個(gè)參數(shù)，第二個(gè)就是值為空時(shí)取的值。這里使用concat進(jìn)行連接。

6 結(jié)論

6.1 ?戶分析

UV異常分析：每?UV數(shù)據(jù)中，明顯異常點(diǎn)為雙??活動(dòng)造成，該影響為已知影響。

對于UV周環(huán)?的分析：?常周環(huán)?數(shù)據(jù)?多?于0，說明?戶程?定上升趨勢，其中如11?26?、12?2?、12?7?等的數(shù)據(jù)為下降數(shù)據(jù)，需要結(jié)合其他數(shù)據(jù)做進(jìn)?步的下降原因分析。雙??活動(dòng)后?戶周環(huán)?會相應(yīng)下降，為正常原因。

猜測可能的問題有：

內(nèi)部問題：產(chǎn)品BUG（?站bug）、策略問題（周年慶活動(dòng)結(jié)束了）、營銷問題（代??換了）等；

外部問題：競品活動(dòng)問題（其他平臺?酬賓），政治環(huán)境問題（進(jìn)?商品限制），輿情?碑問題（平臺商品爆出質(zhì)量問題）等；

6.2 ?戶精細(xì)化運(yùn)營

通過RFM模型中的?戶最近?次購買時(shí)間、?戶消費(fèi)頻次分析，分拆得到以下重要?戶。

可以在后續(xù)精細(xì)化運(yùn)營場景中直接使?細(xì)分?戶，做差異化運(yùn)營：

對?價(jià)值客戶做VIP服務(wù)設(shè)計(jì)，增加?戶粘性同時(shí)通過設(shè)計(jì)優(yōu)惠券提升客戶消費(fèi)；

對深耕客戶做?告、推送刺激，提升消費(fèi)頻次；

對挽留客戶做優(yōu)惠券、簽到送禮策略，增加挽留?戶粘性；

對喚回客戶做定向?告、短信召回策略，嘗試召回?戶。

6.3 商品分析

熱銷商品品類如下所示。

其中’5027‘、’5399‘品類購買轉(zhuǎn)化率較其余商品品類偏低，需要結(jié)合更多數(shù)據(jù)做進(jìn)?步解讀。（可能的原因：品類?有特性導(dǎo)致?戶購買較低，?如?必需品、奢侈品等等。）

6.4 產(chǎn)品功能路徑分析

以下為主要購買路徑?？梢园l(fā)現(xiàn)?戶多以直接購買為主；添加購物?的購買在主要購買路徑中數(shù)量較少。后續(xù)的產(chǎn)品加購功能和產(chǎn)品收藏功能還需要結(jié)合更多數(shù)據(jù)做改進(jìn)?案。