Sample queries for the new CUDs data model

Queries for 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 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 AS 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