Exercice SQL : analyser un tunnel de conversion e-commerce

L'analyse de tunnel de conversion (funnel analysis) est l'un des cas d'usage les plus fréquents pour un data analyst e-commerce. En entretien, c'est un exercice qui combine agrégation, CASE WHEN, CTE et parfois des fonctions fenêtre. Thomas Leroy vous guide à travers un exercice complet avec plusieurs niveaux de difficulté.

L'énoncé

Vous êtes data analyst pour un site e-commerce. Le product manager veut comprendre les taux de conversion à chaque étape du parcours d'achat. L'analyse de tunnel de conversion est cruciale pour identifier les points de friction dans l'expérience utilisateur et optimiser les performances business.

Selon une étude de Baymard Institute (2023), le taux d'abandon moyen du panier d'achat est de 70,19% dans l'e-commerce. Une bonne analyse de funnel permet d'identifier précisément où et pourquoi les utilisateurs abandonnent leur parcours.

Le schéma

Table events :

Les types d'événements sont : page_view, product_view, add_to_cart, begin_checkout, purchase.

Les données d'exemple

Les questions

Question 1 (facile) : Comptez le nombre de sessions à chaque étape du funnel.

Question 2 (intermédiaire) : Calculez le taux de conversion entre chaque étape et le taux de conversion global (page_view → purchase).

Question 3 (avancé) : Calculez le funnel par semaine avec variation semaine sur semaine.

Question 4 (expert) : Analysez les parcours atypiques et les abandons par segment.

Question 1 : Nombre de sessions par étape

Approche

Il faut compter les sessions distinctes qui ont atteint chaque étape. Un point important : une session qui a fait un purchase a aussi fait un page_view, un product_view, etc. On veut le nombre de sessions qui ont au moins atteint cette étape.

Solution

SELECT
    event_type,
    COUNT(DISTINCT session_id) AS nb_sessions
FROM events
GROUP BY event_type
ORDER BY
    CASE event_type
        WHEN 'page_view'      THEN 1
        WHEN 'product_view'   THEN 2
        WHEN 'add_to_cart'    THEN 3
        WHEN 'begin_checkout' THEN 4
        WHEN 'purchase'       THEN 5
    END;

<div style="overflow-x:auto;margin:20px 0;"><table>
<thead style="background-color:#0F172A;color:white;">
<tr><th style="padding:12px 16px;text-align:left">event_type</th><th style="padding:12px 16px;text-align:left">nb_sessions</th></tr>
</thead><tbody>
<tr style="background:#f8fafc"><td style="padding:12px 16px">page_view</td><td style="padding:12px 16px">5</td></tr>
<tr><td style="padding:12px 16px">product_view</td><td style="padding:12px 16px">4</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">add_to_cart</td><td style="padding:12px 16px">3</td></tr>
<tr><td style="padding:12px 16px">begin_checkout</td><td style="padding:12px 16px">2</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">purchase</td><td style="padding:12px 16px">2</td></tr>
</tbody></table></div>

Le `ORDER BY` avec [CASE WHEN](/case-when-sql-syntaxe-exemples) garantit l'ordre logique du funnel, pas l'ordre alphabétique.

## Question 2 : Taux de conversion inter-étapes

### Approche

On veut :
- Le taux de conversion **entre chaque étape** (product_view → add_to_cart = ?)
- Le taux de conversion **global** (page_view → purchase = ?)

On utilise une [CTE](/cte-sql-common-table-expressions) pour le comptage, puis LAG pour calculer le taux inter-étapes.

### Solution

