This document provides reference information for the schema of Cloud Billing pricing data that's exported to each table in BigQuery.
Schema of the pricing data
In your BigQuery dataset, your Cloud Billing account
pricing data is loaded into a data table named cloud_pricing_export
.
The following information describes the schema of the Cloud Billing account pricing data that's exported to BigQuery.
Field | Type | Description |
---|---|---|
export_time |
Timestamp | A processing time associated with an append of Cloud Billing data.
This will always increase with each new export. |
pricing_as_of_time |
Timestamp | The pricing data applicable to your Cloud Billing account is
generated once each day to prepare it for export to
BigQuery. This is the daily timestamp of when the
pricing data was generated. |
billing_account_id |
String | The Cloud Billing account ID that the pricing is associated with. |
billing_account_name |
String | The name of the Cloud Billing account that the pricing is associated with. |
business_entity_name |
String | The name of the Google service family for the service which offers the SKU. Values include GCP (Google Cloud) or Maps (Google Maps Platform). |
service.id |
String | The ID of the Google Cloud service or Google Maps Platform API that reported the Cloud Billing data. For example, 6F81-5844-456A. |
service.description |
String | The description of the Google Cloud service or Google Maps Platform API that reported the Cloud Billing data. For example, Compute Engine. |
sku.id |
String | The unique identifier for the resource SKU used by the service. For example, 2E27-4F75-95CD. For the full list of SKUs, see Google Cloud SKUs. |
sku.description |
String | A human-readable description of the resource SKU used by the service. For example, N1 Predefined Instance Core running in Americas. |
sku.destination_migration_mappings |
Array of Strings | The destination SKUs' names which this SKU is being migrated to. |
product_taxonomy |
Array of Strings | List of product categories that apply to the SKU, such as Serverless, Cloud Run, TaskQueue, VMs On Demand, Cores: Per Core, and others. |
geo_taxonomy |
Struct | Geographic metadata that applies to the SKU, such as regions and multi-regions like us-east4 the European Union. |
geo_taxonomy.type |
String | The type of geographic metadata associated with the SKU.
Valid values are:
|
geo_taxonomy.regions |
Array of Strings | The Google Cloud regions associated with the SKU.
For example, Europe-west2 or US .
A region is a specific geographic place, such as London. A multi-region is a large geographic area, such as the United States, that contains two or more geographic places. |
pricing_unit |
String | The shorthand for the unit of usage in which the pricing is specified (such as GiBy.mo). |
pricing_unit_description |
String | The human-readable description of the unit of usage (such as gibibyte month). |
account_currency_code |
String | The currency that the Cloud Billing account is configured to operate in, using a three-letter currency code defined in ISO 4217. For more information, see Local currency for billing and automatic payments. |
currency_conversion_rate |
String | The exchange rate from US dollars to the
local currency the Cloud Billing account is configured to
operate in. If the currency of the Cloud Billing account is USD,
the exchange rate defaults to 1.0.
If your Cloud Billing
costs are billed in a non-USD currency, you can convert your
usage costs to USD using this formula:
When Google charges in local currency, we convert
prices into applicable local currency pursuant to the conversion rates
published by leading financial institutions. This includes any surcharge
collected for billing in non-USD currency. We use the rates that are in
effect on the
|
list_price |
Struct | The list price of the Google Cloud or Google Maps Platform SKUs
and SKU
pricing tiers, in effect as of the
List prices can be found at
The list price data is generated and exported for all customers. If your Cloud Billing account has custom contract pricing, billing-account-specific pricing data is exported as well. |
billing_account_price |
Struct | If you have contract pricing, this is your custom SKU price from the contract that's linked to your Cloud Billing account.
Your contracted prices (if applicable to your Cloud Billing
account) can be found at
|
price_info |
Struct | Background information about the contract price. |
price_info.price_reason |
String | Background information on the origin of the contract price. Reasons include:
|
price_info.discount_percent |
Numeric | For contract pricing that's the result of a percent discount
(FIXED_DISCOUNT or FLOATING_DISCOUNT ), this is the
percentage of the discount used.
|
price_info.discount_percent_fixed_date |
Date | For contact pricing with a percent discount that's anchored to a
specific date (FIXED_DISCOUNT ), this is the date used.
|
price_info.discount_migrated_from |
String | For contract pricing discounts that were migrated from other SKUs
(MIGRATED_PRICE ), this is the source SKU of the discount.
Sometimes when a SKU is split into two, the discount is migrated from the
old SKU to the new SKUs.
|
aggregation_info |
Struct | Represents the aggregation level and interval for the pricing tiers of a single SKU. |
aggregation_info.aggregation_level |
String | The level at which usage is aggregated to compute cost for
pricing tiers.
Levels include:
|
aggregation_info.aggregation_interval |
String | The interval at which usage is aggregated to compute cost for
pricing tiers.
Intervals include:
|
tiered_rates |
Array of Strings | Information about the
pricing tier including the SKU's price in USD and the SKU's
price in the
currency the Cloud Billing account is configured to use.
Some SKUs have only one pricing tier. If a SKU has multiple pricing tiers,
each pricing tier will appear as a different row. You can identify SKUs
with multiple pricing tiers using the
|
tiered_rates.pricing_unit_quantity |
Float | The SKU's pricing tier unit quantity. For example, if the tier price is $1 per 1000000 Bytes, then this column will show 1000000. |
tiered_rates.start_usage_amount |
Float | Lower bound amount for a given pricing tier, in pricing units. For example, a SKU with three pricing tiers such as 0-100 units, 101-1000 units, and 1001+ units, would display three pricing rows, with 0, 101, and 1001 as the [tiered_rates].start_usage_amount values. |
tiered_rates.usd_amount |
Numeric | The price for the SKU, in US dollars. |
tiered_rates.account_currency_amount |
Numeric | The SKU's tier price converted from USD to the
currency the Cloud Billing account is configured to use,
using the
currency_conversion_rate .
This converted price is calculated using the following formula:
When Google charges in local currency, we convert
prices into applicable local currency pursuant to the conversion rates
published by leading financial institutions. This includes any surcharge
collected for billing in non-USD currency. We use the rates that are in
effect on the
|
About pricing tiers
SKU prices are offered by pricing tiers. Pricing tiers provide a pricing structure based on different tier levels. Some SKUs have only a single pricing tier while others have multiple pricing tiers. Examples of SKUs with multi-tiered pricing include the following:
- SKUs with a free usage tier. For example: 1-1000 units are free. 1001+ units are priced at $1 each.
- SKUs where the price per unit decreases after the usage quantity within a tier is exceeded. For example: 1-100 units are priced at $5 each, 101-1000 units are priced at $4 each, and 1001+ units are priced at $3 each.
Some notes about multi-tiered pricing:
The tier usage counter resets to zero based on the
aggregation_interval
of the SKU: daily or monthly.- Daily SKUs reset each day at 12 AM US and Canadian Pacific Time (UTC-8 or UTC-7).
- Monthly SKUs reset to zero on the first day of each calendar month (example: January, February, and so on), at 12 AM US and Canadian Pacific Time (UTC-8 or UTC-7).
Tiers operate independently for each Cloud Billing account and don't aggregate across multiple Cloud Billing accounts, even if the projects are in the same Organization or belong to the same legal entity.
Tiers operate independently per SKU: The usage of one SKU can only affect the price of that SKU. There are no SKUs in which usage affects the tiered pricing of another SKU.
It's possible that the SKU pricing tiers for list prices might not line up exactly with the pricing tiers for contract prices. This circumstance is rare.
If a SKU has multiple pricing tiers, each SKU tier price is listed as a separate row in the pricing table. You can identify SKUs with multiple pricing tiers using the
tiered_rates.start_usage_amount
.Depending on how you write your query, you can return your SKU pricing tiers as nested or unnested data. For more information about nested and unnested data, see the following examples.
Pricing data query examples
This section provides examples of how to query the Cloud Billing pricing data exported to BigQuery.
- Get list prices for a specific SKU
- Get list prices for a specific SKU, and include service description
- Use product taxonomy and geo taxonomy to query SKUs
This section provides different examples of how to query the Cloud Billing pricing data exported to BigQuery.
Common values used in the example pricing queries
The query examples in this section use the following values:
- Table name:
project.dataset.cloud_pricing_export
- SKU ID:
2DA5-55D3-E679
(Cloud Run - Requests)
Get list prices for a specific SKU
This example demonstrates a query that returns the
list_price
for each
pricing tier
for a specified SKU.
Standard SQL
SELECT sku.id, sku.description, list_price.* FROM `project.dataset.cloud_pricing_export` WHERE DATE(_PARTITIONTIME) = "2020-07-20" AND sku.id = "2DA5-55D3-E679" ;
_PARTITIONTIME
is a field auto-generated by BigQuery
and represents the date that the data belongs to. Instead of _PARTITIONTIME
,
you can use a field that Cloud Billing export explicitly generates,
such as
pricing_as_of_time
.
Here's the same query configured to use the pricing_as_of_time
field:
SELECT sku.id, sku.description, list_price.* FROM `project.dataset.cloud_pricing_export` WHERE DATE(pricing_as_of_time) = "2020-07-20" AND sku.id = "2DA5-55D3-E679" ;
Query results
Row | id | description | pricing_unit | aggregation_info. aggregation_level |
aggregation_info. aggregation_interval |
tiered_rates. pricing_unit_quantity |
tiered_rates. start_usage_amount |
tiered_rates. usd_amount |
tiered_rates. account_currency_amount |
---|---|---|---|---|---|---|---|---|---|
1 | 2DA5-55D3-E679 | Requests | COUNT | ACCOUNT | MONTHLY | 1000000 | 0 | 0 | 0 |
1000000 | 2000000 | 0.4 | 0.4 |
Get list prices for a specific SKU, and include service description
The two examples in this section demonstrate queries that return the
list_price
for each pricing tier
for a specified SKU, and includes the SKU description and the service
description.
- Example 1 returns one SKU per row, with the pricing tiers displayed as nested data.
- Example 2 demonstrates unnesting the data to return one row per SKU per pricing tier.
Example 1: Returns nested data
This example queries a single SKU to return the
list_price
data. This SKU has multiple
pricing tiers.
The list price field values display in individual rows that are nested under
the SKU ID row.
Standard SQL
SELECT sku.id AS sku_id, sku.description AS sku_description, service.id AS service_id, service.description as service_description, list_price.* FROM my-billing-admin-project.my_billing_dataset.cloud_pricing_export WHERE DATE(_PARTITIONTIME) = "2020-07-20" AND sku.id = "2DA5-55D3-E679" ;
Query results:
Row | sku_id | sku_description | service_id | service_description | aggregation_info. aggregation_level |
aggregation_info. aggregation_interval |
tiered_rates. pricing_unit_quantity |
tiered_rates. start_usage_amount |
tiered_rates. usd_amount |
tiered_rates. account_currency_amount |
---|---|---|---|---|---|---|---|---|---|---|
1 | 2DA5-55D3-E679 | Requests | 152E-C115-5142 | Cloud Run | ACCOUNT | MONTHLY | 1000000 | 0 | 0 | 0 |
1000000 | 2000000 | 0.4 | 0.4 |
Example 2: Returns unnested data joined with the same table
This example queries a single SKU to return the
list price
.
The SKU has multiple
pricing tiers.
The query demonstrates using the
UNNEST
operator to
flatten the tiered_rates
array
and join the fields with the same table, resulting in one row per pricing tier.
Standard SQL
SELECT sku.id AS sku_id, sku.description AS sku_description, service.id AS service_id, service.description as service_description, tier.* FROM `my-billing-admin-project.my_billing_dataset.cloud_pricing_export` as sku_pricing, UNNEST (sku_pricing.list_price.tiered_rates) as tier WHERE DATE(_PARTITIONTIME) = "2020-07-20" AND sku.id = "2DA5-55D3-E679" ;
Query results:
Row | sku_id | sku_description | service_id | service_description | pricing_unit_quantity | start_usage_amount | usd_amount | account_currency_amount |
---|---|---|---|---|---|---|---|---|
1 | 2DA5-55D3-E679 | Requests | 152E-C115-5142 | Cloud Run | 1000000.0 | 0.0 | 0.0 | 0.0 |
2 | 2DA5-55D3-E679 | Requests | 152E-C115-5142 | Cloud Run | 1000000.0 | 2000000.0 | 0.4 | 0.4 |
Use product taxonomy and geo taxonomy to query SKUs
- Product taxonomy is a list of product categories that apply to the SKU, such as Serverless, Cloud Run, or VMs On Demand.
- Geo taxonomy is the geographic metadata that applies to a SKU, consisting of type and region values.
Get the product taxonomy of a SKU
This example demonstrates a query that returns the
product_taxonomy
list for a specified SKU, where the SKU ID = 2DA5-55D3-E679
(Cloud Run - Requests).
Standard SQL
SELECT sku.id AS sku_id, sku.description AS sku_description, service.id AS service_id, service.description as service_description, product_taxonomy FROM `project.dataset.cloud_pricing_export` WHERE DATE(_PARTITIONTIME) = "2020-07-20" AND sku.id = "2DA5-55D3-E679" ;
Query results:
Row | sku_id | sku_description | service_id | service_description | product_taxonomy |
---|---|---|---|---|---|
1 | 2DA5-55D3-E679 | Requests | 152E-C115-5142 | Cloud Run | GCP |
Serverless | |||||
Cloud Run | |||||
Other |
Get all SKUs for a specific product taxonomy
This example demonstrates a query that returns all SKUs that match a specified
product_taxonomy
.
In this query, we are specifying Serverless as the product taxonomy
value.
Standard SQL
SELECT sku.id AS sku_id, sku.description AS sku_description, service.id AS service_id, service.description as service_description, product_taxonomy FROM `project.dataset.cloud_pricing_export` WHERE DATE(_PARTITIONTIME) = "2020-07-20" AND "Serverless" in UNNEST(product_taxonomy) LIMIT 10 ;
Query results:
Row | sku_id | sku_description | service_id | service_description | product_taxonomy |
---|---|---|---|---|---|
1 | 0160-BD7B-4C40 | Cloud Tasks Network Intra Region Egress | F3A6-D7B7-9BDA | Cloud Tasks | GCP |
Serverless | |||||
Cloud Tasks | |||||
Other | |||||
2 | FE08-0A74-7AFD | Cloud Tasks GOOGLE-API Egress | F3A6-D7B7-9BDA | Cloud Tasks | GCP |
Serverless | |||||
Cloud Tasks | |||||
Other | |||||
3 | A81A-32A2-B46D | Task Queue Storage Salt Lake City | F17B-412E-CB64 | App Engine | GCP |
Serverless | |||||
GAE | |||||
Other | |||||
TaskQueue |
Get all SKUs for a specific geo taxonomy and product taxonomy
This example demonstrates a query that returns all SKUs that match a specified
geo_taxonomy
region and a specified
product_taxonomy
,
where region
= us-east4 and product_taxonomy
= VMs On Demand.
Standard SQL
SELECT sku.id AS sku_id, sku.description AS sku_description, service.id AS service_id, service.description as service_description, geo_taxonomy, product_taxonomy FROM `project.dataset.cloud_pricing_export` WHERE DATE(_PARTITIONTIME) = "2020-07-20" AND "VMs On Demand" in UNNEST(product_taxonomy) AND geo_taxonomy.type = "REGIONAL" AND "us-east4" in UNNEST (geo_taxonomy.regions) ;
Query results:
Row | sku_id | sku_description | service_id | service_description | geo_taxonomy.type | geo_taxonomy.regions | product_taxonomy |
---|---|---|---|---|---|---|---|
1 | 9174-81EE-425B | Sole Tenancy Premium for Sole Tenancy Instance Ram running in Virginia | 6F81-5844-456A | Compute Engine | REGIONAL | us-east4 | GCP |
Compute | |||||||
GCE | |||||||
VMs On Demand | |||||||
Memory: Per GB | |||||||
2 | C3B9-E891-85ED | Sole Tenancy Instance Ram running in Virginia | 6F81-5844-456A | Compute Engine | REGIONAL | us-east4 | GCP |
Compute | |||||||
GCE | |||||||
VMs On Demand | |||||||
Memory: Per GB | |||||||
3 | 6E2A-DCD9-87ED | N1 Predefined Instance Ram running in Virginia | 6F81-5844-456A | Compute Engine | REGIONAL | us-east4 | GCP |
Compute | |||||||
GCE | |||||||
VMs On Demand | |||||||
Memory: Per GB |
Returns the list prices for a SKU with multiple pricing tiers
You query your BigQuery data by table name. The table name
used in the query's FROM
clause is determined using three values:
project.dataset.BQ_table_name
.
Common values used in these examples:
- Table name:
project.dataset.cloud_pricing_export
- SKU ID:
2DA5-55D3-E679
(Cloud Run - Requests)
Example 1: Returns nested data
This example queries a single SKU to return the
list_price
data. This SKU has multiple
pricing tiers.
The list price field values display in individual rows that are nested under
the SKU ID row.
Standard SQL
SELECT sku.id AS sku_id, sku.description AS sku_description, service.id AS service_id, service.description as service_description, list_price.* FROM my-billing-admin-project.my_billing_dataset.cloud_pricing_export WHERE DATE(_PARTITIONTIME) = "2020-07-20" AND sku.id = "2DA5-55D3-E679" ;
Query results:
Row | sku_id | sku_description | service_id | service_description | aggregation_info. aggregation_level |
aggregation_info. aggregation_interval |
tiered_rates. pricing_unit_quantity |
tiered_rates. start_usage_amount |
tiered_rates. usd_amount |
tiered_rates. account_currency_amount |
---|---|---|---|---|---|---|---|---|---|---|
1 | 2DA5-55D3-E679 | Requests | 152E-C115-5142 | Cloud Run | ACCOUNT | MONTHLY | 1000000 | 0 | 0 | 0 |
1000000 | 2000000 | 0.4 | 0.4 |
Example 2: Returns unnested data joined with the same table
This example queries a single SKU to return the
list price
.
The SKU has multiple
pricing tiers.
The query demonstrates using the
UNNEST
operator to
flatten the tiered_rates
array
and join the fields with the same table, resulting in one row per pricing
tier.
Standard SQL
SELECT sku.id AS sku_id, sku.description AS sku_description, service.id AS service_id, service.description as service_description, tier.* FROM `my-billing-admin-project.my_billing_dataset.cloud_pricing_export` as sku_pricing, UNNEST (sku_pricing.list_price.tiered_rates) as tier WHERE DATE(_PARTITIONTIME) = "2020-07-20" AND sku.id = "2DA5-55D3-E679" ;
Query results:
Row | sku_id | sku_description | service_id | service_description | pricing_unit_quantity | start_usage_amount | usd_amount | account_currency_amount |
---|---|---|---|---|---|---|---|---|
1 | 2DA5-55D3-E679 | Requests | 152E-C115-5142 | Cloud Run | 1000000.0 | 0.0 | 0.0 | 0.0 |
2 | 2DA5-55D3-E679 | Requests | 152E-C115-5142 | Cloud Run | 1000000.0 | 2000000.0 | 0.4 | 0.4 |
Related topics
Topics related to exported Cloud Billing data
- Set up Cloud Billing data export to BigQuery
- Example queries for Cloud Billing data export to BigQuery
- Visualize spend over time with Looker Studio
Cost and pricing reports available in the Google Cloud console
- View your Cloud Billing reports and cost trends
- View and download the cost details of your invoice or statement
- View and download prices for Google's cloud services
- Understand your savings with cost breakdown reports
- Analyze the effectiveness of your committed use discounts
- View your cost and payment history