[GA4] Примеры запросов для аудиторий на основе данных BigQuery

После экспорта данных 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';
  
  
Эта информация оказалась полезной?
Как можно улучшить эту статью?