Vous avez conçu une super fonctionnalité et votre équipe technique vient de la livrer en production : félicitations ! Maintenant, vous êtes assaillis de questions comme “est-ce que cette fonctionnalité est utilisée ?” ou encore “que font les utilisateurs avec ?”. Pour répondre à ces questions, rien de mieux que regarder la Data !
Pour ça, il existe plein d’outils, mais on peut notamment les diviser en 2 groupes :
- les outils analysant les clics des utilisateurs ou les écrans affichés (les outils incontournables là-dessus sont Amplitude, mais aussi Content Square ou encore MixPanel )
- les requêtes tapant directement dans la base de données de production. Il existe de nombreux outils pour cela, mais personnellement, j’ai appris les requêtes en pratiquant sur MetaBase. C’est un outil très simple qui permet notamment d’afficher les résultats des requêtes sous forme de graphes, camemberts, etc…
On va se concentrer ici sur les requêtes, car les outils d’analytics sont souvent conçus pour être utilisables par des personnes non-tech. De plus, il n’y a rien de plus agaçant que d’être dépendant de l’équipe Data lorsqu’on se pose des questions sur l’utilisation d’une fonctionnalité.
Bien évidemment, il ne s’agit pas ici d’un tutoriel pour devenir expert en requêtes, mais plutôt du minimum pour être autonomes dans vos analyses : un peu ce que je cherchais lorsque j’ai fait mes premières requêtes en 2020 ^^
A noter qu’ici, je vais tout écrire en Postgre SQL. Il existe d’autres types de bases de données, avec des syntaxes légèrement différentes, mais globalement, la logique reste similaire. Rapprochez vous de votre équipe Engineering/Data pour avoir la bonne “grammaire” liée à votre base de données.
Vocabulaire
Une base de données est composée de plusieurs tables, chacune ayant un objectif précis et regroupant toutes les informations pertinentes qui y sont associées. On trouvera par exemple une table pour toutes les informations liées aux clients, une table pour les commandes, une table pour les produits en vente, etc….
Chaque table comporte :
- des colonnes, représentant les attributs de chaque table (par exemple, la table des clients contient un colonne “nom de famille”, une colonne “prénom”, une colonne “sexe”, etc.). A noter qu’une table a toujours une colonne ID qui est un identifiant unique pour chaque ligne (vous pouvez l’imaginer comme le compteur du nombre de lignes)
- des lignes, représentant les éléments stockés dans votre base de données (une ligne pour le client “Jean Dupont”, une ligne pour le client “John Doe”, etc…)
Chaque table peut être reliée à au moins une autre table de votre base de données en faisant correspondre les colonnes de chaque table entre elles (nous reviendrons sur ce point précis un peu plus loin).
En d’autres termes, et de manière très simplifiée, vous pouvez considérer une base de données comme un énorme fichier Excel composé de plein de feuilles (les tables), et chaque feuille peut être liée à au moins une autre feuille en faisant correspondre des colonnes entre elles.
Étude de cas
Le mieux reste encore un exemple concret, et de faire nos propres requêtes au plus vite. Imaginons une base client avec 2 tables :
- une table “users” qui regroupe les noms des utilisateurs, le pays de livraison, leurs dates de naissance, les identifiants de leurs parrains s’ils ont été parrainés par d’autres utilisateurs, et enfin leurs emails.
| ID | Firstnames | Country | Birthday | Referrer ID | |
|---|---|---|---|---|---|
| 1 | Lucie | France | 03-06-1987 | 2 | lucie@xyz.fr |
| 2 | Julien | France | 12-01-1989 | julien@toto.com | |
| 3 | Salomé | Belgique | 19-03-1982 | salome@titi.com | |
| 4 | Maxime | Belgique | 25-10-1996 | 3 | maxime@example.fr |
| 5 | Ibrahim | France | 14-05-1993 | ibrahim@doe.eu |
- une table “orders” qui regroupe une liste de commandes, avec les identifiants des acheteurs, les prix des produits, et leurs dates de commandes.
| ID | Product | Buyer | Price | Created At |
|---|---|---|---|---|
| 1 | PS5 | 2 | 500 | 20-08-2021 |
| 2 | iPhone 12 | 3 | 600 | 12-05-2021 |
| 3 | Ordinateur portable | 5 | 900 | 01-03-2021 |
| 4 | Casque audio | 1 | 200 | 29-10-2021 |
| 5 | Enceinte bluetooth | 2 | 100 | 25-01-2022 |
| 6 | iPhone 12 | 1 | 600 | 24-03-2022 |
En particulier, notez que les colonnes “buyer” et “Referrer ID” comportent des numéros, qui représentent en fait les identifiants des utilisateurs, soit la colonne “id” de la table “users”. Cela signifie par exemple que la PS5 a été commandée par Julien, ou encore que Lucie a été parrainée par Julien.
Les principales expressions
La requête la plus basique
Imaginons que vous voulez obtenir le nom de tous les utilisateurs de votre produit. Vous allez devoir récupérer les informations de la colonne “firstnames” dans votre table “users”. Pour cela, 2 mots clés qui vous serviront dans toutes vos requêtes :
- SELECT qui indique les colonnes que vous allez récupérer
- FROM qui indique la table dans laquelle vous allez chercher
Dans notre cas, ce serait donc :
SELECT
users.firstnames
FROM usersIci, nous obtiendrons le résultat suivant :
| Firstnames |
|---|
| Lucie |
| Julien |
| Salomé |
| Maxime |
| Ibrahim |
NB : comme plusieurs tables peuvent avoir des colonnes avec le même nom (par exemple, la colonne ID existe dans la table “users” et dans la table “orders”), levez l’ambiguïté en préfixant la colonne par la table à laquelle elle appartient : ce n’est pas obligatoire, mais c’est une bonne pratique pour toujours être sûr de quel paramètre on parle. Ainsi, si vous voulez la colonne ID de la table “users“, écrivez users.id.
Si vous aviez voulu en plus les dates de naissance de chaque utilisateur, cela aurait été :
SELECT
users.firstnames,
users.birthday
FROM usersPour obtenir
| Firstnames | Birthday |
|---|---|
| Lucie | 03-06-1987 |
| Julien | 12-01-1989 |
| Salomé | 19-03-1982 |
| Maxime | 25-10-1996 |
| Ibrahim | 14-05-1993 |
Filtrer sur un critère
Maintenant, vous aimeriez connaître le nom des utilisateurs qui sont nés après le 30 Juin 1990. Pour cela, vous allez filtrer votre requête grâce au mot clé WHERE que vous appliquerez sur la colonne “birthday”
SELECT
users.firstnames,
users.birthday
FROM users
WHERE users.birthday > '06/30/1990'NB : les dates sont indiquées avec le format américain, donc MM/DD/YYYY
Résultat :
| Firstnames | Birthday |
|---|---|
| Maxime | 25-10-1996 |
| Ibrahim | 14-05-1993 |
Vous pouvez ajouter autant de conditions que vous le souhaitez à l’aide du mot-clé AND. Par exemple, pour rajouter un filtre sur tous les utilisateurs dont le pays est France, il suffit d’indiquer :
SELECT
users.firstnames,
users.birthday
FROM users
WHERE users.birthday > '06/30/1990' AND users.country = 'France'Il est même possible de faire des filtres sur des valeurs relatives ou des durées. Il existe de nombreux mots-clés très pratiques que vous pouvez chercher sur internet. Les principaux que j’utilise sont les suivants :
- “now()” qui renvoie la date actuelle
- “interval” suivi d’une durée (comme ‘1 day’, ’30 minute’, ou ‘2 year’) qui renvoie la durée demandée
- “date_trunc” qui permet d’extraire un élément d’une date (comme par exemple le jour ou le mois)
Ainsi, si vous voulez récupérer toutes les commandes qui ont été faites depuis moins d’un an :
SELECT
orders.product
FROM orders
WHERE orders.created_at > now() - interval '1 year'Joindre 2 tables entre elles
Imaginons maintenant que vous souhaitez savoir quels sont vos utilisateurs ayant acheté au moins un produit, leur adresse mail, ainsi que le produit qu’ils ont acheté. Pour cela, vous allez devoir faire une jointure entre la table “users” et la table “orders”, grâce au mot clé JOIN. Une jointure est toujours suivi du mot-clé ON, qui indique quelle colonne de la première table correspond à quelle colonne de la seconde table.
SELECT
users.firstnames,
users.email,
orders.product
FROM users
JOIN orders ON users.id = orders.buyeret on obtient
| Firstnames | Product | |
|---|---|---|
| Lucie | lucie@xyz.fr | Casque audio |
| Julien | julien@toto.com | PS5 |
| Salomé | salome@titi.com | iPhone 12 |
| Julien | maxime@example.fr | Enceinte bluetooth |
| Ibrahim | ibrahim@doe.eu | Ordinateur portable |
| Lucie | lucie@xyz.fr | iPhone 12 |
Pour détailler davantage ce qu’il se passe ici, la requête va examiner chaque ligne de la table “users”, puis comparer la valeur “id” de cette table à la valeur “buyer” de la table “orders”. A chaque fois qu’elle trouve 2 valeurs égales, elle va ajouter une nouvelle ligne au résultat avec les informations correspondantes de chaque ligne.
Suivant cette explication, on peut donc constater les points suivants :
- les lignes de Julien et Lucie ont été dupliquées puisqu’ils ont passé chacun 2 commandes
- à l’inverse, Maxime n’a rien commandé et n’est donc pas affiché
Par ailleurs, sachez que vous pouvez faire autant de jointures que vous le souhaitez, vous n’êtes pas obligés de vous limiter à une seule jointure par requête. Il faut juste avoir conscience que plus vous faites de jointures, plus votre requête sera longue à exécuter, donc n’appelez que les tables qui vous sont réellement utiles.
La jointure de 2 tables est vraiment quelque chose d’essentiel car quasiment toutes vos requêtes en feront, il est donc impératif de bien comprendre ce point.
Joindre une table sur elle-même
Dans certains cas bien précis, vous pouvez avoir envie de joindre un table avec elle-même. Ainsi, dans notre cas, nous pouvons joindre la table “users” sur elle même en faisant coïncider les colonnes “users.id” et “users.referral_id”. Cela peut permettre d’avoir par exemple la liste de toutes les personnes ayant été parrainées, et de connaître leurs parrains.
SELECT
users.firstnames,
referred_users.firstnames
FROM users
JOIN users referred_users ON users.id = referred_users.referrer_idNous introduisons en plus le concept d’alias. Cela permet d’ajouter un nouveau nom à une table pour lever les ambiguïtés. Ici, comme nous devons joindre une table sur elle-même et qu’il faut dire quelles colonnes doivent correspondre, impossible d’écrire “users.id = users.referrer_id”. Il faut alors donner un deuxième nom à la table avec laquelle nous faisons la jointure (le fameux alias). Dans notre cas, il s’agit de “referred_users”, que l’on retrouve donc aussi dans les colonnes à afficher dans le SELECT.
Le résultat de cette requête sera alors :
| users.firstnames | referred_users.firstnames |
|---|---|
| Lucie | Julien |
| Maxime | Salomé |
Compter le nombre de lignes
Imaginons que vous souhaitez maintenant compter le nombre de produits qui ont été commandés. Pour cela, nous allons compter le nombre de lignes dans notre table “orders” grâce au mot-clé “count”
SELECT
count(orders.id)
FROM ordersLe résultat renvoyé n’est alors plus un tableau mais bien une valeur unique : 6.
Si maintenant, nous voulons connaître le nombre de produits uniques qui ont été commandé (c’est-à-dire qu’on ne veut pas compter 2 fois l’iPhone 12), il faut ajouter le mot-clé “distinct”
SELECT
count(distinct orders.firstnames)
FROM ordersVous noterez que nous avons compté le nombre de lignes en se basant cette fois-ci sur le nom de l’article, afin de bien distinguer les commandes faites sur un même produit. Le résultat est maintenant égal à 5.
Une requête qui reprend tout ce qu’on a vu
Essayons maintenant de réutiliser tout ce que nous avons vu en une seule requête. Je vous propose donc l’exercice suivant, n’hésitez pas à le faire de votre côté, vous trouverez la solution ci-dessous.
Réalisez une requête renvoyant les noms des utilisateurs qui ont commandé des produits après Avril 2021 et dont chaque commande valait plus que 300€.
SELECT
users.firstnames
FROM users
JOIN orders ON orders.buyer = users.id
WHERE orders.amount > '300' AND orders.created_at > '04/01/2021'Vérifiez vos résultats
Enfin, pour tous ceux qui n’ont pas l’habitude de programmer, n’oubliez jamais de tester et vérifier les résultats de vos requêtes ! Il y a toujours plein de subtilités dans une base de données, des cas particuliers, des conditions supplémentaires que les développeurs ont dû rajouter pour gérer un cas aux limites… Pour cela, il existe plein de manières pour vérifier vos requêtes, parmi lesquelles :
- Comptez le nombre de résultats que vous obtenez (avec le fonction “count” bien sûr) et, avec un regard critique, comparez le aux ordres de grandeurs que vous attendiez ou si votre équipe Data avait sorti des données s’en rapprochant
- Sélectionnez aléatoirement 3 ou 4 éléments du résultat de votre requête (utilisateurs, produit, etc…) et regardez dans votre back-office si leurs critères correspondent aux filtres que vous avez configuré dans la requête.
Bien évidemment, tout ceci n’est pas 100% fiable, mais cela permet souvent d’identifier quelques petites subtilités que vous n’imaginiez pas initialement.
Conclusion
Voilà, vous avez maintenant les bases pour faire des requêtes qui peuvent vous débloquer sur de nombreuses questions de votre quotidien. Il y a plein d’autres possibilités que nous aborderons dans un prochain article, mais maîtriser ces premiers éléments est essentiel avant d’aborder les fonctions plus complexes.
En fonction des attributs de chaque table dans votre base de données, vous pourrez filtrer vos données sur de nombreux critères liés à votre produit. Même des requêtes utilisant des règles aussi simples que celles présentées ici peuvent débloquer de nombreuses situations comme :
- connaître le nombre d’utilisateurs qui ont fait au moins une action (passé une commande par exemple)
- identifier rapidement les utilisateurs qui ont été créés pendant une période donnée
- obtenir les contacts (email, numéros de téléphones, etc…) de vos utilisateurs répondant à un critère précis.
En particulier pour ce dernier point, je me sers très souvent de telles requêtes pour identifier les utilisateurs que je souhaite interviewer.
N’oubliez pas de vous rapprocher de votre équipe Data/Engineering pour savoir comment relier les différentes tables entre elles, puis pratiquez ! C’est en forgeant qu’on devient forgeron, et les requêtes n’échappent pas à cette règle. De plus, vous constaterez vite que dans 90% de vos requêtes, vous utiliserez toujours les mêmes tables. Donc même si cela peut paraitre angoissant au début, vous vous habituerez vite à ce formalisme et les requêtes vous sembleront vite de plus en plus logiques !