數(shù)倉：用戶行為類指標一網(wǎng)打盡

數(shù)倉：用戶行為類指標一網(wǎng)打盡前言用戶行為分析是對用戶在產(chǎn)品或觸點上產(chǎn)生的行為及行為背后的數(shù)據(jù)進行分析，通過構(gòu)建用戶行為數(shù)據(jù)分析體系或者用戶畫像，來改變產(chǎn)品、營銷、運營決策，實現(xiàn)精細化運營，指導業(yè)務(wù)增長?？傊?，很重要。先來看下用戶類行為指標說明，然后下面詳解常寫的指標。僅供參考，實際開發(fā)邏輯還是按業(yè)務(wù)需求為標準。指標名稱解釋說明新增用戶首次聯(lián)網(wǎng)使用應用的用戶，如果一個用戶首次打開某APP，那這個用戶定義為新增用戶：卸載再安裝的設(shè)備，不會被算作一次新增，新增用戶包括日新增用戶，周新增用戶，月新增用戶?；钴S用戶打開應用的用戶即為活躍用戶，不考慮用戶的使用情況，每天一臺設(shè)備打開多次會話被記為一個活躍用戶周（月）活躍用戶用戶以設(shè)備作為判斷標準，在移動統(tǒng)計中，每個獨立設(shè)備認為是一個獨立用戶。安卓系統(tǒng)根據(jù)imei號，IOS系統(tǒng)根據(jù)openUDUID來標識一個獨立用戶，每部手機一個用戶。沉默用戶用戶僅在安裝當天（次日）啟動一次，后續(xù)時間無再啟動的行為。該指標可以反應新增用戶質(zhì)量和用戶與app的匹配程度版本分布不同版本的周內(nèi)新增用戶數(shù)，活躍用戶數(shù)和啟動次數(shù)。利于判斷app各個版本之間的優(yōu)劣和用戶行為習慣。本周回流用戶上周未啟動過的應用，本周啟動了應用的用戶連續(xù)n周活躍用戶連續(xù)n周，每周至少啟動一次。忠誠用戶連續(xù)活躍5周以上的用戶連續(xù)活躍用戶連續(xù)兩周及以上活躍的用戶近期流失用戶連續(xù)n（2<=n<=4）周沒有啟動應用的用戶。（第n+1周沒有啟動過）留存用戶某段時間內(nèi)的新增用戶，經(jīng)過一段時間后，仍然使用應用的被認作是留存用戶：這部分用戶占當時新增用戶的比例即是留存率用戶新鮮度每天啟動應用的新老用戶比例，即新增用戶數(shù)占活躍用戶數(shù)的比例單次使用時長每次啟動使用的時間長度日使用時長累計一天內(nèi)使用時間長度用戶活躍描述判斷業(yè)務(wù)健康程度和發(fā)展趨勢的一個重要指標。業(yè)務(wù)指標場景舉例最近一個周/月內(nèi)，有過連續(xù)活躍n天的人數(shù)。最近一周/月內(nèi)，每個用戶的平均活躍天數(shù)。最近一周/月內(nèi)，最大沉默天數(shù)超n天的用戶數(shù)。最近七天內(nèi)連續(xù)三天活躍用戶數(shù)。分析思路以日明細，獲取周，月用戶明細。活躍用戶按天明細表dws_uv_detail_day。create

table

dws_uv_detail_day

(

`mid_id`

string

COMMENT

'設(shè)備唯一標識',

`user_id`

string

COMMENT

'用戶標識',

`app_time`

string

COMMENT

'客戶端日志產(chǎn)生時的時間',

.

.

.

.

.

.

(省略其他字段)

)

partitioned

by(dt

string)

.

.

.

.

.

.活躍用戶按周明細表dws_uv_detail_wk。create

table

dws_uv_detail_wk(

`mid_id`

string

COMMENT

'設(shè)備唯一標識',

`user_id`

string

COMMENT

'用戶標識',

`app_time`

string

COMMENT

'客戶端日志產(chǎn)生時的時間',

`monday_date`

string

COMMENT

'周一日期',

`sunday_date`

string

COMMENT

'周日日期'

,

.

.

.

.

.

.

(省略其他字段)

)

PARTITIONED

BY

(`wk_dt`

string)

.

.

.

.

.

.案例最近七天內(nèi)連續(xù)三天活躍用戶數(shù)思路。（1）查詢出最近7天的活躍用戶，并對用戶活躍日期進行排名。（2）計算用戶活躍日期及排名之間的差值。（3）對同用戶及差值分組，統(tǒng)計差值個數(shù)。（4）將差值相同個數(shù)大于等于3的數(shù)據(jù)取出，然后去重，即為連續(xù)3天及以上活躍的用戶數(shù)。select

'2022-01-04',

concat(date_add('2022-01-04',-6),'_','2022-01-04'),

count(*)

from

(

select

uid_id

from

(

select

uid_id

from

(

select

uid_id,

date_sub(dt,rank)

date_diff

from

(

select

uid_id,

dt,

rank()

over(partition

by

uid_id

order

by

dt)

rank

from

dws_uv_detail_day

where

dt>=date_add('2022-01-04',-6)

and

dt<='2022-01-04'

)t1

)t2

group

by

uid_id,date_diff

having

count(*)>=3

)t3

group

by

uid_id

)t4;用戶（設(shè)備）新增描述用戶第一次打開應用，那這個用戶定義為新增用戶。業(yè)務(wù)指標場景舉例日新增用戶。周新增用戶。月新增用戶。分析思路每日活躍設(shè)備leftjoin每日新增產(chǎn)生的結(jié)果，每日新增的設(shè)備id為null，則為新增。案例每日新增用戶（設(shè)備）dws_uv_detail_day:每日活躍用戶表

