Comparaison interactive de plages de dates personnalisées avec Looker Studio et BigQuery

---

Il y a environ deux ans, j’ai commencé à travailler sur cette solution de comparaison interactive de plages de dates au sein de Data Studio, pour palier à un manque de l’outil. Elle nécessitait d’utiliser BigQuery comme source de données et le contexte de l’époque n’était pas équivalent à celui aujourd’hui, même si les affinités entre Looker Studio et BigQuery étaient déjà fortes.

Aujourd’hui, elles le sont encore plus. De manière générale, BigQuery est beaucoup plus utilisé dans le monde du digital analytics, et l’équipe BI de Google Cloud travaillant sur Looker Studio ne cesse de faire progresser et d’enrichir les fonctionnalités entre les 2 logiciels. La nouvelle intégration native de BigQuery pour LS en est une nouvelle preuve.

Durant ces 2 années, j’ai testé et amélioré cette solution par l’intermédiaire de projets internes et de rapports pour les clients. Durant ces 2 années, BigQuery est aussi devenu un allier quasi systématique en guise de couche de préparation de données pour des rapports Looker Studio pérennes, inscrits dans un dispositif analytics complet, quels que soient les logiciels de collecte de données, avec ou sans réel entrepôt de données d’ingestion et de traitement.

Donc, la solution que je décris dans cet article n’est pas parfaite mais elle permet d’adresser l’une des demandes insatisfaites les plus demandées, en attendant que la fonctionnalité soit prise en charge directement par Looker Studio…

C’est parti.

---

Le résultat

Dans la vidéo ci-dessous, j’ai réalisé un rapport simple présentant des données de ventes par pays. On peut les afficher sur une période principale, et les comparer avec une autre période personnalisée.
Le but du tutoriel sera de reproduire ce rapport.

J’ai choisi de proposer les options de comparaison suivantes, à savoir que l’utilisateur peut comparer les données de la période principale avec les données de :

• L’année précédente
• L’année précédente, les mêmes jours de la semaine
• La période précédente (le même nombre de jours précédant le nombre de jours sélectionnés pour la période principale)
• Un période personnalisée, saisie avec 2 champs de dates en respectant le format « YYYY-MM-DD »

La comparaison est faite uniquement si une case est cochée pour activer la requête (« compare »).
Cela évite de charger des données non voulues durant la saisie des différents champs.

---

---

---

Ingrédients

Pour créer ce rapport, nous allons avoir besoin des éléments suivants :

• Une source de données BigQuery sous forme d’une requête personnalisée
• Des paramètres injectant les souhaits de comparaison dans la requête BigQuery
• Quelques champs calculés au niveau de la source de données
• Des sélecteurs Looker Studio reflétant le souhait de comparaison de l’utilisateur et correspondant directement aux paramètres valorisés de la requête
• Des graphiques couvrant les principales représentations comparatives dont on à généralement besoin dans un rapport personnalisé : series temporelles, tableaux avec variation, histogramme comparatif, KPI sous forme de simple métrique avec variation
• Quelques champs calculés au niveau des graphiques
• Des mises en forme conditionnelles pour les variations

La liste peut paraître longue mais vous allez voir que la fonction de chaque élément est très facile à appréhender, pour une configuration simple.

---

0. Jeu de données pour l’exemple

Pour ce tutoriel, j’ai utilisé un jeu de données très basique de ventes journalières par pays. Regardons rapidement à quoi ressemble la table BigQuery préparée :

• Une date (date – Date avec partition journalière)
• Un continent (continent – Chaîne de caractère)
• Un pays (country – Chaîne de caractère)
• Les ventes (sales – Entier)

---

---

Vous pouvez, biensûr, appliquer ce tutoriel sur n’importe quel jeu de données provenant de BigQuery, avec le nombre de dimensions et de métriques nécessaires pour votre rapport interactif.

Par exemple, dans le monde du digital analytics, on aurait très bien pu préparer une table contenant des dimensions et des statistiques compatibles avec une portée de visites (données de source de trafic, appareils et technologies de l’utilisateur, page d’atterrissage, visites, visites avec conversions, pages vues, temps cumulés des visites…).

Continuons avec cet exemple simple de jeu de données qui va permettre de décrire unitairement chaque étape nécessaire pour aboutir au résultat.

---

1. Source de données : Principe de la requête SQL BigQuery

