Exemples de requêtes pour des audiences basées sur les données BigQuery

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.

Pour le moment, ces données relatives aux audiences ne sont fournies qu'à titre d'information et ne sont pas exploitables.

Nous sommes intéressés de savoir si ces exemples de requêtes vous sont utiles. Nous aimerions également savoir 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.

 

Cet article aborde les points suivants :

Acheteurs

  /**
 * Permet de calculer l'audience d'acheteurs.
 *
 * Acheteurs = utilisateurs qui ont enregistré un achat in_app_purchase ou
 * ecommerce_purchase.
 */
SELECT
  COUNT(DISTINCT user_id) AS purchasers_count
FROM
  -- VEUILLEZ REMPLACER PAR LE NOM DE VOTRE TABLEAU
  `YOUR_TABLE.events_*`
WHERE
  event_name IN ('in_app_purchase', 'ecommerce_purchase')
  -- VEUILLEZ REMPLACER PAR LA PLAGE DE DATES SOUHAITÉE
  AND _TABLE_SUFFIX BETWEEN '20180501' AND '20240131';
  

Utilisateurs actifs (N jours)

  /**
 * Permet de définir une audience d'utilisateurs actifs (N jours).
 *
 * Utilisateurs actifs = utilisateurs qui ont enregistré au moins un
 * événement user_engagement au cours des N derniers jours.
*/
SELECT
  COUNT(DISTINCT user_id) AS n_day_active_users_count
FROM
  -- VEUILLEZ REMPLACER PAR LE NOM DE VOTRE TABLEAU.
  `YOUR_TABLE.events_*`
WHERE
  event_name = 'user_engagement'
  -- Sélectionnez des événements au cours des N = 20 derniers jours.
  AND event_timestamp >
      UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY))
  -- VEUILLEZ REMPLACER PAR LA PLAGE DE DATES SOUHAITÉE.
  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 des M derniers jours qui n'ont pas enregistré
 *   d'événement user_engagement 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
      /* VEUILLEZ REMPLACER PAR LE NOM DE VOTRE TABLEAU */
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      /* A interagi au cours des M = 7 derniers jours */
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY))
      /* VEUILLEZ REMPLACER PAR LA PLAGE DE DATES SOUHAITÉE */
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
  ) AS MDaysUsers
-- EXCEPT ALL n'est pas encore accepté par BigQuery. Utilisez LEFT JOIN pour le moment.
LEFT JOIN
  (
    SELECT
      user_id
    FROM
      /* VEUILLEZ REMPLACER PAR LE NOM DE VOTRE TABLEAU */
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      /* A interagi au cours des N = 2 derniers jours */
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY))
      /* VEUILLEZ REMPLACER PAR LA PLAGE DE DATES SOUHAITÉE */
      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 'user_engagement' 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
      -- VEUILLEZ REMPLACER PAR LE NOM DE VOTRE TABLEAU.
      `YOUR_TABLE.events_*`
    WHERE
      event_name = 'user_engagement'
      -- Engagement utilisateur dans les M = 10 derniers jours.
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
      -- VEUILLEZ REMPLACER PAR LA PLAGE DE DATES SOUHAITÉE.  Pour enregistrer des performances optimales
      -- la plage _TABLE_SUFFIX doit correspondre à la valeur INTERVAL ci-dessus.
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
    GROUP BY 1
    -- Ayant interagi pendant au moins N = 4 jours.
    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
      -- VEUILLEZ REMPLACER PAR LE NOM DE VOTRE TABLEAU.
      `YOUR_TABLE.events_*` AS T
    CROSS JOIN
      T.event_params
    WHERE
      event_name = 'user_engagement'
      -- Engagement utilisateur au cours des M = 10 derniers jours.
      AND event_timestamp >
          UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY))
      AND event_params.key = 'engagement_time_msec'
      -- VEUILLEZ REMPLACER PAR LA PLAGE DE DATES SOUHAITÉE.
      AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131'
    GROUP BY 1, 2
    HAVING
      -- Ayant interagi pendant plus de N = 0,1 minute.
      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
  -- VEUILLEZ REMPLACER PAR LE NOM DE VOTRE TABLEAU.
  `YOUR_TABLE.events_*`
WHERE
  traffic_source.source = 'google'
  AND traffic_source.medium = 'cpc'
  AND traffic_source.name = 'VTA-Test-Android'
  -- VEUILLEZ REMPLACER PAR LA PLAGE DE DATES SOUHAITÉE.
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
  

Cohortes avec filtres

  /**
 * 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
  -- VEUILLEZ REMPLACER PAR LE NOM DE VOTRE TABLEAU.
  `YOUR_TABLE.events_*`
WHERE
  traffic_source.source = 'google'
  AND traffic_source.medium = 'cpc'
  AND traffic_source.name = 'VTA-Test-Android'
  -- VEUILLEZ REMPLACER PAR LA PLAGE DE DATES SOUHAITÉE.
  AND _TABLE_SUFFIX BETWEEN '20180521' AND '20240131';
  
Ces informations vous-ont elles été utiles ?
Comment pouvons-nous l'améliorer ?