Here’s a breakdown of GA4 Advertising Metrics and their corresponding BigQuery fields:

Points to consider

BigQuery Field Names (Formulas) provided above are conceptual and assume that you have fields in your BigQuery dataset that directly correspond to these metrics.

If these fields are not directly available, you may need to create them using custom calculations or ensure that your data collection setup in GA4 is configured to capture this data.

For example, the specific BigQuery field names provided, such as SUM(google_ads_clicks), SUM(google_ads_cost), etc., were illustrative and not directly pulled from the GA4 BigQuery export schema.

GA4’s BigQuery export does not directly provide fields named ‘google_ads_clicks‘, ‘google_ads_cost‘, ‘non_google_clicks‘, ‘non_google_cost‘, etc.

Instead, advertising metrics and costs are typically derived from the event data that GA4 exports to BigQuery, which requires parsing and aggregating specific event parameters related to advertising.

Let’s construct example SQL queries to calculate various Google Ads and non-Google advertising metrics.

These examples assume that you have linked your Google Ads account with GA4 and are utilizing BigQuery to analyze the exported data.

The metrics will be derived from custom fields or calculations based on the event data available in your GA4 BigQuery export.

Google Ads Clicks

SELECT
  SUM(metrics_clicks) AS google_ads_clicks
FROM
  `your_project.your_dataset.google_ads_table`

Google Ads Cost

SELECT
  SUM(metrics_cost_micros) / 1000000 AS google_ads_cost
FROM
  `your_project.your_dataset.google_ads_table`

Google Ads Impressions

SELECT
  SUM(metrics_impressions) AS google_ads_impressions
FROM
  `your_project.your_dataset.google_ads_table`

Google Ads Video Cost

Assuming video cost is part of the cost metrics and needs to be filtered by video ad interactions:

SELECT
  SUM(metrics_cost_micros) / 1000000 AS google_ads_video_cost
FROM
  `your_project.your_dataset.google_ads_table`
WHERE
  segments_ad_network_type = 'VIDEO'

Google Ads Video Views

Assuming video views are tracked as a specific interaction type:

SELECT
  SUM(metrics_interactions) AS google_ads_video_views
FROM
  `your_project.your_dataset.google_ads_table`
WHERE
  metrics_interaction_event_types = 'VIDEO_VIEW'

Non-Google Advertising Metrics

For non-Google advertising metrics, let’s assume you are tracking these through custom events or parameters in GA4 that are then exported to BigQuery.

The specific field names and calculations might vary based on how you’ve set up tracking for these metrics.

Non-Google Clicks

SELECT
  COUNT(*) AS non_google_clicks
FROM
  `your_project.your_dataset.events_*`
WHERE
  event_name = 'non_google_ad_click'

Non-Google Key Events

Assuming a key event is defined and tracked explicitly:

SELECT
  COUNT(*) AS non_google_key_events
FROM
  `your_project.your_dataset.events_*`
WHERE
  event_name = 'non_google_key_event'

Non-Google Impressions

SELECT
  COUNT(*) AS non_google_impressions
FROM
  `your_project.your_dataset.events_*`
WHERE
  event_name = 'non_google_ad_impression'

Non-Google Revenue

Assuming revenue from non-Google ads is tracked as part of event parameters:

SELECT
  SUM(event_params.value.double_value) AS non_google_revenue
FROM
  `your_project.your_dataset.events_*`,
  UNNEST(event_params) AS event_params
WHERE
  event_name = 'purchase' AND
  event_params.key = 'non_google_ad_revenue'

Non-Google Cost

Assuming the cost for non-Google ads is tracked similarly:

SELECT
  SUM(event_params.value.double_value) AS non_google_cost
FROM
  `your_project.your_dataset.events_*`,
  UNNEST(event_params) AS event_params
WHERE
  event_params.key = 'non_google_ad_cost'

Note: The specific calculations and field names might need adjustments based on your data schema and how you have configured tracking for Google Ads and non-Google advertising metrics.