Getting address data ready for BI dashboards that actually work

Every analytics team has a "sales by region" chart that is quietly wrong. Not catastrophically wrong — not wrong enough to trigger an incident. Wrong in the way that means sales leadership has been making territory decisions based on a number that is off by 12% for the South-East and 18% for the North-West, and nobody has noticed because the chart looks plausible.

The root cause is almost always the same: raw address strings sitting in the fact table, no shared geographic key, a BI tool doing string matching against inconsistently formatted city names, and half the rows silently dropping because "New York" and "New York City" and "NYC" do not join to the same region record.

This post is for the analytics engineer or BI developer who owns that chart. The fix is not a BI plugin. The fix is upstream: geocode every record once at intake, attach a coordinate pair and the containing boundary codes to each row, and let the BI tool do what BI tools are actually good at — grouping by a clean categorical key and aggregating a number column. The geographic intelligence moves out of the dashboard query and into the data model, where it belongs.

Why raw addresses break BI maps

The failure modes are predictable and compounding.

City name collisions. "Springfield" exists in more than thirty US states. "Richmond" appears as a city, a neighbourhood, a borough, and a county in different parts of the English-speaking world. When a BI tool groups by a city column, it does not know which Springfield is which. The number for each Springfield row lands in one aggregated bucket, and the map looks like Springfield is doing remarkably well across seventeen states simultaneously.

Formatting inconsistency across data sources. Your CRM stores "St. Louis". Your ERP stores "Saint Louis". Your support ticketing system stores "St Louis" without the period. Your logistics provider sends "SAINT LOUIS MO". These are the same city. A string join treats them as four distinct values. Depending on which source feeds the dashboard and which feeds the territory dimension table, some fraction of records join correctly and the rest are dropped or aggregated into an "Unknown" bucket that the chart designer eventually hides because it clutters the visual.

No stable geographic key. BI tools need a join key — a column whose value is the same in the fact table and in the dimension table, every single time, no exceptions. City names cannot serve this role because they are human-authored strings. Postcodes are better but still collide across countries and occasionally get reassigned. Administrative boundary codes — ISO 3166-2 region codes, FIPS county codes, or equivalent national schemes — are stable by design. They are the right join key for geographic grouping. But they are not in most raw address datasets because nobody entered them at intake.

Null-island aggregation. Records with no geocoordinate at all tend to accumulate in whichever default behaviour the mapping library applies when it cannot find a geometry for a row. Usually that is a pin dropped at 0,0 in the Gulf of Guinea — which BI map developers call "null island" — or a silent drop from the query result. Either way, the counts for your real regions are wrong and you cannot see which rows are causing the problem.

The fix for all four failure modes is the same: enrich every record with a coordinate pair and the codes for its containing administrative boundaries at intake, before the row ever lands in the warehouse.

The shape of the fix

The architecture is simple. Three additions to your intake pipeline:

1. Geocode every address when it enters your system — at CRM record creation, order capture, form submission, or import time. Store lat and lng on the row. This converts the free-text address into a machine-readable position that is source-agnostic.
2. Attach boundary codes by querying the Boundaries or Divisions endpoint with the coordinate. Store the containing admin codes — country, region/state, county, postcode — as first-class columns on the fact row. These become the join keys for every downstream geographic grouping.
3. Let the BI tool group by the code, not the string. A region dimension table keyed on the same boundary codes maps each code to a display name, a polygon for the map, a sales territory assignment, whatever you need. The fact table never again needs to parse a city name to figure out which region a record belongs to.

After this, "sales by region" is SELECT region_code, SUM(revenue) FROM orders GROUP BY region_code — a query whose result is correct by construction because the geographic assignment happened at write time by a geocoding API, not at read time by a string heuristic.

The same pattern is documented in the health-analytics context in Mapping health data to boundaries without storing PII, where the geocode→boundary→aggregate shape is used to protect individual records while still producing area-level statistics. The mechanics are identical; only the downstream aggregation changes.

What the API surfaces look like

CSV2GEO exposes the two endpoints you need for this pattern.

`GET /api/v1/geocode` — converts a free-text address into a coordinate pair and a confidence score. Takes q (free-text address) or structured fields. Returns lat, lng, confidence, and a normalised formatted address. One credit per call.

