I want to walk you through a practical, privacy-first approach to building a first-party attribution layer. Over the last few years I've helped teams move away from black-box, third-party trackers toward server-side event collection and a compact, low-cost data warehouse that gives reliable attribution without leaking user data to ad platforms.
This is not theory — it’s a pragmatic architecture you can stand up quickly, iterate on, and keep under control financially. I’ll cover the event model, identity strategy, server-side pipeline, minimal warehouse choices, simple attribution SQL, and privacy guardrails you should enforce.
Why build a first-party attribution layer?
Putting attribution under your control solves several problems at once:
- Resilience to browser restrictions and third-party cookie deprecation.
- Cleaner data: you control schema, deduplication and sampling behavior.
- Privacy: you can limit PII, hash or tokenise identity, and audit access centrally.
- Cost control: cheap storage + compute for the queries you actually need.
My goal is a small, robust dataset you can use for ad measurement, creative testing, funnel analysis and activation — without handing raw user-level PII to third parties.
High-level architecture
Here’s the flow I usually implement:
- Client-side: lightweight event emitter that sends minimal first-party payloads to your server endpoint.
- Server-side ingestion: Cloud function (Cloud Run, AWS Lambda) or an open-source collector (Rudder, Snowplow collector) validates, enriches and deduplicates events, then writes to a low-cost data store.
- Warehouse: a small, queryable store (BigQuery, ClickHouse, or DuckDB-backed storage) for attribution queries and dashboards.
- Activation: aggregated audiences and conversions are pushed out to ad platforms with privacy controls (hashed identifiers, Google Ads conversion uploads, or private API activations).
Keep the server-side layer simple: its job is validation, enrichment, identity resolution (within privacy rules) and reliable delivery to the warehouse.
Event schema: keep it small and consistent
The single most important decision is designing a small, stable event schema. My default schema has these fields:
| field | type | notes |
|---|---|---|
| event_id | string | UUID generated client or server side |
| event_name | string | page_view, signup, purchase, click, etc. |
| event_time | timestamp | ISO timestamp, server authoritative if possible |
| client_id | string | first-party cookie ID — no 3rd-party ids |
| user_pseudonym | string|null | hashed email or user id, only when consented |
| utm_source/medium/campaign | string | campaign metadata |
| page_url | string | page or product context |
| value | float|null | transaction value when relevant |
| properties | json|null | small JSON blob for product ids, categories |
Less is more here. Avoid sending raw emails, phone numbers, or device fingerprints to the warehouse. If you must keep a link to identity for matching (e.g., email for server-side conversion uploads), hash it client-side or in the collector with a salted SHA256 and document the salt rotation policy.
Identity strategy and consent
Two identity tokens I rely on:
- client_id — a persistent first-party cookie (e.g., 2 years) used to link a sequence of anonymous events. No PII, no device fingerprinting.
- user_pseudonym — a hashed identifier set only after explicit user consent (or login). Use hashing with a rotating salt. Keep salted hashes only in the server-side store and avoid exposing them to front-end tools.
Consent drives when you create or persist user_pseudonym. If the user opts out, you should still collect fully anonymised, aggregated event counts for product analytics, but do not persist linking identifiers.
Server-side collector: choices and patterns
You can build a lightweight collector in a few ways:
- Cloud Run / AWS Lambda + API Gateway: low friction, pay-per-use, suitable for small to medium traffic.
- Open-source collectors: Snowplow/OpenReplay/RudderStack — more features for enrichment and routing.
- Self-hosted queue + consumer: use Kafka/Redpanda with a small consumer that writes Parquet batches to object storage.
Collector responsibilities:
- Validate schema and drop unexpected PII fields.
- Enrich events: reverse DNS, geo (coarse), UTM parsing.
- Deduplicate: de-duplicate by event_id within a short window.
- Batch writes to the warehouse: write hourly/daily Parquet files to object storage or stream to BigQuery/ClickHouse.
Low-cost warehouse options
Pick based on scale and team skillset:
- DuckDB + Object Storage — cheapest for small teams. Write hourly Parquet to S3/GCS, query locally or use a serverless SQL layer (e.g., Preset, Trino).
- ClickHouse — excellent for event workloads, fast and cheap at scale. Self-hosted on a small cluster can be very cost-effective.
- BigQuery — simple, serverless, good for bursty queries. For low-volume use you can keep storage cheap and control query costs by scheduled materialised tables.
- Snowflake — enterprise features but watch compute costs; not the cheapest for small projects.
I often prototype with DuckDB/Parquet locally or on a small VM, then move to BigQuery when query complexity grows or the team prefers a managed SQL interface.
Simple attribution logic — examples
Below are two simple SQL patterns I use: a last-touch conversion attribution and a time-windowed session attribution. Adapt these to your schema and your warehouse dialect.
Last-touch attribution (identify last non-null utm_source before conversion):
WITH conversions AS ( SELECT client_id, event_time AS conv_time, value FROM events WHERE event_name = 'purchase'),touches AS ( SELECT client_id, event_time AS touch_time, utm_source FROM events WHERE utm_source IS NOT NULL)SELECT c.client_id, c.conv_time, t.utm_source AS last_touch_source, c.valueFROM conversions cLEFT JOIN LATERAL ( SELECT utm_source FROM touches t WHERE t.client_id = c.client_id AND t.touch_time <= c.conv_time ORDER BY t.touch_time DESC LIMIT 1) t ON TRUE;Sessionization (30-minute window):
WITH ordered AS ( SELECT client_id, event_time, LAG(event_time) OVER (PARTITION BY client_id ORDER BY event_time) AS prev_time FROM events),sessionized AS ( SELECT *, SUM(CASE WHEN prev_time IS NULL OR TIMESTAMP_DIFF(event_time, prev_time, MINUTE) > 30 THEN 1 ELSE 0 END) OVER (PARTITION BY client_id ORDER BY event_time) AS session_id FROM ordered)SELECT client_id, session_id, MIN(event_time) AS session_start, MAX(event_time) AS session_end, COUNT(*) AS eventsFROM sessionizedGROUP BY client_id, session_id;Activation without sending PII
For ad measurement or audience activation, prefer these patterns:
- Server-side conversion uploads (Google Ads offline conversions, Meta’s Conversions API) using hashed identifiers and only when consented.
- Send aggregated conversions by cohort (e.g., campaign X, day, conversion count) instead of user-level exports.
- Use cohort-based attribution for lookalike or feed-based campaigns: export hashed cohort ids rather than raw emails.
Privacy guardrails I insist on
- Schema enforcement in the collector: drop unknown keys and redact PII.
- Hashing policy: SHA-256 with rotating salt for any identifier kept beyond ephemeral processing.
- Access controls: only analysts with a documented need can query user-level tables; everything else is aggregate-only.
- Retention policy: keep raw event-level data for a minimal period (e.g., 90 days) then aggregate and delete raw rows.
- Audit logs: record who exported what and when, especially for any identity-linked data.
Putting these controls in place lets you run reliable attribution and measurement while minimising privacy risk and compliance overhead.
If you want, I can sketch a starter Terraform template for a Cloud Run collector + BigQuery ingestion, or a minimal DuckDB/Parquet pipeline if you prefer ultra-low-cost. Tell me what cloud or stack you’re on and I’ll draft something tailored to your constraints.
