Spend-based CUDs data model updates

As part of the committed use discounts (CUDs) program expansion, we update the spend-based CUD model and provide tools to help you prepare for the changes, which include:

• BigQuery sample data export: A sample dataset that demonstrates how opting in changes the appearance of your spend-based CUD data exports in BigQuery.
• CUD KPI example queries: Example queries to use with the BigQuery sample data export to calculate important CUD key performance indicators (KPIs).
• New CUD details: Descriptions of new CUDs fields and data migration, for example new CUD Fee SKUs IDs, offer names, and consumption model IDs.

BigQuery sample data export

You can use the BigQuery sample data export to prepare your internal systems for the changes that occur in your spend-based CUD data. The process to use the sample data export has these main steps:

1. Check the prerequisites.
2. Enable the sample data export.
3. Allow the new data to accumulate.
4. Explore the new data model and queries.
5. Update your internal systems and workflows accordingly.

Prerequisites

You must meet the following prerequisites to use the sample data export:

• You must have a detailed or standard billing data export configured for your Cloud Billing account. For more information, see Set up Cloud Billing data export to BigQuery.

• You must have permissions on the project that owns the export, and permissions on the Cloud Billing account where you are enabling the export. For example:

  • bigquery.datasets.create permission on the project that contains the dataset.
  • billing.accounts.getUsageExportSpec permission on the Cloud Billing account.

  To find predefined Cloud Billing roles that contain these permissions, for example Billing Account Viewer, Billing Account Costs Manager, or Billing Account Administrator, see Cloud Billing access control and permissions. For more information about BigQuery-specific permissions, see BigQuery IAM roles and permissions.

• When you create a new Cloud Billing account, proportional attribution is enabled by default for spend-based commitments. Otherwise, you must have enabled it in order to use this export. You can do so by following these instructions.

• If you have VPC enabled for BigQuery resources on your project or organization, you need to create ingress and egress rules to properly enable this:

  • Create an ingress rule that gives the individual access to create the export:

  - ingressFrom:
      identities:
      - PRINCIPAL_ID_FOR_PERSON_ENABLING_SUBSCRIPTION
      sources:
      - accessLevel: "*"
  ingressTo:
      roles:
      - roles/bigquery.dataOwner
      resources:
      - projects/YOUR_PROJECT_ID_TO_HOST_SAMPLE_DATA
  title: 'Sample Export Ingress'
  

  • Create an egress rule to enable Google Cloud to create the BigQuery Linked Dataset within the VPC:

  - egressTo:
      roles:
      - roles/bigquery.dataOwner
      resources:
      - projects/710382390241
  egressFrom:
      identityType: ANY_IDENTITY
      sources:
      - accessLevel: "*"
      sourceRestriction: RESTRICTION_STATUS
  title: 'Sample Export Egress'
  

Enable the sample data export

To enable the sample data export, complete the following steps:

1. Open the Billing export section of the Google Cloud console.

   Go to Billing export

2. In the Billing export dialog, select the Cloud Billing account where you want to enable the sample data export, as shown in the following screen.

3. The data export process begins and takes approximately one day to be enabled. You'll see the following note until it is ready: After you enable the sample data export, it starts collecting Cloud Billing data, with new data added continuously until January 2026. Allow adequate time for sufficient data to accumulate in the export before updating your systems to align with the new data model.

4. When the export is ready, you'll see the following notification in the Billing section of the Google Cloud console:

   The data export is created as a linked dataset within the same BigQuery dataset that holds your detailed or standard billing data export. Because it is a linked dataset, you won't incur additional charges for the sample export. For more information, see Introduction to BigQuery sharing.

5. Click View Sample Dataset to open BigQuery in the Google Cloud console, where you can run queries to understand your important CUD KPIs.

Sample export limitations

The sample data export is a useful tool to prepare for the data model changes, but differs from real-world data exports in these important ways:

• Post-migration: Do not use the sample exports after you opt in to the new data model, because after that point the sample exports no longer be accurate.
• Output size: Due to data aggregation difference, the size of the sample export might vary from the actual export that you see after you opt in to these changes.
• Rounding methods: Due to rounding method differences, small discrepancies might occur in very small amounts or non-USD currencies.
• Prorated fees: The sample export might overestimate costs for the first and last hour of a CUD purchase, because it doesn't account for partial-hour commitment fees in the same way. Purchasing a spend-based CUD prorates the fee for the first hour.

Example data export before and after the new CUD model

