Guide complet : Extraire les données GA4 vers BigQuery et Looker Studio

Publié:
15 Août 2025

L'arrivée de Google Analytics a bouleversé la façon dont nous collectons et analysons les données web. Sa dernière version, GA4, propose nativement une intégration avec BigQuery et permet d'exporter les évenements générés sur le site web directement vers un entrepôt de données, ouvrant de nouvelles perspectives pour l'analyse de données à grande échelle.

Ce guide vous accompagne dans la mise en place complète d'une pipeline GA4 → BigQuery → Looker Studio pour exploiter pleinement vos données analytics.

Si vous avez besoin d'un accompagnement personnalisé pour la mise en place de cette solution, n'hésitez pas à me contacter via le formulaire de contact ou par email à contact@florianblanchet.fr

1. Configuration et mise en place

1.1 Prérequis

Avant de commencer, assurez-vous de disposer des éléments suivants :

Côté Google Cloud Platform :

Un compte GCP actif avec facturation configurée
Les APIs BigQuery et Analytics activées
Un projet GCP dédié pour vos données analytics
Les rôles IAM appropriés : BigQuery Admin et Analytics Editor

Côté Google Analytics 4 :

Une propriété GA4 fonctionnelle avec au moins quelques jours de données
Droits administrateur sur cette propriété
Events personnalisés configurés si nécessaire

Estimation des coûts : Pour un site avec 1 million de pages vues par mois, comptez environ 50-100€/mois de stockage et requêtes BigQuery.

1.2 Liaison GA4 - BigQuery

La configuration de l'export se fait directement depuis l'interface GA4 :

1. Accédez à Admin → Linking BigQuery
- Sélectionnez votre propriété GA4
- Cliquez sur "Link" dans la section BigQuery

2. Configuration du projet BigQuery
- Sélectionnez votre projet GCP
- Choisissez la région de stockage (Europe recommandée pour le RGPD)
- Définissez l'ID du dataset (ex: analytics_123456789)

3. Options d'export
- Export quotidien : données consolidées J-1, gratuit
- Export en streaming : données en temps réel, payant (~5€/million d'events)
- Export des conversions : événements de conversion uniquement

{
  "export_frequency": "daily",
  "include_advertising_id": false,
  "export_events": ["all_events"],
  "export_user_data": true
}

Configuration avancée de l'export

Astuce : Commencez par l'export quotidien pour tester, puis activez le streaming si vous avez besoin de données temps réel.

1.3 Structure des données

Une fois l'export configuré, BigQuery crée automatiquement les tables suivantes :

Tables principales :

events_YYYYMMDD : données quotidiennes consolidées
pseudonymous_users_YYYYMMDD : profils utilisateurs anonymisés
events_intraday_YYYYMMDD : données du jour en cours (si streaming activé)

-- Structure type d'un événement GA4
SELECT
    event_date,
    event_timestamp,
    event_name,
    user_pseudo_id,
    session_id,
    event_params,  -- ARRAY de STRUCT(key STRING, value STRUCT)
    user_properties,  -- ARRAY de STRUCT(key STRING, value STRUCT)
    traffic_source,
    device,
    geo,
    ecommerce  -- STRUCT avec purchase_revenue, etc.
FROM `votre-projet.analytics_123456789.events_*`

Schéma des événements GA4

Points clés à retenir :

Les paramètres sont stockés dans des structures nested
Chaque ligne = un événement (pas une session comme dans UA)
Les sessions sont reconstruites via ga_session_id
Les données historiques ne sont pas migrées depuis Universal Analytics

2. Exploitation des données avec SQL

2.1 Requêtes de base

Extraction des métriques principales :

-- Sessions, utilisateurs et pages vues sur les 30 derniers jours
SELECT
    COUNT(DISTINCT CONCAT(user_pseudo_id, 
        (SELECT value.int_value FROM UNNEST(event_params) 
        WHERE key = 'ga_session_id'))) as sessions,
    COUNT(DISTINCT user_pseudo_id) as users,
    COUNTIF(event_name = 'page_view') as page_views,
    AVG(COUNTIF(event_name = 'page_view')) as pages_per_session
FROM `votre-projet.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())

Métriques de base GA4

Manipulation des event_params :

-- Extraction des pages les plus vues avec titre
SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') as page_url,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') as page_title,
    COUNT(*) as page_views,
    COUNT(DISTINCT user_pseudo_id) as unique_users
FROM `votre-projet.analytics_123456789.events_*`
WHERE event_name = 'page_view'
    AND _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE()-1)
GROUP BY 1, 2
ORDER BY page_views DESC
LIMIT 20

Top des pages les plus visitées

2.2 Analyses avancées

Calcul de sessions personnalisées :

-- Reconstruction des sessions avec métriques détaillées
WITH sessions_base AS (
SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as session_id,
    MIN(event_timestamp) as session_start,
    MAX(event_timestamp) as session_end,
    COUNT(*) as events_in_session,
    COUNTIF(event_name = 'page_view') as page_views,
    COUNTIF(event_name = 'purchase') as conversions,
    STRING_AGG(DISTINCT traffic_source.medium, ', ') as traffic_medium
FROM `votre-projet.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE()-1)
GROUP BY 1, 2
)

