BigQuery tables for the Black Friday report | Learning Center

If you want to work directly with the data output in BigQuery, getting started is straightforward. Here’s an example of a query you can use to analyze performance metrics from your Black Friday hourly report.

This query aggregates data by date, hour, market, and channel to provide an hourly breakdown of campaign performance. It summarises total spend, compares platform and GA4 revenue and transactions, and evaluates spend efficiency and budget pacing across markets throughout the day.

SELECT
  date,
  hour,
  market,
  channel,
  SUM(ga4_revenue) AS total_revenue,
  SUM(ga4_transactions) AS total_transactions,
  SUM(platform_transactions) AS total_platform_transactions,
  SUM(platform_revenue) AS total_platform_revenue,
  SUM(total_spend) AS total_spend,
  -- Ratio of spend to GA4 revenue (safe against division by zero)
  SAFE_DIVIDE(SUM(total_spend), SUM(ga4_revenue)) AS spend_to_revenue_ratio,
  SUM(hourly_budget) AS total_budget
FROM `your_project.your_dataset.black_friday_report_{table_suffix}`
GROUP BY
  date,
  hour,
  market,
  channel
ORDER BY
  date,
  hour,
  market,
  channel;

Below is the detailed schema of the output tables in BigQuery:

Table: black_friday_report_{table_suffix}

Field name	Type	Description
date	DATE	The date of the record.
hour	INTEGER	The hour of the day (0-23) when the data was recorded.
market	STRING	The market in which the campaign is run, such as geographic regions or specific countries.
channel	STRING	The marketing channel used, such as paid search, organic search, email, or social media.
total_spend	FLOAT	The total amount of money spent on all marketing efforts during the hour.
ga4_revenue	FLOAT	The total revenue generated during the hour, as reported by GA4.
ga4_transactions	FLOAT	The total number of transactions completed during the hour, as reported by GA4.
platform_revenue	FLOAT	The revenue generated specifically from transactions on particular platforms such as Google Ads, Meta, etc.
platform_transactions	FLOAT	The number of transactions attributed to specific platforms such as Google Ads, Meta, etc.
clicks	FLOAT	The total number of clicks recorded across all channels and platforms during the hour.
impressions	FLOAT	The total number of impressions recorded across all channels and platforms during the hour.
ly_total_spend	FLOAT	The total amount of money spent during the same hour in the previous year.
ly_ga4_revenue	FLOAT	The total revenue generated during the same hour in the previous year, as reported by GA4.
ly_ga4_transactions	FLOAT	The total number of transactions completed during the same hour in the previous year, as reported by GA4.
ly_platform_revenue	FLOAT	The revenue generated from transactions on specific platforms during the same hour in the previous year.
ly_platform_transactions	FLOAT	The number of transactions attributed to specific platforms during the same hour in the previous year.
ly_impressions	FLOAT	The total number of impressions recorded during the same hour in the previous year across all channels and platforms.
ly_clicks	FLOAT	The total number of clicks recorded during the same hour in the previous year across all channels and platforms.
hourly_budget	FLOAT	Planned ad spend for the specified date, hour, market and channel.
hourly_target_conversion_value	FLOAT	Planned conversion value (revenue) for the specified hour.
hourly_target_conversions	FLOAT	Planned number of conversions or transactions for the specified hour.
hourly_target_clicks	FLOAT	Planned number of clicks for the specified hour.
custom_currency	STRING	The currency in which the revenue and spend amounts are recorded, allowing for customisation based on market or client preferences.
rn	INTEGER	The record number, used for indexing or order identification within the dataset.