Forecasting Monthly Geocoding API Costs in Python

TL;DR: A geocoding bill is not proportional to orders. Decompose it into new distinct addresses, cache misses on returning addresses, and retries; forecast each separately; then multiply by the per-provider rate. The first term grows with the business, the second with cache decay, and the third only when something is wrong. The counters this reads are described in API quota tracking and cost management.

Three Terms, Three Behaviours

Forecasting total calls as a multiple of orders fails because the three components that make up the bill behave completely differently. Separating them is what makes a forecast both accurate and diagnostic.

What each component of the bill responds to Three rows. New distinct addresses scale with customer acquisition and are the largest term for a growing business. Cache misses on returning addresses scale with the TTL policy and are predictable from the age distribution. Retries should be near zero and are a defect signal rather than a growth signal. new distinct addresses driven by customer acquisition — the term that should grow, and the one worth paying grows with the business cache misses on repeats driven by the TTL policy and the age distribution — fully predictable a month ahead a policy choice retries should be a rounding error — a rising share is a defect, never a forecast input a defect signal

Forecasting the third term at its historical level is the mistake worth avoiding explicitly. A model that projects last month’s retry rate forward normalises a defect into a budget line, and the budget then absorbs the cost of never fixing it.

The Decomposition Query

-- Historical spend split into its three components, by provider and month.
SELECT date_trunc('month', called_at)                     AS month,
       provider,
       count(*) FILTER (WHERE is_retry)                   AS retry_calls,
       count(*) FILTER (WHERE NOT is_retry AND first_seen) AS new_address_calls,
       count(*) FILTER (WHERE NOT is_retry AND NOT first_seen) AS repeat_calls,
       count(*)                                           AS total_calls
FROM   geocode_call_log
WHERE  called_at >= now() - interval '12 months'
GROUP  BY 1, 2
ORDER  BY 1, 2;

first_seen is the flag that makes the decomposition possible, and it must be recorded at call time rather than derived later — once an address is in the durable table, there is no way to reconstruct whether the call that created it was its first.

Projecting Each Term

from __future__ import annotations

import pandas as pd


def forecast_month(
    history: pd.DataFrame,
    order_growth: float,
    expiring_entries: int,
    unit_price: dict[str, float],
) -> pd.DataFrame:
    """Project next month's billed calls and cost, per provider."""
    recent = history[history["month"] >= history["month"].max() - pd.DateOffset(months=2)]
    by_provider = recent.groupby("provider").mean(numeric_only=True)

    projected = pd.DataFrame(index=by_provider.index)
    projected["new_address_calls"] = by_provider["new_address_calls"] * (1 + order_growth)
    projected["repeat_calls"] = expiring_entries * by_provider["repeat_share"]
    projected["retry_calls"] = by_provider["retry_calls"] * 0.5      # assume improvement
    projected["total_calls"] = projected.sum(axis=1)
    projected["cost"] = projected["total_calls"] * projected.index.map(unit_price)
    return projected.round(0)

Halving the retry term rather than carrying it forward is a deliberate modelling choice: it states in the forecast that the retries are expected to be addressed, which puts the question in front of whoever reviews the number instead of hiding it in an average.

Cache Decay Is Predictable

The repeat-lookup term is the one people assume is unforecastable and is in fact the most predictable of the three. Every cached entry has a known TTL and a known write date, so the number expiring next month is a query, not an estimate.

Entries due to expire, by month A bar chart of cached entries grouped by the month in which their TTL expires. The next month's bar is highlighted, showing that the repeat-lookup component of the forecast is a direct count rather than a projection. A note observes that jitter spreads each cohort slightly. Cached entries by expiry month next month Only a fraction of expiring entries are looked up again — multiply by the observed repeat rate, not by one.

The multiplier in that closing note is the part worth measuring rather than assuming. An expiring entry costs nothing unless the address is queried again, and on most corpora the share that returns within a month is well below half.