```sql
WITH funnel AS (
    SELECT
        event_type,
        COUNT(DISTINCT session_id) AS nb_sessions,
        CASE event_type
            WHEN 'page_view'      THEN 1
            WHEN 'product_view'   THEN 2
            WHEN 'add_to_cart'    THEN 3
            WHEN 'begin_checkout' THEN 4
            WHEN 'purchase'       THEN 5
        END AS etape_num
    FROM events
    GROUP BY event_type
),
avec_precedent AS (
    SELECT
        event_type,
        nb_sessions,
        etape_num,
        LAG(nb_sessions) OVER (ORDER BY etape_num) AS sessions_etape_precedente,
        FIRST_VALUE(nb_sessions) OVER (ORDER BY etape_num) AS sessions_depart
    FROM funnel
)
SELECT
    event_type,
    nb_sessions,
    ROUND(
        100.0 * nb_sessions / NULLIF(sessions_etape_precedente, 0), 1
    ) AS taux_inter_etapes,
    ROUND(
        100.0 * nb_sessions / NULLIF(sessions_depart, 0), 1
    ) AS taux_global
FROM avec_precedent
ORDER BY etape_num;
<div style="overflow-x:auto;margin:20px 0;"><table>
<thead style="background-color:#0F172A;color:white;">
<tr><th style="padding:12px 16px;text-align:left">event_type</th><th style="padding:12px 16px;text-align:left">nb_sessions</th><th style="padding:12px 16px;text-align:left">taux_inter_etapes</th><th style="padding:12px 16px;text-align:left">taux_global</th></tr>
</thead><tbody>
<tr style="background:#f8fafc"><td style="padding:12px 16px">page_view</td><td style="padding:12px 16px">5</td><td style="padding:12px 16px">NULL</td><td style="padding:12px 16px">100.0</td></tr>
<tr><td style="padding:12px 16px">product_view</td><td style="padding:12px 16px">4</td><td style="padding:12px 16px">80.0</td><td style="padding:12px 16px">80.0</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">add_to_cart</td><td style="padding:12px 16px">3</td><td style="padding:12px 16px">75.0</td><td style="padding:12px 16px">60.0</td></tr>
<tr><td style="padding:12px 16px">begin_checkout</td><td style="padding:12px 16px">2</td><td style="padding:12px 16px">66.7</td><td style="padding:12px 16px">40.0</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">purchase</td><td style="padding:12px 16px">2</td><td style="padding:12px 16px">100.0</td><td style="padding:12px 16px">40.0</td></tr>
</tbody></table></div>

### Lecture du résultat

- 80% des visiteurs regardent un produit
- 75% de ceux qui regardent un produit l'ajoutent au panier
- 66.7% de ceux qui ajoutent au panier lancent le checkout
- 100% de ceux qui lancent le checkout achètent
- Au global : 40% des visiteurs achètent (taux de conversion)

Le LAG est une [fonction fenêtre](/fonctions-fenetre-sql-guide-complet) qui récupère la valeur de la ligne précédente. `FIRST_VALUE` récupère la première valeur de la fenêtre (ici, les sessions de l'étape page_view).

## Question 3 : Funnel par semaine avec variation

### Approche

On ajoute une dimension temporelle (semaine) et on calcule la variation par rapport à la semaine précédente. Cette analyse permet d'identifier les tendances et de mesurer l'impact des optimisations.

### Solution

```sql
WITH funnel_hebdo AS (
    SELECT
        DATE_TRUNC('week', event_date) AS semaine,
        COUNT(DISTINCT CASE WHEN event_type = 'page_view' THEN session_id END) AS page_views,
        COUNT(DISTINCT CASE WHEN event_type = 'product_view' THEN session_id END) AS product_views,
        COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart' THEN session_id END) AS add_to_carts,
        COUNT(DISTINCT CASE WHEN event_type = 'begin_checkout' THEN session_id END) AS checkouts,
        COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN session_id END) AS purchases
    FROM events
    GROUP BY DATE_TRUNC('week', event_date)
),
avec_taux AS (
    SELECT
        semaine,
        page_views,
        product_views,
        add_to_carts,
        checkouts,
        purchases,
        ROUND(100.0 * purchases / NULLIF(page_views, 0), 1) AS taux_conversion_global,
        ROUND(100.0 * add_to_carts / NULLIF(product_views, 0), 1) AS taux_ajout_panier
    FROM funnel_hebdo
),
avec_variation AS (
    SELECT
        *,
        LAG(taux_conversion_global) OVER (ORDER BY semaine) AS taux_semaine_precedente,
        taux_conversion_global - LAG(taux_conversion_global) OVER (ORDER BY semaine) AS variation_pts
    FROM avec_taux
)
SELECT * FROM avec_variation ORDER BY semaine;

