models/
├── base/
│   ├── base/base_ga4__events
│   ├── stg_ga4__events
│   ├── stg_ga4__event_page_view
│   └── stg_ga4__sessions_traffic_sources
└── marts/
    └── core/
        ├── dim_ga4__sessions
        └── fct_ga4__sessions

vars:
  ga4:
    page_view_custom_parameters:
      - name: "clean_event"
        value_type: "string_value"

models/
├── base/
│   └── ga4/
│       ├── _ga4__sources.yml
│       ├── _ga4__models.yml
│       └── base__ga4__events.sql              ← Modèle de base unique
├── intermediate/
│   └── ga4/
│       ├── _int_ga4__models.yml
│       ├── int__ga4__event_items.sql          ← Grain item
│       └── int__ga4__events_sessionized.sql   ← La table principale
    └── ga4/
        ├── _mrt_ga4__models.yml
        ├── mrt__analytics__sessions.sql
        └── mrt__analytics__users.sql

macros/
└── ga4/
    ├── extract_event_param.sql
    ├── generate_surrogate_key.sql
    └── default_channel_grouping.sql

-- models/base/ga4/base__ga4__events.sql

{{
    config(
        materialized='incremental',
        incremental_strategy='insert_overwrite',
        partition_by={
            "field": "event__date",
            "data_type": "date",
            "granularity": "day"
        },
        cluster_by=['user__pseudo_id', 'session__key', 'event__name']
    )
}}

{% set lookback_days = var('ga4_static_incremental_days', 3) %}

WITH source AS (

    SELECT
        event_date,
        event_timestamp,
        event_name,
        event_params,
        event_bundle_sequence_id,
        user_id,
        user_pseudo_id,
        user_properties,
        device,
        geo,
        ecommerce,
        items,
        collected_traffic_source,
        is_active_user
    FROM {{ source('ga4', 'events') }}
    WHERE _TABLE_SUFFIX >= '{{ var("ga4_start_date", "20230101") }}'

    {% if is_incremental() %}
        AND _TABLE_SUFFIX >= FORMAT_DATE(
            '%Y%m%d',
            DATE_SUB(CURRENT_DATE(), INTERVAL {{ lookback_days }} DAY)
        )
    {% endif %}

    -- Exclure les tables intraday
    AND _TABLE_SUFFIX NOT LIKE '%intraday%'

),

cleaned AS (

    SELECT
        -- Timestamps
        PARSE_DATE('%Y%m%d', event_date) AS event__date,
        TIMESTAMP_MICROS(event_timestamp) AS event__timestamp_utc,
        event_timestamp AS event__timestamp_raw,

        -- Identifiants utilisateur
        user_pseudo_id AS user__pseudo_id,
        user_id AS user__id,

        -- Identifiants de session
        {{ extract_event_param('event_params', 'ga_session_id', 'int_value') }}
            AS session__ga_id,
        {{ extract_event_param('event_params', 'ga_session_number', 'int_value') }}
            AS session__number,

        -- Détails de l'événement
        event_name AS event__name,
        event_bundle_sequence_id AS event__bundle_sequence_id,

        -- Informations de page
        {{ extract_event_param('event_params', 'page_location', 'string_value') }}
            AS page__location,
        {{ extract_event_param('event_params', 'page_title', 'string_value') }}
            AS page__title,
        {{ extract_event_param('event_params', 'page_referrer', 'string_value') }}
            AS page__referrer,

        -- Source de trafic (niveau événement)
        {{ extract_event_param('event_params', 'source', 'string_value') }}
            AS event__source,
        {{ extract_event_param('event_params', 'medium', 'string_value') }}
            AS event__medium,
        {{ extract_event_param('event_params', 'campaign', 'string_value') }}
            AS event__campaign,

        -- Engagement
        {{ extract_event_param('event_params', 'session_engaged', 'string_value') }}
            AS session__is_engaged,
        {{ extract_event_param('event_params', 'engagement_time_msec', 'int_value') }}
            AS event__engagement_time_msec,
        {{ extract_event_param('event_params', 'entrances', 'int_value') }}
            AS event__is_entrance,

        -- Device et géolocalisation
        device.category AS device__category,
        device.mobile_brand_name AS device__brand,
        device.operating_system AS device__os,
        device.web_info.browser AS device__browser,
        geo.country AS geo__country,
        geo.city AS geo__city,

        -- Source de trafic (niveau session, depuis collected_traffic_source)
        collected_traffic_source.manual_source AS session__source,
        collected_traffic_source.manual_medium AS session__medium,
        collected_traffic_source.manual_campaign_name AS session__campaign,
        collected_traffic_source.gclid AS session__gclid,

        -- E-commerce
        ecommerce.transaction_id AS transaction__id,
        ecommerce.purchase_revenue AS transaction__revenue,

        -- Conserver les structures imbriquées pour les modèles en aval
        event_params AS event__params,
        user_properties AS user__properties,
        items AS items__array

    FROM source

)

