沒錯，留存的問題還沒有寫完，之前兩篇把日、周、月當(dāng)期活躍用戶在后續(xù)周期的留存率問題解決了。但是還有個非常重要的指標(biāo)，當(dāng)期新增用戶的留存率，這個指標(biāo)也是很有價值的，我們必須要關(guān)注不同日期拉新用戶的質(zhì)量如何，看看不同時期新用戶的后續(xù)留存情況，對后續(xù)拉新的時間選擇也是有參考價值的。

其實實現(xiàn)也很簡單，只需要在之前的基礎(chǔ)上，先把當(dāng)期的首次登陸用戶找出來就行了。實現(xiàn)方式是，按照用戶聚合，然后取日期最小值就能取出每個用戶首次登陸日期了，SQL語句如下↓

SELECT
  user_id,
  DATE_FORMAT(min(time), "%Y-%m-%d" ) AS date 
FROM
  liucun
GROUP BY
  user_id

然后就以此為基礎(chǔ)，通過左連接把用戶表格再連接一次，判斷與首次登陸的日期相差多少天就行了，就能判斷是第N天有活躍，就能計算N日留存和留存率了，SQL語句和結(jié)果如下↓

SELECT 
  t1.*,
  DATE_FORMAT(lc1.time,"%Y-%m-%d") AS lcdate,
  DATEDIFF(date(lc1.time),date(t1.date)) daydiff
FROM
  (SELECT
    user_id,
    DATE_FORMAT(min(time), "%Y-%m-%d" ) AS date 
  FROM
    liucun
  GROUP BY
    user_id) as t1
LEFT JOIN liucun as lc1 on lc1.user_id = t1.user_id

后面就和之前思路一樣了，就可以求出日留存率情況了，SQL語句如下，解釋可以看前面兩篇。

SELECT
  date,
  COUNT(DISTINCT user_id) 當(dāng)日新增戶數(shù),
  COUNT(DISTINCT CASE WHEN daydiff=1 THEN user_id ELSE NULL END) 次日用戶數(shù),
  CONCAT(ROUND(COUNT(DISTINCT CASE WHEN daydiff=1 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 次日留存率,
  CONCAT(ROUND(COUNT(DISTINCT CASE WHEN daydiff=2 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 三日留存率,
  CONCAT(ROUND(COUNT(DISTINCT CASE WHEN daydiff=6 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 七日留存率
  FROM
  (SELECT 
  t1.*,
  DATE_FORMAT(lc1.time,"%Y-%m-%d") AS lcdate,
  DATEDIFF(date(lc1.time),date(t1.date)) daydiff
FROM
  (SELECT
    user_id,
    DATE_FORMAT(min(time), "%Y-%m-%d" ) AS date 
  FROM
    liucun
  GROUP BY
    user_id) as t1
LEFT JOIN liucun as lc1 on lc1.user_id = t1.user_id) temp
GROUP BY
  date

然后按月實現(xiàn)方式和上一篇一樣的思路，關(guān)聯(lián)一個輔助表就行了，這里不詳細(xì)解釋了，可以參考上一篇，完整SQL語句和結(jié)果如下↓

SELECT
  月份,
  COUNT(DISTINCT user_id) 當(dāng)月新增用戶,
  CONCAT(ROUND(COUNT(DISTINCT CASE WHEN mdiff=1 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 次月留存率,
  CONCAT(ROUND(COUNT(DISTINCT CASE WHEN mdiff=2 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 兩月留存率,
  CONCAT(ROUND(COUNT(DISTINCT CASE WHEN mdiff=3 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 三月留存率
  FROM
  (SELECT 
  t1.*,
  DATE_FORMAT(t1.date,"%Y-%m") 月份,
  DATE_FORMAT(lc1.time,"%Y-%m-%d") AS lcdate,
  d1.monthnum m0,
  d2.monthnum m1,
  d2.monthnum-d1.monthnum mdiff
FROM
  (SELECT
    user_id,
    DATE_FORMAT(min(time), "%Y-%m-%d" ) AS date 
  FROM
    liucun
  GROUP BY
    user_id) as t1
  LEFT JOIN liucun as lc1 on lc1.user_id = t1.user_id
  LEFT JOIN date as d1 ON date(t1.date)=d1.日期
  LEFT JOIN date as d2 ON date(lc1.time)=d2.日期) temp
GROUP BY
  月份

那么按周的留存率也是一樣的，SQL語句和結(jié)果如下↓

SELECT
  周次,
  COUNT(DISTINCT user_id) 當(dāng)周新增用戶,
  CONCAT(ROUND(COUNT(DISTINCT CASE WHEN wdiff=1 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 次周留存率,
  CONCAT(ROUND(COUNT(DISTINCT CASE WHEN wdiff=2 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 兩周留存率,
  CONCAT(ROUND(COUNT(DISTINCT CASE WHEN wdiff=3 THEN user_id ELSE NULL END)/COUNT(DISTINCT user_id)*100,2),"%") 三周留存率
  FROM
  (SELECT 
  t1.*,
  d1.周次 周次,
  DATE_FORMAT(lc1.time,"%Y-%m-%d") AS lcdate,
  d2.weeknum-d1.weeknum wdiff
FROM
  (SELECT
    user_id,
    DATE_FORMAT(min(time), "%Y-%m-%d" ) AS date 
  FROM
    liucun
  GROUP BY
    user_id) as t1
  LEFT JOIN liucun as lc1 on lc1.user_id = t1.user_id
  LEFT JOIN date as d1 ON date(t1.date)=d1.日期
  LEFT JOIN date as d2 ON date(lc1.time)=d2.日期) temp
GROUP BY
  周次

End

◆ PowerBI開場白
◆ Python高德地圖可視化
◆ Python不規(guī)則條形圖