Sample queries for the new CUDs data model

WITH
cost_dataAS(
SELECT*
FROMproject.dataset.gcp_billing_export_resource_v1_NNNNNN_NNNNNN_NNNNNN
WHEREinvoice.month='month'
),
cud_product_dataAS(
SELECT*FROMUNNEST(
[
STRUCT(
'Compute Engine Flexible CUDs'AScud_product,
'Commitment - dollar based v1: GCE'AScud_fee_regex,
'GCE Commitments'AScud_credit_regex)])
),
cud_costsAS(
SELECT
invoice.monthASinvoice_month,
cud_product_data.cud_product,
IFNULL(
(
SELECTl.value
FROMUNNEST(labels)l
WHEREl.key='goog-originating-service-id'
),
service.id)ASservice,
SUM(cost)AScost
FROM
cost_data
JOINcud_product_data
ON
REGEXP_CONTAINS(
sku.description,cud_fee_regex)
GROUPBY1,2,3
),
cud_creditsAS(
SELECT
invoice.monthASinvoice_month,
cud_product,
service.idASservice,
SUM(credit.amount)ASspend_cud_credits
FROM
cost_data,UNNEST(credits)AScredit
JOINcud_product_data
ON
REGEXP_CONTAINS(
credit.full_name,cud_credit_regex)
WHERE
credit.type='COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE'
GROUPBY1,2,3
)
SELECT
invoice_month,
cud_product,
costAscommitment_cost,
-1*(cost+IFNULL(spend_cud_credits,0))AScommitment_savings
FROMcud_costs
LEFTJOINcud_credits
USING(invoice_month,cud_product,service);

WITH
cost_dataAS(
SELECT*
FROMproject.dataset.gcp_billing_export_resource_v1_NNNNNN_NNNNNN_NNNNNN
WHEREinvoice.month='month'
),
cud_product_dataAS(
SELECT*FROMUNNEST(
[
STRUCT(
'Compute Engine Flexible CUDs'AScud_product,
'Commitment - dollar based v1: GCE'AScud_fee_regex,
'GCE Commitments'AScud_credit_regex)])
),
cud_commitment_amountAS(
SELECT
invoice.monthASinvoice_month,
cud_product_data.cud_product,
SUM(usage.amount_in_pricing_units/100)AScommitment_amount,
FROM
cost_data
JOINcud_product_data
ON
REGEXP_CONTAINS(
sku.description,cud_fee_regex)
GROUPBY1,2
),
cud_utilized_commitment_amountAS(
SELECT
invoice.monthASinvoice_month,
cud_product,
ABS(SUM(credit.amount/currency_conversion_rate))
ASutilized_commitment_amount
FROM
cost_data,UNNEST(credits)AScredit
JOINcud_product_data
ON
REGEXP_CONTAINS(
credit.full_name,cud_credit_regex)
WHERE
credit.type='COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE'
GROUPBY1,2
)
SELECT
invoice_month,
cud_product,
utilized_commitment_amount/commitment_amount*100AScommitment_utilization
FROMcud_commitment_amount
LEFTJOINcud_utilized_commitment_amount
USING(invoice_month,cud_product);