SELECT
    -- Clé primaire
    {{ generate_surrogate_key(['user__pseudo_id', 'event__timestamp_raw', 'event__name']) }}
        AS event__key,

    -- Clé de session (composite)
    {{ generate_surrogate_key(['user__pseudo_id', 'session__ga_id']) }}
        AS session__key,

    -- Timestamps
    event__date,
    event__timestamp_utc,
    event__timestamp_raw,

    -- Identifiants utilisateur
    user__pseudo_id,
    user__id,

    -- Identifiants de session
    session__ga_id,
    session__number,

    -- Détails de l'événement
    event__name,
    event__bundle_sequence_id,

    -- Informations de page
    page__location,
    page__title,
    page__referrer,

    -- Source de trafic (niveau événement)
    event__source,
    event__medium,
    event__campaign,

    -- Engagement
    session__is_engaged,
    event__engagement_time_msec,
    event__is_entrance,

    -- Device et géolocalisation
    device__category,
    device__brand,
    device__os,
    device__browser,
    geo__country,
    geo__city,

    -- Source de trafic (niveau session)
    session__source,
    session__medium,
    session__campaign,
    session__gclid,

    -- E-commerce
    transaction__id,
    transaction__revenue,

    -- Structures imbriquées
    event__params,
    user__properties,
    items__array

FROM cleaned

-- macros/ga4/extract_event_param.sql

{% macro extract_event_param(params_column, param_key, value_type='string_value') %}
    (
        SELECT value.{{ value_type }}
        FROM UNNEST({{ params_column }})
        WHERE key = '{{ param_key }}'
        LIMIT 1
    )
{% endmacro %}

{% macro extract_event_param_numeric(params_column, param_key) %}
    (
        SELECT COALESCE(
            value.int_value,
            CAST(value.float_value AS INT64),
            CAST(value.double_value AS INT64)
        )
        FROM UNNEST({{ params_column }})
        WHERE key = '{{ param_key }}'
        LIMIT 1
    )
{% endmacro %}

-- models/intermediate/ga4/int__ga4__event_items.sql

{{
    config(
        materialized='view'
    )
}}

WITH events_with_items AS (

    SELECT
        event__key,
        event__date,
        event__timestamp_utc,
        user__pseudo_id,
        session__key,
        event__name,
        transaction__id,
        items__array
    FROM {{ ref('base__ga4__events') }}
    WHERE event__name IN ('purchase', 'add_to_cart', 'remove_from_cart',
                         'view_item', 'begin_checkout', 'add_payment_info')
        AND ARRAY_LENGTH(items__array) > 0

)

SELECT
    e.event__key,
    e.event__date,
    e.event__timestamp_utc,
    e.user__pseudo_id,
    e.session__key,
    e.event__name,
    e.transaction__id,

    -- Détails item
    item.item_id AS item__id,
    item.item_name AS item__name,
    item.item_brand AS item__brand,
    item.item_category AS item__category,
    item.item_category2 AS item__category2,
    item.item_category3 AS item__category3,
    item.item_variant AS item__variant,
    item.price AS item__price,
    item.quantity AS item__quantity,
    item.coupon AS item__coupon,
    item.item_list_name AS item__list_name,
    item.item_list_index AS item__list_index,

    -- Calculer le revenu item
    COALESCE(item.price, 0) * COALESCE(item.quantity, 1) AS item__revenue

FROM events_with_items e,
UNNEST(e.items__array) AS item

-- models/intermediate/ga4/int__ga4__events_sessionized.sql

