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

Pour déterminer le parcours le plus courant menant à un achat, nous suivons les étapes suivantes :

3.4.1 Identification des séquences d’événements aboutissant à un achat

Nous utilisons des requêtes SQL pour repérer les séquences d’événements qui se terminent par un achat.

Pour ce faire, nous réutilisons la CTE de la partie précédente qui permet d’obtenir une séquence d’évènements grâce à la fonction LEAD. Ensuite, nous identifions les différentes voies empruntées par les clients avant de finaliser un achat:

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
)

3.4.2 Comptage des occurrences de chaque séquence

Une fois les séquences identifiées, nous les comptons pour déterminer leur fréquence d’apparition dans notre ensemble de données.

SELECT 
path, 
COUNT(*) AS occurrence 
FROM purchase_paths 
GROUP BY path

3.4.3 Identification du parcours le plus courant

Enfin, nous extrayons le parcours le plus fréquent en triant les résultats précédents par ordre décroissant et en limitant le résultat à une seule entrée.

SELECT 
path, 
COUNT(*) AS occurrence 
FROM purchase_paths 
GROUP BY path 
ORDER BY occurrence DESC 
LIMIT 1;

Voici le résultat de la requête:

┌─────────────────────────────────────────────────────────────────────────────────────────────────┬────────────┐
│                                              path                                               │ occurrence │
│                                             varchar                                             │   int64    │
├─────────────────────────────────────────────────────────────────────────────────────────────────┼────────────┤
│ home_page-search_for_items-view_item_details-add_to_cart-complete_purchase-                     │        411 │
│ home_page-home_page_click-view_item_details-add_to_cart-complete_purchase-                      │        407 │
│ home_page-promotion_click-view_item_details-add_to_cart-complete_purchase-                      │        403 │
│ view_item_details-add_to_cart-continue_shopping-view_item_details-add_to_cart-complete_purchase │        379 │
└─────────────────────────────────────────────────────────────────────────────────────────────────┴────────────┘