Consultas de ejemplo del nuevo modelo de datos de CUDs

Importante: En esta página se explica el nuevo y mejorado programa de descuentos por compromiso de uso (CUDs), que se aplica a los clientes que compren sus primeros CUDs basados en el gasto a partir del 15 de julio del 2025. Los clientes pueden habilitar la nueva experiencia de CUD ahora. Para obtener más información, consulta Mejoras del programa de CUDs basados en el gasto.

Consultas de métricas de CUD

Puedes usar estas métricas de KPIs importantes para validar que tus sistemas funcionan correctamente con el nuevo modelo de datos:

1. Ahorro por compromiso de uso (en $): describe el ahorro que has conseguido gracias a tus compromisos. La métrica usa la fórmula (Cost of resources at on-demand rates - cost of resources with commitment discounts).
2. Descuentos por compromiso de uso (%): describe el porcentaje de ahorro que has conseguido gracias a tus compromisos. La métrica usa la fórmula (Commitment savings / costs of resources at on-demand rates)*100.
3. Utilización del compromiso (%): mide la eficacia con la que usas tus compromisos, expresada como porcentaje. La métrica usa la fórmula (Commitment applied to eligible spend / total commitment).

4. Tasa de ahorro efectiva (%): indica el retorno de la inversión (ROI) de los descuentos por compromiso. La métrica usa la fórmula (Commitment Savings / On-Demand Equivalent Spend).

   Para obtener información valiosa sobre sus datos de costes, las siguientes consultas de ejemplo de BigQuery muestran cómo obtener información útil sobre los siguientes KPIs.

Elige la consulta de ejemplo correcta

Para ayudarle a actualizar sus consultas de acuerdo con los cambios del modelo de datos, le ofrecemos dos versiones de las consultas de ejemplo de KPI. Elige una de estas opciones:

• Consultas de KPIs de ejemplo con el modelo de datos antiguo
• Consultas de KPIs de ejemplo con el nuevo modelo de datos

Consultas de KPIs de ejemplo con el modelo de datos antiguo

Usa estas consultas de ejemplo si no utilizas el nuevo modelo de datos.

Nota: Los cálculos de los KPIs en el formato de datos de exportación de BigQuery antiguo solo se pueden hacer a nivel de producto de CUD (subscription_definition). No son lo mismo que las nuevas métricas de indicadores clave de rendimiento que se usan a nivel de subs_instance

.

Estas consultas solo se aplican a los CUDs flexibles de Compute. Para consultar otros productos con CUD basados en el gasto, debe cambiar los siguientes valores:

• cud_product
• sku.description
• credit.type

Coste del CUD más el ahorro del CUD

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 es el año y el mes actuales en formato YYYYMM. Por ejemplo, "202504".

Uso confirmado

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 es el año y el mes actuales en formato YYYYMM, por ejemplo, "202504".

Tasa de ahorro efectiva

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 es el año y el mes actuales en formato YYYYMM, por ejemplo, "202504".

Consultas de KPIs de ejemplo con el nuevo modelo de datos

Usa esta consulta de ejemplo si has adoptado el nuevo modelo de datos.

Estas consultas solo se aplican a los CUDs flexibles de Compute. Para consultar otros productos con CUD basados en el gasto, debe cambiar los siguientes valores:

• 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 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 es el año y el mes actuales en formato YYYYMM, por ejemplo, "202504".

Consultar y analizar los CUDs flexibles de Compute históricos

La siguiente consulta te permite analizar tus CUDs históricos en una sola consulta. Detecta la fecha en la que habilitaste la función y gestiona los tipos de datos presentes en los modelos de CUD antiguos y nuevos. Para usar esta consulta, ya debes haber migrado al nuevo modelo de CUD.

Esta consulta solo es para los CUDs flexibles de Compute. Para consultar otros productos de CUD basados en el gasto, debes cambiar los siguientes valores:

• cud_product
• sku.description
• Credit.type
• Credit.full_name
• cud_fee_skus
• consumption_model.id

-- This query calculates both legacy and new model CUD KPIs, splitting the data by a migration event.
-- The migration event is defined as the first time the consumption_model.description is not 'Default'.
-- It calculates commitment cost, savings, utilization, and effective savings rate for both models.

