Requêtes de base pour l'exportation de données d'événement Google Analytics

Les exemples de requêtes sur cette page s'appliquent aux données d'exportation des événements BigQuery pour Google Analytics.

Interroger votre ensemble de données au lieu de l'ensemble de données exemple

Sauf indication contraire, toutes les requêtes listées ici utilisent des exemples d'ensembles de données et devraient produire des résultats valides. Pour utiliser vos propres données d'exportation d'événements BigQuery de votre propriété Google Analytics, recherchez le commentaire -- Replace table dans chaque requête et remplacez l'exemple de tableau. Pour copier le nom du tableau à partir de votre ensemble de données :

  1. Accédez à l'interface utilisateur BigQuery et sélectionnez le projet contenant votre ensemble de données.
  2. Recherchez la table dans l'explorateur.
  3. Cliquez sur les trois points verticaux à droite du tableau, puis sur Copier l'ID.
  4. Collez le nom de la table à la place de l'exemple de table dans la requête.
  5. Remplacez la partie de la date de la table par *.

Par exemple, si Copier l'ID a copié le nom de la table BigQuery my-first-gcp-project:analytics_28239234.events_20240718, remplacez :

  -- Replace table
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

avec :

  -- Replace table
  `my-first-gcp-project.analytics_28239234.events_*`

Interroger une période spécifique

Pour interroger une période spécifique à partir d'un ensemble de données d'exportation d'événements BigQuery, utilisez la pseudo-colonne _TABLE_SUFFIX dans la clause WHERE de votre requête. Pour en savoir plus, consultez Filtrer des tables sélectionnées avec _TABLE_SUFFIX.

Par exemple, la requête suivante comptabilise les événements uniques par date et par nom d'événement pour une période spécifique et des événements sélectionnés :

-- Example: Query a specific date range for selected events.
--
-- Counts unique events by date and by event name for a specifc period of days and
-- selected events(page_view, session_start, and purchase).

SELECT
  event_date,
  event_name,
  COUNT(*) AS event_count
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('page_view', 'session_start', 'purchase')
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY 1, 2;

Nombre d'utilisateurs, nouveaux utilisateurs et utilisateurs connus

  • Pour obtenir le nombre total d'utilisateurs actifs, comptez le nombre de valeurs user_id distinctes. Toutefois, si votre client Google Analytics ne renvoie pas de user_id avec chaque appel ou si vous n'êtes pas sûr, comptez le nombre de valeurs user_pseudo_id distinctes.
  • Pour les nouveaux utilisateurs, comptez le nombre d'utilisateurs distincts dont event_name est :
  • Pour les utilisateurs connus, comptabilisez le nombre d'utilisateurs ayant enregistré une activité au cours de la période, mais n'ayant pas démarré leur première session ni lancé l'application pour la première fois au cours de la période (utilisateurs actifs moins nouveaux utilisateurs).
-- Example: Get 'Total User' (Active User), 'New User', and 'Returning User' counts.

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      MAX(IF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new_user
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
    GROUP BY 1
  )
SELECT
  COUNT(*) AS active_users,
  SUM(is_new_user) AS new_users,
  COUNT(*) - SUM(is_new_user) AS returning_users
FROM UserInfo;

Nombre moyen de transactions par acheteur

La requête suivante affiche le nombre moyen de transactions par acheteur.

-- Example: Average number of transactions per purchaser.

SELECT
  COUNT(*) / COUNT(DISTINCT user_pseudo_id) AS avg_transaction_per_purchaser
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('in_app_purchase', 'purchase')
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201231';

Valeurs pour un nom d'événement spécifique

La requête suivante affiche le event_timestamp pour tous les événements purchase et les valeurs de paramètres d'événement associées :

-- Example: Query values for a specific event name.
--
-- Queries the individual timestamps and values for all 'purchase' events.

SELECT
  event_timestamp,
  (
    SELECT COALESCE(value.int_value, value.float_value, value.double_value)
    FROM UNNEST(event_params)
    WHERE key = 'value'
  ) AS event_value
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

La requête précédente peut être modifiée pour afficher le total des valeurs des paramètres d'événement au lieu d'une liste :

-- Example: Query total value for a specific event name.
--
-- Queries the total event value for all 'purchase' events.

