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:
┌──────────────────────────────────────────────────────────────────────────────────────────────┬───────────────────────┐ │ path │ avg_purchase_duration │ │ varchar │ double │ ├──────────────────────────────────────────────────────────────────────────────────────────────┼───────────────────────┤ │ view_item_details-add_to_cart-continue_shopping-view_item_details-add_to_cart-complete_pur… │ 14323.49626209323 │ │ home_page-home_page_click-view_item_details-add_to_cart-complete_purchase- │ 13529.291932841932 │ │ home_page-search_for_items-view_item_details-add_to_cart-complete_purchase- │ 13524.336293592864 │ │ home_page-promotion_click-view_item_details-add_to_cart-complete_purchase- │ 13381.425765095131 │ └──────────────────────────────────────────────────────────────────────────────────────────────┴───────────────────────┘