Analyser le parcours client avec SQL

3 h env. Débutant
Partie 5
Chapitre 4 sur 5
Partie 5 – 5. Quel est le temps moyen entre la visite de la première page et l’achat?

5.4 Démarches pas à pas, avec code SQL à l’appui

Dans cette section, nous détaillons les différentes expressions de table commune (CTE) utilisées dans notre requête SQL pour calculer le temps moyen entre la première visite de la page et l’achat. Chaque CTE a un rôle spécifique qui aide à structurer et simplifier la requête finale.

5.4.1 CTE lag_data

Cette première CTE, lag_data, enrichisse notre dataset avec des informations sur les événements successifs.

Pour chaque événement enregistré par un utilisateur, elle ajoute les noms et les horodatages des cinq prochains événements.

  • LEAD(event_name, n) OVER(...) : Cette fonction est utilisée pour obtenir le nom du n-ième événement suivant.
  • LEAD(event_timestamp, n) OVER(...) : De même, cette fonction récupère l’horodatage du n-ième événement suivant.

Le partitionnement par user_id et l’ordonnancement par event_timestamp garantissent que l’ordre des événements est respecté pour chaque utilisateur.

5.4.2 CTE purchase_paths

La CTE purchase_paths utilise les données de lag_data pour construire des chemins d’achat potentiels.

Chaque chemin est une concaténation des noms d’événements jusqu’au cinquième événement suivant, séparés par des tirets.

Cette CTE capture également les horodatages correspondants à ces événements, ce qui est nécessaire pour le calcul de la durée entre le début et la fin d’un parcours.

  • Concaténation des événements : permet de visualiser le parcours complet emprunté par l’utilisateur.
  • Horodatages suivants : ces champs sont essentiels pour calculer plus tard la durée totale du parcours.

5.4.3 CTE purchase_duration

Cette CTE se concentre sur les parcours qui mènent à un achat, filtrant les données pour ne garder que les séquences complètes qui finissent par un achat (complete_purchase). Elle calcule la durée du parcours en minutes depuis le début jusqu’à l’achat. La durée est calculée seulement pour les chemins reconnus comme menant à un achat.

  • Conditions sur path : Des conditions spécifiques identifient les chemins aboutissant à un achat, et des calculs distincts sont appliqués selon la longueur du chemin (quatre ou cinq événements).
  • Calcul de la durée : La différence entre l’horodatage de l’événement initial et celui de l’achat final est convertie en minutes.

5.4.4 Requête principale

Enfin, la requête principale calcule la durée moyenne des achats pour chaque type de parcours identifié et les classe du plus long au plus court. Cette étape finale fournit les insights directs sur la durée moyenne passée sur les parcours avant un achat.

WITH lag_data AS ( SELECT user_id, event_name, event_timestamp, event_id, LEAD(event_name, 1) OVER(PARTITION BY user_id ORDER BY event_timestamp) AS following_event, LEAD(event_name, 2) OVER(PARTITION BY user_id ORDER BY event_timestamp) AS two_following_event, LEAD(event_name, 3) OVER(PARTITION BY user_id ORDER BY event_timestamp) AS three_following_event, LEAD(event_name, 4) OVER(PARTITION BY user_id ORDER BY event_timestamp) AS four_following_event, LEAD(event_name, 5) OVER(PARTITION BY user_id ORDER BY event_timestamp) AS five_following_event, LEAD(event_timestamp, 1) OVER(PARTITION BY user_id ORDER BY event_timestamp) AS following_event_timestamp, LEAD(event_timestamp, 2) OVER(PARTITION BY user_id ORDER BY event_timestamp) AS two_following_event_timestamp, LEAD(event_timestamp, 3) OVER(PARTITION BY user_id ORDER BY event_timestamp) AS three_following_event_timestamp, LEAD(event_timestamp, 4) OVER(PARTITION BY user_id ORDER BY event_timestamp) AS four_following_event_timestamp, LEAD(event_timestamp, 5) OVER(PARTITION BY user_id ORDER BY event_timestamp) AS five_following_event_timestamp FROM data ) , purchase_paths AS ( SELECT user_id, event_timestamp, event_id, event_name || '-' || COALESCE(following_event, '') || '-' || COALESCE(two_following_event, '') || '-' || COALESCE(three_following_event, '') || '-' || COALESCE(four_following_event, '') || '-' || COALESCE(five_following_event, '') AS path, following_event_timestamp, two_following_event_timestamp, three_following_event_timestamp, four_following_event_timestamp, five_following_event_timestamp FROM lag_data ),  purchase_duration AS ( SELECT user_id, event_timestamp, event_id, path, CASE WHEN path LIKE '%home_page-home_page_click-view_item_details-add_to_cart-complete_purchase%' OR path LIKE '%home_page-search_for_items-view_item_details-add_to_cart-complete_purchase%' OR path LIKE '%home_page-promotion_click-view_item_details-add_to_cart-complete_purchase%' THEN (EPOCH(CAST(four_following_event_timestamp AS TIMESTAMP)) - EPOCH(CAST(event_timestamp AS TIMESTAMP)))/60 WHEN path LIKE '%view_item_details-add_to_cart-continue_shopping-view_item_details-add_to_cart-complete_purchase%' THEN (EPOCH(CAST(five_following_event_timestamp AS TIMESTAMP)) - EPOCH(CAST(event_timestamp AS TIMESTAMP)))/60 END AS purchase_duration FROM purchase_paths WHERE path LIKE '%complete_purchase%' ) -- Main Query SELECT  path, AVG(purchase_duration) AS avg_purchase_duration FROM purchase_duration GROUP BY path  HAVING avg_purchase_duration NOT NULL  ORDER BY avg_purchase_duration DESC

Voici le résultat de la requête:

Chapitre précédent Chapitre suivant