SELECT
traffic_medium,
COUNT(*) as sessions,
AVG(page_views) as avg_pages_per_session,
AVG((session_end - session_start) / 1000000) as avg_session_duration_seconds,
SUM(conversions) / COUNT(*) * 100 as conversion_rate_percent
FROM sessions_base
GROUP BY 1
ORDER BY sessions DESC

Sessions avec métriques personnalisées

Analyse funnel de conversion :

-- Funnel e-commerce : view_item → add_to_cart → purchase
WITH funnel_events AS (
SELECT
    user_pseudo_id,
    event_name,
    event_timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_id') as item_id
FROM `votre-projet.analytics_123456789.events_*`
WHERE event_name IN ('view_item', 'add_to_cart', 'purchase')
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
),

funnel_users AS (
SELECT
    user_pseudo_id,
    MAX(CASE WHEN event_name = 'view_item' THEN 1 ELSE 0 END) as viewed,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) as added_to_cart,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) as purchased
FROM funnel_events
GROUP BY user_pseudo_id
)

SELECT
    SUM(viewed) as step1_view_item,
    SUM(added_to_cart) as step2_add_to_cart,
    SUM(purchased) as step3_purchase,
    ROUND(SUM(added_to_cart) / SUM(viewed) * 100, 2) as conversion_rate_1_to_2,
    ROUND(SUM(purchased) / SUM(added_to_cart) * 100, 2) as conversion_rate_2_to_3,
    ROUND(SUM(purchased) / SUM(viewed) * 100, 2) as overall_conversion_rate
FROM funnel_users

Analyse de funnel e-commerce

2.3 Optimisation des performances

Utilisation du partitioning :

-- Toujours utiliser des filtres sur _TABLE_SUFFIX pour limiter le scan
SELECT *
FROM `votre-projet.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20241201' AND '20241231'  -- Obligatoire !
AND event_name = 'purchase'

Filtrage efficace des tables

Création de vues agrégées pour Looker Studio :

CREATE OR REPLACE VIEW `votre-projet.analytics_123456789.daily_metrics` AS
WITH daily_stats AS (
SELECT
    PARSE_DATE('%Y%m%d', event_date) as date,
    traffic_source.source,
    traffic_source.medium,
    device.category as device_category,
    COUNT(DISTINCT user_pseudo_id) as users,
    COUNT(DISTINCT CONCAT(user_pseudo_id, 
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) as sessions,
    COUNTIF(event_name = 'page_view') as page_views,
    COUNTIF(event_name = 'purchase') as purchases,
    SUM(CASE WHEN event_name = 'purchase' 
        THEN (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value')
        ELSE 0 END) as revenue
FROM `votre-projet.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
GROUP BY 1, 2, 3, 4
)

SELECT
    *,
    SAFE_DIVIDE(page_views, sessions) as pages_per_session,
    SAFE_DIVIDE(purchases, sessions) * 100 as conversion_rate,
    SAFE_DIVIDE(revenue, purchases) as average_order_value
