These SQL queries are useful for customers who integrate FB Insights into their workspace/project. The text in BOLD shows the table names that need to be changed according to the users’ data schema.

For the last update the tables have a common name, the only thing that change is the name of the campaign, ad or adset.

Summary Traffic Campaigns:

WITH
  base AS (
  SELECT
	* EXCEPT (actions),
	JSON_EXTRACT_ARRAY(REPLACE(actions, '"', ''),
  	'$') AS action
  FROM (
	SELECT
  	*,
  	RANK() OVER (PARTITION BY campaign_id, date_start ORDER BY execution_date_time DESC) AS rank
	FROM
  	***`Facebook_Data__campaigns_daily_insights`***
	ORDER BY
  	date_start ASC ) AS latest
  WHERE
	latest.rank = 1
  ORDER BY
	date_start),
  insights AS (
  SELECT
	*,
	JSON_EXTRACT_SCALAR(items,
  	'$.action_type') AS action_type,
	CAST(JSON_EXTRACT_SCALAR(items,
    	'$.value') AS FLOAT64 ) action_value
  FROM
	base,
	UNNEST (action) AS items),
  actions_values AS (
  SELECT
	campaign_id,
	campaign_name,
	objective,
	SUM(
	IF
  	(action_type = 'view_content',
    	action_value,
    	NULL)) AS view_content,
	SUM(
	IF
  	(action_type = 'add_to_cart',
    	action_value,
    	NULL)) AS add_to_cart,
	SUM(
	IF
  	(action_type = 'initiate_checkout',
    	action_value,
    	NULL)) AS initiate_checkout,
	SUM(
	IF
  	(action_type = 'purchase',
    	action_value,
    	NULL)) AS purchase
  FROM
	insights
  GROUP BY
	1,
	2,
	3 )
SELECT
  base.campaign_name,
  base.objective,
  ROUND(SUM(spend), 3) AS spend,
  SUM(impressions) AS impressions,
  SUM(reach) AS reach,
  SUM(inline_link_clicks) AS inline_link_clicks,
CONCAT(CAST(ROUND(SUM(SAFE_DIVIDE(inline_link_clicks,
        	impressions)*100), 3) AS STRING), '%') AS CTR,
  view_content,
  add_to_cart,
  initiate_checkout,
  purchase,
  ROUND(SUM(SAFE_DIVIDE(spend,
    	purchase)), 3) AS CPR,
  ROUND(SUM(SAFE_DIVIDE(purchase,
    	inline_link_clicks)), 3) AS result_rate
FROM
  base
LEFT JOIN
  actions_values
ON
  base.campaign_id = actions_values.campaign_id
WHERE
  base.objective = 'LINK_CLICKS'
  AND EXTRACT(MONTH
  FROM
	date_start) = EXTRACT(MONTH
  FROM
	CURRENT_DATE())
GROUP BY
  base.campaign_name,
  base.objective,
  view_content,
  add_to_cart,
  initiate_checkout,
  purchase
ORDER BY
  1

Summary Conversions Campaigns:

WITH
  base AS (
  SELECT
	* EXCEPT (actions),
	JSON_EXTRACT_ARRAY(REPLACE(actions, '"', ''),
  	'$') AS action
  FROM (
	SELECT
  	*,
  	RANK() OVER (PARTITION BY campaign_id, date_start ORDER BY execution_date_time DESC) AS rank
	FROM
  	***`Facebook_Data__campaigns_daily_insights`***
	ORDER BY
  	date_start ASC ) AS latest
  WHERE
	latest.rank = 1
  ORDER BY
	date_start),
  insights AS (
  SELECT
	*,
	JSON_EXTRACT_SCALAR(items,
  	'$.action_type') AS action_type,
	CAST(JSON_EXTRACT_SCALAR(items,
    	'$.value') AS FLOAT64 ) action_value
  FROM
	base,
	UNNEST (action) AS items),
  actions_values AS (
  SELECT
	campaign_id,
	campaign_name,
	objective,
	SUM(
	IF
  	(action_type = 'view_content',
    	action_value,
    	NULL)) AS view_content,
	SUM(
	IF
  	(action_type = 'add_to_cart',
    	action_value,
    	NULL)) AS add_to_cart,
	SUM(
	IF
  	(action_type = 'initiate_checkout',
action_value,
    	NULL)) AS initiate_checkout,
	SUM(
	IF
  	(action_type = 'purchase',
    	action_value,
    	NULL)) AS purchase
  FROM
	insights
  GROUP BY
	1,
	2,
	3 )