Variante : pivot par étape

Si le product manager préfère un format avec une ligne par semaine et par étape :

WITH evenements_etapes AS (
    SELECT
        DATE_TRUNC('week', event_date) AS semaine,
        event_type,
        COUNT(DISTINCT session_id) AS nb_sessions
    FROM events
    GROUP BY DATE_TRUNC('week', event_date), event_type
),
avec_total_depart AS (
    SELECT
        e.*,
        FIRST_VALUE(nb_sessions) OVER (
            PARTITION BY semaine
            ORDER BY CASE event_type
                WHEN 'page_view' THEN 1
                WHEN 'product_view' THEN 2
                WHEN 'add_to_cart' THEN 3
                WHEN 'begin_checkout' THEN 4
                WHEN 'purchase' THEN 5
            END
        ) AS sessions_depart
    FROM evenements_etapes e
)
SELECT
    semaine,
    event_type,
    nb_sessions,
    ROUND(100.0 * nb_sessions / NULLIF(sessions_depart, 0), 1) AS taux_global
FROM avec_total_depart
ORDER BY semaine, CASE event_type
    WHEN 'page_view' THEN 1
    WHEN 'product_view' THEN 2
    WHEN 'add_to_cart' THEN 3
    WHEN 'begin_checkout' THEN 4
    WHEN 'purchase' THEN 5
END;

Question 4 : Analyses avancées par profil utilisateur

Analyse par type d'appareil

Les taux de conversion varient significativement selon l'appareil. Selon Statista (2023), le taux de conversion mobile est en moyenne 40% plus faible que desktop.

WITH funnel_device AS (
    SELECT
        device_type,
        event_type,
        COUNT(DISTINCT session_id) AS nb_sessions,
        CASE event_type
            WHEN 'page_view' THEN 1
            WHEN 'product_view' THEN 2
            WHEN 'add_to_cart' THEN 3
            WHEN 'begin_checkout' THEN 4
            WHEN 'purchase' THEN 5
        END AS etape_num
    FROM events
    GROUP BY device_type, event_type
),
avec_conversion AS (
    SELECT
        device_type,
        event_type,
        nb_sessions,
        ROUND(
            100.0 * nb_sessions / FIRST_VALUE(nb_sessions) OVER (
                PARTITION BY device_type 
                ORDER BY etape_num
            ), 1
        ) AS taux_conversion_cumule
    FROM funnel_device
)
SELECT * FROM avec_conversion 
ORDER BY device_type, etape_num;

Segmentation par statut utilisateur

Distinguer les nouveaux visiteurs des utilisateurs récurrents :

WITH sessions_enrichies AS (
    SELECT
        e.session_id,
        e.event_type,
        e.event_date,
        CASE 
            WHEN e.user_id IS NULL THEN 'anonyme'
            WHEN COUNT(*) OVER (PARTITION BY e.user_id) = 1 THEN 'nouveau'
            ELSE 'recurrent'
        END AS type_utilisateur
    FROM events e
),
funnel_par_type AS (
    SELECT
        type_utilisateur,
        event_type,
        COUNT(DISTINCT session_id) AS nb_sessions,
        CASE event_type
            WHEN 'page_view' THEN 1
            WHEN 'product_view' THEN 2
            WHEN 'add_to_cart' THEN 3
            WHEN 'begin_checkout' THEN 4
            WHEN 'purchase' THEN 5
        END AS etape_num
    FROM sessions_enrichies
    GROUP BY type_utilisateur, event_type
)
SELECT
    type_utilisateur,
    event_type,
    nb_sessions,
    ROUND(
        100.0 * nb_sessions / FIRST_VALUE(nb_sessions) OVER (
            PARTITION BY type_utilisateur 
            ORDER BY etape_num
        ), 1
    ) AS taux_conversion
FROM funnel_par_type
ORDER BY type_utilisateur, etape_num;

Variante bonus : où les utilisateurs abandonnent