dws_new_mid_day:每日新增設(shè)備表

select

ud.mid_id,

ud.user_id

from

dws_uv_detail_day

ud

left

join

dws_new_mid_day

nm

on

ud.mid_id=nm.mid_id

where

ud.dt='2022-01-04'

and

nm.mid_id

is

null;用戶留存描述「用戶留存」：某段時間內(nèi)的新增活躍用戶，經(jīng)過一段時間后，有繼續(xù)訪問或者使用應用被稱為留存用戶。留存可以看成是一個用戶在今天出現(xiàn)，在明天也出現(xiàn)，那就是次日留存。一個用戶在今天出現(xiàn)，在后天出現(xiàn)，那就是2日留存?！噶舸媛省梗毫舸嬗脩粽夹略龌钣脩舻谋壤I(yè)務(wù)指標場景舉例次日留存率3日留存率7日留存率分析思路統(tǒng)計2022-01-04日，以統(tǒng)計2022-01-03日留存率為業(yè)務(wù)需求分析：「2022-01-03設(shè)備留存率=01-03日新增設(shè)備且01-04日活躍的占

01-03日新增設(shè)備。」（1）01-03日新增設(shè)備01-04日活躍表leftjoin每日新增表，新增表id為null的為新增設(shè)備。（2）01-03日新增設(shè)備且01-04日活躍的01-03日新增join01-04的活躍。（3）代入上面公式案例計算前1天新用戶訪問留存明細dws_uv_detail_day:每日活躍用戶表

dws_user_retention_day：每日留存用戶明細表

dws_new_mid_day:每日新增設(shè)備表

select

nm.mid_id

mid_id,

nm.user_id

user_id,

ud.dt

dt,

1

retention_day

//

留存天數(shù)

from

dws_uv_detail_day

ud

join

dws_new_mid_day

nm

on

ud.mid_id

=nm.mid_id

where

ud.dt='2022-01-04'

and

nm.create_date=date_add('2022-01-04',-1)沉默用戶描述指的是只在安裝當天啟動過，且啟動時間是在一周/月（某段時間）前。分析思路（1）按照設(shè)備id對日活表分組（2）過濾只是在啟動當天用過的（3）時間在某個時間前的案例7日沉默用戶（設(shè)備）數(shù)。dws_uv_detail_day:每日活躍用戶表

select

'2022-01-04'

dt,

count(*)

silent_count

from

(

select

mid_id

from

dws_uv_detail_day

where

dt<='2022-01-04'

group

by

mid_id

having

count(*)=1

and

min(dt)<=date_add('2022-01-04',-6)

)

t1;回流用戶描述相當于回頭客的意思，以前用過某物，過一段時間再次使用某物，這樣的用戶就是回流用戶。用戶已經(jīng)記錄n日以及n日以上未有登錄（即先認定這部分用戶已經(jīng)流失），而在今天有登錄，那在今天這部分用戶稱之為回流用戶。業(yè)務(wù)指標場景舉例（時間周期）回流=（時間周期活躍-（時間周期新增-（上個時間周期）活躍如：本周回流=本周活躍-本周新增-上周活躍。分析思路本周活躍leftjoin本周新增leftjoin上周活躍。條件是：本周新增id為null，上周活躍id為null。案例本周回流dws_uv_detail_wk:每周活躍用戶表

dws_new_mid_day:每日新增設(shè)備表

mid_id：設(shè)備id

select

'2022-01-04'

dt,

concat(date_add(next_day('2022-01-04','MO'),-7),'_',date_add(next_day('2022-01-04','MO'),-1))

wk_dt,

count(*)

from

(

select

t1.mid_id

from

(

select

mid_id

from

dws_uv_detail_wk

where

wk_dt=concat(date_add(next_day('2022-01-04','MO'),-7),'_',date_add(next_day('2022-01-04','MO'),-1))

)t1

left

join

(

select

mid_id

from

dws_new_mid_day

where

create_date<=date_add(next_day('2022-01-04','MO'),-1)

and

create_date>=date_add(next_day('2022-01-04','MO'),-7)

)t2

on

t1.mid_id=t2.mid_id

left

join

(

select

mid_id

from

dws_uv_detail_wk

where

wk_dt=concat(date_add(next_day('2022-01-04','MO'),-7*2),'_',date_add(next_day('2022-01-04','MO'),-7-1))

)t3

on

t1.mid_id=t3.mid_id

where

t2.mid_id

is

null

and

t3.mid_id

is

null

)t4;流失用戶描述最近n天（自己定義）未登錄我們稱之為流失用戶。案例流失用戶數(shù)：最近7天未登錄用戶。dws_uv_detail_day:每日活躍用戶表

select

'2022-01-04',

count(*)

from

(

select

mid_id

from

dws_uv_detail_day

group

by

mid_id

having

max(dt)<=date_add('2022-01-04',-7)

)t1;瀏覽數(shù)與訪客數(shù)瀏覽量（PV）：網(wǎng)站各頁面被查看的次數(shù)。用戶多次打開或刷新同一個頁面，該指標值累加。訪客數(shù)(UV)：網(wǎng)站各頁面的訪問人數(shù)。所選時間段內(nèi)，同一訪客多次訪問會進行去重。這兩個指標沒啥說的，count取值或者去重count即可。用戶行為漏斗分析描述舉例說明訂單轉(zhuǎn)化率訪問到下單轉(zhuǎn)化率=下單人數(shù)/日活躍人數(shù)下單到支付轉(zhuǎn)化率