WITH
 -- Determine the migration timestamp based on the first usage of a non-default consumption model
 migration_hour AS (
   SELECT
     MIN(t.usage_start_time) AS smallest_usage_start_time
   FROM
     `project.dataset.gcp_billing_export_resource_v1_NNNNNN_NNNNNN_NNNNNN` AS t
   WHERE
     t.consumption_model.description != 'Default'
 ),
 -- Filter for cost data that occurred before the migration
 legacy_cost_data AS (
   SELECT
    *
   FROM
     `project.dataset.gcp_billing_export_resource_v1_NNNNNN_NNNNNN_NNNNNN`
   WHERE
     usage_start_time < (
       SELECT
         smallest_usage_start_time
       FROM
         migration_hour
     )
 ),
 -- Filter for cost data that occurred at or after the migration
 new_cost_data AS (
   SELECT
    *
   FROM
     `project.dataset.gcp_billing_export_resource_v1_NNNNNN_NNNNNN_NNNNNN`
   WHERE
     usage_start_time >= (
       SELECT
         smallest_usage_start_time
       FROM
         migration_hour
     )
 ),
 -- Define CUD product metadata for matching fees and credits
 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)])
 ),
 -- =================================================================================================
 -- Part 1: Legacy Model Calculations (before migration)
 -- =================================================================================================
 legacy_commitment_costs AS (
   SELECT
     usage_start_time,
     pd.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
     legacy_cost_data
     JOIN cud_product_data AS pd ON REGEXP_CONTAINS(sku.description, pd.cud_fee_regex)
   GROUP BY
     1,
     2,
     3
 ),
 legacy_cud_credits AS (
   SELECT
     usage_start_time,
     pd.cud_product,
     service.id AS service,
     SUM(credit.amount) AS spend_cud_credits
   FROM
     legacy_cost_data,
     UNNEST(credits) AS credit
     JOIN cud_product_data AS pd ON REGEXP_CONTAINS(credit.full_name, pd.cud_credit_regex)
   WHERE
     credit.type = 'COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE'
   GROUP BY
     1,
     2,
     3
 ),
 legacy_commitment_savings AS (
   SELECT
     c.usage_start_time,
     c.cud_product,
     c.service,
     SUM(c.cost) AS commitment_cost,
     SUM(-1 * (c.cost + IFNULL(cr.spend_cud_credits, 0))) AS commitment_savings
   FROM
     legacy_commitment_costs AS c
     LEFT JOIN legacy_cud_credits AS cr USING (usage_start_time, cud_product, service)
   GROUP BY
     1,
     2,
     3
 ),
 legacy_commitment_amount AS (
   SELECT
     usage_start_time,
     pd.cud_product,
     SUM(usage.amount_in_pricing_units / 100) AS commitment_amount
   FROM
     legacy_cost_data
     JOIN cud_product_data AS pd ON REGEXP_CONTAINS(sku.description, pd.cud_fee_regex)
   GROUP BY
     1,
     2
 ),
 legacy_utilized_commitment AS (
   SELECT
     usage_start_time,
     pd.cud_product,
     ABS(SUM(credit.amount / currency_conversion_rate)) AS utilized_commitment_amount
   FROM
     legacy_cost_data,
     UNNEST(credits) AS credit
     JOIN cud_product_data AS pd ON REGEXP_CONTAINS(credit.full_name, pd.cud_credit_regex)
   WHERE
     credit.type = 'COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE'
   GROUP BY
     1,
     2
 ),
 legacy_cud_utilization AS (
   SELECT
     ca.usage_start_time,
     ca.cud_product,
     SAFE_DIVIDE(uc.utilized_commitment_amount, ca.commitment_amount) * 100 AS cud_utilization_percent
   FROM
     legacy_commitment_amount AS ca
     LEFT JOIN legacy_utilized_commitment AS uc USING (usage_start_time, cud_product)
 ),
 eligible_cud_skus AS (
   SELECT
     sku_id
   FROM
     UNNEST([ /* Insert the full list of CUD eligible SKUs 'F35A-5D39-DA9D', '7E09-0800-D3BA', '1641-654E-D130', 'D616-27D3-51E1'*/ ]) AS sku_id
 ),
 eligible_cud_spend AS (
   SELECT
     usage_start_time,
     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
     legacy_cost_data
     JOIN eligible_cud_skus ON sku.id = eligible_cud_skus.sku_id
   GROUP BY
     1
 ),
 total_cud_savings AS (
   SELECT
     c.usage_start_time,
     -1 * (c.cost + IFNULL(cr.spend_cud_credits, 0)) AS commitment_savings,
     cr.spend_cud_credits
   FROM (
     SELECT
       usage_start_time,
       SUM(cost) AS cost
     FROM
       legacy_cost_data
       JOIN cud_product_data pd ON REGEXP_CONTAINS(sku.description, pd.cud_fee_regex)
     GROUP BY
       1
   ) AS c
     LEFT JOIN (
       SELECT
         usage_start_time,
         SUM(credit.amount) AS spend_cud_credits
       FROM
         legacy_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
     ) AS cr USING (usage_start_time)
 ),
 -- =================================================================================================
 -- Part 2: New Model Calculations (at or after migration)
 -- =================================================================================================
 new_model_commitment_costs AS (
   SELECT
     usage_start_time, -- Changed from 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
     new_cost_data
     JOIN (
       SELECT
        *
       FROM
         UNNEST(['5515-81A8-03A2', 'B22F-51BE-D599']) fee_sku_id
     ) AS cud_fee_skus ON fee_sku_id = sku.id
   GROUP BY
     1,
     2,
     3
 ),
 new_model_cud_savings AS (
   SELECT
     usage_start_time, -- Changed from 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
     new_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
 ),
 -- =================================================================================================
 -- Final Combination
 -- =================================================================================================
 legacy_kpis AS (
   SELECT
     cs.usage_start_time,
     'legacy' AS model_version,
     CAST(NULL AS STRING) AS subscription_instance_id,
     cs.cud_product,
     cs.service,
     cs.commitment_cost,
     cs.commitment_savings,
     u.cud_utilization_percent,
     NULL AS effective_savings_rate
   FROM
     legacy_commitment_savings AS cs
     LEFT JOIN legacy_cud_utilization AS u USING (usage_start_time, cud_product)
   UNION ALL
   SELECT
     es.usage_start_time,
     'legacy' AS model_version,
     CAST(NULL AS STRING) AS subscription_instance_id,
     NULL AS cud_product,
     NULL AS service,
     NULL AS commitment_cost,
     NULL AS commitment_savings,
     NULL AS cud_utilization_percent,
     SAFE_DIVIDE(s.commitment_savings, (es.cost + es.costs_ineligible_for_cud - IFNULL(s.spend_cud_credits, 0))) * 100 AS effective_savings_rate
   FROM
     eligible_cud_spend AS es
     LEFT JOIN total_cud_savings AS s USING (usage_start_time)
 ),
 new_kpis AS (
   SELECT
     ncc.usage_start_time,
     'new' AS model_version,
     CAST(ncc.subscription_instance_id AS STRING) AS subscription_instance_id,
     CAST(NULL AS STRING) AS cud_product,
     ncc.service,
     ncc.commitment_cost,
     ncc.commitment_cost + ncc.fee_utilization_offset + IFNULL(ncs.cud_savings_amount, 0) AS commitment_savings,
     SAFE_DIVIDE(ABS(ncc.fee_utilization_offset), ncc.commitment_cost) * 100 AS cud_utilization_percent,
     SAFE_DIVIDE((ncc.commitment_cost + ncc.fee_utilization_offset + IFNULL(ncs.cud_savings_amount, 0)), IFNULL(ncs.on_demand_costs, 1)) * 100 AS effective_savings_rate
   FROM
     new_model_commitment_costs AS ncc
     LEFT JOIN new_model_cud_savings AS ncs USING (usage_start_time, subscription_instance_id, service)
 )
SELECT
*
FROM
 legacy_kpis
UNION ALL
SELECT
*
FROM
 new_kpis;

Temas relacionados

• Mejoras en el programa de CUDs basados en la inversión
• Cambios en el modelo de datos de los DUCs basados en la inversión
• Plazos para la migración al nuevo modelo de CUD
• Verificar los descuentos después de la migración
• Elegir la cantidad correcta de CUD que se va a comprar
• SKUs, ofertas e IDs de modelo de consumo de CUD migrados
• Mejoras en la interfaz de usuario de facturación