本文目錄：
資料準備
日活計算
SQL計算日活
pandas計算日活
留存率計算
SQL計算
次日留存計算
多日留存計算
pandas方式
小結
在之前的五篇系列文章中，我們對比了pandas和SQL在資料方面的多項操作。
本篇文章主要來總結學習SQL和pandas中計算日活和多日留存的方法。
資料準備
先來看一下日活和留存的定義，對任何一款App而言，這兩個指標都是很重要的。
日活(Daily Active User，即DAU)顧名思義即每天的活躍使用者，至於如何定義就有多種口徑了。一方面要約定何為“活躍”，可以是啟動一次App，可以是到達某一個頁面，可以是進入App後產生某一個行為等等。
另一方面要約定計量的口徑，可以是計算使用者id的去重數，也可以是裝置id的去重數。這兩種口徑統計結果會有差異，原因在於未登入的使用者可能存在裝置id，不存在使用者id；並且裝置id與使用者id可能存在多對多的情況。因此對於運營來講，確定合理有效的口徑是很重要的。
留存是一個動態的概念，指的是某段時間使用了產品的使用者，在一段時間之後仍然在使用產品的使用者，二者相比可以求出留存率。常見的留存率有次日留存率，7日留存率，30日留存率等。次日留存是指今天活躍的使用者，在明天還剩下多少仍然活躍的使用者。留存率越高，說明產品的粘性越好。
https://www.kaggle.com/nikhil04/login-time-for-users 。
資料格式比較簡單：id：自增id，uid：使用者唯一id。ts：使用者登入的時間(精確到秒)，資料樣例如下圖，在公眾號後臺回覆“對比六”可以獲得本文全部的資料和程式碼，方便進行實操。
本次我們只用到MySQL和pandas。MySQL可以直接執行我提供的login.sql檔案載入資料，具體過程可以參考前面的文章。pandas中直接使用read_csv的方式讀取即可，可以參考後面的程式碼。
日活計算
這裡我們約定日活是指每天登入的user_id去重數，從我們的資料來看，計算方式非常簡單。
SQL計算日活
早在系列第一篇中我們就學習過group by聚合操作。只需要按天分組，將uid去重計數，即可得到答案。程式碼如下：
pandas計算日活
pandas計算日活也不難，同樣是使用groupby ，對uid進行去重計數。程式碼如下：
importpandasaspdlogin_data=pd.read_csv('login_data.txt',sep='\\t',parse_dates=['ts'])login_data.head()login_data['day']=login_data['ts'].map(lambdax:x.strftime('%Y-%m-%d'))uid_count=login_data.groupby('day').aggregate({'uid':lambdax:x.nunique()})uid_count.reset_index(inplace=True)uid_count我們增加了一列精確到天的日期資料，便於後續分組。在聚合時，使用了nunique進行去重。(在這裡也糾正一下系列第一篇文章中第6部分中的寫法，np.size 是不去重的，相當於count，但又不能直接寫np.nunique，所以我們採用了lambda函式的形式。感謝熱心讀者的指出~)最終uid_count的輸出結果如下圖所示，uid列就是我們要求的dau，結果和SQL算出來一樣。可以再用rename對列進行重新命名，此處略：
留存計算
如前文所示，這裡我們定義，留存率是指一段時間後仍然登入的使用者佔第一天登入使用者的比例，由於2017-01-07登入的使用者太少，我們選擇2017-01-12作為第一天。分別計算次日留存率，7日，14日留存率。
SQL方式
次日留存計算
同前面計算日環比周同比一樣，我們可以採用自連線的方式，但連線的條件除了日期外，還需要加上uid，這是一個更加嚴格的限制。左表計數求出初始活躍使用者，右表計數求出留存使用者，之後就可以求出留存率。程式碼如下，注意連線條件：
SELECTsubstr(a.ts,1,10)asdt,count(distincta.uid),count(distinctb.uid), concat(round((count(distinctb.uid)/count(distincta.uid))*100,2),'%')as1_day_remainfromt_loginaleftjoint_loginbona.uid=b.uidanddate_add(substr(a.ts,1,10),INTERVAL1day)=substr(b.ts,1,10)groupbysubstr(a.ts,1,10)得到的結果如下：
多日留存計算
上面自連線的方法固然可行，但是如果要同時計算次日，7日，14日留存，還需要在此基礎上進行關聯兩次，關聯條件分別為日期差為6和13。讀者可以試試寫一下程式碼。
當資料量比較大時，多次關聯在執行效率上會有瓶頸。因此我們可以考慮新的思路。在確定要求固定日留存時，我們使用了日期關聯，那麼如果不確定求第幾日留存的情況下，是不是可以不寫日期關聯的條件呢，答案是肯定的。來看程式碼：
selectsubstr(a.ts,1,10)asdt,count(distincta.uid),count(distinctif(datediff(substr(b.ts,1,10),substr(a.ts,1,10))=1,b.uid,null))as1_day_remain_uid,count(distinctif(datediff(substr(b.ts,1,10),substr(a.ts,1,10))=6,b.uid,null))as7_day_remain_uid,count(distinctif(datediff(substr(b.ts,1,10),substr(a.ts,1,10))=13,b.uid,null))as14_day_remain_uidfromt_loginaleftjoint_loginbona.uid=b.uidgroupbysubstr(a.ts,1,10)如程式碼所示，在關聯時先不限制日期，最外層查詢時根據自己的目標限定日期差，可以算出相應的留存使用者數，第一天的活躍使用者也可以看作是日期差為0時的情況。這樣就可以一次性計算多日留存了。結果如下，如果要計算留存率，只需轉換為對應的百分比即可，參考前面的程式碼，此處略。
pandas方式
次日留存計算
pandas計算留存也是緊緊圍繞我們的目標進行：同時求出第一日和次日的活躍使用者數，然後求比值。同樣也可以採用自連線的方式。程式碼如下(這裡的步驟比較多)：
1.匯入資料並新增兩列日期，分別是字串格式和datetime64格式，便於後續日期計算
2.構造新的dataframe，計算日期，之後與原資料進行連線。
3.合併前面的兩個資料，使用uid和dt_ts 關聯，dt_ts_1是當前日期減一天，左邊是第一天活躍的使用者，右邊是第二天活躍的使用者
4.計算第一天活躍的使用者數
5.計算次日活躍的使用者數
6.合併前面兩步的結果，計算最終留存
merge_one_day=pd.merge(init_user,one_day_remain_user,on=['day_x'])merge_one_day['one_remain_rate']=merge_one_day['uid_y']/merge_one_day['uid_x']merge_one_day['one_remain_rate']=merge_one_day['one_remain_rate'].apply(lambdax:format(x,'.2%'))merge_one_day.head(20)多日留存計算
方法一：
多日留存的計算可以沿用SQL中的思路，關聯時先不用帶日期條件
1.計算日期差，為後續做準備
merge_all=pd.merge(login_data,login_data,on=['uid'],how='left'merge_all['diff']=(merge_all['dt_ts_y']-merge_all['dt_ts_x']).map(lambdax:x.days)#使用map取得具體數字merge_all.head()2.計算第n天的留存人數，n=0,1,6,13。需要先進行篩選再進行計數，仍然使用nunique
diff_0=merge_all[merge_all['diff']==0].groupby('day_x')['uid'].nunique()diff_1=merge_all[merge_all['diff']==1].groupby('day_x')['uid'].nunique()diff_6=merge_all[merge_all['diff']==6].groupby('day_x')['uid'].nunique()diff_13=merge_all[merge_all['diff']==13].groupby('day_x')['uid'].nunique()diff_0=diff_0.reset_index()#groupby計數後得到的是series格式，reset得到dataframediff_1=diff_1.reset_index()diff_6=diff_6.reset_index()diff_13=diff_13.reset_index()3.對多個dataframe進行一次合併
liucun=pd.merge(pd.merge(pd.merge(diff_0,diff_1,on=['day_x'],how='left'),diff_6,on=['day_x'],how='left'),diff_13,on=['day_x'],how='left')liucun.head()4.對結果重新命名，並用0填充na值
liucun.columns=['day','init','one_day_remain','seven_day_remain','fifteen_day_remain']#後來發現英文寫錯了，將就看，懶得改了liucun.fillna(0,inplace=True)liucun.head(20)
得到的結果和SQL計算的一致，同樣省略了百分比轉換的程式碼。
方法二：
這種方法是從網上看到的，也放在這裡供大家學習，文末有連結。它沒有用自關聯，而是對日期進行迴圈，計算當日的活躍使用者數和n天后的活躍使用者數。把n作為引數傳入封裝好的函式中。參考下面程式碼：
defcal_n_day_remain(df,n): dates=pd.Series(login_data.dt_ts.unique()).sort_values()[:-n]#取截止到n天的日期，保證有n日留存 users=[]#定義列表存放初始使用者數remains=[]#定義列表存放留存使用者數 fordindates: user=login_data[login_data['dt_ts']==d]['uid'].unique()#當日活躍使用者 user_n_day=login_data[login_data['dt_ts']==d+timedelta(n)]['uid'].unique()#n日後活躍使用者 remain=[xforxinuser_n_dayifxinuser]#取交集 users.append(len(user)) remains.append(len(remain))#一次迴圈計算一天的n日留存#迴圈結束後構造dataframe並返回 remain_df=pd.DataFrame({'days':dates,'user':users,'remain':remains}) returnremain_df程式碼的邏輯整體比較簡單，必要的部分我做了註釋。但需要一次一次呼叫，最後再merge起來。最後結果如下所示，從左到右依次是次日，7日，14日留存，和前面結果一樣(可以再重新命名一下)。
至此，我們完成了SQL和pandas對日活和留存率的計算。
小結
本篇文章我們研究了非常重要的兩個概念，日活和留存。探討了如何用SQL和pandas進行計算。日活計算比較簡單。留存計算可以有多種思路。pandas計算這兩個指標沒有特別之處，但是用到了前面文章中的分組聚合(第一篇)，日期處理(第三篇)的部分，正好幫我們複習一下。後臺回覆“對比六”可以獲取本文pdf版本、資料和程式碼~希望對你有所幫助！
reference
https://blog.csdn.net/msspark/article/details/86727058