Cloud Cost Analysis, in depth: explaining a bill change with rate, volume and mix decomposition

The question behind the question

A cost change is only interesting relative to what it bought. A 20 percent rise with 30 percent more traffic is an efficiency gain; a 5 percent rise with flat traffic is a regression. So a complete analysis answers three questions in order. What changed, in dollars, by service and SKU? Why did it change: price, quantity or the mix of things bought? And was it worth it: did cost per unit of business output rise or fall?

The failure mode of most ad-hoc analyses is stopping at the first question. A chart of cost by service shows that compute rose by $40,000, and the conversation ends with 'we used more compute'. That is not an explanation anyone can act on.

The data: FOCUS columns and which cost to use

FOCUS, the FinOps Open Cost and Usage Specification, defines common columns for billing data, one row per charge. Choosing the right cost column is the most consequential decision in the whole exercise.

The rule of thumb: use EffectiveCost to explain engineering-driven change and BilledCost to reconcile with the invoice. BilledCost is lumpy. An upfront commitment purchase appears in full in the month it was bought, which makes that month look like a spike and the following months look artificially cheap. EffectiveCost spreads the purchase across the months it covers, so it tracks what the workload really consumed. When the two disagree, the difference is itself a line in the variance report, labelled as an accounting effect.

Rate, volume and mix, from first principles

For a single SKU, cost is quantity times unit rate: C = Q x R. Between a base month (0) and a current month (1), the change can be split into a volume effect (more or less quantity at the old rate) and a rate effect (a different price for the old quantity). The naive split, volume = (Q1 - Q0) x R0 and rate = (R1 - R0) x Q0, leaves an interaction term (Q1 - Q0) x (R1 - R0) unassigned, and whichever way you allocate it changes the story.

The clean fix is the midpoint split. Evaluate each effect at the average of the other variable: volume = (Q1 - Q0) x (R0 + R1) / 2 and rate = (R1 - R0) x (Q0 + Q1) / 2. Multiply out and the two sum exactly to Q1R1 - Q0R0, with nothing left over and no arbitrary ordering. This is the two-factor case of the Shapley allocation.

Mix enters when you aggregate. Suppose compute moved from one instance family to another at a different price per hour. At the SKU level, one SKU shows negative volume and another shows positive volume, and each has a stable rate. Aggregated to 'compute hours', total volume looks flat and the average rate looks higher, so a naive service-level analysis reports a price increase that no price list ever made. Mix is the part of the aggregate rate change explained by shifting quantity between SKUs with different rates. Compute it by decomposing per SKU, summing the volume effects, and comparing that with the volume effect you would get from total quantity at the average rate. New SKUs (Q0 = 0) and retired SKUs (Q1 = 0) are pure volume at the SKU level and pure mix at the aggregate level, which is exactly what they are.

The monthly cube in SQL

The first step is a cube: one row per month, account, service, SKU and pricing unit, with cost and quantity. Filtering to usage charges keeps purchases, credits and tax out of the rate and volume maths; they are handled separately.

-- One row per month x account x service x SKU x pricing unit, usage only.
CREATE TABLE cost_cube AS
SELECT
    DATE_TRUNC('month', ChargePeriodStart) AS month,
    SubAccountId,
    ServiceName,
    SkuId,
    PricingUnit,
    SUM(EffectiveCost)   AS effective_cost,
    SUM(ListCost)        AS list_cost,
    SUM(PricingQuantity) AS qty
FROM focus_billing
WHERE ChargeCategory = 'Usage'
GROUP BY 1, 2, 3, 4, 5;

-- Rate and volume effects per SKU between two months, midpoint split.
WITH pair AS (
    SELECT
        COALESCE(b.SubAccountId, c.SubAccountId) AS account,
        COALESCE(b.ServiceName, c.ServiceName)   AS service,
        COALESCE(b.SkuId, c.SkuId)               AS sku,
        COALESCE(b.qty, 0) AS q0, COALESCE(c.qty, 0) AS q1,
        COALESCE(b.effective_cost, 0) AS c0, COALESCE(c.effective_cost, 0) AS c1
    FROM (SELECT * FROM cost_cube WHERE month = DATE '2026-06-01') b
    FULL OUTER JOIN (SELECT * FROM cost_cube WHERE month = DATE '2026-07-01') c
      ON  b.SubAccountId = c.SubAccountId AND b.ServiceName = c.ServiceName
      AND b.SkuId = c.SkuId AND b.PricingUnit = c.PricingUnit
)
SELECT account, service, sku, c1 - c0 AS delta,
       CASE WHEN q0 = 0 OR q1 = 0 THEN c1 - c0
            ELSE (q1 - q0) * (c0 / q0 + c1 / q1) / 2 END AS volume_effect,
       CASE WHEN q0 = 0 OR q1 = 0 THEN 0
            ELSE (c1 / q1 - c0 / q0) * (q0 + q1) / 2 END AS rate_effect
FROM pair
ORDER BY ABS(c1 - c0) DESC;

Joining on PricingUnit as well as SKU stops a provider re-expressing a price, say per hour to per second, from appearing as a huge volume swing. The rate is cost divided by quantity, so it already includes discounts, and a discount change correctly shows up as a rate effect.

The decomposition in Python, with mix

SQL gives the per-SKU split. Rolling it up to a service-level story with a mix term is easier in a few lines of Python:

import pandas as pd