The new spend-based CUD model requires you to plan and adjust your internal systems that might consume Cloud Billing data. As a result, we provide the following scenarios to show how the data export schema and data change, before and after the new CUD model. We further divide these scenarios into situations where you overutilize and underutilize your CUDs to show the affect on the data export.

For both scenarios, consider that you've purchased an E2-Standard-8 VM in US Central 1, consisting of two SKUs for RAM and Core. These SKUs use the fictional ID of RAM SKU and Core SKU, respectively.

Then, you purchase a 1 Year GCE Flex CUD for $0.1/hr for the overutilized scenario and $.3/hr for the underutilized scenario. These are represented in the data as the fictional ID Fee SKU.

Overutilized CUD scenario

In the overutilized scenario, you made the previously mentioned purchases and overutilized the CUDs.

Data before

Before the new CUD model, your Cloud Billing export schema and data values look like the following table.

^{1. This column represents the value of the goog-originating-sku-id label.}

Data After

After the new CUD model, your Cloud Billing export schema and data values look like the following table.

^{1. This column represents the value of the goog-originating-sku-id label.}

Note the following in this new CUD model:

• There are two rows for each CUD, instead of one for each.
• There is a new consumption_model.description column that separates the additional CUD entries, where:
  • the Compute Flexible CUDs 1 Year value indicates that you received the expected CUD discount.
  • the Default value indicates that you overutilized the CUD, and your cost reverted to the default pricing for the overage amount. This is also indicated by the subscription.instance_id having no value.
  • the CUD fee rows also have the Default value, because discounts don't apply to them. Instead, the credits field indicates that a negative offset was applied to negate the fee.

Underutilized CUD scenario

For this underutilized scenario, we assume you made the previously mentioned purchases and underutilized the CUDs.

Data before

Before the new CUD model, your Cloud Billing export schema and data values look like the following table.

^{1. This column represents the value of the goog-originating-sku-id label.}

Data After

After the new CUD model, your Cloud Billing export schema and data values look like the following table.

^{1. This column represents the value of the goog-originating-sku-id label.}

Note the following in this new CUD model:

• There are two rows for each CUD, instead of one for each.
• There is a new consumption_model.description column that separates the additional CUD entries, where:
  • the Compute Flexible CUDs 1 Year value indicates that you received the expected CUD discount.
  • the Default value indicates the CUD fee rows, because discounts don't apply to them. Instead, the credits field indicates that a negative offset was applied to negate the fees, which were rolled up into the first row.
• The first row shows a sum of the CUD fees.

Sample Queries for key CUD KPIs

You can use these important KPI metrics to validate that your systems are functioning well with the new data model:

1. Commitment savings ($): Describes the savings that resulted from your commitments. The metric uses the formula (Cost of resources at on-demand rates - cost of resources with commitment discounts).
2. Commitment savings (%): Describes the savings percentage that resulted from your commitments. The metric uses the formula (Commitment savings / costs of resources at on-demand rates)*100.
3. Commitment utilization (%): Measures how effectively you use your commitments, expressed as a percentage. The metric uses the formula (Commitment applied to eligible spend / total commitment).

4. Effective savings rate (%): Explains the return on investment (ROI) for commitment discounts. The metric uses the formula (Commitment Savings / On-Demand Equivalent Spend).

   To gain better insight into your cost data, the following BigQuery sample queries show how to retrieve useful information for the following KPIs.

Choose the correct sample query

To help you update your queries for the changes to the data model, we provide two versions of the KPI sample queries. Choose one of the following:

• Sample KPI queries using the legacy data model
• Sample KPI queries using the new data model

Sample KPI queries using the legacy data model

Use these sample queries if you aren't using the new data model.

These queries are only for Compute flexible CUDs. To query for other spend-based CUD products, you must change the following values:

• cud_product
• sku.description
• credit.type

CUD cost plus CUD savings

WITH
 cost_data AS (
   SELECT *
   FROM project.dataset.gcp_billing_export_resource_v1_NNNNNN_NNNNNN_NNNNNN
   WHERE invoice.month = 'month'
 ),
 cud_product_data AS (
   SELECT * FROM UNNEST(
     [
       STRUCT(
         'Compute Engine Flexible CUDs' AS cud_product,
         'Commitment - dollar based v1: GCE' AS cud_fee_regex,
         'GCE Commitments' AS cud_credit_regex)])
 ),
 cud_costs AS (
   SELECT
     invoice.month AS invoice_month,
     cud_product_data.cud_product,
     IFNULL(
       (
         SELECT l.value
         FROM UNNEST(labels) l
         WHERE l.key = 'goog-originating-service-id'
       ),
       service.id) AS service,
     SUM(cost) AS cost
   FROM
     cost_data
   JOIN cud_product_data
     ON
       REGEXP_CONTAINS(
         sku.description, cud_fee_regex)
   GROUP BY 1, 2, 3
 ),
 cud_credits AS (
   SELECT
     invoice.month AS invoice_month,
     cud_product,
     service.id AS service,
     SUM(credit.amount) AS spend_cud_credits
   FROM
     cost_data, UNNEST(credits) AS credit
   JOIN cud_product_data
     ON
       REGEXP_CONTAINS(
         credit.full_name, cud_credit_regex)
   WHERE
     credit.type = 'COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE'
   GROUP BY 1, 2, 3
 )
