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 │
└─────────────────────────┘