Cette page a été traduite par l'API Cloud Translation.

Exemples de requêtes pour le nouveau modèle de données des remises sur engagement d'utilisation

Requêtes pour les KPI de remises sur engagement d'utilisation

Vous pouvez utiliser ces métriques de KPI importantes pour vérifier que vos systèmes fonctionnent correctement avec le nouveau modèle de données :

Économies grâce aux engagements (en $) : décrit les économies réalisées grâce à vos engagements. La métrique utilise la formule (Cost of resources at on-demand rates - cost of resources with commitment discounts).
Économies grâce aux engagements (%) : pourcentage d'économies réalisées grâce à vos engagements. La métrique utilise la formule (Commitment savings / costs of resources at on-demand rates)*100.
Utilisation de l'engagement (%) : mesure l'efficacité avec laquelle vous utilisez vos engagements, exprimée en pourcentage. La métrique utilise la formule (Commitment applied to eligible spend / total commitment).
Taux d'économies effectif (%) : indique le retour sur investissement (ROI) pour les remises sur engagement. La métrique utilise la formule (Commitment Savings / On-Demand Equivalent Spend).

Pour mieux comprendre vos données de coûts, les exemples de requêtes BigQuery suivants montrent comment récupérer des informations utiles pour les KPI suivants.

Choisir l'exemple de requête approprié

Pour vous aider à mettre à jour vos requêtes en fonction des modifications apportées au modèle de données, nous vous proposons deux versions des exemples de requêtes de KPI. Choisissez l'une des options suivantes :

Exemples de requêtes de KPI utilisant l'ancien modèle de données
Exemples de requêtes de KPI utilisant le nouveau modèle de données

Exemples de requêtes de KPI utilisant l'ancien modèle de données

Utilisez ces exemples de requêtes si vous n'utilisez pas le nouveau modèle de données.

Ces requêtes ne concernent que les remises sur engagement d'utilisation flexibles de Compute Engine. Pour interroger d'autres produits CUD basés sur les dépenses, vous devez modifier les valeurs suivantes :

cud_product
sku.description
credit.type

Coût des remises sur engagement d'utilisation plus économies associées

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 correspond à l'année et au mois en cours au format YYYYMM, par exemple "202504".

Utilisation de l'engagement

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 correspond à l'année et au mois en cours au format YYYYMM, par exemple "202504".

Taux d'épargne effectif

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 correspond à l'année et au mois en cours au format YYYYMM, par exemple "202504".

Exemples de requêtes de KPI utilisant le nouveau modèle de données

Utilisez cet exemple de requête si vous avez adopté le nouveau modèle de données.

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 correspond à l'année et au mois en cours au format YYYYMM, par exemple "202504".

Interroger et analyser les remises sur engagement d'utilisation flexibles Compute historiques

La requête suivante vous permet d'analyser vos remises sur engagement d'utilisation historiques dans une seule requête. Il détecte votre date d'activation et gère les types de données présents dans les anciens et nouveaux modèles de CUD. Pour utiliser cette requête, vous devez déjà avoir migré vers le nouveau modèle de remise sur engagement d'utilisation.

Cette requête ne concerne que les remises sur engagement d'utilisation flexibles de Compute Engine. Pour interroger d'autres produits CUD basés sur les dépenses, vous devez modifier les valeurs suivantes :

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;