Besoin

Le but de l’analyse de cohorte est d’avoir une analyse qui nous permette de voir les résultats d’une cohortes donnée au cours du temps pour observer la rétention des clients.

Il n’est pas possible de le faire actuellement dans la Viz de Biron car cela demande d’avoir dans un même tableau deux informations avec des granularité d’agrégation différentes :

• Le nombre total de personne dans une cohorte
• Le nombre de personne d’une cohorte

Nous pouvons contourner cette limite grâce au NexusQL.

Requête finale

Plan

Pour réaliser l’analyse, nous aurons besoin de plusieurs requêtes :

1. Une requête pour récupérer la taille de chaque cohorte client
2. Une requête pour aller chercher les performances des clients par cohorte et par mois d’activité
3. Une requête finale pour faire la jointure entre nos deux précédentes requêtes

Requêtes

Taille Cohorte

Dans cette première requête on cherche à avoir pour chaque cohorte le nombre total d’utilisateur dans la cohorte

Pour cela on a besoin de récupérer :

• Le mois de la cohorte via la dimension Client - Mois de premier Achat
  • DIMENSION( 'customer_first_order_date_month' )
• La quantité de clients dans la cohorte avec la métrique Nb Clients
  • metric( 'customers_first_order_date.n_actives' )

<aside> 🚨 On n’oubliera pas d’ajouter la date de référence sur laquelle on fait notre analyse : Ici on étudie tous les clients qui ont fait leur première commande sur l’année 2022

</aside>

SELECT
  DIMENSION( 'customer_first_order_date_month' )    AS `Mois Cohorte`,
  metric( 'customers_first_order_date.n_actives' )  AS `Nb Utilisateurs Cohorte`
FROM
  datamodel
WHERE
  refDate BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY
  `Mois Cohorte`

Performances Clients

On récupère ensuite l’activité des clients pour chaque mois d’activité du client et par cohorte.

Pour cela on a besoin de récupérer :

• Le mois de la cohorte via la dimension Client - Mois de premier Achat
  • DIMENSION( 'customer_first_order_date_month' )
• Le mois d’activité du client grâce à la date de référence avec une granularité mensuelle
  • refDateDimension( INTERVAL 1 MONTH )
• Le nombre d’utilisateur actif de la cohorte sur le mois donné grâce à la métrique Nb Clients de la vue Commande
  • metric( 'transactions_order_date.billing_n_customers' )
• J’ai ajouté d’autres métriques pour faire des analyses supplémentaire comme l’évolution du panier moyen par exemple

<aside> 🚨 Pour que la requête soit fonctionnelle, on doit ajouter dans le GROUP BY chaque dimension appellée dans la requête, ici Mois Activité et Mois Cohorte

</aside>

SELECT
  refDateDimension( INTERVAL 1 MONTH )                          AS `Mois Activité`,
  DIMENSION( 'customer_first_order_date_month' )                AS `Mois Cohorte`,
  metric( 'transactions_order_date.billing_n_customers' )       AS `Nb Utilisateurs Actifs`,
  metric( 'transactions_order_date.billing_ttc' )               AS `CA TTC`,
  metric( 'transactions_order_date.refund_billing_ttc' )        AS `Remboursement TTC`,
  metric( 'transactions_order_date.billing_n_orders' )          AS `Nb de Commande Facturées`,
  metric( 'transactions_order_date.billing_n_products' )        AS `Nb Produits Vendus`,
  metric( 'transactions_order_date.refund_billing_n_products' ) AS `Nb Produits Remboursés`
FROM
  datamodel
WHERE
    refDate BETWEEN '2022-01-01' AND '2022-12-31'
  AND `Mois Cohorte` LIKE '2022%'
GROUP BY
  `Mois Activité`,
  `Mois Cohorte`

Requête de calcul

Il ne reste plus qu’a faire le lien entre nos deux première requêtes grâce à un LEFT JOIN sur la clé commune des deux tables Mois Cohorte

On peut alors chercher les données qui nous intéressent des deux précédentes requêtes.