SELECT
  SUM(
    (
      SELECT COALESCE(value.int_value, value.float_value, value.double_value)
      FROM UNNEST(event_params)
      WHERE key = 'value'
    ))
    AS event_value
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

Top 10 des articles ajoutés au panier

La requête suivante affiche les 10 articles les plus ajoutés au panier par le plus grand nombre d'utilisateurs.

-- Example: Top 10 items added to cart by most users.

SELECT
  item_id,
  item_name,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items)
WHERE
  -- Replace date range.
  _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name IN ('add_to_cart')
GROUP BY
  1, 2
ORDER BY
  user_count DESC
LIMIT 10;

Nombre moyen de pages vues par type d'acheteur (acheteurs ou non-acheteurs)

La requête suivante affiche le nombre moyen de pages vues par type d'acheteur (acheteurs ou non-acheteurs) :

-- Example: Average number of pageviews by purchaser type.

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      COUNTIF(event_name = 'page_view') AS page_view_count,
      COUNTIF(event_name IN ('in_app_purchase', 'purchase')) AS purchase_event_count
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
    GROUP BY 1
  )
SELECT
  (purchase_event_count > 0) AS purchaser,
  COUNT(*) AS user_count,
  SUM(page_view_count) AS total_page_views,
  SUM(page_view_count) / COUNT(*) AS avg_page_views,
FROM UserInfo
GROUP BY 1;

Séquence de pages vues

Cette requête affiche la séquence de pages vues par chaque utilisateur. La requête trie les résultats à l'aide des champs suivants afin que les événements soient listés dans l'ordre dans lequel ils se sont produits pour l'utilisateur, même s'ils ont été envoyés dans le même lot :

  • user_pseudo_id
  • user_id
  • batch_page_id
  • batch_ordering_id
  • batch_event_index

Bien que l'échantillon limite les résultats à page_view événements, vous pouvez utiliser la même clause ORDER BY pour ordonner correctement tous les événements en supprimant la condition de clause WHERE pour event_name.

La requête montre également comment utiliser les fonctions définies par l'utilisateur GetParamString et GetParamInt pour réduire les doublons et rendre vos requêtes plus faciles à comprendre et à gérer.

-- Example: Sequence of pageviews.

/** Temporary function to retrieve the string_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamString(event_params ANY TYPE, param_name STRING)
AS ((SELECT ANY_VALUE(value.string_value) FROM UNNEST(event_params) WHERE key = param_name));

/** Temporary function to retrieve the int_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamInt(event_params ANY TYPE, param_name STRING)
AS ((SELECT ANY_VALUE(value.int_value) FROM UNNEST(event_params) WHERE key = param_name));

SELECT
  user_pseudo_id,
  user_id,
  batch_page_id,
  batch_ordering_id,
  batch_event_index,
  event_name,
  GetParamInt(event_params, 'ga_session_id') as ga_session_id,
  GetParamString(event_params, 'page_location') as page_location,
  GetParamString(event_params, 'page_title') as page_title,
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'page_view'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20240718' AND '20240731'
ORDER BY
  user_pseudo_id,
  user_id,
  batch_page_id,
  batch_ordering_id,
  batch_event_index;

Liste des paramètres d'événement

La requête suivante liste tous les paramètres d'événement qui apparaissent dans votre ensemble de données :

-- Example: List all available event parameters and count their occurrences.

SELECT
  EP.key AS event_param_key,
  COUNT(*) AS occurrences
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(event_params) AS EP
WHERE
  -- Replace date range.
  _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY
  event_param_key
ORDER BY
  event_param_key ASC;

Rejoindre le programme avec Google Ads

Pour récupérer des données Google Ads supplémentaires pour vos événements Google Analytics, configurez le service de transfert de données BigQuery pour Google Ads, puis joignez collected_traffic_source.gclid à partir des données d'événement Google Analytics au champ gclid de ads_ClickStats_customer_id à partir du transfert Google Ads.

N'oubliez pas que l'exportation des données d'événement Google Analytics crée un tableau pour chaque jour, tandis que le transfert Google Ads génère un seul tableau ads_ClickStats_customer_id par client.

Précédent
Premiers pas
Suivant
Requêtes d'événements avancées