Looker pour l'e-commerce : marge, panier, réachat, un seul CA

Un comité de pilotage e-commerce a besoin d'une dizaine d'indicateurs, et chacun d'eux a une bonne définition et trois mauvaises. La méthode qui tient dans la durée consiste à écrire chaque règle une seule fois dans dbt, sur une table de faits au grain de la ligne de commande, puis à ne laisser Looker exposer que des sommes et des ratios de totaux. Nous avons publié un dépôt de référence complet sur le jeu de données public TheLook, avec dbt, LookML, tableau de bord et intégration continue, pour montrer ce que cela donne concrètement.

Les dix indicateurs et leur définition unique

Le tableau ci-dessous reprend le glossaire du dépôt de référence. Chaque ligne a un identifiant, une définition d'une phrase, une colonne dbt et une mesure LookML. Il n'existe pas de deuxième définition ailleurs.

Indicateur	Définition	Le piège habituel
CA brut	Somme du prix de vente de toutes les lignes, quel que soit leur statut	Présenté comme « le CA » au comité
CA net	Somme du prix de vente des lignes ni annulées ni retournées	Le statut est lu sur l'en-tête de commande et non sur la ligne
Marge nette	Prix de vente moins coût unitaire, sur les lignes nettes	Le coût vient de l'ERP avec un décalage d'un mois
Taux de marge	Marge nette divisée par CA net	Moyenne des taux par produit
Commandes	Commandes distinctes ayant au moins une ligne nette	Comptage des lignes au lieu des commandes
Panier moyen	CA net divisé par commandes	Moyenne des paniers par commande, puis moyenne des moyennes
Clients	Clients distincts ayant au moins une ligne nette	Comptage des comptes créés, achat ou non
Clients récurrents	Clients ayant deux commandes nettes ou plus	Deuxième commande annulée comptée comme réachat
Taux de réachat	Clients récurrents divisés par clients	Dénominateur pris sur une autre période que le numérateur
Taux de retour	Lignes retournées divisées par lignes non annulées	Annulations comptées comme retours

La règle « net » est le point décisif. Dans le dépôt, elle vit dans un seul modèle intermédiaire, int_order_items_enriched, sous la forme d'un booléen is_net_sale calculé à partir d'une variable dbt qui liste les statuts exclus. Toutes les tables de marts héritent du drapeau, toutes les mesures LookML le lisent, et l'écart entre CA brut et CA net est exactement la valeur des lignes annulées et retournées : les deux se réconcilient ligne par ligne, ce qui met fin au débat en réunion.

Le piège du grain : commande ou ligne de commande

La plupart des écarts entre dashboards e-commerce viennent d'une confusion de grain. Une commande a un en-tête (date, client, statut global) et des lignes (produit, quantité, prix, statut de la ligne). Une commande de trois articles dont un est retourné a un statut d'en-tête « livrée » et une ligne « retournée ». Si le CA net lit le statut de l'en-tête, il compte l'article retourné.

Le second effet du grain est la multiplication silencieuse. Un explore qui joint commandes, lignes et clients dans un seul modèle produit une ligne par article : la somme du montant de l'en-tête est alors multipliée par le nombre d'articles. Looker sait rattraper cette situation avec les agrégats symétriques, à condition que les clés primaires soient déclarées, mais le SQL généré devient illisible et lent.

Le dépôt de référence choisit la discipline de grain plutôt que le rattrapage. Chaque explore est ancré sur un fait à un seul grain : fct_order_items par ligne, fct_orders par commande, dim_users par client. Toutes les jointures vont du fait vers une dimension en many_to_one, et les mesures qui vivent côté dimension sont retirées des explores de faits avec fields:. Le SQL généré ne contient que des SUM et des COUNT DISTINCT. fct_orders est un agrégat de fct_order_items produit dans dbt, ce qui explique que les deux niveaux concordent au centime.

measure: average_order_value {
  label: "Panier moyen"
  description: "M6. CA net divisé par le nombre de commandes nettes. Ratio de totaux."
  type: number
  sql: ${net_revenue} / NULLIF(${order_count}, 0) ;;
  value_format_name: eur
}

Le panier moyen est un ratio de deux totaux. Une mesure type: average sur une colonne de panier par commande donne un autre chiffre dès que l'on filtre ou que l'on regroupe, et un dashboard qui affiche la moyenne des moyennes mensuelles en donne un troisième.

Shopify, GA4, ERP : les sources dans BigQuery

Une boutique de taille moyenne alimente son entrepôt depuis trois familles de sources, chacune avec son domaine de vérité.

Source	Ce qu'elle porte	Ce qu'elle ne doit pas porter
Plateforme de vente (Shopify, Magento, PrestaShop)	Commandes, lignes, remboursements, clients, catalogue	Le coût d'achat, souvent absent ou saisi à la main
GA4, export natif vers BigQuery	Sessions, événements, sources d'acquisition, conversion	Le chiffre d'affaires officiel
ERP ou logiciel de gestion	Coût unitaire, stock, avoirs, facturation	La notion de session ou de panier

La règle qui évite les réunions difficiles : un système de référence par fait. Le CA vient de la plateforme de vente, la marge de la jointure avec le coût ERP, la conversion de GA4. Le CA mesuré par GA4 est collecté côté navigateur, soumis au consentement et aux bloqueurs, et il manquera toujours quelques pour cent par rapport aux commandes réelles. Il sert à l'attribution, pas au comité.