{{
    config(
        materialized='incremental',
        incremental_strategy='insert_overwrite',
        partition_by={
            "field": "event__date",
            "data_type": "date",
            "granularity": "day"
        },
        cluster_by=['user__pseudo_id', 'session__key', 'event__name']
    )
}}

{% set lookback_days = var('ga4_static_incremental_days', 3) %}

WITH events AS (

    SELECT
        event__key,
        session__key,
        event__date,
        event__timestamp_utc,
        user__pseudo_id,
        user__id,
        session__ga_id,
        session__number,
        event__name,
        page__location,
        page__title,
        event__source,
        event__medium,
        event__campaign,
        session__source,
        session__medium,
        session__campaign,
        event__engagement_time_msec,
        device__category,
        geo__country,
        transaction__revenue
    FROM {{ ref('base__ga4__events') }}

    {% if is_incremental() %}
    WHERE event__date >= DATE_SUB(CURRENT_DATE(), INTERVAL {{ lookback_days }} DAY)
    {% endif %}

),

-- Agréger les items par événement (éviter les jointures répétées)
event_items AS (

    SELECT
        event__key,
        COUNT(*) AS event__items,
        SUM(item__revenue) AS event__items_revenue
    FROM {{ ref('int__ga4__event_items') }}
    {% if is_incremental() %}
    WHERE event__date >= DATE_SUB(CURRENT_DATE(), INTERVAL {{ lookback_days }} DAY)
    {% endif %}
    GROUP BY event__key

),

-- Nettoyer les URLs de page pour l'analyse landing/exit page
with_clean_urls AS (

    SELECT
        *,
        -- Supprimer les paramètres de requête pour des landing/exit pages plus lisibles
        REGEXP_REPLACE(
            SPLIT(page__location, '?')[SAFE_OFFSET(0)],
            r'#.*$',
            ''
        ) AS page__path
    FROM events

),

-- Ajouter les métriques de session via window functions
with_session_metrics AS (

    SELECT
        e.*,

        -- Landing et exit pages
        FIRST_VALUE(page__path IGNORE NULLS) OVER (session_window) AS session__landing_page,
        FIRST_VALUE(page__location IGNORE NULLS) OVER (session_window) AS session__landing_page_full,
        LAST_VALUE(page__path IGNORE NULLS) OVER (session_window_full) AS session__exit_page,

        -- Source de trafic de session (première source non nulle dans la session)
        FIRST_VALUE(
            COALESCE(event__source, session__source) IGNORE NULLS
        ) OVER (session_window) AS session__source_final,
        FIRST_VALUE(
            COALESCE(event__medium, session__medium) IGNORE NULLS
        ) OVER (session_window) AS session__medium_final,
        FIRST_VALUE(
            COALESCE(event__campaign, session__campaign) IGNORE NULLS
        ) OVER (session_window) AS session__campaign_final,

        -- Métriques d'engagement de session
        COUNT(*) OVER (PARTITION BY session__key) AS session__events,
        COUNT(CASE WHEN event__name = 'page_view' THEN 1 END)
            OVER (PARTITION BY session__key) AS session__pageviews,
        SUM(event__engagement_time_msec) OVER (PARTITION BY session__key) AS session__engagement_time_msec,

        -- Durée de session
        TIMESTAMP_DIFF(
            MAX(event__timestamp_utc) OVER (PARTITION BY session__key),
            MIN(event__timestamp_utc) OVER (PARTITION BY session__key),
            SECOND
        ) AS session__duration_seconds,

        -- Flags de conversion de session
        MAX(CASE WHEN event__name = 'purchase' THEN 1 ELSE 0 END)
            OVER (PARTITION BY session__key) AS session__has_purchase,
        MAX(CASE WHEN event__name = 'add_to_cart' THEN 1 ELSE 0 END)
            OVER (PARTITION BY session__key) AS session__has_add_to_cart,
        MAX(CASE WHEN event__name = 'begin_checkout' THEN 1 ELSE 0 END)
            OVER (PARTITION BY session__key) AS session__has_checkout,
        MAX(CASE WHEN event__name = 'sign_up' THEN 1 ELSE 0 END)
            OVER (PARTITION BY session__key) AS session__has_signup,
        MAX(CASE WHEN event__name = 'generate_lead' THEN 1 ELSE 0 END)
            OVER (PARTITION BY session__key) AS session__has_lead,

        -- Revenu de session
        SUM(transaction__revenue) OVER (PARTITION BY session__key) AS session__revenue,

        -- Séquençage d'événements dans la session
        ROW_NUMBER() OVER (
            PARTITION BY session__key
            ORDER BY event__timestamp_utc
        ) AS event__sequence_number,

        -- Est-ce le premier/dernier événement de la session ?
        CASE
            WHEN ROW_NUMBER() OVER (
                PARTITION BY session__key
                ORDER BY event__timestamp_utc
            ) = 1 THEN TRUE
            ELSE FALSE
        END AS event__is_session_start,

        CASE
            WHEN ROW_NUMBER() OVER (
                PARTITION BY session__key
                ORDER BY event__timestamp_utc DESC
            ) = 1 THEN TRUE
            ELSE FALSE
        END AS event__is_session_end,

        -- Temps depuis l'événement précédent (pour l'analyse d'engagement)
        TIMESTAMP_DIFF(
            event__timestamp_utc,
            LAG(event__timestamp_utc) OVER (
                PARTITION BY session__key
                ORDER BY event__timestamp_utc
            ),
            SECOND
        ) AS event__seconds_since_previous

    FROM with_clean_urls e

    WINDOW
        session_window AS (
            PARTITION BY session__key
            ORDER BY event__timestamp_utc
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ),
        session_window_full AS (
            PARTITION BY session__key
            ORDER BY event__timestamp_utc
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        )

),

