ServicesÀ proposNotesContact Me contacter →
EN FR
Note

Tableau actions Meta Ads dans BigQuery

Comment aplatir le tableau JSON imbriqué des actions de Meta dans BigQuery — patterns UNNEST, pivots configurables par type d'action, intégration dbt, et le champ complémentaire action_values.

Planté
bigquerydbtdata engineeringdata modelingetl

Meta ne retourne pas les conversions sous forme de colonnes plates. À la place, l’API Insights retourne le champ actions comme un tableau JSON imbriqué où chaque élément est une paire clé-valeur de type d’action et de comptage. Chaque pipeline qui touche à Meta Ads doit résoudre ce problème d’aplatissement. C’est là que se concentre l’essentiel de l’effort de data engineering.

La structure brute

Le champ actions ressemble à ceci dans la réponse API :

[
  {"action_type": "link_click", "value": "150"},
  {"action_type": "offsite_conversion.fb_pixel_purchase", "value": "12"},
  {"action_type": "lead", "value": "8"},
  {"action_type": "post_engagement", "value": "430"},
  {"action_type": "onsite_conversion.messaging_conversation_started_7d", "value": "2"}
]

Le champ action_values suit la même structure mais contient des valeurs monétaires :

[
  {"action_type": "offsite_conversion.fb_pixel_purchase", "value": "1240.50"},
  {"action_type": "lead", "value": "0"}
]

Toutes les lignes n’ont pas tous les types d’action. Une publicité sans achats n’aura simplement pas offsite_conversion.fb_pixel_purchase dans son tableau. Une publicité sans leads n’aura pas lead. L’ensemble des types d’action présents varie selon la publicité, l’objectif de campagne, et au fil du temps à mesure que Meta introduit de nouveaux événements de conversion.

La liste des types d’action possibles est longue et évolue. La documentation de Meta liste les plus courants, mais de nouveaux types apparaissent avec les lancements de produits et les mises à jour d’API. Toute solution qui code en dur une liste spécifique finira par rencontrer un type non répertorié.

Aplatissement dans BigQuery

L’approche UNNEST

Le pattern BigQuery standard utilise UNNEST pour éclater le tableau et JSON_VALUE pour extraire les champs, suivi d’une agrégation conditionnelle pour pivoter le résultat :

SELECT
  ad_id,
  date_start,
  impressions,
  clicks,
  spend,
  MAX(CASE
    WHEN JSON_VALUE(action, '$.action_type') = 'offsite_conversion.fb_pixel_purchase'
    THEN CAST(JSON_VALUE(action, '$.value') AS INT64)
  END) AS purchases,
  MAX(CASE
    WHEN JSON_VALUE(action, '$.action_type') = 'lead'
    THEN CAST(JSON_VALUE(action, '$.value') AS INT64)
  END) AS leads,
  MAX(CASE
    WHEN JSON_VALUE(action, '$.action_type') = 'link_click'
    THEN CAST(JSON_VALUE(action, '$.value') AS INT64)
  END) AS link_clicks
FROM raw_meta_ads.ad_insights,
UNNEST(JSON_EXTRACT_ARRAY(actions_json)) AS action
GROUP BY 1, 2, 3, 4, 5

Le pivot MAX() avec CASE fonctionne car après UNNEST, chaque ligne a un seul type d’action. L’agrégation avec MAX donne une ligne par publicité par jour avec des colonnes séparées pour chaque type d’action.

Associer actions et action_values

Les achats ont à la fois un comptage (depuis actions) et une valeur de revenus (depuis action_values). Vous avez besoin des deux tableaux dans la même requête :

