Excel、SQL、Python做數(shù)據(jù)分析有何不同？

作者簡(jiǎn)介
HeoiJin：立志透過(guò)數(shù)據(jù)看清世界的產(chǎn)品策劃，專(zhuān)注爬蟲(chóng)、數(shù)據(jù)分析、產(chǎn)品策劃領(lǐng)域。

萬(wàn)物皆營(yíng)銷(xiāo) | 資本永不眠 | 數(shù)據(jù)恒真理
CSDN：https://me.csdn.net/weixin_40679090

一、前言

后互聯(lián)網(wǎng)時(shí)代，獲客拉新的成本越來(lái)越高，如何增加客戶的留存，提高客戶的復(fù)購(gòu)次數(shù)、購(gòu)買(mǎi)金額等變得十分重要，同期群分析便是當(dāng)中非常重要的分析方法。

關(guān)于同期群分析概念和思路的文章很多，但分享如何實(shí)現(xiàn)的文章非常罕見(jiàn)。因此，本文將簡(jiǎn)單介紹同期群分析的概念，并用數(shù)據(jù)分析師的三板斧ESP（Excel、MySQL、Python）分別實(shí)現(xiàn)同期群分析。

二、項(xiàng)目準(zhǔn)備

• Excel：
• office或wps均可，office 2013后的版本更好
• MySQL：
• 版本：8.0（本次不涉及窗口函數(shù)，其他版本亦可）
• Navicat
• Python：
• 版本：3.7
• IDE：pycharm
• 庫(kù)：pandas、xlrt

PS.

• 因篇幅原因，可能會(huì)有未能詳細(xì)講解的過(guò)程
• 完整源碼及數(shù)據(jù)集請(qǐng)移步至文末獲取

三、同期群分析概念講解

數(shù)據(jù)分析最終目標(biāo)都是為了解決業(yè)務(wù)問(wèn)題，任何分析方法都只是工具。因此在詳細(xì)講解如何實(shí)現(xiàn)之前，需要先明晰方法的含義是什么，能帶來(lái)什么收益，才能在合適的問(wèn)題上選對(duì)分析方法。

3.1 同期群分析含義

同期群(Cohort)即相同時(shí)間內(nèi)具有相似或特定屬性 、行為的群體。核心要素為時(shí)間+特定屬性，比如把00后出生的人劃分為一個(gè)群組。

同期群分析指將用戶進(jìn)行同期群劃分后，對(duì)比不同同期群用戶的相同指標(biāo)。我們耳熟能詳?shù)牧舸媛示褪峭谌悍治龅钠渲幸环N，案例如下圖：

同期群分析包含了3個(gè)重要元素：

1. 客戶首次行為時(shí)間，這是我們劃分同期群的依據(jù)
2. 時(shí)間維度，即上圖中+N月或者N日留存率中的N日
3. 指標(biāo)，注冊(cè)轉(zhuǎn)化率、付款轉(zhuǎn)化率、留存率等等

3.2 意義

同期群分析給到更加細(xì)致的衡量指標(biāo)，幫助我們實(shí)時(shí)監(jiān)控真實(shí)的用戶行為、衡量用戶價(jià)值，并為營(yíng)銷(xiāo)方案的優(yōu)化和改進(jìn)提供支撐：

• 橫向比較：觀察同一同期群在不同生命周期下的行為變化，推測(cè)相似群體的行為隨時(shí)間的變化

• 縱向比較：觀察不同的同期群在同一個(gè)生命周期下的行為變化，驗(yàn)證業(yè)務(wù)行為是否取得預(yù)期效果

四、材料梳理

4.1 數(shù)據(jù)情況梳理

拿到數(shù)據(jù)的第一步，自然是了解數(shù)據(jù)的情況。針對(duì)本次同期群分析，我們可能需要用到的字段有：

• 客戶昵稱
• 付款時(shí)間：時(shí)間戳形式
• 訂單狀態(tài)：交易失敗/交易成功
• 支付金額
• 購(gòu)買(mǎi)數(shù)量

