Free resource

BilledCost vs EffectiveCost in FOCUS, with SQL

What each column means, how a commitment purchase and its amortization appear in FOCUS data, the SQL to reconcile them, the mistakes that make $0 rows look free, and where AWS, Azure and Google Cloud differ.

By Adam Morad · Reviewed October 1, 2026 · Column names: FOCUS 1.2 (what AWS and Microsoft export today); rules cross-checked against FOCUS 1.4

The short version

BilledCost is what the invoice says you owe for a charge in a billing period. EffectiveCost is what the charge cost once prepaid commitments are spread over the usage they cover. For on-demand usage with no commitment they are the same number. They diverge in exactly one situation that matters a lot: commitments (Savings Plans, reservations, committed-use discounts).

The FOCUS 1.4 specification states the rules directly. BilledCost “MUST be 0 for charges that are fully covered by one or more covering charges.” EffectiveCost “MUST include any portion of the BilledCost of covering purchase charges … that is applied to this charge,” and “MUST be 0 when ChargeCategory is ‘Purchase’ and the purchase is intended to cover related eligible charges.” For Tax and Credit rows, the two “MUST” be equal. FOCUS 1.2 described the same idea as amortization: EffectiveCost is the “amortized cost of the charge after applying all reduced rates, discounts, and the applicable portion of relevant, prepaid purchases.”

How a commitment looks in FOCUS rows

Take a one-year, all-upfront, spend-based commitment bought on January 1 for $8,760, which works out to $24.00 per day. It covers a VM fleet whose list price is $33.60 per day. On July 1 the fleet is cut in half. The data is synthetic; the row pattern follows the FOCUS 1.4 appendix examples.

Three things follow. The money leaves on the purchase row, where EffectiveCost is 0. Every covered usage row has a BilledCost of 0, because it was already paid for. And once the fleet shrinks, the half of each day’s commitment nobody consumes appears as its own Unused row, with no resource attached.

Run it yourself

The SQL below builds the whole year (550 rows) and runs five checks. It is plain SQL that runs unchanged in DuckDB and PostgreSQL. We ran it on DuckDB v1.5.3 and PostgreSQL 16.14 on October 1, 2026, and both returned identical results.

Download the SQL (.sql, CC BY 4.0)

CREATE TEMP TABLE focus_example AS
WITH days AS (
  SELECT CAST(d AS DATE) AS day
  FROM generate_series(DATE '2026-01-01', DATE '2026-12-31', INTERVAL '1 day') AS t(d)
),
purchase AS (
  SELECT
    DATE '2026-01-01'          AS "ChargePeriodStart",
    'Purchase'                 AS "ChargeCategory",
    'One-Time'                 AS "ChargeFrequency",
    CAST(NULL AS VARCHAR)      AS "ResourceId",
    CAST(NULL AS VARCHAR)      AS "CommitmentDiscountStatus",
    CAST(NULL AS DECIMAL(12,2)) AS "ListCost",
    CAST(8760.00 AS DECIMAL(12,2)) AS "BilledCost",
    CAST(0.00 AS DECIMAL(12,2))    AS "EffectiveCost"
),
used AS (
  SELECT
    day,
    'Usage', 'Usage-Based', 'vm-fleet-01', 'Used',
    CAST(CASE WHEN day < DATE '2026-07-01' THEN 33.60 ELSE 16.80 END AS DECIMAL(12,2)),
    CAST(0.00 AS DECIMAL(12,2)),
    CAST(CASE WHEN day < DATE '2026-07-01' THEN 24.00 ELSE 12.00 END AS DECIMAL(12,2))
  FROM days
),
unused AS (
  SELECT
    day,
    'Usage', 'Usage-Based', CAST(NULL AS VARCHAR), 'Unused',
    CAST(NULL AS DECIMAL(12,2)),
    CAST(0.00 AS DECIMAL(12,2)),
    CAST(12.00 AS DECIMAL(12,2))
  FROM days
  WHERE day >= DATE '2026-07-01'
)
SELECT * FROM purchase
UNION ALL SELECT * FROM used
UNION ALL SELECT * FROM unused;

Over the term they agree; by month they do not

-- Q1. Over the full term the two columns agree; that is the point of amortization.
SELECT
  SUM("BilledCost")    AS billed_cost,
  SUM("EffectiveCost") AS effective_cost
FROM focus_example;

-- Q2. By month they do not. BilledCost is the invoice (all in January); EffectiveCost is the
-- cost recognized as the commitment is consumed (flat every day of the term).
SELECT
  EXTRACT(MONTH FROM "ChargePeriodStart") AS month,
  SUM("BilledCost")    AS billed_cost,
  SUM("EffectiveCost") AS effective_cost
FROM focus_example
GROUP BY 1
ORDER BY 1;

