The thing about click analytics is that every shortener on the market has them, and almost none of them will tell you the schema of the row they store. Here is ours. One row per click; everything you see in the dashboard rolls up from this table.

01What goes into a row

  • Timestamp in UTC, to the millisecond.
  • Link ID — the internal UUID, not the slug.
  • OS family and device type (mobile / tablet / desktop), derived from the User-Agent.
  • Browser family — Chrome, Safari, Firefox, in-app webview, and so on. We do not store the full UA string.
  • Country from the edge geo header. No region, no city.
  • Referrer host — the domain only. Not the full URL, not the path.
  • UTM tuple — source, medium, campaign, content, term — when present on the request.
  • Redirect type — which branch resolved (android, ios, fallback, or a rule ID).
  • Bot / preview flag — true when we detected a crawler or chat-link preview.
Not in the row

Raw IP address. Full User-Agent. Cookies. Browser fingerprint. Email. Device ID. The link slug appears in the URL but it is not stored on the click row — only the link ID is. Joining stays on our side.

02How we handle IP addresses

The IP shows up in the request once. We hash it with a server-side salt that rotates daily, store the hash for 24 hours, and use it for two things only: rate limiting on password gates, and abuse protection (someone hitting a single link 50,000 times in a minute). After 24 hours the hash is dropped.

We never write the raw IP to disk. We never write the hashed IP onto the click row. Joining the hash to the click ID is not possible.

03Bots and chat link previews

About 14% of the requests we see across the platform are not humans. Slack, iMessage, Facebook Messenger, X, LinkedIn, Discord, Telegram, every search-engine crawler, plus a long tail of monitoring services. We do not block them — blocking breaks chat link previews. Instead:

  1. Identify the bot via UA or known IP range.
  2. Tag the click row with is_bot = true.
  3. Filter them out of the dashboard charts by default.
  4. Let you opt-in to including them in CSV exports if you need the raw stream.
Half of fixing attribution is honesty about what you cannot measure. The other half is being clear about what you can.

04How long we keep clicks

  • Free — 30 days of click-level history, indefinite aggregate counts.
  • Pro — 365 days of click-level history.
  • Team — forever.

After the retention window, individual click rows are dropped. The aggregate counters per link survive — so you still see lifetime click totals, just without the underlying rows.

05Getting the data out

Pro and Team plans include CSV export from the dashboard. The CSV is the same schema as above, one row per click, with bots optionally included. Team plans get programmatic access through the API — same data, same retention.

If you are reading this because you are about to switch from a shortener that is heavier on the tracking side: yes, we are deliberately lighter. We think the small set of signals above is enough to run a serious mobile-growth program. If you find a thing you cannot answer with our data, email me — priya@getapproute.com. We have changed the schema before.