til.duyet.net
Search…
Bigquery - Sample queries for audiences based
These queries return the number of users in the audience. If you'd like to get the list of user IDs in the audience instead, then remove the outermost COUNT() function, e.g., COUNT(DISTINCT user_id) -

N-day active users

1
/**
2
* Builds an audience of N-Day Active Users.
3
*
4
* N-day active users = users who have logged at least one user_engagement
5
* event in the last N days.
6
*/
7
SELECT
8
COUNT(DISTINCT user_id) AS n_day_active_users_count
9
FROM
10
-- PLEASE REPLACE WITH YOUR TABLE NAME.
11
`YOUR_TABLE.events_*`
12
WHERE
13
event_name = 'user_engagement'
14
-- Pick events in the last N = 20 days.
15
AND event_timestamp >
16
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY))
17
-- PLEASE REPLACE WITH YOUR DESIRED DATE RANGE.
18
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
19
​
Copied!

N-day inactive users

1
/**
2
* Builds an audience of N-Day Inactive Users.
3
*
4
* N-Day inactive users = users in the last M days who have not logged a
5
* user_engagement event in the last N days where M > N.
6
*/
7
SELECT
8
COUNT(DISTINCT MDaysUsers.user_id) AS n_day_inactive_users_count
9
FROM
10
(
11
SELECT
12
user_id
13
FROM
14
/* PLEASE REPLACE WITH YOUR TABLE NAME */
15
`YOUR_TABLE.events_*`
16
WHERE
17
event_name = 'user_engagement'
18
/* Has engaged in last M = 7 days */
19
AND event_timestamp >
20
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
21
/* PLEASE REPLACE WITH YOUR DESIRED DATE RANGE */
22
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
23
) AS MDaysUsers
24
-- EXCEPT ALL is not yet implemented in BigQuery. Use LEFT JOIN in the interim.
25
LEFT JOIN
26
(
27
SELECT
28
user_id
29
FROM
30
/* PLEASE REPLACE WITH YOUR TABLE NAME */
31
`YOUR_TABLE.events_*`
32
WHERE
33
event_name = 'user_engagement'
34
/* Has engaged in last N = 2 days */
35
AND event_timestamp >
36
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY))
37
/* PLEASE REPLACE WITH YOUR DESIRED DATE RANGE */
38
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
39
) AS NDaysUsers
40
ON MDaysUsers.user_id = NDaysUsers.user_id
41
WHERE
42
NDaysUsers.user_id IS NULL;
Copied!

Frequently active users

1
/**
2
* Builds an audience of Frequently Active Users.
3
*
4
* Frequently Active Users = users who have logged at least once
5
* 'user_engagement' event on N of the last M days where M > N.
6
*/
7
SELECT
8
COUNT(DISTINCT user_id) AS frequent_active_users_count
9
FROM
10
(
11
SELECT
12
user_id,
13
COUNT(DISTINCT event_date)
14
FROM
15
-- PLEASE REPLACE WITH YOUR TABLE NAME.
16
`YOUR_TABLE.events_*`
17
WHERE
18
event_name = 'user_engagement'
19
-- User engagement in the last M = 10 days.
20
AND event_timestamp >
21
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
22
-- PLEASE REPLACE YOUR DESIRED DATE RANGE. For optimal performance
23
-- the _TABLE_SUFFIX range should match the INTERVAL value above.
24
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
25
GROUP BY 1
26
-- Having engaged in at least N = 4 days.
27
HAVING COUNT(event_date) >= 4
28
);
29
​
Copied!

Highly active users

1
/**
2
* Builds an audience of Highly Active Users.
3
*
4
* Highly Active Users = users who have been active for more than N minutes
5
* in the last M days where M > N.
6
*/
7
SELECT
8
COUNT(DISTINCT user_id) AS high_active_users_count
9
FROM
10
(
11
SELECT
12
user_id,
13
event_params.key,
14
SUM(event_params.value.int_value)
15
FROM
16
-- PLEASE REPLACE WITH YOUR TABLE NAME.
17
`YOUR_TABLE.events_*` AS T
18
CROSS JOIN
19
T.event_params
20
WHERE
21
event_name = 'user_engagement'
22
-- User engagement in the last M = 10 days.
23
AND event_timestamp >
24
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
25
AND event_params.key = 'engagement_time_msec'
26
-- PLEASE REPLACE YOUR DESIRED DATE RANGE.
27
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
28
GROUP BY 1, 2
29
HAVING
30
-- Having engaged for more than N = 0.1 minutes.
31
SUM(event_params.value.int_value) > 0.1 * 60 * 1000000
32
);
33
​
Copied!

Acquired users

1
/**
2
* Builds an audience of Acquired Users.
3
*
4
* Acquired Users = users who were acquired via some Source/Medium/Campaign.
5
*/
6
SELECT
7
COUNT(DISTINCT user_id) AS acquired_users_count
8
FROM
9
-- PLEASE REPLACE WITH YOUR TABLE NAME.
10
`YOUR_TABLE.events_*`
11
WHERE
12
traffic_source.source = 'google'
13
AND traffic_source.medium = 'cpc'
14
AND traffic_source.name = 'VTA-Test-Android'
15
-- PLEASE REPLACE YOUR DESIRED DATE RANGE.
16
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
Copied!

Cohorts with filters

1
/**
2
* Builds an audience composed of users acquired last week
3
* through Google campaigns, i.e., cohorts with filters.
4
*
5
* Cohort is defined as users acquired last week, i.e. between 7 - 14
6
* days ago. The cohort filter is for users acquired through a direct
7
* campaign.
8
*/
9
SELECT
10
COUNT(DISTINCT user_id) AS users_acquired_through_google_count
11
FROM
12
-- PLEASE REPLACE WITH YOUR TABLE NAME.
13
`YOUR_TABLE.events_*`
14
WHERE
15
event_name = 'first_open'
16
-- Cohort: opened app 1-2 weeks ago. One week of cohort, aka. weekly.
17
AND event_timestamp >
18
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY))
19
AND event_timestamp <
20
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
21
-- Cohort filter: users acquired through 'google' source.
22
AND traffic_source.source = 'google'
23
-- PLEASE REPLACE YOUR DESIRED DATE RANGE.
24
AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';
Copied!