BigQuery en pratique

Cette fonctionnalité n'est disponible que dans Analytics 360, qui fait partie de Google Marketing Platform.
En savoir plus sur Google Marketing Platform

Cet article contient des exemples montrant comment créer des requêtes concernant les données Analytics que vous exportez vers BigQuery. Grâce à notre exemple d'ensemble de données, vous pouvez vous entraîner à utiliser les requêtes présentées dans cet article.

Au sommaire de cet article :

Optimisation des requêtes

Chaque requête que vous exécutez contribue à votre allocation mensuelle de traitement de données. Si vous sélectionnez des champs superflus, vous augmentez la quantité de données qui doit être traitée, vous utilisez donc votre allocation mensuelle plus que nécessaire. Les requêtes optimisées utilisent votre allocation mensuelle de traitement de données d'une manière plus efficace.

En savoir plus sur la tarification

Sélectionnez uniquement ce dont vous avez besoin

Lorsque vous formulez une requête, sélectionnez les champs pertinents au sein de l'instruction SELECT. En évitant d'appeler des champs superflus, vous réduisez la quantité de données ainsi que le temps nécessaire au traitement de la requête.

Exemple : Évitez d'utiliser l'opérateur générique

Mauvaise formulation : en utilisant l'opérateur générique
SELECT *
FROM [table name];

 

Meilleure formulation : en utilisant les noms des champs pour éviter tout traitement inutile
SELECT field1, field2
FROM [table name];

Autoriser la mise en cache

Lorsque cela est possible, évitez d'utiliser des fonctions à la place des champs. Les fonctions (telles que NOW() ou TODAY()) renvoient des résultats variables qui empêchent la mise en cache des requêtes et retardent donc la réception de leurs résultats. Utilisez plutôt des dates et des heures précises.

Actuellement, les résultats mis en cache ne sont pas compatibles avec les requêtes sur plusieurs tableaux utilisant un caractère générique, même si l'option "Utiliser les résultats mis en cache" est cochée. Si vous exécutez plusieurs fois la même requête générique, chaque requête est facturée sur votre compte. En savoir plus

Utiliser des tableaux intermédiaires pour les sous-requêtes couramment utilisées

Si vous constatez que vous utilisez régulièrement une même requête en tant que sous-requête, vous pouvez l'enregistrer sous la forme d'un tableau intermédiaire en cliquant sur Save as Table (Enregistrer en tant que tableau) au-dessus des résultats de la requête. Vous pouvez ensuite référencer ce tableau dans la section FROM de votre requête, ce qui réduira à la fois la quantité de données à traiter et le temps de traitement nécessaire.

En utilisant un tableau intermédiaire
SELECT field1, field2
FROM [Dataset name.table name];

Débogage des requêtes

BigQuery permet de déboguer votre code au fur et à mesure que vous le construisez. Dans la fenêtre dédiée, le débogage est indiqué juste en dessous de la requête. Le débogage est également disponible via l'API grâce au marqueur dryRun.

Les requêtes correctes ont un indicateur vert sur lequel vous pouvez cliquer pour afficher la quantité de données traitées par la requête. Cette fonction vous donne la possibilité d'optimiser vos données avant d'exécuter la requête de sorte à éviter le traitement de données inutiles.

Query Debugging - Success

 

Les requêtes incorrectes ont un indicateur rouge sur lequel vous pouvez cliquer pour afficher des informations sur l'erreur, et trouver la ligne et la colonne où l'erreur se produit. Dans l'exemple ci-dessous, l'instruction GROUP BY est vide et l'erreur est mise en évidence.

Query Debugging - Error

 

Conseils et bonnes pratiques

Utiliser l'exemple d'ensemble de données

Les exemples suivants utilisent l'exemple d'ensemble de données Google Analytics.

Pour utiliser les requêtes pour vos propres données, il vous suffit de remplacer les noms du projet et de l'ensemble de données dans les exemples par vos propres noms de projet et d'ensemble de données.

Utiliser SQL standard ou l'ancien SQL

BigQuery est compatible avec les deux dialectes SQL suivants :

Pour en savoir plus sur les différences entre les deux dialectes, consultez Migrer vers le langage SQL standard.

SQL standard est actuellement le dialecte SQL privilégié pour interroger des données stockées dans BigQuery.

Pour en savoir plus sur l'activation du dialecte SQL standard dans l'interface utilisateur, la CLI ou l'API BigQuery (ou toute autre interface que vous utilisez), consultez Activer le langage SQL standard.

