Structure of Standard data export

This document provides reference information for the schema of Cloud Billing standard usage cost data that's exported to each table in BigQuery.

Schema of the standard usage cost data

In your BigQuery dataset, your standard Google Cloud usage cost data is loaded into a data table named gcp_billing_export_v1_<BILLING_ACCOUNT_ID> .

The following information describes the schema of the Google Cloud standard usage cost data that's exported to BigQuery. The schema contains standard Cloud Billing account cost usage information, such as account ID, invoice date, services, SKUs, projects, labels, locations, cost, usage, credits, adjustments, and currency.

When you use the standard usage cost data in BigQuery, note the following:

• The table schema for the standard Cloud Billing data export can change over time, such as when new fields are added to the standard export. To protect your queries from future schema changes, rather than relying on queries that directly reference the exported columns, we recommend that you use BigQuery views to normalize the data so that the table presents the same schema to your queries. Learn more about this limitation .
• When selecting or creating a BigQuery dataset for your standard usage cost data , you can select any dataset location that's supported for use with Cloud Billing data .
• When you enable the standard usage cost data export for the first time in Cloud Billing, if you select a dataset configured to use a multi-region location (EU or US), Cloud Billing data will be available retroactively from the start of the previous month. Data is exported in chronological order. For the initial backfill of exported data, it might take up to five days for your retroactive Cloud Billing data to finish exporting before you start seeing your most recent usage data.
• If you enable the standard usage cost data export and select a dataset that's configured to use a supported region location , your Cloud Billing data will be available starting from the date when you enabled the export.
• If you enabled, disabled, and subsequently re-enabled the standard usage cost data export, the Cloud Billing data might not be available for the period when data export was explicitly disabled.
• Learn more about the frequency of the data loads into your BigQuery tables .
• The standard usage cost data doesn't include any resource-level cost data, like a virtual machine or SSD that generates service usage. If you're interested in exporting resource-level cost data to BigQuery for analysis, consider enabling the export of detailed usage cost data . The exported detailed usage cost data includes all of the fields and information contained in the standard usage cost data .
• See other limitations that might impact exporting your billing data to BigQuery, such as datasets with customer-managed encryption keys (CMEK) enabled.

Understand standard and detailed usage cost data

The following sections describe the standard and detailed usage cost data exported to BigQuery.

About labels

The cost data for a specific label only shows usage from the date that the label was applied to a resource. For example, if you add the label environment:dev to a Compute Engine VM on January 15, 2024, any analysis for environment:dev includes only the usage for that VM since January 15.

You might also see label data at different times for different services, depending on when each service provides it.

Available system labels

System labels are key-value pairs for important metadata about the resource that generated the usage. The following system labels are automatically included on applicable usage.

Differences between exported data and invoices

Google Cloud products report usage and cost data to Cloud Billing processes at varying intervals. As a result, you might see a delay between your use of Google Cloud services, and the usage and costs being available to view in Cloud Billing. Typically, your costs are available within a day, but can sometimes take more than 24 hours.

At the end of a calendar month, late-reported usage might not be included on that month's invoice and instead might roll over to the next month's invoice.

When you query your costs using timestamp fields, your returned data might pick up late-reported usage that wasn't originally included on the invoice that was generated for the same usage month. As a result, the Cloud Billing data returned might not map directly to that invoice.

Timestamp fields include:

• usage_start_time
• usage_end_time
• export_time

To return Cloud Billing data that maps directly to an invoice, query on invoice.month instead of timestamp fields.

Taxes

As of September 1, 2020, your usage cost data shows your tax liability for each of your projects, instead of as a single line item. If you have queries or visualizations that depend on tax data, you might need to update the queries to account for these changes.

For example, for costs recorded before September 1, your usage cost data looks similar to the following example, which shows a total tax liability of $10.

For costs recorded after September 1, the $10 is broken down to $6 for example-project , and $4 for test-project :

Errors and adjustments

In the rare event that your Cloud Billing data contains an error or requires an adjustment, it's appended with corrective data. These adjustments fall under one of two categories: billing modifications or corrections.

Billing modifications

Billing modifications appear as separate line items. If you received a billing modification, a new line item in your Cloud Billing export to BigQuery shows the change. The adjustments shown correspond to the invoice, credit memo, and debit memo documents available in the Documentsarea of the Billingsection in the Google Cloud console.