Deux détails techniques pèsent plus que prévu. La date de référence, d'abord : commande, expédition, facture ou paiement produisent quatre CA mensuels différents, et la couche sémantique doit en désigner un. Le fuseau horaire, ensuite : les horodatages arrivent en UTC et une commande passée à 23 h 30 heure de Paris change de jour selon la conversion. Le dépôt fixe created_date en UTC comme clé de jointure vers le calendrier, avec convert_tz: no, tout en laissant la dimension visible se convertir pour l'affichage.

Ce qui change au-delà de dix analystes

Avec deux ou trois analystes, une définition partagée à l'oral tient à peu près. Au-delà d'une dizaine, personne ne connaît plus toutes les mesures, chaque nouvel explore duplique une logique existante, et les dashboards de direction dépendent de champs que personne n'ose modifier.

Ce que nous observons en production, sur une couche sémantique Looker servant les dashboards de direction d'un grand groupe industriel depuis plusieurs années : ce qui a permis de passer à l'échelle n'est pas un outil supplémentaire, mais quatre habitudes devenues obligatoires.

• Une PR par changement de modèle, relue par une seconde personne, avec le validateur LookML et les tests dbt en intégration continue.
• Une description sur chaque champ, qui commence par l'identifiant du glossaire. Le dépôt de référence fait échouer la CI quand elle manque.
• Des droits au niveau du modèle, pas du dashboard : la marge et les données personnelles derrière un access_grant, les lignes filtrées par pays via un attribut utilisateur.
• Un seul datagroup déclenché par l'arrivée des données, pour que le cache et les tables dérivées se rafraîchissent ensemble plutôt qu'à heure fixe.

Ces habitudes préparent aussi l'étape suivante : un agent conversationnel branché sur Looker n'utilise que les explores, les libellés et les descriptions tels qu'ils existent. Un modèle propre à dix analystes est un modèle prêt pour les agents.

Le dépôt de référence, pas à pas

Le code est public : looker-semantic-layer-thelook, sous licence MIT. Il se déploie en un quart d'heure sur un projet Google Cloud, à partir du jeu de données public bigquery-public-data.thelook_ecommerce.

Architecture. Trois couches dbt : staging (une vue par table source, renommage et typage), intermediate (le seul endroit où la marge et le drapeau « net » sont calculés) et marts (fct_order_items, fct_orders, dim_users, dim_products, dim_dates). Le glossaire dbt/metrics.md liste les treize métriques M1 à M13.

LookML. Une vue par mart, sql_table_name lu depuis une constante @{dataset} pour changer d'environnement en une ligne. Trois explores (order_items, orders, users), tous en many_to_one. Une table dérivée persistée, order_sequence, qui numérote les commandes de chaque client pour répondre à « première commande ou réachat » au grain de la commande. Un datagroup, thelook_daily, déclenché par MAX(created_at) sur le fait de base. Un attribut utilisateur, country_access, qui pilote à la fois le filtre de lignes et l'accès aux champs personnels.

Tableau de bord. executive_overview, six tuiles et deux filtres. Chaque titre de tuile porte l'identifiant du glossaire, et aucune tuile ne calcule quoi que ce soit : elle lit une mesure.

Intégration continue. Un linter maison parse chaque fichier .lkml et refuse les jointures sans relationship, les dates en dur dans les filtres d'explore, les vues sans clé primaire et les champs sans description. Un second job lance dbt build, en ne reconstruisant que les modèles modifiés sur les pull requests.

FAQ

Faut-il dbt si nous avons déjà Looker ?

Looker peut porter la logique dans des tables dérivées, mais il ne la teste pas. dbt apporte les tests (unicité, non-nullité, cohérence avec la source) et une seule couche de transformation lisible par d'autres outils. Sur un périmètre e-commerce, la règle « net » dans dbt et un LookML fin par-dessus est la combinaison qui vieillit le mieux.

Le CA de GA4 et celui de Shopify ne concordent pas, lequel est le bon ?

Celui de la plateforme de vente, parce qu'il correspond à ce qui a été payé. L'écart avec GA4 est normal et devrait rester stable dans le temps ; c'est sa variation qui mérite une alerte, pas son existence.

Peut-on réutiliser le dépôt sur nos données Shopify ?

Oui, c'est l'usage prévu. La couche staging est la seule à connaître les tables source : il suffit de remplacer les cinq modèles stg_ par les tables de votre connecteur. Les couches intermediate, marts et LookML restent inchangées si les colonnes de sortie gardent leur nom.

Comment traiter les retours partiels et les avoirs ?

Au grain de la ligne, un retour partiel est une ligne retournée parmi d'autres lignes nettes de la même commande : le modèle le gère déjà. Un avoir sans retour physique est un événement distinct, à porter dans une table de faits dédiée et à déduire du CA net par une mesure explicite.

Et ensuite

Si votre comité débat encore de la définition du panier moyen, commencez par le glossaire : dix lignes, une définition par ligne, signée par la direction e-commerce. Le dépôt de référence montre ensuite comment ces dix lignes deviennent des tables, des mesures et un tableau de bord.

Voir l'offre Couche sémantique BigQuery, dbt, Looker, et pour aller plus loin : Couche sémantique : pourquoi vos chiffres ne concordent pas et La stack dbt, BigQuery, Looker.