`GET /api/v1/boundaries` — takes a coordinate pair and returns the administrative hierarchy containing that point: country, region (state/province), county or equivalent, and postcode district. The response is a set of stable code + name pairs per level. One credit per call.

For historical tables — existing data that needs enriching before the first dashboard ships — the web batch tool at csv2geo.com accepts a CSV upload and returns it with coordinates and boundary codes appended. Credits are consumed per address row, not per file. This is the right tool when you have 500,000 records in a historical orders table and no appetite for writing a batching script before the quarterly review.

For real-time intake — new records arriving via API, form, or event stream — the REST geocoding endpoint handles each address as it arrives, and you store the coordinates and boundary codes alongside the other fields before writing to the warehouse.

Step 1: Geocode the incoming address

At intake, a new order or customer record arrives with a raw address string. The first call converts it to a coordinate.

curl -G "https://csv2geo.com/api/v1/geocode" \
  --data-urlencode "q=350 Fifth Avenue, New York, NY 10118" \
  --data-urlencode "api_key=$CSV2GEO_API_KEY"

Response (abbreviated):

{
  "results": [{
    "lat": 40.7484,
    "lng": -73.9967,
    "confidence": 0.97,
    "formatted": "350 5th Ave, New York, NY 10118, US"
  }]
}

In Python:

import os
import requests

API = "https://csv2geo.com/api/v1"
KEY = os.environ["CSV2GEO_API_KEY"]

def geocode(address: str) -> dict | None:
    r = requests.get(
        f"{API}/geocode",
        params={"q": address, "api_key": KEY},
        timeout=15,
    )
    r.raise_for_status()
    results = r.json().get("results", [])
    if not results:
        return None
    top = results[0]
    if top.get("confidence", 0) < 0.7:
        # Flag for manual review rather than silently storing a bad coordinate.
        return None
    return {"lat": top["lat"], "lng": top["lng"], "confidence": top["confidence"]}

The confidence threshold matters here. A result with confidence below 0.7 typically indicates the geocoder was uncertain about the match — a partial street match, an ambiguous locality, or a postcode-level centroid rather than a rooftop position. For BI purposes, a postcode centroid is often acceptable (the boundary code will still be correct), but you want to track how many records fell back to postcode-level and make that metric visible. See Geocoding confidence scores explained for the full decision tree.

In Node:

const API = 'https://csv2geo.com/api/v1';
const KEY = process.env.CSV2GEO_API_KEY;

async function geocode(address) {
  const url = `${API}/geocode?q=${encodeURIComponent(address)}&api_key=${KEY}`;
  const r = await fetch(url);
  if (!r.ok) throw new Error(`geocode http ${r.status}`);
  const data = await r.json();
  const top = data.results?.[0];
  if (!top || top.confidence < 0.7) return null;
  return { lat: top.lat, lng: top.lng, confidence: top.confidence };
}

Store lat, lng, and confidence on the record before continuing. If geocode() returns null, flag the record for manual address correction rather than writing a null coordinate — a null coordinate that joins to no boundary is exactly the null-island problem you are trying to eliminate.

Step 2: Attach the boundary codes

With a coordinate in hand, call the Boundaries endpoint to retrieve the administrative hierarchy.

curl -G "https://csv2geo.com/api/v1/boundaries" \
  --data-urlencode "lat=40.7484" \
  --data-urlencode "lng=-73.9967" \
  --data-urlencode "api_key=$CSV2GEO_API_KEY"

Response (abbreviated):

{
  "result": {
    "country": { "code": "US", "name": "United States" },
    "region":  { "code": "US-NY", "name": "New York" },
    "county":  { "code": "US-NY-061", "name": "New York County" },
    "postcode": { "code": "10118", "name": "10118" }
  }
}

In Python, paired with the geocode call:

def get_boundaries(lat: float, lng: float) -> dict | None:
    r = requests.get(
        f"{API}/boundaries",
        params={"lat": lat, "lng": lng, "api_key": KEY},
        timeout=15,
    )
    r.raise_for_status()
    result = r.json().get("result")
    if not result:
        return None
    return {
        "country_code":  result.get("country", {}).get("code"),
        "region_code":   result.get("region", {}).get("code"),
        "county_code":   result.get("county", {}).get("code"),
        "postcode_code": result.get("postcode", {}).get("code"),
    }