-- Ajouter le channel grouping
with_channel AS (

    SELECT
        *,
        {{ default_channel_grouping('session__source_final', 'session__medium_final') }}
            AS session__channel_grouping
    FROM with_session_metrics

),

-- Joindre les agrégats items
final AS (

    SELECT
        e.*,
        COALESCE(i.event__items, 0) AS event__items,
        COALESCE(i.event__items_revenue, 0) AS event__items_revenue
    FROM with_channel e
    LEFT JOIN event_items i ON e.event__key = i.event__key

)

SELECT * FROM final

-- FAUX : renvoie la page de la ligne courante
LAST_VALUE(page__path) OVER (PARTITION BY session__key ORDER BY event__timestamp_utc)

-- CORRECT : renvoie la vraie dernière page
LAST_VALUE(page__path) OVER (
    PARTITION BY session__key
    ORDER BY event__timestamp_utc
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

FIRST_VALUE(page__path IGNORE NULLS) OVER (session_window) AS session__landing_page

MAX(CASE WHEN event__name = 'purchase' THEN 1 ELSE 0 END)
    OVER (PARTITION BY session__key) AS session__has_purchase

-- macros/ga4/default_channel_grouping.sql

{% macro default_channel_grouping(source_column, medium_column) %}
    CASE
        -- Direct
        WHEN {{ source_column }} IS NULL
             OR {{ source_column }} = '(direct)'
             OR {{ source_column }} = '(none)'
        THEN 'Direct'

        -- Paid Search
        WHEN REGEXP_CONTAINS({{ medium_column }}, r'^(cpc|ppc|paidsearch)$')
             OR ({{ source_column }} = 'google' AND {{ medium_column }} = 'sem')
        THEN 'Paid Search'

        -- Paid Social
        WHEN REGEXP_CONTAINS({{ medium_column }}, r'^(paidsocial|paid-social|paid_social)$')
             OR (REGEXP_CONTAINS({{ source_column }}, r'facebook|instagram|linkedin|twitter|tiktok')
                 AND REGEXP_CONTAINS({{ medium_column }}, r'^(cpc|ppc|paid)'))
        THEN 'Paid Social'

        -- Display
        WHEN REGEXP_CONTAINS({{ medium_column }}, r'^(display|cpm|banner)$')
        THEN 'Display'

        -- Organic Search
        WHEN {{ medium_column }} = 'organic'
             OR REGEXP_CONTAINS({{ source_column }}, r'google|bing|yahoo|duckduckgo|baidu')
                AND {{ medium_column }} = 'organic'
        THEN 'Organic Search'

        -- Organic Social
        WHEN REGEXP_CONTAINS({{ source_column }}, r'facebook|instagram|linkedin|twitter|tiktok|pinterest|youtube')
             AND ({{ medium_column }} IS NULL OR {{ medium_column }} IN ('social', 'referral', '(none)'))
        THEN 'Organic Social'

        -- Email
        WHEN REGEXP_CONTAINS({{ medium_column }}, r'^(email|e-mail|e_mail)$')
        THEN 'Email'

        -- Affiliate
        WHEN REGEXP_CONTAINS({{ medium_column }}, r'^(affiliate|affiliates)$')
        THEN 'Affiliates'

        -- Referral
        WHEN {{ medium_column }} = 'referral'
        THEN 'Referral'

        -- Other
        ELSE 'Other'
    END
{% endmacro %}

-- tests/singular/test_sessions_missing_session_start.sql

-- Les sessions devraient avoir un événement session_start
-- Certaines sessions peuvent légitimement ne pas en avoir (bloqueurs de pub, consentement),
-- mais un pourcentage élevé indique un problème de tracking

WITH session_stats AS (
    SELECT
        session__key,
        COUNT(*) AS session__events,
        MAX(CASE WHEN event__name = 'session_start' THEN 1 ELSE 0 END) AS session__has_start
    FROM {{ ref('int__ga4__events_sessionized') }}
    WHERE event__date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    GROUP BY session__key
)

SELECT
    session__key,
    session__events,
    session__has_start
FROM session_stats
WHERE session__has_start = 0
    AND session__events > 3  -- Sessions légitimes avec plusieurs événements
LIMIT 100

-- tests/singular/test_purchase_without_session.sql

-- Les achats devraient avoir un contexte de session valide

SELECT
    event__key,
    event__date,
    session__ga_id,
    user__pseudo_id
FROM {{ ref('base__ga4__events') }}
WHERE event__name = 'purchase'
    AND transaction__id IS NOT NULL
    AND (session__ga_id IS NULL OR user__pseudo_id IS NULL)
    AND event__date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
LIMIT 100

# Projet dbt GA4

Ce projet transforme les exports bruts GA4 BigQuery en tables prêtes pour l'analyse.

## Démarrage rapide

1. Configurez vos variables dans `dbt_project.yml` :
   ```yaml
   vars:
     ga4_project_id: "votre-projet-gcp"
     ga4_dataset: "analytics_123456789"
     ga4_start_date: "20230101"
     ga4_static_incremental_days: 3

base__ga4__events (shardé → partitionné, nettoyé, typé)
    ↓
int__ga4__events_sessionized (sessionisé)
    ↓
mrt__analytics__sessions / mrt__analytics__users (marts)

### Visualisation du lignage

Générez la documentation dbt pour le lignage visuel :

```bash
dbt docs generate
dbt docs serve

graph LR
    A[events_*] --> B[base__ga4__events]
    B --> C[int__ga4__event_items]
    B --> D[int__ga4__events_sessionized]
    C --> D
    D --> E[mrt__analytics__sessions]
    D --> F[mrt__analytics__users]

name: 'ga4_analytics'
version: '1.0.0'

profile: 'ga4_analytics'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]