For more information on billing modifications and how they're applied, see Understand memos and adjustments .

Corrections

Corrections appear as new data that negates incorrect data on the source SKUs. In some cases, new data replaces the incorrect charge. All columns in the billing data export will match the original data, except for the following columns:

• cost
• credit
• usage.amount
• export_time

For example, imagine that you're charged $10 for your usage of SKU A on January 1. On your January invoice (issued in early February), you'll see a charge of $10 for SKU A . However, on February 2, Google Cloud issued a correction against SKU A , reducing the usage cost to $5. You'll receive two additional line items on your February invoice (issued in early March):

• -$10 for usage on January 1 (negating the original charge)
• $5 for usage on January 1 (stating the intended charge)

These new items have an adjustment_info column in the billing data export. The original January invoice, showing the overcharge, won't be adjusted. You can verify your charges in your billing data export by viewing your costs by usage_start_time and grouping by Day . In these views, any corrections or charges for late-monetized usage are accumulated, and you don't need to worry about any temporarily incorrect data.

If you want more detailed information on your corrections, view all charges in an invoice month , and look for charges where the usage date occurred before the invoice month. These charges are the results of corrections or late-monetized usage.

The following code sample shows how to create a basic query that returns the total cost of corrections or late-monetized usage:

  SELECT 
  
 SUM 
 ( 
 cost 
 ) 
  
 + 
  
 SUM 
 ( 
 IFNULL 
 (( 
 SELECT 
  
 SUM 
 ( 
 c 
 . 
 amount 
 ) 
  
 FROM 
  
 UNNEST 
 ( 
 credits 
 ) 
  
 c 
 ), 
  
 0 
 )) 
  
 AS 
  
 total 
 FROM 
  
 ` 
 project 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX 
 - 
 XXXXXX 
 - 
 XXXXXX 
 ` 
 WHERE 
  
 invoice 
 . 
 month 
  
 = 
  
 '202311' 
  
 AND 
  
 DATE 
 ( 
 TIMESTAMP_TRUNC 
 ( 
 usage_start_time 
 , 
  
 Day 
 , 
  
 'US/Pacific' 
 )) 
 < 
 '2023-11-01' 
 ; 
 

For a query example that returns a cost breakdown by service , for invoice charges, where the usage date occurred before the invoice month , see Query cost details to view corrections or late-monetized usage by service for a specified invoice month in "Example queries for Cloud Billing data export."

About promotional credits in custom pricing contracts

If you have a custom pricing contract, you might receive promotional credits to use on Google Cloud as part of the contract. For example, you might receive $1,000 to use on Compute Engine resources. Promotional credits are typically considered a form of payment. When available, promotional credits are automatically applied to reduce your total bill.

The terms of your contract specify whether the promotional credits apply to your costs calculated at the list price of a SKU, or the net price (after discounts).

If your promotional credits apply to costs that are calculated at the list price , in the Cost tablereport, there's a service called Invoice , with a SKU called Contract billing adjustment . This SKU adjusts your credits so that they apply to the costs at list price. To see the usage that the adjustment is for, query the system.labels columns. The key in system.labels.key is cloud-invoice.googleapis.com/sku_id , and the value in system.labels.value contains the SKU ID that the credit and the adjustment applied to.

About tags

Tags are resources in the form of key-value pairs that can be attached to resources directly or through inheritance. You can use tags to perform chargebacks, audits, and other cost allocation analysis. You can also use tags and conditional enforcement of policies for fine-grained control across your resource hierarchy.

Tags have a robust permissions model and can support inheritance, centralized management, nomenclature standardization, and policy engine integration, while labels are a separate tool that allow you to annotate resources.

Tags data appears in BigQuery exports for Resources, Projects, Folders, and Organizations.

Available tags

The Standard costs and Detailed costs exports for Resources, Projects, Folders, and Organizations include these fields for tags data: Tags Key, Tags Value, Tags Inherited, and Tags Namespace.

Resource-level tags in the Cloud Billing data export are available for the following resources:

• AlloyDB for PostgreSQL clusters, instances, and backups
• Artifact Registry repositories
• Bigtable instances
• Cloud Run services and jobs
• Cloud Storage buckets
• Compute Engine instances
• Memorystore for Redis instances
• Secret Manager global and regional secrets
• Spanner instances
• Firestore databases

Tags limitations

• Tags might take up to an hour to propagate to BigQuery exports. If a tag has been added or removed within an hour, or if a resource has existed for less than an hour, it might not appear in the export.

Standard usage cost query examples

This section provides examples of how to query the Cloud Billing standard usage cost data exported to BigQuery.

• Return the total costs on an invoice
  • Example 1: Sum of all costs, per invoice
  • Example 2: Return details by cost type, per invoice month
• Query examples with labels
  • Query every row without grouping
  • Group by label map as a JSON string
  • Group by label value for a specific key
  • Group by key/value pairs
• Committed use discount queries
  • Viewing commitment fees
  • Viewing commitment credits
• Use resource hierarchy filters to review ancestry
• Example 1: Filter by resource name
• Example 2: Filter by display name
• Additional query examples
  • Query costs and credits by project for a specified invoice month

Specifying the table name to use in your queries

In these examples, to query the Cloud Billing data in BigQuery, you need to specify the table name in the FROM clause. The table name is determined using three values: project.dataset.BQ_table_name .

• project is the ID of the Google Cloud project you set up that contains your BigQuery dataset.
• dataset is the name of the BigQuery dataset you set up to contain the BigQuery tables with your exported Cloud Billing data.
• BQ_table_name is the name of the BigQuery table that contains the exported Cloud Billing data that you want to query. There are three BigQuery tables that contain Cloud Billing data:
  • Standard usage cost table : In your BigQuery dataset, this table is named gcp_billing_export_v1_<BILLING_ACCOUNT_ID> .
  • Detailed usage cost table : In your BigQuery dataset, this table is named gcp_billing_export_resource_v1_<BILLING_ACCOUNT_ID> .
  • Pricing table : In your BigQuery dataset, this table is named cloud_pricing_export .

Common values used in the example standard cost queries

The query examples in this section use the following value for Table name: project.dataset.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX

These query examples also work with the detailed usage cost data exported to BigQuery, although they aren't written to retrieve any of the resource-level information that's provided with the detailed usage cost export option.

Return the total costs on an invoice

The following queries demonstrate two ways of viewing cost and credit values using exported billing data.

• The total field directly sums the floating point cost and credit values, which can result in floating point rounding errors.
• The total_exact field converts costs and credit values to micros before summing, then converts back to dollars after summing, avoiding the floating point rounding error.

Example 1: Sum of all costs, per invoice

This query shows the invoice total for each month, as a sum of regular costs, taxes, adjustments, and rounding errors.

Standard SQL

 SELECT 
  
 invoice 
 . 
 month 
 , 
  
 SUM 
 ( 
 cost 
 ) 
  
 + 
  
 SUM 
 ( 
 IFNULL 
 (( 
 SELECT 
  
 SUM 
 ( 
 c 
 . 
 amount 
 ) 
  
 FROM 
  
 UNNEST 
 ( 
 credits 
 ) 
  
 c 
 ), 
  
 0 
 )) 
  
 AS 
  
 total 
 , 
  
 ( 
 SUM 
 ( 
 CAST 
 ( 
 cost 
  
 * 
  
 1000000 
  
 AS 
  
 int64 
 )) 
  
 + 
  
 SUM 
 ( 
 IFNULL 
 (( 
 SELECT 
  
 SUM 
 ( 
 CAST 
 ( 
 c 
 . 
 amount 
  
 * 
  
 1000000 
  
 as 
  
 int64 
 )) 
  
 FROM 
  
 UNNEST 
 ( 
 credits 
 ) 
  
 c 
 ), 
  
 0 
 ))) 
  
 / 
  
 1000000 
  
 AS 
  
 total_exact 
 FROM 
  
 ` 
 project 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ` 
 GROUP 
  
 BY 
  
 1 
 ORDER 
  
 BY 
  
 1 
  
 ASC 
 ; 

For example, the result of the preceding query might be:

Example 2: Return details by cost type, per invoice month

This query shows the totals for each cost_type for each month. Cost types include regular costs, taxes, adjustments, and rounding errors.

