BigQuery (un des services de Google Cloud Platform) est un cloud data warehouse dans lequel vous pouvez stocker et travailler vos données avant de les visualiser dans une solution de data visualisation comme Looker Studio ou l’un de ses principaux concurrents (Power BI, Tableau).
La combinaison de BigQuery et Looker Studio vous offre une flexibilité sans limites pour visualiser vos données.
Contrairement à ce que vous pourriez penser, BigQuery est accessible. Vous n’avez pas besoin d’être ingénieur pour commencer à l’utiliser correctement !
Cet outil, c’est notre dada.
Nous l’utilisons tous les jours depuis 3 ans avec nos clients et nous le maîtrisons parfaitement !
Nous avons donc décidé de vous partager gratuitement une formation introductive sur le sujet 🤓
La documentation complète de BigQuery est disponible ici. Le but de cette formation n’est pas de faire un copier-coller de la documentation officielle, mais de vulgariser certains concepts clés et vous donner des exemples concrets.
Cette formation est une introduction à BigQuery et s’adresse à tout niveau de compétence !
Nous ne rentrerons pas en détail dans les concepts relatifs au fonctionnement et à l’utilisation de Google Cloud Platform car il y a beaucoup trop à dire et la documentation de Google à ce sujet est suffisamment complète et explicite.
Allez c’est parti, accrochez-vous, nous n’avons pas fait les choses à moitié ! 🤓 📚
Nos autres ressources sur Google BigQuery
Combien coûte la visualisation des données Google Analytics 4 avec BigQuery et Looker Studio ?
Comment optimiser le temps de chargement d’un rapport Looker Studio connecté à BigQuery ?
Comment connaître les coûts BigQuery engendrés par rapport Looker Studio ?
Quel est l’intérêt d’alimenter des audiences publicitaires depuis BigQuery ?
Comment créer un sélecteur de période de comparaison sur Looker Studio en utilisant BigQuery ?
Google Cloud Platform (GCP) est une suite de services cloud offerts par Google pour les entreprises et les développeurs.
Un service cloud est un service qui est fourni en ligne. Google Cloud Platform regroupe des services cloud orientés sur l’informatique. On parle alors de “cloud computing”.
Le cloud computing est un modèle qui vous permet d’accéder à des ressources informatiques en tant que service, sur demande via Internet, plutôt que de les stocker localement (chez vous). Cela signifie que vous pouvez accéder à ces ressources de n’importe où et à tout moment, sans avoir à vous inquiéter de la maintenance ou de la gestion des ressources elles-mêmes.
Les principaux avantages du cloud computing sont les suivants :
Google Cloud Platform offre une gamme complète de services informatiques répondant (en partie) à de multiples besoins issus du développement/déploiement d’application, du big data, de l’analyse de données et du machine learning.
Nous y retrouvons principalement des services de calcul, de stockage, de gestion de base de données, de mise en réseau, de sécurité, de machine learning et d’analyse de données.
Les 2 principaux concurrents de Google Cloud Platform sont Microsoft Azure et Amazon Web Services.
Voici les services de Google Cloud Platform que nous utilisons le plus souvent pour nos missions au sein de l’agence data marketing Boryl :
Nous vous conseillons de lire cette documentation pour comprendre l’environnement global de Google Cloud Platform et ses différents concepts.
BigQuery est le cloud data warehouse proposé par Google.
Un data warehouse est sorte de base de données sur laquelle vous pouvez stocker et analyser de grandes quantités de données via du SQL (Structured Query Language) très rapidement.
Ce type de base de données se caractérise par plusieurs éléments, voici les principaux :
En liant les principaux avantages du cloud computing (cité dans la partie précédente) et les éléments qui caractérisent un data warehouse (cité juste au-dessus), vous devriez comprendre ce qu’est un cloud data warehouse ainsi que ses avantages 🙂
Voici quelques informations majeures, propres au cloud data warehouse BigQuery :
Pour utiliser BigQuery (ainsi que les autres services proposés par Google Cloud Platform), vous devez créer un compte de facturation.
Un compte de facturation représente votre profil de paiement. Il peut être soit de type “entreprise” soit de type “particulier”.
Si vous n’avez jamais été un client payant de Google Cloud Platform, vous pouvez bénéficier d’une période d’essai de 90 jours incluant 300 $ de crédits gratuits pour tester les différents services proposés (dont BigQuery).
Pour créer un compte de facturation, rendez-vous ici, cliquez sur “Profiter d’un essai gratuit”, renseignez les informations demandées dans les 2 étapes, puis cliquez sur “Démarrer l’essai gratuit” afin de finaliser la création de votre compte de facturation.
Sur Google Cloud Platform, un projet est un “conteneur logique” regroupant généralement des ressources liées à une même “finalité”. C’est une entité obligatoire pour utiliser les différents services proposés par Google Cloud Platform.
Un compte de facturation (une entreprise) peut disposer de plusieurs dossiers, pouvant inclure plusieurs projets, pouvant inclure plusieurs ressources.
Grâce à IAM (le service de gestion des identités et des autorisations sur Google Cloud Platform), vous pouvez contrôler qui a accès à quelles ressources en définissant une stratégie.
Vous pouvez définir une stratégie IAM au niveau d’une organisation, au niveau d’un dossier, au niveau d’un projet ou, dans certains cas, au niveau d’une ressource.
Vous trouverez plus d’informations sur les projets ici.
Il existe trois méthodes pour créer un projet :
Dans ce cas pratique, nous utiliserons la première méthode (depuis la console Google Cloud).
Pour créer un projet, rendez-vous ici, renseignez les informations demandées, puis cliquez sur “Créer” afin de finaliser la création de votre projet.
L’interface graphique de BigQuery vous permet de créer et gérer des ressources, et d’exécuter des requêtes SQL.
Pour accéder à cette interface, rendez-vous ici.
Nous ne rentrerons pas dans les détails de l’interface, car cette documentation est déjà très complète et l’interface très intuitive.
Voici à quoi ressemble l’interface graphique de BigQuery :
Nous y retrouvons 4 éléments principaux.
1 – Le sélecteur de projet
En cliquant ici (cf. flèche ci-dessus) vous pouvez sélectionner le projet sur lequel vous voulez travailler.
2 – Le menu de navigation BigQuery
Depuis ce menu, vous pouvez sélectionner des onglets offrant des fonctionnalités/services d’analyse, de migration ou d’administration.
Les deux onglets généralement les plus utilisés sont “Espace de travail SQL” et “Requêtes programmées”.
L’onglet “Espace de travail SQL” vous permet d’afficher vos projets, ensembles de données, tables, vues et autres ressources ainsi que de travailler dessus.
L’onglet “Requêtes programmées” affiche les requêtes SQL que vous avez planifiées.
3 – La section explorateur
Cette section est visible uniquement lorsque vous sélectionnez “Espace de travail SQL” dans le menu de navigation BigQuery.
Elle liste vos projets, ainsi que leurs ensembles de données, tables, vues et autres ressources.
Depuis cette section, vous pouvez réaliser plusieurs actions, notamment :
4 – La section détails
Cette section affiche les informations relatives à l’ensemble de données, la table, la vue ou une autre ressource sélectionnée dans la section explorateur. C’est également dans cette section que vous pouvez créer et exécuter des requêtes SQL, exporter des résultats, etc.
Depuis cette section vous pouvez réaliser plusieurs actions, notamment :
Il existe plusieurs méthodes pour envoyer des données dans BigQuery. Vous trouverez toutes les informations relatives à l’envoi de données vers BigQuery ici.
Chez Boryl, de notre côté, nous avons développé +100 connecteurs API nous permettant d’extraire des données depuis les outils les plus utilisés et de les envoyer dans des tables de données brutes BigQuery.
Voici les connecteurs propriétaires que nous utilisons le plus avec nos clients :
Dans cette formation, nous allons aborder 2 méthodes :
Avant d’envoyer des données vers BigQuery vous devez créer un ensemble de données dans un projet.
Un ensemble de données est un “conteneur logique” regroupant des tables et vues liées à une même “finalité”. C’est une entité obligatoire pour créer des tables et vues. Les ensembles de données permettent d’organiser et contrôler l’accès à vos tables et vues.
Vous trouverez toutes les informations relatives aux ensembles de données ici.
Il existe plusieurs méthodes pour créer un ensemble de données, notamment :
Dans ce cas pratique, nous utiliserons la première méthode (depuis la console Google Cloud).
Pour créer un ensemble de données, rendez-vous ici, cliquez sur les trois petits points au niveau du projet concerné, cliquez sur “Créer un ensemble de données”, renseigner les informations demandées puis cliquez sur “Créer l’ensemble de données”
Pour créer une table à partir d’un fichier CSV local, rendez-vous ici, cliquez sur les trois petits points au niveau de l’ensemble de données concerné, cliquez sur “Créer une table”, renseignez les informations demandées (cf. capture ci-dessous en modifiant le fichier sélectionner ainsi que le nom de la table par celui de votre choix) puis cliquez sur “Créer un tableau”.
Google propose une connexion native entre BigQuery et Google Analytics 4. Cette connexion vous permet d’exporter tous les événements bruts de vos propriétés Google Analytics 4 vers BigQuery.
Chaque jour, une exportation complète des événements de la journée passée a lieu. Cette exportation donne lieu à la création d’une nouvelle table de données brute “events_YYYYMMDD” dans l’ensemble de données concerné “analytics_123456789”. Les tables créées peuvent être mises à jour par Google Analytics dans les 72 heures suivant la création.
Pour une propriété standard Google Analytics 4, la limite d’événements exportés par jour est fixée à 1 million.
Il est possible d’activer une exportation en flux continu, afin d’obtenir les données du jour en quelques minutes, mais cette option implique des coûts supplémentaires.
Vous trouverez toutes les informations relatives à la connexion entre BigQuery et Google Analytics 4 ici.
Pour configurer la connexion entre BigQuery et Google Analytics 4, rendez-vous dans la section administration de votre propriété Google Analytics 4, cliquez sur “Associations à BigQuery”, cliquez sur “Associer”, renseignez les informations demandées (cf. capture ci-dessous en modifiant le projet par celui de votre choix) puis cliquez sur “Envoyer”.
Vous n’avez pas besoin de créer un ensemble de données au préalable, il se crée automatiquement une fois que la connexion est établie (son nom sera “analytics_{id_propriété}” – “analytics_123456789”). En revanche, pensez à désactiver l’expiration des tables de cet ensemble de données qui est par défaut fixé à 60 jours.
Pour désactiver l’expiration des tables d’un ensemble de données, rendez-vous ici, cliquez sur l’ensemble de données concerné, cliquez sur “Modifier les détails”, décochez la case “Activer l’expiration de la table” puis cliquez sur “Save”.
Si cela vous intéresse, voici une formation très complète sur Google Analytics 4 :
Formation sur Google Analytics 4 (2023)
L’objectif de ce cas pratique est de créer une première requête SQL vous donnant le nombre de sessions par jour depuis la configuration de la connexion native entre votre propriété Google Analytics 4 et BigQuery.
C’est une requête SQL très simple, mais essentielle (nous vous partagerons pleins d’autres requêtes SQL Google Analytics 4 plus complexes tout au long de l’année 2023 😉).
Pour retrouver le nombre de sessions par jour, vous devez compter le nombre de concaténations “ga_session_id + user_pseudo_id” uniques et différents par jour.
Vous devez utiliser l’ID “user_pseudo_id” en plus de “ga_session_id” car ce dernier n’est pas unique (c’est juste un horodatage du moment où l’événement “session_start” s’est produit). Ainsi, en concaténant les deux, vous aurez toujours un ID de session unique.
Voici la requête SQL :
SELECT
FORMAT_DATE("%Y-%m-%d",PARSE_DATE("%Y%m%d",event_date)) AS date,
COUNT(DISTINCT CONCAT(user_pseudo_id,(
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
key = 'ga_session_id'))) AS session
FROM
`boryl-corporate.analytics_251845694.events_*`
-- Changez 'boryl-corporate.analytics_251845694' par le '{projet}.{ensemble de données}' de votre choix.
GROUP BY
date
ORDER BY
datePour exécuter cette requête, rendez-vous ici, cliquez sur “Saisir une nouvelle requête”, copiez-collez la requête en modifiant le “{projet}.{ensemble de données}”, vérifiez que le voyant est vert en haut à droite puis cliquez sur “Exécuter”.
Voici un aperçu du résultat de notre côté :
L’objectif de ce cas pratique est de créer une table analysée “analysed_session” répertoriant vos sessions par jour (cf. cas pratique précédent) et de la mettre à jour chaque matin à 6h avec les nouvelles données.
Les tables analysées sont généralement celles que l’on connecte aux outils de data visualisation comme Looker Studio, Power BI ou encore Tableau.
Afin de limiter la consommation en puissance de calcul, et prendre en compte les éventuelles mis à jour de données dans le temps (le 72h énoncé un peu plus haut), vous allez exécuter chaque jour une requête SQL qui :
Avant tout, vous devez créer la table analysée “analysed_session” et envoyer l’historique des sessions par jour dans cette dernière en exécutant la requête du cas pratique précédent et modifiant les paramètres de requête.
Pour modifier les paramètres de requête, cliquez sur “Plus”>”Paramètres de requête”, renseignez les informations comme ci-dessous en choisissant l’ensemble de données de votre choix, vérifiez que le voyant est vert en haut à droite, cliquez sur “Enregistrer” puis sur “Exécuter”.
À ce stade votre table analysée est créée et l’historique envoyé. Il ne vous reste plus qu’à automatiser l’exécution de la requête ci-dessous afin de mettre à jour la table analysée chaque matin à 6h.
-- Suppression des données des 7 derniers jours dans la table analysed_session
DELETE
FROM
`boryl-corporate.formation_bigquery.analysed_session`
WHERE
DATE(date) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
-- Envoi des données des 7 derniers jours dans la table analysed_session
INSERT INTO
`boryl-corporate.formation_bigquery.analysed_session` (date,
session)
-- Changez `boryl-corporate.formation_bigquery` par le '{projet}.{ensemble de données}' de votre choix.
SELECT
FORMAT_DATE("%Y-%m-%d",PARSE_DATE("%Y%m%d",event_date)) AS date,
COUNT(DISTINCT CONCAT(user_pseudo_id,(
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
key = 'ga_session_id'))) AS session
FROM
`boryl-corporate.analytics_251845694.events_*`
-- Changez `boryl-corporate.analytics_251845694` par le '{projet}.{ensemble de données}' de votre choix.
WHERE
_table_suffix BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 7 day))
AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 day))
GROUP BY
date
ORDER BY
datePour planifier/automatiser l’exécution de cette requête SQL, cliquez sur “Planifier”>“Créer une nouvelle requête programmée”, renseignez les informations comme ci-dessous (07:00 UTC = 06:00 en France), vérifiez que le voyant est vert en haut à droite puis cliquez sur “Enregistrer”.
Une autre méthode (celle que nous utilisons en interne avec Airflow) consiste à planifier l’exécution d’une première requête “DELETE” puis d’une seconde requête “SELECT” (peu de temps après la première) en modifiant le paramètre “Préférence d’écriture pour la table de destination” de la seconde requête sur “Ajouter à la table”.
Les tables brutes “events_YYYYMMDD” pouvant être créées à des heures différentes chaque jour, vous pouvez répéter le processus de suppression/ajout plusieurs fois par jour à des heures bien différentes (par exemple 6h/12h/18h) afin d’être sûr d’avoir les nouvelles données chaque jour dans la table analysée concernée. Vous devez cependant faire attention à bien optimiser vos requêtes afin de limiter les coûts engendrés par cette répétition de requête.
Voici également un article sur comment exécuter une requête automatiquement lorsque la table “events_YYYYMMDD” se crée :
L’objectif de ce cas pratique est de visualiser dans Looker Studio (avec un graphique de série temporelle) les données de la table analysée “analysed_session” (résultat d’une requête SQL) créée dans le cas pratique précédent et mise à jour chaque jour.
Si cela vous intéresse, voici une formation très complète sur Looker Studio :
Formation sur Looker Studio (2023)
Pour visualiser les données de la table “analysed_session” dans Looker Studio, rendez-vous ici, cliquez sur le “+”, cliquez sur BigQuey, sélectionnez la table “analysed_session”, cliquez sur “Ajouter”, puis créez et configurez un graphique de série temporelle avec “date” en dimension et “session” en métrique.
Voilà, vous venez de créer un graphique (basique) automatisé avec des données brutes issues de Google Analytics 4 !
Nous vous partagerons pleins d’autres requêtes SQL Google Analytics 4 plus complexes/avancées tout au long de l’année 2023 afin que vous puissiez construire un reporting/dashboard complet !
N’hésitez pas à nous envoyer un petit message si vous avez des questions 😉
