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 retrieves and summarizes data by market and channel, offering insights into the revenue generated, transactions completed, and total spend for each marketing platform. It groups the data by market, channel, and hour to provide a clear summary of performance across different regions and marketing channels throughout the day.
SELECT
date,
hour,
market,
channel,
SUM(revenue) AS total_revenue,
SUM(transactions) AS total_transactions,
SUM(platform_transactions) AS total_platform_transactions, SUM(platform_revenue) AS total_platform_revenue,
SUM(total_spend) AS total_spend,
AVG(total_spend / NULLIF(SUM(revenue), 0)) AS avg_spend_to_revenue_ratio,
SUM(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}
Full 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. |
revenue | FLOAT | The total revenue generated during the hour, as reported by 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 (gads), Meta (Facebook/Instagram), etc. |
platform_transactions | FLOAT | The number of transactions attributed to specific platforms such as Google Ads (gads), 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_revenue | FLOAT | The total revenue generated during the same hour in the previous year, as reported by GA4. |
ly_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. |
ga4_migration_date | STRING | The date on which the system migrated to GA4, used for tracking and comparison purposes. |
custom_currency | STRING | The currency in which the revenue and spend amounts are recorded, allowing for customization based on market or client preferences. |
rn | INTEGER | The record number, used for indexing or order identification within the dataset. |