Billing data export

Google Cloud allows using BigQuery to store and query Cloud Billing data. Exporting billing data to BigQuery is convenient if you need to conduct detailed analysis or use other tools for data visualization.

Billing data

As a Google Cloud customer, you have access to your own billing export table and can follow Google Cloud's instructions to Set up Cloud Billing data export to BigQuery.

However, if you need an accurate export that includes changes applied to your billing data by DoiT (for example, DoiT discounts or custom pricing), you'll need the billing data from the DoiT Platform.

There are two ways to access your Google Cloud billing data in the DoiT Platform:

Using DoiT Reports API.
Opening a support ticket to request access to the DoiT billing export table.
Note
When requesting access to the DoiT billing export table, make sure to provide the account that you'll use for querying. It could be a Google user account, a Google group account, or a service account. The account needs a Billing Account Administrator role for the target Cloud Billing account.

This page explains how to query the DoiT billing export table.

DoiT billing export table

Data availability

Once you provided the necessary information for querying the DoiT billing export table, we'll create a VIEW in Google BigQuery for the target Cloud Billing account and share it with you (if you have multiple billing accounts, we'll create multiple VIEWs, one per billing account).

Your billing export table contains Cloud Billing data incurred from the date you joined the DoiT International consolidated billing.
BigQuery load jobs are ACID-compliant, which means, if you query the BigQuery Cloud Billing export table while data is loading, you will not encounter partially loaded data.

Project, dataset, and table name

To query the Google Cloud Billing data in BigQuery, you need to specify the table name in the FROM clause. The table name consists of three parts: project.dataset.BQ_table_name.

project: The Cloud project that contains your BigQuery dataset. It is always doitintl-cmp-gcp-data.
dataset: The name of the BigQuery dataset that contains the BigQuery tables with your exported Cloud Billing data. It is gcp_billing_ concatenated with the name of your Google Billing account (dashes in the billing account ID being replaced by underscores). For example, if your Google Billing Account ID is 006C3F-3613C3-2A2169, the dataset name should be gcp_billing_006C3F_3613C3_2A2169.
BQ_table_name: The name of the BigQuery table that contains the exported Cloud Billing data. It is doitintl_billing_export_resource_v1_XXXXXX_XXXXXX_XXXXXX_view, where XXXXXX_XXXXXX_XXXXXX is your billing account ID with dashes being replaced by underscores.

VIEW schema

The structure of the the DoiT billing export table is different from that of a regular GCP billing export table.

Below is the schema of the DoiT billing export table.

Tip

Most fields in the DoiT billing export table are the same as those described at Google Cloud Docs: Structure of Detailed data export. DoiT-specific fields are indicated in the field descriptions.