La manière la plus simple de commencer consiste à inclure le commentaire "standardSQL" en haut de vos requêtes SQL standard, comme indiqué dans les exemples suivants.

En cas d'utilisation de l'ancien SQL, les données Google Analytics 360 sont transmises chaque jour à un nouveau tableau. Pour créer des requêtes portant sur plusieurs tableaux à la fois, vous pouvez séparer le nom des tableaux par une virgule, utiliser la fonction de caractères génériques du tableau TABLE_DATE_RANGE ou plusieurs fonctions TABLE_DATE_RANGE séparées par une virgule, comme dans les exemples suivants.

Créer des requêtes sur plusieurs tableaux

Vous trouverez ci-dessous des exemples de requêtes dans les deux dialectes (SQL standard et ancien SQL) pour les mêmes données.

3 jours

SQL standard
3 jours avec "UNION ALL"
#standardSQL
WITH ga_tables AS (
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160802`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160803`
GROUP BY date

)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC
Ancien SQL
3 jours avec des noms de tableaux séparés par une virgule
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
[bigquery-public-data.google_analytics_sample.ga_sessions_20160801],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160802],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160803]
GROUP BY
date
ORDER BY
date ASC

 

1 095 derniers jours

SQL standard
1 095 derniers jours avec "_TABLE_SUFFIX"
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1095 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Ancien SQL
1 095 derniers jours avec "TABLE_DATE_RANGE"
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -1095, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

36 derniers mois

SQL standard
36 derniers mois avec "_TABLE_SUFFIX"
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 36 MONTH))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Ancien SQL
36 derniers mois avec "TABLE_DATE_RANGE"
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -36, 'MONTH'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

3 dernières années

SQL standard
3 dernières années avec "_TABLE_SUFFIX"
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Ancien SQL
3 dernières années avec "TABLE_DATE_RANGE"
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

Période spécifique

SQL standard
Période spécifique avec "_TABLE_SUFFIX"
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20160801' AND '20170731'
GROUP BY date
ORDER BY date ASC
Ancien SQL
Période spécifique avec "TABLE_DATE_RANGE"
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2016-08-01'), TIMESTAMP('2017-07-31')))
GROUP BY
date
ORDER BY
date ASC

3 dernières années, avec les données d'aujourd'hui (intrajournalières)

SQL standard
3 dernières années, avec les données d'aujourd'hui (intrajournalières), avec "UNION ALL" et "_TABLE_SUFFIX"
Remarque : Cet exemple de requête n'est pas compatible actuellement avec l'ensemble de données public Google Analytics, car celui-ci ne contient pas de tableau intrajournalier.
#standardSQL
WITH ga_tables AS ( SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
GROUP BY date
)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC

Ancien SQL

3 dernières années, avec les données d'aujourd'hui (intrajournalières), avec plusieurs "TABLE_DATE_RANGE"
Remarque : Cet exemple de requête n'est pas compatible actuellement avec l'ensemble de données public Google Analytics, car celui-ci ne contient pas de tableau intrajournalier.
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))),
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_intraday_],
DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), CURRENT_TIMESTAMP()))
GROUP BY
date
ORDER BY
date ASC

 

 

Exemples de requêtes de base

Cette section explique comment construire des requêtes de base à l'aide de statistiques et de dimensions à partir des données d'exemple Analytics.

Nombre total de [metric] par [dimension] ?

Voici des exemples de scripts pour la question Quel est le nombre total de transactions générées par navigateur d'appareil en juillet 2017 ?

SQL standard

Nombre total de transactions par navigateur d'appareil en juillet 2017
#standardSQL
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
device.browser
ORDER BY
total_transactions DESC

Ancien SQL

Nombre total de transactions par navigateur d'appareil en juillet 2017
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
device.browser
ORDER BY
total_transactions DESC

 

 

Taux de rebond moyen par [dimension] ?

Le taux de rebond réel est défini comme étant le pourcentage de visites avec une seule page vue. Voici des exemples de scripts pour la question Quel a été le taux de rebond réel par source de trafic ?

SQL standard

Taux de rebond par source de trafic en juillet 2017
#standardSQL
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
source )
ORDER BY
total_visits DESC

Ancien SQL

Taux de rebond par source de trafic en juillet 2017
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
source )
ORDER BY
total_visits DESC

 

 

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