SELECT
 invoice_month,
 cud_product,
 cost As commitment_cost,
 -1 * (cost + IFNULL(spend_cud_credits, 0)) AS commitment_savings
FROM cud_costs
LEFT JOIN cud_credits
 USING (invoice_month, cud_product, service);

• month is the current year and month in YYYYMM format, for example '202504'.

Commitment utilization

WITH
 cost_data AS (
   SELECT *
   FROM project.dataset.gcp_billing_export_resource_v1_NNNNNN_NNNNNN_NNNNNN
   WHERE invoice.month = 'month'
 ),
 cud_product_data AS (
   SELECT * FROM UNNEST(
     [
       STRUCT(
         'Compute Engine Flexible CUDs' AS cud_product,
         'Commitment - dollar based v1: GCE' AS cud_fee_regex,
         'GCE Commitments' AS cud_credit_regex)])
),
 cud_commitment_amount AS (
   SELECT
     invoice.month AS invoice_month,
     cud_product_data.cud_product,
     SUM(usage.amount_in_pricing_units / 100) AS commitment_amount,
   FROM
     cost_data
   JOIN cud_product_data
     ON
       REGEXP_CONTAINS(
         sku.description, cud_fee_regex)
   GROUP BY 1, 2
 ),
 cud_utilized_commitment_amount AS (
   SELECT
     invoice.month AS invoice_month,
     cud_product,
     ABS(SUM(credit.amount / currency_conversion_rate))
       AS utilized_commitment_amount
   FROM
     cost_data, UNNEST(credits) AS credit
   JOIN cud_product_data
     ON
       REGEXP_CONTAINS(
         credit.full_name, cud_credit_regex)
   WHERE
     credit.type = 'COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE'
   GROUP BY 1, 2
 )
SELECT
 invoice_month,
 cud_product,
 utilized_commitment_amount / commitment_amount *100 AS commitment_utilization
FROM cud_commitment_amount
LEFT JOIN cud_utilized_commitment_amount
 USING (invoice_month, cud_product);

• month is the current year and month in YYYYMM format, for example '202504'.

Effective savings rate

WITH
 cost_data AS (
   SELECT *
   FROM project.dataset.gcp_billing_export_resource_v1_NNNNNN_NNNNNN_NNNNNN
   WHERE invoice.month = 'month'
 ),
 cud_product_data AS (
   SELECT * FROM UNNEST(
     [
       STRUCT(
         'Compute Engine Flexible CUDs' AS cud_product,
         'Commitment - dollar based v1: GCE' AS cud_fee_regex,
         'GCE Commitments' AS cud_credit_regex)])
 ),
 eligible_cud_skus AS (
   SELECT sku_id
   FROM example_project.dataset.flex_cud_skus
 ),
 eligible_cud_spend AS (
   SELECT
     invoice.month AS invoice_month,
     SUM(cost) AS cost,
     SUM(
       IFNULL(
         (
           SELECT SUM(credit.amount)
           FROM UNNEST(credits) AS credit
           WHERE
             credit.type IN (
               'COMMITTED_USAGE_DISCOUNT',
               'COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE',
               'DISCOUNT',
               'FREE_TIER')
         ),
         0)) AS costs_ineligible_for_cud,
   FROM cost_data
   JOIN eligible_cud_skus
     ON sku.id = sku_id
   GROUP BY 1
 ),
 cud_costs AS (
   SELECT
     invoice.month AS invoice_month,
     cud_product_data.cud_product,
     IFNULL(
       (
         SELECT l.value
         FROM UNNEST(labels) l
         WHERE l.key = 'goog-originating-service-id'
       ),
       service.id) AS service,
     SUM(cost) AS cost
   FROM
     cost_data
   JOIN cud_product_data
     ON
       REGEXP_CONTAINS(
         sku.description, cud_fee_regex)
   GROUP BY 1, 2, 3
 ),
 cud_credits AS (
   SELECT
     invoice.month AS invoice_month,
     SUM(credit.amount) AS spend_cud_credits
   FROM
     cost_data, UNNEST(credits) AS credit
   WHERE
     credit.type = 'COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE'
     AND REGEXP_CONTAINS(credit.full_name, 'GCE Commitments')
   GROUP BY 1
 ),
