Uninstall attribution

Why should we use uninstall attribution?

Find the reasons why users uninstall and reduce user churn

User structure composition

• User

• Surviving users

  • Active Users

  – Silent User

• Uninstall user

Uninstallation hypothesis

• Add new user
• Uninstall before starting:
• Unopened & uninstalled events
• Not experiencing the full product features
• Novice experience funnel (all pages before the novice reaches the results page)
  • 发生该事件，且未发生该事件可能的后续事件的卸载用户数/当日未体验功能的卸载用户卸载用户数
• Uninstalled after trying it, not satisfied with the function:

-Experience the function, uninstall and compare

• 体验该功能后，且未体验其它功能的卸载用户数 / 当日已体验功能的的卸载用户数
• Uninstallation time judgment
• After using a function, uninstall it immediately. The event time used by the function and the uninstallation time are less than 1 minute

-Old Users

• Infrequently used
• Among users who have uninstalled the function and used it: the average of the time of the last results page and the time of uninstallation. The longer the distance, the longer it has been useless.
• Among users who have uninstalled the function and have not used it: the time since the uninstall was added.
• Tired of it
• Frequent use, but found no effect
• Among users who have uninstalled and experienced the function: the average frequency of result pages for a single function on that day. Used too often and found it useless
• Number of notification reminders
• Among users who have uninstalled and experienced the function: the average number of ad impressions on that day, a high frequency indicates that it may be due to advertising
• Others
• Feedback

Query the number of uninstalls of new users in 24 hours

```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

```

Added in 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

```

Users who uninstalled and did not fully experience the functions

```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```

New users who have experienced the function and uninstalled it within 24 hours Frequency of notification pop-ups

```

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

```

Query events that occurred 1 minute before uninstallation

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

The last event that occurred before uninstallation

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 new uninstall notification display

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

New notification

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

Looking at the uninstall rate from the time dimension

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

View notifications by time period

24h new uninstall notification display

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