卸载归因

为什么要坐卸载归因

找到用户卸载的原因，减少用户的流失

用户结构组成

• 用户
  • 存活用户
    • 活跃用户
    • 静默用户
  • 卸载用户

卸载假设

• 新增用户
  • 未启动就卸载：
    • 未打开 & 卸载事件
  • 未体验完整产品功能
    • 新手体验漏斗（新手触达结果页前的所有页面）
      • 发生该事件，且未发生该事件可能的后续事件的卸载用户数/当日未体验功能的卸载用户卸载用户数
  • 体验后卸载，对功能不满意：
    • 体验功能，卸载比较
      • 体验该功能后，且未体验其它功能的卸载用户数 / 当日已体验功能的的卸载用户数
    • 卸载时间判断
      • 使用某功能后，立即卸载，功能使用的事件时间时间与卸载时间小于1min
• 老用户
  • 不常使用
    • 卸载的使用过功能的用户中：最后一次结果页的时间与卸载的时间的均值，距离越久说明越长时间没用
    • 卸载的未使用过功能的用户中：卸载距离新增的时间。
  • 厌倦了
    • 频繁使用，发现没什么效果
      • 卸载且体验过功能的用户中：当日单一功能的结果页频次的均值。使用频次过高，发现并没有用
    • 通知提示的次数
      • 卸载且体验过功能的用户中：当日广告展示次数的均值，频次高说明可能是广告的原因
  • 其它
    • 意见反馈

查询新增用户24h卸载数

```SELECT

DATE(TIMESTAMP_ADD(TIMESTAMP_MICROS( AFO.app_first_open_time ), INTERVAL -8 HOUR)) AS D,

AFO.country as country,

COUNT(DISTINCT AFO.users_id) AS users

FROM (

SELECT

user_pseudo_id AS users_id,

event_timestamp AS app_first_open_time,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name = 'first_open'

and geo.country in( 'United States','Japan','South Korea')

AND app_info.id = 'com.cleanteam.oneboost'

GROUP BY

users_id,

app_first_open_time,

country) AFO

INNER JOIN (

SELECT

user_pseudo_id AS users_id,

event_timestamp AS app_remove_time,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name = 'app_remove'

and geo.country in( 'United States','Japan','South Korea')

AND app_info.id = 'com.cleanteam.oneboost'

GROUP BY

users_id,

app_remove_time,

country) AR

ON

(AFO.users_id = AR.users_id)

WHERE

AR.app_remove_time - AFO.app_first_open_time < 24606010001000

AND AR.app_remove_time - AFO.app_first_open_time > 0

group by D,country

order by D

```

24h新增

```SELECT

DATE(TIMESTAMP_ADD(TIMESTAMP_MICROS( event_timestamp ), INTERVAL -8 HOUR)) AS D,

geo.country as country,

count(distinct user_pseudo_id) AS users

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name = 'first_open'

and geo.country in( 'United States','Japan','South Korea')

AND app_info.id = 'com.cleanteam.oneboost'

GROUP BY

D,

country

```

卸载且未完整体验完功能的用户

```SELECT

DATE(TIMESTAMP_ADD(TIMESTAMP_MICROS( AFO.app_first_open_time ), INTERVAL -8 HOUR)) AS D,

AFO.country as country,

COUNT(DISTINCT AFO.users_id) AS users

FROM (

select

a.users_id,

a.app_first_open_time,

a.country

from

(SELECT

user_pseudo_id AS users_id,

event_timestamp AS app_first_open_time,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name = 'first_open'

and geo.country in( 'United States','Japan','South Korea')

AND app_info.id = 'com.cleanteam.oneboost'

GROUP BY

users_id,

app_first_open_time,

country) as a

left join

(SELECT

user_pseudo_id AS users_id,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name in ('result_page')

and geo.country in( 'United States','Japan','South Korea')

AND app_info.id = 'com.cleanteam.oneboost'

GROUP BY

users_id,

country) as b

on a.users_id=b.users_id

where b.users_id is null

) AFO

INNER JOIN (

SELECT

user_pseudo_id AS users_id,

event_timestamp AS app_remove_time,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name = 'app_remove'

and geo.country in( 'United States','Japan','South Korea')

AND app_info.id = 'com.cleanteam.oneboost'

GROUP BY

users_id,

app_remove_time,

country) AR

ON

(AFO.users_id = AR.users_id)

WHERE

AR.app_remove_time - AFO.app_first_open_time < 24606010001000

AND AR.app_remove_time - AFO.app_first_open_time > 0

group by D,country

order by D```

体验过功能并24h卸载的新增用户 通知弹窗频次