Pour commencer, allons sur notre rapport Looker Studio et ajoutons une source de données BigQuery de type « Requête personnalisée », en utilisant le projet Google Cloud dan lequel votre table est stockée.

---

Deux périodes, deux sous-requêtes avec une union

La requête que nous allons utiliser se compose de 2 sous-requêtes jointes par une union (UNION ALL) et correspondant aux 2 périodes souhaitées :

• La période principale, choisie dans le rapport avec le sélecteur de plage de dates
• La période de comparaison, définie par la combinaison de paramètres que nous détaillerons plus tard

---

Deux champs supplémentaires de contextualisation pour faciliter la comparaison

En plus des champs énumérés précédents (date, continent, pays, ventes), nous allons ajouter des 2 données supplémentaires aux schémas partagés par ces 2 sous-requêtes :

• La période concernée
  • Le champ « date_range » est alimenté avec la valeur « current » pour la période principale et avec « compared » pour la période de comparaison
• L’ordre temporel de la date pour chaque sous-ensemble (champ row_number) : La première date de la période principale et de la période de comparaison sont valorisées avec l’entier 1. Ce champ permettra d’aligner les dates à comparer, notamment pour les séries temporelles.

---

---

2. Paramètres de la requête personnalisée

Mais avant d’écrire la requête, nous allons d’abord configurer les paramètres qu’elle va utiliser.

---

Paramètres de plage de dates principale

Activer les paramètres de plage de dates (en début de section « Paramètres »).

---

Paramètres personnalisés

Puis, ajouter les paramètres suivants :

• « Compared to »
  • Identifiant « date_range_for_comparison »
  • Type « Texte », avec valeurs suivantes autorisées :
    • Previous year
    • Previous year the same day of week
    • Previous period
    • Custom
  • Cardinalité : Sélection unique
  • Valeur par défaut « Previous year »
• « Compared date range start »
  • Identifiant « compared_date_range_start »
  • Type « Texte », toute valeur autorisée
  • Avec la valeur par défaut « 2023-12-01 » ou toute autre valeur de début de période à comparer, respectant le format choisi
• « Compared date range end »
  • Identifiant « compared_date_range_end »
  • Type « Texte », toute valeur autorisée
  • Valeur par défaut « 2023-12-31 »
• « Compare »
  • Identifiant « compare »
  • Type « Booléen »
  • Coché par défaut (valeur true par défaut)

---

---

3. Source de données : Requête personnalisée SQL BigQuery

Passons à la requête proprement dite et précisons que :

• Nous allons unir la sous-requête des données de période principale et celle des données de période à comparer avec un « UNION ALL »
• La 2nde sous-requête est principalement conditionnée par l’activation de la comparaison avec le booléen « Compare »
• Les conditions liées à chaque type de comparaison correspondent aussi à des conditions dans la 2nde sous-requêtes
• Comparer avec les données de l’année précédente sur les même jours de la semaine, revient à comparer la même période en retirant 52 semaines

Voici donc la requête à saisir en guise de source de données, sur le même écran où vous avez renseigné les paramètres précédemment. Il faut simplement adapter :

• le projet, le dataset et la table dans les clauses « FROM »
• les noms des champs dans les clauses « SELECT »

---

