Consultas de ejemplo sobre audiencias basadas en datos de BigQuery

Tras exportar sus datos de Firebase a BigQuery, puede consultarlos según audiencias específicas.

En este artículo se proporcionan una serie de plantillas que puede utilizar como base para sus consultas. Recuerde modificar las consultas de ejemplo para abordar los aspectos específicos de sus datos. Por ejemplo, debe cambiar los periodos y los nombres de las tablas.

Estas consultas devuelven el número de usuarios de una audiencia. Si en su lugar quiere obtener una lista de IDs de usuarios, elimine la función COUNT() más externa, por ejemplo, COUNT(DISTINCT user_id) -> DISTINCT user_id.

En estas consultas se utiliza SQL estándar, por lo que debe seleccionar esa opción antes de ejecutar una.

Actualmente, estos datos sobre la audiencia solo son informativos y no puede ejecutarse ninguna acción concreta sobre ellos.

Nos gustaría saber si estos ejemplos de consulta le resultan útiles y si le gustaría ejecutar otras consultas referentes a otros tipos de audiencia. Puede enviar una solicitud de función a través del formulario de asistencia de Firebase.

 

Secciones de este artículo:

Compradores

  /**
 * Calcula la audiencia de compradores.
 *
 * Compradores = usuarios que han registrado un evento in_app_purchase o ecommerce_purchase.
 */
SELECT
  COUNT(DISTINCT user_id) AS purchasers_count
FROM
  -- SUSTITÚYALO POR EL NOMBRE DE SU TABLA
  `YOUR_TABLE.events_*`
WHERE
  event_name IN ('in_app_purchase', 'ecommerce_purchase')
  -- 
SUSTITÚYALO POR EL PERIODO QUE QUIERA Y 
_TABLE_SUFFIX BETWEEN '20180501' AND '20240131'
;
  

Usuarios activos (N días)

  /**
 * Crea una audiencia de usuarios que han estado activos en un número de días N.
 *
 * Usuarios activos = 
usuarios que han registrado al menos un evento user_engagement en los últimos N días.
*/
SELECT
  COUNT(DISTINCT user_id) AS n_day_active_users_count
FROM
  -- SUSTITÚYALO POR EL NOMBRE DE SU TABLA.
  `YOUR_TABLE.events_*`
WHERE
  event_name = 'user_engagement'
  -- Elija eventos de los últimos N = 20 días.
  AND event_timestamp >
      UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY))
  -- SUSTITÚYALO POR EL PERIODO QUE QUIERA.
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
  

Usuarios inactivos (N días)

  /**
 * Crea una audiencia de usuarios que han estado inactivos en un número de días N.
 *
 * Usuarios inactivos (N días) = usuarios que en los últimos M días no han registrado
 *   ningún evento user_engagement en los últimos N días, donde M > N.
 */
SELECT
  COUNT(DISTINCT MDaysUsers.user_id) AS n_day_inactive_users_count
FROM
  (
    SELECT
      user_id
    FROM
      /* SUSTITÚYALO POR EL NOMBRE DE SU TABLA */
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      /* Has engaged in last M = 7 days */
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
      /* SUSTITÚYALO POR EL PERIODO QUE QUIERA */
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
  ) AS MDaysUsers
-- EXCEPT ALL todavía no está implementado en BigQuery. Utilice LEFT JOIN mientras tanto.
LEFT JOIN
  (
    SELECT
      user_id
    FROM
      /* SUSTITÚYALO POR EL NOMBRE DE SU TABLA */
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      /* Ha interactuado en los últimos N = 2 días */
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY))
      /* SUSTITÚYALO POR EL PERIODO QUE QUIERA */
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
  ) AS NDaysUsers
  ON MDaysUsers.user_id = NDaysUsers.user_id
WHERE
  NDaysUsers.user_id IS NULL;
  

Usuarios activos frecuentemente

  /**
 * Crea una audiencia de usuarios activos frecuentemente.
 *
 * Usuarios activos frecuentemente = usuarios que han registrado al menos un evento
 * user_engagement en N de los últimos M días, donde M > N.
 */
SELECT
  COUNT(DISTINCT user_id) AS frequent_active_users_count
FROM
  (
    SELECT
      user_id,
      COUNT(DISTINCT event_date)
    FROM
      -- SUSTITÚYALO POR EL NOMBRE DE SU TABLA.
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      -- Interacción de los usuarios en los últimos M = 10 días.
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
      -- SUSTITÚYALO POR EL PERIODO QUE QUIERA.  Para optimizar el rendimiento,
      -- el periodo de _TABLE_SUFFIX debe ser el del valor de INTERVAL anterior.
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
    GROUP BY 1
    -- Han interactuado en al menos N = 4 días.
    HAVING COUNT(event_date) >= 4
  );

  

Usuarios muy activos

  /**
 * Crea una audiencia de usuarios muy activos.
 *
 * Usuarios muy activos = usuarios que han estado activos durante más de N minutos
 * en los últimos M días, donde 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
      -- SUSTITÚYALO POR EL NOMBRE DE SU TABLA.
      `YOUR_TABLE.events_*` AS T
    CROSS JOIN
      T.event_params
    WHERE
      event_name = 'user_engagement'
      -- Interacción de los usuarios en los últimos M = 10 días.
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
      AND event_params.key = 'engagement_time_msec'
      -- SUSTITÚYALO POR EL PERIODO QUE QUIERA.
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
    GROUP BY 1, 2
    HAVING
      -- Han interactuado durante al menos N = 0,1 minutos.
      SUM(event_params.value.int_value) > 0.1 * 60 * 1000000
  );

  

Usuarios adquiridos

  /**
 * Crea una audiencia de usuarios adquiridos.
 *
 * Usuarios adquiridos = usuarios que se adquirieron a través de algunos valores de fuente, medio o campaña.
 */
SELECT
  COUNT(DISTINCT user_id) AS acquired_users_count
FROM
  -- SUSTITÚYALO POR EL NOMBRE DE SU TABLA.
  `YOUR_TABLE.events_*`
WHERE
  traffic_source.source = 'google'
  AND traffic_source.medium = 'cpc'
  AND traffic_source.name = 'VTA-Test-Android'
  -- SUSTITÚYALO POR EL PERIODO QUE QUIERA.
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
  

Cohortes con filtros

  /**
 * Crea una audiencia de usuarios adquiridos.
 *
 * Usuarios adquiridos = usuarios que se adquirieron a través de algunos valores de fuente, medio o campaña.
 */
SELECT
  COUNT(DISTINCT user_id) AS acquired_users_count
FROM
  -- SUSTITÚYALO POR EL NOMBRE DE SU TABLA.
  `YOUR_TABLE.events_*`
WHERE
  traffic_source.source = 'google'
  AND traffic_source.medium = 'cpc'
  AND traffic_source.name = 'VTA-Test-Android'
  -- SUSTITÚYALO POR EL PERIODO QUE QUIERA.
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
  
¿Te ha resultado útil esta información?
¿Cómo podemos mejorar esta página?