Question bonus que le recruteur pourrait ajouter : « À quelle étape perd-on le plus de sessions ? »

WITH funnel AS (
    SELECT
        event_type,
        COUNT(DISTINCT session_id) AS nb_sessions,
        CASE event_type
            WHEN 'page_view' THEN 1
            WHEN 'product_view' THEN 2
            WHEN 'add_to_cart' THEN 3
            WHEN 'begin_checkout' THEN 4
            WHEN 'purchase' THEN 5
        END AS etape_num
    FROM events
    GROUP BY event_type
),
abandons AS (
    SELECT
        event_type,
        nb_sessions,
        nb_sessions - LEAD(nb_sessions) OVER (ORDER BY etape_num) AS sessions_perdues,
        ROUND(
            100.0 * (nb_sessions - LEAD(nb_sessions) OVER (ORDER BY etape_num))
            / NULLIF(nb_sessions, 0), 1
        ) AS taux_abandon
    FROM funnel
)
SELECT * FROM abandons ORDER BY sessions_perdues DESC NULLS LAST;

Le plus gros taux d'abandon est entre add_to_cart et begin_checkout (33.3%). C'est le type d'insight actionnable que le product manager attend.

Analyses avancées pour impressionner le recruteur

Analyse de cohortes par source de trafic

Si vous avez une colonne utm_source ou referrer, proposez une analyse segmentée :

WITH funnel_par_source AS (
    SELECT
        COALESCE(utm_source, 'direct') AS source_trafic,
        event_type,
        COUNT(DISTINCT session_id) AS nb_sessions,
        CASE event_type
            WHEN 'page_view' THEN 1
            WHEN 'product_view' THEN 2
            WHEN 'add_to_cart' THEN 3
            WHEN 'begin_checkout' THEN 4
            WHEN 'purchase' THEN 5
        END AS etape_num
    FROM events e
    GROUP BY COALESCE(utm_source, 'direct'), event_type
)
SELECT
    source_trafic,
    event_type,
    nb_sessions,
    ROUND(
        100.0 * nb_sessions / FIRST_VALUE(nb_sessions) OVER (
            PARTITION BY source_trafic 
            ORDER BY etape_num
        ), 1
    ) AS taux_conversion_cumule
FROM funnel_par_source
ORDER BY source_trafic, etape_num;

Temps moyen entre chaque étape

Une analyse temporelle du parcours utilisateur révèle souvent des insights précieux. Amazon a observé qu'une augmentation de 100ms du temps de chargement réduit les ventes de 1%.

WITH etapes_avec_temps AS (
    SELECT
        session_id,
        event_type,
        event_date,
        CASE event_type
            WHEN 'page_view' THEN 1
            WHEN 'product_view' THEN 2
            WHEN 'add_to_cart' THEN 3
            WHEN 'begin_checkout' THEN 4
            WHEN 'purchase' THEN 5
        END AS etape_num
    FROM events
),
temps_inter_etapes AS (
    SELECT
        session_id,
        event_type,
        etape_num,
        event_date - LAG(event_date) OVER (
            PARTITION BY session_id 
            ORDER BY etape_num
        ) AS duree_depuis_etape_precedente
    FROM etapes_avec_temps
)
SELECT
    event_type,
    AVG(EXTRACT(EPOCH FROM duree_depuis_etape_precedente)) AS duree_moyenne_secondes,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM duree_depuis_etape_precedente)
    ) AS duree_mediane_secondes
FROM temps_inter_etapes
WHERE duree_depuis_etape_precedente IS NOT NULL
GROUP BY event_type, etape_num
ORDER BY etape_num;

Segmentation par panier moyen

Analyse du comportement selon la valeur du panier (nécessite une table orders) :