Comparing the Forecast With the Invoice

A forecast that is never checked against reality decays into a ritual. Recording the forecast, the actual, and the error per provider per month turns it into a model that improves — and the errors themselves are informative.

A consistent under-forecast usually means the new-address ratio is higher than the model assumes, which is a growth signal worth passing on. A consistent over-forecast usually means the cache is performing better than the TTL policy suggests, which is an argument for lengthening TTLs. A large one-month error with no pattern is almost always a retry storm or a key-version change, both of which are visible in the decomposition.

Keep the comparison at provider granularity. Two providers with offsetting errors produce a total that looks accurate and a model that is wrong in two directions, and the aggregate hides exactly the detail that would have explained it.

Forecast Error Is Itself a Signal

Reading the shape of the error Three error patterns. A consistent under-forecast indicates the new-address ratio is higher than modelled, which is a growth signal. A consistent over-forecast indicates the cache is outperforming its TTL policy. A single unexplained spike is almost always a retry storm or a key-version change. consistently under the new-address ratio is higher than modelled a growth signal pass it on, then adjust consistently over the cache is outperforming its TTL policy lengthen the TTLs free money, already earned one large spike a retry storm, or a key version change a defect, not a trend visible in the decomposition

Presenting the Number

How the forecast is communicated determines whether it is acted on. Three presentation choices make the difference between a number that is questioned productively and one that is simply accepted or ignored.

Quote the decomposition, not just the total. “Next month: 340k calls, of which 210k are new addresses, 118k are cache expiries and 12k are retries” invites exactly the right questions, where a single figure invites none. It also makes the levers visible: the second term is a policy choice and the third is a defect, and both are actionable in a way that the total is not.

State the assumptions inline. Order growth, the retry improvement factor and the repeat rate are all judgement calls, and putting them beside the number lets whoever reads it disagree with a specific input rather than with the conclusion. A forecast that cannot be argued with at the level of its inputs will be argued with at the level of its output, which is far less productive.

Finally, give a range rather than a point where the inputs are genuinely uncertain. A forecast expressed as a single number implies a precision the model does not have, and the first month it is missed by ten percent the whole exercise loses credibility it did not need to spend.

Edge Cases and Failure Modes

Tiered pricing. Providers with volume tiers make cost non-linear in calls, so the final multiplication must apply the tier schedule rather than a flat rate. Model the tier boundaries explicitly — crossing one is the most common cause of a surprising bill.

Free-tier expiry. A promotional or trial tier that ends mid-month produces a step change that no historical model will predict. Contract dates belong in the forecast as explicit inputs.

A pending migration. Any planned change — a new market, a normalisation version bump, a provider switch — invalidates the historical base. Forecast those as separate line items rather than folding them into the trend.

Currency and billing period. Providers bill in different currencies and on different cycles. Normalise both before summing, or a month with an unfavourable rate looks like a volume increase.

Who the Forecast Is For

A cost forecast has two audiences with different needs, and serving both from one artefact is usually a mistake.

Finance needs a number with a date and a confidence range, expressed in the currency of the invoice, delivered on the same cadence as the budgeting cycle. They do not need the decomposition, and including it invites questions the forecast is not the right place to answer.

The engineering team needs the decomposition and almost nothing else. Their question is which term is moving and why, and the total is only useful as a sanity check. Presenting the three components as a trend, with the retry share highlighted, turns the forecast into an operational instrument rather than a financial one.

Producing both from the same computation keeps them consistent, and it takes one extra function. What it prevents is the familiar situation where the finance number and the engineering number differ, nobody can reconcile them, and the resulting exercise costs more than either forecast saved.

Integration Note

The forecast reads the same counters that tracking API spend with Python and Redis maintains, plus the expiry schedule from the cache freshness policy. That coupling is the useful part: a change to the TTL policy immediately shows up as a change in next month’s projected spend, which makes the cost consequence of a caching decision visible before it is taken rather than a quarter afterwards.