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

После экспорта данных Firebase в BigQuery вы сможете запрашивать данные для определенных аудиторий.

В этой статье представлены шаблоны, которые можно использовать в качестве основы для собственных запросов. Не забудьте отредактировать образцы в соответствии со спецификой ваших данных (например, изменить названия таблиц и диапазоны дат).

Эти запросы возвращают количество пользователей в аудитории. Если же вам нужно получить список идентификаторов пользователей в аудитории, удалите внешнюю функцию COUNT (). Пример: COUNT (DISTINCT user_id) -> DISTINCT user_id.

Перед выполнением запроса убедитесь, что вы выбрали стандартный SQL.

В настоящее время данные об аудиториях носят лишь справочный характер.

Нам бы очень хотелось узнать, были ли вам полезны эти образцы запросов и какие еще типы аудиторий вас интересуют. Вы можете сообщить нам об этом с помощью формы для связи со службой поддержки Firebase (выберите категорию Feature Request).

 

Содержание

Покупатели

  /**
 * Расчет аудитории покупателей.
 *
 * Покупатели – пользователи, для которых было зарегистрировано событие in_app_purchase или
 * ecommerce_purchase.
 */
SELECT
  COUNT(DISTINCT user_id) AS purchasers_count
FROM
  -- ЗАМЕНИТЕ НА НАЗВАНИЕ СОБСТВЕННОЙ ТАБЛИЦЫ
  `YOUR_TABLE.events_*`
WHERE
  event_name IN ('in_app_purchase', 'ecommerce_purchase')
  -- ЗАМЕНИТЕ НА НУЖНЫЙ ДИАПАЗОН ДАТ
  AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';
  

Активные пользователи за выбранное вами количество дней

  /**
 * Создание аудитории активных пользователей за N дн.
 *
 * Активные пользователи – это лица, для которых было зарегистрировано хотя бы одно событие user_engagement
 * за последние N дн.
*/
SELECT
  COUNT(DISTINCT user_id) AS n_day_active_users_count
FROM
  -- ЗАМЕНИТЕ НА НАЗВАНИЕ СОБСТВЕННОЙ ТАБЛИЦЫ.
  `YOUR_TABLE.events_*`
WHERE
  event_name = 'user_engagement'
  -- Выберите события за последние N = 20 дн.
  AND event_timestamp >
      UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY))
  -- ЗАМЕНИТЕ НА НУЖНЫЙ ДИАПАЗОН ДАТ.
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
  

Неактивные пользователи за выбранное вами количество дней

  /**
 * Создание аудитории неактивных пользователей за N дн.
 *
 * Неактивные пользователи за N дн. – это лица, для которых в течение прошедших M дн. не было зарегистрировано ни одного события
 *   user_engagement за последние N дн., где M > N.
 */
SELECT
  COUNT(DISTINCT MDaysUsers.user_id) AS n_day_inactive_users_count
FROM
  (
    SELECT
      user_id
    FROM
      /* ЗАМЕНИТЕ НА НАЗВАНИЕ СОБСТВЕННОЙ ТАБЛИЦЫ */
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      /* Активность пользователя в течение M = 7 дн. */
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
      /* ЗАМЕНИТЕ НА НУЖНЫЙ ДИАПАЗОН ДАТ */
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
  ) AS MDaysUsers
-- Оператор EXCEPT ALL в настоящее время не реализован в BigQuery. Используйте LEFT JOIN.
LEFT JOIN
  (
    SELECT
      user_id
    FROM
      /* ЗАМЕНИТЕ НА НАЗВАНИЕ СОБСТВЕННОЙ ТАБЛИЦЫ */
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      /* Активность пользователя за последние N = 2 дн. */
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY))
      /* ЗАМЕНИТЕ НА НУЖНЫЙ ДИАПАЗОН ДАТ */
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
  ) AS NDaysUsers
  ON MDaysUsers.user_id = NDaysUsers.user_id
WHERE
  NDaysUsers.user_id IS NULL;
  