Voici des exemples de scripts pour la question Quel était le nombre moyen de pages produit vues par les utilisateurs ayant effectué un achat en juillet 2017 ?

SQL standard

Nombre moyen de pages produit vues par les utilisateurs ayant effectué un achat en juillet 2017
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions >=1
GROUP BY
users )

Ancien SQL

Nombre moyen de pages produit vues par les utilisateurs ayant effectué un achat en juillet 2017
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions >=1
GROUP BY
users )

 

 

Voici des exemples de scripts pour la question Quel était le nombre moyen de pages produit vues par les utilisateurs n'ayant pas effectué d'achat en juillet 2017 ?

SQL standard

Nombre moyen de pages produit vues par les utilisateurs n'ayant pas effectué d'achat en juillet 2017
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions IS NULL
GROUP BY
users )

Ancien SQL

Nombre moyen de pages produit vues par les utilisateurs n'ayant pas effectué d'achat en juillet 2017
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions IS NULL
GROUP BY
users )

 

 

Nombre moyen de transactions par acheteur

Vous trouverez ci-dessous des exemples de scripts pour la question Quel a été le total moyen de transactions enregistrées par utilisateur ayant effectué un achat en juillet 2017 ?

SQL standard

Nombre moyen de transactions par utilisateur ayant effectué un achat en juillet 2017
#standardSQL
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

Ancien SQL

Nombre moyen de transactions par utilisateur ayant effectué un achat en juillet 2017
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

 

 

Montant moyen dépensé par session

Voici des exemples de scripts pour la question Quel a été le montant moyen dépensé par session en juillet 2017 ?

SQL standard

Montant moyen dépensé par session en juillet 2017
#standardSQL
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

Ancien SQL

Montant moyen dépensé par session en juillet 2017
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

 

 

Séquence d'appels

Voici des exemples de scripts pour la question Dans quel ordre les pages ont-elles été vues ?

SQL standard

Ordre dans lequel les pages ont été vues par les utilisateurs en juillet 2017
#standardSQL
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
UNNEST(hits) as hits
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

Ancien SQL

Ordre dans lequel les pages ont été vues par les utilisateurs en juillet 2017
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

Dans cette requête, vous devez limiter les types d'appels aux PAGES pour ne pas voir apparaître les interactions liées aux événements et aux transactions. Chacune des lignes de la sortie représente une page vue. Elles sont listées par défaut en fonction des champs de l'instruction SELECT.

 

 

Dimensions personnalisées multiples au niveau de l'appel ou de la session

Dimension personnalisée au niveau de l'appel
SELECT fullVisitorId, visitId, hits.hitNumber, hits.time,
MAX(IF(hits.customDimensions.index=1,
hits.customDimensions.value,
NULL)) WITHIN hits AS customDimension1,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

 

Dimension personnalisée au niveau de la session
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

Dans chaque requête :

L'instruction SELECT permet d'obtenir les champs de dimension et de métrique correspondants.

La fonction MAX :

  • Renvoie la dimension personnalisée en tant que nouvelle colonne. Vous pouvez répéter la fonction afin de renvoyer plusieurs dimensions personnalisées en tant que nouvelles colonnes.
  • Les appels WITHIN et les WITHIN RECORD évaluent la condition à l'intérieur des champs répétés dans BigQuery.
  • La condition que comporte le MAX est évaluée en fonction de chaque dimension personnalisée. Pour toutes celles qui ne sont pas des index=1 (hits) ou des index=2 (sessions), la fonction renvoie la valeur NULL.
  • Renvoie la valeur maximale, qui correspond à la valeur de la dimension personnalisée 1 pour les appels ou à celle de la dimension personnalisée 2 pour les sessions, étant donné que toutes les autres valeurs sont NULL.

Exemples de requêtes avancées

Maintenant que vous connaissez les requêtes simples, vous pouvez créer des requêtes à l'aide des fonctions avancées et des fonctionnalités disponibles dans BigQuery.

Produits achetés par les clients qui acquièrent le produit A (e-commerce classique)

Voici le squelette de script pour la question : Quels sont les autres produits que peuvent acheter les clients qui acquièrent le produit A ?

Produits achetés par un client qui acquiert le produit A (e-commerce classique)
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [‘Dataset Name’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘Dataset Name’ ]
  WHERE hits.item.productName CONTAINS 'Product Item Name A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != 'Product Item Name A'
