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