通過(guò)進(jìn)一步計(jì)算，發(fā)現(xiàn)付款時(shí)間中缺失值所在行的訂單狀態(tài)均為“交易失敗”，那么下文分析都需要將訂單狀態(tài)為“交易失敗”的行全部剔除。

4.2 分析方法確定

針對(duì)此份數(shù)據(jù)，有3個(gè)分析方向可以選擇：

1. 留存率或付款率
2. 人均付款金額
3. 人均購(gòu)買(mǎi)次數(shù)

我們選擇其中最經(jīng)典，也是數(shù)分面試中最常考的留存率作為例子，需要用到的字段有：

• 客戶昵稱
• 付款時(shí)間
• 訂單狀態(tài)

相信各位對(duì)留存率都十分熟悉，不過(guò)多介紹。在本次的分析中，留存率的具體計(jì)算方式為：+N月留存率=（+N月付款用戶數(shù)/首月付款用戶數(shù)）*100%

注意：公式中的+N月存在歧義，會(huì)有兩種計(jì)算方法：

1. 以自然月作為月份偏移的依據(jù)：即所有首次行為在9月的用戶，只要10月有付款行為，都計(jì)算進(jìn)+1月留存
2. 以每30天作為月份偏移的依據(jù)：即9月30日首次付款的用戶，在10月30日-11月29日之間有付款行為，才計(jì)算進(jìn)+1月留存

具體的差距會(huì)在Excel（用算法1）和MySQL（用算法2）兩種工具實(shí)現(xiàn)的結(jié)果中分別展示。沒(méi)有相關(guān)技術(shù)背景的看官老爺可直接對(duì)比最終的留存率結(jié)果。

五、Excel實(shí)現(xiàn)

Excel的實(shí)現(xiàn)方式是三個(gè)當(dāng)中門(mén)檻最低的，只需要掌握數(shù)據(jù)透視表和一些基礎(chǔ)函數(shù)，但過(guò)程相對(duì)繁雜。實(shí)現(xiàn)思路如下：

實(shí)現(xiàn)思路一共分為4大部分：數(shù)據(jù)清洗 -> 計(jì)算首單時(shí)間 -> 計(jì)算首單時(shí)間與付款時(shí)間差 -> 利用透視表計(jì)算同期群留存量和留存率。其中由于部分版本的office和wps的數(shù)據(jù)透視表不支持非重復(fù)計(jì)數(shù)，因此需要先計(jì)算各月中各用戶出現(xiàn)的次數(shù)。

數(shù)據(jù)清洗部分只需要篩選+刪除便可完成，相信如此簡(jiǎn)單的操作難不倒各位看官老爺們，那么我們便從第二部分開(kāi)始詳細(xì)講解。

5.1 計(jì)算每個(gè)客戶首單時(shí)間

首先通過(guò)數(shù)據(jù)透視表求每一個(gè)用戶首次付款時(shí)間。數(shù)據(jù)透視表，說(shuō)白了就是通過(guò)特定的條件進(jìn)行分組，并對(duì)數(shù)據(jù)進(jìn)行求和、求均值、求方差等聚合操作。在制作數(shù)據(jù)透視表時(shí)要注意以下幾點(diǎn)：

1. 數(shù)據(jù)區(qū)域的第一行為標(biāo)題欄（字段名稱）
2. 標(biāo)題欄不能出現(xiàn)空單元格，亦不要出現(xiàn)重復(fù)的標(biāo)題名
3. 數(shù)據(jù)中避免有合并單元格
4. 不能出現(xiàn)非法日期

5.1.1 創(chuàng)建透視表

全選數(shù)據(jù)?->?插入?->?數(shù)據(jù)透視表?->?確定

5.1.2 選擇分組字段和值字段

將“客戶昵稱”拖進(jìn)“行”，將付款時(shí)間拖進(jìn)“值”，并將值字段設(shè)置中的匯總方式設(shè)置為最小值

這里最小付款時(shí)間顯示為10位的時(shí)間戳，只要調(diào)整顯示格式便可轉(zhuǎn)為我們常見(jiàn)的xx年xx月xx日。

5.1.3 將首單時(shí)間拼接到每個(gè)用戶所在行

