AdTech Programmatic Analytics data model
Kimball warehouse for AdTech: high-volume bid, impression, and conversion transaction facts over conformed advertiser/campaign/creative/publisher/placement/audience/device dimensions on BigQuery, carrying cost, revenue, viewability, and multi-touch attribution credit.
Programmatic advertising analytics covering real-time bids, served impressions, and attributed conversions over conformed campaign, creative, advertiser, publisher, placement, audience, and device dimensions for RTB win-rate, delivery, viewability, ROAS, and attribution analytics. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 11
- tables
- 117
- columns
- 23
- relationships
- 1
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: BigQuery). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Three separate transaction facts at native grains
Bids, impressions, and conversions occur at vastly different volumes and have distinct measures (bid price, media cost, conversion value); separate facts avoid sparse columns and mismatched additivity.
Conformed dimensions across the funnel
Sharing campaign, creative, advertiser, publisher, audience, and device dimensions lets win-rate, delivery, and ROAS be drilled consistently from bid through conversion.
Audience sensitive-category flag and no device-level PII
GDPR/CCPA restrict targeting on special categories and storing raw identifiers; the audience grain carries a sensitivity flag and device is modelled as a non-identifying profile rather than a user.
Fractional attribution credit on the conversion fact
Multi-touch attribution distributes credit across touchpoints; an attribution_credit measure plus model/window makes ROAS allocation explicit and re-aggregatable.
Tables in the AdTech Programmatic Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (3)
fct_bid
Grain: one row per real-time bid request response
RTB transaction fact — one row per bid opportunity for win-rate and bid-density analytics.
| Column | Type | Keys |
|---|---|---|
| bid_key | bigint | PK |
| date_key | bigint | |
| campaign_key | bigint | |
| creative_key | bigint | |
| advertiser_key | bigint | |
| publisher_key | bigint | |
| placement_key | bigint | |
| audience_key | bigint | |
| device_key | bigint | |
| bid_request_id | varchar | |
| bid_timestamp | timestamp | |
| bid_price_cpm | decimal | |
| floor_price_cpm | decimal | |
| bid_count | smallint | |
| is_bid_submitted | boolean | |
| is_won | boolean | |
| clearing_price_cpm | decimal | |
| no_bid_reason | varchar |
fct_impression
Grain: one row per served ad impression
Impression transaction fact — the core delivery grain carrying cost, revenue, and viewability.
| Column | Type | Keys |
|---|---|---|
| impression_key | bigint | PK |
| date_key | bigint | |
| campaign_key | bigint | |
| creative_key | bigint | |
| advertiser_key | bigint | |
| publisher_key | bigint | |
| placement_key | bigint | |
| audience_key | bigint | |
| device_key | bigint | |
| impression_id | varchar | |
| auction_id | varchar | |
| impression_timestamp | timestamp | |
| impression_count | smallint | |
| media_cost | decimal | |
| data_cost | decimal | |
| total_cost | decimal | |
| revenue | decimal | |
| is_viewable | boolean | |
| is_clicked | boolean | |
| viewable_seconds | decimal | |
| is_fraud_flagged | boolean |
fct_conversion
Grain: one row per attributed conversion
Conversion transaction fact for attribution, ROAS, and CPA analytics.
| Column | Type | Keys |
|---|---|---|
| conversion_key | bigint | PK |
| date_key | bigint | |
| campaign_key | bigint | |
| creative_key | bigint | |
| advertiser_key | bigint | |
| publisher_key | bigint | |
| audience_key | bigint | |
| device_key | bigint | |
| conversion_id | varchar | |
| conversion_timestamp | timestamp | |
| conversion_type | varchar | |
| attribution_model | varchar | |
| attribution_window_hours | integer | |
| conversion_count | smallint | |
| conversion_value | decimal | |
| attribution_credit | decimal | |
| is_view_through | boolean |
Dimension tables (8)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_week | smallint | |
| week_number | smallint | |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| is_weekend | boolean |
dim_advertiser
Grain: one row per advertiser per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| advertiser_key | bigint | PK |
| advertiser_id | varchar | BK |
| advertiser_name | varchar | |
| industry_vertical | varchar | |
| agency_name | varchar | |
| account_manager | varchar | PII |
| billing_country | varchar | |
| is_active | boolean |
dim_campaign
Grain: one row per ad campaign per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| campaign_key | bigint | PK |
| campaign_id | varchar | BK |
| campaign_name | varchar | |
| campaign_objective | varchar | |
| buy_type | varchar | |
| pricing_model | varchar | |
| flight_start_date | date | |
| flight_end_date | date | |
| budget_amount | decimal | |
| campaign_status | varchar |
dim_creative
Grain: one row per ad creative
| Column | Type | Keys |
|---|---|---|
| creative_key | bigint | PK |
| creative_id | varchar | BK |
| creative_name | varchar | |
| creative_format | varchar | |
| ad_size | varchar | |
| duration_seconds | smallint | |
| landing_url | varchar | |
| is_third_party_tag | boolean |
dim_publisher
Grain: one row per publisher/supply source
| Column | Type | Keys |
|---|---|---|
| publisher_key | bigint | PK |
| publisher_id | varchar | BK |
| publisher_name | varchar | |
| supply_source | varchar | |
| inventory_type | varchar | |
| domain | varchar | |
| is_brand_safe | boolean |
dim_placement
Grain: one row per ad placement/slot
| Column | Type | Keys |
|---|---|---|
| placement_key | bigint | PK |
| placement_id | varchar | BK |
| placement_name | varchar | |
| ad_unit | varchar | |
| position | varchar | |
| environment | varchar | |
| is_viewable_position | boolean |
dim_audience
Grain: one row per audience segment
| Column | Type | Keys |
|---|---|---|
| audience_key | bigint | PK |
| audience_id | varchar | BK |
| audience_name | varchar | |
| segment_type | varchar | |
| data_provider | varchar | |
| targeting_category | varchar | |
| is_sensitive_category | boolean |
dim_device
Grain: one row per device profile
| Column | Type | Keys |
|---|---|---|
| device_key | bigint | PK |
| device_code | varchar | BK |
| device_type | varchar | |
| operating_system | varchar | |
| browser | varchar | |
| connection_type | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_bid.date_key → dim_date (many-to-one)
- fct_bid.campaign_key → dim_campaign (many-to-one)
- fct_bid.creative_key → dim_creative (many-to-one)
- fct_bid.advertiser_key → dim_advertiser (many-to-one)
- fct_bid.publisher_key → dim_publisher (many-to-one)
- fct_bid.placement_key → dim_placement (many-to-one)
- fct_bid.audience_key → dim_audience (many-to-one)
- fct_bid.device_key → dim_device (many-to-one)
- fct_impression.date_key → dim_date (many-to-one)
- fct_impression.campaign_key → dim_campaign (many-to-one)
- fct_impression.creative_key → dim_creative (many-to-one)
- fct_impression.advertiser_key → dim_advertiser (many-to-one)
- fct_impression.publisher_key → dim_publisher (many-to-one)
- fct_impression.placement_key → dim_placement (many-to-one)
- fct_impression.audience_key → dim_audience (many-to-one)
- fct_impression.device_key → dim_device (many-to-one)
- fct_conversion.date_key → dim_date (many-to-one)
- fct_conversion.campaign_key → dim_campaign (many-to-one)
- fct_conversion.creative_key → dim_creative (many-to-one)
- fct_conversion.advertiser_key → dim_advertiser (many-to-one)
- fct_conversion.publisher_key → dim_publisher (many-to-one)
- fct_conversion.audience_key → dim_audience (many-to-one)
- fct_conversion.device_key → dim_device (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.