Who actually arrived: telling a reader, a crawler, and your own robot apart in one event table

You ship a small page. You add a counter. A week later the counter says thirteen visitors, and you start making decisions from it — this copy works, that aggregator sends traffic, the landing page is worth improving.

Then you look at the rows and find that four of the thirteen were not people at all. Two were your own deploy check. One was a crawler. One was a link-preview bot from a chat app, fetching your title so a stranger could see a card. You did not lose the visitors; you never had them. The number was never a count of people. It was a count of requests, and requests are made by programs.

This article is about the small event table that fixes that, the classification ladder that makes its numbers defensible, and the specific ways those numbers lie even after you build it properly. It is written for the case most solo builders are actually in: no analytics vendor, no consent banner, one table you control, and a need to answer "did anyone come, and did they do anything" without fooling yourself.

A row is an observation, not a person

Start with the honest description of the data. Each row records that something fetched a URL at a time, with a referrer it claimed, from an address your server saw, with a user agent it volunteered. Everything about a person is inference on top of that.

So the table should hold what you can defend, and nothing that pretends to know more:

Two of those deserve arguing.

Do not store raw addresses. A salted hash is enough to deduplicate a session and useless for identifying a household, which is the correct trade for a page that wants no cookie banner. Rotate the salt on a schedule you choose, because a stable salt is a pseudonym that outlives its purpose.

Do not delete your own traffic; flag it. Internal rows are the audit trail that your deploy, your check script and your monitoring all still work. Delete them and the next time the counter reads zero you cannot tell whether the page is fine or the instrumentation died. Keep the row, exclude it by is_internal = 0 in every query, and require a reason string so a later you can disagree with the classification.

The ladder: classify in order, and name the remainder honestly

The temptation is a single boolean, is_bot. That boolean will be wrong in both directions: it will mark a curious reader who pastes a link into a terminal as a robot, and it will let a headless browser through because it had no user agent at all.

Use an ordered ladder instead, first match wins:

1. Us. A path marker, a header, or an address we know is ours. Reason: probe, deploy-check, monitor.
2. Declared crawler. User agent matches a known crawler family. Reason: ua:googlebot.
3. Undeclared automation. No referrer, one event, no downstream event ever, user agent truncated or absent, or a burst at a machine cadence. Reason: shape:no-referrer-single.
4. Link preview / unfurl. A fetch of the HTML with a HEAD-like pattern or a request seconds after a share, often from a datacenter address, often from an app's preview service. Reason: shape:unfurl.
5. Unclassified. Everything else.

And then the naming discipline that matters more than the ladder: the last bucket is "not identified as a robot", not "human". Report it that way. A number labelled honestly is still useful; a number labelled wishfully gets built on.

The reason strings are the part that pays off months later. When you reclassify, you reclassify by reason, not by guessing which rows were which:

SELECT coalesce(nullif(internal_reason,''),'(none)') reason, is_internal, count(*) n, count(distinct ip_hash) ppl FROM traffic_events WHERE created_at >= ? GROUP BY reason, is_internal ORDER BY n DESC 

Your own traffic is the largest contaminant, and the easiest to fix

Every deploy you open the page to see whether the stylesheet loaded. Every check script you will write over the next month (see the companion piece on schedules and checks) touches the same route. Left alone, that is a steady drip of "visitors" that scales with how much you work — which is precisely backwards, because the busiest weeks look like the most successful ones.

Three fixes, in order of preference:

1. Separate origin. Put the pre-deploy view on a staging host. Then production can only be reached by production traffic and your own probes, of which there are few.
2. Mark the probe at the source. One header, checked once in the request handler, recorded as is_internal=1 with the reason. This is a two-line change and it survives every future script, because every future script copies the one you wrote today.
3. Recognise by address. A fallback when you cannot touch the code path: the egress address of your own worker, recorded with a reason like ip:worker. This is the weakest option: shared egress means you may swallow someone else's traffic, and a changed egress silently stops working.

The failure mode to plan against: an internal exclusion that quietly catches real people. That happens when the rule is broad — "user agent contains curl" — and someone genuinely arrives from a terminal. The reason string is what saves you: when the totals look wrong, sort by reason and read the rows.

Crawlers announce themselves, and that is all the user agent is worth

A user agent is self-reported and trivially forgeable, so it is evidence, never proof. What a crawler actually looks like in an event table is a shape, and the shape is more reliable than the string:

• no referrer, because the crawler was handed a URL rather than following a link on a page;
• a single event, because it does not click;
• a fetch of /robots.txt and a sitemap shortly before, if you log every path and not just your pages;
• a request pattern that repeats on a fixed interval across many URLs, which no reader does.

Reverse DNS is not the answer either — it is slow, frequently absent, and still spoofable in the direction that matters. If you need proof, verify that a claimed crawler address belongs to the crawler's published ranges, and cache the result. Otherwise accept that you are estimating, and keep the reason string so the estimate is auditable.

There is one more crawler class worth its own bucket because it confuses funnel math: the preview fetcher. When someone shares your link in a chat app, the app fetches the page to render a card. That request is a real request, sometimes from a real person's account, and it produces a view with no click, no scroll, and no second request. If your page's job is to get someone to press a button, a preview fetch is a false positive on the first funnel step, and it arrives exactly when you have shared the link somewhere — which is when you are most eager to read the numbers.

Time: measure at both ends, and watermark by id

Two habits make "nothing happened" a claim instead of a guess.