GROUP BY other_purchased_products
ORDER BY quantity DESC;
  1. Dans la première ligne, vous sélectionnez tous les autres articles achetés par un utilisateur, la fonction d'agrégation COUNT() se charge de compter les autres articles achetés. Le résultat s'affiche ensuite dans un champ intitulé quantity et l'article correspondant dans le champ du produit intitulé other_purchased_products.
  2. Dans la sous-requête grise, vous devez sélectionner uniquement les utilisateurs uniques (fullVisitorId) qui ont effectué des transactions (totals.transactions>=1) et qui ont acheté le produit A (WHERE hits.item.productName CONTAINS 'Product Item Name A') au cours d'une transaction.

Les règles (instructions WHERE et AND) dans la requête de premier niveau (verte) ignorent les valeurs hits.item.productName qui sont NULL et qui contiennent le produit A.

Voici un exemple de la requête Si un client achète un lot de quatre stylos en métal Brighton, quel(s) autre(s) produit(s) a-t-il acheté(s) ?

Produits achetés par un client qui acquiert "un lot de quatre stylos en métal Brighton" le 24 juin 2013
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [GoogleStore.ga_sessions_20130624]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [GoogleStore.ga_sessions_20130624]
  WHERE hits.item.productName CONTAINS 'Brighton Metallic Pens - Set of 4'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Brighton Metallic Pens - Set of 4'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

Dans Dremel/BigQuery, l'utilisation de WHERE expr IN déclenche un JOIN et des restrictions de taille s'appliquent ; plus précisément, la taille du côté droit du JOIN (dans ce cas, le nombre de visiteurs) doit être inférieure à 8 Mo. Dans Dremel, cette opération porte le nom de broadcast JOIN. Lorsque la taille est supérieure à 8 Mo, vous devez déclencher un shuffled JOIN à l'aide de la syntaxe JOIN EACH. Malheureusement, cette requête ne peut pas être réalisée avec IN, mais il est toutefois possible de la réécrire avec la commande JOIN.

Produits achetés par les clients qui acquièrent le produit A (commerce électronique amélioré)

Il s'agit du même squelette de requête que le précédent, mais celui-ci s'applique au commerce électronique amélioré. Il utilise également les requêtes de données PLAGE_DE_DATES_DU_TABLEAU sur plusieurs jours.

Produits achetés par un client qui acquiert le produit A (e-commerce amélioré)
SELECT hits.product.productSKU AS other_purchased_products, COUNT(hits.product.productSKU) AS quantity
FROM SELECT fullVisitorId, hits.product.productSKU, hits.eCommerceAction.action_type FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14')))
WHERE fullVisitorId IN (
  SELECT fullVisitorId
FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14'))
WHERE hits.product.productSKU CONTAINS '10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY fullVisitorId )
AND hits.product.productSKU IS NOT NULL
AND hits.product.productSKU !='10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

Nombre moyen d'interactions de l'utilisateur avant l'achat

Voici un exemple de requête d'une commande JOIN() [...] ON, qui repose uniquement sur des données Analytics.

Voici le squelette de script pour la question Quel est le nombre moyen d'interactions de l'utilisateur avant un achat ?

Nombre d'interactions de l'utilisateur avant un achat
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_1’
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_2’
ON Alias_Name_1.hits.item.productSku = Alias_Name_2.hits.item.productSku;
  1. La première ligne réalise l'opération mathématique principale pour déterminer le nombre moyen d'interactions de l'utilisateur par produit, et cette requête crée une jointure entre deux sous-requêtes appelées ‘Alias_Name_1’ et ‘Alias_Name_2’.
  2. ‘Alias_Name_1’ sert à générer un champ qui utilise la fonction d'agrégation SUM() pour regrouper tous les numéros d'appel enregistrés pour un produit.
  3. ‘Alias_Name_2’ sert à déterminer le nombre d'appels effectués par les utilisateurs pour chaque produit, en utilisant la fonction COUNT().
  4. La dernière ligne affiche le champ (hits.item.productSku ) commun aux deux ensembles de données lors de la jointure.

Voici un exemple de la requête Quel est le nombre moyen d'interactions de l'utilisateur avant un achat au 10 septembre 2013 ?

Nombre d'interactions de l'utilisateur avant un achat, au 10 septembre 2013
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku  ) AS one
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS two
ON one.hits.item.productSku = two.hits.item.productSku;

Pourcentage de stock vendu par produit

