Logs, APIs, and the Warehouse: A GEO Data Pipeline

At an October review, a smart-lock brand selling into the US and Germany puts four tables on one screen: retrieval-crawler requests to product pages falling since a CDN migration, brand mentions rising, a few orders tagged “chatgpt referral”, a shopper’s screenshot asking whether the lock fits a two-inch door. All four tables are right. The lock has three names across them.

A GEO dashboard does not fail for lack of charts. It fails because nothing in it shares a key — settle the source, the key, and the refresh rate first, or the join turns four grains into one precise, wrong number.

Four tables, all correct, one bad sum

Each table counts a different thing: a log row is one request, a search-console row one page × one query, an AI sample row one answer, an order row one transaction. Summing them adds requests to orders.

Join request-level logs to order-level revenue and a page fetched a hundred times duplicates that order a hundred times. Join sample-level answers to transactions and twenty questions sit against one order, so the conversion rate has a denominator that means nothing. Neither query errors; both produce a tidy number for a slide.

Source	Question it answers	One row is	Usual trap
Access log / CDN	Did a crawler arrive	One request	Sampled export
Search console	Is the brand searched	Page × query	Brand and category queries mix
AI answer samples	Mention, citation, shortlist	One answer	Sample size unrecorded
Orders, CRM, tickets	Who bought, returned	One transaction	Channel is free text
PIM / feed	Do page and feed agree	One SKU	Feed trails the page

The table shows which source you lack: product records in a category manager’s spreadsheet, the log a sampled CDN export. Missing a source is survivable; pretending you have it is not — see fixing product master data first.

Three keys, or no denominator

GEO joins need three keys, not one: SKU plus variant and GTIN, canonical URL plus locale, entity ID plus sameAs. Mixing them is the most common GEO data error.

Lifetimes differ, so one key cannot carry history: a SKU dies with the next generation, a URL with a slug edit, the brand entity persists. Make the page primary and every slug change cuts a series in half.

One-to-many must be explicit: one SKU across five locales is five URLs, one page with three colors is three SKUs. Dealer and white-label pages look like you and are not you; they belong to the brand layer, not the product layer.

Every ID map row carries an effective date, or last month’s report disagrees with today’s. Put the unmatched rate on the dashboard: log rows resolving to no SKU are the bias in every SKU metric.

Refresh by purpose, keep the misfits

Four cadences are enough: crawl daily, answers weekly, orders monthly, master data on change. Faster costs more and turns variance into trend.

Crawl health daily; 403s and robots changes same-day. Answer samples weekly, since daily sampling reports noise as movement. Orders daily but settled monthly, since refunds rewrite history. Master data on change, so page and feed agree.

Four layers, each failing quietly.

Stage	Does	Fails as
Collect	Log exports, API pulls, sample uploads	One missing day; the weekly line breaks
Normalize	UTC date, currency, URL without parameters	One page with utm tags counted as twenty
Join	Apply the ID map; park unmatched rows	Unmatched dropped silently, so the metric improves
Serve	Wide table, definitions, permissions	Three people, three queries, three mention rates

One hard rule: unmatched records go to their own table, never the bin. They are a quality metric and the first sign of a new SKU or channel. Discarded rows return next quarter as “the numbers don’t match”.

Five steps, back from three questions

Start from the three questions this quarter must answer, then pick sources. More pipes is more maintenance, not more accuracy.

Step	Do	Owner	Passes when
1 Name the questions	Crawl health, answer rank, orders from AI	Growth	Each source points at a decision
2 Build the ID map	SKU, GTIN, canonical URL, locale, effective date	Engineering	Twenty orders and log rows each resolve once
3 Write the definitions	Numerator, denominator, window, sample size	Growth, finance	Two people compute the same figure
4 Ship the minimum	Daily logs, weekly samples, monthly orders	Data	Four weeks without a gap; unmatched rate visible
5 Attach the review	Three numbers and a confidence column	Growth	One metric never appears twice in a meeting

Step 3 is the one teams skip. Without it, a stable pipeline only moves the argument from “what is the number” to “how did you compute yours”. Borrow the four layers of AI visibility as the skeleton for your definitions.

Example: one lock through the pipeline

Example — brand, model, paths, and figures are invented; only the field matching is reusable.

On 8 October at 02:14 UTC a crawler fetches a product page and gets a 200. Normalized, the URL loses its parameters; the ID map resolves it to matte black and brushed nickel. One page, two SKUs.

On 9 October one prompt runs five times: three answers name the brand, one carries a clickable source, none place it on a shortlist. Sample size, model version, and region node go in the row.

On 12 October an order tagged “chatgpt referral” arrives; two weeks later it is returned: “app pairing failed” — also a content finding.

Three rows, one SKU, one URL, one window — before the pipeline, four tables under three names.

Where this breaks

Too little volume. A few dozen log hits a week makes weekly conclusions noise; run crawl health and a monthly manual read.

No raw data. Marketplace-only sellers and storefronts that expose reports but not logs run a two-source pipeline: useful, not evidence-grade. For crawl questions, start with the AI crawler map.

A ceiling on attribution. AI referrals vanish into copy-paste, cross-device journeys, and dark social, so the pipeline gives a floor. Say “identifiable AI referrals”, never “all orders from AI”.

A black-box vendor. Opaque sampling, model versions, and nodes make months incomparable; the contract should say whether raw rows export.

Sources that disagree. When site, reviews, and media describe different companies, a clean join assembles the contradiction better — a brand narrative consistency problem.

This month, take twenty log rows, twenty order lines, and the last AI sample set, and align them by hand on one ID map. The column you cannot fill is where your pipeline starts. The rest of this line lives in technical foundations.