If you want to work with the data output directly in BigQuery, getting started is straightforward. Below is an example query to analyse performance metrics from your Facebook ad campaigns, including data from image assets.
This query retrieves and summarises Facebook ad performance data, focusing on total spend, impressions, clicks, purchases, and image asset details for each campaign, starting from 1 January 2023. It groups the data by campaign name and ad set name and includes the image asset URL and related metrics:
SELECT
campaign_name,
adset_name,
SUM(spend) AS total_spend,
SUM(impressions) AS total_impressions,
SUM(clicks) AS total_clicks,
SUM(actions__purchase) AS total_purchases, image_asset.name AS image_name, image_asset.url AS image_url,
SUM(image_asset.impressions) AS image_impressions, SUM(image_asset.clicks) AS image_clicks,
SUM(image_asset.spend) AS image_spend
FROM `project.dataset.facebook_visual_creative_report_{table_suffix}`, UNNEST(image_asset) AS image_asset
WHERE date >= '2023-01-01'
GROUP BY
campaign_name,
adset_name,
image_name,
image_url
ORDER BY
campaign_name,
adset_name,
image_name
Below is the detailed schema of the output tables in BigQuery:
Table: facebook_visual_creative_report_{table_suffix}
Full name | Type | Description |
date | DATE | The date of the record. |
account_id | STRING | The ID of the account. |
account_currency | STRING | The currency used by the account. |
campaign_id | STRING | The ID of the campaign. |
adset_id | STRING | The ID of the ad set. |
ad_id | STRING | The ID of the ad. |
ad_name | STRING | The name of the ad. |
run_date | TIMESTAMP | The date and time the ad was run. |
run_id | STRING | The ID of the run. |
created_time | STRING | The time the ad was created. |
account_name | STRING | The name of the account. |
adset_name | STRING | The name of the ad set. |
campaign_name | STRING | The name of the campaign. |
buying_type | STRING | The buying type of the ad. |
objective | STRING | The objective of the campaign. |
date_start | STRING | The start date of the campaign. |
date_stop | STRING | The end date of the campaign. |
quality_ranking | STRING | The quality ranking of the ad. |
clicks | FLOAT | The number of clicks. |
unique_clicks | FLOAT | The number of unique clicks. |
spend | FLOAT | The amount spent on the ad. |
social_spend | FLOAT | The amount spent on social interactions. |
impressions | FLOAT | The number of impressions. |
reach | FLOAT | The number of people reached. |
actions | RECORD | The actions taken (repeated record). |
action_values | RECORD | The values of actions taken (repeated record). |
actions__purchase | FLOAT | The number of purchases. |
actions__purchase_value | FLOAT | The value of purchases. |
fb_pixel_add_to_cart | FLOAT | The number of add to cart actions tracked by Facebook pixel. |
fb_pixel_view_content | FLOAT | The number of view content actions tracked by Facebook pixel. |
currency_variable | FLOAT | The currency variable for conversion calculations. |
reverse_currency_variable | FLOAT | The reverse currency variable for conversion calculations. |
custom_spend | FLOAT | The custom spend in unified currency. |
spend_last_year | FLOAT | The spend from the previous year. |
actions__purchase_last_year | FLOAT | The number of purchases from the previous year. |
custom_spend_last_year | FLOAT | The custom spend from the previous year. |
actions__purchase_value_value_last_year | FLOAT | The value of purchases from the previous year. |
custom_actions__purchase_value_last_year | FLOAT | The custom value of purchases from the previous year. |
image_asset | RECORD | The performance data for image assets (repeated record). |
video_asset | RECORD | The performance data for video assets (repeated record). |
custom_currency | STRING | The custom currency used for unified currency calculations. |
conversions | FLOAT | The number of conversions. |
conversion_value | FLOAT | The value of conversions. |
custom_conversion_value | FLOAT | The custom conversion value in unified currency. |
channel_grouping | STRING | The grouping of marketing channels. |
market_name | STRING | The name of the market. |
actions.action_type | STRING | The type of action taken. |
actions.value | STRING | The value of the action taken. |
action_values.action_type | STRING | The type of action value. |
action_values.value | STRING | The value of the action. |
image_asset.name | STRING | The name of the image asset. |
image_asset.url | STRING | The URL of the image asset. |
image_asset.impressions | INTEGER | The number of impressions for the image asset. |
image_asset.spend | FLOAT | The spend for the image asset. |
image_asset.clicks | INTEGER | The number of clicks for the image asset. |
image_asset.reach | INTEGER | The reach for the image asset. |
image_asset.actions__purchase | FLOAT | The number of purchases from the image asset. |
image_asset.actions__add_to_cart | FLOAT | The number of add to cart actions from the image asset. |
image_asset.actions__view_content | FLOAT | The number of view content actions from the image asset. |
image_asset.actions__post_engagement | FLOAT | The number of post engagements from the image asset. |
image_asset.actions__post_reaction | FLOAT | The number of post reactions from the image asset. |
image_asset.actions__post | FLOAT | The number of posts from the image asset. |
image_asset.custom_spend | FLOAT | The custom spend for the image asset. |
video_asset.id | STRING | The ID of the video asset. |
video_asset.thumbnail_url | STRING | The thumbnail URL of the video asset. |
video_asset.url | STRING | The URL of the video asset. |
video_asset.video_id | STRING | The video ID of the video asset. |
video_asset.video_name | STRING | The name of the video asset. |
video_asset.impressions | INTEGER | The number of impressions for the video asset. |
video_asset.spend | FLOAT | The spend for the video asset. |
video_asset.clicks | INTEGER | The number of clicks for the video asset. |
video_asset.reach | INTEGER | The reach for the video asset. |
video_asset.actions__purchase | FLOAT | The number of purchases from the video asset. |
video_asset.actions__add_to_cart | FLOAT | The number of add to cart actions from the video asset. |
video_asset.actions__view_content | FLOAT | The number of view content actions from the video asset. |
video_asset.actions__post_engagement | FLOAT | The number of post engagements from the video asset. |
video_asset.actions__post_reaction | FLOAT | The number of post reactions from the video asset. |
video_asset.actions__post | FLOAT | The number of posts from the video asset. |
video_asset.actions__video_avg_time_watched | FLOAT | The average time watched for the video asset. |
video_asset.custom_spend | FLOAT | The custom spend for the video asset. |