Standard SQL

 SELECT 
  
 invoice 
 . 
 month 
 , 
  
 cost_type 
 , 
  
 SUM 
 ( 
 cost 
 ) 
  
 + 
  
 SUM 
 ( 
 IFNULL 
 (( 
 SELECT 
  
 SUM 
 ( 
 c 
 . 
 amount 
 ) 
  
 FROM 
  
 UNNEST 
 ( 
 credits 
 ) 
  
 c 
 ), 
  
 0 
 )) 
  
 AS 
  
 total 
 , 
  
 ( 
 SUM 
 ( 
 CAST 
 ( 
 cost 
  
 * 
  
 1000000 
  
 AS 
  
 int64 
 )) 
  
 + 
  
 SUM 
 ( 
 IFNULL 
 (( 
 SELECT 
  
 SUM 
 ( 
 CAST 
 ( 
 c 
 . 
 amount 
  
 * 
  
 1000000 
  
 as 
  
 int64 
 )) 
  
 FROM 
  
 UNNEST 
 ( 
 credits 
 ) 
  
 c 
 ), 
  
 0 
 ))) 
  
 / 
  
 1000000 
  
 AS 
  
 total_exact 
 FROM 
  
 ` 
 project 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ` 
 GROUP 
  
 BY 
  
 1 
 , 
  
 2 
 ORDER 
  
 BY 
  
 1 
  
 ASC 
 , 
  
 2 
  
 ASC 
 ; 

For example, the result of the preceding query might be:

Query examples with labels

The following examples illustrate other ways to query your data with labels.

For the examples in this section, assume the following:

• You have two apps (grapefruit-squeezer and chocolate-masher).
• For each app, you have two environments (dev and prod).
• The dev environment has one small instance per app.
• The prod environment has one small instance in Americas and one small instance in Asia.
• Each instance is labeled with the app and environment.
• You have one instance with no labels that you use for experimentation.

Your total bill is $24 with the following breakdown:

Query every row without grouping

The most granular view of these costs would be to query every row without grouping. Assume all fields, except labels and sku description, are the same (project, service, and so on).

Standard SQL

 SELECT 
  
 sku 
 . 
 description 
 , 
  
 TO_JSON_STRING 
 ( 
 labels 
 ) 
  
 as 
  
 labels 
 , 
  
 cost 
  
 as 
  
 cost 
 FROM 
  
 ` 
 project 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ` 
 ; 

Legacy SQL

 TO_JSON_STRING 
  
 not 
  
 supported 
 . 

Group by label map as a JSON string

This is a quick and easy way to break down cost by each label combination.

Standard SQL

 SELECT 
  
 TO_JSON_STRING 
 ( 
 labels 
 ) 
  
 as 
  
 labels 
 , 
  
 sum 
 ( 
 cost 
 ) 
  
 as 
  
 cost 
 FROM 
  
 ` 
 project 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ` 
 GROUP 
  
 BY 
  
 labels 
 ; 

Legacy SQL

 TO_JSON_STRING 
  
 not 
  
 supported 
 . 

Group by label value for a specific key

Breaking down costs for values of a specific label key is a common use case. By using a LEFT JOIN and putting the key filter in the JOIN condition (rather than WHERE), you include cost that doesn't contain this key, and so receive a complete view of your cost.

Standard SQL

 SELECT 
  
 labels 
 . 
 value 
  
 as 
  
 environment 
 , 
  
 SUM 
 ( 
 cost 
 ) 
  
 as 
  
 cost 
 FROM 
  
 ` 
 project 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ` 
 LEFT 
  
 JOIN 
  
 UNNEST 
 ( 
 labels 
 ) 
  
 as 
  
 labels 
  
 ON 
  
 labels 
 . 
 key 
  
 = 
  
 "environment" 
 GROUP 
  
 BY 
  
 environment 
 ; 

Legacy SQL

 SELECT 
  
 labels 
 . 
 value 
  
 as 
  
 environment 
 , 
  
 SUM 
 ( 
 cost 
 ) 
  
 as 
  
 cost 
 FROM 
  
 [ 
 project 
 : 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ] 
 WHERE 
  
 labels 
 . 
 key 
  
 = 
  
 "environment" 
  
 OR 
  
 labels 
 . 
 key 
  
 IS 
  
 NULL 
 GROUP 
  
 BY 
  
 environment 
 ; 

