This query aggregates multiple metrics for each adset.
select
i.account_name,
i.campaign_name,
i.adset_name,
sum(i.spend) as total_spend,
sum(i.impressions) as total_impressions,
sum(i.reach) as total_reach,
sum(case when i.quality_ranking='BELOW_AVERAGE_10' then ia.action_value else null end) as quality_below_average_10_value
,sum(case when i.quality_ranking='BELOW_AVERAGE_20' then ia.action_value else null end) as quality_below_average_20_value
,sum(case when i.quality_ranking='BELOW_AVERAGE_35' then ia.action_value else null end) as quality_below_average_35_value
,sum(case when i.quality_ranking='AVERAGE' then ia.action_value else null end) as quality_below_average_value
,sum(case when i.quality_ranking='ABOVE_AVERAGE' then ia.action_value else null end) as quality_above_average_value
,sum(case when i.quality_ranking='UNKNOWN' then ia.action_value else null end) as quality_unknown_value
,sum(case when i.engagement_rate_ranking='BELOW_AVERAGE_10' then ia.action_value else null end) as engagement_rate_below_average_10_value
,sum(case when i.engagement_rate_ranking='BELOW_AVERAGE_20' then ia.action_value else null end) as engagement_rate_below_average_20_value
,sum(case when i.engagement_rate_ranking='BELOW_AVERAGE_35' then ia.action_value else null end) as engagement_rate_below_average_35_value
,sum(case when i.engagement_rate_ranking='AVERAGE' then ia.action_value else null end) as engagement_rate_below_average_value
,sum(case when i.engagement_rate_ranking='ABOVE_AVERAGE' then ia.action_value else null end) as engagement_rate_above_average_value
,sum(case when i.engagement_rate_ranking='UNKNOWN' then ia.action_value else null end) as engagement_rate_unknown_value
,sum(case when i.conversion_rate_ranking='BELOW_AVERAGE_10' then ia.action_value else null end) as conversion_rate_below_average_10_value
,sum(case when i.conversion_rate_ranking='BELOW_AVERAGE_20' then ia.action_value else null end) as conversion_rate_below_average_20_value
,sum(case when i.conversion_rate_ranking='BELOW_AVERAGE_35' then ia.action_value else null end) as conversion_rate_below_average_35_value
,sum(case when i.conversion_rate_ranking='AVERAGE' then ia.action_value else null end) as conversion_rate_below_average_value
,sum(case when i.conversion_rate_ranking='ABOVE_AVERAGE' then ia.action_value else null end) as conversion_rate_above_average_value
,sum(case when i.conversion_rate_ranking='UNKNOWN' then ia.action_value else null end) as conversion_rate_unknown_value
from
fb_ads_insights i
left join fb_ads_insights_actions ia on i.insights_id = ia.insights_id
group by 1, 2, 3;
Column | Description |
---|---|
account_name |
Ad account name. |
campaign_name |
The name of the campaign . |
adset_name |
The name of the adset. |
total_spend |
Total spend for adset. |
total_impressions |
Total impressions for adset. |
total_reach |
Total reach of adset. |
quality_below_average_10_value |
Total value for quality below average 10. |
quality_below_average_20_value |
Total value for quality below average 20. |
quality_below_average_35_value |
Total value for quality below average 35. |
quality_below_average_value |
Total value for quality below average. |
quality_above_average_value |
Total value for quality above average. |
quality_unknown_value |
Total value for quality unknown. |
engagement_rate_below_average_10_value |
Total value for engagement rate below average 10. |
engagement_rate_below_average_20_value |
Total value for engagement rate below average 20. |
engagement_rate_below_average_35_value |
Total value for engagement rate below average 35. |
engagement_rate_below_average_value |
Total value for engagement rate below average. |
engagement_rate_above_average_value |
Total value for engagement rate above average. |
engagement_rate_unknown_value |
Total value for engagement rate unknown. |
conversion_rate_below_average_10_value |
Total value for conversion rate below average 10. |
conversion_rate_below_average_20_value |
Total value for conversion rate below average 20. |
conversion_rate_below_average_35_value |
Total value for conversion rate below average 35. |
conversion_rate_below_average_value |
Total value for conversion rate below average. |
conversion_rate_above_average_value |
Total value for conversion rate above average. |
conversion_rate_unknown_value |
Total value for conversion rate unknown. |