Field	Type	Description
`billing_account_id`	string	The Cloud Billing account ID that the usage is associated with.
`project_id`	string	The ID of the Google Cloud project that generated the Cloud Billing data. The same as `project.id`.
`service_description`	string	The Google Cloud service that reported the Cloud Billing data.
`service_id`	string	The ID of the service that the usage is associated with.
`sku_description`	string	A description of the resource type used by the service.
`sku_id`	string	The ID of the resource used by the service. For the full list of SKUs, see Google Cloud SKUs.
`usage_start_time`	timestamp	The start time of the hourly usage window within which the given cost was calculated.
`usage_end_time`	timestamp	The end time of the hourly usage window within which the given cost was calculated.
`project`	record	`project` contains fields that describe the Cloud Billing project.
`project.id`	string	The ID of the Google Cloud project that generated the Cloud Billing data.
`project.number`	string	A unique identifier for the Google Cloud project that generated the Cloud Billing data.
`project.name`	string	The name of the Google Cloud project that generated the Cloud Billing data.
`project.labels`	record (repeated)	`project.labels` contains key:value pairs that comprise the labels on the Google Cloud project where the usage occurred.
`project.labels.key`	string	If project labels are present, the key portion of the key:value pair.
`project.labels.value`	string	If project labels are present, the value portion of the key:value pair.
`project.ancestry_numbers`	string	The ancestors in the resource hierarchy for the project identified by the specified `project.id`.
`project.ancestry_names`	string	(DoiT specific) The same as `project.ancestry_numbers` but in the folder name instead of the folder number.
`labels`	record (repeated)	`labels` contains key:value pairs that comprise the labels on the Google Cloud resource where the usage occurred.
`labels.key`	string	If labels are present, the key portion of the key:value pair.
`labels.value`	string	If labels are present, the value portion of the key:value pair.
`system_labels`	record (repeated)	`system_labels` contains key:value pairs that comprise the system-generated labels on the resource where the usage occurred.
`system_labels.key`	string	If system labels are present, the key portion of the key:value pair.
`system_labels.value`	string	If system labels are present, the value portion of the key:value pair .
`location`	record	`location` contains geographic information of usage.
`location.location`	string	Location of usage at the level of a multi-region, country, region, or zone; or global for resources that have no specific location.
`location.country`	string	The country of usage.
`location.region`	string	The region of usage.
`location.zone`	string	The zone of usage.
`export_time`	timestamp	A processing time associated with an append of Cloud Billing data.
`cost`	float	The cost of the usage before any credits, to a precision of up to six decimal places.
`currency`	string	The currency that the cost is billed in.
`currency_conversion_rate`	float	The exchange rate from US dollars to the local currency.
`usage`	record	`usage` contains fields that describe the quantity of usage units and usage pricing units.
`usage.amount`	float	The quantity of `usage.unit` used.
`usage.unit`	string	The base unit in which resource usage is measured.
`usage.amount_in_pricing_units`	float	The quantity of `usage.pricing_unit` used.
`usage.pricing_unit`	string	The unit in which resource usage is measured, according to the Google Cloud Billing Catalog API.
`credits`	record (repeated)	`credits` contains fields that describe the structure and value of the credits associated with Google Cloud and Google Maps Platform SKUs.
`credits.name`	string	A description of the credit applied to the Cloud Billing account.
`credits.amount`	float	The amount of the credit applied to the usage. Credits are always negative amounts.
`credits.full_name`	string	The name of the credit associated with the product SKU. A human-readable description of an alphanumeric `credits.id`.
`credits.id`	string	If present, indicates that a credit is associated with the product SKU.
`credits.type`	string	This field describes the purpose or origin of the `credits.id`. See Google Cloud Docs for details.
`invoice`	record	`invoice` contains fields that describe the general information about the invoice.
`invoice.month`	string	The year and month (YYYYMM) of the invoice that includes the cost line items.
`cost_type`	string	The type of cost this line item represents: regular, tax, adjustment, or rounding error.
`adjustment_info`	record	`adjustment_info` contains fields that describe the structure and value of an adjustment to cost line items associated with a Cloud Billing account.
`adjustment_info.id`	string	If present, indicates that an adjustment is associated with a cost line item.
`adjustment_info.description`	string	A description of the adjustment and its cause.
`adjustment_info.mode`	string	How the adjustment was issued. See Google Cloud Docs for details.
`adjustment_info.type`	string	The type of adjustment. See Google Cloud Docs for details.
`tags`	record (repeated)	(DoiT specific) `tags` contains fields that describe the resource structure of tags attached to the Google Cloud resource where the usage occurred. See Google Cloud Docs for more information about GCP tags.
`tags.key`	string	(DoiT specific) The key portion of the tag key:value pair.
`tags.value`	string	(DoiT specific) The value portion of the tag key:value pair.
`tags.inherited`	boolean	(DoiT specific) This field indicates whether the tag bindings are inherited from the parent resource in the Google Cloud resource hierarchy.
`resource_id`	string	(DoiT specific) A service-specific identifier for the resource that generated relevant usage. This is the `resource.name` field in Google's detailed usage cost data export.
`resource_global_id`	string	(DoiT specific) A globally unique service identifier for the resource that generated relevant usage. This is the `resource.global_name` field in Google's detailed usage cost data export.
`report`	record (repeated)	(DoiT specific) `report` contains fields that describe changes applied to your billing data by DoiT.
`report.cost`	float	(DoiT specific) The cost of the usage after applying DoiT discounts and/or custom pricing.
`report.usage`	float	(DoiT specific) The same as `usage.amount_in_pricing_units`.
`report.credit`	string	(DoiT specific) The same as `credits.name`. The amount of the credit is included in `report.cost`.

Table configuration

Review the table partitioning and clustering schema to allow query optimization.

Item	Setting
Table type	Partitioned
Partitioned by	Day
Partitioned on field	`export_time`
Partition filter	Required
Clustered by	`project_id`, `service_description`, `sku_description`

Sample queries

This section provides examples of how to query the DoiT billing export table.

Return daily cost breakdown by project, service, and SKU

The query below returns the cost (including credits) from the last month to date, with daily breakdown by project, service, and SKU.

DECLARE from_date DATE DEFAULT DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH);

SELECT
  DATE(usage_start_time, "America/Los_Angeles") AS usage_date,
  project_id,
  service_description,
  sku_description,
  SUM(report_val.cost) AS cost
FROM
  `doitintl-cmp-gcp-data.gcp_billing_XXXXXX_XXXXXX_XXXXXX.doitintl_billing_export_resource_v1_XXXXXX_XXXXXX_XXXXXX_view`
LEFT JOIN
  UNNEST(report) AS report_val
WHERE
  DATE(export_time) >= from_date
  AND DATE(usage_start_time, "America/Los_Angeles") >= from_date
GROUP BY
  usage_date, project_id, service_description, sku_description

Query with labels

The query below shows how to filter costs using labels. It returns cost breakdown for resources with the environment label of the previous month.

Note

The query reads all data that was exported between 2022-07-01 and 2022-08-10 to ensure all the usage data that belongs to July is included. For more information, see Google Cloud Docs: Differences between exported data and invoices.

WITH rows_with_label AS (
  SELECT
    l.value AS label_value,
    report
  FROM
    `doitintl-cmp-gcp-data.gcp_billing_XXX.doitintl_billing_export_resource_v1_XXX_view`
  LEFT JOIN
    UNNEST(labels) AS l
  WHERE
    DATE(export_time) BETWEEN "2022-07-01" AND "2022-08-10"
    AND DATE_TRUNC(DATE(usage_start_time, "America/Los_Angeles"), MONTH) = "2022-07-01"
    AND l.key = "environment" AND l.value IS NOT NULL
)

SELECT
  label_value,
  SUM(report_val.cost) AS cost
FROM
  rows_with_label
LEFT JOIN
  UNNEST(report) AS report_val
GROUP BY 1

Billing data export

Billing data​

DoiT billing export table​

Data availability​

Project, dataset, and table name​

VIEW schema​

Table configuration​

Sample queries​

Return daily cost breakdown by project, service, and SKU​

Query with labels​