The codes you get back — ISO 3166-2 region codes, FIPS-equivalent county codes depending on the country, postcode districts — are the stable keys your dimension tables need. They do not drift when a city renames itself, when a postcode gets an extra digit, or when someone enters "St. Louis" instead of "Saint Louis." They are the geographic equivalent of a foreign key: machine-generated, stable, and unambiguous.

Write all four codes to columns on the fact row: country_code, region_code, county_code, postcode_code. Even if today's dashboard only groups by region_code, having the others pre-populated means the next analyst who wants county-level breakdowns does not trigger another geocoding run.

Step 3: Store coordinates and codes on the fact table

The schema change is the same whatever warehouse you are using. On an orders table:

ALTER TABLE orders
  ADD COLUMN lat            DOUBLE PRECISION,
  ADD COLUMN lng            DOUBLE PRECISION,
  ADD COLUMN geo_confidence DOUBLE PRECISION,
  ADD COLUMN country_code   VARCHAR(10),
  ADD COLUMN region_code    VARCHAR(20),
  ADD COLUMN county_code    VARCHAR(30),
  ADD COLUMN postcode_code  VARCHAR(20);

These columns are written once at intake and never updated unless the customer changes their address (in which case you re-geocode and re-attach the boundary codes). The cost of the two API calls — one geocode, one boundaries lookup — is two credits per record. At paid pricing starting from $54/month for 100,000 calls, that is under $0.001 per order. For a business processing ten thousand orders a month, the enrichment budget is well within the entry tier.

For the historical table — records already in the warehouse that predate this enrichment — use the web batch tool. Upload the CSV with a column containing your raw address strings, configure the output columns you want, and download the enriched CSV. Import the result back into the warehouse with an UPDATE ... WHERE order_id = ... join. Credits are charged per row. The batch tool is the right choice here because it handles the batching, retry, and progress tracking for you; a hand-rolled script that processes 500,000 rows one-by-one is a weekend of debugging that you do not need.

Step 4: Build the dimension table

A geographic dimension table maps each boundary code to everything a BI tool needs to render the map and label the chart.

CREATE TABLE dim_geography (
  region_code   VARCHAR(20) PRIMARY KEY,
  region_name   VARCHAR(100),
  country_code  VARCHAR(10),
  country_name  VARCHAR(100),
  territory     VARCHAR(100),   -- your internal sales territory assignment
  geojson       TEXT            -- polygon geometry for the map layer
);

The territory column is where your internal business logic lives — mapping ISO region codes to the sales territories your organisation actually uses. This is the table you update when territories are realigned; the fact table never changes. The separation is the point: geography is normalised into a dimension, so changing a territory boundary is a one-row UPDATE in the dimension table, not a re-geocoding run across three years of orders.

The geojson column holds the polygon for each region. Your BI tool's map layer references it to draw the choropleth. If your BI tool pulls boundaries from its own internal source, you can leave the column out and rely on the code join — most mapping components accept an ISO 3166-2 code and resolve the polygon themselves.

Step 5: Wire the intake pipeline and validate

Bring the two API calls together into the intake path. A minimal complete example that enriches one record:

import os, requests

API = "https://csv2geo.com/api/v1"
KEY = os.environ["CSV2GEO_API_KEY"]

def enrich_address(address: str) -> dict:
    """Return geo fields to merge into the record before writing to the warehouse."""
    # 1. Geocode
    gr = requests.get(
        f"{API}/geocode",
        params={"q": address, "api_key": KEY},
        timeout=15,
    )
    gr.raise_for_status()
    results = gr.json().get("results", [])
    if not results or results[0].get("confidence", 0) < 0.7:
        return {"geo_status": "needs_review"}

    top = results[0]
    lat, lng = top["lat"], top["lng"]

    # 2. Boundaries
    br = requests.get(
        f"{API}/boundaries",
        params={"lat": lat, "lng": lng, "api_key": KEY},
        timeout=15,
    )
    br.raise_for_status()
    b = br.json().get("result", {})

    return {
        "lat":            lat,
        "lng":            lng,
        "geo_confidence": top["confidence"],
        "country_code":   b.get("country", {}).get("code"),
        "region_code":    b.get("region", {}).get("code"),
        "county_code":    b.get("county", {}).get("code"),
        "postcode_code":  b.get("postcode", {}).get("code"),
        "geo_status":     "ok",
    }