target-path: "target"
clean-targets:
  - "target"
  - "dbt_packages"

vars:
  # Configuration GCP
  ga4_project_id: "{{ env_var('GA4_PROJECT_ID') }}"
  ga4_dataset: "{{ env_var('GA4_DATASET', 'analytics_123456789') }}"

  # Configuration de traitement
  ga4_start_date: "20230101"
  ga4_static_incremental_days: 3

  # Configuration métier
  ga4_conversion_events:
    - 'purchase'
    - 'sign_up'
    - 'generate_lead'
    - 'contact_form_submit'

  # Nettoyage d'URL
  ga4_query_params_to_remove:
    - 'gclid'
    - 'fbclid'
    - 'utm_id'
    - '_ga'

models:
  ga4_analytics:
    base:
      +schema: base
      +materialized: view
      +tags: ['ga4', 'base']
      ga4:
        +materialized: incremental

    intermediate:
      +schema: intermediate
      +materialized: incremental
      +tags: ['ga4', 'intermediate']

    marts:
      +schema: marts
      +materialized: table
      +tags: ['ga4', 'marts']

tests:
  +severity: warn
  +store_failures: true

-- models/marts/ga4/mrt__analytics__sessions.sql

{{
    config(
        materialized='table',
        partition_by={
            "field": "event__date",
            "data_type": "date"
        },
        cluster_by=['session__channel_grouping', 'device__category']
    )
}}

