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 con IDs de usuario, quite 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 consulta de este tipo. En BigQuery > Espacio de trabajo SQL, haga clic en Más > Configuración de consultas. En Otros ajustes > Dialecto SQL, seleccione Estándar.
De momento, estos datos sobre la audiencia solo son informativos y no se pueden usar para nada más.
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 respondernos enviando 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
* 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';
Usuarios activos (N días)
/**
* Crea una audiencia de usuarios que han estado activos en un número de días N.
*
* Usuarios activos (N días) = usuarios que han registrado al menos un evento con el parámetro
* engagement_time_msec > 0 en los últimos N días.
*/
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';
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 de los últimos M días que no han registrado ningún evento
* con el parámetro engagement_time_msec > 0 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
/* 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;
Usuarios activos frecuentemente
/**
* Crea una audiencia de usuarios activos frecuentemente.
*
* Usuarios activos frecuentemente = usuarios que han registrado al menos un
* evento con el parámetro engagement_time_msec > 0 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
-- 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
);
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
-- 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
);
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
-- 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';
Cohortes con filtros
/**
* Crea una audiencia que contiene los usuarios adquiridos
* durante la semana anterior mediante campañas de Google, es decir, cohortes con filtros.
*
* Las cohortes se componen de los usuarios adquiridos durante la semana anterior, es decir,
* entre los 7 y 14 días anteriores. Los filtros de cohortes se usan para identificar usuarios adquiridos
* mediante una campaña directa.
*/
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';