Stay organized with collections
Save and categorize content based on your preferences.
MRC and GroupM viewability rates
This example shows how to query rates of metrics that have different
measurability fields.
-- Write queries as a union of all 3 tables.WITHCombinedEventsAS(SELECTviewability_metrics,campaign_idFROMadh.google_ads_impressionsUNIONALLSELECTviewability_metrics,impression_data.campaign_idFROMadh.google_ads_active_viewsUNIONALLSELECTviewability_metrics,impression_data.campaign_idFROMadh.google_ads_creative_conversions),MetricsAS(SELECTcampaign_id,SUM(viewability_metrics.mrc_viewable_impressions.measurable_count)ASmrc_measurable_impressions,SUM(viewability_metrics.mrc_viewable_impressions.viewable_count)ASmrc_viewable_impressions,SUM(viewability_metrics.groupm_viewable_impressions.measurable_count)ASgroupm_measurable_impressions,SUM(viewability_metrics.groupm_viewable_impressions.viewable_count)ASgroupm_viewable_impressionsFROMCombinedEventsGROUPBYcampaign_id)SELECTcampaign_id,SAFE_DIVIDE(mrc_viewable_impressions,mrc_measurable_impressions)ASmrc_viewability_rate,SAFE_DIVIDE(groupm_viewable_impressions,groupm_measurable_impressions)ASgroupm_viewability_rateFROMMetrics
Combine viewability metrics
This example calculates the number of impressions that were both full-screened
and backgrounded at some point during the view. This metric can only be
calculated by combining 2 existing metrics.
-- Write queries as a union of all 3 tables.WITHCombinedEventsAS(SELECTimpression_id,campaign_id,viewability_metrics,TRUEASis_impressionFROMadh.google_ads_impressionsUNIONALLSELECTimpression_id,impression_data.campaign_id,viewability_metrics,FALSEASis_impressionFROMadh.google_ads_active_viewsUNIONALLSELECTimpression_id,impression_data.campaign_id,viewability_metrics,FALSEASis_impressionFROMadh.google_ads_creative_conversions),AnnotatedImpressionsAS(SELECTcampaign_id,LOGICAL_OR(is_impression)ASis_valid_impression,SUM(viewability_metrics.active_view_plus_metrics.measurable_count) > 0ASis_av_plus_measurable,SUM(viewability_metrics.active_view_plus_metrics.background_count) > 0ASwas_backgrounded,SUM(viewability_metrics.active_view_plus_metrics.fullscreen_count) > 0ASwas_fullscreenedFROMCombinedEventsGROUPBYimpression_id,campaign_idHAVINGis_valid_impression)SELECTcampaign_id,COUNT(*)AStotal_impressions,COUNTIF(is_av_plus_measurable)ASav_plus_measurable_impressions,COUNTIF(was_backgroundedANDwas_fullscreened)ASfullscreen_and_backgrounded_impressionsFROMAnnotatedImpressionsGROUPBYcampaign_id
Compare measurable and viewable eligible impressions for different format categories
Video-measured ads
-- Write queries as a union of all 3 tables.WITHVideoImpressionsAS(SELECTimpression_idFROMadh.google_ads_impressionsWHEREformat_category='VIDEO'),CombinedEventsAS(SELECTimpression_id,campaign_id,viewability_metrics,TRUEASis_impressionFROMadh.google_ads_impressionsWHEREformat_category='VIDEO'UNIONALLSELECTIm.impression_id,Av.impression_data.campaign_id,Av.viewability_metrics,FALSEASis_impressionFROMVideoImpressionsASImINNERJOINadh.google_ads_active_viewsASAvUSING(impression_id)UNIONALLSELECTIm.impression_id,Cc.impression_data.campaign_id,Cc.viewability_metrics,FALSEASis_impressionFROMVideoImpressionsASImINNERJOINadh.google_ads_creative_conversionsASCcUSING(impression_id))SELECTcampaign_id,COUNTIF(is_impression)AStotal_impressions,SUM(viewability_metrics.mrc_viewable_impressions.measurable_count)ASmrc_measurable_impressions,SUM(viewability_metrics.mrc_viewable_impressions.viewable_count)ASmrc_viewable_impressionsFROMCombinedEventsGROUPBYcampaign_id;
Display-measured ads
-- Write queries as a union of all 3 tables.WITHDisplayImpressionsAS(SELECTimpression_idFROMadh.google_ads_impressionsWHEREformat_category='DISPLAY'),CombinedEventsAS(SELECTimpression_id,campaign_id,viewability_metrics,TRUEASis_impressionFROMadh.google_ads_impressionsWHEREformat_category='DISPLAY'UNIONALLSELECTIm.impression_id,Av.impression_data.campaign_id,Av.viewability_metrics,FALSEASis_impressionFROMDisplayImpressionsASImINNERJOINadh.google_ads_active_viewsASAvUSING(impression_id)UNIONALLSELECTIm.impression_id,Cc.impression_data.campaign_id,Cc.viewability_metrics,FALSEASis_impressionFROMDisplayImpressionsASImINNERJOINadh.google_ads_creative_conversionsASCcUSING(impression_id))SELECTcampaign_id,COUNTIF(is_impression)AStotal_impressions,SUM(viewability_metrics.mrc_viewable_impressions.measurable_count)ASmrc_measurable_impressions,SUM(viewability_metrics.mrc_viewable_impressions.viewable_count)ASmrc_viewable_impressionsFROMCombinedEventsGROUPBYcampaign_id;
Calculate commonly used metrics
This example calculates a number of commonly used metrics. Download sample code.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Missing the information I need","missingTheInformationINeed","thumb-down"],["Too complicated / too many steps","tooComplicatedTooManySteps","thumb-down"],["Out of date","outOfDate","thumb-down"],["Samples / code issue","samplesCodeIssue","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2024-09-18 UTC."],[[["\u003cp\u003eTo accurately calculate advanced Active View metrics, queries must be structured as a union of three specific tables (google_ads_impressions, google_ads_active_views, and google_ads_creative_conversions).\u003c/p\u003e\n"],["\u003cp\u003eThe provided examples demonstrate how to calculate MRC and GroupM viewability rates, identify impressions that were both fullscreened and backgrounded, and compare viewability metrics for video and display ad formats.\u003c/p\u003e\n"],["\u003cp\u003eAll queries should be written with a union of the three aforementioned tables to ensure comprehensive and accurate results when analyzing Active View metrics.\u003c/p\u003e\n"],["\u003cp\u003eSample code for calculating commonly used metrics is available for download.\u003c/p\u003e\n"]]],["To accurately compute Active View metrics, queries must combine data from `adh.google_ads_impressions`, `adh.google_ads_active_views`, and `adh.google_ads_creative_conversions` tables via a union. This allows for calculating MRC and GroupM viewability rates, determining impressions that were both fullscreened and backgrounded, and comparing measurable and viewable impressions for different ad formats. It also allows for calculating common metrics using combined data sets, such as for video or display-measured ads.\n"],null,["# Examples\n\nMRC and GroupM viewability rates\n--------------------------------\n\n| **Warning:** In order to compute advanced Active View metrics accurately, you must write your queries as a union of all 3 tables.\n\nThis example shows how to query rates of metrics that have different\nmeasurability fields. \n\n -- Write queries as a union of all 3 tables.\n\n WITH\n CombinedEvents AS (\n SELECT\n viewability_metrics,\n campaign_id\n FROM adh.google_ads_impressions\n UNION ALL\n SELECT\n viewability_metrics,\n impression_data.campaign_id\n FROM adh.google_ads_active_views\n UNION ALL\n SELECT\n viewability_metrics,\n impression_data.campaign_id\n FROM adh.google_ads_creative_conversions\n ),\n Metrics AS (\n SELECT\n campaign_id,\n SUM(viewability_metrics.mrc_viewable_impressions.measurable_count)\n AS mrc_measurable_impressions,\n SUM(viewability_metrics.mrc_viewable_impressions.viewable_count)\n AS mrc_viewable_impressions,\n SUM(viewability_metrics.groupm_viewable_impressions.measurable_count)\n AS groupm_measurable_impressions,\n SUM(viewability_metrics.groupm_viewable_impressions.viewable_count)\n AS groupm_viewable_impressions\n FROM\n CombinedEvents\n GROUP BY\n campaign_id\n )\n SELECT\n campaign_id,\n SAFE_DIVIDE(mrc_viewable_impressions, mrc_measurable_impressions)\n AS mrc_viewability_rate,\n SAFE_DIVIDE(groupm_viewable_impressions, groupm_measurable_impressions)\n AS groupm_viewability_rate\n FROM\n Metrics\n\nCombine viewability metrics\n---------------------------\n\nThis example calculates the number of impressions that were both full-screened\nand backgrounded at some point during the view. This metric can only be\ncalculated by combining 2 existing metrics. \n\n -- Write queries as a union of all 3 tables.\n\n WITH\n CombinedEvents AS (\n SELECT\n impression_id,\n campaign_id,\n viewability_metrics,\n TRUE AS is_impression\n FROM adh.google_ads_impressions\n UNION ALL\n SELECT\n impression_id,\n impression_data.campaign_id,\n viewability_metrics,\n FALSE AS is_impression\n FROM adh.google_ads_active_views\n UNION ALL\n SELECT\n impression_id,\n impression_data.campaign_id,\n viewability_metrics,\n FALSE AS is_impression\n FROM adh.google_ads_creative_conversions\n ),\n AnnotatedImpressions AS (\n SELECT\n campaign_id,\n LOGICAL_OR(is_impression) AS is_valid_impression,\n SUM(viewability_metrics.active_view_plus_metrics.measurable_count) \u003e 0\n AS is_av_plus_measurable,\n SUM(viewability_metrics.active_view_plus_metrics.background_count) \u003e 0\n AS was_backgrounded,\n SUM(viewability_metrics.active_view_plus_metrics.fullscreen_count) \u003e 0\n AS was_fullscreened\n FROM\n CombinedEvents\n GROUP BY\n impression_id,\n campaign_id\n HAVING\n is_valid_impression\n )\n SELECT\n campaign_id,\n COUNT(*) AS total_impressions,\n COUNTIF(is_av_plus_measurable) AS av_plus_measurable_impressions,\n COUNTIF(was_backgrounded AND was_fullscreened) AS fullscreen_and_backgrounded_impressions\n FROM\n AnnotatedImpressions\n GROUP BY\n campaign_id\n\nCompare measurable and viewable eligible impressions for different format categories\n------------------------------------------------------------------------------------\n\n### Video-measured ads\n\n -- Write queries as a union of all 3 tables.\n\n WITH\n VideoImpressions AS (\n SELECT impression_id\n FROM adh.google_ads_impressions\n WHERE format_category = 'VIDEO'\n ),\n CombinedEvents AS (\n SELECT\n impression_id,\n campaign_id,\n viewability_metrics,\n TRUE AS is_impression\n FROM adh.google_ads_impressions\n WHERE format_category = 'VIDEO'\n\n UNION ALL\n\n SELECT\n Im.impression_id,\n Av.impression_data.campaign_id,\n Av.viewability_metrics,\n FALSE AS is_impression\n FROM VideoImpressions AS Im\n INNER JOIN adh.google_ads_active_views AS Av\n USING (impression_id)\n\n UNION ALL\n\n SELECT\n Im.impression_id,\n Cc.impression_data.campaign_id,\n Cc.viewability_metrics,\n FALSE AS is_impression\n FROM VideoImpressions AS Im\n INNER JOIN adh.google_ads_creative_conversions AS Cc\n USING (impression_id)\n )\n SELECT\n campaign_id,\n COUNTIF(is_impression) AS total_impressions,\n SUM(viewability_metrics.mrc_viewable_impressions.measurable_count)\n AS mrc_measurable_impressions,\n SUM(viewability_metrics.mrc_viewable_impressions.viewable_count)\n AS mrc_viewable_impressions\n FROM\n CombinedEvents\n GROUP BY\n campaign_id;\n\n### Display-measured ads\n\n -- Write queries as a union of all 3 tables.\n\n WITH\n DisplayImpressions AS (\n SELECT impression_id\n FROM adh.google_ads_impressions\n WHERE format_category = 'DISPLAY'\n ),\n CombinedEvents AS (\n SELECT\n impression_id,\n campaign_id,\n viewability_metrics,\n TRUE AS is_impression\n FROM adh.google_ads_impressions\n WHERE format_category = 'DISPLAY'\n\n UNION ALL\n\n SELECT\n Im.impression_id,\n Av.impression_data.campaign_id,\n Av.viewability_metrics,\n FALSE AS is_impression\n FROM DisplayImpressions AS Im\n INNER JOIN adh.google_ads_active_views AS Av\n USING (impression_id)\n\n UNION ALL\n\n SELECT\n Im.impression_id,\n Cc.impression_data.campaign_id,\n Cc.viewability_metrics,\n FALSE AS is_impression\n FROM DisplayImpressions AS Im\n INNER JOIN adh.google_ads_creative_conversions AS Cc\n USING (impression_id)\n )\n SELECT\n campaign_id,\n COUNTIF(is_impression) AS total_impressions,\n SUM(viewability_metrics.mrc_viewable_impressions.measurable_count)\n AS mrc_measurable_impressions,\n SUM(viewability_metrics.mrc_viewable_impressions.viewable_count)\n AS mrc_viewable_impressions\n FROM\n CombinedEvents\n GROUP BY\n campaign_id;\n\nCalculate commonly used metrics\n-------------------------------\n\nThis example calculates a number of commonly used metrics. [Download sample code](/static/ads-data-hub/samples/sample-files/advanced_av_metrics_sample.sql)."]]