Get started
Next.js

Add First-Party Affiliate Analytics to a Next.js Site (Without a Heavy Platform)

By SK · runs 20+ affiliate sites·11 min read·Updated 2026-07-28

In the redirect-route guide you built a /go/[slug] handler that captures every affiliate click first-party, on your server. Most people stop there, satisfied with a click count. But a click count isn't the goal — EPC per page is. The good news: you've already done the hard part. Turning those click records into per-page earnings takes a small table and a single join, and it needs no Mixpanel, no GA, no third-party pixel. Here's the whole thing.

Step 1: store the click

Your redirect route already calls recordClick. Point it at a minimal table. On Supabase (or any Postgres):

create table affiliate_clicks (
  id         bigint generated always as identity primary key,
  slug       text not null,
  page       text,
  created_at timestamptz not null default now()
);
create index on affiliate_clicks (page);
create index on affiliate_clicks (slug);

And the insert — derive the source page from the referer so every click knows where it came from:

// lib/analytics.ts
import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

export async function recordClick(e: { slug: string; from: string | null }) {
  const page = e.from ? new URL(e.from).pathname : null;
  await supabase.from("affiliate_clicks").insert({ slug: e.slug, page });
}

That's it for capture. Every click through /go/… now leaves a row that knows its slug and its source page.

Step 2: bring in the revenue

Clicks alone give you engagement per page. For earnings you need revenue, and here's the honest constraint: your network reports revenue at the tag or sub-ID level, not per individual click. There's a granularity gap between your click log and their payout report. You bridge it by aggregating both to the page and joining there.

Two ways to key revenue to a page. The clean one: pass the page (or slug) as a sub-ID in your affiliate links where the network supports it, so payouts come back already tagged by page. The simple one: periodically import the network's payout CSV into a small table keyed by page:

create table affiliate_revenue (
  page    text not null,
  revenue numeric not null,
  period  text not null,   -- e.g. '2026-07'
  primary key (page, period)
);

Either way, you now have revenue-per-page sitting next to your clicks-per-page.

Step 3: the join that produces EPC per page

Clicks joined with revenue gives EPC per pageYour first-party clicks per page, joined with the network's revenue per page, yields EPC per page.One join turns click records into EPC per pageCLICKS BY PAGE · yours/best-blenders 1,240/vitamix-review 880REVENUE BY PAGE · network payouts/best-blenders $520/vitamix-review $210⋈join on pageEPC BY PAGE/best-blenders $0.42/vitamix-review $0.24Your first-party clicks × the network's payout report = the one number that ranks your pages.
Clicks per page are yours to count first-party; revenue per page comes from the network's payout export. Join them on the page and you get EPC per page — the metric that decides what to write next.

Now the payoff — one query turns two tables into the number that ranks your whole site:

select
  c.page,
  c.clicks,
  r.revenue,
  round(r.revenue / c.clicks, 2) as epc
from (
  select page, count(*) as clicks
  from affiliate_clicks
  group by page
) c
join affiliate_revenue r using (page)
order by epc desc;

That epc column is the whole game. A page doing 1,240 clicks and $520 earns $0.42 a click; one doing 880 clicks and $210 earns $0.24. Same site, nearly double the earning power per click — and until you ran this, both just looked like "pages that get traffic."

What this unlocks

With EPC per page in a table you can sort, you can finally act on the things this hub keeps pointing at:

  • Find the leaks. High-traffic, low-EPC pages are where attention is going and money isn't — the content or intent is off, and now you can see it by name.
  • Double down on winners. Your highest-EPC page tells you the next five to write.
  • Prove a change worked. Sped up a page or restructured a roundup? Compare its EPC before and after, not just its traffic.

This is the affiliate analytics stack — traffic, clicks, revenue — assembled on your own infrastructure, first-party and cheap.

Being honest about the limits

This is deliberately lightweight, and it's worth knowing where it stops. It won't filter every bot click without extra rules, it aggregates rather than attributing click-by-click (the granularity gap is real), and it gives you queries, not a real-time dashboard or multi-touch attribution. For most affiliate operators, "which of my pages earns, and how much per click" is the question that actually moves revenue — and a table plus a join answers it. When you outgrow that, a dedicated analytics layer does the join, the bot filtering, and the dashboards for you.

Skip the SQL: Clickolytics does this join for you — first-party clicks tied to revenue, EPC per page, no table-wrangling — so you get the ranking without building the pipeline. See how it works →

Frequently asked questions

Do I need GA or Mixpanel? No — a redirect log plus one aggregation gives clicks and EPC per page; a platform only adds dashboards.

How do I compute EPC per page? Clicks per page from your table, joined to revenue per page from network payouts; EPC = revenue ÷ clicks.

Why not revenue per click directly? Networks report at the tag/sub-ID level, so you aggregate to the page and join there.

Is first-party tracking accurate? For counting real redirect clicks, yes — cleaner than ad-blocked client pixels, though bot filtering takes extra work.

Related reading

Related reading