WITH sessions_avec_valeur AS (
    SELECT 
        e.session_id,
        e.event_type,
        CASE 
            WHEN o.order_value IS NULL THEN 'sans_achat'
            WHEN o.order_value < 50 THEN 'petit_panier'
            WHEN o.order_value < 150 THEN 'panier_moyen'
            ELSE 'gros_panier'
        END AS segment_panier
    FROM events e
    LEFT JOIN orders o ON e.session_id = o.session_id
),
funnel_par_segment AS (
    SELECT
        segment_panier,
        event_type,
        COUNT(DISTINCT session_id) AS nb_sessions
    FROM sessions_avec_valeur
    GROUP BY segment_panier, event_type
)
SELECT * FROM funnel_par_segment 
WHERE segment_panier != 'sans_achat'
ORDER BY segment_panier, 
    CASE event_type
        WHEN 'page_view' THEN 1
        WHEN 'product_view' THEN 2
        WHEN 'add_to_cart' THEN 3
        WHEN 'begin_checkout' THEN 4
        WHEN 'purchase' THEN 5
    END;

Les pièges à connaître

Piège 1 : Sessions vs utilisateurs

Les funnels peuvent être comptés par sessions ou par utilisateurs. Le résultat peut différer significativement. Prenons l'exemple de Sarah, une utilisatrice qui visite le site sur mobile le matin (session s1) pour consulter un produit, puis revient le soir sur desktop (session s2) pour finaliser l'achat. Si on compte par sessions, on aura 2 page_views et 1 purchase. Si on compte par utilisateurs, on aura 1 page_view et 1 purchase.

Piège 2 : Événements hors séquence

Un utilisateur peut ajouter au panier sans avoir de product_view enregistré (navigation directe via URL partagée, deep link depuis une app mobile, ou problème de tracking). Exemple : Marc reçoit un lien direct vers /produit/123 et achète immédiatement. La question est : faut-il l'inclure dans l'étape product_view ?

-- Approche conservative : ne compter que les événements trackés
COUNT(DISTINCT CASE WHEN event_type = 'product_view' THEN session_id END)

-- Approche inclusive : inférer les étapes manquantes
COUNT(DISTINCT CASE 
    WHEN event_type IN ('product_view', 'add_to_cart', 'begin_checkout', 'purchase') 
    THEN session_id 
END)

Piège 3 : Visiteurs anonymes

Les visiteurs anonymes (user_id = NULL) ont une session_id mais pas de profil utilisateur. Ils représentent souvent 60-80% du trafic selon le secteur. Leur comportement d'achat diffère généralement des utilisateurs connectés.

-- Funnel incluant tous les utilisateurs
SELECT 
    event_type,
    COUNT(DISTINCT session_id) as total_sessions,
    COUNT(DISTINCT CASE WHEN user_id IS NOT NULL THEN session_id END) as sessions_connectees,
    COUNT(DISTINCT CASE WHEN user_id IS NULL THEN session_id END) as sessions_anonymes
FROM events
GROUP BY event_type;

Piège 4 : Problèmes de tracking

Dans la réalité, le tracking n'est pas parfait. Vous pouvez avoir :

• Des achats sans begin_checkout (checkout en une étape)
• Des add_to_cart sans product_view (ajout depuis une liste)
• Des doublons d'événements (double-clic, rafraîchissement)

Piège 5 : Fenêtre temporelle

Quelle est la durée maximale d'une session ? Un utilisateur qui revient 3 jours plus tard a-t-il la même session_id ? Ces questions impactent l'interprétation du funnel.

-- Analyser la durée des sessions
WITH durees_session AS (
    SELECT 
        session_id,
        MIN(event_date) as debut_session,
        MAX(event_date) as fin_session,
        MAX(event_date) - MIN(event_date) as duree_session,
        COUNT(*) as nb_evenements
    FROM events 
    GROUP BY session_id
)
SELECT 
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM duree_session)) / 3600 as duree_mediane_heures,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM duree_session)) / 3600 as duree_p90_heures,
    AVG(nb_evenements) as nb_moyen_evenements
FROM durees_session;

Erreurs fréquentes à éviter

Erreur 1 : Oublier DISTINCT

-- ❌ Incorrect : compte les événements, pas les sessions
SELECT event_type, COUNT(session_id) FROM events GROUP BY event_type;

-- ✅ Correct : compte les sessions distinctes
SELECT event_type, COUNT(DISTINCT session_id) FROM events GROUP BY event_type;

