Medium Dashboards system

Lead Source Attribution Dashboard

An operational view that breaks inbound leads down by where they actually came from (form source tag, UTM, referrer, paid vs organic vs referral) and shows volume, share, and trend per source over a chosen window. It joins the CRM lead table to first-touch UTM/referrer capture so owners can see which channels feed the pipeline and which have gone quiet, instead of guessing. Built as a read-only Postgres-backed panel that any operator can open during weekly review.

Start a ProjectAll Systems
3 to 6 days
timeline
Medium
complexity
5
tools
4
steps

Built with real HMX dashboard tool paths

Supabase PostgresSQL views / materialized viewsSupabase Cron (pg_cron)Next.js 16 server componentsTypeScriptSupabase PostgresSQL views / materialized viewsSupabase Cron (pg_cron)Next.js 16 server componentsTypeScript

01 // System facts

System facts

Lead Source Attribution Dashboard uses a reporting model and review layer for Dashboards. An operational view that breaks inbound leads down by where they actually came from (form source tag, UTM, referrer, paid vs organic vs referral) a... The architecture connects a reliable source signal, supabase postgres, sql views / materialized, and owner review with an explicit control path.

Outcome

Owners can see, in one place, which channels are actually producing leads and which dried up — turning 'we think most come from referrals' into a sourced, current breakdown that informs where to spend attention.

Main risk

Garbage attribution: a large share of leads land as 'direct'/null because UTMs were dropped or the form didn't capture referrer, making the chart misleading.

Prevention

Normalize sources at capture and in the view, show a visible 'unattributed' bucket rather than hiding it, and reconcile counts against the raw leads total so nothing is silently dropped.

Fallback

If attribution coverage is too thin to trust, fall back to a simpler captured-source-tag-only breakdown plus an explicit 'unknown' slice, and flag the capture gap as the first fix before adding more channels.

System architecture

Lead Source Attribution Dashboard Architecture

6 nodes
a reliable source signal
a Postgres view that
Supabase Postgres
SQL views / materialized
Review Queue
Owner Review
  1. 01a reliable source signal

    An operational view that breaks inbound leads down by where they actually came from (form source tag, UTM, referrer, paid vs organic vs referral) a...

  2. 02a Postgres view that

    Write a Postgres view (or materialized view refreshed by Supabase Cron/pg_cron every 15-30 min) that aggregates leads by source_normalized x day/week with count, share %, and prior-period delta.

  3. 03Supabase Postgres

    Supabase Postgres contributes the trusted model for Lead Source Attribution Dashboard so metrics are defined before they are visualized.

  4. 04SQL views / materialized

    Render a server-component dashboard panel (Next.js 16 App Router, same pattern as app/admin/monitoring) with a bar/stack by source plus a small sparkline trend and a date-range selector reading the view via the service-role client.

  5. 05Review Queue

    If attribution coverage is too thin to trust, fall back to a simpler captured-source-tag-only breakdown plus an explicit 'unknown' slice, and flag...

  6. 06Owner Review

    Owners can see, in one place, which channels are actually producing leads and which dried up — turning 'we think most come from referrals' into a s...

How it is built

Build steps

An operational view that breaks inbound leads down by where they actually came from (form source tag, UTM, referrer, paid vs organic vs referral) and shows volume, share, and trend per source over a chosen window. It joins the CRM lead table to first-touch UTM/referrer capture so owners can see which channels feed the pipeline and which have gone quiet, instead of guessing. Built as a read-only Postgres-backed panel that any operator can open during weekly review.

  1. 01Confirm a reliable source signal: ensure each lead row stores a normalized source/UTM/referrer at capture (backfill a 'source_normalized' column with a CASE mapping for the messy real values like null, 'direct', and tagged campaigns).
  2. 02Write a Postgres view (or materialized view refreshed by Supabase Cron/pg_cron every 15-30 min) that aggregates leads by source_normalized x day/week with count, share %, and prior-period delta.
  3. 03Render a server-component dashboard panel (Next.js 16 App Router, same pattern as app/admin/monitoring) with a bar/stack by source plus a small sparkline trend and a date-range selector reading the view via the service-role client.
  4. 04Add a 'low/no volume' flag so a source that produced zero leads in the last N days surfaces as a review item, and document each source label so definitions stay stable.

Tools

Workflow surface

  • Supabase Postgres
  • SQL views / materialized views
  • Supabase Cron (pg_cron)
  • Next.js 16 server components
  • TypeScript
  • Inputs layer: Confirm a reliable source signal: ensure each lead row stores a normalized source/UTM/referrer at capture (backfill a 'source_normalized' column with a CASE mapping for the messy real values like null, 'direct', and tagged campaigns).
  • Transform layer: Write a Postgres view (or materialized view refreshed by Supabase Cron/pg_cron every 15-30 min) that aggregates leads by source_normalized x day/week with count, share %, and prior-period delta.
  • Metrics layer: Supabase Postgres contributes the trusted model for Lead Source Attribution Dashboard so metrics are defined before they are visualized.
  • Visualization layer: SQL views / materialized views handles refresh, review, or reporting delivery while normalize sources at capture and in the view, show a visible 'unattributed' bucket rather than hiding it, and reconcile counts against the raw lead...
  • Action layer: Owners can see, in one place, which channels are actually producing leads and which dried up — turning 'we think most come from referrals' into a s...

Data flow

  1. 01Confirm a reliable source signal: ensure each lead row stores a normalized source/UTM/referrer at capture (backfill a 'source_normalized' column with a CASE mapping for the messy real values like null, 'direct', and tagged campaigns).
  2. 02Write a Postgres view (or materialized view refreshed by Supabase Cron/pg_cron every 15-30 min) that aggregates leads by source_normalized x day/week with count, share %, and prior-period delta.
  3. 03Render a server-component dashboard panel (Next.js 16 App Router, same pattern as app/admin/monitoring) with a bar/stack by source plus a small sparkline trend and a date-range selector reading the view via the service-role client.
  4. 04Add a 'low/no volume' flag so a source that produced zero leads in the last N days surfaces as a review item, and document each source label so definitions stay stable.

Controls and fallbacks

  • Garbage attribution: a large share of leads land as 'direct'/null because UTMs were dropped or the form didn't capture referrer, making the chart m...
  • Normalize sources at capture and in the view, show a visible 'unattributed' bucket rather than hiding it, and reconcile counts against the raw lead...
  • If attribution coverage is too thin to trust, fall back to a simpler captured-source-tag-only breakdown plus an explicit 'unknown' slice, and flag...

Research basis