此步驟需要使用到vlookup函數(shù)進(jìn)行匹配。VLOOKUP函數(shù)是一個(gè)縱向查找的函數(shù)，包含4個(gè)參數(shù)，具體語(yǔ)法為=VLOOKUP（查找的依據(jù)，查找的區(qū)域，返回的值在查找區(qū)域中的列號(hào)，是否近似匹配）

注意：

1. 查找的位置如果要保持不變，要使用A:B或者1:15的形式鎖定匹配區(qū)域
2. 參數(shù)[ 查找的位置 ]中，“!”號(hào)前為表的名稱
3. 列號(hào)的計(jì)數(shù)是從1開(kāi)始，且第一列必須是與查找依據(jù)對(duì)應(yīng)的列
4. 近似匹配參數(shù)中，0為否（即必須與查找依據(jù)一模一樣才匹配），1為是（即依據(jù)為“同期”時(shí)，可以匹配出“同期”、“同期群”或者“同期群分析”）

=VLOOKUP(A2,首付時(shí)間透視表!A:B,2,0)

利用VLOOKUP拼接之后，首單時(shí)間同樣顯示為10位的時(shí)間戳，設(shè)置單元格格式后即可顯示為上圖的形式。

5.2 計(jì)算時(shí)間差

5.2.1 對(duì)付款時(shí)間和首單時(shí)間進(jìn)行降采樣

如按算法2進(jìn)行計(jì)算，可直接省略此步驟。

可能有看官老爺對(duì)重采樣的概念并不是很清楚，簡(jiǎn)單說(shuō)下：

• 將時(shí)間序列從一個(gè)頻率轉(zhuǎn)化為另外一個(gè)頻率的過(guò)程即重采樣
• 常見(jiàn)的時(shí)間頻率由低到高依次為：年 -> 月 -> 日 -> 時(shí) -> 分 -> 秒
• 將高頻率轉(zhuǎn)為低頻率為降采樣，將低頻率轉(zhuǎn)為高頻率為升采樣

在Excel當(dāng)中可以使用分列或者時(shí)間相關(guān)函數(shù)（YEAR、MONTH、DAY等）方式來(lái)獲取到對(duì)應(yīng)的時(shí)間頻率。我們使用YEAR和MONTH來(lái)對(duì)時(shí)間進(jìn)行降采樣，注意與字符串連接一定要用“&”號(hào)。

=YEAR(B2)&"/"&MONTH(B2)

5.2.2 計(jì)算時(shí)間差

此步驟中需要用到DATEDIF函數(shù)，此公式常用于計(jì)算兩個(gè)日期之間的天數(shù)、月份、年數(shù)差，語(yǔ)法為：=DATEDIF（起始時(shí)間，結(jié)束時(shí)間，時(shí)間頻率），常用的時(shí)間頻率參數(shù)有['Y','M','D']，分別對(duì)應(yīng)年月日

=DATEDIF(E2,D2,"M")

5.2.3 重置月份差標(biāo)簽

修改透視表的標(biāo)簽并不方便，因此先重置月份差標(biāo)簽，需要用到一個(gè)IF函數(shù)便可。具體語(yǔ)法：=IF（條件，符合條件時(shí)的操作，不符合條件時(shí)的操作）

=IF(F2=0,"首月","+"&F2&"月")

5.3 計(jì)算同期留存量和留存率

如果是office 2013及之后的版本，以上的數(shù)據(jù)已經(jīng)足夠我們進(jìn)行留存量的計(jì)算，可以直接跳過(guò)計(jì)算用戶出現(xiàn)次數(shù)環(huán)節(jié)。

5.3.1 計(jì)算每月中每個(gè)用戶出現(xiàn)的次數(shù)

這里利用COUNTIFS函數(shù)，計(jì)算出“用戶昵稱”和“付款時(shí)間（重采樣）”均相同的次數(shù)，并取其倒數(shù)，讓當(dāng)月無(wú)論該用戶出現(xiàn)多少次，最終都只會(huì)計(jì)算為一次。即假設(shè)用戶當(dāng)月付款5次，倒數(shù)后權(quán)重變?yōu)?/5，求和后出現(xiàn)次數(shù)為1。

