После экспорта данных Firebase в BigQuery вы сможете запрашивать данные для определенных аудиторий.
В этой статье представлены шаблоны, которые можно использовать в качестве основы для собственных запросов. Не забудьте отредактировать образцы в соответствии со спецификой ваших данных (например, изменить названия таблиц и диапазоны дат).
Эти запросы возвращают количество пользователей в аудитории. Если же вам нужно получить список идентификаторов пользователей в аудитории, удалите внешнюю функцию COUNT(). Пример: COUNT(DISTINCT user_id) --> DISTINCT user_id.
Перед выполнением запроса убедитесь, что вы выбрали стандартный SQL. В разделе BigQuery > Рабочая область SQL нажмите Ещё > Настройки запроса. В разделе Дополнительные настройки > Диалект SQL выберите Стандартный.
В настоящее время данные об аудиториях носят лишь справочный характер.
Нам бы очень хотелось узнать, были ли вам полезны эти образцы запросов и какие ещё типы аудиторий вас интересуют. Вы можете сообщить нам об этом с помощью формы для связи со службой поддержки Firebase (выберите категорию Feature Request).
Содержание
Покупатели
/**
* Расчет аудитории покупателей.
*
* Покупатели – пользователи, для которых было зарегистрировано событие in_app_purchase или
* purchase.
*/
SELECT
COUNT(DISTINCT user_id) AS purchasers_count
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*`
WHERE
event_name IN ('in_app_purchase', 'purchase')
-- PLEASE REPLACE WITH YOUR DESIRED DATE RANGE
AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';
Активные пользователи за N дней
/**
* Создание аудитории активных пользователей за N дней.
*
* Активные пользователи за N дней – число пользователей, для которых было зарегистрировано хотя бы одно событие с параметром
* engagement_time_msec > 0 за последние N дн.
*/
SELECT
COUNT(DISTINCT user_id) AS n_day_active_users_count
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*` AS T
CROSS JOIN
T.event_params
WHERE
event_params.key = 'engagement_time_msec' AND event_params.value.int_value > 0
-- Pick events in the last N = 20 days.
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY))
-- PLEASE REPLACE WITH YOUR DESIRED DATE RANGE.
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
Неактивные пользователи за N дней
/**
* Создание аудитории неактивных пользователей за N дней.
*
* Неактивные пользователи за N дней – это лица, для которых в течение прошедших M дн. не было зарегистрировано ни одного
* события с параметром engagement_time_msec > 0 за последние N дн.,
* где M > N.
*/
SELECT
COUNT(DISTINCT MDaysUsers.user_id) AS n_day_inactive_users_count
FROM
(
SELECT
user_id
FROM
/* PLEASE REPLACE WITH YOUR TABLE NAME */
`YOUR_TABLE.events_*` AS T
CROSS JOIN
T.event_params
WHERE
event_params.key = 'engagement_time_msec' AND event_params.value.int_value > 0
/* Has engaged in last M = 7 days */
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
/* PLEASE REPLACE WITH YOUR DESIRED DATE RANGE */
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
) AS MDaysUsers
-- EXCEPT ALL is not yet implemented in BigQuery. Use LEFT JOIN in the interim.
LEFT JOIN
(
SELECT
user_id
FROM
/* PLEASE REPLACE WITH YOUR TABLE NAME */
`YOUR_TABLE.events_*`AS T
CROSS JOIN
T.event_params
WHERE
event_params.key = 'engagement_time_msec' AND event_params.value.int_value > 0
/* Has engaged in last N = 2 days */
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY))
/* PLEASE REPLACE WITH YOUR DESIRED DATE RANGE */
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
) AS NDaysUsers
ON MDaysUsers.user_id = NDaysUsers.user_id
WHERE
NDaysUsers.user_id IS NULL;
Пользователи, часто проявляющие активность
/**
* Создание аудитории пользователей, часто проявляющих активность.
*
* Пользователи, часто проявляющие активность – число пользователей, для которых * было зарегистрировано хотя бы одно событие с параметром engagement_time_msec > 0
* в течение N дн. из последних M дн., где M > N.
*/
SELECT
COUNT(DISTINCT user_id) AS frequent_active_users_count
FROM
(
SELECT
user_id,
COUNT(DISTINCT event_date)
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*` AS T
CROSS JOIN
T.event_params
WHERE
event_params.key = 'engagement_time_msec' AND event_params.value.int_value > 0
-- User engagement in the last M = 10 days.
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
-- PLEASE REPLACE YOUR DESIRED DATE RANGE. For optimal performance
-- the _TABLE_SUFFIX range should match the INTERVAL value above.
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
GROUP BY 1
-- Having engaged in at least N = 4 days.
HAVING COUNT(event_date) >= 4
);
Пользователи, проявляющие высокую активность
/**
* Создание аудитории пользователей, проявляющих высокую активность.
*
* К пользователям, проявляющим высокую активность, относятся лица, которые были активны более N минут
* за последние M дней, где M > N.
*/
SELECT
COUNT(DISTINCT user_id) AS high_active_users_count
FROM
(
SELECT
user_id,
event_params.key,
SUM(event_params.value.int_value)
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*` AS T
CROSS JOIN
T.event_params
WHERE
-- User engagement in the last M = 10 days.
event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
AND event_params.key = 'engagement_time_msec'
-- PLEASE REPLACE YOUR DESIRED DATE RANGE.
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
GROUP BY 1, 2
HAVING
-- Having engaged for more than N = 0.1 minutes.
SUM(event_params.value.int_value) > 0.1 * 60 * 1000000
);
Привлеченные пользователи
/**
* Создание аудитории привлеченных пользователей.
*
* Привлеченные пользователи – это новые посетители, которых удалось заинтересовать с помощью какого-либо источника, канала или кампании.
*/
SELECT
COUNT(DISTINCT user_id) AS acquired_users_count
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*`
WHERE
traffic_source.source = 'google'
AND traffic_source.medium = 'cpc'
AND traffic_source.name = 'VTA-Test-Android'
-- PLEASE REPLACE YOUR DESIRED DATE RANGE.
AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
Когорты с фильтрами
/**
* Использование когорт с фильтрами для создания аудитории пользователей,
* которые были привлечены на прошлой неделе с помощью кампаний Google.
*
* Когорта задается как группа лиц, привлеченных на прошлой неделе,
* то есть 7–14 дней назад. Фильтрация осуществляется по пользователям, которых удалось заинтересовать с помощью
* прямых кампаний.
*/
SELECT
COUNT(DISTINCT user_id) AS users_acquired_through_google_count
FROM
-- PLEASE REPLACE WITH YOUR TABLE NAME.
`YOUR_TABLE.events_*`
WHERE
event_name = 'first_open'
-- Cohort: opened app 1-2 weeks ago. One week of cohort, aka. weekly.
AND event_timestamp >
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY))
AND event_timestamp <
UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
-- Cohort filter: users acquired through 'google' source.
AND traffic_source.source = 'google'
-- PLEASE REPLACE YOUR DESIRED DATE RANGE.
AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';