Пользователи, часто проявляющие активность

  /**
 * Создание аудитории пользователей, часто проявляющих активность.
 *
 * К пользователям, часто проявляющим активность, относятся лица, для которых регистрировалось хотя бы одно событие user_engagement в течение N дн. из последних M дн., где M > N.
 */
SELECT
  COUNT(DISTINCT user_id) AS frequent_active_users_count
FROM
  (
    SELECT
      user_id,
      COUNT(DISTINCT event_date)
    FROM
      -- ЗАМЕНИТЕ НА НАЗВАНИЕ СОБСТВЕННОЙ ТАБЛИЦЫ.
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      -- Активность пользователя за последние M = 10 дн.
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
      -- ЗАМЕНИТЕ НА НУЖНЫЙ ДИАПАЗОН ДАТ.  Чтобы обеспечить эффективную работу кода, руководствуйтесь следующей инструкцией:
      -- диапазон _TABLE_SUFFIX должен соответствовать значению INTERVAL, указанному выше.
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
    GROUP BY 1
    -- Активность была проявлена в течение как минимум N = 4 дн.
    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
      -- ЗАМЕНИТЕ НА НАЗВАНИЕ СОБСТВЕННОЙ ТАБЛИЦЫ.
      `YOUR_TABLE.events_*` AS T
    CROSS JOIN
      T.event_params
    WHERE
      event_name = 'user_engagement'
      -- Активность пользователя за последние M = 10 дн.
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
      AND event_params.key = 'engagement_time_msec'
      -- ЗАМЕНИТЕ НА НУЖНЫЙ ДИАПАЗОН ДАТ.
       '20180521' AND '20240131'
    GROUP BY 1, 2
    HAVING
      -- Активность длительностью более N = 0,1 мин.
      SUM(event_params.value.int_value) > 0.1 * 60 * 1000000
  );

  

Привлеченные пользователи

  /**
 * Создание аудитории привлеченных пользователей.
 /*
 Привлеченные пользователи – это новые посетители, которых удалось заинтересовать с помощью какого-либо источника, канала или кампании.
 */
SELECT
  COUNT(DISTINCT user_id) AS acquired_users_count
FROM
  -- ЗАМЕНИТЕ НА НАЗВАНИЕ СОБСТВЕННОЙ ТАБЛИЦЫ.
  `YOUR_TABLE.events_*`
WHERE
  traffic_source.source = 'google'
  AND traffic_source.medium = 'cpc'
  AND traffic_source.name = 'VTA-Test-Android'
  -- ЗАМЕНИТЕ НА НУЖНЫЙ ДИАПАЗОН ДАТ.
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
  

Когорты с фильтрами

  /**
 * Использование когорт с фильтрами для создания аудитории пользователей,
 * которые были привлечены на прошлой неделе с помощью кампаний Google.
 *
 * Когорта задается как группа лиц, привлеченных на прошлой неделе,
 * то есть 7–14 дн. назад. Фильтрация осуществляется по пользователям, которых удалось заинтересовать с помощью
 * прямых кампаний.
 */
SELECT
  COUNT(DISTINCT user_id) AS users_acquired_through_google_count
FROM
  -- ЗАМЕНИТЕ НА НАЗВАНИЕ СОБСТВЕННОЙ ТАБЛИЦЫ.
  `YOUR_TABLE.events_*`
WHERE
  event_name = 'first_open'
  -- В когорту включаются пользователи, которые открывали приложение 1–2 нед. назад. Учитываются данные за одну неделю (еженедельная когорта).
  AND event_timestamp >
      UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY))
  AND event_timestamp <
      UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
  -- Фильтрация для когорты предусматривает выбор пользователей, для которых в качестве источника трафика указан вариант google.
  AND traffic_source.source = 'google'
  -- ЗАМЕНИТЕ НА НУЖНЫЙ ДИАПАЗОН ДАТ.
  AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';
  
Эта информация оказалась полезной?
Как можно улучшить эту статью?

Требуется помощь?

Войдите в свой аккаунт, чтобы мы могли предоставить вам дополнительные варианты поддержки и быстрее решить вашу проблему.