Consultas de amostra para públicos com base nos dados do BigQuery

Depois de exportar seus dados do Firebase para o BigQuery, você pode consultar esses dados para públicos específicos.

Este artigo fornece vários modelos que podem ser usados como base para suas consultas. Lembre-se de modificar as consultas de amostra para abordar as especificidades dos seus dados, por exemplo, alterar os nomes das tabelas e modificar os períodos.

Essas consultas retornam a quantidade de usuários no público. Para receber a lista de códigos de usuário no público, 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 executar uma consulta.

Atualmente, esses dados de público são apenas informativos, não acionáveis.

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

 

Neste artigo:

Compradores

  /**
 * Calcula o público de compradores.
 *
 * Compradores = usuários que registraram um evento in_app_purchase ou
 * ecommerce_purchase.
 */
SELECT
  COUNT(DISTINCT user_id) AS purchasers_count
FROM
  -- SUBSTITUIR PELO NOME DA SUA TABELA
  `YOUR_TABLE.events_*`
WHERE
  event_name IN ('in_app_purchase', 'ecommerce_purchase')
  -- SUBSTITUIR PELO PERÍODO DESEJADO
  AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';
  

Usuários ativos em N dias

  /**
 * Cria um público de usuários ativos em N dias.
 *
 * Usuários ativos = usuários que registraram pelo menos um evento user_engagement
 * nos últimos N dias.
*/
SELECT
  COUNT(DISTINCT user_id) AS n_day_active_users_count
FROM
  — SUBSTITUIR PELO NOME DA SUA TABELA.
  `YOUR_TABLE.events_*`
WHERE
  event_name = 'user_engagement'
  — Escolher eventos nos últimos N = 20 dias.
  AND event_timestamp >
      UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY))
  -- SUBSTITUIR PELO PERÍODO DESEJADO.
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
  

Usuários inativos em N dias

  /**
 * Cria um público de usuários inativos em N dias.
 *
 * Usuários inativos em N dias = usuários dos últimos M dias que não registraram um evento
 *   user_engagement 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
      /* SUBSTITUIR PELO NOME DA SUA TABELA */
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      /* Interagiu nos últimos M = 7 dias */
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
      /* SUBSTITUIR PELO PERÍODO DESEJADO */
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
  ) AS MDaysUsers
-- A opção EXCEPT ALL ainda não foi implementada no BigQuery. Use a opção LEFT JOIN temporariamente.
LEFT JOIN
  (
    SELECT
      user_id
    FROM
      /* SUBSTITUIR PELO NOME DA SUA TABELA */
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      /* Interagiu nos últimos N = 2 dias */
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY))
      /* SUBSTITUIR PELO PERÍODO DESEJADO */
      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 de usuários ativos com frequência.
 *
 * Usuários ativos com frequência = usuários que registraram pelo menos um evento
 * 'user_engagement' 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
      -- SUBSTITUIR PELO NOME DA SUA TABELA.
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      -- Engajamento dos usuários nos últimos M = 10 dias.
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
      -- SUBSTITUIR PELO PERÍODO DESEJADO.  Para que o desempenho seja ideal,
      -- o período _TABLE_SUFFIX precisa corresponder ao valor INTERVAL acima.
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
    GROUP BY 1
    -- Houve interações nos últimos N = 4 dias.
    HAVING COUNT(event_date) >= 4
  );

  

Usuários muito ativos

  /**
 * Cria um público de usuários muito ativos.
 *
 * Usuários muito ativos = usuários que ficaram ativos por mais de N minutos
 * nos últimos M dias, em 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
      -- SUBSTITUIR PELO NOME DA SUA TABELA.
      `YOUR_TABLE.events_*` AS T
    CROSS JOIN
      T.event_params
    WHERE
      event_name = 'user_engagement'
      -- Engajamento dos usuários nos últimos M = 10 dias.
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
      AND event_params.key = 'engagement_time_msec'
      -- SUBSTITUIR PELO PERÍODO DESEJADO.
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
    GROUP BY 1, 2
    HAVING
      -- Houve interações por mais de N = 0,1 minuto.
      SUM(event_params.value.int_value) > 0.1 * 60 * 1000000
  );

  

Usuários adquiridos

  /**
 * Cria um público de usuários adquiridos.
 *
 * Usuários adquiridos = usuários que foram adquiridos por meio de alguma origem/mídia/campanha.
 */
SELECT
  COUNT(DISTINCT user_id) AS acquired_users_count
FROM
  -- SUBSTITUIR PELO NOME DA SUA TABELA.
  `YOUR_TABLE.events_*`
WHERE
  traffic_source.source = 'google'
  AND traffic_source.medium = 'cpc'
  AND traffic_source.name = 'VTA-Test-Android'
  -- SUBSTITUIR PELO PERÍODO DESEJADO.
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
  

Coortes com filtros

  /**
 * Cria um público composto por usuários adquiridos na semana passada
 * por meio de 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 meio de uma campanha
 * direta.
 */
SELECT
  COUNT(DISTINCT user_id) AS users_acquired_through_google_count
FROM
  -- SUBSTITUIR PELO NOME DA SUA TABELA.
  `YOUR_TABLE.events_*`
WHERE
  event_name = 'first_open'
  -- Coorte: aplicativo aberto de uma a duas semanas atrás. Uma semana de coorte, também chamado de "semanal".
  AND event_timestamp >
      UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY))
  AND event_timestamp <
      UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
  -- Filtro de coorte: usuários adquiridos por meio da origem "google".
  AND traffic_source.source = 'google'
  -- SUBSTITUIR PELO PERÍODO DESEJADO.
  AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';
  
Isso foi útil?
Como podemos melhorá-lo?