COUNTIFS的語(yǔ)法為：=COUNTIFS（區(qū)域A，條件A，區(qū)域B，條件B，....）

=COUNTIFS(A:A,A:A,D:D,D:D,E:E,E:E)

=1/H2

5.3.2 創(chuàng)建留存量數(shù)據(jù)透視表

針對(duì)wps及office2013以前的版本，我們已經(jīng)計(jì)算了出現(xiàn)次數(shù)的倒數(shù)，只需要仿照前文“計(jì)算每個(gè)用戶首單時(shí)間”的步驟創(chuàng)建數(shù)據(jù)透視表，以“首單時(shí)間重采樣”作為行，以“月份差標(biāo)簽”作為列，以“出現(xiàn)次數(shù)（倒數(shù)）”作為值，并修改值字段設(shè)置中的計(jì)算類(lèi)型為求和即可。

而office 2013及之后的版本，我們?cè)诓迦霐?shù)據(jù)透視表時(shí)，需要注意勾選“將此數(shù)據(jù)添加到數(shù)據(jù)模型”

同樣以“首單時(shí)間重采樣”作為行，以“月份差標(biāo)簽”作為列，但不同的是，我們可以直接以“客戶昵稱”作為值，并在值字段設(shè)置當(dāng)中，將計(jì)算類(lèi)型設(shè)置為“非重復(fù)計(jì)數(shù)”。

到此，我們留存量的透視圖便完成了，但格式看上去還是有點(diǎn)丑，我們手動(dòng)拖動(dòng)下行、列標(biāo)簽的排序，最終獲得如下效果：

5.3.3 計(jì)算留存率

在值字段顯示方式當(dāng)中并沒(méi)有找到我們想要的效果，因此我們?cè)跀?shù)據(jù)透視表下方選定一個(gè)區(qū)域，復(fù)制好行標(biāo)簽和列標(biāo)簽。通過(guò)公式“=C5/$B5”計(jì)算出留存率，并向右向下拖動(dòng)公式便可完成

注：

• B5為2019年9月的首月留存量，C5為2019年9月的+1月留存量
• 分母需要將B列鎖定，否則在向右拖動(dòng)公式時(shí)，分母會(huì)依次變?yōu)镈5、E5

完美符合我們預(yù)期的結(jié)果！Excel版本的實(shí)現(xiàn)就到這里便完成，接下來(lái)是門(mén)檻稍微高一億點(diǎn)點(diǎn)的MySQL實(shí)現(xiàn)。

六、MySQL實(shí)現(xiàn)

MySQL的實(shí)現(xiàn)路徑與Excel的實(shí)現(xiàn)路徑非常相近，具體步驟為：

1. 導(dǎo)入數(shù)據(jù)
2. 清洗數(shù)據(jù)：篩選訂單狀態(tài)為“交易成功”的行
3. 獲取首單時(shí)間
4. 求月份偏移：求出月份差，并對(duì)首付時(shí)間降采樣
5. 計(jì)算留存量：通過(guò)首付時(shí)間和月份差進(jìn)行分組，求唯一的用戶id數(shù)
6. 求留存率

6.1 導(dǎo)入數(shù)據(jù)

目前的數(shù)據(jù)的保存格式為xlsx，我們需要先將數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫(kù)當(dāng)中才能執(zhí)行查詢。第一步選擇一個(gè)庫(kù)，右鍵選擇導(dǎo)入向?qū)А?/p>

第二步選擇導(dǎo)入類(lèi)型，我們直接選擇Excel文件即可。

第三步為選擇數(shù)據(jù)源的路徑，我們找到對(duì)應(yīng)的數(shù)據(jù)后，勾選需要導(dǎo)入的表。

完成前文的操作之后便可以點(diǎn)擊“>>”跳轉(zhuǎn)至最后的步驟，當(dāng)然中間還有幾個(gè)調(diào)整數(shù)據(jù)的步驟，但此次數(shù)據(jù)十分工整，不需要進(jìn)行額外操作。

到達(dá)下圖的界面，我們按照指引直接點(diǎn)擊“開(kāi)始”即可，如導(dǎo)入成功，會(huì)在日志欄中顯示Finished successfully，如下圖所示。

