Consultas de exemplo para públicos-alvo com base em dados do BigQuery

Após exportar os seus dados do Firebase para o BigQuery, pode consultar esses dados para públicos específicos.

Este artigo fornece vários modelos que pode utilizar como base para as suas consultas. Não se esqueça de modificar as consultas de exemplo de acordo com os aspetos específicos dos seus dados, por exemplo, alterar os nomes das tabelas e modificar os intervalos de datas.

Estas consultas devolvem o número de utilizadores no público-alvo. Em alternativa, se pretender obter a lista de USER IDs no público-alvo, remova a função COUNT() mais externa, por exemplo, COUNT(DISTINCT user_id) --> DISTINCT user_id.

Estas consultas utilizam o SQL padrão, por isso, certifique-se de que seleciona essa opção antes de executar uma consulta.

Atualmente, estes dados do público-alvo são apenas informativos, e não acionáveis.

Gostaríamos de saber se considera úteis estes exemplos de consulta e se existem outros tipos de público-alvo que pretenda consultar. Pode responder através de um pedido de funcionalidade junto do apoio técnico do Firebase.

 

Neste artigo:

Compradores

  /**
 * Calcula o público-alvo de compradores.
 *
 * Compradores = utilizadores que registaram o evento in_app_purchase ou
 * ecommerce_purchase.
 */
SELECT
  COUNT(DISTINCT user_id) AS purchasers_count
FROM
  -- SUBSTITUA PELO NOME DA SUA TABELA.
  `YOUR_TABLE.events_*`
WHERE
  event_name IN ('in_app_purchase', 'ecommerce_purchase')
  -- SUBSTITUA PELO INTERVALO DE DATAS PRETENDIDO.
  AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';
  

Utilizadores ativos há N dias

  /**
 * Crie um público-alvo de utilizadores ativos há N dias.
 *
 * Utilizadores ativos = utilizadores que registaram, pelo menos, um evento user_engagement
 * nos últimos N dias.
*/
SELECT
  COUNT(DISTINCT user_id) AS n_day_active_users_count
FROM
  -- SUBSTITUA PELO NOME DA SUA TABELA.
  `YOUR_TABLE.events_*`
WHERE
  event_name = 'user_engagement'
  -- Escolha eventos dos últimos N = 20 dias.
  AND event_timestamp >
      UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY))
  -- SUBSTITUA PELO INTERVALO DE DATAS PRETENDIDO.
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
  

Utilizadores inativos há N dias

  /**
 * Crie um público-alvo de utilizadores inativos há N dias.
 *
 * Utilizadores inativos há N dias = utilizadores nos últimos M dias que não registaram 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
      /* SUBSTITUA 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))
      /* SUBSTITUA PELO INTERVALO DE DATAS PRETENDIDO. */
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
  ) AS MDaysUsers
-- EXCEPT ALL ainda não foi implementado no BigQuery. Entretanto, utilize LEFT JOIN.
LEFT JOIN
  (
    SELECT
      user_id
    FROM
      /* SUBSTITUA 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))
      /* SUBSTITUA PELO INTERVALO DE DATAS PRETENDIDO. */
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
  ) AS NDaysUsers
  ON MDaysUsers.user_id = NDaysUsers.user_id
WHERE
  NDaysUsers.user_id IS NULL;
  

Utilizadores ativos frequentemente

  /**
 * Crie um público-alvo de utilizadores ativos frequentemente.
 *
 * Utilizadores ativos frequentemente = utilizadores que registaram, 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
      -- SUBSTITUA PELO NOME DA SUA TABELA.
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      -- Interação dos utilizadores nos últimos M = 10 dias.
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
      -- SUBSTITUA PELO INTERVALO DE DATAS PRETENDIDO.  Para um desempenho ideal,
      -- o intervalo _TABLE_SUFFIX deve corresponder ao valor INTERVAL acima.
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
    GROUP BY 1
    -- Interagiram em, pelo menos, N = 4 dias.
    HAVING COUNT(event_date) >= 4
  );

  

Utilizadores muito ativos

  /**
 * Crie um público-alvo de utilizadores muito ativos.
 *
 * Utilizadores muito ativos = utilizadores que estiveram ativos durante 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
      -- SUBSTITUA PELO NOME DA SUA TABELA.
      `YOUR_TABLE.events_*` AS T
    CROSS JOIN
      T.event_params
    WHERE
      event_name = 'user_engagement'
      -- Interação dos utilizadores nos últimos M = 10 dias.
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
      AND event_params.key = 'engagement_time_msec'
      -- SUBSTITUA PELO INTERVALO DE DATAS PRETENDIDO.
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
    GROUP BY 1, 2
    HAVING
      -- Interagiram durante mais de N = 0,1 minutos.
      SUM(event_params.value.int_value) > 0.1 * 60 * 1000000
  );

  

Utilizadores adquiridos

  /**
 * Crie um público-alvo de utilizadores adquiridos.
 *
 * Utilizadores adquiridos = utilizadores que foram adquiridos através de algum(a) Origem/Meio/Campanha.
 */
SELECT
  COUNT(DISTINCT user_id) AS acquired_users_count
FROM
  -- SUBSTITUA 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'
  -- SUBSTITUA PELO INTERVALO DE DATAS PRETENDIDO.
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
  

Grupos com caraterísticas em comum com filtros

  /**
 * Crie um público-alvo composto por utilizadores adquiridos na última semana
 * através de campanhas da Google, ou seja, grupos com caraterísticas em comum com filtros.
 *
 * Um grupo com caraterísticas em comum é definido como os utilizadores adquiridos na última semana, ou seja, há 7–14
 * dias. O filtro do grupo com caraterísticas em comum destina-se a utilizadores adquiridos através de uma campanha
 * direta.
 */
SELECT
  COUNT(DISTINCT user_id) AS users_acquired_through_google_count
FROM
  -- SUBSTITUA PELO NOME DA SUA TABELA.
  `YOUR_TABLE.events_*`
WHERE
  event_name = 'first_open'
  -- Grupo com caraterísticas em comum: abriu a aplicação há 1–2 semanas. Uma semana de grupo com caraterísticas em comum, ou seja, grupo com caraterísticas em comum 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 do grupo com caraterísticas em comum: utilizadores adquiridos através da origem 'google'.
  AND traffic_source.source = 'google'
  -- SUBSTITUA PELO INTERVALO DE DATAS PRETENDIDO.
  AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';
  
A informação foi útil?
Como podemos melhorá-la?