SELECT  
    date, continent, country, sales
   ,"current" as date_range
   ,DATE_DIFF( date, PARSE_DATE('%Y%m%d',  @DS_START_DATE), DAY)+1 as row_number
 FROM your-project.your_dataset.viz_sales_by_country
 WHERE TRUE
 AND 
 date between PARSE_DATE('%Y%m%d',  @DS_START_DATE) AND PARSE_DATE('%Y%m%d',  @DS_END_DATE)

 UNION ALL

 SELECT  
    date, continent, country, sales
   ,"compared" as date_range
   ,DATE_DIFF( date, 
      CASE @date_range_for_comparison
       WHEN 'Custom' THEN PARSE_DATE('%Y-%m-%d', @compared_date_range_start)
       WHEN 'Previous year' THEN  date_sub(PARSE_DATE('%Y%m%d',  @DS_START_DATE),INTERVAL 1 YEAR)
       WHEN 'Previous year the same day of week' THEN date_sub(PARSE_DATE('%Y%m%d',  @DS_START_DATE),INTERVAL 52 WEEK)
       WHEN 'Previous period' THEN date_sub(PARSE_DATE('%Y%m%d',  @DS_START_DATE),INTERVAL DATE_DIFF(PARSE_DATE('%Y%m%d',  @DS_END_DATE),PARSE_DATE('%Y%m%d',  @DS_START_DATE),DAY)+1 DAY)
      END, DAY)+1 as row_number
 FROM your-project.your_dataset.viz_sales_by_country
 WHERE @compare 
 AND
 (
     (
     @date_range_for_comparison = 'Previous year' 
     AND date between date_sub(PARSE_DATE('%Y%m%d',  @DS_START_DATE),INTERVAL 1 YEAR) AND date_sub(PARSE_DATE('%Y%m%d',  @DS_END_DATE),INTERVAL 1 YEAR)
     )
     OR
     (
     @date_range_for_comparison = 'Previous year the same day of week' 
     AND date between date_sub(PARSE_DATE('%Y%m%d',  @DS_START_DATE),INTERVAL 52 WEEK) AND date_sub(PARSE_DATE('%Y%m%d',  @DS_END_DATE),INTERVAL 52 WEEK)
     )
     OR
     (
     @date_range_for_comparison = 'Previous period' 
     AND date between date_sub(PARSE_DATE('%Y%m%d',  @DS_START_DATE),INTERVAL DATE_DIFF(PARSE_DATE('%Y%m%d',  @DS_END_DATE),PARSE_DATE('%Y%m%d',  @DS_START_DATE),DAY)+1 DAY) AND date_sub(PARSE_DATE('%Y%m%d',  @DS_START_DATE),INTERVAL 1 DAY)   
     )
     OR
     (
     @date_range_for_comparison = 'Custom' 
     AND date between PARSE_DATE('%Y-%m-%d', @compared_date_range_start) AND PARSE_DATE('%Y-%m-%d',  @compared_date_range_end) 
     )
 )

---

---

4. Champs calculés de la source de données

Nous allons à présent compléter la sources de données avec 2 champs calculés – 2 métriques qui nous aideront à simplifier la configuration des graphiques.

---

Sales

SUM(IF(date_range ='current', sales ,null)) 

---

Compared sales

SUM(IF(date_range ='compared', sales ,null)) 

---

5. Sélecteurs Looker Studio

Ensuite, nous allons ajouter quelques sélecteurs pour créer les blocs de sélection de plages de dates principales et de comparaison.

Ajouter un sélectionneur de date classique et les sélecteurs suivants pour le bloc dédié à la période à comparer :

• Une liste déroulante avant le paramètre « Compared to » en guise de champ de contrôle
• 2 zones de saisie en utilisant les paramètres « Compared date range start » et « Compared date range end » comme champs de contrôle, pour la plage de dates personnalisées.
  Dans l’exemple ci-dessous, j’ai ajouter aussi un texte « Custom dates – From » avant le premier champ et un autre texte « To » entre les 2 champs.
• Une case à cocher avec le paramètre »Compare » comme champ de contrôle

Afin de présenter la période servant de comparaison et issue des saisies réalisées avec les champs décrits ci-dessus, j’ai aussi ajouté un graphique Tableau d’une ligne et d’une cellule utilisant pour dimension la formule suivante de champ calculé ci-dessous.

---

CONCAT( "Vs " ,
 FORMAT_DATETIME("%b %e, %Y",
 PARSE_DATE("%Y/%m/%d" , SUBSTR(MIN(IF(date_range= "compared",date,null)), 1, 10)))
 , " - " ,
 FORMAT_DATETIME("%b %e, %Y",
 PARSE_DATE("%Y/%m/%d" , SUBSTR(MAX(IF(date_range= "compared",date,null)), 1, 10)))
 )

---

---

6. Graphiques

Enfin, attaquons-nous à la configuration des différents graphiques présents sur la page.

6.1. Métrique simple avec variation en pourcentage (scorecard)

Pour le scorecard présent au dessus de la série temporelle :

• Source de données : Notre requête BigQuery
• Utiliser la métrique « Sales » (cartouche bleue, avec un S majuscule)
• Pour la variation en dessous de la métrique principale
  • Ajouter un un graphique Tableau d’une ligne et d’une cellule utilisant pour métrique la formule ci-dessous
  • Appliquer 2 règles de forme conditionnelle pour afficher la variation en pourcentage en vert si elle contient un « + » et en rouge si elle contient un « -« 
  • Centrer le contenu de la cellule et placer le graphique en dessous de la métrique principale

---