6.2 數(shù)據(jù)清洗

照舊先篩選出訂單狀態(tài)為交易成功的行，并提取用戶昵稱、付款時(shí)間兩個(gè)字段。這里我們稍微修改了列名，把`用戶昵稱`修改成`c_id`，`付款時(shí)間`修改為`paytime`，`交易狀態(tài)`修改成了`status`。

我們后續(xù)的查詢都是基于篩選后的數(shù)據(jù)，因此這里新建一個(gè)表sheet2去存儲(chǔ)查詢結(jié)果。

--?步驟一：篩選訂單狀態(tài)為”交易成功“的行，并輸出表sheet2：用戶昵稱[c_id]、付款時(shí)間[paytime]
CREATE?table?sheet2?as
SELECT?c_id,paytime
FROM?sheet1
WHERE?`status`='交易成功';

6.3 計(jì)算首單時(shí)間

此步驟只需要對(duì)用戶昵稱進(jìn)行g(shù)roupby，再求最小值即可，不多贅述。

--?步驟二：找出每個(gè)用戶的首單時(shí)間
SELECT?c_id,min(paytime)?f_time
FROM?sheet2
GROUP?BY?c_id;

6.4 計(jì)算月份差，重采樣首付時(shí)間

此步驟中會(huì)涉及到兩個(gè)重要的函數(shù)：

1. 與Excel類(lèi)似，MySQL對(duì)時(shí)間戳重采樣也是用YEAR()、MONTH()等函數(shù)
2. 用于計(jì)算日期差的TIMESTAMPDIFF，具體語(yǔ)法為T(mén)IMESTAMPDIFF(頻率，起始時(shí)間，結(jié)束時(shí)間)

當(dāng)然在計(jì)算月份差之前，需要以用戶名稱作為依據(jù)，拼接用戶的首單時(shí)間。但由于數(shù)據(jù)量較大，拼接需要重復(fù)遍歷整個(gè)表很多遍，耗時(shí)很長(zhǎng)。而當(dāng)前查詢的結(jié)果并不是最終結(jié)果，我們只需要確保查詢語(yǔ)句沒(méi)有問(wèn)題即可。因此我們引入分頁(yè)查詢（LIMIT語(yǔ)句）來(lái)限制查詢結(jié)果的行數(shù)，從而提高查詢效率。

--?步驟三：求出月份差，對(duì)首付時(shí)間進(jìn)行重采樣
SELECT?
?a.c_id,
?b.f_time,
?TIMESTAMPDIFF(MONTH,b.f_time,a.paytime)?m_diff,
?CONCAT(YEAR(b.f_time),"年",MONTH(b.f_time),"月")?y_m
FROM?sheet2?a
LEFT?JOIN?(
?SELECT?c_id,min(paytime)?f_time
?FROM?sheet2
?GROUP?BY?c_id
--??LIMIT測(cè)試時(shí)用，為了提升效率
?LIMIT?0,7000
)?b?on?a.c_id=b.c_id
--?同樣是為了提升效率而使用
WHERE?b.f_time?is?NOT?NULL;

6.5 計(jì)算留存量

我們只需要將前面的三個(gè)步驟作為子查詢，并以`首單時(shí)間`以及`月份差`作為條件對(duì)數(shù)據(jù)進(jìn)行分組，用DISTINCT篩選出唯一的`用戶ID`即可求出我們所需的留存量。這里創(chuàng)建一個(gè)名為cohort的表儲(chǔ)存查詢結(jié)果。

--?步驟四：通過(guò)首付時(shí)間和月份差進(jìn)行分組，求出唯一的用戶id數(shù),并輸出為表[cohort]
CREATE?table?cohort?as
SELECT?c.y_m?"首付月份",c.m_diff"月份差",COUNT(DISTINCT?c.c_id)?"留存量"
FROM?(
SELECT?
?a.c_id,
?b.f_time,
?TIMESTAMPDIFF(MONTH,b.f_time,a.paytime)?m_diff,
?CONCAT(YEAR(b.f_time),"年",MONTH(b.f_time),"月")?y_m
from?sheet2?a
LEFT?JOIN?(
?SELECT?c_id,min(paytime)?f_time
?FROM?sheet2
?GROUP?BY?c_id
)?b?on?a.c_id=b.c_id
--?為了提升效率而使用
WHERE?b.f_time?is?NOT?NULL
)?c
GROUP?BY?c.y_m,c.m_diff;