cud_savings AS (
  SELECT
   invoice_month,
   Cud_product,
   spend_cud_credits as spend_cud_credits,
   -1 * (cost + IFNULL(spend_cud_credits, 0)) AS commitment_savings
FROM cud_costs
LEFT JOIN cud_credits
 USING (invoice_month)
)
SELECT
 Invoice_month,
 commitment_savings * 100
   / (cost + costs_ineligible_for_cud - IFNULL(spend_cud_credits, 0))
   AS effective_savings_rate
FROM eligible_cud_spend
LEFT JOIN cud_savings
 USING (invoice_month);

• month is the current year and month in YYYYMM format, for example '202504'.

Sample KPI queries using the new data model

Use this sample query if you have adopted the new data model.

These queries are only for Compute flexible CUDs. To query for other spend-based CUD products, you must change the following values:

• cud_fee_skus
• consumption_model.id

SET bigquery_billing_project = billing-project-id;

WITH
  cost_data AS (
    SELECT *
    FROM
      project.dataset.gcp_billing_export_resource_v1_NNNNNN_NNNNNN_NNNNNN
    WHERE invoice.month = 'month'
  ),
  cud_fee_skus AS (
    SELECT * FROM UNNEST(
      [
        '5515-81A8-03A2',
        'B22F-51BE-D599'])
      fee_sku_id
  ),
  cud_costs AS (
    SELECT
      invoice.month AS invoice_month,
      subscription.instance_id AS subscription_instance_id,
      IFNULL(
        (
          SELECT l.value
          FROM UNNEST(labels) l
          WHERE l.key = 'goog-originating-service-id'
        ),
        service.id) AS service,
      SUM(cost) AS commitment_cost,
      SUM(
        (
          SELECT SUM(credit.amount)
          FROM UNNEST(credits) credit
          WHERE credit.type = 'FEE_UTILIZATION_OFFSET'
        )) AS fee_utilization_offset
    FROM
      cost_data
    JOIN cud_fee_skus
      ON fee_sku_id = sku.id
    GROUP BY 1, 2, 3
  ),
  cud_savings AS (
    SELECT
      invoice.month AS invoice_month,
      subscription.instance_id,
      service.id AS service,
      SUM(cost - cost_at_effective_price_default) AS cud_savings_amount,
      SUM(cost_at_effective_price_default) AS on_demand_costs
    FROM
      cost_data
    WHERE
      consumption_model.id IS NOT NULL
      AND consumption_model.id IN ('D97B-0795-975B','70D7-D1AB-12A4')
    GROUP BY 1, 2, 3
  )
SELECT
  invoice_month,
  subscription_instance_id,
  service,
  commitment_cost,
  commitment_cost + fee_utilization_offset + IFNULL(cud_savings_amount, 0)
    AS commitment_savings,
  ABS(fee_utilization_offset) / commitment_cost * 100 AS cud_utilization_percent,
  (commitment_cost + fee_utilization_offset + IFNULL(cud_savings_amount, 0))
    / IFNULL(on_demand_costs, 1) * 100 AS effective_savings_rate
FROM cud_costs
LEFT JOIN cud_savings
  USING (invoice_month, subscription_instance_id, service);

• month is the current year and month in YYYYMM format, for example '202504'.

Cloud Billing export to BigQuery

The Cloud Billing export to BigQuery standard, detailed and rebilling (reseller only) data export, add or change the following fields:

Price export changes

Cloud Billing export to BigQuery adds or changes these fields for pricing information:

New CUD product information

New CUD fee SKUs replace the existing CUD fee SKUs, and new offer IDs and consumption model IDs apply to all in-scope CUDs. You can use the following details to help you adjust your queries and dashboards.

Offers and consumption model ID migration

The following table shows the Offers and Consumption model IDs that will migrate from the old to the new data model.

CUD Fee SKU ID migration

The following tables show the CUD fee SKU IDs that migrate from the old to the new data model, per product.

Cloud Run

Bigtable

Dataflow

Memorystore For Redis

Cloud Spanner

Kubernetes Engine

AlloyDB For PostgreSQL

Cloud SQL

Compute Flexible

Managed Service For Apache Kafka

BigQuery

Cloud Firestore

Backup and DR for Oracle

Related topics

• Learn about spend-based CUDs program improvements.