碎語
- 學(xué)習(xí)的正態(tài)曲線：入門容易，精通難
- 積累的正態(tài)曲線：先越讀越多，后越讀越少
星中有數(shù)
客戶是公司最重要的資源，而想要獲得更多的用戶，我們可以通過兩種方式：要么拉新，要么留存，當(dāng)然，對于初創(chuàng)公司來說，拉新是必須的。但是，在互聯(lián)成熟的時(shí)期，拉新成本一路攀升，基本拉新的成本是維護(hù)老用的成本3倍。所以，留存很重要。
從另外一個(gè)角度來說，用戶在平臺(tái)上待的時(shí)間越長，對于平臺(tái)的價(jià)值越高，不管是直接價(jià)值和間接的價(jià)值。并且留存高證明平臺(tái)的價(jià)值越高。
所以，我們必須關(guān)注新用戶的留存。關(guān)于怎么去系統(tǒng)的分析留存，我里不做過多的介紹，主要是給大家從技術(shù)的角度講講，怎么通過sql來提取次日留存
SQL
select
reg_date,
count(reg.user_id) as reg_user_cnt,
count(case when login.user_id is not null then reg.user_id end ) as back_user_cnt
from
(select
user_id,
reg_date --注冊日期
from
register_data --注冊表
where
reg_date between '2020-01-01' and '2020-01-31'
)reg left join
(
select
user_id,
login_date --登陸日期
from
login_data --登陸表
where
login_date bewteen '2020-01-02' and '2020-02-01'
group by
user_id,
login_date
)login on reg.user_id = login.user_id and reg.reg_date = date_sub(login_date,1)
group by
reg_date
星星詳析
原理
- register_data:注冊表，login_data:登陸表
- 如果使用了多表連接的朋友們，你們應(yīng)該可以看出是一個(gè)左連接，此處的技巧在于我們在連接條件時(shí)使用了date_sub函數(shù)，這樣我們就以注冊表為主表，連接登陸表，與登陸表中第二天登陸用戶進(jìn)行連接，這樣：
- count(reg.user_id)就是求的注冊的用戶數(shù)
- count(case when login.user_id is not null then reg.user_id end )就是第二天回來的用戶
注意
- 一定要保證注冊表和登陸表中每天的用戶ID是唯一的，因?yàn)槲覀冎恍枰ＷC能夠記錄這個(gè)用戶是否回歸登陸了。
- 在注冊表(register_data)中,每天注冊的用戶ID一定是唯一的，所以不需要去重
- 而在登陸表(login_data)中,有可能用戶這個(gè)用戶一天登陸幾次，所以，需要對user_id和login_date去重，一般去重是使用distinct,此處我使用的是group by用法來保證唯一，這種方法的效率會(huì)高一些。
- 注冊日期(reg_date)和登陸日期(login_date)一定保證是標(biāo)準(zhǔn)的日期類型，因?yàn)槲覀冃枰褂萌掌诤瘮?shù)(date_sub),一般處理成"yyyy-MM-dd"("2020-02-01")形式，有的公司日期會(huì)記錄為"yyyyMMdd"(20200201)，此時(shí)我們可以這么做from_unixtime(unix_timestamp(string(login_date),'yyyyMMdd'),'yyyy-MM-dd'),使用時(shí)把login_date換成你公司的字段即可
- 大家可以好好理解一下，left join和case when的用法，這種用法需要你進(jìn)一步的理解左連接，其實(shí)就是沒有連接上就為空。
- SQL查詢最好進(jìn)行一對一的查詢，其次時(shí)一對多，盡量避免多對多查詢
結(jié)束語
如果我不能讓您看懂，那是我的問題，如果有疑問可以關(guān)注我，然后私聊我，我會(huì)盡最大的努力幫助你。如果覺得對你有幫助，請幫忙點(diǎn)贊/關(guān)注，謝謝！