查詢結(jié)果如下。相比于步驟三，我們這里刪除了用于分頁(yè)查詢的LIMIT語(yǔ)句，但依然保留了WHERE b.f_time is NOT NULL。這里的where語(yǔ)句并沒(méi)有篩選任何一行，但有無(wú)這一句的查詢效率相差非常大，分別為0.739s和125.649s。這里涉及到SQL優(yōu)化的問(wèn)題，有機(jī)會(huì)以后專(zhuān)門(mén)整理一篇文章分享給各位。

6.6 計(jì)算留存率

我們有了留存量的表格，計(jì)算留存率便非常容易，只要讓每一期的留存率都除以首月的留存率即可。

--?步驟五：計(jì)算留存率（基礎(chǔ)版）
SELECT?c.`首付月份`,CONCAT(ROUND((c.`留存量`/m.`留存量`)*100,2),"%")?留存率
FROM?cohort?c
LEFT?JOIN?(
?SELECT?首付月份,留存量
?FROM?cohort
?where?`月份差`=0
)?m?
on?c.`首付月份`=m.`首付月份`;

留存率結(jié)果如上圖，但結(jié)果并不利于觀察和分析，因此接下來(lái)的進(jìn)階版將通過(guò)case when語(yǔ)句，加入億點(diǎn)細(xì)節(jié)來(lái)優(yōu)化下展示格式。

--?步驟五：計(jì)算留存率（進(jìn)階版）
SELECT?
?n.`首付月份`,
?AVG(n.`留存量`)?"本月新增",
?CONCAT(sum(n.`+1月`),"%")?"+1月",
?CONCAT(sum(n.`+2月`),"%")?"+2月",
?CONCAT(sum(n.`+3月`),"%")?"+3月",
?CONCAT(sum(n.`+4月`),"%")?"+4月",
?CONCAT(sum(n.`+5月`),"%")?"+5月"
FROM(
?#?一級(jí)子查詢：轉(zhuǎn)置表格，將月份差作為列名
?SELECT?
??a.`首付月份`,
??a.`留存量`,
??CASE?a.`月份差`?when?1?THEN?a.`留存率`?ELSE?0?END?"+1月",
??CASE?a.`月份差`?when?2?THEN?a.`留存率`?ELSE?0?END?"+2月",
??CASE?a.`月份差`?when?3?THEN?a.`留存率`?ELSE?0?END?"+3月",
??CASE?a.`月份差`?when?4?THEN?a.`留存率`?ELSE?0?END?"+4月",
??CASE?a.`月份差`?when?5?THEN?a.`留存率`?ELSE?0?END?"+5月"
?FROM(
??#?二級(jí)子查詢：計(jì)算留存率
??SELECT?a.`首付月份`,b.`留存量`,a.`月份差`,ROUND((a.`留存量`/b.`留存量`)*100,2)?留存率
??FROM?cohort?a
??LEFT?JOIN?(
???#?三級(jí)子查詢：查詢首月用戶量
???SELECT?`首付月份`,`留存量`
???FROM?cohort
???WHERE?cohort.`月份差`=0
?)?b
?on?a.`首付月份`=b.`首付月份`
?)?a
)?n
GROUP?BY?n.`首付月份`;

正如“分析方法確定”環(huán)節(jié)中提及，Excel中通過(guò)自然月去劃分月份的偏移量，而MySQL中則直接將付款時(shí)間和首單時(shí)間相減。我們使用的TIMESTAMPDIFF函數(shù)的邏輯為結(jié)束日期的DAY參數(shù)大于等于起始日期的DAY參數(shù)時(shí)，月份差才會(huì)+N。即：

• 起始日期為9月30日，終止日期大于等于10月30日時(shí)，月份差才不為0。
• 起始日期為10月31日，終止日期大于等于12月1日時(shí)，月份差才不為0。
• 起始日期為1月30或31日，終止日期大于等于3月1日時(shí)，月份差才不為0，平/閏年一樣。

