Track contract commitment burn-down over time

FOCUS Versions

v1.3 v1.4

Context

Monitor the consumption of contract commitments across billing periods to track progress toward fulfilling contractual obligations and identify potential under-utilization risks.

FOCUS SQL Query

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