SELECT
  ad_id,
  date_start,
  spend,
  -- Depuis le tableau actions
  MAX(CASE
    WHEN JSON_VALUE(action, '$.action_type') = 'offsite_conversion.fb_pixel_purchase'
    THEN CAST(JSON_VALUE(action, '$.value') AS INT64)
  END) AS purchases,
  -- Depuis le tableau action_values
  MAX(CASE
    WHEN JSON_VALUE(action_value, '$.action_type') = 'offsite_conversion.fb_pixel_purchase'
    THEN CAST(JSON_VALUE(action_value, '$.value') AS FLOAT64)
  END) AS purchase_value
FROM raw_meta_ads.ad_insights,
UNNEST(JSON_EXTRACT_ARRAY(actions_json)) AS action,
UNNEST(JSON_EXTRACT_ARRAY(action_values_json)) AS action_value
GROUP BY 1, 2, 3

La cross-jointure résultant du dépliage des deux tableaux crée un produit cartésien — à gérer avec soin. Si votre table stocke actions et action_values comme ARRAY plutôt que comme chaînes JSON, utilisez LEFT JOIN UNNEST() à la place :

FROM raw_meta_ads.ad_insights
LEFT JOIN UNNEST(actions) AS action
LEFT JOIN UNNEST(action_values) AS action_value

Gérer les valeurs nulles

Les valeurs d’action arrivent sous forme de chaînes dans l’API (“12” et non 12) et peuvent être nulles ou absentes. Cast défensif :

SAFE_CAST(JSON_VALUE(action, '$.value') AS INT64)

SAFE_CAST retourne NULL au lieu d’échouer sur des valeurs incorrectes, ce qui protège contre les cas limites dans les données live.

Rendre les types d’action configurables

Coder en dur les chaînes de types d’action dans le SQL crée un problème de maintenance : quand un nouvel événement de conversion est important pour le métier, quelqu’un doit modifier le code du modèle. Le meilleur pattern rend les types d’action configurables via des variables dbt ou un fichier seed.

Utilisation des variables dbt

-- models/intermediate/int__meta_ads__actions_pivoted.sql
{% set action_types = var('meta_ads_action_types', [
    'offsite_conversion.fb_pixel_purchase',
    'lead',
    'link_click',
    'post_engagement'
]) %}


SELECT
  ad_id,
  date_start,
  impressions,
  clicks,
  spend,
  {% for action_type in action_types %}
  MAX(CASE
    WHEN JSON_VALUE(action, '$.action_type') = '{{ action_type }}'
    THEN SAFE_CAST(JSON_VALUE(action, '$.value') AS INT64)
  END) AS {{ action_type | replace('.', '_') | replace('-', '_') }},
  {% endfor %}
FROM {{ source('meta_ads', 'ad_insights') }},
UNNEST(JSON_EXTRACT_ARRAY(actions_json)) AS action
GROUP BY 1, 2, 3, 4, 5

Dans dbt_project.yml :

vars:
  meta_ads_action_types:
    - offsite_conversion.fb_pixel_purchase
    - lead
    - link_click
    - post_engagement
    - onsite_conversion.purchase

Ajouter un nouveau type d’action est désormais un changement de configuration, pas un changement de code. La boucle Jinja génère les instructions CASE au moment de la compilation.

Utilisation du package dbt_facebook_ads de Fivetran

Si vous utilisez Fivetran comme couche d’extraction, le package dbt_facebook_ads gère le pivot pour vous. Configurez les types d’action via des variables :

vars:
  facebook_ads__conversion_action_types:
    - name: offsite_conversion.fb_pixel_purchase
    - pattern: onsite_conversion%

La syntaxe pattern utilise la correspondance SQL LIKE, ce qui vous permet de capturer des familles entières de types d’action (tous les événements onsite_conversion.*) sans les lister individuellement. Le package crée des modèles de base (basic_ad_report, actions_report, action_values_report) et des modèles mart aux niveaux compte, campagne, groupe d’annonces et annonce.

Normalisation pour le reporting cross-plateforme

Lors de la construction d’un modèle publicitaire unifié qui inclut Google Ads et LinkedIn, les actions Meta doivent se mapper à un ensemble commun de métriques. La couche intermédiaire est là où se produit cette normalisation.