對(duì)比可知，算法1中留存率會(huì)出現(xiàn)小幅度的回升，但在算法2則隨時(shí)間增加而遞減。由此可知，不同的計(jì)算標(biāo)準(zhǔn)對(duì)結(jié)果影響非常大，可能會(huì)造成誤判，因此數(shù)據(jù)分析中確認(rèn)標(biāo)準(zhǔn)非常重要。

七、Python實(shí)現(xiàn)

作為壓軸，肯定是路子野、效率高、操作騷的Python。得益于pandas強(qiáng)大的分組功能及非常多的奇技淫巧，Python的實(shí)現(xiàn)相比于Excel或MySQL會(huì)更加簡(jiǎn)單，但實(shí)現(xiàn)路徑會(huì)比較抽象，需要注入一點(diǎn)想象力。按慣例先盤(pán)實(shí)現(xiàn)思路：

1. 數(shù)據(jù)清洗：刪除訂單狀態(tài)為”交易失敗“的行
2. 拼接首單時(shí)間：計(jì)算每個(gè)用戶首單時(shí)間，并拼接為新的dataframe
3. 求留存量：對(duì)數(shù)據(jù)分組，并求唯一的客戶昵稱數(shù)
4. 求留存率：用首月留存量除整個(gè)留存量的dataframe

7.1 數(shù)據(jù)清洗

此步驟只需要調(diào)用drop函數(shù)即可完成刪除，難度不大，核心是找到訂單狀態(tài)為“交易失敗”的所在行的行索引。

df.drop(index=df[df['訂單狀態(tài)']?==?'交易失敗'].index,?axis=1,?inplace=True)

7.2 拼接首單時(shí)間

調(diào)用分組聚合函數(shù)groupby以及數(shù)據(jù)拼接函數(shù)merge便能完成我們的需求，都是常規(guī)操作

df_f?=?df.groupby(by='客戶昵稱')['付款時(shí)間'].min().to_frame(name='首單時(shí)間')
df_f.reset_index(inplace=True)

#?合并新的dataframe，包含客戶昵稱，付款時(shí)間，首單時(shí)間
df_f?=?df[['客戶昵稱',?'付款時(shí)間']].merge(df_f)

7.3 計(jì)算留存量

接下來(lái)就是見(jiàn)證騷操作的時(shí)刻了。在pandas的分組聚合當(dāng)中，對(duì)時(shí)間戳進(jìn)行重采樣不要太簡(jiǎn)單，只需要修改freq參數(shù)即可。核心思路：

• 利用groupby函數(shù)對(duì)首單時(shí)間和付款時(shí)間進(jìn)行分組，獲得復(fù)合索引的series
• 利用pd.Grouper對(duì)首單時(shí)間和付款時(shí)間進(jìn)行重采樣
• 利用nunique函數(shù)求不重復(fù)值個(gè)數(shù)
• 利用unstack函數(shù)將復(fù)合索引的series轉(zhuǎn)為dataframe

#?通過(guò)首單時(shí)間及付款時(shí)間進(jìn)行分組，獲得每個(gè)時(shí)間段的不重復(fù)客戶數(shù)量
df_f?=?df_f.groupby(by=[pd.Grouper(key='首單時(shí)間',?freq='m'),?pd.Grouper(key='付款時(shí)間',?freq='m')])['客戶昵稱'].nunique()

#?將復(fù)合索引的series轉(zhuǎn)置為dataframe
df_f?=?df_f.unstack()

獲得的結(jié)果如上圖。如果有看Excel或MySQL實(shí)現(xiàn)方式的看官可能有會(huì)有疑問(wèn)，為什么python不用計(jì)算月份差而其他兩種需要。那是因?yàn)檫@種分組方式，首月用戶量都分布在表格的對(duì)角線上，在Excel的數(shù)據(jù)透視表或者M(jìn)ySQL當(dāng)中，等差地移動(dòng)單元格并不是一件容易的事，但對(duì)于Python來(lái)說(shuō)，不過(guò)是一個(gè)for循環(huán)。