```

SELECT

DATE(TIMESTAMP_ADD(TIMESTAMP_MICROS( event_timestamp ), INTERVAL -8 HOUR)) AS D,

count(user_pseudo_id)AS event,

count(distinct user_pseudo_id)AS users,

geo.country as country,

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name in ('notice_cleanjunk_show','notice_boost_show','notice_saver_show','notice_security_show','batteryoptimize_use_show','notice_cpucooler_show')

and geo.country in( 'United States','Japan','South Korea')

and user_pseudo_id in((SELECT

AFO.users_id as user_pseudo_id

FROM (

select

a.users_id,

a.app_first_open_time,

a.country

from

(SELECT

user_pseudo_id AS users_id,

event_timestamp AS app_first_open_time,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name = 'first_open'

and geo.country in( 'United States','Japan','South Korea')

GROUP BY

users_id,

app_first_open_time,

country) as a

inner join

(SELECT

user_pseudo_id AS users_id,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name in ('result_page')

and geo.country in( 'United States','Japan','South Korea')

GROUP BY

users_id,

country) as b

on a.users_id=b.users_id

) AFO

INNER JOIN (

SELECT

user_pseudo_id AS users_id,

event_timestamp AS app_remove_time,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name = 'app_remove'

and geo.country in( 'United States','Japan','South Korea')

GROUP BY

users_id,

app_remove_time,

country) AR

ON

(AFO.users_id = AR.users_id)

WHERE

AR.app_remove_time - AFO.app_first_open_time < 24606010001000

AND AR.app_remove_time - AFO.app_first_open_time > 0)

GROUP BY

D,

country

order by D

```

查询卸载前1min发生的事件

SELECT

DATE(TIMESTAMP_ADD(TIMESTAMP_MICROS( AFO.app_first_open_time ), INTERVAL -8 HOUR)) AS D,

AFO.country as country ,

AR.event_name as event_name,

COUNT(DISTINCT AFO.users_id) AS users

FROM (

SELECT

user_pseudo_id AS users_id,

event_timestamp AS app_first_open_time,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name = 'first_open'

and geo.country in( 'United States','Japan','South Korea')

GROUP BY

users_id,

app_first_open_time,

country) AFO

INNER JOIN (

select a.app_remove_time as app_remove_time,

b.last_time as last_time,

a.country as country,

a.users_id as users_id,

b.event_name as event_name

from

(SELECT

user_pseudo_id AS users_id,

event_timestamp AS app_remove_time,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name = 'app_remove'

and geo.country in( 'United States','Japan','South Korea')

GROUP BY

users_id,

app_remove_time,

country) as a

inner join

(SELECT

user_pseudo_id AS users_id,

event_timestamp AS last_time,

geo.country as country,

event_name

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and geo.country in( 'United States','Japan','South Korea')

GROUP BY

users_id,

last_time,

country,event_name)as b

on a.users_id=b.users_id

where a.app_remove_time-b.last_time<=6010001000*10

) AR

ON

(AFO.users_id = AR.users_id)

WHERE

AR.app_remove_time - AFO.app_first_open_time < 24606010001000

AND AR.app_remove_time - AFO.app_first_open_time > 0

group by D,country,event_name

order by D

卸载前发生的最后一个事件

select

b.event_name as event_name,

a.users_id as users_id,

b.last_time as last_time

from

(SELECT

user_pseudo_id AS users_id,

event_timestamp AS app_remove_time,

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200328'

AND '20200401'

and event_name = 'app_remove'

and geo.country in( 'United States')

and (event_timestamp-user_first_touch_timestamp )<=10001000606024

GROUP BY

users_id,

app_remove_time

) as a

inner join

(SELECT

user_pseudo_id AS users_id,

event_timestamp AS last_time,

event_name

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200328'

AND '20200401'

and geo.country in( 'United States')

GROUP BY

users_id,event_name,last_time

)as b

on a.users_id=b.users_id

24h的新增卸载通知展示

select

a.D as D,

a.country as country,

count(distinct b.users_id) as user,

count(b.users_id) as event

from

(SELECT

DATE(TIMESTAMP_ADD(TIMESTAMP_MICROS( user_first_touch_timestamp), INTERVAL -8 HOUR)) AS D,

user_pseudo_id AS users_id,

event_timestamp AS app_remove_time,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name = 'app_remove'

and geo.country in( 'United States','Japan','South Korea')

AND app_info.id = 'com.cleanteam.oneboost'

and (event_timestamp-user_first_touch_timestamp )<=10001000606024

) as a

inner join

(SELECT

user_pseudo_id AS users_id,

event_timestamp AS last_time,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name in ('notice_cleanjunk_show','notice_boost_show','notice_saver_show','notice_security_show','batteryoptimize_use_show','notice_cpucooler_show')

and geo.country in( 'United States','Japan','South Korea')

AND app_info.id = 'com.cleanteam.oneboost'

)as b

on a.users_id=b.users_id

group by D,country

order by D

新增通知

select

DATE(TIMESTAMP_ADD(TIMESTAMP_MICROS( b.D), INTERVAL -8 HOUR)) AS D,

a.country as country,

count(distinct b.user) as user,

count(b.user) as event

from

(SELECT

event_timestamp as D,

user_pseudo_id as user,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name in ('notice_cleanjunk_show','notice_boost_show','notice_saver_show','notice_security_show','batteryoptimize_use_show','notice_cpucooler_show')

and geo.country in( 'United States','Japan','South Korea')

AND app_info.id = 'com.cleanteam.oneboost'

) as a

inner join