Voici l'exemple d'une requête qui ne dépend pas seulement des données Analytics, mais aussi des données n'appartenant pas à Analytics. En combinant les deux ensembles de données, vous pouvez commencer à comprendre le comportement de l'utilisateur à un niveau plus segmenté. Vous pouvez importer des données n'appartenant pas à Analytics dans BigQuery, mais gardez à l'esprit que cela va être pris en compte dans vos frais mensuels de stockage de données.

Voici un squelette de script pour la question : Quel est le pourcentage de stock vendu par produit ?

Pourcentage de stock vendu par produit
SELECT AnalyticsImport.product_data_20130728.productId, ((( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold )) * 100 ) AS percentage_of_stock_sold
FROM [ ‘Imported_DataSet’ ]
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM [‘GA Dataset’ ]
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS ‘Alias_Name’
ON Imported_DataSet.’productId_field’ = Alias_Name.hits.item.productSku;
  1. La première ligne génère deux champs : le premier contient tous les identifiants produit et le second correspond à une opération mathématique qui affiche le pourcentage de stock vendu pour cet identifiant produit.
  2. Étant donné que cette requête dépend de deux ensembles de données, vous devez utiliser la fonction JOIN() ... ON. Cette commande permet de joindre les lignes des deux ensembles de données en se basant sur le champ qu'ils ont en commun. Dans ce cas, les deux ensembles de données sont [ ‘Imported_DataSet’ ] et ‘Alias_Name’.
  3. [ ‘Imported_DataSet’ ] représente la donnée n'appartenant pas à Analytics. C'est l'ensemble de données qui contient le champ de la métrique relative au stock restant (Imported DataSet.'stock_left_field') et le champ de la dimension indiquant l'identifiant produit (Imported_DataSet.'productId_field').
  4. ‘Alias_Name’ est le nom attribué aux données renvoyées par la sous-requête grise. Cette sous-requête utilise des données Google Analytics pour déterminer la quantité totale d'articles vendus par produit.
  5. La dernière ligne utilise l'instruction ON pour afficher le champ commun et la jointure entre les deux ensembles de données.

Dans cette requête, de nombreuses variables ont pour préfixe le nom de l'ensemble de données auquel elles sont associées (par exemple, Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Ceci permet de vous indiquer clairement le champ que vous sélectionnez et de mettre en évidence l'ensemble de données auquel il appartient.

Voici un exemple de la requête Quel a été le pourcentage de stock vendu par produit le 28 juillet 2013 ?

Pourcentage de stock vendu par produit le 28 juillet 2013
SELECT AnalyticsImport.product_data_20130728.productId, ( ( ( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold ) ) * 100 ) AS percentage_of_stock_sold
FROM AnalyticsImport.product_data_20130728
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM GoogleStore.ga_sessions_20130728
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS one
ON AnalyticsImport.product_data_20130728.productId = one.hits.item.productSku
ORDER BY percentage_of_stock_sold DESC;

Rentabilité de chaque produit

Voici un squelette de script pour la question : Quelle est la rentabilité de chaque produit ?

Bénéfice par produit
SELECT Alias_Name.hits.item.productSku, ( Imported_DataSet.’product profit field’ * Alias_Name.quantity ) AS profit
FROM (
  SELECT Alias_Name.hits.item.productSku, Imported_DataSet.’product profit field’
  FROM [ ‘Imported Data Set’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘GA Dataset Name’ ]
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS ‘Alias_Name’
  ON Imported_DataSet.productId = Alias_Name.hits.item.productSku );
  1. La première ligne contient l'opération mathématique permettant de calculer le bénéfice total réalisé sur chaque produit.
  2. La sous-requête grise utilise les données n'appartenant pas à Analytics. Elle recueille les données relatives au bénéfice réalisé suite à la vente d'un produit.
  3. La sous-requête rouge correspond à la sous-requête de données Analytics, qui sera jointe avec les données n'appartenant pas à Analytics. Cette sous-requête calcule la quantité d'articles vendus par produit.
  4. La dernière ligne utilise l'instruction ON pour préciser le champ commun aux deux ensembles de données. Dans ce cas, il s'agit du numéro d'identifiant produit.

Voici un exemple de la requête : Quelle était la rentabilité de chaque produit le 28 juillet 2013 ?