WITH
cost_dataAS(
SELECT*
FROMproject.dataset.gcp_billing_export_resource_v1_NNNNNN_NNNNNN_NNNNNN
WHEREinvoice.month='month'
),
cud_product_dataAS(
SELECT*FROMUNNEST(
[
STRUCT(
'Compute Engine Flexible CUDs'AScud_product,
'Commitment - dollar based v1: GCE'AScud_fee_regex,
'GCE Commitments'AScud_credit_regex)])
),
eligible_cud_skusAS(
SELECTsku_id
FROMexample_project.dataset.flex_cud_skus
),
eligible_cud_spendAS(
SELECT
invoice.monthASinvoice_month,
SUM(cost)AScost,
SUM(
IFNULL(
(
SELECTSUM(credit.amount)
FROMUNNEST(credits)AScredit
WHERE
credit.typeIN(
'COMMITTED_USAGE_DISCOUNT',
'COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE',
'DISCOUNT',
'FREE_TIER')
),
0))AScosts_ineligible_for_cud,
FROMcost_data
JOINeligible_cud_skus
ONsku.id=sku_id
GROUPBY1
),
cud_costsAS(
SELECT
invoice.monthASinvoice_month,
cud_product_data.cud_product,
IFNULL(
(
SELECTl.value
FROMUNNEST(labels)l
WHEREl.key='goog-originating-service-id'
),
service.id)ASservice,
SUM(cost)AScost
FROM
cost_data
JOINcud_product_data
ON
REGEXP_CONTAINS(
sku.description,cud_fee_regex)
GROUPBY1,2,3
),
cud_creditsAS(
SELECT
invoice.monthASinvoice_month,
SUM(credit.amount)ASspend_cud_credits
FROM
cost_data,UNNEST(credits)AScredit
WHERE
credit.type='COMMITTED_USAGE_DISCOUNT_DOLLAR_BASE'
ANDREGEXP_CONTAINS(credit.full_name,'GCE Commitments')
GROUPBY1
),
cud_savingsAS(
SELECT
invoice_month,
Cud_product,
spend_cud_creditsasspend_cud_credits,
-1*(cost+IFNULL(spend_cud_credits,0))AScommitment_savings
FROMcud_costs
LEFTJOINcud_credits
USING(invoice_month)
)
SELECT
Invoice_month,
commitment_savings*100
/(cost+costs_ineligible_for_cud-IFNULL(spend_cud_credits,0))
ASeffective_savings_rate
FROMeligible_cud_spend
LEFTJOINcud_savings
USING(invoice_month);

SETbigquery_billing_project=billing-project-id;
WITH
cost_dataAS(
SELECT*
FROM
project.dataset.gcp_billing_export_resource_v1_NNNNNN_NNNNNN_NNNNNN
WHEREinvoice.month='month'
),
cud_fee_skusAS(
SELECT*FROMUNNEST(
[
'5515-81A8-03A2',
'B22F-51BE-D599'])
fee_sku_id
),
cud_costsAS(
SELECT
invoice.monthASinvoice_month,
subscription.instance_idASsubscription_instance_id,
IFNULL(
(
SELECTl.value
FROMUNNEST(labels)l
WHEREl.key='goog-originating-service-id'
),
service.id)ASservice,
SUM(cost)AScommitment_cost,
SUM(
(
SELECTSUM(credit.amount)
FROMUNNEST(credits)credit
WHEREcredit.type='FEE_UTILIZATION_OFFSET'
))ASfee_utilization_offset
FROM
cost_data
JOINcud_fee_skus
ONfee_sku_id=sku.id
GROUPBY1,2,3
),
cud_savingsAS(
SELECT
invoice.monthASinvoice_month,
subscription.instance_id,
service.idASservice,
SUM(cost-cost_at_effective_price_default)AScud_savings_amount,
SUM(cost_at_effective_price_default)ASon_demand_costs
FROM
cost_data
WHERE
consumption_model.idISNOTNULL
ANDconsumption_model.idIN('D97B-0795-975B','70D7-D1AB-12A4')
GROUPBY1,2,3
)
SELECT
invoice_month,
subscription_instance_id,
service,
commitment_cost,
commitment_cost+fee_utilization_offset+IFNULL(cud_savings_amount,0)
AScommitment_savings,
ABS(fee_utilization_offset)/commitment_cost*100AScud_utilization_percent,
(commitment_cost+fee_utilization_offset+IFNULL(cud_savings_amount,0))
/IFNULL(on_demand_costs,1)*100ASeffective_savings_rate
FROMcud_costs
LEFTJOINcud_savings
USING(invoice_month,subscription_instance_id,service);

-- 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;