Skip to main content

OpenRTB Analytics Demo

Learn how to analyze real-time bidding (RTB) data with Rill using the OpenRTB Analytics demo project. This guide shows you how to track ad impressions, bids, wins, and revenue across different publishers, advertisers, and campaigns.

Step 1: Clone the Project

# Clone the OpenRTB Analytics demo
git clone https://github.com/rilldata/rill-examples.git
cd rill-examples/rill-openrtb-prog-ads

# Start Rill Developer
rill start

Visit http://localhost:9009 to explore your OpenRTB analytics dashboard.

Step 2: Project Structure

The project is organized as follows:

rill-openrtb-prog-ads/
├── rill.yaml # Project configuration
├── sources/ # Data source definitions
│ ├── auction_data_raw.yaml # Ad impression data
│ └── bids_data_raw.yaml.yaml # Bid request/response data
├── models/ # SQL transformations
│ ├── auction_data_model.sql # Workaround SQL to keep data up to date
│ └── bids_data_model.sql # Workaround SQL to keep data up to date
├── metrics/ # Defined measures and dimensions
│ ├── auction_metrics.yaml # Auction metrics
│ └── bid_metrics.yaml # Bids metrics
├── dashboards/ # Dashboard configurations
│ ├── auction_explore.yaml # Auction Explore dashboard
│ ├── bids_explore.yaml # Bid Explore dashboard
│ └── executive_overview.yaml # Canvas dashboard
└── README.md # Project documentation

Step 3: Data Sources

Bids Source

The bids source captures bid request and response data:

# sources/bids_data_raw.yaml
type: source
connector: "https"
uri: "https://storage.googleapis.com/rilldata-public/auction_data.parquet"

What this does:

  • Records all bid requests and responses
  • Tracks bid prices and win/loss status
  • Enables analysis of bidding patterns and market dynamics

Auction Source

The auction source tracks aggregated auction metrics and bid outcomes:

# sources/auction_data_raw.yaml
type: source
connector: "https"
uri: "https://storage.googleapis.com/rilldata-public/auction_data.parquet"

What this does:

  • Connects to the rilldata-public GCS bucket
  • Fetches aggregated auction data with key metrics
  • Tracks bid requests, responses, wins, and bid floors
  • Data includes volume metrics, performance ratios, and revenue data

Step 4: Data Models

In this case, we are not joining the data models and having two unique explore models and joining the visualization in a Canvas Dashboard.

Step 5: Creating your Metrics View

Metrics in Rill define the measures and dimensions that power your RTB dashboards:

# Metrics view YAML
# Reference documentation: https://docs.rilldata.com/reference/project-files/dashboards

version: 1
model: "auction_data_model"
type: metrics_view

timeseries: "__time"
smallest_time_grain: "hour"

measures:
- display_name: Total Requests
name: requests
expression: sum(bid_request_cnt)
description: Total Requests
format_preset: humanize
- display_name: "Avg Bid Floor"
name: avg_bid_floor
expression: "sum(bid_floor) / sum(has_bid_floor_cnt) "
description: "Average Bid Floor"
format_preset: currency_usd
- display_name: "1D QPS"
name: 1d_qps
expression: "sum(bid_request_cnt) / 86400 "
description: "1D QPS"
format_preset: humanize


dimensions:
- name: app_site_name
display_name: App Site Name
column: app_site_name
- display_name: App Site Domain
column: app_site_domain
name: app_site_domain
- name: pub_name
display_name: Pub Name
column: pub_name
- name: app_site_cat
display_name: App Site Cat
column: app_site_cat
- name: ad_size
display_name: Ad Size
column: ad_size
- name: device_state
display_name: Device State
column: device_state
- name: device_osv
display_name: Device OS Version
column: device_osv
- name: bid_floor_bucket
display_name: Bid Floor Bucket
column: bid_floor_bucket
- name: platform_browser
display_name: Platform Browser
column: platform_browser
- name: device_os
display_name: Device OS
column: device_os
- name: device_type
display_name: Device Type
column: device_type
- name: ad_position
display_name: Ad Position
column: ad_position
- name: video_max_duration_bucket
display_name: Video Max Duration Bucket
column: video_max_duration_bucket
- name: video_min_duration_bucket
display_name: Video Min Duration Bucket
column: video_min_duration_bucket
- display_name: Placement Type
column: placement_type
name: placement_type
- name: auction_type
display_name: Auction Type
column: auction_type
- name: app_or_site
display_name: App or Site
column: app_or_site
- name: device_country
display_name: "Device Country"
column: "device_country"

ai_instructions: ...

What this metrics file does:

  • Measures define the key RTB performance indicators:

    • Total Requests - Total number of bid requests processed
    • Avg Bid Floor - Average minimum acceptable price across auctions
    • 1D QPS - Daily queries per second (bid request volume)
    • avg_profit_margin_pct - Average profit margin percentage
  • Dimensions enable analysis across different segments:

    • app_site_name - Application or website name for publisher analysis
    • pub_name - Publisher name for performance tracking
    • ad_size - Ad unit dimensions (300x250, 728x90, etc.)
    • bid_floor_bucket - Grouped bid floor ranges for optimization analysis
    • device_country - Geographic performance by country
    • device_type - Mobile, desktop, or tablet device analysis
    • auction_type - Type of auction (first-price, second-price, etc.)
    • placement_type - Ad placement context (banner, video, native)

Step 6: Dashboard Exploration

Features

  • Explore Slice-and-Dice - For data exploration and ad-hoc analysis
  • Canvas - Traditional charts and visualizations
  • Pivot/Flat Table - Tabular data views with sorting and grouping
  • Measure's TDD - Granular Analysis of single measure

Selectors

  • Date range selector - Analyze specific time periods
  • Time Comparison Toggle - Compare previous time periods
  • Dimensions Comparison - Compare unique dimension values

Filters

  • Publisher filter - Focus on specific publishers
  • Advertiser filter - Analyze specific advertisers
  • Geographic filter - Regional performance analysis

Let's answer some questions basic questions!

For campaign Leafly_MarketMight, which site domains are getting the most bids?


Which US region has the highest activity and what devices are being used?


Compare Bids and Auctions.


These are just some of the insights that you can find within your explore dashboard but you'll find more hidden gems in your data as you continue to use Rill. Please let us know if you have any other questions!