Erreur 2 : Division par zéro

-- ❌ Risque d'erreur si sessions_precedentes = 0
SELECT 100.0 * nb_sessions / sessions_precedentes FROM funnel;

-- ✅ Gestion du cas zéro
SELECT ROUND(100.0 * nb_sessions / NULLIF(sessions_precedentes, 0), 1) FROM funnel;

Erreur 3 : Ordre alphabétique au lieu de logique

-- ❌ Ordre alphabétique inutile
SELECT * FROM funnel ORDER BY event_type;

-- ✅ Ordre logique du parcours
SELECT * FROM funnel ORDER BY CASE event_type 
    WHEN 'page_view' THEN 1 
    WHEN 'product_view' THEN 2 
    -- etc.
END;

Erreur 4 : Ne pas questionner les données

En entretien, montrez votre sens critique :

• "Ces données couvrent quelle période ?"
• "Y a-t-il des événements de test à exclure ?"
• "Faut-il filtrer certains types de sessions (bots, admin) ?"
• "Comment gérer les remboursements dans le funnel ?"

Exemples concrets par secteur

E-commerce Fashion (ex: Zalando)

-- Funnel par catégorie de produits
WITH funnel_mode AS (
    SELECT
        p.category,
        e.event_type,
        COUNT(DISTINCT e.session_id) as nb_sessions
    FROM events e
    JOIN products p ON REGEXP_EXTRACT(e.page_url, '/produit/(\d+)', 1) = p.product_id::text
    WHERE e.event_type IN ('page_view', 'product_view', 'add_to_cart', 'purchase')
    GROUP BY p.category, e.event_type
)
SELECT 
    category,
    ROUND(100.0 * SUM(CASE WHEN event_type = 'purchase' THEN nb_sessions ELSE 0 END) 
          / SUM(CASE WHEN event_type = 'page_view' THEN nb_sessions ELSE 0 END), 2) as taux_conversion
FROM funnel_mode 
GROUP BY category
ORDER BY taux_conversion DESC;

Marketplace B2B (ex: Alibaba)

-- Funnel par taille d'entreprise
WITH sessions_enrichies AS (
    SELECT 
        e.*,
        c.company_size,
        c.industry
    FROM events e
    LEFT JOIN companies c ON e.user_id = c.user_id
)
SELECT
    company_size,
    industry,
    COUNT(DISTINCT CASE WHEN event_type = 'page_view' THEN session_id END) as visitors,
    COUNT(DISTINCT CASE WHEN event_type = 'contact_supplier' THEN session_id END) as contacts,
    COUNT(DISTINCT CASE WHEN event_type = 'quote_request' THEN session_id END) as quotes,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN event_type = 'quote_request' THEN session_id END)
          / NULLIF(COUNT(DISTINCT CASE WHEN event_type = 'page_view' THEN session_id END), 0), 1) as lead_conversion_rate
FROM sessions_enrichies
GROUP BY company_size, industry;

SaaS Freemium (ex: Slack)

-- Funnel d'activation utilisateur
WITH activation_funnel AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_type = 'signup' THEN 1 ELSE 0 END) as signed_up,
        MAX(CASE WHEN event_type = 'email_confirmed' THEN 1 ELSE 0 END) as email_confirmed,
        MAX(CASE WHEN event_type = 'first_message_sent' THEN 1 ELSE 0 END) as sent_message,
        MAX(CASE WHEN event_type = 'invited_teammate' THEN 1 ELSE 0 END) as invited_teammate,
        MAX(CASE WHEN event_type = 'trial_started' THEN 1 ELSE 0 END) as trial_started
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    COUNT(*) as total_signups,
    SUM(email_confirmed) as confirmed_emails,
    SUM(sent_message) as active_users,
    SUM(invited_teammate) as team_builders,
    SUM(trial_started) as trial_conversions,
    ROUND(100.0 * SUM(trial_started) / COUNT(*), 1) as signup_to_trial_rate
FROM activation_funnel;

Tableau récapitulatif des métriques clés

Source : Données agrégées de ConversionXL, Baymard Institute, et rapports sectoriels 2023-2024.