Les métriques cross-plateformes standard sont : clicks, impressions, spend, conversions, conversions_value.

Pour Meta :

  • spend se mappe directement (déjà en unités monétaires, contrairement aux micros de Google)
  • impressions et clicks sont des champs directs (pas depuis le tableau actions)
  • conversions nécessite de choisir quels types d’action comptabiliser — documentez cette décision
  • conversions_value vient de action_values avec le même filtre de type d’action
-- models/intermediate/int__meta_ads__campaign_performance.sql
SELECT
  date_start AS date_day,
  campaign_id,
  campaign_name,
  impressions,
  clicks,
  spend,
  -- Conversions : définir ce qui compte comme conversion pour ce métier
  COALESCE(purchases, 0) + COALESCE(leads, 0) AS conversions,
  COALESCE(purchase_value, 0) AS conversions_value
FROM {{ ref('int__meta_ads__actions_pivoted') }}

Le choix des types d’action qui entrent dans conversions est spécifique au métier et doit être documenté dans la description du modèle et idéalement dans un bloc de documentation dbt. La prochaine personne qui lira ce modèle doit comprendre pourquoi les leads et achats sont combinés mais pas le post_engagement.

Stocker les données brutes avant transformation

Une pratique opérationnelle qui vaut la peine d’être intégrée dès le départ : stocker le JSON actions brut avant transformation. BigQuery permet de stocker du JSON comme colonne STRING, ou vous pouvez utiliser le type JSON natif (disponible dans BigQuery depuis 2023).

-- Conserver le tableau brut comme filet de sécurité
SELECT
  ad_id,
  date_start,
  actions_json,  -- le tableau brut, non transformé
  action_values_json,
  -- ... colonnes pivotées
FROM ...

Quand Meta introduit un nouveau type d’action que vous n’avez pas pivoté, vous pouvez interroger la colonne brute pour investiguer sans re-tirer les données depuis l’API. Quand votre liste de variables dbt est incomplète pour une période historique, la colonne brute vous permet de remplir les données manquantes sans appel API. Le raw-avant-transformation est la pratique standard pour toute source API dont le schéma évolue.

Ce qui change au fil du temps

L’espace de noms des types d’action n’est pas stable. Meta introduit de nouveaux types avec les lancements de produits — événements Live Shopping, événements catalogue Advantage+, nouveaux types d’interaction vidéo — et déprécie occasionnellement les anciens. Le tableau actions contiendra parfois des types que vous n’avez pas mappés.

La meilleure défense est de journaliser les types d’action non mappés dans votre couche de transformation. Si un type d’action apparaît dans les données brutes mais ne correspond à aucune clé de votre liste de variables, signalez-le quelque part — un test singulier dbt, une requête de monitoring, une alerte Slack. Les types d’action non mappés signifient que vous laissez des données de conversion de côté.

-- tests/assert_no_unmapped_meta_action_types.sql
-- Échoue si les données brutes contiennent des types d'action non dans notre liste configurée
WITH known_types AS (
  SELECT action_type FROM UNNEST([
    'offsite_conversion.fb_pixel_purchase',
    'lead',
    'link_click',
    'post_engagement'
  ]) AS action_type
),
raw_types AS (
  SELECT DISTINCT JSON_VALUE(action, '$.action_type') AS action_type
  FROM {{ source('meta_ads', 'ad_insights') }},
  UNNEST(JSON_EXTRACT_ARRAY(actions_json)) AS action
  WHERE date_start >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
)
SELECT raw_types.action_type
FROM raw_types
LEFT JOIN known_types USING (action_type)
WHERE known_types.action_type IS NULL

Exécutez ce test hebdomadairement. Quand il signale de nouveaux types, décidez si vous les ajoutez à votre liste de variables ou documentez pourquoi ils sont intentionnellement exclus.