Introduction — Why affiliates need clean rooms now
Cookieless changes, tightening privacy law enforcement, and walled‑garden measurement controls have made many traditional affiliate attribution methods brittle. Clean rooms let brands and partners run privacy‑preserving joins and measurement on pseudonymized signals so affiliates and advertisers can attribute conversions without exchanging raw PII or moving unrestricted identity graphs off platform.
Major industry clean rooms include platform/big‑tech solutions such as Google Ads Data Hub (ADH) and Amazon Marketing Cloud (AMC), cloud services like AWS Clean Rooms, and vendor solutions (LiveRamp, Snowflake partners, Habu, InfoSum) — each with different tradeoffs in control, cost, and interoperability.
This article gives affiliate teams an operational playbook: simple, portable data models; example SQL/joins you can adapt to ADH/AMC/Snowflake; practical measurement queries (cohort conversion, last touch reconciliation, incrementality primitives); and rules of thumb for budgeting clean‑room work.
Core data model patterns for affiliate measurement
Design your clean‑room inputs so they are minimal, reproducible, and privacy compliant. Below are three practical table patterns that work across ADH, AMC, Snowflake and most vendor clean rooms.
1) Event-level ad impressions & clicks (platform export)
Columns (pseudonymized):
- platform_id — platform-specific pseudonym (e.g., Google anonymous ID / Amazon pseudo id)
- event_time_utc — ISO timestamp
- ad_unit — campaign/ad creative id
- event_type — impression / click
- signal_hash — hashed, salted identifier for join (see note)
2) Affiliate click / referral events (publisher export)
- pub_id — affiliate identifier (no PII)
- referral_time_utc
- referral_token_hash — hashed token created at click time
- utm_campaign / utm_medium — raw campaign metadata
3) Conversion / order events (advertiser export)
- order_id
- order_time_utc
- order_value
- conversion_token_hash — hashed token embedded server‑side at purchase or in postback
- conversion_flags — refund/chargeback markers
Note on hashing and salting: never share raw emails or device identifiers. Use a one‑way hash (SHA256) with a collaboration‑specific salt stored only in each party’s secure key store. Many clean rooms accept already‑hashed identifiers so you can perform deterministic joins without exposing raw identifiers.
Example queries — portable SQL patterns
Below are compact, annotated SQL snippets you can adapt to Snowflake, ADH or AMC (SQL dialects differ slightly; treat these as portable pseudo‑SQL).
A) Attribution join (match affiliate referral to conversion)
-- Resolve referrals to conversions using hashed tokens
WITH referrals AS (
SELECT pub_id, referral_time_utc, referral_token_hash
FROM publisher.referrals
),
conversions AS (
SELECT order_id, order_time_utc, order_value, conversion_token_hash
FROM advertiser.conversions
)
SELECT r.pub_id,
COUNT(DISTINCT c.order_id) AS conversions,
SUM(c.order_value) AS revenue
FROM referrals r
JOIN conversions c
ON r.referral_token_hash = c.conversion_token_hash
WHERE c.order_time_utc BETWEEN r.referral_time_utc AND r.referral_time_utc + INTERVAL '30' DAY
GROUP BY r.pub_id;
Use a time window appropriate to your product cycle (30 days is a common starting point for eCommerce affiliates).
B) Basic incrementality primitive (holdout vs exposed cohorts)
-- Requires a randomized experiment or an instrumental variable
SELECT cohort, COUNT(DISTINCT user_id) AS users, SUM(converted) AS conversions,
100.0 * SUM(converted) / COUNT(DISTINCT user_id) AS conv_rate
FROM (
SELECT user_id,
CASE WHEN random_value < 0.01 THEN 'holdout' ELSE 'exposed' END AS cohort,
MAX(CASE WHEN conversion_time IS NOT NULL THEN 1 ELSE 0 END) AS converted
FROM combined.events
GROUP BY user_id, random_value
) t
GROUP BY cohort;
Where randomized assignment may not be possible, use matched synthetic cohorts and propensity‑score techniques inside the clean room instead of exporting identifiers off‑platform.
Tip: many platform clean rooms (ADH/AMC) provide prebuilt event exports and require you to run queries inside their environment; adapt joins to platform IDs rather than raw user emails.
Cost benchmarks, provider tradeoffs and budgeting rules
There is no single price for a clean room: charges can include platform query fees (per‑query or compute‑minute), storage (S3/BigQuery/Snowflake), data egress rules, managed‑service setup/licensing, and specialized ML/modeling fees. Use the list below as rules of thumb and reference points.
- AWS Clean Rooms: pricing includes per‑million‑record pricing for ML training/inference plus the usual S3, Glue and compute charges when datasets are accessed in queries — expect charges for query compute and standard cloud storage/API usage. Budget for collaboration query costs and S3 access for each participant.
- Amazon Marketing Cloud (AMC): AMC is Amazon’s walled‑garden clean room for ad and shopper signals; it integrates with AWS tooling for uploads and analysis. Its value is direct access to Amazon signals, which can reduce work needed to normalize Amazon‑specific event schemas. Pricing and access models vary by advertiser and integration.
- Google Ads Data Hub (ADH): provides access to Google ad/event signals inside a controlled query environment. ADH often requires accredited access and is optimized for Google ad measurement workloads — expect procedural onboarding and query‑based limits rather than simple flat fees.
- Vendor clean rooms (LiveRamp / Snowflake / Habu / InfoSum): these vary from managed, interoperable cloud solutions (LiveRamp/Snowflake partners) to non‑movement architectures (InfoSum). They often charge a combination of platform licensing, per‑collaboration fees, and professional services. LiveRamp provides guidance on selection and integration considerations for advertisers.
Budgeting rules of thumb (affiliate programs):
- Small program (monthly affiliate revenue < $50k): expect to start with free or low‑cost analyses inside platform clean rooms (AMC/ADH where available) and/or use a managed partner for occasional queries — budget $2k–$8k/mo for ad hoc clean‑room work plus data engineering time.
- Mid program ($50k–$500k/month): plan for a multi‑party collaboration with recurring query budgets, storage and a managed integration — budget $8k–$30k/mo including partner fees and cloud compute.
- Large enterprise program (>$500k/month): you will need dedicated clean‑room infrastructure, frequent model runs, and SLAs — budgets commonly exceed $30k/mo and can include significant one‑time onboarding and modeling fees.
These ranges are directional. Exact costs depend on query volume, retention windows, number of collaborators, and whether you use managed services or platform‑native environments. For platform‑native details and the exact AWS billing model for ML/query usage, consult the provider docs.
Practical contracting tips
- Negotiate a clear definition of "collaboration query" and per‑query limits; include monthly/annual spend caps and monitoring dashboards.
- Specify data retention and output‑row thresholds to protect privacy and control costs.
- Include a runbook for dispute resolution (reconciliation samples, sample rates, and SLAs for query turnaround).
Sources and further reading: vendor docs and implementation guides for AWS Clean Rooms, ADH, AMC, LiveRamp and Snowflake.
Operational checklist & recommended next steps for affiliate teams
- Scope the use case: define the core question (attribution, incrementality, audience overlap) and expected outputs (publisher-level conversion counts, LTV buckets, cohort lift).
- Map inputs and owners: decide which hashed tokens each party will provide, who manages salts/keys, and how often exports run.
- Pick the right environment: if most spend is on Amazon, start with AMC; for Google‑centric measurement use ADH; if cross‑platform interoperability and custom modeling matter, consider Snowflake/Liveramp/Habu or AWS Clean Rooms.
- Prototype small & instrument for cost: run a 6–8 week pilot with a single affiliate cohort, measure query counts and compute, then iterate on data pruning and bucketing to reduce overhead.
- Governance: capture a privacy agreement, minimum disclosure rules, and an output‑table approval workflow so that results leaving the clean room meet legal and commercial requirements.
Conclusion: Clean rooms are not a silver bullet, but they are a practical, privacy‑first way for affiliates and advertisers to reconcile performance while respecting user privacy. Start with a focused use case, build a portable data model, and budget for query/compute costs early — that combination will deliver the fastest path to trustworthy affiliate measurement.
Key references: ADH and AMC platform docs, AWS Clean Rooms pricing, LiveRamp and Snowflake implementation guides.