OddsRabbit Ad Schema
Stickied
-- Advertisers table
CREATE TABLE advertisers (
advertiser_id SERIAL PRIMARY KEY,
company_name VARCHAR(255) NOT NULL,
contact_email VARCHAR(255) NOT NULL,
contact_name VARCHAR(255) NOT NULL,
billing_address TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
account_status VARCHAR(50) DEFAULT 'active' -- active, suspended, closed
);
-- Campaigns table
CREATE TABLE ad_campaigns (
campaign_id SERIAL PRIMARY KEY,
advertiser_id INTEGER REFERENCES advertisers(advertiser_id),
campaign_name VARCHAR(255) NOT NULL,
start_date TIMESTAMP NOT NULL,
end_date TIMESTAMP NOT NULL,
daily_budget DECIMAL(10,2) NOT NULL,
total_budget DECIMAL(10,2) NOT NULL,
status VARCHAR(50) DEFAULT 'draft' -- draft, active, paused, completed
);
-- Ads table
CREATE TABLE ads (
ad_id SERIAL PRIMARY KEY,
campaign_id INTEGER REFERENCES ad_campaigns(campaign_id),
title VARCHAR(255) NOT NULL,
body_text TEXT,
media_url VARCHAR(512),
landing_url VARCHAR(512) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(50) DEFAULT 'pending_approval' -- pending_approval, approved, rejected, active
);
-- Targeting table
CREATE TABLE ad_targeting (
targeting_id SERIAL PRIMARY KEY,
ad_id INTEGER REFERENCES ads(ad_id),
target_subreddits TEXT[], -- Array of subreddit IDs/names
target_interests TEXT[], -- Array of interest categories
target_locations TEXT[], -- Array of location codes
target_demographics JSONB, -- JSON for age ranges, gender, etc.
device_types TEXT[] -- desktop, mobile, etc.
);
-- Impressions table (for tracking views)
CREATE TABLE ad_impressions (
impression_id SERIAL PRIMARY KEY,
ad_id INTEGER REFERENCES ads(ad_id),
user_id INTEGER, -- can be NULL for anonymous users
subreddit_id INTEGER, -- where the ad was shown
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ip_hash VARCHAR(64), -- hashed IP for privacy
device_info VARCHAR(255)
);
-- Clicks table (for tracking engagement)
CREATE TABLE ad_clicks (
click_id SERIAL PRIMARY KEY,
impression_id INTEGER REFERENCES ad_impressions(impression_id),
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
conversion_type VARCHAR(50) DEFAULT 'click' -- click, purchase, signup, etc.
);
-- Billing/Transactions table
CREATE TABLE ad_transactions (
transaction_id SERIAL PRIMARY KEY,
advertiser_id INTEGER REFERENCES advertisers(advertiser_id),
campaign_id INTEGER REFERENCES ad_campaigns(campaign_id),
amount DECIMAL(10,2) NOT NULL,
transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
payment_method VARCHAR(50),
transaction_status VARCHAR(50) DEFAULT 'pending' -- pending, completed, failed, refunded
);
-- Campaign Spending table (to track daily and total costs)
CREATE TABLE campaign_spending (
spending_id SERIAL PRIMARY KEY,
campaign_id INTEGER REFERENCES ad_campaigns(campaign_id),
spend_date DATE NOT NULL,
daily_spend DECIMAL(10,2) DEFAULT 0.00,
total_spend_to_date DECIMAL(10,2) DEFAULT 0.00,
impressions_count INTEGER DEFAULT 0,
clicks_count INTEGER DEFAULT 0,
average_cpm DECIMAL(10,4) DEFAULT 0.00, -- Cost per thousand impressions
average_cpc DECIMAL(10,4) DEFAULT 0.00, -- Cost per click
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Ad Spending table (for per-ad metrics)
CREATE TABLE ad_spending (
ad_spending_id SERIAL PRIMARY KEY,
ad_id INTEGER REFERENCES ads(ad_id),
spend_date DATE NOT NULL,
impressions_count INTEGER DEFAULT 0,
clicks_count INTEGER DEFAULT 0,
spend_amount DECIMAL(10,2) DEFAULT 0.00,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
); Comments
No comments yet. Why don't you leave the first?