Comment se préparer pour un entretien

1. Maîtrisez les patterns SQL

• COUNT(DISTINCT CASE WHEN ... THEN id END)
• Fonctions fenêtre : LAG, LEAD, FIRST_VALUE
• CTE pour structurer l'analyse
• Gestion des divisions par zéro avec NULLIF

2. Préparez des questions business

• "Quelle est la définition d'une session convertie ?"
• "Faut-il inclure les sessions de test/admin ?"
• "Sur quelle période analyser ?"
• "Y a-t-il des saisonnalités à prendre en compte ?"

3. Proposez des analyses complémentaires

Après avoir résolu l'exercice de base, suggérez :

• Analyse par cohorte (nouveau vs récurrent)
• Segmentation par device/source
• Analyse temporelle (jour de semaine, heure)
• Identification des parcours atypiques

Ressources pour aller plus loin

Livres recommandés

• "Lean Analytics" par Alistair Croll - référence pour les métriques produit
• "Web Analytics 2.0" par Avinash Kaushik - fondamentaux de l'analyse web

Outils d'analyse

• Google Analytics 4 : funnel analysis natif
• Mixpanel : événementiel et cohortes
• Amplitude : analyses comportementales avancées
• Tableau/Looker : dashboards et visualisations

Datasets publics pour s'entraîner

• Google Analytics Sample Dataset (BigQuery)
• Kaggle E-commerce datasets
• TheLook eCommerce (Looker public dataset)

Pour approfondir vos compétences SQL en vue d'un entretien, consultez notre guide sur comment préparer un entretien SQL pour data analyst et découvrez les 20 questions SQL les plus posées en entretien.

Questions fréquentes

Comment gérer les sessions qui sautent des étapes dans le funnel ?

C'est fréquent dans la réalité (liens directs, problèmes de tracking). Deux approches : soit vous comptez uniquement les événements trackés (approche conservative), soit vous inférez les étapes manquantes avec des règles métier. Par exemple, un add_to_cart implique nécessairement un product_view même si pas tracké.

Faut-il analyser le funnel par sessions ou par utilisateurs ?

Cela dépend de l'objectif business. L'analyse par sessions montre l'efficacité de chaque visite, tandis que l'analyse par utilisateurs révèle le comportement global sur plusieurs sessions. En B2C, on privilégie souvent les sessions. En B2B avec cycles longs, les utilisateurs sont plus pertinents.

Comment traiter les événements dupliqués dans le funnel ?

Utilisez COUNT(DISTINCT session_id) pour éviter les doublons par session. Pour les événements temporellement proches (< 5 secondes), vous pouvez déduplicquer avec une fenêtre temporelle : ROW_NUMBER() OVER (PARTITION BY session_id, event_type ORDER BY event_date) = 1.

Quelle période analyser pour un funnel e-commerce ?

Évitez les périodes avec des événements exceptionnels (Black Friday, soldes). 30 jours donnent une vision stable, 7 jours permettent d'identifier des patterns hebdomadaires. Pour les analyses temporelles, excluez toujours les périodes incomplètes (semaine en cours).

Comment identifier les goulots d'étranglement dans un funnel ?

Calculez le taux d'abandon entre chaque étape avec LEAD(). L'étape avec le plus grand nombre absolu de sessions perdues ET le plus fort taux d'abandon relatif est prioritaire à optimiser. Complétez avec une analyse temporelle pour voir si c'est récurrent.

Que faire quand les données de funnel semblent incohérentes ?

Validez d'abord la logique : peut-on avoir plus d'achats que de pages vues ? Vérifiez les définitions d'événements, les filtres appliqués, et la fenêtre temporelle. Proposez toujours de croiser avec d'autres sources (Google Analytics, données financières) pour valider la cohérence.

Comment présenter un funnel analysis aux parties prenantes ?

Commencez par les métriques globales (taux de conversion général), puis détaillez par étape. Utilisez des visualisations en entonnoir, mettez en avant les insights actionnables (où agir en priorité), et proposez des hypothèses d'optimisation avec impact estimé.