Group by key/value pairs

Be careful when interpreting or exporting these results. An individual row here shows a valid sum without any double counting, but shouldn't be combined with other rows (except possibly if the key is the same, or if you're certain the keys are never set on the same resource).

Standard SQL

 SELECT 
  
 labels 
 . 
 key 
  
 as 
  
 key 
 , 
  
 labels 
 . 
 value 
  
 as 
  
 value 
 , 
  
 SUM 
 ( 
 cost 
 ) 
  
 as 
  
 cost 
 FROM 
  
 ` 
 project 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ` 
 LEFT 
  
 JOIN 
  
 UNNEST 
 ( 
 labels 
 ) 
  
 as 
  
 labels 
 GROUP 
  
 BY 
  
 key 
 , 
  
 value 
 ; 

Legacy SQL

 SELECT 
  
 labels 
 . 
 key 
  
 as 
  
 key 
 , 
  
 labels 
 . 
 value 
  
 as 
  
 value 
 , 
  
 SUM 
 ( 
 cost 
 ) 
 FROM 
  
 [ 
 project 
 : 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ] 
 GROUP 
  
 BY 
  
 key 
 , 
  
 value 
 ; 

Note that the total sum is greater than your bill.

Committed use discount queries

The following queries demonstrate ways of viewing the fees and credits associated with committed use discounts in exported billing data. To understand how your commitment fees and credits are attributed to your Cloud Billing account and projects, see Attribution of committed use discounts .

Viewing commitment fees

To view the commitment fees for your committed use discounts in your billing data export, use the following sample query.

Standard SQL

 SELECT 
  
 invoice 
 . 
 month 
  
 AS 
  
 invoice_month 
 , 
  
 SUM 
 ( 
 cost 
 ) 
  
 as 
  
 commitment_fees 
 FROM 
  
 ` 
 project 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ` 
 WHERE 
  
 LOWER 
 ( 
 sku 
 . 
 description 
 ) 
  
 LIKE 
  
 "commitment%" 
 GROUP 
  
 BY 
  
 1 

Viewing commitment credits

To view your committed use discount credits in your billing data export, use the following sample query.

Standard SQL

 SELECT 
  
 invoice 
 . 
 month 
  
 AS 
  
 invoice_month 
 , 
  
 SUM 
 ( 
 credits 
 . 
 amount 
 ) 
  
 as 
  
 CUD_credits 
 FROM 
  
 ` 
 project 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ` 
 LEFT 
  
 JOIN 
  
 UNNEST 
 ( 
 credits 
 ) 
  
 AS 
  
 credits 
 WHERE 
  
 LOWER 
 ( 
 credits 
 . 
 name 
 ) 
  
 LIKE 
  
 "committed use discount%" 
 GROUP 
  
 BY 
  
 1 

Use resource hierarchy filters to review ancestry

You can use resource hierarchy filters to aggregate costs by hierarchy elements such as projects, folders, and organizations. These query examples show methods for summing costs filtered by resource hierarchy elements and displaying project ancestries.

Example 1: Filter by resource name

This example demonstrates queries that group costs by project ancestry and filter for only costs generated under a specified hierarchy element, identified by the relative resource name.

String method

 SELECT 
  
 invoice 
 . 
 month 
  
 AS 
  
 invoice_month 
 , 
  
 TO_JSON_STRING 
 ( 
 project 
 . 
 ancestors 
 ) 
  
 as 
  
 ancestors 
 , 
  
 SUM 
 ( 
 cost 
 ) 
  
 + 
  
 SUM 
 ( 
 IFNULL 
 (( 
 SELECT 
  
 SUM 
 ( 
 c 
 . 
 amount 
 ) 
  
 FROM 
  
 UNNEST 
 ( 
 credits 
 ) 
  
 c 
 ), 
  
 0 
 )) 
  
 AS 
  
 net_cost 
  
 FROM 
  
 ` 
 project 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ` 
  
 as 
  
 bq 
  
 WHERE 
  
 TO_JSON_STRING 
 ( 
 project 
 . 
 ancestors 
 ) 
  
 like 
  
 "%resource_name\" 
 : 
 \ 
 "folders/1234" 
  
 GROUP 
  
 BY 
  
 invoice_month 
 , 
  
 ancestors 
  
 ORDER 
  
 BY 
  
 invoice_month 
 , 
  
 ancestors 