def decompose(df):
    """df columns: service, sku, q0, q1, c0, c1. Returns per-service volume, mix and rate."""
    rows = []
    for service, g in df.groupby("service"):
        q0, q1, c0, c1 = g.q0.sum(), g.q1.sum(), g.c0.sum(), g.c1.sum()
        if q0 == 0 or q1 == 0:                        # new or retired service: all volume
            rows.append(dict(service=service, delta=c1 - c0, volume=c1 - c0, mix=0.0, rate=0.0, check=c1 - c0))
            continue
        both = g[(g.q0 > 0) & (g.q1 > 0)]
        edge = g[(g.q0 == 0) | (g.q1 == 0)]           # new or retired SKUs
        r0, r1 = both.c0 / both.q0, both.c1 / both.q1
        rate = ((r1 - r0) * (both.q0 + both.q1) / 2).sum()
        sku_volume = ((both.q1 - both.q0) * (r0 + r1) / 2).sum() + (edge.c1 - edge.c0).sum()
        avg0, avg1 = c0 / q0, c1 / q1                 # assumes one pricing unit per service
        pure_volume = (q1 - q0) * (avg0 + avg1) / 2   # volume at the blended average rate
        rows.append(dict(service=service, delta=c1 - c0, volume=pure_volume,
                         mix=sku_volume - pure_volume, rate=rate, check=sku_volume + rate))
    out = pd.DataFrame(rows)
    assert (out.check - out.delta).abs().max() < 1e-6   # SKU volume + rate must equal the change
    return out.sort_values("delta", key=abs, ascending=False)

The assertion checks that per-SKU volume and rate sum exactly to the change; a report whose drivers do not will be challenged. Run it per service and pricing unit: GB-months and hours cannot share an average rate.

Worked example: an 18 percent rise

A platform's EffectiveCost for usage rose from $412,000 in June to $486,000 in July, up $74,000. A service-level chart says compute rose $52,000, storage $9,000 and data transfer $13,000. The decomposition tells a sharper story.

The drivers sum to exactly $74,000. The calendar row illustrates the most common error: comparing a 31-day month against a 30-day one inflates growth by about 3.3 percent before anything changed. Normalise to cost per day first, decompose the per-day figures, scale them back to the current month's length, and report the day-count difference once, as its own driver.

The actionable items are now obvious and owned: the mix change is a $21,000 per month decision that someone can reverse, the storage growth is a policy gap, and the transfer increase is an architecture question for the team that placed the replica. Only the 9 percent volume growth is 'the business grew', and cost per request actually fell slightly on that line. For the transfer line, the egress cost article explains where cross-zone and internet charges come from.

Unit cost: the number that survives growth

Absolute cost rises with success. Cost per unit of output, such as per thousand requests, per active customer or per training run, is the measure that says whether engineering is getting more efficient. Join the monthly cube with a business-metrics table and track cost per unit per service. A rising unit cost with flat rates is a mix or efficiency regression; a falling one during growth is the result you want to be able to prove.

Unit cost has pitfalls of its own. Pick a denominator the cost actually scales with, and never redefine it mid-year. For GPU-heavy workloads, cost per token or per training step is the useful unit, discussed in LLM FinOps on GPUs.

Accounting effects that look like usage

• Commitment purchases. In BilledCost a purchase is a spike; in EffectiveCost it is amortised. Report the difference, never mix the two in one chart.
• Credits and their expiry. Promotional or migration credits reduce cost until they run out, and the month they end looks like a jump in usage. Track credits (ChargeCategory Credit) as their own driver.
• Commitment coverage changes. When usage outgrows a commitment, the extra runs at on-demand rates, which appears as a rate effect even though nobody changed a price.
• Late and restated data. Billing data for a month keeps changing for days after it ends. Analyse closed months only, or record the extract date and rerun after close.
• Tag coverage. If tagging improves, cost moves from 'untagged' into teams; that is a reallocation, not growth. Compare total cost before judging any team's trend.

Catching changes before the invoice

Monthly variance analysis explains the past; a daily check catches problems while they are cheap. A simple, robust approach compares each day's cost per service with the same weekday over the previous four weeks and flags large deviations. It avoids the false alarms that a plain moving average raises every Monday.

def daily_flags(daily, threshold=0.25, min_dollars=500):
    """daily: DataFrame with date, service, cost (EffectiveCost, usage only)."""
    daily = daily.sort_values("date").copy()
    daily["weekday"] = daily.date.dt.weekday
    daily["baseline"] = (daily.groupby(["service", "weekday"]).cost
                         .transform(lambda s: s.shift(1).rolling(4, min_periods=3).median()))
    daily["change"] = daily.cost - daily.baseline
    hit = (daily.change.abs() > threshold * daily.baseline) & (daily.change.abs() > min_dollars)
    return daily[hit][["date", "service", "cost", "baseline", "change"]]

Without the dollar floor, small services generate most of the alerts and people stop reading them. Route each flag to the owning team with its driver attached. Billing data lags usage by hours to a day, so this is early warning, not real-time control.

Trade-offs and making it routine

Finer grouping keys explain more, but they also multiply rows and create spurious mix effects when resource identifiers churn; SKU and account are usually the right grain, with resource-level drill-down only for the top drivers. EffectiveCost is best for engineering, BilledCost for finance, and a report that shows both with the reconciling line keeps both audiences satisfied.

A good monthly report fits on one page: total change, the calendar and accounting effects, the top five usage drivers with owners, unit-cost trends and actions from last month with their measured savings. For interruption-tolerant workloads, rate effects are also where spot capacity savings show up and can be verified.

What to do next

1. Enable a FOCUS-format billing export for every provider and account, and load it into one warehouse table.
2. Build the monthly cube on EffectiveCost, usage charges only, keyed by account, service, SKU and pricing unit.
3. Implement the midpoint rate and volume split per SKU, roll it up with a mix term per service, and assert that drivers sum to the total.
4. Normalise for days in the month and list commitments, credits, tax and restatements as separate drivers.
5. Join a business-metrics table and publish cost per unit for your top five services.
6. Add the same-weekday daily check with a dollar floor, and route flags to owning teams with the driver attached.