Every Row Needs a Geo Stamp: Designing the Geography Fields of a Scraping Pipeline

Here is a conversation that has played out at every scraping project I've been part of, usually about three months in. Someone opens a notebook to ask a perfectly reasonable question — "what does this price look like in Germany?" — and discovers the pipeline cannot answer it. The prices are there, thousands of them. The geolocation they were fetched from is not. It lived in the proxy configuration of the fetcher at fetch time, and the fetcher's config is not data. Nobody wrote it down per row, and now the entire dataset is geo-anonymous.

The core claim of this article: geography in a scraping pipeline is not a fetch-time configuration detail, it is first-class stored data, and the difference between pipelines that treat it as config and pipelines that treat it as data is the difference between datasets you can interrogate and datasets you can only count. And there's a subtlety most teams miss: you need to store two geo values per record — the geography you asked for and the geography you got — because they are not the same thing, and the gap between them is one of your best quality signals.

Two geo values, not one

When you send a fetch through a geo-targeted proxy, you express an intent: "exit in Germany, ideally Berlin, on a residential network." What actually happens is the provider's gateway picks an exit from current inventory. Most of the time it honors the intent. Sometimes the nearest available exit is in Frankfurt instead of Berlin. Occasionally the city-level inventory is empty and the gateway falls back to country-level. Rarely — and this is the dangerous case — the exit's geolocation in public databases disagrees with what the provider believes, and every geo-inferring downstream system sees a different country than you requested.

So the schema needs:

• geo_intent: what you asked the proxy for. Country, region, city, ASN, targeting mode. You know this before the request; it's your sampling design.
• geo_realized: what the exit actually was. Derived from the IP the provider reports back (or your own check of the exit IP's geolocation). You only know this after the request.
• geo_drift: not stored per se, but computed — intent vs realized mismatches, alertable, trendable.

A pipeline that stores only intent will silently mislabel rows during inventory shortfalls. A pipeline that stores only realized geo can't distinguish "we sampled Frankfurt because we wanted to" from "we wanted Berlin, got Frankfurt" — so it can't detect that its Berlin coverage quietly went to zero for two weeks. You need both columns, and you need the join between them to be queryable.

The schema

Here's the field layout I've converged on, as a SQLite DDL (it ports to Postgres with trivial changes):

CREATE TABLE fetch_geo (
    fetch_id      INTEGER PRIMARY KEY,
    ts_utc        TEXT NOT NULL,           -- ISO 8601, always UTC

    -- what we asked for
    intent_country TEXT NOT NULL,          -- ISO 3166-1 alpha-2
    intent_region  TEXT,                   -- ISO 3166-2
    intent_city    TEXT,                   -- provider city code
    intent_asn     TEXT,                   -- e.g. 'AS3320'
    targeting_mode TEXT NOT NULL,          -- country|city|asn|latlng

    -- what we got
    exit_ip        TEXT,                   -- provider-reported exit IP
    realized_country TEXT,
    realized_region  TEXT,
    realized_city    TEXT,
    realized_asn     TEXT,
    geo_source     TEXT,                   -- provider|local-db|none

    -- identity
    proxy_session  TEXT NOT NULL,          -- sticky session id
    session_age_req INTEGER,               -- request # within this session

    geo_match      TEXT                    -- exact|city_fallback|country_only|mismatch
);

Three details worth defending:

session_age_req. The number of requests already sent through this sticky session. Success rates decay with session age on many targets; storing the age turns your fetch log into the dataset for measuring that decay — which is how you size sticky windows in the first place, instead of guessing.

geo_source. Record how you know the realized geo. Provider-reported metadata, a local IP-geolocation database lookup, or nothing. These have different error profiles, and six months later when someone asks "how reliable is the country column," you want the answer to be a query, not archaeology.

geo_match as a precomputed bucket. You could derive it from the other columns, but materializing it makes the quality dashboard one GROUP BY and makes alerting trivial: mismatch rate above X% in an hour pages someone.

Writing it from Python

The fetcher side is deliberately boring — a small context manager that wraps a request, pulls the intent from the proxy URL construction, and resolves the realized geo after the fact:

# geo_stamp.py -- stamp every fetch with intent + realized geography.
# Python 3.8+, stdlib + requests.

import sqlite3
import time
import uuid
from contextlib import contextmanager

import requests

DB = sqlite3.connect("fetches.db")
DB.execute("""CREATE TABLE IF NOT EXISTS fetch_geo (
    fetch_id INTEGER PRIMARY KEY, ts_utc TEXT NOT NULL,
    intent_country TEXT NOT NULL, intent_region TEXT, intent_city TEXT,
    intent_asn TEXT, targeting_mode TEXT NOT NULL,
    exit_ip TEXT, realized_country TEXT, realized_region TEXT,
    realized_city TEXT, realized_asn TEXT, geo_source TEXT,
    proxy_session TEXT NOT NULL, session_age_req INTEGER, geo_match TEXT)""")

PROXY = {
    "http": "http://youruser-country-de-city-berlin"
            "-session-{sid}:[e-mail]:8000",
    "https": "http://youruser-country-de-city-berlin"
             "-session-{sid}:[e-mail]:8000",
}


class GeoSession:
    """A sticky proxy session that geo-stamps everything it fetches."""

    def __init__(self, country, city=None, region=None, asn=None):
        self.intent = dict(
            intent_country=country, intent_city=city,
            intent_region=region, intent_asn=asn,
            targeting_mode="city" if city else
                           ("asn" if asn else "country"),
        )
        self.session_id = uuid.uuid4().hex[:12]
        self.age = 0

    def fetch(self, url):
        proxies = {k: v.format(sid=self.session_id)
                   for k, v in PROXY.items()}
        resp = requests.get(url, proxies=proxies, timeout=30)
        self.age += 1
        self._stamp(resp, url)
        return resp

    def _resolve_geo(self, resp):
        # Priority 1: provider headers (many gateways echo exit info).
        ip = resp.headers.get("x-thordata-exit-ip")
        if ip:
            # lookup against your local geo DB; stubbed here
            return ip, "DE", "BE", "berlin", None, "provider"
        # Priority 2: an IP-geo endpoint you trust, called sparingly
        try:
            geo = requests.get(
                f"https://ipinfo.example/{resp.headers.get('x-exit-ip')}/json",
                timeout=10).json()
            return (geo.get("ip"), geo.get("country"), None,
                    geo.get("city"), None, "local-db")
        except Exception:
            return None, None, None, None, None, "none"

    def _stamp(self, resp, url):
        ip, rc, rr, rcity, rasn, src = self._resolve_geo(resp)
        intent_city = self.intent["intent_city"]
        match = "mismatch"
        if rc is None:
            match = "unknown"
        elif rc != self.intent["intent_country"]:
            match = "mismatch"
        elif intent_city and rcity and intent_city[:4] == (rcity or "")[:4]:
            match = "exact"
        elif intent_city:
            match = "city_fallback"
        else:
            match = "country_only"
        DB.execute(
            "INSERT INTO fetch_geo (ts_utc, intent_country, intent_region,"
            " intent_city, intent_asn, targeting_mode, exit_ip,"
            " realized_country, realized_region, realized_city, realized_asn,"
            " geo_source, proxy_session, session_age_req, geo_match)"
            " VALUES (datetime('now'),?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
            (self.intent["intent_country"], self.intent["intent_region"],
             intent_city, self.intent["intent_asn"],
             self.intent["targeting_mode"], ip, rc, rr, rcity, rasn, src,
             self.session_id, self.age, match))
        DB.commit()


# usage
sess = GeoSession(country="de", city="berlin")
r = sess.fetch("https://example.example/product/123")

The queries this unlocks

Once every row carries both geo values, the questions that used to require guesswork become one-liners:

-- Where is my sampling actually landing? (coverage audit)
SELECT realized_country, realized_city, COUNT(*) AS n
FROM fetch_geo GROUP BY 1, 2 ORDER BY n DESC;

-- Where did intent and reality disagree, and when did it start?
SELECT date(ts_utc) AS day, geo_match, COUNT(*) AS n
FROM fetch_geo WHERE geo_match IN ('mismatch','city_fallback')
GROUP BY 1, 2 ORDER BY day;

-- Which target+geo cells have gone quiet? (coverage gaps)
SELECT intent_country, intent_city, MAX(ts_utc) AS last_seen
FROM fetch_geo GROUP BY 1, 2
HAVING last_seen < datetime('now', '-1 day');

That third query is the one that earns its keep. In the Berlin/Frankfurt incident I mentioned at the top of a previous team's postmortem, the gap went unnoticed for two weeks because intent was recorded but realized geo wasn't — the pipeline believed it had Berlin coverage. With both columns, the coverage-gap query surfaces it the morning after inventory dries up.

Design rules worth keeping

Keep geography normalized (ISO codes, provider city codes) rather than free text, so joins against dimension tables are exact. Store UTC and convert at display time; a geo pipeline spanning timezones that stores local times will eventually lie to you about "daily" aggregates. Treat the geo block as a sidecar table keyed by fetch_id if your main records are wide — it keeps the dimension composable and lets you re-resolve geolocation later (databases update; your stored geo_source tells you which rows are worth re-checking).

And a rule for the schema's future: whatever targeting dimension you add next — ASN granularity, lat/lng radius, device type — give it the same intent/realized treatment from day one. The pattern generalizes: every dial you can set on a fetch is data about that fetch. Config forgets. Rows remember.

Disclosure: I use Thordata's residential proxies for this project. New users get 500MB free — code thor020 (10% off): https://www.thordata.com/?ls=uXcSHJzx&lk=02-tele