UNNEST method

 SELECT 
  
 invoice 
 . 
 month 
  
 AS 
  
 invoice_month 
 , 
  
 TO_JSON_STRING 
 ( 
 project 
 . 
 ancestors 
 ) 
  
 as 
  
 ancestors 
 , 
  
 SUM 
 ( 
 cost 
 ) 
  
 + 
  
 SUM 
 ( 
 IFNULL 
 (( 
 SELECT 
  
 SUM 
 ( 
 c 
 . 
 amount 
 ) 
  
 FROM 
  
 UNNEST 
 ( 
 credits 
 ) 
  
 c 
 ), 
  
 0 
 )) 
  
 AS 
  
 net_cost 
  
 FROM 
  
 ` 
 project 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ` 
  
 as 
  
 bq 
 , 
  
 UNNEST 
 ( 
 project 
 . 
 ancestors 
 ) 
  
 as 
  
 ancestor 
  
 WHERE 
  
 ancestor 
 . 
 resource_name 
  
 = 
  
 "folders/1234" 
  
 GROUP 
  
 BY 
  
 invoice_month 
 , 
  
 ancestors 
  
 ORDER 
  
 BY 
  
 invoice_month 
 , 
  
 ancestors 

Example 2: Filter by display name

This example demonstrates queries that group costs by project ancestry and filter for only costs generated under a specified hierarchy element, identified by the user-provided display name.

String matching method

 SELECT 
  
 invoice 
 . 
 month 
  
 AS 
  
 invoice_month 
 , 
  
 TO_JSON_STRING 
 ( 
 project 
 . 
 ancestors 
 ) 
  
 as 
  
 ancestors 
 , 
  
 SUM 
 ( 
 cost 
 ) 
  
 + 
  
 SUM 
 ( 
 IFNULL 
 (( 
 SELECT 
  
 SUM 
 ( 
 c 
 . 
 amount 
 ) 
  
 FROM 
  
 UNNEST 
 ( 
 credits 
 ) 
  
 c 
 ), 
  
 0 
 )) 
  
 AS 
  
 net_cost 
  
 FROM 
  
 ` 
 project 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ` 
  
 as 
  
 bq 
  
 WHERE 
  
 TO_JSON_STRING 
 ( 
 project 
 . 
 ancestors 
 ) 
  
 like 
  
 "%display_name\" 
 : 
 \ 
 "MyFolderName%" 
  
 GROUP 
  
 BY 
  
 invoice_month 
 , 
  
 ancestors 
  
 ORDER 
  
 BY 
  
 invoice_month 
 , 
  
 ancestors 

UNNEST method

 SELECT 
  
 invoice 
 . 
 month 
  
 AS 
  
 invoice_month 
 , 
  
 TO_JSON_STRING 
 ( 
 project 
 . 
 ancestors 
 ) 
  
 as 
  
 ancestors 
 , 
  
 SUM 
 ( 
 cost 
 ) 
  
 + 
  
 SUM 
 ( 
 IFNULL 
 (( 
 SELECT 
  
 SUM 
 ( 
 c 
 . 
 amount 
 ) 
  
 FROM 
  
 UNNEST 
 ( 
 credits 
 ) 
  
 c 
 ), 
  
 0 
 )) 
  
 AS 
  
 net_cost 
  
 FROM 
  
 ` 
 project 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ` 
  
 as 
  
 bq 
 , 
  
 UNNEST 
 ( 
 project 
 . 
 ancestors 
 ) 
  
 as 
  
 ancestor 
  
 WHERE 
  
 ancestor 
 . 
 display_name 
  
 = 
  
 "MyFolderName" 
  
 GROUP 
  
 BY 
  
 invoice_month 
 , 
  
 ancestors 
  
 ORDER 
  
 BY 
  
 invoice_month 
 , 
  
 ancestors 

Query examples with tags

The following examples illustrate ways to query your data with tags.

Calculate costs by invoice month with tags