Validation before you ship to production: pick twenty addresses you know well — one or two in each major region your dashboard covers — and check that the region_code returned matches your expectation. If you have a Denver address, the region_code should be US-CO. If you have a Tokyo address, it should be something in JP. This sanity check takes five minutes and catches any environment variable problem (wrong API key), endpoint misconfiguration, or misunderstanding of the response schema before bad codes propagate into the warehouse.

For bulk validation of the historical enrichment, run a frequency count of region_code values against your known order distribution. If 30% of your orders historically came from California and the enriched table shows 3% with region_code = US-CA, something is wrong — likely a confidence threshold that is too aggressive, or a CRM address format that the geocoder is struggling with. See Benchmarking geocoding APIs — honest numbers for the right way to measure match rate before committing to a full historical enrichment run.

The dashboard query after enrichment

After enrichment, the "sales by region" query is genuinely simple:

SELECT
  g.region_name,
  g.territory,
  COUNT(*)           AS order_count,
  SUM(o.revenue)     AS total_revenue
FROM orders o
JOIN dim_geography g ON o.region_code = g.region_code
WHERE o.order_date >= '2026-01-01'
GROUP BY g.region_name, g.territory
ORDER BY total_revenue DESC;

No string parsing. No city-name matching. No silent drops for rows where the city name did not join. Every row in orders that received a region_code at intake is included exactly once in the correct region bucket. Rows flagged as geo_status = 'needs_review' are excluded from the aggregation and surface in a separate data-quality report, which means the analyst knows exactly what is missing rather than having it silently swallowed into an "Unknown" bucket.

This is the shape the query should always have had. The geocoding step is what makes it possible.

Handling the historical table

If your warehouse has three years of orders with raw address strings and no coordinates, you have two options.

Option A — batch tool. Export the address column (or the columns you want to feed the geocoder: street, city, postcode, country), upload the CSV to the web batch tool at csv2geo.com, download the enriched result, and import back. For tables under a few hundred thousand rows, this is the fastest path. The batch tool handles the parallelism and rate limiting; you handle the import and the UPDATE join.

Option B — scripted batch with the REST API. For tables over a million rows, or where you want the enrichment to run inside your existing pipeline tooling, write a script that reads the table in pages, calls /api/v1/geocode followed by /api/v1/boundaries for each address, and writes results back. Process in batches of 50-100 concurrent requests to stay well inside the rate limit; see Caching geocoding results — 90% cost reduction for the pattern that prevents double-billing duplicate addresses (many order tables have the same shipping address appearing hundreds of times).

Caching is particularly valuable for the historical enrichment. A customer's shipping address typically appears in tens or hundreds of orders. Deduplicate the address list before sending it to the geocoder — enrich each unique address once, then join the result back to every order row that shares that address. A table with 1,000,000 orders might have only 80,000 unique addresses; the enrichment cost drops by more than 90%.

Instrument the enrichment job with three counters: geocoded_ok, geocoded_low_confidence, and geocoded_failed. Emit them to your observability stack. When the job finishes, the ratio geocoded_ok / total_rows is your baseline geographic coverage figure — it is the number you report to the data engineering team and use to size the manual address-correction backlog. See Observability for geocoding pipelines for the full set of metrics that make a geocoding batch job debuggable.

Why this is better than geocoding inside the BI tool

Some BI tools offer built-in address-to-coordinate conversion or territory assignment. The appeal is obvious — no pipeline changes required, just drag a column onto a map and let the tool figure it out. In practice, this pattern creates three problems that compound over time.

Query-time cost. When geocoding happens at query time, every dashboard load triggers API calls. Ten users refreshing the dashboard simultaneously is ten parallel geocoding runs against the same address set. This is expensive and unpredictable. Geocoding at write time means the cost is paid once per record, regardless of how many times the dashboard is loaded.

