2.4 Démarches pas à pas, avec code SQL à l’appui
Nous cherchons à déterminer le nombre de clients ayant emprunté un parcours spécifique :
home_page → home_page_click → view_item_details → add_to_cart → complete_purchase
La particularité est que ces événements se succèdent directement. Nous ciblons une séquence de 5 événements et, pour s’assurer que l’événement A est suivi immédiatement par l’événement B, nous utilisons la fonction LEAD.
Petit rappel théorique : la fonction LEAD permet d’accéder à une ligne située à un certain décalage après la ligne actuelle. Nous l’employons avec SELECT pour comparer les valeurs d’une ligne à celles d’une autre ligne subséquente.
Plus concrètement, pour chaque événement, nous récupérons les 4 suivants :
- Le premier suivant immédiatement, avec la fonction LEAD(event_name).
- Le deuxième à deux décalages, avec la fonction LEAD(event_name, 2).
- Le troisième à trois décalages, avec la fonction LEAD(event_name, 3).
- Et le quatrième à quatre décalages, avec la fonction LEAD(event_name, 4).
Voyons comment mettre tout cela dans une CTE.
WITH lag_data as ( SELECT user_id, event_name, event_timestamp, event_id, LEAD(event_name) 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 FROM query )
Finalement, nous comptons le nombre de clients ayant suivi un parcours spécifique, en vérifiant si chaque événement correspond à celui attendu :
SELECT COUNT(DISTINCT user_id) FROM lag_data WHERE event_name = 'home_page' AND following_event = 'home_page_click' AND two_following_event = 'view_item_details' AND three_following_event = 'add_to_cart' AND four_following_event = 'complete_purchase'
Voici le résultat de la requête:
┌─────────────────────────┐ │ count(DISTINCT user_id) │ │ int64 │ ├─────────────────────────┤ │ 407 │ └─────────────────────────┘