Monitor the consumption of contract commitments across billing periods to track progress toward fulfilling contractual obligations and identify potential under-utilization risks.
Column names from the source use case.
ContractApplied and its ContractCommitmentID / ContractCommitmentAppliedCost elements are absent from the v1.0 sample, so contract-commitment burn-down cannot be tracked.
SELECT CU.BillingPeriodStart, CU.ServiceProviderName, CU.BillingAccountId, JSON_VALUE(CA, '$.ContractCommitmentID') AS ContractCommitmentId, SUM(CAST(JSON_VALUE(CA, '$.ContractCommitmentAppliedCost') AS FLOAT64)) AS AppliedCost, SUM(CU.EffectiveCost) AS TotalEffectiveCost FROM focus_data_table CU CROSS JOIN UNNEST(JSON_EXTRACT_ARRAY(CU.ContractApplied, '$.Elements')) AS CA WHERE JSON_VALUE(CA, '$.ContractCommitmentID') IS NOT NULL AND BillingPeriodStart >= ? AND BillingPeriodStart < ? GROUP BY CU.BillingPeriodStart, CU.ServiceProviderName, CU.BillingAccountId, JSON_VALUE(CA, '$.ContractCommitmentID') ORDER BY BillingPeriodStart, ContractCommitmentId