Inconsistency across tools. If your BI tool geocodes differently from the tool your data team uses to validate the numbers, you get different region assignments for the same addresses in different contexts. The "sales by region" chart in the BI tool disagrees with the "sales by region" export in the analyst's spreadsheet. Stakeholders notice, trust in the numbers erodes, and someone spends a week trying to reconcile two versions of reality that are both slightly wrong.

No audit trail. When a territory assignment is made at query time inside a BI tool, there is no record of it. When a stakeholder asks why a particular order was attributed to the North-West region instead of the South-West, there is no geocoding log to inspect, no confidence score to review, no lat/lng to verify on a map. The assignment is ephemeral. Geocoding at write time, with confidence scores and coordinates stored on the row, means every geographic attribution is auditable.

The fix is upstream. The BI tool's job is aggregation and visualisation. It is very good at that. Geographic intelligence — translating a raw address into a stable area key — is the geocoding API's job. Let each tool do what it does well.

Frequently Asked Questions

How many of our 504M+ addresses does CSV2GEO cover, and does global coverage affect boundary codes? CSV2GEO covers 504M+ addresses across 63 countries. Boundary codes are available for all covered countries, though the hierarchy depth varies — some countries have country, region, and postcode but no county-level code. The response will always include whatever levels exist for the country in question. Build your fact table schema to treat each level as nullable.

Should we geocode at order creation time or in a background job? Both patterns work. Geocoding synchronously at order creation gives you the coordinate immediately but adds latency to the write path — budget for the two API calls (geocode + boundaries) in your request timeout. A background job is lower risk for the write path but means newly created orders are temporarily unenriched, which may cause them to be excluded from dashboards until the job runs. For most businesses, a background job that runs every few minutes is the right balance.

What is the right confidence threshold for BI use? For boundary-code assignment, a confidence of 0.6 or above is usually sufficient — you need the geocoder to identify the correct administrative area, not the exact rooftop. For map-pin display or distance calculations, a higher threshold (0.8+) is appropriate. Use different thresholds for different downstream uses, and store the raw confidence score so you can adjust the threshold later without re-geocoding.

Our addresses span 30 countries. Will boundary codes be consistent across countries? The code format varies by country — US regions use ISO 3166-2 codes like US-CA, UK regions use different conventions, and so on. The response structure is consistent (the same JSON keys at each level), but the values follow each country's national standards. Your dimension table needs to be built with this in mind: the region_code is globally unique but not globally formatted the same way. This is the correct behaviour — it mirrors how real administrative geography works.

How do we handle address corrections and re-geocoding when a customer updates their address? When a customer updates their delivery address, re-run enrich_address() and overwrite the geo columns on the affected record. If you are versioning orders (an update creates a new order record rather than modifying the old one), the new record gets its own geocoding call and the old record's coordinates are preserved for historical reporting. Never mutate a historical order's geographic attribution without also updating the effective-from date.

What should we do with records that fail geocoding entirely? Store them in a needs_review queue with the original address string and the failure reason (no results returned, low confidence, API timeout). Review the queue weekly. Common causes: a PO Box address (no rooftop coordinate exists), a non-standard address format for the country, or a data-entry error. Correct the source address and re-geocode. Do not silently drop these records — an unresolvable address is a data quality signal, and the volume of unresolvable addresses per intake source is a useful metric for surfacing upstream data problems.

Can we use the free tier to validate the approach before committing to a paid plan? Yes. The free tier provides 3,000 calls per day with no credit card required. That is 1,500 complete enrichments per day (one geocode + one boundaries call per record). It is enough to validate the approach on a representative sample of your address data, measure your match rate, and confirm that the boundary codes align with your territory dimension table before processing the full historical table.

Related Articles

• Geocoding confidence scores explained — how to interpret the confidence field and set the right threshold for your use case
• Caching geocoding results — 90% cost reduction — deduplicate before you geocode; the historical enrichment case study is directly applicable here
• Observability for geocoding pipelines — the metrics that make a batch enrichment job debuggable and auditable
• Benchmarking geocoding APIs — honest numbers — how to measure match rate and boundary accuracy before committing to a full historical run
• Mapping health data to boundaries without storing PII — the same geocode→boundary→aggregate pattern applied to sensitive health records

---

*I.A. / CSV2GEO Creator*