(SELECT

event_timestamp as D,

user_pseudo_id as user,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name in ('first_open')

and geo.country in( 'United States','Japan','South Korea')

AND app_info.id = 'com.cleanteam.oneboost'

) as b

on a.user=b.user

where a.D-b.D<=10001000606024

group by D,country

order by D

时间维度来看卸载率

SELECT

DATE(TIMESTAMP_ADD(TIMESTAMP_MICROS( AFO.app_first_open_time ), INTERVAL -8 HOUR)) AS D,

(CASE

WHEN (AR.app_remove_time - AFO.app_first_open_time < 5601000*1000) THEN '10min'

WHEN (AR.app_remove_time - AFO.app_first_open_time > 56010001000 AND AR.app_remove_time - AFO.app_first_open_time < 106010001000) THEN '10min'

WHEN (AR.app_remove_time - AFO.app_first_open_time > 106010001000 AND AR.app_remove_time - AFO.app_first_open_time < 206010001000) THEN '20min'

WHEN (AR.app_remove_time - AFO.app_first_open_time > 206010001000 AND AR.app_remove_time - AFO.app_first_open_time < 306010001000) THEN '30min'

WHEN (AR.app_remove_time - AFO.app_first_open_time > 306010001000 AND AR.app_remove_time - AFO.app_first_open_time < 360601000*1000) THEN '3h'

WHEN (AR.app_remove_time - AFO.app_first_open_time > 3606010001000 AND AR.app_remove_time - AFO.app_first_open_time < 6606010001000) THEN '6h'

WHEN (AR.app_remove_time - AFO.app_first_open_time > 666010001000 AND AR.app_remove_time - AFO.app_first_open_time < 6606010001000) THEN '12h'

ELSE 'Other'

END

) AS seg,

AFO.country as country,

COUNT(DISTINCT AFO.users_id) AS users

FROM (

SELECT

user_pseudo_id AS users_id,

event_timestamp AS app_first_open_time,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name = 'first_open'

and geo.country in( 'United States','Japan','South Korea')

AND app_info.id = 'com.cleanteam.oneboost'

GROUP BY

users_id,

app_first_open_time,

country) AFO

INNER JOIN (

SELECT

user_pseudo_id AS users_id,

event_timestamp AS app_remove_time,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name = 'app_remove'

and geo.country in( 'United States','Japan','South Korea')

AND app_info.id = 'com.cleanteam.oneboost'

GROUP BY

users_id,

app_remove_time,

country) AR

ON

(AFO.users_id = AR.users_id)

WHERE

AR.app_remove_time - AFO.app_first_open_time < 24606010001000

AND AR.app_remove_time - AFO.app_first_open_time > 0

group by D,country,seg

order by D

分时间段看通知

24h的新增卸载通知展示

select

a.D as D,

a.country as country,

(CASE

WHEN (a.app_remove_time - a.app_first_open_time < 5601000*1000) THEN '10min'

WHEN (a.app_remove_time - a.app_first_open_time > 56010001000 AND a.app_remove_time - a.app_first_open_time < 106010001000) THEN '10min'

WHEN (a.app_remove_time - a.app_first_open_time > 106010001000 AND a.app_remove_time - a.app_first_open_time < 206010001000) THEN '20min'

WHEN (a.app_remove_time - a.app_first_open_time > 206010001000 AND a.app_remove_time - a.app_first_open_time < 306010001000) THEN '30min'

WHEN (a.app_remove_time - a.app_first_open_time > 306010001000 AND a.app_remove_time - a.app_first_open_time < 360601000*1000) THEN '3h'

WHEN (a.app_remove_time - a.app_first_open_time > 3606010001000 AND a.app_remove_time - a.app_first_open_time < 6606010001000) THEN '6h'

WHEN (a.app_remove_time - a.app_first_open_time > 666010001000 AND a.app_remove_time - a.app_first_open_time < 6606010001000) THEN '12h'

ELSE 'Other'

END

) AS seg,

count(distinct b.users_id) as user,

count(b.users_id) as event

from

(SELECT

DATE(TIMESTAMP_ADD(TIMESTAMP_MICROS( user_first_touch_timestamp), INTERVAL -8 HOUR)) AS D,

user_first_touch_timestamp as app_first_open_time,

user_pseudo_id AS users_id,

event_timestamp AS app_remove_time,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name = 'app_remove'

and geo.country in( 'United States','Japan','South Korea')

AND app_info.id = 'com.cleanteam.oneboost'

and (event_timestamp-user_first_touch_timestamp )<=10001000606024

) as a

inner join

(SELECT

user_pseudo_id AS users_id,

event_timestamp AS last_time,

geo.country as country

FROM

`one-booster-55090.analytics_214127162.events_*`

WHERE

_TABLE_SUFFIX BETWEEN '20200325'

AND '20200401'

and event_name in ('notice_cleanjunk_show','notice_boost_show','notice_saver_show','notice_security_show','batteryoptimize_use_show','notice_cpucooler_show')

and geo.country in( 'United States','Japan','South Korea')

AND app_info.id = 'com.cleanteam.oneboost'

)as b

on a.users_id=b.users_id

group by D,country,seg

order by D