# Examples

## MRC and GroupM viewability rates

> [!WARNING]
> **Warning:** In order to compute advanced Active View metrics accurately, you must write your queries as a union of all 3 tables.

This example shows how to query rates of metrics that have different
measurability fields.

    -- Write queries as a union of all 3 tables.

    WITH
      CombinedEvents AS (
        SELECT
          viewability_metrics,
          campaign_id
        FROM adh.google_ads_impressions
        UNION ALL
        SELECT
          viewability_metrics,
          impression_data.campaign_id
        FROM adh.google_ads_active_views
        UNION ALL
        SELECT
          viewability_metrics,
          impression_data.campaign_id
        FROM adh.google_ads_creative_conversions
      ),
      Metrics AS (
        SELECT
          campaign_id,
          SUM(viewability_metrics.mrc_viewable_impressions.measurable_count)
            AS mrc_measurable_impressions,
          SUM(viewability_metrics.mrc_viewable_impressions.viewable_count)
            AS mrc_viewable_impressions,
          SUM(viewability_metrics.groupm_viewable_impressions.measurable_count)
            AS groupm_measurable_impressions,
          SUM(viewability_metrics.groupm_viewable_impressions.viewable_count)
            AS groupm_viewable_impressions
        FROM
          CombinedEvents
        GROUP BY
          campaign_id
      )
    SELECT
      campaign_id,
      SAFE_DIVIDE(mrc_viewable_impressions, mrc_measurable_impressions)
        AS mrc_viewability_rate,
      SAFE_DIVIDE(groupm_viewable_impressions, groupm_measurable_impressions)
        AS groupm_viewability_rate
    FROM
      Metrics

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

    WITH
      CombinedEvents AS (
        SELECT
          impression_id,
          campaign_id,
          viewability_metrics,
          TRUE AS is_impression
        FROM adh.google_ads_impressions
        UNION ALL
        SELECT
          impression_id,
          impression_data.campaign_id,
          viewability_metrics,
          FALSE AS is_impression
        FROM adh.google_ads_active_views
        UNION ALL
        SELECT
          impression_id,
          impression_data.campaign_id,
          viewability_metrics,
          FALSE AS is_impression
        FROM adh.google_ads_creative_conversions
      ),
      AnnotatedImpressions AS (
        SELECT
          campaign_id,
          LOGICAL_OR(is_impression) AS is_valid_impression,
          SUM(viewability_metrics.active_view_plus_metrics.measurable_count) > 0
            AS is_av_plus_measurable,
          SUM(viewability_metrics.active_view_plus_metrics.background_count) > 0
            AS was_backgrounded,
          SUM(viewability_metrics.active_view_plus_metrics.fullscreen_count) > 0
            AS was_fullscreened
        FROM
          CombinedEvents
        GROUP BY
          impression_id,
          campaign_id
        HAVING
          is_valid_impression
      )
    SELECT
      campaign_id,
      COUNT(*) AS total_impressions,
      COUNTIF(is_av_plus_measurable) AS av_plus_measurable_impressions,
      COUNTIF(was_backgrounded AND was_fullscreened) AS fullscreen_and_backgrounded_impressions
    FROM
      AnnotatedImpressions
    GROUP BY
      campaign_id

## Compare measurable and viewable eligible impressions for different format categories

### Video-measured ads

    -- Write queries as a union of all 3 tables.

    WITH
      VideoImpressions AS (
        SELECT impression_id
        FROM adh.google_ads_impressions
        WHERE format_category = 'VIDEO'
      ),
      CombinedEvents AS (
        SELECT
          impression_id,
          campaign_id,
          viewability_metrics,
          TRUE AS is_impression
        FROM adh.google_ads_impressions
        WHERE format_category = 'VIDEO'

        UNION ALL

        SELECT
          Im.impression_id,
          Av.impression_data.campaign_id,
          Av.viewability_metrics,
          FALSE AS is_impression
        FROM VideoImpressions AS Im
        INNER JOIN adh.google_ads_active_views AS Av
        USING (impression_id)

        UNION ALL

        SELECT
          Im.impression_id,
          Cc.impression_data.campaign_id,
          Cc.viewability_metrics,
          FALSE AS is_impression
        FROM VideoImpressions AS Im
        INNER JOIN adh.google_ads_creative_conversions AS Cc
        USING (impression_id)
      )
    SELECT
      campaign_id,
      COUNTIF(is_impression) AS total_impressions,
      SUM(viewability_metrics.mrc_viewable_impressions.measurable_count)
        AS mrc_measurable_impressions,
      SUM(viewability_metrics.mrc_viewable_impressions.viewable_count)
        AS mrc_viewable_impressions
    FROM
      CombinedEvents
    GROUP BY
      campaign_id;

### Display-measured ads

    -- Write queries as a union of all 3 tables.

    WITH
      DisplayImpressions AS (
        SELECT impression_id
        FROM adh.google_ads_impressions
        WHERE format_category = 'DISPLAY'
      ),
      CombinedEvents AS (
        SELECT
          impression_id,
          campaign_id,
          viewability_metrics,
          TRUE AS is_impression
        FROM adh.google_ads_impressions
        WHERE format_category = 'DISPLAY'

        UNION ALL

        SELECT
          Im.impression_id,
          Av.impression_data.campaign_id,
          Av.viewability_metrics,
          FALSE AS is_impression
        FROM DisplayImpressions AS Im
        INNER JOIN adh.google_ads_active_views AS Av
        USING (impression_id)

        UNION ALL

        SELECT
          Im.impression_id,
          Cc.impression_data.campaign_id,
          Cc.viewability_metrics,
          FALSE AS is_impression
        FROM DisplayImpressions AS Im
        INNER JOIN adh.google_ads_creative_conversions AS Cc
        USING (impression_id)
      )
    SELECT
      campaign_id,
      COUNTIF(is_impression) AS total_impressions,
      SUM(viewability_metrics.mrc_viewable_impressions.measurable_count)
        AS mrc_measurable_impressions,
      SUM(viewability_metrics.mrc_viewable_impressions.viewable_count)
        AS mrc_viewable_impressions
    FROM
      CombinedEvents
    GROUP BY
      campaign_id;

## Calculate commonly used metrics

This example calculates a number of commonly used metrics. [Download sample code](https://developers.google.com/static/ads-data-hub/samples/sample-files/advanced_av_metrics_sample.sql).