SELECT
    session__key,
    user__pseudo_id,
    session__ga_id,
    session__number,

    -- Timestamps
    event__date,
    MIN(event__timestamp_utc) AS session__started_at,
    MAX(event__timestamp_utc) AS session__ended_at,

    -- Attributs de session (prendre de n'importe quelle ligne, ils sont tous identiques)
    ANY_VALUE(session__landing_page) AS session__landing_page,
    ANY_VALUE(session__exit_page) AS session__exit_page,
    ANY_VALUE(session__source_final) AS session__source,
    ANY_VALUE(session__medium_final) AS session__medium,
    ANY_VALUE(session__campaign_final) AS session__campaign,
    ANY_VALUE(session__channel_grouping) AS session__channel_grouping,
    ANY_VALUE(device__category) AS device__category,
    ANY_VALUE(geo__country) AS geo__country,

    -- Métriques d'engagement
    ANY_VALUE(session__pageviews) AS session__pageviews,
    ANY_VALUE(session__events) AS session__events,
    ANY_VALUE(session__duration_seconds) AS session__duration_seconds,
    ANY_VALUE(session__engagement_time_msec) / 1000 AS session__engagement_time_seconds,

    -- Flags de conversion
    ANY_VALUE(session__has_purchase) AS session__has_purchase,
    ANY_VALUE(session__has_add_to_cart) AS session__has_add_to_cart,
    ANY_VALUE(session__has_checkout) AS session__has_checkout,
    ANY_VALUE(session__has_signup) AS session__has_signup,

    -- Revenu
    ANY_VALUE(session__revenue) AS session__revenue

FROM {{ ref('int__ga4__events_sessionized') }}
GROUP BY 1, 2, 3, 4, 5

-- models/marts/ga4/mrt__analytics__users.sql

{{
    config(
        materialized='table',
        cluster_by=['user__first_seen_at']
    )
}}

WITH user_sessions AS (

    SELECT
        user__pseudo_id,
        user__id,
        session__key,
        event__date,
        session__started_at,
        session__channel_grouping,
        device__category,
        geo__country,
        session__has_purchase,
        session__revenue
    FROM {{ ref('mrt__analytics__sessions') }}

),

user_first_last AS (

    SELECT
        user__pseudo_id,

        -- Première visite
        MIN(event__date) AS user__first_seen_at,
        FIRST_VALUE(session__channel_grouping) OVER (
            PARTITION BY user__pseudo_id
            ORDER BY event__date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS user__first_channel,
        FIRST_VALUE(device__category) OVER (
            PARTITION BY user__pseudo_id
            ORDER BY event__date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS user__first_device,

        -- Dernière visite
        MAX(event__date) AS user__last_seen_at,
        LAST_VALUE(session__channel_grouping) OVER (
            PARTITION BY user__pseudo_id
            ORDER BY event__date
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS user__last_channel,

        -- Agrégats
        COUNT(DISTINCT session__key) AS user__sessions,
        MAX(session__has_purchase) AS user__has_purchased,
        SUM(session__revenue) AS user__lifetime_revenue

    FROM user_sessions
    GROUP BY user__pseudo_id

),

-- Réconcilier user_id (depuis l'article 4)
user_identity AS (

    SELECT DISTINCT
        user__pseudo_id,
        FIRST_VALUE(user__id IGNORE NULLS) OVER (
            PARTITION BY user__pseudo_id
            ORDER BY event__date DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS user__stitched_id
    FROM user_sessions
    WHERE user__id IS NOT NULL

)

SELECT
    u.user__pseudo_id,
    i.user__stitched_id AS user__id,
    u.user__first_seen_at,
    u.user__last_seen_at,
    u.user__first_channel,
    u.user__first_device,
    u.user__last_channel,
    u.user__sessions,
    u.user__has_purchased,
    u.user__lifetime_revenue,

    -- Métriques dérivées
    DATE_DIFF(u.user__last_seen_at, u.user__first_seen_at, DAY) AS user__lifespan_days,
    DATE_DIFF(CURRENT_DATE(), u.user__last_seen_at, DAY) AS user__days_since_last_visit

FROM user_first_last u
LEFT JOIN user_identity i ON u.user__pseudo_id = i.user__pseudo_id