FROM daily_stats

Vue agrégée quotidienne

3. Visualisation avec Looker Studio

3.1 Connexion BigQuery - Looker Studio

Configuration du connecteur :

1. Création d'une nouvelle source de données
- Ouvrez Looker Studio
- Cliquez sur "Créer" → "Source de données"
- Sélectionnez "BigQuery" dans la liste des connecteurs

2. Authentification et sélection
- Autorisez l'accès à votre projet GCP
- Sélectionnez votre projet BigQuery
- Choisissez le dataset analytics_123456789
- Sélectionnez votre table ou vue (recommandé : utilisez la vue daily_metrics créée précédemment)

3. Configuration des champs

Dimensions : date (Type: Date), source (Type: Texte), medium (Type: Texte), device_category (Type: Texte)
Métriques : users (Type: Nombre, Agrégation: Somme), sessions (Type: Nombre, Agrégation: Somme), revenue (Type: Devise, Agrégation: Somme)

3.2 Création de rapports dynamiques

Dashboard web analytics essentiel :

1. KPIs principaux (Cartes de score)
- Utilisateurs actifs (comparaison avec période précédente)
- Sessions totales
- Taux de conversion global
- Chiffre d'affaires (si e-commerce)

2. Graphique d'évolution temporelle
Configuration : Dimension: date, Métriques: users, sessions, page_views, Type: Graphique linéaire, Période: 90 derniers jours

3. Répartition du trafic (Graphique circulaire)
Configuration : Dimension: medium, Métrique: sessions, Filtre: sessions > 10, Tri: décroissant par sessions

3.3 Visualisations avancées

Analyse des parcours utilisateurs (Funnel) :

-- Vue SQL pour le funnel
CREATE OR REPLACE VIEW `votre-projet.analytics_123456789.conversion_funnel` AS
WITH funnel_data AS (
SELECT
    'Étape 1: Pages vues' as etape,
    1 as ordre,
    COUNT(DISTINCT user_pseudo_id) as users
FROM `votre-projet.analytics_123456789.events_*`
WHERE event_name = 'page_view'
    AND _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE()-1)

UNION ALL

SELECT
    'Étape 2: Ajout panier' as etape,
    2 as ordre,
    COUNT(DISTINCT user_pseudo_id) as users
FROM `votre-projet.analytics_123456789.events_*`
WHERE event_name = 'add_to_cart'
    AND _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE()-1)

UNION ALL

SELECT
    'Étape 3: Commandes' as etape,
    3 as ordre,
    COUNT(DISTINCT user_pseudo_id) as users
FROM `votre-projet.analytics_123456789.events_*`
WHERE event_name = 'purchase'
    AND _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE()-1)
)

SELECT 
    etape,
    users,
    LAG(users) OVER (ORDER BY ordre) as users_precedent,
    ROUND(SAFE_DIVIDE(users, LAG(users) OVER (ORDER BY ordre)) * 100, 2) as taux_conversion
FROM funnel_data
ORDER BY ordre

Vue SQL pour funnel de conversion

Analyses géographiques :