CONCAT(
 IF (SUM(IF (date_range = 'current', sales ,NULL))>= SUM(IF(date_range = 'compared', sales,NULL)) , "+","")
 ,
 CAST(ROUND(ROUND((SUM(IF(date_range = 'current', sales ,NULL))/    SUM(IF(date_range = 'compared', sales,NULL)))-1,3) *100,1) AS TEXT)
 , "%")

---

---

6.2. Tableau des ventes par continent et par pays

Ce graphique tableau se configure comme suit :

• Onglet « Configurer »
  • Source de données : Notre requête BigQuery
  • Dimensions : continent, country
  • Activer « Afficher le détail » et sélectionner « country comme niveau par défaut »
  • Métriques :
    • Sales (cartouche bleue, avec un S majuscule)
    • Compared sales
    • Un champ calculé au niveau du graphique, nommé « % Δ. », en utilisant la même formule que celle indiquée pour la variation de la métrique simple plus haut. On applique exactement le même principe sur une ventilation par pays et par continent
  • Trier par Sales par ordre décroissant
• Onglet « Style »
  • Appliquer 2 règles de forme conditionnelle pour afficher la variation (% Δ.) en pourcentage en vert si elle contient un « + » et en rouge si elle contient un « -« 

---

---

6.3. Histogramme avec ventes par pays

Appliquer les détails de configuration d’un « Graphique à barres » ci-dessous :

• Onglet « Configurer »
  • Source de données : Notre requête BigQuery
  • Dimension : country
  • Dimension « Répartition » : date_range
  • Métrique : sales (cartouche verte avec s minuscule)
  • Trier par sales par ordre décroissant
  • Tri secondaire : Formule ci-dessous en ordre croissant avec une agrégation en Moyenne
• Onglet « Style »
  • Appliquer les couleurs par valeurs de dimensions : Dans l’exemple, rouge foncé pour « current » et rouge clair pour « compared ».
  • Adapter les autres options de présentation

Vous pouvez aussi recréer votre propre légende en reportant des formules de calculs de plages de dates dans des graphiques tableau d’une cellule (cf. le chapitre 5).

---

Pour le tri secondaire

IF (date_range='current', 1, 2)

---

---

6.4. Tendance des ventes

Enfin, nous allons créer le graphique de série temporelle en s’appuyant sur une combinaison de données pour aligner les points des 2 périodes.

Combiner donc les données comme décrit ci-dessous et nommer le résultat « DB – For trends ».

• Table de gauche
  • Source de données : Notre requête BigQuery
  • Dimensions : row_number, date
  • Métriques : Sales (cartouche bleue, avec un S majuscule)
• Table de droite
  • Source de données : Notre requête BigQuery
  • Dimensions : row_number
  • Métriques : Compared sales
• Jointure entre les 2 tables
  • Externe gauche
  • Condition de jointure : row_number pour les 2 tables

---

---

Puis, créer le graphique de série temporelle :

• Onglet « Configurer »
  • Source de données : DB – For trends (notre combinaison)
  • Dimension : date
  • Métriques : Sales, Compared Sales
• Onglet « Style »
  • Série #1 : Utilisation de la couleur correspondant à la période principale, ici le rouge foncé
  • Série #2 : Utilisation de la couleur correspondant à la période de comparaison , ici le rouge clair
  • Adapter les autres options de présentation

Comme pour l’histogramme, vous pouvez aussi recréer votre propre légende en reportant des formules de calculs de plages de dates dans des graphiques tableau d’une cellule (cf. le chapitre 5).

---

---

Et voilà, la configuration est terminée. Je concède que cela peut paraître fastidieux mais c’est très utile sur un rapport interactif, souvent consulté par comparaison temporelle de performance.

---

Gestion de vos requêtes de données par BigQuery et Looker Studio

Pour terminer, vous pourriez vous demander légitimement comment BigQuery et Looker Studio gèrent les requêtes personnalisées générées par l’interface que nous venons de créer.

• Les données sont mises en cache. Un aller / retour sur 2 options de comparaisons vous permet de le constater
• Par contre, oui, de nouvelles données seront chargées si l’utilisateur personnalise la période principale et/ou de comparaison
• Mais l’utilisation de tables préparées comme dans cet exemple vous permet de scanner/charger un faible volume de données
• Et d’ailleurs, la nouvelle intégration native de BigQuery en cours de déploiement progressif offre de nombreuses nouvelles capacités qui facilitent ce type d’usage, son monitoring grâce à des méta données enrichies, et bien d’autres fonctionnalités avancées.

---

Bonne comparaison temporelle !