If you want to work with the data output directly in BigQuery, getting started is straightforward. Below is an example query to analyse advertising performance by channel. This query retrieves and summarises data from 1 January 2023, focusing on total ad cost, transactions, and transaction revenue. It also includes 30-day and 90-day trends for ad cost and transaction revenue. The data is grouped by date, platform, custom channel grouping, and market channel grouping. This approach provides a clear summary of your advertising performance metrics across different channels and time periods:
SELECT
date,
platform,
custom_channel_grouping,
market_channel_grouping,
SUM(ad_cost) AS total_ad_cost,
SUM(transactions) AS total_transactions,
SUM(transaction_revenue) AS total_transaction_revenue,
AVG(trend_data.ad_cost_trend_30_days_latest.value) AS avg_ad_cost_30_days_trend, AVG(trend_data.transaction_revenue_trend_30_days_latest.value) AS avg_transaction_revenue_30_days_trend, AVG(trend_data.ad_cost_trend_90_days_latest.value) AS avg_ad_cost_90_days_trend, AVG(trend_data.transaction_revenue_trend_90_days_latest.value) AS avg_transaction_revenue_90_days_trend
FROM `project.dataset.channel_grouping_performance_report_v2_{table_suffix}` WHERE date >= '2023-01-01' AND is_after_ga4_migration = TRUE
GROUP BY
date,
platform,
custom_channel_grouping,
market_channel_grouping
ORDER BY
date,
platform;
Below is the detailed schema of the output tables in BigQuery:
Table 1: channel_grouping_performance_report_v2_{table_suffix}
Full name | Type | Description |
client_name | STRING | The name of the client. |
is_before_ga4_migration | BOOLEAN | Indicates whether the data is from before the GA4 migration. |
is_after_ga4_migration | BOOLEAN | Indicates whether the data is from after the GA4 migration. |
is_ga4_data | BOOLEAN | Indicates whether the data is from GA4. |
is_combined_data | BOOLEAN | Indicates whether the data is combined from multiple sources. |
date | DATE | The date of the record. |
custom_channel_grouping | STRING | Custom grouping of marketing channels. |
market_channel_grouping | STRING | Market-specific grouping of marketing channels. |
transaction_revenue | FLOAT | The revenue generated from transactions. |
transactions | FLOAT | The number of transactions recorded. |
sessions | FLOAT | The number of sessions recorded. |
engaged_sessions | FLOAT | The number of engaged sessions recorded. |
impressions | FLOAT | The total number of impressions. |
bing_impressions | FLOAT | The number of impressions from Bing. |
dv360_impressions | FLOAT | The number of impressions from DV360. |
gads_impressions | FLOAT | The number of impressions from Google Ads. |
facebook_impressions | FLOAT | The number of impressions from Facebook. |
ad_clicks | FLOAT | The total number of ad clicks. |
bing_ad_clicks | FLOAT | The number of ad clicks from Bing. |
dv360_ad_clicks | FLOAT | The number of ad clicks from DV360. |
gads_ad_clicks | FLOAT | The number of ad clicks from Google Ads. |
facebook_ad_clicks | FLOAT | The number of ad clicks from Facebook. |
ad_cost | FLOAT | The total cost of ads. |
bing_ad_cost | FLOAT | The cost of ads from Bing. |
dv360_ad_cost | FLOAT | The cost of ads from DV360. |
gads_ad_cost | FLOAT | The cost of ads from Google Ads. |
facebook_ad_cost | FLOAT | The cost of ads from Facebook. |
conversions | FLOAT | The total number of conversions. |
bing_conversions | FLOAT | The number of conversions from Bing. |
dv360_conversions | FLOAT | The number of conversions from DV360. |
gads_conversions | FLOAT | The number of conversions from Google Ads. |
facebook_conversions | FLOAT | The number of conversions from Facebook. |
conversion_value | FLOAT | The total value of conversions. |
bing_conversion_value | FLOAT | The value of conversions from Bing. |
dv360_conversion_value | FLOAT | The value of conversions from DV360. |
gads_conversion_value | FLOAT | The value of conversions from Google Ads. |
facebook_conversion_value | FLOAT | The value of conversions from Facebook. |
date_shifted_data_1_year | RECORD | Data shifted by one year for comparison. |
ga4_migration_date | STRING | The date of GA4 migration. |
target_currency | STRING | The target currency for reporting. |
trend_data | RECORD | Data showing trends over different periods. |
date_shifted_data_1_year.client_name | STRING | The name of the client for the date-shifted data. |
date_shifted_data_1_year.is_before_ga4_migration | BOOLEAN | Indicates whether the date-shifted data is from before GA4 migration. |
date_shifted_data_1_year.is_after_ga4_migration | BOOLEAN | Indicates whether the date-shifted data is from after GA4 migration. |
date_shifted_data_1_year.is_ga4_data | BOOLEAN | Indicates whether the date-shifted data is from GA4. |
date_shifted_data_1_year.is_combined_data | BOOLEAN | Indicates whether the date-shifted data is combined from multiple sources. |
date_shifted_data_1_year.custom_channel_grouping | STRING | Custom channel grouping for the date-shifted data. |
date_shifted_data_1_year.market_channel_grouping | STRING | Market-specific channel grouping for the date-shifted data. |
date_shifted_data_1_year.transaction_revenue | FLOAT | Transaction revenue for the date-shifted data. |
date_shifted_data_1_year.transactions | FLOAT | Transactions for the date-shifted data. |
date_shifted_data_1_year.sessions | FLOAT | Sessions for the date-shifted data. |
date_shifted_data_1_year.engaged_sessions | FLOAT | Engaged sessions for the date-shifted data. |
date_shifted_data_1_year.impressions | FLOAT | Impressions for the date-shifted data. |
date_shifted_data_1_year.bing_impressions | FLOAT | Bing impressions for the date-shifted data. |
date_shifted_data_1_year.dv360_impressions | FLOAT | DV360 impressions for the date-shifted data. |
date_shifted_data_1_year.gads_impressions | FLOAT | Google Ads impressions for the date-shifted data. |
date_shifted_data_1_year.facebook_impressions | FLOAT | Facebook impressions for the date-shifted data. |
date_shifted_data_1_year.ad_clicks | FLOAT | Ad clicks for the date-shifted data. |
date_shifted_data_1_year.bing_ad_clicks | FLOAT | Bing ad clicks for the date-shifted data. |
date_shifted_data_1_year.dv360_ad_clicks | FLOAT | DV360 ad clicks for the date-shifted data. |
date_shifted_data_1_year.gads_ad_clicks | FLOAT | Google Ads ad clicks for the date-shifted data. |
date_shifted_data_1_year.facebook_ad_clicks | FLOAT | Facebook ad clicks for the date-shifted data. |
date_shifted_data_1_year.ad_cost | FLOAT | Ad cost for the date-shifted data. |
date_shifted_data_1_year.bing_ad_cost | FLOAT | Bing ad cost for the date-shifted data. |
date_shifted_data_1_year.dv360_ad_cost | FLOAT | DV360 ad cost for the date-shifted data. |
date_shifted_data_1_year.gads_ad_cost | FLOAT | Google Ads ad cost for the date-shifted data. |
date_shifted_data_1_year.facebook_ad_cost | FLOAT | Facebook ad cost for the date-shifted data. |
date_shifted_data_1_year.conversions | FLOAT | Conversions for the date-shifted data. |
date_shifted_data_1_year.bing_conversions | FLOAT | Bing conversions for the date-shifted data. |
date_shifted_data_1_year.dv360_conversions | FLOAT | DV360 conversions for the date-shifted data. |
date_shifted_data_1_year.gads_conversions | FLOAT | Google Ads conversions for the date-shifted data. |
date_shifted_data_1_year.facebook_conversions | FLOAT | Facebook conversions for the date-shifted data. |
date_shifted_data_1_year.conversion_value | FLOAT | Conversion value for the date-shifted data. |
date_shifted_data_1_year.bing_conversion_value | FLOAT | Bing conversion value for the date-shifted data. |
date_shifted_data_1_year.dv360_conversion_value | FLOAT | DV360 conversion value for the date-shifted data. |
date_shifted_data_1_year.gads_conversion_value | FLOAT | Google Ads conversion value for the date-shifted data. |
date_shifted_data_1_year.facebook_conversion_value | FLOAT | Facebook conversion value for the date-shifted data. |
date_shifted_data_1_year.date | DATE | Date for the date-shifted data. |
trend_data.ad_cost_trend_7_days_latest | RECORD | Data showing ad cost trend for the latest 7 days. |
trend_data.ad_cost_trend_7_days_latest.value | FLOAT | Value of ad cost trend for the latest 7 days. |
trend_data.ad_cost_trend_7_days_latest.counter | INTEGER | Counter for ad cost trend for the latest 7 days. |
trend_data.ad_cost_trend_7_days_orig | RECORD | Data showing ad cost trend for the original 7 days. |
trend_data.ad_cost_trend_7_days_orig.value | FLOAT | Value of ad cost trend for the original 7 days. |
trend_data.ad_cost_trend_7_days_orig.counter | INTEGER | Counter for ad cost trend for the original 7 days. |
trend_data.ad_cost_trend_7_days_latest_last_year | RECORD | Data showing ad cost trend for the latest 7 days last year. |
trend_data.ad_cost_trend_7_days_latest_last_year.value | FLOAT | Value of ad cost trend for the latest 7 days last year. |
trend_data.ad_cost_trend_7_days_latest_last_year.counter | INTEGER | Counter for ad cost trend for the latest 7 days last year. |
trend_data.transaction_revenue_trend_7_days_latest | RECORD | Data showing transaction revenue trend for the latest 7 days. |
trend_data.transaction_revenue_trend_7_days_latest.value | FLOAT | Value of transaction revenue trend for the latest 7 days. |
trend_data.transaction_revenue_trend_7_days_latest.counter | INTEGER | Counter for transaction revenue trend for the latest 7 days. |
trend_data.transaction_revenue_trend_7_days_latest_last_year | RECORD | Data showing transaction revenue trend for the latest 7 days last year. |
trend_data.transaction_revenue_trend_7_days_latest_last_year.value | FLOAT | Value of transaction revenue trend for the latest 7 days last year. |
trend_data.transaction_revenue_trend_7_days_latest_last_year.counter | INTEGER | Counter for transaction revenue trend for the latest 7 days last year. |
trend_data.transaction_revenue_trend_7_days_orig | RECORD | Data showing transaction revenue trend for the original 7 days. |
trend_data.transaction_revenue_trend_7_days_orig.value | FLOAT | Value of transaction revenue trend for the original 7 days. |
trend_data.transaction_revenue_trend_7_days_orig.counter | INTEGER | Counter for transaction revenue trend for the original 7 days. |
trend_data.ad_cost_trend_30_days_latest | RECORD | Data showing ad cost trend for the latest 30 days. |
trend_data.ad_cost_trend_30_days_latest.value | FLOAT | Value of ad cost trend for the latest 30 days. |
trend_data.ad_cost_trend_30_days_latest.counter | INTEGER | Counter for ad cost trend for the latest 30 days. |
trend_data.ad_cost_trend_30_days_orig | RECORD | Data showing ad cost trend for the original 30 days. |
trend_data.ad_cost_trend_30_days_orig.value | FLOAT | Value of ad cost trend for the original 30 days. |
trend_data.ad_cost_trend_30_days_orig.counter | INTEGER | Counter for ad cost trend for the original 30 days. |
trend_data.ad_cost_trend_30_days_latest_last_year | RECORD | Data showing ad cost trend for the latest 30 days last year. |
trend_data.ad_cost_trend_30_days_latest_last_year.value | FLOAT | Value of ad cost trend for the latest 30 days last year. |
trend_data.ad_cost_trend_30_days_latest_last_year.counter | INTEGER | Counter for ad cost trend for the latest 30 days last year. |
trend_data.transaction_revenue_trend_30_days_latest | RECORD | Data showing transaction revenue trend for the latest 30 days. |
trend_data.transaction_revenue_trend_30_days_latest.value | FLOAT | Value of transaction revenue trend for the latest 30 days. |
trend_data.transaction_revenue_trend_30_days_latest.counter | INTEGER | Counter for transaction revenue trend for the latest 30 days. |
trend_data.transaction_revenue_trend_30_days_latest_last_year | RECORD | Data showing transaction revenue trend for the latest 30 days last year. |
trend_data.transaction_revenue_trend_30_days_latest_last_year.value | FLOAT | Value of transaction revenue trend for the latest 30 days last year. |
trend_data.transaction_revenue_trend_30_days_latest_last_year.counter | INTEGER | Counter for transaction revenue trend for the latest 30 days last year. |
trend_data.transaction_revenue_trend_30_days_orig | RECORD | Data showing transaction revenue trend for the original 30 days. |
trend_data.transaction_revenue_trend_30_days_orig.value | FLOAT | Value of transaction revenue trend for the original 30 days. |
trend_data.transaction_revenue_trend_30_days_orig.counter | INTEGER | Counter for transaction revenue trend for the original 30 days. |
trend_data.ad_cost_trend_90_days_latest | RECORD | Data showing ad cost trend for the latest 90 days. |
trend_data.ad_cost_trend_90_days_latest.value | FLOAT | Value of ad cost trend for the latest 90 days. |
trend_data.ad_cost_trend_90_days_latest.counter | INTEGER | Counter for ad cost trend for the latest 90 days. |
trend_data.ad_cost_trend_90_days_orig | RECORD | Data showing ad cost trend for the original 90 days. |
trend_data.ad_cost_trend_90_days_orig.value | FLOAT | Value of ad cost trend for the original 90 days. |
trend_data.ad_cost_trend_90_days_orig.counter | INTEGER | Counter for ad cost trend for the original 90 days. |
trend_data.ad_cost_trend_90_days_latest_last_year | RECORD | Data showing ad cost trend for the latest 90 days last year. |
trend_data.ad_cost_trend_90_days_latest_last_year.value | FLOAT | Value of ad cost trend for the latest 90 days last year. |
trend_data.ad_cost_trend_90_days_latest_last_year.counter | INTEGER | Counter for ad cost trend for the latest 90 days last year. |
trend_data.transaction_revenue_trend_90_days_latest | RECORD | Data showing transaction revenue trend for the latest 90 days. |
trend_data.transaction_revenue_trend_90_days_latest.value | FLOAT | Value of transaction revenue trend for the latest 90 days. |
trend_data.transaction_revenue_trend_90_days_latest.counter | INTEGER | Counter for transaction revenue trend for the latest 90 days. |
trend_data.transaction_revenue_trend_90_days_latest_last_year | RECORD | Data showing transaction revenue trend for the latest 90 days last year. |
trend_data.transaction_revenue_trend_90_days_latest_last_year.value | FLOAT | Value of transaction revenue trend for the latest 90 days last year. |
trend_data.transaction_revenue_trend_90_days_latest_last_year.counter | INTEGER | Counter for transaction revenue trend for the latest 90 days last year. |
trend_data.transaction_revenue_trend_90_days_orig | RECORD | Data showing transaction revenue trend for the original 90 days. |
trend_data.transaction_revenue_trend_90_days_orig.value | FLOAT | Value of transaction revenue trend for the original 90 days. |
trend_data.transaction_revenue_trend_90_days_orig.counter | INTEGER | Counter for transaction revenue trend for the original 90 days. |
Table 2: channel_grouping_performance_report_v2_ga_union_{table_suffix}
Full name | Type | Description |
ga4_migration_date | STRING | The date on which the system migrated from Universal Analytics to GA4, used to distinguish between UA and GA4 data. |
is_before_ga4_migration | BOOLEAN | Indicates whether the data was collected before the GA4 migration date. |
is_after_ga4_migration | BOOLEAN | Indicates whether the data was collected after the GA4 migration date. |
is_ga4_data | BOOLEAN | Indicates whether the data source is GA4 (as opposed to Universal Analytics). |
view_or_property_id | STRING | The identifier for the view or property from which the data was collected, representing either a UA view or a GA4 property. |
date | DATE | The date of the record. |
source_currency | STRING | The currency code used in the original data source (e.g., USD, EUR). |
target_currency | STRING | The currency code to which financial metrics are converted for reporting consistency. |
custom_channel_grouping | STRING | A custom grouping of channels, defined specifically for your analysis, such as "Paid Search," "Organic Search," or "Social Media." |
market_channel_grouping | STRING | The standard grouping of channels based on market definitions, often used for cross-market comparisons. |
hostname | STRING | The hostname where the data was collected, typically representing the domain of the website or app. |
source | STRING | The source of the traffic, such as a search engine (e.g., Google), a social media platform, or a direct link. |
medium | STRING | The medium through which the traffic was acquired, such as organic search, paid search, email, or referral. |
source_medium | STRING | A combined field representing both the source and the medium of the traffic (e.g., "google / organic," "facebook / paid"). |
campaign | STRING | The name of the marketing campaign that drove the traffic. |
sessions | FLOAT | The number of sessions recorded, representing user interactions with the property within a given time frame. |
engaged_sessions | FLOAT | The number of engaged sessions, where users interacted meaningfully with the property (e.g., spending a certain amount of time or visiting multiple pages). |
transactions | FLOAT | The number of transactions completed by users during their sessions. |
transaction_revenue_target_currency | FLOAT | The total revenue generated from transactions, converted to the target currency. |
transaction_revenue_source_currency | FLOAT | The total revenue generated from transactions in the original source currency. |
new_users | FLOAT | The number of new users acquired during the period, representing first-time visitors to the property. |
first_time_purchasers | FLOAT | The number of users who made their first purchase during the period, often a key metric in evaluating the success of acquisition campaigns. |
Table 3: channel_grouping_performance_report_v2_platforms_union_{table_suffix}
Full name
Full name | Type | Description |
date | DATE | The date of the record. |
platform | STRING | The platform from which the data was collected, such as Google Ads, Facebook Ads, or any other advertising platform. |
accountid | STRING | The unique identifier for the account on the respective platform. |
accountname | STRING | The name of the account associated with the platform data. |
campaign | STRING | The name of the campaign from which the data was collected. |
currency_code | STRING | The currency code used in the original data source (e.g., USD, EUR). |
target_currency | STRING | The currency code to which financial metrics are converted for reporting consistency. |
custom_channel_grouping | STRING | A custom grouping of channels, defined specifically for your analysis, such as "Paid Search," "Social Media," or "Display Ads." |
market_channel_grouping | STRING | The standard grouping of channels based on market definitions, often used for cross-market comparisons. |
impressions | FLOAT | The number of times the ad was shown to users (impressions) on the respective platform. |
ad_clicks | FLOAT | The number of clicks the ad received on the respective platform. |
ad_cost_target_currency | FLOAT | The total cost of the ads, converted to the target currency for reporting. |
ad_cost_source_currency | FLOAT | The total cost of the ads in the original source currency. |
conversions | FLOAT | The total number of conversions resulting from the ads, as recorded on the respective platform. |
conversion_value_target_currency | FLOAT | The total value of conversions, converted to the target currency. |
conversion_value_source_currency | FLOAT | The total value of conversions in the original source currency. |