The following query demonstrates how you can use return costs by invoice month for the cost_center tag.

 SELECT 
  
 invoice 
 . 
 month 
  
 AS 
  
 invoice_month 
 , 
  
 tag 
 . 
 value 
  
 AS 
  
 cost_center 
 , 
  
 ROUND 
 (( 
 SUM 
 ( 
 CAST 
 ( 
 cost 
  
 AS 
  
 NUMERIC 
 )) 
  
 + 
  
 SUM 
 ( 
 IFNULL 
 (( 
 SELECT 
  
 SUM 
  
 ( 
 CAST 
 ( 
 c 
 . 
 amount 
  
 AS 
  
 NUMERIC 
 )) 
  
 FROM 
  
 UNNEST 
 ( 
 credits 
 ) 
  
 AS 
  
 c 
 ), 
  
 0 
 ))), 
  
 2 
 ) 
  
 AS 
  
 net_cost 
 FROM 
  
 ` 
 project 
 - 
 ID 
 . 
 dataset 
 . 
 gcp_billing_export_resource_v1_XXXXXX 
 - 
 XXXXXX 
 - 
 XXXXXX 
 ` 
 , 
  
 UNNEST 
 ( 
 tags 
 ) 
  
 AS 
  
 tag 
 WHERE 
  
 tag 
 . 
 key 
  
 = 
  
 "cost_center" 
  
 AND 
  
 tag 
 . 
 namespace 
  
 = 
  
 "821092389413" 
 GROUP 
  
 BY 
  
 invoice 
 . 
 month 
 , 
  
 tag 
 . 
 value 
 ORDER 
  
 BY 
  
 invoice 
 . 
 month 
 , 
  
 tag 
 . 
 value 
 ; 

For example, the result of the preceding query might be:

View costs of untagged resources

This query shows the invoice total for untagged resources, grouped by invoice month.

 SELECT 
  
 invoice 
 . 
 month 
  
 AS 
  
 invoice_month 
 , 
  
 ROUND 
 (( 
 SUM 
 ( 
 CAST 
 ( 
 cost 
  
 AS 
  
 NUMERIC 
 )) 
  
 + 
  
 SUM 
 ( 
 IFNULL 
 (( 
 SELECT 
  
 SUM 
 ( 
 CAST 
 ( 
 c 
 . 
 amount 
  
 AS 
  
 NUMERIC 
 )) 
  
 FROM 
  
 UNNEST 
 ( 
 credits 
 ) 
  
 AS 
  
 c 
 ), 
  
 0 
 ))), 
  
 2 
 ) 
  
 AS 
  
 net_cost 
 FROM 
  
 ` 
 project 
 - 
 ID 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX 
 - 
 XXXXXX 
 - 
 XXXXXX 
 ` 
 WHERE 
  
 "color" 
  
 NOT 
  
 IN 
  
 ( 
 SELECT 
  
 key 
  
 FROM 
  
 UNNEST 
 ( 
 tags 
 )) 
 GROUP 
  
 BY 
  
 invoice_month 
 ORDER 
  
 BY 
  
 invoice_month 
 ; 

For example, the result of the preceding query might be:

Additional query examples

Query costs and credits by project for a specified invoice month

By providing a specific invoice month of June 2020 (in the format YYYYMM), this query will return a view of the costs and credits grouped by project along with showing project labels.

Standard SQL

 SELECT 
  
 project 
 . 
 name 
 , 
  
 TO_JSON_STRING 
 ( 
 project 
 . 
 labels 
 ) 
  
 as 
  
 project_labels 
 , 
  
 sum 
 ( 
 cost 
 ) 
  
 as 
  
 total_cost 
 , 
  
 SUM 
 ( 
 IFNULL 
 (( 
 SELECT 
  
 SUM 
 ( 
 c 
 . 
 amount 
 ) 
  
 FROM 
  
 UNNEST 
 ( 
 credits 
 ) 
  
 c 
 ), 
  
 0 
 )) 
  
 as 
  
 total_credits 
 FROM 
  
 ` 
 project 
 . 
 dataset 
 . 
 gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX 
 ` 
 WHERE 
  
 invoice 
 . 
 month 
  
 = 
  
 "202006" 
 GROUP 
  
 BY 
  
 1 
 , 
  
 2 
 ORDER 
  
 BY 
  
 1 
 ; 

Legacy SQL

 TO_JSON_STRING 
  
 not 
  
 supported 
 . 

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