Une fois que vous avez exporté vos données Firebase vers BigQuery, vous pouvez interroger ces données pour des audiences spécifiques.
Vous trouverez dans cet article un certain nombre de modèles que vous pouvez utiliser comme base pour vos requêtes. N'oubliez pas de modifier les exemples de requêtes afin de tenir compte des spécificités de vos données (modification des noms des tableaux et des plages de dates, par exemple).
Ces requêtes affichent le nombre d'utilisateurs dans l'audience. Si vous souhaitez plutôt obtenir la liste d'ID utilisateur inclus dans l'audience, supprimez la fonction COUNT() de plus haut niveau (par exemple, remplacez COUNT(DISTINCT user_id) par DISTINCT user_id).
SQL standard est utilisé pour ces requêtes. Veillez donc à sélectionner cette option avant de lancer une requête. (Dans BigQuery > Espace de travail SQL, cliquez sur Plus > Paramètres de requête. Sous Paramètres supplémentaires > Dialecte SQL, sélectionnez Standard.)
Pour le moment, ces données relatives aux audiences ne sont fournies qu'à titre d'information et ne sont pas exploitables.
Nous aimerions savoir si ces exemples de requêtes vous sont utiles, et si vous souhaitez lancer des requêtes pour d'autres types d'audiences. Vous pouvez répondre en envoyant une demande de fonctionnalité à l'assistance Firebase.
Au sommaire de cet article :
Acheteurs
/**
* Permet de calculer l'audience d'acheteurs.
*
* Acheteurs = utilisateurs qui ont enregistré un événement "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';
Utilisateurs actifs (N jours)
/**
* Permet de définir une audience d'utilisateurs actifs (N jours).
*
* Utilisateurs actifs (N jours) = utilisateurs qui ont enregistré au moins un événement avec le paramètre d'événement
* engagement_time_msec > 0 au cours des N derniers jours.
*/
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';
Utilisateurs inactifs (N jours)
/**
* Permet de définir une audience d'utilisateurs inactifs (N jours).
*
* Utilisateurs inactifs (N jours) = utilisateurs au cours des M derniers jours qui n'ont enregistré aucun événement avec le paramètre d'événement
* engagement_time_msec > 0 au cours des N derniers jours,
* où 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;
Utilisateurs souvent actifs
/**
* Permet de définir une audience d'utilisateurs souvent actifs.
*
* Utilisateurs souvent actifs = utilisateurs qui ont enregistré au moins un événement avec le paramètre d'événement
* engagement_time_msec > 0 pendant N jours
* au cours des M derniers jours, où 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
);
Utilisateurs très actifs
/**
* Permet de définir une audience d'utilisateurs très actifs.
*
* Utilisateurs très actifs = utilisateurs qui ont été actifs pendant plus de N minutes
* au cours des M derniers jours, où 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
);
Utilisateurs acquis
/**
* Permet de définir une audience d'utilisateurs acquis.
*
* Utilisateurs acquis = utilisateurs qui ont été acquis via une source/un support/une campagne spécifiques.
*/
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 avec filtres
/**
* Permet de définir une audience composée d'utilisateurs acquis la semaine dernière
* via des campagnes Google, c'est-à-dire des cohortes utilisant des filtres.
*
* Une cohorte correspond à des utilisateurs acquis la semaine dernière, c'est-à-dire il y a entre 7 et 14
* jours. Le filtre appliqué à la cohorte concerne les utilisateurs acquis via une
* campagne directe.
*/
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';