Structure of pricing data export

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:
  • GLOBAL – has no regions
  • REGIONAL – has one region
  • MULTI_REGION – has two or more regions
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: cost ÷ currency_conversion_rate = usage cost in US dollars.

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 pricing_as_of_time.

list_price Struct

The list price of the Google Cloud or Google Maps Platform SKUs and SKU pricing tiers, in effect as of the pricing_as_of_time.

list_price contains fields that describe the structure and value of the list price, including:

List prices can be found at list_price.tiered_rates.usd_amount

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.

billing_account_price contains fields that describe the structure and value of the custom contract pricing for Google Cloud and Google Maps Platform SKUs and SKU pricing tiers. The billing_account_price includes:

Your contracted prices (if applicable to your Cloud Billing account) can be found at billing_account_price.tiered_rates.usd_amount

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:

  • DEFAULT_PRICE: The default price is the current list price for the SKU.
  • FIXED_PRICE: A set fixed price for the SKU applicable during the terms of the contract agreement.
  • FIXED_DISCOUNT: Percentage of discount off the list price price, anchored to the list price as of a fixed date.
  • FLOATING_DISCOUNT: Percentage of discount off the current list price (not anchored to a list price as of a specific date).
  • MIGRATED_PRICE: This is applicable for prices that were migrated from other SKUs.
  • MERGED_PRICE: SKU price after merging from multiple sources. For example, with merged tiers, each individual tier can be from a different source with different discount types.
  • LIST_PRICE_AS_CEILING: This is an optional contract feature, available to new contracts after March 21, 2022. When active, if the current list price drops lower than the custom fixed price, the list price (DEFAULT_PRICE) is used for the SKU price. Applies to all fixed price SKUs in the contract, including FIXED_PRICE, FIXED_DISCOUNT, MIGRATED_PRICE, and MERGED_PRICE.
  • CONTRACTED_PRICE_PROTECTION: This is applicable for SKUs that are price protected in your contract. This is rarely used.
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:

  • ACCOUNT: Indicates a SKU with multi-tiered pricing; SKU usage for the pricing tiers is aggregated across all projects in a single Cloud Billing account.
  • PROJECT: Indicates a SKU with multi-tiered pricing; SKU usage for the pricing tiers is aggregated per project.
  • UNKNOWN_AGGREGATION_LEVEL: Indicates a SKU with single-tier pricing; this is the default value for SKUs with non-tiered pricing.
aggregation_info.aggregation_interval String The interval at which usage is aggregated to compute cost for pricing tiers.

Intervals include:

  • ONE_DAY: Indicates a SKU with multi-tiered pricing; SKU usage for the pricing tiers is aggregated every day.
  • ONE_MONTH: Indicates a SKU with multi-tiered pricing; SKU usage for the pricing tiers is aggregated every month.
  • UNKNOWN_AGGREGATION_INTERVAL: Indicates a SKU with single-tier pricing; this is the default value for SKUs with non-tiered pricing.
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.start_usage_amount.

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: tiered_rates.usd_amount * currency_conversion_rate = tiered_rates.account_currency_amount.

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 pricing_as_of_time.

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.

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

Cost and pricing reports available in the Google Cloud console