實際工作中常見的業務場景是求次日留存率，還有一些會對次日留存率增加限制，例如求新使用者的次日留存率或者求活躍使用者留存率。另外，留存率和復購率看起來都是統計重複出現的機率，但實際求解方法是不一樣的。
【場景】：次日留存率、復購率
【知識點】：留存率的定義、復購率的定義、多表連線、date_sub(dt1, interval 1 day) = dt2、datediff(dt1, dt2) = 1
一、分析思路
1、留存率如何定義？
次日留存率 = （當天活躍的使用者第二天又活躍了的使用者數）/（當天活躍的使用者數）
常見的場景是求次日留存率，本文以次日留存率為例。
擴充套件： 第N日留存率 = （當天活躍的使用者第N天又活躍了的使用者數）/（當天活躍的使用者數）
舉個例子：如果使用者1在1號、2號、3號活躍；使用者2在1號、2號活躍。那麼次日留存率是多少？
- 先把上面的活躍記錄按照日期、活躍使用者、次日留存使用者數整理成下表：
日期 活躍使用者 次日留存使用者數 1號 使用者1、使用者2 2 2號 使用者1、使用者2 1 3號 使用者1 0 總計 5 3 次日留存率 = 3/5。
2、如何判斷當日活躍的使用者次日又有活躍記錄？
同一個表看成兩個表做連線，使用
select distinct a.主鍵, a.活躍日期 as 當日, b.活躍日期 as 次日 from 表名1 a left join( select distinct 主鍵, 活躍日期 from 表名1 ) b on a.主鍵 = b.主鍵 and 次日-當日=一天這樣就得到使用者當日和次日的活躍記錄。
- 使用者當日、次日活躍記錄表
使用者 當日 次日 使用者1 1號 2號 使用者2 1號 2號 使用者1 2號 3號 使用者2 2號 None 使用者1 3號 None
3、計算次日留存率
次日留存率 = 次日留存使用者數 / 當日活躍使用者數
select count(次日) / count(當日) as 次日留存率 from 使用者當日和次日的活躍記錄表
二、例項
下面就以三個例項講清楚什麼是次日留存率、新使用者的次日留存率和復購率。
三種問題的區別：
| 問題 | 描述 | 特點 | 使用方法 |
|---|---|---|---|
| 次日留存率 | 次日留存率為當天活躍的使用者數中第二天又活躍了的使用者數佔比 | 固定時間的記錄 | 先獲取使用者、當日和第n日活躍時間記錄表，後按照留存率公式進行計算 |
| 新使用者次日留存率 | 新使用者的次日留存率為當天新增的使用者數中第二天又活躍了的使用者數佔比 | 限制條件的留存率 | 先獲取新使用者、當日和第n日活躍時間記錄表，後按照留存率公式進行計算 |
| 復購率 | 復購率指使用者在一段時間內對某商品的重複購買比例 | 一段時間內的記錄 | 先獲取根據商品、購買人分組，按照購買時間排序後的購買記錄表，後按照復購率公式進行計算 |
（1）計算次日留存率
題目： 現在運營想要檢視使用者在某天刷題後第二天還會再來刷題的平均機率。請你取出相應資料。
示例：question_practice_detail
| id | device_id | quest_id | result | date |
|---|---|---|---|---|
| 1 | 2138 | 111 | wrong | 2021-05-03 |
| 2 | 3214 | 112 | wrong | 2021-05-09 |
| 3 | 3214 | 113 | wrong | 2021-06-15 |
| 4 | 6543 | 111 | right | 2021-08-13 |
| 5 | 2315 | 115 | right | 2021-08-13 |
| 6 | 2315 | 116 | right | 2021-08-14 |
| 7 | 2315 | 117 | wrong | 2021-08-15 |
| …… |
根據示例，你的查詢應返回以下結果：
| avg_ret |
|---|
| 0.3000 |
求解程式碼：
select
count(date2) / count(date1) as avg_ret
from(
select distinct
a.device_id,
a.date as date1,
b.date as date2
from question_practice_detail a
left join(
select distinct device_id,
date
from question_practice_detail
) b on a.device_id = b.device_id and date_add(a.date, interval 1 day) = b.date
) c
- 使用者當日、次日活躍記錄表
device_id date1 date2
1 2138 2021-05-03 None
2 3214 2021-05-09 None
3 3214 2021-06-15 None
4 6543 2021-08-13 None
5 2315 2021-08-13 2021-08-14
6 2315 2021-08-14 2021-08-15
7 2315 2021-08-15 None
8 3214 2021-08-15 2021-08-16
9 3214 2021-08-16 None
10 3214 2021-08-18 None
擴充套件：
（2）計算新使用者的次日留存率
問題：統計2021年11月每天新使用者的次日留存率（保留2位小數）
使用者行為日誌表tb_user_log。（uid-使用者ID, artical_id-文章ID, in_time-進入時間, out_time-離開時間, sign_in-是否簽到）
| id | uid | artical_id | in_time | out_time | sign_cin |
|---|---|---|---|---|---|
| 1 | 101 | 0 | 2021-11-01 10:00:00 | 2021-11-01 10:00:42 | 1 |
| 2 | 102 | 9001 | 2021-11-01 10:00:00 | 2021-11-01 10:00:09 | 0 |
| 3 | 103 | 9001 | 2021-11-01 10:00:01 | 2021-11-01 10:01:50 | 0 |
| 4 | 101 | 9002 | 2021-11-02 10:00:09 | 2021-11-02 10:00:28 | 0 |
| 5 | 103 | 9002 | 2021-11-02 10:00:51 | 2021-11-02 10:00:59 | 0 |
| 6 | 104 | 9001 | 2021-11-02 11:00:28 | 2021-11-02 11:01:24 | 0 |
| 7 | 101 | 9003 | 2021-11-03 11:00:55 | 2021-11-03 11:01:24 | 0 |
| 8 | 104 | 9003 | 2021-11-03 11:00:45 | 2021-11-03 11:00:55 | 0 |
| 9 | 105 | 9003 | 2021-11-03 11:00:53 | 2021-11-03 11:00:59 | 0 |
| 10 | 101 | 9002 | 2021-11-04 11:00:55 | 2021-11-04 11:00:59 | 0 |
注：
- 次日留存率為當天新增的使用者數中第二天又活躍了的使用者數佔比。
- 如果in_time-進入時間和out_time-離開時間跨天了，在兩天裡都記為該使用者活躍過，結果按日期升序。
輸出示例：
示例資料的輸出結果如下
| dt | uv_left_rate |
|---|---|
| 2021-11-01 | 0.67 |
| 2021-11-02 | 1.00 |
| 2021-11-03 | 0.00 |
解釋：
11.01有3個使用者活躍101、102、103，均為新使用者，在11.02只有101、103兩個又活躍了，因此11.01的次日留存率為0.67；
11.02有104一位新使用者，在11.03又活躍了，因此11.02的次日留存率為1.00；
11.03有105一位新使用者，在11.04未活躍，因此11.03的次日留存率為0.00；
11.04沒有新使用者，不輸出。
分析思路
1、如何計算新使用者的次日留存率？
新使用者的次日留存率 = （當日活躍且次日仍活躍的新使用者數）/（當日活躍的新使用者數）
- 先把上面的活躍記錄按照日期、活躍使用者、次日留存使用者數、每天的次日留存率整理成下表：
日期 活躍使用者 次日留存新使用者 每天的次日留存率 1號 101、102、103 101、103 2/3 = 0.67 2號 101、103、104 104 1/1 = 1 3號 104、105 None 0
2、如何判斷當日活躍的新使用者次日又有活躍記錄？
同一個表看成兩個表做連線，使用
select distinct a.主鍵, a.活躍日期 as 當日, b.活躍日期 as 次日 from 新使用者活躍記錄表 a left join( select distinct 主鍵, 活躍日期 from 使用者活躍記錄表 ) b on a.主鍵 = b.主鍵 and a表和b表活躍日期的天數相差1天這樣就得到新使用者當日和次日的活躍記錄。
- 新使用者當日、次日活躍記錄表
使用者 當日 次日 101 1號 2號 102 1號 None 103 1號 2號 104 2號 3號 105 3號 None
3、計算每天的次日留存率
每天的次日留存率 = 次日留存使用者數 / 當日活躍使用者數在求每天的次日留存率時，注意需要分組，使用group by。
select 當日, count(次日)/ count(當日) as 每天的次日留存率 from 新使用者當日、次日活躍記錄表 group by 當日
求解程式碼：
with
temp as(
select
main.uid,
main.min_dt,
attr.dt
from(
#統計新增使用者
select
uid,
min(date(in_time)) as min_dt
from tb_user_log
group by uid
) main
left join(
#統計使用者的活躍記錄
(select
uid,
date(in_time) as dt
from tb_user_log)
union
(select
uid,
date(out_time) as dt
from tb_user_log)
) attr on main.uid = attr.uid and date_sub(dt, interval 1 day) = min_dt
)
#統計2021年11月每天新使用者的次日留存率,（保留2位小數）
select
min_dt as dt,
round(count(dt)/count(min_dt),2) as uv_left_rate
from temp
where min_dt like '2021-11%'
group by min_dt
order by min_dt
- 新使用者當日、次日活躍記錄表
device_id date1 date2
1 101 2021-11-01 2021-11-02
2 102 2021-11-01 None
3 103 2021-11-01 2021-11-02
4 104 2021-11-02 2021-11-03
5 105 2021-11-03 None
（3）計算復購率
問題：請統計零食類商品中復購率top3高的商品。
商品資訊表tb_product_info。（product_id-商品ID, shop_id-店鋪ID, tag-商品類別標籤, in_price-進貨價格, quantity-進貨數量, release_time-上架時間）
| id | product_id | shop_id | tag | int_ | quantity | release_time |
|---|---|---|---|---|---|---|
| 1 | 8001 | 901 | 零食 | 60 | 1000 | 2020-01-01 10:00:00 |
| 2 | 8002 | 901 | 零食 | 140 | 500 | 2020-01-01 10:00:00 |
| 3 | 8003 | 901 | 零食 | 160 | 500 | 2020-01-01 10:00:00 |
訂單總表tb_order_overall。（order_id-訂單號, uid-使用者ID, event_time-下單時間, total_amount-訂單總金額, total_cnt-訂單商品總件數, status-訂單狀態）
| id | order_id | uid | event_time | total_amount | total_cnt | status |
|---|---|---|---|---|---|---|
| 1 | 301001 | 101 | 2021-09-30 10:00:00 | 140 | 1 | 1 |
| 2 | 301002 | 102 | 2021-10-01 11:00:00 | 235 | 2 | 1 |
| 3 | 301011 | 102 | 2021-10-31 11:00:00 | 250 | 2 | 1 |
| 4 | 301003 | 101 | 2021-10-02 10:00:00 | 300 | 2 | 1 |
| 5 | 301013 | 105 | 2021-10-02 10:00:00 | 300 | 2 | 1 |
| 6 | 301005 | 104 | 2021-10-03 10:00:00 | 170 | 1 | 1 |
訂單明細表tb_order_detail。（order_id-訂單號, product_id-商品ID, price-商品單價, cnt-下單數量）
| id | order_id | product_id | price | cnt |
|---|---|---|---|---|
| 1 | 301001 | 8002 | 150 | 1 |
| 2 | 301011 | 8003 | 200 | 1 |
| 3 | 301011 | 8001 | 80 | 1 |
| 4 | 301002 | 8001 | 85 | 1 |
| 5 | 301002 | 8003 | 180 | 1 |
| 6 | 301003 | 8002 | 140 | 1 |
| 7 | 301003 | 8003 | 180 | 1 |
| 8 | 301013 | 8002 | 140 | 2 |
| 9 | 301005 | 8003 | 180 | 1 |
場景邏輯說明：
- 使用者將購物車中多件商品一起下單時，訂單總表會生成一個訂單（但此時未付款， status-訂單狀態- 訂單狀態為0表示待付款），在訂單明細表生成該訂單中每個商品的資訊；
- 當使用者支付完成時，在訂單總表修改對應訂單記錄的status-訂單狀態- 訂單狀態為1表示已付款；
- 若使用者退貨退款，在訂單總表生成一條交易總金額為負值的記錄（表示退款金額，訂單號為退款單號，訂單狀態為2表示已退款）。
注：復購率指使用者在一段時間內對某商品的重複購買比例，復購率越大，則反映出消費者對品牌的忠誠度就越高，也叫回頭率
此處我們定義：某商品復購率 = 近90天內購買它至少兩次的人數 ÷ 購買它的總人數
近90天指包含最大日期（記為當天）在內的近90天。結果中復購率保留3位小數，並按復購率倒序、商品ID升序排序
輸出示例：
示例資料的輸出結果如下：
| product_id | repurchase_rate |
|---|---|
| 8001 | 1.000 |
| 8002 | 0.500 |
| 8003 | 0.333 |
解釋：
商品8001、8002、8003都是零食類商品，8001只被使用者102購買了兩次，復購率1.000；
商品8002被101購買了兩次，被105購買了1次，復購率0.500；
商品8003被102購買兩次，被101和105各購買1次，復購率為0.333。
分析思路
1、如何計算每個商品的復購率？
某商品復購率 = 近90天內購買它至少兩次的人數 / 購買它的總人數
- 先把上面的購買記錄按照商品、購買使用者、購買次數、每個商品的復購率整理成下表：
商品 購買使用者 購買次數 8001 102 2 8002 101 2 8002 105 1 8003 101 1 8003 102 2 8003 105 1 由表可知每個商品的復購率分別為1.000、0.500、0.333。
2、統計每個商品的購買使用者和購買次數
統計每個貨物使用者的購買記錄。利用視窗函式根據貨號、使用者分組按購買時間排序select 商品, 使用者, 利用視窗函式根據貨號、使用者分組按購買時間排序 from 訂單明細表 left join 商品資訊表 using(主鍵) left join 訂單總表 using(主鍵)這樣就得到每個商品的使用者購買記錄排序表。
- 每個商品的使用者購買記錄排序表
商品 使用者 按照購買時間排序 8001 102 1 8001 102 2 8002 101 1 8002 101 2 8002 105 1 8003 101 1 8003 102 1 8003 102 2 8003 104 1
3、統計復購率top3高的商品及復購率
在求商品的復購率時，注意需要分組，使用group by。
select 商品, count(distinct if(排名 >= 2,使用者,null))/count(distinct 使用者) as 復購率 from 每個商品的使用者購買記錄排序表 group by 商品 order by 復購率 desc,商品 limit 3
求解程式碼：
#統計零食類商品中復購率top3高的商品
select
product_id,
round(count(distinct if(ranking >= 2,uid,null))/count(distinct uid),3) rate
from(
#統計每個貨物使用者的購買記錄；利用視窗函式根據貨號、使用者分組按購買時間排序
#在時間的處理上，使用datediff()和date_sub都可以
select
product_id,
uid,
row_number() over(partition by product_id,uid order by event_time) ranking
from tb_order_detail
left join tb_product_info using(product_id)
left JOIN tb_order_overall using (order_id)
where tag='零食'
and status=1
and datediff(date(
(select
max(event_time)
from tb_order_overall)),date(event_time)) < 90
) main
group by product_id
order by rate desc,product_id
limit 3
- 每個商品的使用者購買記錄排名表
product_id uid ranking
1 8001 102 1
2 8001 102 2
3 8002 101 1
4 8002 101 2
5 8002 105 1
6 8003 101 1
7 8003 102 1
8 8003 102 2
9 8003 104 1
三、適用場景
（1）留存率
留存率的作用
留存率是用於反映網站、網際網路應用或網路遊戲的運營情況的統計指標，其具體含義為在統計週期（周/月）內，每日活躍使用者數在第N日仍啟動該App的使用者數佔比的平均值。其中N通常取2、4、8、15、31，分別對應次日留存率、三日留存率、周留存率、半月留存率和月留存率。
留存率常用於反映使用者粘性，當N取值越大、留存率越高時，使用者粘性越高。
實際應用：
遊戲直播行業中，在資本的扶持下游戲直播行業穩定增長，鬥魚、虎牙雙巨頭格局形成。
受短影片進軍遊戲直播的衝擊，鬥魚、虎牙新安裝使用者規模同比有下降趨勢，但鬥魚新安裝轉化率同比微增。提高使用者留存率成為遊戲直播行業發展的關鍵，鬥魚、虎牙活躍使用者留存率同比均有不同程度的提高。
遊戲直播行業逐漸完善收入模式，變現能力不斷增強。 [1]
在藥物代謝動力學中指：每隔t小時體內留存藥量佔原藥量的比率。是除了半衰期（T1/2）外另一描述藥物消除規律的引數。
如何提高留存率？
- 1、營銷活動刺激
營銷活動能夠刺激使用者消費，而一個好的購物體驗絕對能夠大幅度提升使用者的留存和復購率。所以，有很多商家會透過優惠券、滿減等營銷活動，以此來拉進與顧客之間的距離，實現使用者的積累和留存。
常見的活動有拼團、打折等。另外可以設定限時活動，這樣除了給人一種緊迫感之外，也能在短時間裡聚集起使用者，調動使用者的活躍性，提升點選率。
- 2、簽到打卡機制
簽到玩法在很多app上也能看到，讓使用者連續簽到7 天、21 天、40 天等，將會獲得不同的獎品福利。
透過簽到活動讓使用者逐漸形成每天開啟app看一看的習慣。如果使用者使用體驗很好，那使用者留存率將直線上升，從而逐漸形成穩定的客戶群體。
- 3、優質內容互動
優質的內容是留住使用者的關鍵，使用者對你的內容有需求才會來使用，如果有一天內容不再優質了，使用者得不到想要的東西，自然來的就少了。
- 4、承諾留存獎勵：
這種方法常見於一些知識付費類的社群，設定二十天的課程，堅持聽完二十天並認真做好筆記的最終退還學費或者贈送一份超值大禮品。
- 5、履行承諾：
為了吸粉，為了引流，你的宣傳文案中，設定了懸念也好或是承諾了效果也好，一定丟擲了不少噱頭，當粉絲真的到來了之後，就是時候還債了，承諾了“進群領紅包”的就要發紅包了，吹噓了“十天瘦十斤”的就要運用專業只是為使用者進行介紹了。總而言之無論怎樣都得讓使用者覺得你就是文案中宣傳的那麼回事，不能讓使用者進群之後發現你是誇大宣傳的。
- 6、設定懸念：
對你承諾的內容做一份詳細的時間規劃，把你能提供的價值做一個詳略得當的拆分，並把價值提供的時間戰線控制得不長不短，最有吸引力的內容放在最開始展示，這樣以來能讓使用者剛進群就被吸引住，並對日後的內容有很大的期待，這樣以來自然不會輕易退群。
（2）復購率
復購率的作用
商品復購率指消費者對該品牌寶貝或者服務的重複購買次數，重複購買率越多，則反應出消費者對品牌的忠誠度和信賴度就越高，反之則越低。
復購率有兩種計算技巧：一種是所有購買過寶貝的顧客，以每個人人為獨立單位重複購買寶貝的次數；另一種是按交易計算，即重複購買交易次數與總交易次數的比值。
復購率高有哪些作用？
1、提高回頭率。當新的消費者對店鋪的整體情況有了一些瞭解，對寶貝的價效比進行分析之後，應該不會排斥對店鋪的關注。這樣一來，讓他們再一次關注店鋪，應該不會有哪些難度。
2、提高店鋪的權重。老客戶對店鋪的寶貝有一些瞭解，對店鋪服務也會很滿意。第二次再來購買時，幾乎不會給差評。多一個好評，對店鋪權重的提高會有很大幫助。
3、提高訪客價值。平時，店鋪需要花錢做直通車、鑽展以及淘寶聯盟等推廣，拼命吸引訪客，爭取提高轉化率。有了老客戶，一切都變得不一樣了。既然老客戶第二次或者第N次進店，一定是有所需求，會帶來訪客和成交量。說不定還會推薦新客戶，帶來更多的免費訪客。
4、提高客單價。對於新客戶來說，不太瞭解店鋪的寶貝以及服務，不敢輕易下手。但是老客戶不同，每一次看到套餐設定，復購率真的會提高。
5、如果使用者丟失嚴峻，缺少忠誠度，粘性低，這時候就要考慮“復購率”。
如何提高復購率？
- 1、剖析使用者行為資料
從購物行為來說，使用者購買行為45%的決議都是出於習慣，未曾經過思考。怎麼養成使用者的購買習慣，這就需要對使用者的全體概況進行了解，包括累計使用者、新增使用者、訂單量、閱讀量、購買使用者數、重複購買率等等。
分析這些資訊能夠看出：哪些使用者只閱讀，從未下單，甚至連下單期望都沒有；哪些使用者是忠實使用者：看的多，買的也多；哪些使用者有大量的收藏、加購行為，但並未下單；哪些使用者會有相似的購買、閱讀行為等等，瞭解這些資料也為制定營銷計劃提供有利的資料支撐。
- 2、解讀使用者行為資料，把握使用者喜愛
藉助資料剖析工具，調查使用者的閱讀、購買、收藏等重要行為資料，然後分析使用者購買了哪些商品，哪些商品看了（或許收藏了）但沒購買，明確使用者喜歡的商品。
- 3、根據使用者行為，進行精準推薦
根據會員行為，尋覓二次出售時機。此外，還能夠根據產品之間的相關性，進行產品推薦和套裝，提高單個訂單的出售額，新增出售時機。
- 4、滿意度調查，精準提升質量
對於已經購買過商品的老使用者來說，獲取使用者的聯絡手段是最簡單不過的了，所以需要提升使用者對於商品的用後體驗，比如定期對老使用者進行商品使用滿意調查，根據反饋提高商品的質量和客服人員的服務質量；
- 5、關注微信賬號
引導使用者關注店鋪或賣家的微信公眾賬號，以此可以第一時間獲取店鋪的最新訊息；
- 6、針對老使用者的店鋪活動
在節假日設定一些假日活動，店鋪活動，老使用者會有折上折之類的活動；
- 7、建立獎勵機制
如果新上線的商品或爆款商品，引導使用者參加好評曬照片，後臺設定抽獎或贈送超級會員卡等。
對於一些中小商家來說，想維護好老使用者，就要送一些優惠券。當他們第一次購買物品時，送一些代金券，可以刺激他們再一次來複購。不用給予太大的優惠力度，幾塊錢的代金券，會讓顧客感到很高興。
參考
例項題目均來自牛客網
百度百科 留存率
私域2.0關鍵詞：留存率
為什麼說復購率很重要？如何提升復購率增加會員忠誠度