On peux exporter le résultats de cette requête et l’intégrer dans un fichier Excel pour faire un tableau à double entrée de cohorte

SELECT
	t1.`Mois Cohorte`                                                     AS `Mois Cohorte`,
	t1.`Mois Activité`                                                    AS `Mois Activité`,
	COALESCE( t2.`Nb Utilisateurs Cohorte`, 0 )                           AS `Nb Utilisateurs Cohorte`,
	t1.`Nb Utilisateurs Actifs`                                           AS `Nb Utilisateurs Actifs`,
  100 * round( `Nb Utilisateurs Actifs` / `Nb Utilisateurs Cohorte`, 4) AS `% Utilisateurs Actifs`
	t1.`CA TTC`                                                           AS `CA TTC`,
	t1.`Remboursement TTC`                                                AS `Remboursement TTC`,
	t1.`Nb de Commande Facturées`                                         AS `Nb de Commande Facturées`,
	t1.`Nb Produits Vendus`                                               AS `Nb Produits Vendus`,
	t1.`Nb Produits Remboursés`                                           AS `Nb Produits Remboursés`
FROM
	 *{Requête Performance Client}* AS t1
	 LEFT JOIN *{Requête Taille Cohorte}* AS t2
			ON t1.ON t1.`Mois Cohorte` = t2.`Mois Cohorte`
ORDER BY
	`Mois Activité` DESC,
	`Mois Cohorte` DESC
;

Requête finale

SELECT
	t1.`Mois Cohorte`                           AS `Mois Cohorte`,
	t1.`Mois Activité`                          AS `Mois Activité`,
	COALESCE( t2.`Nb Utilisateurs Cohorte`, 0 ) AS `Nb Utilisateurs Cohorte`,
	t1.`Nb Utilisateurs Actifs`                 AS `Nb Utilisateurs Actifs`,
	t1.`CA TTC`                                 AS `CA TTC`,
	t1.`Remboursement TTC`                      AS `Remboursement TTC`,
	t1.`Nb de Commande Facturées`               AS `Nb de Commande Facturées`,
	t1.`Nb Produits Vendus`                     AS `Nb Produits Vendus`,
	t1.`Nb Produits Remboursés`                 AS `Nb Produits Remboursés`
FROM
	( SELECT
		  refDateDimension( INTERVAL 1 MONTH )                         AS `Mois Activité`,
		  DIMENSION( 'customer_first_order_date_month' )              AS `Mois Cohorte`,
		  metric( 'transactions_order_date.billing_n_customers' )       AS `Nb Utilisateurs Actifs`,
		  metric( 'transactions_order_date.billing_ttc' )              AS `CA TTC`,
		  metric( 'transactions_order_date.refund_billing_ttc' )       AS `Remboursement TTC`,
		  metric( 'transactions_order_date.billing_n_orders' )         AS `Nb de Commande Facturées`,
		  metric( 'transactions_order_date.billing_n_products' )        AS `Nb Produits Vendus`,
		  metric( 'transactions_order_date.refund_billing_n_products' ) AS `Nb Produits Remboursés`
	  FROM
		  datamodel
	  WHERE
		    refDate BETWEEN '2022-01-01' AND '2022-12-31'
	    AND `Mois Cohorte` LIKE '2022%'
	  GROUP BY
		  `Mois Activité`,
		  `Mois Cohorte` ) AS t1
	LEFT JOIN ( SELECT
		            DIMENSION( 'customer_first_order_date_month' ) AS `Mois Cohorte`,
		            metric( 'customers_first_order_date.n_actives' )  AS `Nb Utilisateurs Cohorte`
	            FROM
		            datamodel
	            WHERE
		            refDate BETWEEN '2022-01-01' AND '2022-12-31'
	            GROUP BY
		            `Mois Cohorte` ) AS t2
	          ON t1.`Mois Cohorte` = t2.`Mois Cohorte`
ORDER BY
	`Mois Activité` DESC,
	`Mois Cohorte` DESC
;