SELECT
  base.campaign_name,
  base.objective,
  ROUND(SUM(spend), 3) AS spend,
  SUM(impressions) AS impressions,
  SUM(reach) AS reach,
  SUM(inline_link_clicks) AS inline_link_clicks,
  CONCAT(CAST(ROUND(SUM(SAFE_DIVIDE(inline_link_clicks,
        	impressions)*100), 3) AS STRING), '%') AS CTR,
  view_content,
  add_to_cart,
  initiate_checkout,
  purchase,
  ROUND(SUM(SAFE_DIVIDE(spend,
    	purchase)), 3) AS CPR,
  ROUND(SUM(SAFE_DIVIDE(purchase,
    	inline_link_clicks)), 3) AS result_rate
FROM
  base
LEFT JOIN
  actions_values
ON
  base.campaign_id = actions_values.campaign_id
WHERE
  base.objective = 'CONVERSIONS'
  AND EXTRACT(MONTH
  FROM
	date_start) = EXTRACT(MONTH
  FROM
CURRENT_DATE())
GROUP BY
  base.campaign_name,
  base.objective,
  view_content,
  add_to_cart,
  initiate_checkout,
  purchase
ORDER BY
  1

Summary Ad:

WITH
  base AS (
  SELECT
	* EXCEPT (actions),
	JSON_EXTRACT_ARRAY(REPLACE(actions, '"', ''),
  	'$') AS action
  FROM (
	SELECT
  	*,
  	RANK() OVER (PARTITION BY campaign_id, date_start ORDER BY execution_date_time DESC) AS rank
	FROM
  	***`Facebook_Data__ad_daily_insights`***
	ORDER BY
  	date_start ASC ) AS latest
  WHERE
	latest.rank = 1
  ORDER BY
	date_start),
  insights AS (
  SELECT
	*,
	JSON_EXTRACT_SCALAR(items,
  	'$.action_type') AS action_type,
	CAST(JSON_EXTRACT_SCALAR(items,
    	'$.value') AS FLOAT64 ) action_value
  FROM
	base,
	UNNEST (action) AS items),
  actions_values AS (
  SELECT
	campaign_id,
	campaign_name,
	objective,
	SUM(
	IF
  	(action_type = 'view_content',
    	action_value,
    	NULL)) AS view_content,
	SUM(
	IF
  	(action_type = 'add_to_cart',
    	action_value,
    	NULL)) AS add_to_cart,
	SUM(
	IF
  	(action_type = 'initiate_checkout',
    	action_value,
    	NULL)) AS initiate_checkout,
	SUM(
	IF
  	(action_type = 'purchase',
    	action_value,
    	NULL)) AS purchase
  FROM
	insights
  GROUP BY
	1,
	2,
	3 )
SELECT
  base.campaign_name,
  base.objective,
  base.ad_name,
  ROUND(SUM(spend), 3) AS spend,
  SUM(impressions) AS impressions,
  SUM(reach) AS reach,
  SUM(inline_link_clicks) AS inline_link_clicks,
  CONCAT(CAST(ROUND(SUM(SAFE_DIVIDE(inline_link_clicks,
impressions)*100), 3) AS STRING), '%') AS CTR,
  view_content,
  add_to_cart,
  initiate_checkout,
  purchase,
  ROUND(SUM(SAFE_DIVIDE(spend,
    	purchase)), 3) AS CPR,
  ROUND(SUM(SAFE_DIVIDE(purchase,
    	inline_link_clicks)), 3) AS result_rate
FROM
  base
LEFT JOIN
  actions_values
ON
  base.campaign_id = actions_values.campaign_id
WHERE
  EXTRACT(MONTH
  FROM
	date_start) = EXTRACT(MONTH
  FROM
	CURRENT_DATE())
GROUP BY
  base.campaign_name,
  base.objective,
  base.ad_name,
  view_content,
  add_to_cart,
  initiate_checkout,
  purchase
ORDER BY
  1

Summary Adset:

WITH
  base AS (
  SELECT
	* EXCEPT (actions),
	JSON_EXTRACT_ARRAY(REPLACE(actions, '"', ''),
  	'$') AS action
  FROM (
	SELECT
  	*,
  	RANK() OVER (PARTITION BY campaign_id, date_start ORDER BY execution_date_time DESC) AS rank
	FROM
  	***`Facebook_Data__adsets_daily_insights`***
	ORDER BY
  	date_start ASC ) AS latest
  WHERE
	latest.rank = 1
  ORDER BY
	date_start),
  insights AS (
  SELECT
	*,
	JSON_EXTRACT_SCALAR(items,
  	'$.action_type') AS action_type,
	CAST(JSON_EXTRACT_SCALAR(items,
    	'$.value') AS FLOAT64 ) action_value
  FROM
	base,
	UNNEST (action) AS items),
  actions_values AS (
  SELECT
	campaign_id,
	campaign_name,
	objective,
	SUM(
	IF
  	(action_type = 'view_content',
    	action_value,
    	NULL)) AS view_content,
	SUM(
	IF
  	(action_type = 'add_to_cart',
    	action_value,
    	NULL)) AS add_to_cart,
	SUM(
	IF
  	(action_type = 'initiate_checkout',
    	action_value,
    	NULL)) AS initiate_checkout,
	SUM(
	IF
  	(action_type = 'purchase',
    	action_value,
    	NULL)) AS purchase
  FROM
	insights
  GROUP BY
	1,
	2,
	3 )
SELECT
  base.campaign_name,
  base.objective,
  base.adset_name,
  ROUND(SUM(spend), 3) AS spend,
  SUM(impressions) AS impressions,
  SUM(reach) AS reach,
  SUM(inline_link_clicks) AS inline_link_clicks,
  CONCAT(CAST(ROUND(SUM(SAFE_DIVIDE(inline_link_clicks,
        	impressions)*100), 3) AS STRING), '%') AS CTR,
  view_content,
  add_to_cart,
  initiate_checkout,
  purchase,
  ROUND(SUM(SAFE_DIVIDE(spend,
    	purchase)), 3) AS CPR,
  ROUND(SUM(SAFE_DIVIDE(purchase,
    	inline_link_clicks)), 3) AS result_rate
FROM
  base
LEFT JOIN
  actions_values
ON
  base.campaign_id = actions_values.campaign_id
WHERE
  EXTRACT(MONTH
  FROM
	date_start) = EXTRACT(MONTH
  FROM
	CURRENT_DATE())
GROUP BY
  base.campaign_name,
  base.objective,
  base.adset_name,
  view_content,
  add_to_cart,
  initiate_checkout,
  purchase
ORDER BY
  1

Daily Behavior by Campaign - Conversions:

WITH
  base AS (
  SELECT
	* EXCEPT (actions),
	JSON_EXTRACT_ARRAY(REPLACE(actions, '"', ''),
  	'$') AS action
  FROM (
	SELECT
  	*,
  	RANK() OVER (PARTITION BY campaign_id, date_start ORDER BY execution_date_time DESC) AS rank
	FROM
  	***`Facebook_Data__campaigns_daily_insights`***
	ORDER BY
  	date_start ASC ) AS latest
  WHERE
	latest.rank = 1
  ORDER BY
	date_start),
  insights AS (
  SELECT
	*,
	JSON_EXTRACT_SCALAR(items,
  	'$.action_type') AS action_type,
	CAST(JSON_EXTRACT_SCALAR(items,
    	'$.value') AS FLOAT64 ) action_value
  FROM
	base,
	UNNEST (action) AS items),
  actions_values AS (
  SELECT
	campaign_id,
	campaign_name,
	objective,
	date_start,
	SUM(
	IF
  	(action_type = 'purchase',
    	action_value,
    	NULL)) AS purchase
  FROM
	insights
  GROUP BY
	1,
	2,
	3,
	4)
SELECT
  base.campaign_name,
  base.objective,
  base.date_start,
  base.spend,
  base.inline_link_clicks,
  actions_values.purchase,
  ROUND(SAFE_DIVIDE(base.spend,
	actions_values.purchase), 3) * 100 AS CPR
FROM
  base
LEFT JOIN
  actions_values
ON
  actions_values.campaign_name = base.campaign_name
  AND actions_values.date_start = base.date_start
  AND actions_values.objective = base.objective
WHERE
  EXTRACT(MONTH
  FROM
	base.date_start) = EXTRACT(MONTH
  FROM
	CURRENT_DATE())
  AND base.objective = 'CONVERSIONS'
  AND purchase IS NOT NULL
  AND base.campaign_name = 'campaign_name'
ORDER BY
  1,
  3

Daily Behavior by Campaign - Traffic: