用Excel、SQL、做數據分析有何不同？
凹凸數據
作者簡介：立志透過數據看清世界的產品策劃，專注爬蟲、數據分析、產品策劃領域。萬物皆營銷 | 資本永不眠 | 數據恒真理CSDN：、、
一、前言
后互聯網時代，獲客拉新的成本越來越高，如何增加客戶的留存，提高客戶的復購次數、購買金額等變得十分重要，同期群分析便是當中非常重要的分析方法。關于同期群分析概念和思路的文章很多，但分享如何實現的文章非常罕見。因此，本文將簡單介紹同期群分析的概念，并用數據分析師的三板斧ESP（Excel、MySQL、）分別實現同期群分析。
二、項目準備MySQL：：三、同期群分析概念講解
數據分析最終目標都是為了解決業務問題，任何分析方法都只是工具。因此在詳細講解如何實現之前，需要先明晰方法的含義是什么，能帶來什么收益，才能在合適的問題上選對分析方法。
3.1 同期群分析含義
同期群()即相同時間內具有相似或特定屬性 、行為的群體。核心要素為時間+特定屬性，比如把00后出生的人劃分為一個群組。同期群分析指將用戶進行同期群劃分后，對比不同同期群用戶的相同指標。我們耳熟能詳的留存率就是同期群分析的其中一種，案例如下圖：
同期群分析包含了3個重要元素：
客戶首次行為時間，這是我們劃分同期群的依據時間維度，即上圖中+N月或者N日留存率中的N日指標，注冊轉化率、付款轉化率、留存率等等3.2 意義
同期群分析給到更加細致的衡量指標，幫助我們實時監控真實的用戶行為、衡量用戶價值，并為營銷方案的優化和改進提供支撐：
四、材料梳理
4.1 數據情況梳理拿到數據的第一步，自然是了解數據的情況。針對本次同期群分析，我們可能需要用到的字段有：
4.2 分析方法確定
針對此份數據，有3個分析方向可以選擇：
留存率或付款率人均付款金額人均購買次數我們選擇其中最經典，也是數分面試中最常考的留存率作為例子，需要用到的字段有：以自然月作為月份偏移的依據：即所有首次行為在9月的用戶，只要10月有付款行為，都計算進+1月留存以每30天作為月份偏移的依據：即9月30日首次付款的用戶，在10月30日-11月29日之間有付款行為，才計算進+1月留存具體的差距會在Excel（用算法1）和MySQL（用算法2）兩種工具實現的結果中分別展示。沒有相關技術背景的看官老爺可直接對比最終的留存率結果。五、Excel實現
Excel的實現方式是三個當中門檻最低的，只需要掌握數據透視表和一些基礎函數，但過程相對繁雜。實現思路如下：
實現思路一共分為4大部分：數據清洗 -> 計算首單時間 -> 計算首單時間與付款時間差 -> 利用透視表計算同期群留存量和留存率。其中由于部分版本的和wps的數據透視表不支持非重復計數，因此需要先計算各月中各用戶出現的次數。數據清洗部分只需要篩選+刪除便可完成，相信如此簡單的操作難不倒各位看官老爺們，那么我們便從第二部分開始詳細講解。
5.1 計算每個客戶首單時間
首先通過數據透視表求每一個用戶首次付款時間。數據透視表，說白了就是通過特定的條件進行分組，并對數據進行求和、求均值、求方差等聚合操作。在制作數據透視表時要注意以下幾點：
數據區域的第一行為標題欄（字段名稱）標題欄不能出現空單元格，亦不要出現重復的標題名數據中避免有合并單元格不能出現非法日期5.1.1 創建透視表
全選數據 -> 插入 -> 數據透視表 -> 確定
5.1.2 選擇分組字段和值字段
將“客戶昵稱”拖進“行”，將付款時間拖進“值”，并將值字段設置中的匯總方式設置為最小值
這里最小付款時間顯示為10位的時間戳，只要調整顯示格式便可轉為我們常見的xx年xx月xx日。5.1.3 將首單時間拼接到每個用戶所在行此步驟需要使用到函數進行匹配。函數是一個縱向查找的函數，包含4個參數，具體語法為=（查找的依據，查找的區域，返回的值在查找區域中的列號，是否近似匹配）注意：
查找的位置如果要保持不變，要使用A:B或者1:15的形式鎖定匹配區域參數[ 查找的位置 ]中，“!”號前為表的名稱列號的計數是從1開始，且第一列必須是與查找依據對應的列近似匹配參數中，0為否（即必須與查找依據一模一樣才匹配），1為是（即依據為“同期”時，可以匹配出“同期”、“同期群”或者“同期群分析”）
=VLOOKUP(A2,首付時間透視表!A:B,2,0)
利用拼接之后，首單時間同樣顯示為10位的時間戳，設置單元格格式后即可顯示為上圖的形式。
5.2 計算時間差
5.2.1 對付款時間和首單時間進行降采樣如按算法2進行計算，可直接省略此步驟。可能有看官老爺對重采樣的概念并不是很清楚，簡單說下：
=YEAR(B2)&"/"&MONTH(B2)
5.2.2 計算時間差此步驟中需要用到函數，此公式常用于計算兩個日期之間的天數、月份、年數差excel2007數據透視表 求差，語法為：=（起始時間，結束時間，時間頻率）excel2007數據透視表 求差，常用的時間頻率參數有['Y','M','D']，分別對應年月日
=DATEDIF(E2,D2,"M")
5.2.3 重置月份差標簽修改透視表的標簽并不方便，因此先重置月份差標簽，需要用到一個IF函數便可。具體語法：=IF（條件，符合條件時的操作，不符合條件時的操作）
=IF(F2=0,"首月","+"&F2&"月")
5.3 計算同期留存量和留存率如果是 2013及之后的版本，以上的數據已經足夠我們進行留存量的計算，可以直接跳過計算用戶出現次數環節。5.3.1 計算每月中每個用戶出現的次數這里利用函數，計算出“用戶昵稱”和“付款時間（重采樣）”均相同的次數，并取其倒數，讓當月無論該用戶出現多少次，最終都只會計算為一次。即假設用戶當月付款5次，倒數后權重變為1/5，求和后出現次數為1。的語法為：=（區域A，條件A，區域B，條件B，....）
=COUNTIFS(A:A,A:A,D:D,D:D,E:E,E:E)
=1/H2
5.3.2 創建留存量數據透視表針對wps及以前的版本，我們已經計算了出現次數的倒數，只需要仿照前文“計算每個用戶首單時間”的步驟創建數據透視表，以“首單時間重采樣”作為行，以“月份差標簽”作為列，以“出現次數（倒數）”作為值，并修改值字段設置中的計算類型為求和即可。而 2013及之后的版本，我們在插入數據透視表時，需要注意勾選“將此數據添加到數據模型”
數據透視表同樣以“首單時間重采樣”作為行，以“月份差標簽”作為列，但不同的是，我們可以直接以“客戶昵稱”作為值，并在值字段設置當中，將計算類型設置為“非重復計數”。
到此，我們留存量的透視圖便完成了，但格式看上去還是有點丑，我們手動拖動下行、列標簽的排序，最終獲得如下效果：
5.3.3 計算留存率在值字段顯示方式當中并沒有找到我們想要的效果，因此我們在數據透視表下方選定一個區域，復制好行標簽和列標簽。通過公式“=C5/$B5”計算出留存率，并向右向下拖動公式便可完成注：
六、MySQL實現
MySQL的實現路徑與Excel的實現路徑非常相近，具體步驟為：
導入數據清洗數據：篩選訂單狀態為“交易成功”的行獲取首單時間求月份偏移：求出月份差，并對首付時間降采樣計算留存量：通過首付時間和月份差進行分組，求唯一的用戶id數求留存率6.1 導入數據
目前的數據的保存格式為xlsx，我們需要先將數據導入到數據庫當中才能執行查詢。第一步選擇一個庫，右鍵選擇導入向導。
第二步選擇導入類型，我們直接選擇Excel文件即可。
第三步為選擇數據源的路徑，我們找到對應的數據后，勾選需要導入的表。
完成前文的操作之后便可以點擊“>>”跳轉至最后的步驟，當然中間還有幾個調整數據的步驟，但此次數據十分工整，不需要進行額外操作。到達下圖的界面，我們按照指引直接點擊“開始”即可，如導入成功，會在日志欄中顯示 ，如下圖所示。
6.2 數據清洗
照舊先篩選出訂單狀態為交易成功的行，并提取用戶昵稱、付款時間兩個字段。這里我們稍微修改了列名，把用戶昵稱修改成c_id，付款時間修改為，交易狀態修改成了。我們后續的查詢都是基于篩選后的數據，因此這里新建一個表去存儲查詢結果。
-- 步驟一：篩選訂單狀態為”交易成功“的行，并輸出表sheet2：用戶昵稱[c_id]、付款時間[paytime]
CREATE table sheet2 as
SELECT c_id,paytime
FROM sheet1
WHERE `status`='交易成功';
6.3 計算首單時間
此步驟只需要對用戶昵稱進行，再求最小值即可，不多贅述。
-- 步驟二：找出每個用戶的首單時間
SELECT c_id,min(paytime) f_time
FROM sheet2
GROUP BY c_id;
6.4 計算月份差，重采樣首付時間此步驟中會涉及到兩個重要的函數：
與Excel類似，MySQL對時間戳重采樣也是用YEAR()、MONTH()等函數用于計算日期差的，具體語法為(頻率，起始時間，結束時間)當然在計算月份差之前，需要以用戶名稱作為依據，拼接用戶的首單時間。但由于數據量較大，拼接需要重復遍歷整個表很多遍，耗時很長。而當前查詢的結果并不是最終結果，我們只需要確保查詢語句沒有問題即可。因此我們引入分頁查詢（LIMIT語句）來限制查詢結果的行數，從而提高查詢效率。
-- 步驟三：求出月份差，對首付時間進行重采樣
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測試時用，為了提升效率
LIMIT 0,7000
) b on a.c_id=b.c_id
-- 同樣是為了提升效率而使用
WHERE b.f_time is NOT NULL;
6.5 計算留存量
我們只需要將前面的三個步驟作為子查詢，并以首單時間以及月份差作為條件對數據進行分組，用篩選出唯一的用戶ID即可求出我們所需的留存量。這里創建一個名為的表儲存查詢結果。
-- 步驟四：通過首付時間和月份差進行分組，求出唯一的用戶id數,并輸出為表[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;
查詢結果如下。相比于步驟三，我們這里刪除了用于分頁查詢的LIMIT語句，但依然保留了WHERE b. is NOT NULL。這里的where語句并沒有篩選任何一行，但有無這一句的查詢效率相差非常大，分別為0.739s和125.649s。這里涉及到SQL優化的問題，有機會以后專門整理一篇文章分享給各位。
6.6 計算留存率
我們有了留存量的表格，計算留存率便非常容易，只要讓每一期的留存率都除以首月的留存率即可。
-- 步驟五：計算留存率（基礎版）
SELECT c.`首付月份`,CONCAT(ROUND((c.`留存量`/m.`留存量`)*100,2),"%") 留存率
FROM cohort c
LEFT JOIN (
SELECT 首付月份,留存量
FROM cohort
where `月份差`=0
) m
on c.`首付月份`=m.`首付月份`;
留存率結果如上圖，但結果并不利于觀察和分析，因此接下來的進階版將通過case when語句，加入億點細節來優化下展示格式。
-- 步驟五：計算留存率（進階版）
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(
# 一級子查詢：轉置表格，將月份差作為列名
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(
# 二級子查詢：計算留存率
SELECT a.`首付月份`,b.`留存量`,a.`月份差`,ROUND((a.`留存量`/b.`留存量`)*100,2) 留存率
FROM cohort a
LEFT JOIN (
# 三級子查詢：查詢首月用戶量
SELECT `首付月份`,`留存量`
FROM cohort
WHERE cohort.`月份差`=0
) b
on a.`首付月份`=b.`首付月份`
) a
) n
GROUP BY n.`首付月份`;
正如“分析方法確定”環節中提及，Excel中通過自然月去劃分月份的偏移量，而MySQL中則直接將付款時間和首單時間相減。我們使用的函數的邏輯為結束日期的DAY參數大于等于起始日期的DAY參數時，月份差才會+N。即：
七、實現
作為壓軸，肯定是路子野、效率高、操作騷的。得益于強大的分組功能及非常多的奇技淫巧，的實現相比于Excel或MySQL會更加簡單，但實現路徑會比較抽象，需要注入一點想象力。按慣例先盤實現思路：
數據清洗：刪除訂單狀態為”交易失敗“的行拼接首單時間：計算每個用戶首單時間，并拼接為新的求留存量：對數據分組，并求唯一的客戶昵稱數求留存率：用首月留存量除整個留存量的.1 數據清洗
此步驟只需要調用drop函數即可完成刪除，難度不大，核心是找到訂單狀態為“交易失敗”的所在行的行索引。
df.drop(index=df[df['訂單狀態'] == '交易失敗'].index, axis=1, inplace=True)
7.2 拼接首單時間
調用分組聚合函數以及數據拼接函數merge便能完成我們的需求，都是常規操作
df_f = df.groupby(by='客戶昵稱')['付款時間'].min().to_frame(name='首單時間')
df_f.reset_index(inplace=True)
# 合并新的dataframe，包含客戶昵稱，付款時間，首單時間
df_f = df[['客戶昵稱', '付款時間']].merge(df_f)
7.3 計算留存量
接下來就是見證騷操作的時刻了。在的分組聚合當中，對時間戳進行重采樣不要太簡單，只需要修改freq參數即可。核心思路：
# 通過首單時間及付款時間進行分組，獲得每個時間段的不重復客戶數量
df_f = df_f.groupby(by=[pd.Grouper(key='首單時間', freq='m'), pd.Grouper(key='付款時間', freq='m')])['客戶昵稱'].nunique()
# 將復合索引的series轉置為dataframe
df_f = df_f.unstack()
獲得的結果如上圖。如果有看Excel或MySQL實現方式的看官可能有會有疑問，為什么不用計算月份差而其他兩種需要。那是因為這種分組方式，首月用戶量都分布在表格的對角線上，在Excel的數據透視表或者MySQL當中，等差地移動單元格并不是一件容易的事，但對于來說，不過是一個for循環。
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函數常用于移動或，具體參數如下：
7.4 計算留存率
盡管非常強大，但此步驟中，如通過df_f/df_f[‘首月’]計算，結果是全為NaN的。不過我們可以使用apply函數遍歷來實現。
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
作為中最好用的函數之一，apply的詳細用法各位參考官方文檔即可，這里僅提三點注意事項：
在apply中調用的函數不需要加括號，僅提供函數名即可向apply調用的函數傳遞變量，只需賦值給args，如果僅傳遞一個變量，要在變量后加上 “，”號調用的函數當中第0個參數由self提供，從第一個變量開始才是args中的變量，即上面函數中，dx對應的是df_f['本月新增']獲得結果如下，完美完成任務：
八、復盤總結
先回顧下同期群分析的重點
那么本次的分享到這里便結束了。至于同期群分析如何應用到實際業務問題中，我們留到下一篇商業分析實戰再詳細講解。（如果寫出來的話，一定不會鴿，一定不會~鴿！）我是，不要期待有下篇~
參考資料數據分析實戰 | 經典的同期群分析（附實戰數據和代碼）數據分析里經常聽到的同期群，到底是個啥？同期群分析（ ）：