[GA4] Consultas de amostra para públicos-alvo com base nos dados do BigQuery

Depois de exportar seus dados do Firebase para o BigQuery, é possível consultá-los para públicos-alvo específicos.

Este artigo oferece vários modelos que podem ser usados como base para suas consultas. Lembre-se de modificar as consultas de amostra para considerar seus dados específicos. Por exemplo, mude os nomes das tabelas e altere os períodos.

Essas consultas retornam a quantidade de usuários no público-alvo. Para receber a lista de IDs de usuário no público-alvo, remova a função COUNT() mais externa, por exemplo, COUNT(DISTINCT user_id) --> DISTINCT user_id.

Essas consultas usam o SQL padrão. Portanto, selecione essa opção antes de fazer uma consulta. Em BigQuery > Espaço de trabalho SQL, clique em Mais > Configurações de consulta. Em Outras configurações > Dialeto SQL, selecione Padrão.

Atualmente, esses dados de público-alvo são apenas informativos e não podem ser analisados.

Gostaríamos de saber se você considera esses exemplos de consulta úteis e se há outros tipos de público-alvo que gostaria de consultar. Você pode responder por meio de uma solicitação de recurso com o suporte do Firebase.

Neste artigo:

Compradores
Usuários ativos em N dias
Usuários inativos em N dias
Usuários ativos com frequência
Usuários muito ativos
Usuários adquiridos
Coortes com filtros

Compradores

/**
 * Calcula o público-alvo de compradores.
 *
 * Compradores = usuários que registraram um evento in_app_purchase ou
 * 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';

Usuários ativos em N dias

/**
 * Cria um público-alvo de usuários ativos em N dias.
 *
 * Usuários ativos em N dias = usuários que registraram pelo menos um evento com o parâmetro de evento 
 * engagement_time_msec > 0 nos últimos N dias.
*/

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';

Usuários inativos em N dias

/**
 * Cria um público-alvo de usuários inativos em N dias.
 *
 * Usuários inativos em N dias = usuários nos últimos M dias que não registraram um 
 * evento com o parâmetro de evento engagement_time_msec > 0 nos últimos N dias, 
 * em que 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;

Usuários ativos com frequência

/**
 * Cria um público-alvo de usuários ativos com frequência.
 *
 * Usuários ativos com frequência = usuários que registraram pelo menos um
 * evento com o parâmetro de evento engagement_time_msec > 0 em N dos 
 * últimos M dias, em que 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
  );

Usuários muito ativos

/**
 * Cria um público-alvo de usuários muito ativos.
 *
 * Usuários muito ativos = usuários que ficaram ativos por mais de N minutos
 * nos últimos M dias, sendo que 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
  );

Usuários adquiridos

/**
 * Cria um público-alvo de usuários adquiridos.
 *
 * Usuários adquiridos = usuários que foram adquiridos por alguma origem/mídia/campanha.
 */
 
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';

Coortes com filtros

/**
 * Cria um público-alvo de usuários adquiridos na semana passada
 * por campanhas do Google, ou seja, coortes com filtros.
 *
 * Uma coorte é definida como usuários adquiridos na semana passada, ou seja, entre 7 e 14
 * dias atrás. O filtro de coorte aplica-se a usuários adquiridos por uma campanha
 * direta.
 */
 
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';

Isso foi útil?

Como podemos melhorá-lo?