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

De nombreux tutoriels proposent simplement de calculer le taux de conversion de manière simpliste : compter le nombre d’événements à chaque étape puis les regrouper.

Cependant, il est possible qu’un client arrive directement sur la page d’un article, générant ainsi un événement « article_view » sans passer par la page d’accueil.

La méthode appropriée consiste à suivre le parcours complet d’un client.

Nous sélectionnons les clients ayant débuté leur parcours par la visite de la page d’accueil. Nous les suivons ensuite pour évaluer s’ils passent aux étapes suivantes.

Notre code SQL est divisé en 5 sous-requêtes, dont 4 CTEs (Common Table Expression) et 1 requête finale.

Explorons le rôle de chaque requête :

1.4.1 visitors (CTE)

Cette CTE filtre les clients ayant débuté leur parcours par la visite de la page d’accueil. Pour ce faire, nous utilisons un filtre WHERE sur event_name = ‘home_page’ pour initialement sélectionner tous les user_id associés à « home_page » dans le jeu de données.

Ensuite, pour chaque user_id, nous trouvons l’horodatage de sa première visite à la page d’accueil. Ceci est réalisé simplement avec MIN(event_timestamp) et GROUP BY user_id.

Enfin, nous filtrons sur la période étudiée (semaine du 03 au 09 juillet) avec HAVING : HAVING min_event_timestamp BETWEEN ‘2023-07-03’ AND ‘2023-07-10’.

WITH visitors AS (
SELECT 
    user_id, 
    MIN("event_timestamp") AS min_event_timestamp 
FROM data 
WHERE event_name = 'home_page' 
GROUP BY user_id 
HAVING min_event_timestamp BETWEEN '2023-07-03' AND '2023-07-10'
)

1.4.2 article_view (CTE)

Cette CTE sélectionne les clients poursuivant leur parcours en consultant un article.

Pour ce faire, nous effectuons un INNER JOIN de la CTE visitors et la table d’origine avec un filtre sur les événements ‘article_view’.

Nous veillons également à ce que les vues d’articles surviennent après la visite de la page d’accueil en appliquant un filtre sur event_timestamp : visitors.min_event_timestamp <= data.event_timestamp.

-- Parmi les clients qui ont visité la homepage, regarder combien ont regardé la page d'article par la suite
WITH article_view AS (
SELECT 
    visitors.user_id, 
    data.event_timestamp 
FROM visitors 
INNER JOIN data 
ON visitors.user_id = data.user_id 
WHERE data.event_name = 'article_view' 
AND visitors.min_event_timestamp <= data.event_timestamp
)

1.4.3 add_to_cart (CTE)

Cette CTE suit le parcours client en sélectionnant ceux qui, après avoir consulté l’article, l’ont ajouté à leur panier.

La méthode est similaire à la CTE article_view : nous effectuons une jointure entre la CTE article_view et la table d’origine (filtrée sur les événements ‘add_to_cart’).

Comme pour la CTE précédente, nous souhaitons que les événements add_to_cart surviennent après la consultation de l’article.

-- Parmi les clients qui ont regardé la page d'article, sélectionner ceux qui ont ajouté le produit au panier par la suite
WITH add_to_cart AS
(SELECT
article_view.user_id,
data.event_timestamp
FROM article_view
INNER JOIN data
ON article_view.user_id = data.user_id
WHERE data.event_name = 'add_to_cart'
AND article_view.event_timestamp <= data.event_timestamp
)

1.4.4 cart_paid (CTE)

Cette CTE rejoint add_to_cart et sélectionne les clients ayant effectué un paiement.

WITH cart_paid AS
(SELECT
add_to_cart.user_id,
data.event_timestamp
FROM add_to_cart
INNER JOIN data
ON add_to_cart.user_id = data.user_id
WHERE data.event_name = 'cart_paid'
AND add_to_cart.event_timestamp <= data.event_timestamp
)

1.4.5 requête finale

Dans la requête finale, nous comptons le nombre d’événements à chaque étape du parcours : visite de la page d’accueil, consultation de l’article, ajout au panier et paiement. Pour cela, nous utilisons simplement COUNT.

SELECT 'home_page' AS step, COUNT(*) AS count FROM visitors 
UNION
SELECT 'article_view' AS step, COUNT(*) AS count FROM article_view 
UNION
SELECT 'add_to_cart' AS step, COUNT(*) AS count FROM add_to_cart
UNION
SELECT 'cart_paid' AS step, COUNT(*) AS count FROM cart_paid
ORDER BY count DESC

Voici le résultat de la requête:

┌──────────────┬───────┐
│     step     │ count │
│   varchar    │ int64 │
├──────────────┼───────┤
│ home_page    │  1293 │
│ article_view │   588 │
│ add_to_cart  │    64 │
│ cart_paid    │     1 │
└──────────────┴───────┘