Bénéfice par produit le 28 juillet 2013
SELECT two.hits.item.productSku, ((AnalyticsImport.product_data_20130728.productprice-AnalyticsImport.product_data_20130728.productcost)*two.quantity) AS profit
FROM (
  SELECT two.hits.item.productSku, AnalyticsImport.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice
  FROM AnalyticsImport.product_data_20130728
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM GoogleStore.ga_sessions_20130728
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS two
  ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku );

Le bénéfice est calculé en déterminant la différence entre le prix de vente du produit et son coût de fabrication. Ces informations sont stockées dans l'ensemble de données n'appartenant pas à Google Analytics.

Rentabilité réelle de chaque produit (en prenant en compte les remboursements)

Voici un squelette de script pour la question : Quelle est la véritable rentabilité de chaque produit ?

Bénéfice réel par produit
SELECT Alias_Name.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT Alias_Name.hits.item.productSku, ( ( Imported_DataSet.productprice - Imported_DataSet.productcost ) * Alias_Name.quantity ) AS gross_profit, ( ( Imported_DataSet.refunddeliveryprice + Imported_DataSet.productprice ) * Imported_DataSet.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT Alias_Name.hits.item.productSku, Imported_DataSet.productcost, Alias_Name.quantity, Imported_DataSet.productprice, Imported_DataSet.refunddeliveryprice, Imported_DataSet.refundquantity
    FROM [ ‘Imported DataSet Name’ ] AS 'Imported_DataSet'
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘GA Dataset Name’ ]
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS 'Alias_Name'
    ON Imported_DataSet.productId = Alias_Name.hits.item.productSku ) );
  1. Ceci est une requête très semblable à la requête Quelle est la rentabilité de chaque produit ? Les seules différences résident dans l'ensemble de données n'appartenant pas à Analytics contenu dans la sous-requête grise et dans l'opération mathématique, située à la première ligne, qui permet de calculer le bénéfice réel.
  2. Dans l'ensemble de données n'appartenant pas à Analytics, vous calculez également le montant total de l'argent dépensé pour les remboursements (dans l'instruction SELECT de la sous-requête rouge).
  3. Ensuite, vous effectuez une opération mathématique sur la ligne 1 pour trouver le bénéfice réel en soustrayant les revenus dépensés pour les remboursements de votre bénéfice brut.

Pour plus d'informations sur la requête, veuillez consulter la section sur la rentabilité de chaque produit.

Voici un exemple de la requête : Quelle était la véritable rentabilité de chaque produit le 28 juillet 2013 ?

Bénéfice réel par produit le 28 juillet 2013
SELECT two.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT two.hits.item.productSku, ( ( AnalyticsImport.product_data_20130728.productprice - AnalyticsImport.product_data_20130728.productcost ) * two.quantity ) AS gross_profit, ( ( AnalyticsImport.product_data_20130728.refunddeliveryprice + AnalyticsImport.product_data_20130728.productprice ) * AnalyticsImport.product_data_20130728.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT two.hits.item.productSku, Analytics.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice, AnalyticsImport.product_data_20130728.refunddeliveryprice, AnalyticsImport.product_data_20130728.refundquantity
    FROM AnalyticsImport.product_data_20130728
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM GoogleStore.ga_sessions_20130728
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS two
    ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku ) );

Le bénéfice réel tient compte de la rentabilité d'un produit après avoir pris les produits remboursés en considération. Pour calculer le total des revenus associés aux remboursements d'un produit :

Total des revenus associés aux remboursements pour un produit = (le prix du produit + le prix de la livraison du remboursement pour le produit) * la quantité de produits remboursés

Ces informations vous-ont elles été utiles ?

Comment pouvons-nous l'améliorer ?

Vous avez encore besoin d'aide ?

Essayez les solutions ci-dessous :

Publier dans la communauté d'aide Obtenez des réponses de membres de la communauté
Contactez-nous Donnez-nous plus de détails pour que nous puissions vous aider
true
learning path
Choisissez votre propre parcours de formation

Accédez à google.com/analytics/learn, une nouvelle ressource qui vous aidera à exploiter tout le potentiel de Google Analytics 4. Ce site Web inclut des vidéos, des articles et des procédures guidées, ainsi que des liens vers différentes sources d'information sur Google Analytics (Discord, blog, chaîne YouTube et dépôt GitHub).

Commencer la formation

Recherche
Effacer la recherche
Fermer le champ de recherche
Menu principal
7135029336521025729
true
Rechercher dans le centre d'aide
true
true
true
true
true
69256
false
false