Creating the Google Ads Metrics Table

In this guide, we’ll walk through how to create a BigQuery table to store hourly Google Ads metrics data. This structure helps you analyze ad performance at an hourly resolution and can be used for dashboards, reporting, and cost analysis.

Table schema

Here’s the schema for the google_ads_metrics table. Each row represents performance metrics for a specific hour of a specific day.

Create table in BigQuery

CREATE TABLE `project-id.dataset-id.google_ads_metrics` (
  date DATE,
  hour INT64,
  timestamp_start TIMESTAMP,  -- Start of the hour (e.g. "2025-03-20T15:00:00")
  timestamp_end TIMESTAMP,    -- End of the hour (e.g. "2025-03-20T15:59:59")
  timestamp INT64,            -- Epoch timestamp in milliseconds (derived from timestamp_end)
  spend FLOAT64,
  impressions INT64,
  clicks INT64,
  ctr FLOAT64,
  conversions FLOAT64,
  conversions_value FLOAT64,
  average_cpc FLOAT64,
  cost_per_conversion FLOAT64
);

This table supports granular time-based analysis, making it easier to track performance fluctuations over the course of the day.

Field descriptions

Name
date
Description
The date of the metrics (e.g. 2025-03-20).
Name
hour
Description
The hour of the day (0–23) representing the time bucket.
Name
timestamp_start
Description
Start of the hour window (inclusive).
Name
timestamp_end
Description
End of the hour window (inclusive).
Name
timestamp
Description
Unix timestamp in milliseconds, derived from timestamp_end.
Name
spend
Description
Total ad spend in the given hour.
Name
impressions
Description
Number of times ads were shown during this hour.
Name
clicks
Description
Number of clicks recorded in this hour.
Name
ctr
Description
Click-through rate (clicks / impressions).
Name
conversions
Description
Number of conversions attributed to ads during the hour.
Name
conversions_value
Description
Monetary value of the conversions.
Name
average_cpc
Description
Average cost per click.
Name
cost_per_conversion
Description
Cost per conversion.

Example row

{
  "date": "2025-03-20",
  "hour": 15,
  "timestamp_start": "2025-03-20T15:00:00Z",
  "timestamp_end": "2025-03-20T15:59:59Z",
  "timestamp": 1742486399000,
  "spend": 12.34,
  "impressions": 1520,
  "clicks": 89,
  "ctr": 0.0586,
  "conversions": 7,
  "conversions_value": 125.00,
  "average_cpc": 0.14,
  "cost_per_conversion": 1.76
}

When to use this table

This schema is ideal if you want to:

Monitor hourly ad performance and detect trends.
Build visualizations based on time series data.
Aggregate data at daily or weekly intervals without losing granularity.
Join with other time-based datasets for cross-platform analysis.