-- Vue pour la répartition géographique
CREATE OR REPLACE VIEW `votre-projet.analytics_123456789.geographic_metrics` AS
SELECT
    geo.country,
    geo.city,
    geo.continent,
    COUNT(DISTINCT user_pseudo_id) as users,
    COUNT(DISTINCT CONCAT(user_pseudo_id, 
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) as sessions,
    SUM(CASE 
        WHEN event_name = 'purchase' 
            THEN (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value')
        ELSE 0
    END) as revenue
FROM `votre-projet.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
    AND geo.country IS NOT NULL
GROUP BY 1, 2, 3
HAVING users > 5  -- Filtre pour éviter les données trop granulaires

Métriques géographiques

Tableaux croisés dynamiques pour l'e-commerce :
Configuration dans Looker Studio :
- Lignes : item_name (depuis event_params)
- Colonnes : event_date (groupé par semaine)
- Valeurs : revenue (somme), quantity (somme)
- Filtres : event_name = 'purchase'

3.4 Optimisation des performances

Utilisation de vues agrégées BigQuery :

Pour améliorer les performances de Looker Studio, créez des tables pré-agrégées :

-- Table agrégée quotidienne (à exécuter via scheduled query)
CREATE OR REPLACE TABLE `votre-projet.analytics_123456789.daily_summary`
PARTITION BY date
CLUSTER BY source, medium
AS
    SELECT
        PARSE_DATE('%Y%m%d', event_date) as date,
        traffic_source.source,
        traffic_source.medium,
        device.category as device_category,
        geo.country,
        COUNT(DISTINCT user_pseudo_id) as users,
        COUNT(DISTINCT CONCAT(user_pseudo_id, 
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) as sessions,
        COUNTIF(event_name = 'page_view') as page_views,
        COUNTIF(event_name = 'purchase') as purchases,
        SUM(CASE WHEN event_name = 'purchase' 
            THEN (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value')
            ELSE 0
        END) as revenue
    FROM `votre-projet.analytics_123456789.events_*`
    WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE()-1)
    GROUP BY 1, 2, 3, 4, 5

Table agrégée quotidienne

Configuration du cache et rafraîchissement :

1. Paramètres de cache dans Looker Studio :
- Activez le cache des données (12 heures recommandées)
- Configurez l'actualisation automatique quotidienne
- Utilisez des extraits de données pour les gros volumes

2. Scheduled Queries BigQuery :

-- Configuration d'une requête planifiée quotidienne
-- Exécution : tous les jours à 02:00 UTC
-- Destination : table daily_summary

INSERT `votre-projet.analytics_123456789.daily_summary`
SELECT * FROM [requête ci-dessus]
WHERE date = CURRENT_DATE()-1

Requête planifiée quotidienne

3. Bonnes pratiques pour les rapports partagés :

Limitez le nombre de graphiques par page (max 8-10)
Utilisez des filtres pour réduire le volume de données
Évitez les calculs complexes côté Looker Studio
Pré-calculez les métriques dans BigQuery

Alertes automatiques sur les KPIs critiques :

-- Exemple de requête pour alertes
SELECT
'Baisse du trafic détectée' as alerte,
users_today,
users_yesterday,
ROUND((users_today - users_yesterday) / users_yesterday * 100, 2) as variation_percent
FROM (
SELECT
    (SELECT COUNT(DISTINCT user_pseudo_id) 
    FROM `votre-projet.analytics_123456789.events_*`
    WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE())) as users_today,
    (SELECT COUNT(DISTINCT user_pseudo_id) 
    FROM `votre-projet.analytics_123456789.events_*`
    WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE()-1)) as users_yesterday
)
WHERE (users_today - users_yesterday) / users_yesterday < -0.2  -- Alerte si -20%

Requête d'alerte automatique

Conclusion :
L'intégration GA4 → BigQuery → Looker Studio vous permet de dépasser les limitations de l'interface GA4 standard et d'exploiter pleinement vos données analytics. Cette approche vous donne accès aux données brutes, permet des analyses personnalisées impossibles dans GA4, facilite l'intégration avec d'autres sources de données et conserve un historique illimité.
C'est une très bonne première étape pour construire une architecture de données robuste et scalable.

ROI attendu :

Réduction du temps d'analyse de 60-80%
Insights business plus précis et actionnables
Automatisation des reportings
Amélioration du taux de conversion grâce aux analyses avancées

Prochaines étapes recommandées :

Commencez par l'export quotidien pour tester
Créez votre banque de vues SQL réutilisables
Développez un dashboard Looker Studio
Formez vos équipes aux nouveaux outils
Intégrez progressivement d'autres sources de données (CRM, ads, etc.) et croisez les pour suivre les prospects de bout en bout du funnel.

Cette stack GA4/BigQuery/Looker Studio devient rapidement indispensable pour exploiter sérieusement les données web analytics. L'investissement initial en configuration et formation est largement compensé par les gains en productivité et qualité des analyses.

Merci de votre lecture et si vous souhaitez un accompagnement pour un audit ou développer votre architecture n'hésitez pas à m'envoyer un message pour en discuter !

Florian

Retour au Blog