Q1 returns 8,760.00 for both columns. Over the full term, amortization moves cost between periods but never creates or removes it. Q2 shows the difference that matters for reporting (selected months below):

A BilledCost trend shows a $8,760 spike in January and nothing after. That matches the invoice and is right for cash and accounts payable. An EffectiveCost trend shows a steady $672 to $744 a month, which is what running the fleet actually cost. Use EffectiveCost for run-rate, showback, forecasting and unit economics. Use BilledCost when reconciling to the invoice.

Common interpretation mistakes

-- Q3. Mistake: "usage cost" from BilledCost. Every covered usage row has BilledCost = 0, so
-- this reports the fleet as free all year.
SELECT SUM("BilledCost") AS usage_billed_cost
FROM focus_example
WHERE "ChargeCategory" = 'Usage';

1. Treating covered usage as free. Q3 returns 0.00: filter to Usage and sum BilledCost, and the fleet looks free all year. Any cost-per-unit or team showback built this way is wrong by the full value of the commitment.

2. Mixing the two lenses in one total. Adding the purchase row’s BilledCost to the usage rows’ EffectiveCost counts the commitment twice ($17,520 instead of $8,760). Pick one column per question and use it for every row.

-- Q4. Unused commitment: paid for, consumed by nothing. These rows carry no ResourceId, so an
-- allocation that groups by resource silently drops them.
SELECT
  "CommitmentDiscountStatus",
  COUNT(*)              AS row_count,
  SUM("EffectiveCost")  AS effective_cost
FROM focus_example
WHERE "ChargeCategory" = 'Usage'
GROUP BY 1
ORDER BY 1;

3. Losing Unused rows in allocation. Q4 returns 365 Used rows worth 6,552.00 and 184 Unused rows worth 2,208.00. The Unused rows have no ResourceId and often no team tag, so a showback grouped by resource or tag silently drops about a quarter of the commitment. Decide explicitly who carries unused commitment, and show it as its own line.

-- Q5. What did the commitment actually save? Compare list cost of the usage that ran with
-- everything the commitment cost (Used + Unused EffectiveCost). Comparing list cost with
-- the Used rows only overstates the saving.
SELECT
  SUM("ListCost") AS list_cost_of_usage,
  SUM(CASE WHEN "CommitmentDiscountStatus" = 'Used' THEN "EffectiveCost" ELSE 0 END) AS used_effective_cost,
  SUM("EffectiveCost") FILTER (WHERE "ChargeCategory" = 'Usage') AS total_commitment_effective_cost,
  SUM("ListCost") - SUM(CASE WHEN "CommitmentDiscountStatus" = 'Used' THEN "EffectiveCost" ELSE 0 END) AS saving_if_you_ignore_unused,
  SUM("ListCost") - SUM("EffectiveCost") FILTER (WHERE "ChargeCategory" = 'Usage') AS actual_saving
FROM focus_example;

4. Overstating savings. The usage that ran would have cost 9,172.80 at list price. Comparing that with the Used rows alone (6,552.00) suggests a saving of 2,620.80. But the commitment cost 8,760.00 in total, so the real saving was 412.80. Savings must include the Unused cost.

5. Assuming every provider emits the same rows. See the next section.

Where AWS, Azure and Google Cloud differ

The pattern above is the specification’s. Provider exports implement it with gaps, and the gaps are documented by the providers themselves (checked October 1, 2026):

• AWS exports a “FOCUS 1.2 with AWS columns” table, plus provider columns such as x_Discounts, x_Operation and x_ServiceCode. Its column dictionary still describes EffectiveCost as the amortized cost.
• Microsoft publishes a conformance summary for its “1.2-preview” dataset. Among the listed gaps: ContractedCost is 0 for some reservation usage (EA with cost allocation enabled, and all MCA reservation usage), ListCost is 0 for some reservation usage, and SkuId is null for savings plan unused charges. Do not compute “savings vs list” from these rows without checking.
• Google Cloud’s FOCUS export to BigQuery is in Preview. Its documented gaps include no “Purchase” or “Credit” ChargeCategory values and no CommitmentDiscountStatus or CommitmentDiscountId; committed-use discount effects are carried in the provider column x_Credits. The Used and Unused queries above return nothing on Google data.

Before trusting a cross-cloud total, check each provider’s export version and conformance notes, and test the queries on a month where you already know the answer.

Sources

• FOCUS 1.4 specification: BilledCost column
• FOCUS 1.4 specification: EffectiveCost column
• FOCUS 1.4 specification: CommitmentDiscountStatus column
• FOCUS 1.4 appendix: all-upfront spend plan at 50% utilization (informative example)
• FOCUS 1.2 specification: BilledCost and EffectiveCost (amortization wording)
• AWS Data Exports: FOCUS 1.2 with AWS columns
• Microsoft: FOCUS conformance summary for Cost Management
• Google Cloud: FOCUS billing data export to BigQuery (Preview)