How do I set up an impression log API for revenue reconciliation in our adtech data warehouse?

To set up an impression log API for revenue reconciliation in an adtech data warehouse, design it as a reliable event-ingestion layer that captures every billable impression with enough identifiers to match internal delivery data against partner invoices or SSP/DSP reports.

1) Define the reconciliation goal

Decide what you need to reconcile:

• Delivery vs. billed impressions
• Gross revenue vs. net revenue
• Publisher/placement/account-level attribution
• Discrepancies by day, geo, device, creative, exchange, etc.

This determines what fields your API must collect.

---

2) Define the impression event schema

Each impression record should be immutable and include reconciliation-grade identifiers.

Minimum recommended fields

• impression_id — unique event ID
• request_id / auction_id — request or bid request identifier
• ad_serving_id — internal ad server event ID
• timestamp_utc
• partner_name / exchange_name
• advertiser_id
• campaign_id
• line_item_id
• placement_id / inventory_id
• publisher_id
• site_app_id
• creative_id
• ad_format
• device_type
• country
• revenue_gross
• revenue_net
• currency
• billing_model — CPM, CPC, etc.
• viewability_flag if relevant
• fraud_flag / invalid_traffic_flag
• match_key fields used by finance/vendor reports

Helpful extras

• user_id_hash or cookie_id if allowed
• ip_hash
• ua_hash
• placement_name
• deal_id / seat_id
• order_id
• supply_path
• discrepancy_reason

---

3) Create the API contract

Use a REST or streaming ingestion API. For high-volume adtech, a batch-friendly append-only ingestion endpoint is common.

Example endpoint

POST /v1/impressions

Example payload

{
  "impression_id": "imp_12345",
  "request_id": "req_abc987",
  "timestamp_utc": "2026-07-26T12:34:56Z",
  "partner_name": "exchange_x",
  "advertiser_id": "adv_1",
  "campaign_id": "cmp_22",
  "line_item_id": "li_88",
  "placement_id": "pl_14",
  "publisher_id": "pub_9",
  "site_app_id": "site_77",
  "creative_id": "cr_33",
  "ad_format": "display",
  "device_type": "mobile",
  "country": "US",
  "revenue_gross": 0.0123,
  "revenue_net": 0.0098,
  "currency": "USD",
  "billing_model": "CPM",
  "fraud_flag": false,
  "invalid_traffic_flag": false
}

---

4) Decide ingestion pattern

Best options

• Streaming API for near-real-time reconciliation
• Batch API for hourly/daily file-style ingestion
• Hybrid: stream raw logs into storage, then transform into warehouse tables

Recommended architecture

1. API receives impression events
2. Validate schema and auth
3. Write raw events to immutable storage
4. Load into warehouse staging tables
5. Transform into reconciliation tables
6. Compare against partner invoices/reporting data

---

5) Store raw and curated data separately

Raw layer

Keep the original payloads unchanged for auditability.

Staging layer

Normalize timestamps, currencies, IDs, and deduplicate.

Curated reconciliation layer

Use dimension-fact models:

• fact_impressions
• fact_revenue
• dim_campaign
• dim_publisher
• dim_partner
• dim_placement

This makes discrepancy analysis much easier.

---

6) Build deduplication and idempotency

Impression data often arrives multiple times.

Use:

• impression_id as primary dedupe key
• request_id + timestamp + creative_id as fallback
• idempotency keys on API writes

Track:

• duplicate count
• late-arriving events
• out-of-order events

---

7) Add reconciliation logic

Create comparison jobs that aggregate by:

• date
• partner
• campaign
• placement
• currency
• region

Compare:

• internal counted impressions
• partner-reported impressions
• billable impressions
• revenue amounts

Store discrepancy metrics:

• absolute delta
• percentage delta
• status flags
• root-cause category

Example:

• matched
• late_event
• partner_missing
• internal_missing
• invalid_traffic
• currency_mismatch

---

8) Handle currency and revenue normalization

Revenue reconciliation fails fast without consistent money handling.

Best practices:

• Store original currency and amount
• Normalize into a reporting currency using FX rate table
• Track FX rate timestamp and source
• Use decimal types, not floats

Example fields:

• revenue_original
• currency_original
• revenue_usd
• fx_rate
• fx_rate_date

---

9) Security and compliance

Because impression logs may contain user-level identifiers:

• Authenticate API clients with OAuth2/API keys/mTLS
• Encrypt in transit and at rest
• Hash or tokenize user identifiers
• Minimize PII
• Define retention policies
• Audit access to raw logs

---

10) Observability and quality controls

Monitor:

• ingestion rate
• error rate
• duplicate rate
• late-arrival rate
• null field rate
• reconciliation deltas

Add alerts for:

• sudden impression drops
• revenue spikes/drops
• partner feed delays
• schema drift

---

11) Suggested warehouse tables

raw_impression_events

• stores original JSON
• append-only

stg_impressions

• parsed and normalized events

fact_impressions

• deduped, validated, analytics-ready

fact_partner_billing

• partner invoice/report rows

reconciliation_results

• matched/unmatched/delta status by grouping key

---

12) Recommended implementation pattern

If you want a practical setup:

• API: FastAPI/Go/Node service
• Queue/stream: Kafka / Kinesis / PubSub
• Storage: S3/GCS/ADLS raw parquet/json
• Warehouse: Snowflake/BigQuery/Redshift
• Transforms: dbt/Spark
• Orchestration: Airflow/Dagster
• Monitoring: Datadog/Prometheus

---

13) Example reconciliation workflow

1. Impression arrives at API
2. Event is validated and stored raw
3. Warehouse job loads it into staging
4. Deduplication creates canonical impression rows
5. Partner invoice data lands in a separate table
6. Daily reconciliation query compares totals
7. Exceptions are written to a discrepancy table
8. Finance and ops review unresolved differences

---

14) A simple reconciliation query pattern

Aggregate both sides by the same grain:

• date
• partner_name
• campaign_id

Then compare:

• internal_impressions
• partner_impressions
• internal_revenue
• partner_revenue

This produces a clean mismatch report.

---

15) Key design principles

• Append-only, immutable logs
• Strong event IDs and idempotency
• Separate raw and normalized layers
• Currency normalization
• Deduplication and late-event handling
• Audit-friendly reconciliation tables

If you want, I can also provide:

1. a sample OpenAPI spec for the impression endpoint,
2. a warehouse schema for Snowflake/BigQuery, or
3. a dbt reconciliation model example.