Measure twice: at the start and the end of the work. A job that reads the table at 06:54 and reports "zero new" is making a statement about a window it never defined. Read again at 07:01, and now the statement is: from row 687 to row 687, over the interval between two timestamps. Nine quiet minutes. That is a fact, and it is falsifiable.

This is not pedantry. Events land between runs. A visitor who arrives two minutes after one run's last read and before the next run's scheduled time is invisible to both the "no traffic" report and the "we had traffic" report, and you will spend an afternoon trying to explain why a channel appears to do nothing while the totals keep growing:

run start: SELECT max(id) mx, count(*) n, max(created_at) last FROM events; ... work ... run end: SELECT max(id) mx, count(*) n, max(created_at) last FROM events; delta: n_end - n_start, and every row with id > start.mx 

Watermark by id, not by timestamp. WHERE created_at > last_seen skips rows that share a second with the watermark — and in a burst, several rows do. Ids from a monotonically increasing sequence give you an exact, tie-free boundary. If you do not have one, take the timestamp as a boundary and the max id at that timestamp.

Store UTC; display local. One field, one timezone, converted at the edge. The alternative is a database whose meaning depends on which report you are reading, and a bug you will find at the worst possible hour.

Rates from tiny numbers are fiction

You will have days that look like this: thirteen view rows, two click rows. The arithmetic that leaps to mind is 15.4%, and it is worse than useless — it invites a decision about copy from a sample where one more click would move the number eight points.

The rule I use: report counts until n is large enough to make a rate meaningful, and always name the denominator. "Two clicks from thirteen arrivals, over 24 hours, two of the arrivals on different days" is honest. "15% click-through" implies a stability the data does not have.

The same caution applies to deduplication. Counting rows counts events; counting distinct ip_hash counts something like sessions. They answer different questions, and the gap between them is information: thirteen rows from thirteen hashes is thirteen separate arrivals; thirteen rows from three hashes is one enthusiast and a refresh button. Choose deliberately, and print both.

The referrer is the cheapest attribution you have, and it is mostly blank

The referrer header is the only free signal that says how someone arrived, and browsers strip it constantly: cross-origin navigation without a referrer policy, links opened from apps, and anything typed or pasted. So a blank referrer is not "direct traffic". It is "unknown", and labelling it "direct" in a report is how people conclude that their search traffic is worthless.

What the referrer is good for is chains. If your link is listed on an aggregator page, and that page links to three of your routes, you will see a recognisable shape:

And when a path has views but the next step in the funnel has never a single row from any referrer, that is a finding of its own — though not the finding you want. It can be a copy problem (nobody wanted to press it) or an instrumentation problem (the button's event never fires). The table alone cannot tell you which; you find out by being one of the arrivals and checking that your own click produced a row.

That check — do my own events appear, and with the shape I expect — is worth running after every change to the page. An event table nobody has verified against their own behaviour is a table of wishes.

What the table cannot tell you, and what to add instead

Three things people expect an event table to answer, which it does not:

• Whether someone converted. A view and a click in your table are both things that happened on your site. Whatever happens after the click happens in someone else's system — a storefront, an app store, an invite screen. If you cannot observe it, do not report it. Report the click through to your boundary and stop.
• Whether a page was read. A pageview is a fetch. Time-on-page requires a second event (a visibility change, a scroll), and even then it measures the tab, not the attention.
• Whether a channel "works". Channels produce arrivals, arrivals produce clicks, clicks produce whatever happens downstream. A channel with arrivals and no clicks is a messaging problem; a channel with no arrivals is a distribution problem. One number cannot distinguish them, and the most common analytic mistake in a solo project is adding distribution effort to a conversion problem.

The one addition that pays for itself: the same classification on the downstream event as on the arrival, so the funnel's steps are computed over the same population. If arrivals exclude robots and clicks do not, your conversion rate silently includes robots clicking.

Retention: keep raw rows until you know the questions

Aggregating early feels tidy and is a one-way door. Daily rollups answer "how many on Tuesday" and cannot answer "did the arrivals from that listing click", which is the question you will actually have in three weeks.

Keep raw rows long enough to cover the questions you can currently imagine asking — a few months for a personal project — then roll up to daily counts by path, event, referrer class, plus the distinct-person count. Aggregate by reason as well as flag, so a later reclassification does not require the raw rows you just deleted.

The checklist

• Every row records UTC time, normalised path, one of a small set of events, the raw referrer including blanks, a salted address hash, a device class and a probe marker.
• Internal traffic is flagged with a reason, never deleted.
• Classification is an ordered ladder ending in "not identified as a robot", not in a boolean named is_bot.
• User agent is a hint; the shape (no referrer, one event, no click, machine cadence) is the evidence.
• Preview fetchers are their own bucket, because they inflate the first funnel step at exactly the moment you are watching.
• The table is read at both the start and the end of any job that reports on it, and the report names the window.
• Watermarks are ids, not timestamps.
• Rates wait for n; until then, counts with a denominator.
• A blank referrer is "unknown".
• Downstream events carry the same classification as arrivals.
• Someone has personally verified that their own click produces the expected row.

None of this makes the table correct. It makes it auditable — which is the only property that matters, because the moment you trust a number you cannot explain, you have stopped measuring and started guessing with extra steps.

Disclosure: I build and write about small automation tools, and I run an English guide for an AI assistant app whose invite system I use as a working example of these ideas: Pickle invite code SY3LC2 — how to enter it and the invite link pickle.com/i/RDKXK77G9ZRT. If someone joins through that link, I receive in-app usage credit; it costs the person joining nothing. Nothing in the article depends on that product, and the analytics design described is generic.