ServicesAboutNotesContact Get in touch →
EN FR
Note

Meta Ads Actions Array in BigQuery

How to flatten Meta's nested actions JSON array in BigQuery — unnesting patterns, configurable action type pivots, dbt integration, and the action_values companion field.

Planted
bigquerydbtdata engineeringdata modelingetl

Meta doesn’t return conversions as flat columns. Instead, the Insights API returns the actions field as a nested JSON array where each element is a key-value pair of action type and count. Every pipeline that touches Meta Ads has to solve this flattening problem. It’s where most of the engineering effort goes.

The Raw Structure

The actions field looks like this in the API response:

[
  {"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"}
]

The action_values field follows the same structure but contains monetary values:

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

Not every row has every action type. An ad with no purchases simply won’t have offsite_conversion.fb_pixel_purchase in its array. An ad with no leads won’t have lead. The set of action types present varies by ad, by campaign objective, and over time as Meta introduces new conversion events.

The list of possible action types is long and changes. Meta’s documentation lists the most common ones, but new types appear with product launches and API updates. Any solution that hardcodes a specific list will eventually encounter an unlisted type.

Flattening in BigQuery

The UNNEST approach

The standard BigQuery pattern uses UNNEST to explode the array and JSON_VALUE to extract fields, followed by conditional aggregation to pivot the result:

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

The MAX() with CASE pivot works because after UNNEST, each row has one action type. Aggregating back up with MAX gives you one row per ad per day with separate columns for each action type.

Pairing actions with action_values

Purchases have both a count (from actions) and a revenue value (from action_values). You need both arrays in the same query:

SELECT
  ad_id,
  date_start,
  spend,
  -- From actions array
  MAX(CASE
    WHEN JSON_VALUE(action, '$.action_type') = 'offsite_conversion.fb_pixel_purchase'
    THEN CAST(JSON_VALUE(action, '$.value') AS INT64)
  END) AS purchases,
  -- From action_values array
  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

The cross-join from unnesting both arrays creates a Cartesian product — handle this carefully. If your table stores actions and action_values as ARRAY rather than JSON strings, use LEFT JOIN UNNEST() instead:

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

Handling null values

Action values arrive as strings in the API (“12” not 12) and may be null or absent. Defensive casting:

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

SAFE_CAST returns NULL instead of erroring on bad values, which protects against edge cases in live data.

Making Action Types Configurable

Hardcoding action type strings in SQL creates a maintenance problem: when a new conversion event matters to the business, someone has to modify model code. The better pattern makes action types configurable through dbt variables or a seed file.

Using dbt variables

-- 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

In dbt_project.yml:

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

Adding a new action type is now a config change, not a code change. The Jinja loop generates the CASE statements at compile time.

Using Fivetran’s dbt_facebook_ads package

If you’re using Fivetran as your extraction layer, the dbt_facebook_ads package handles the pivot for you. Configure action types through variables:

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

The pattern syntax uses SQL LIKE matching, which lets you capture entire families of action types (all onsite_conversion.* events) without listing each individually. The package creates base models (basic_ad_report, actions_report, action_values_report) and mart models at account, campaign, ad set, and ad levels.

Normalizing for Cross-Platform Reporting

When building a unified ad model that includes Google Ads and LinkedIn, Meta’s actions need to map to a common set of metrics. The intermediate layer is where this normalization happens.

The standard cross-platform metrics are: clicks, impressions, spend, conversions, conversions_value.

For Meta:

  • spend maps directly (already in currency units, unlike Google’s micros)
  • impressions and clicks are direct fields (not from the actions array)
  • conversions requires choosing which action types to count — document this decision
  • conversions_value comes from action_values with the same action type filter
-- models/intermediate/int__meta_ads__campaign_performance.sql
SELECT
  date_start AS date_day,
  campaign_id,
  campaign_name,
  impressions,
  clicks,
  spend,
  -- Conversions: define what counts as a conversion for this business
  COALESCE(purchases, 0) + COALESCE(leads, 0) AS conversions,
  COALESCE(purchase_value, 0) AS conversions_value
FROM {{ ref('int__meta_ads__actions_pivoted') }}

The choice of which action types roll into conversions is business-specific and should be documented in the model’s description and ideally in a dbt doc block. The next person who reads this model needs to understand why leads and purchases are combined but post_engagement is not.

Storing Raw Before Transforming

One operational practice worth building in from the start: store the raw actions JSON before transformation. BigQuery allows storing JSON as a STRING column, or you can use the native JSON type (available in BigQuery as of 2023).

-- Keep the raw array as a safety net
SELECT
  ad_id,
  date_start,
  actions_json,  -- the raw array, untransformed
  action_values_json,
  -- ... pivoted columns
FROM ...

When Meta introduces a new action type you haven’t pivoted, you can query the raw column to investigate without re-pulling from the API. When your dbt variable list is incomplete for a historical period, the raw column lets you backfill without an API call. Raw-before-transform is the standard practice for any API source where the schema evolves.

What Changes Over Time

The action type namespace is not stable. Meta introduces new types with product launches — Live Shopping events, Advantage+ catalog events, new video interaction types — and deprecates old ones occasionally. The actions array will sometimes contain types you haven’t mapped.

The best defense is logging unmapped action types in your transformation layer. If an action type appears in the raw data but doesn’t match any key in your variable list, surface that somewhere — a dbt singular test, a monitoring query, a Slack alert. Unmapped action types mean you’re leaving conversion data on the table.

-- tests/assert_no_unmapped_meta_action_types.sql
-- Fails if the raw data contains action types not in our configured list
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

Run this test weekly. When it flags new types, decide whether to add them to your variable list or document why they’re intentionally excluded.