for?i?in?range(len(df_f.index)):
?df_f.iloc[i]?=?df_f.iloc[i].shift(periods=-i)
??
#?重置columns
df_f.columns?=?['本月新增',?'+1月',?'+2月',?'+3月',?'+4月',?'+5月']

shift函數(shù)常用于移動(dòng)dataframe或series，具體參數(shù)如下：

• axis：針對(duì)dataframe：{0:"向下移動(dòng)" , 1:"向右移動(dòng)"}，針對(duì)series：向下移動(dòng)
• periods：移動(dòng)的步長(zhǎng)，當(dāng)periods為負(fù)時(shí)，向上/左移動(dòng)
• fill_value：補(bǔ)充N(xiāo)aN的值

得到如下結(jié)果

7.4 計(jì)算留存率

盡管pandas非常強(qiáng)大，但此步驟中，如通過(guò)df_f/df_f[‘首月’]計(jì)算，結(jié)果是全為NaN的dataframe。不過(guò)我們可以使用apply函數(shù)遍歷dataframe來(lái)實(shí)現(xiàn)。

df_1?=?df_f.apply(count_per,?axis=0,?args=(df_f['本月新增'],))
df_1['本月新增']=df_f['本月新增']

def?count_per(s,?dx):
????a=[f'{i}%'?if?str(i)!='nan'?else?0?for?i?in?round((s?/?dx)?*?100,?2)]
????return?a

作為pandas中最好用的函數(shù)之一，apply的詳細(xì)用法各位參考官方文檔即可，這里僅提三點(diǎn)注意事項(xiàng)：

1. 在apply中調(diào)用的函數(shù)不需要加括號(hào)，僅提供函數(shù)名即可
2. 向apply調(diào)用的函數(shù)傳遞變量，只需賦值給args，如果僅傳遞一個(gè)變量，要在變量后加上 “，”號(hào)
3. 調(diào)用的函數(shù)當(dāng)中第0個(gè)參數(shù)由self提供，從第一個(gè)變量開(kāi)始才是args中的變量，即上面函數(shù)中，dx對(duì)應(yīng)的是df_f['本月新增']

獲得結(jié)果如下，完美完成任務(wù)：

八、復(fù)盤(pán)總結(jié)

先回顧下同期群分析的重點(diǎn)

• 同期群分析指將用戶進(jìn)行同期群劃分后，對(duì)比不同同期群組用戶的相同指標(biāo)的分析方法
• 同期群分析是產(chǎn)品數(shù)據(jù)分析的核心，能細(xì)致地監(jiān)控用戶行為，衡量用戶價(jià)值
• 時(shí)間的劃分標(biāo)準(zhǔn)對(duì)分析結(jié)果影響很大，確定標(biāo)準(zhǔn)非常重要

最后總結(jié)下本次ESP實(shí)現(xiàn)方式中分別涉及到的重要知識(shí)點(diǎn)

那么本次的分享到這里便結(jié)束了。至于同期群分析如何應(yīng)用到實(shí)際業(yè)務(wù)問(wèn)題中，我們留到下一篇商業(yè)分析實(shí)戰(zhàn)再詳細(xì)講解。（如果寫(xiě)出來(lái)的話，一定不會(huì)鴿，一定不會(huì)~鴿！）

我是HeoiJin，不要期待有下篇~

我們的文章到此就結(jié)束啦，如果你喜歡今天的Python 實(shí)戰(zhàn)教程，請(qǐng)持續(xù)關(guān)注Python實(shí)用寶典。

有任何問(wèn)題，可以在公眾號(hào)后臺(tái)回復(fù)：加群，回答相應(yīng)紅字驗(yàn)證信息，進(jìn)入互助群詢問(wèn)。

原創(chuàng)不易，希望你能在下面點(diǎn)個(gè)贊和在看支持我繼續(xù)創(chuàng)作，謝謝！

點(diǎn)擊下方閱讀原文可獲得更好的閱讀體驗(yàn)

Python實(shí)用寶典?(pythondict.com)
不只是一個(gè)寶典
歡迎關(guān)注公眾號(hào)：Python實(shí)用寶典