Promotional Banner Management

Objective

Create a promotional banner management system for marketing campaigns. This enables coordinated marketing efforts across channels with scheduled banner rotations.

Step 1: Create Campaigns Table

Define marketing campaigns.

CREATE TABLE marketing_campaigns (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    campaign_type VARCHAR(50),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    budget DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'draft',
    target_audience VARCHAR(255),
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Banner Placements Table

Define where banners can appear.

CREATE TABLE banner_placements (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(100) NOT NULL,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE
);

Step 3: Create Promotional Banners Table

Store banner images and metadata.

CREATE TABLE promotional_banners (
    id INTEGER PRIMARY KEY,
    campaign_id INTEGER NOT NULL,
    placement_id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    banner_image IMAGE(JPEG),
    banner_mobile IMAGE(JPEG),
    alt_text VARCHAR(255),
    click_url VARCHAR(500),
    priority INTEGER DEFAULT 0,
    impressions INTEGER DEFAULT 0,
    clicks INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    start_datetime TIMESTAMP,
    end_datetime TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES marketing_campaigns(id),
    FOREIGN KEY (placement_id) REFERENCES banner_placements(id)
);

Step 4: Create Banner Performance Table

Track banner analytics.

CREATE TABLE banner_performance (
    id INTEGER PRIMARY KEY,
    banner_id INTEGER NOT NULL,
    record_date DATE NOT NULL,
    impressions INTEGER DEFAULT 0,
    clicks INTEGER DEFAULT 0,
    conversions INTEGER DEFAULT 0,
    revenue DECIMAL(10, 2) DEFAULT 0,
    FOREIGN KEY (banner_id) REFERENCES promotional_banners(id)
);

Step 5: Insert Banner Placements

Define available positions.

INSERT INTO banner_placements (id, name, location, width, height, description) VALUES
    (1, 'Homepage Hero', 'homepage', 1920, 600, 'Main hero banner on homepage'),
    (2, 'Homepage Secondary', 'homepage', 600, 400, 'Secondary promotional area'),
    (3, 'Category Header', 'category_page', 1200, 300, 'Top of category pages'),
    (4, 'Sidebar Banner', 'sidebar', 300, 600, 'Right sidebar promotional'),
    (5, 'Product Page Banner', 'product_page', 800, 200, 'Above product details'),
    (6, 'Cart Page Banner', 'cart', 1000, 150, 'Shopping cart promotions'),
    (7, 'Email Header', 'email', 600, 200, 'Email marketing header'),
    (8, 'Mobile App Banner', 'mobile_app', 375, 200, 'Mobile app home screen');

Step 6: Insert Marketing Campaigns

Add sample campaigns.

INSERT INTO marketing_campaigns (id, name, campaign_type, start_date, end_date, budget, status, target_audience, created_by) VALUES
    (1, 'Summer Sale 2024', 'seasonal', '2024-06-01', '2024-08-31', 50000.00, 'active', 'all_customers', 'marketing_team'),
    (2, 'Back to School', 'seasonal', '2024-08-15', '2024-09-15', 25000.00, 'active', 'families', 'marketing_team'),
    (3, 'New Product Launch', 'product', '2024-07-01', '2024-07-31', 15000.00, 'active', 'tech_enthusiasts', 'product_team'),
    (4, 'Flash Sale Weekend', 'flash', '2024-07-20', '2024-07-21', 5000.00, 'completed', 'newsletter_subscribers', 'marketing_team'),
    (5, 'Holiday Preview', 'seasonal', '2024-11-01', '2024-12-31', 75000.00, 'draft', 'all_customers', 'marketing_team');

Step 7: Insert Promotional Banners

Add campaign banners.

INSERT INTO promotional_banners (id, campaign_id, placement_id, name, alt_text, click_url, priority, impressions, clicks, is_active, start_datetime, end_datetime) VALUES
    -- Summer Sale banners
    (1, 1, 1, 'Summer Sale Hero', 'Up to 50% off summer essentials', '/summer-sale', 10, 150000, 4500, TRUE, '2024-06-01 00:00:00', '2024-08-31 23:59:59'),
    (2, 1, 2, 'Summer Sale Secondary', 'Shop summer deals', '/summer-sale', 5, 120000, 2400, TRUE, '2024-06-01 00:00:00', '2024-08-31 23:59:59'),
    (3, 1, 3, 'Summer Category Header', 'Summer collection now available', '/category/summer', 5, 80000, 1600, TRUE, '2024-06-01 00:00:00', '2024-08-31 23:59:59'),
    (4, 1, 7, 'Summer Email Banner', 'Exclusive summer savings inside', '/summer-sale?src=email', 10, 50000, 2500, TRUE, '2024-06-01 00:00:00', '2024-08-31 23:59:59'),
    -- Back to School banners
    (5, 2, 1, 'Back to School Hero', 'Get ready for school - Save 30%', '/back-to-school', 10, 45000, 1350, TRUE, '2024-08-15 00:00:00', '2024-09-15 23:59:59'),
    (6, 2, 4, 'Back to School Sidebar', 'School supplies on sale', '/back-to-school/supplies', 5, 35000, 700, TRUE, '2024-08-15 00:00:00', '2024-09-15 23:59:59'),
    -- New Product Launch
    (7, 3, 1, 'New Product Hero', 'Introducing the all-new ProMax', '/products/promax', 15, 75000, 3750, TRUE, '2024-07-01 00:00:00', '2024-07-31 23:59:59'),
    (8, 3, 5, 'Product Page Cross-sell', 'Pair with ProMax accessories', '/products/promax-accessories', 5, 25000, 1250, TRUE, '2024-07-01 00:00:00', '2024-07-31 23:59:59'),
    -- Flash Sale
    (9, 4, 1, 'Flash Sale Hero', '48 Hours Only - Extra 20% Off', '/flash-sale', 20, 30000, 1800, FALSE, '2024-07-20 00:00:00', '2024-07-21 23:59:59'),
    (10, 4, 6, 'Flash Sale Cart', 'Flash sale ends soon - Complete your order', '/flash-sale', 15, 8000, 640, FALSE, '2024-07-20 00:00:00', '2024-07-21 23:59:59');

Step 8: Insert Performance Data

Add daily metrics.

INSERT INTO banner_performance (id, banner_id, record_date, impressions, clicks, conversions, revenue) VALUES
    -- Summer Sale Hero daily performance
    (1, 1, '2024-07-01', 5000, 150, 45, 2250.00),
    (2, 1, '2024-07-02', 5200, 156, 48, 2400.00),
    (3, 1, '2024-07-03', 4800, 144, 42, 2100.00),
    (4, 1, '2024-07-04', 6500, 260, 78, 3900.00),
    (5, 1, '2024-07-05', 5100, 153, 46, 2300.00),
    -- Flash Sale Hero performance
    (6, 9, '2024-07-20', 15000, 900, 270, 13500.00),
    (7, 9, '2024-07-21', 15000, 900, 285, 14250.00),
    -- New Product Launch
    (8, 7, '2024-07-01', 3000, 150, 30, 4500.00),
    (9, 7, '2024-07-02', 3500, 175, 35, 5250.00),
    (10, 7, '2024-07-03', 4000, 200, 40, 6000.00);

Step 9: Get Active Banners for Placement

Retrieve banners for a specific location.

SELECT
    pb.name,
    pb.alt_text,
    pb.click_url,
    pb.priority,
    mc.name as campaign
FROM promotional_banners pb
INNER JOIN marketing_campaigns mc ON pb.campaign_id = mc.id
INNER JOIN banner_placements bp ON pb.placement_id = bp.id
WHERE bp.location = 'homepage'
  AND pb.is_active = TRUE
  AND pb.start_datetime <= CURRENT_TIMESTAMP
  AND pb.end_datetime >= CURRENT_TIMESTAMP
ORDER BY pb.priority DESC;

Step 10: Campaign Performance Summary

Analyze campaign banner performance.

SELECT
    mc.name as campaign,
    mc.campaign_type,
    COUNT(pb.id) as banner_count,
    SUM(pb.impressions) as total_impressions,
    SUM(pb.clicks) as total_clicks,
    CAST(SUM(pb.clicks) AS DECIMAL) / NULLIF(SUM(pb.impressions), 0) * 100 as ctr_percent
FROM marketing_campaigns mc
LEFT JOIN promotional_banners pb ON mc.id = pb.campaign_id
GROUP BY mc.id, mc.name, mc.campaign_type
ORDER BY total_impressions DESC;

Step 11: Daily Performance Trends

View banner performance over time.

SELECT
    pb.name as banner,
    bp.record_date,
    bp.impressions,
    bp.clicks,
    bp.conversions,
    bp.revenue,
    CAST(bp.clicks AS DECIMAL) / NULLIF(bp.impressions, 0) * 100 as ctr,
    CAST(bp.conversions AS DECIMAL) / NULLIF(bp.clicks, 0) * 100 as conversion_rate
FROM banner_performance bp
INNER JOIN promotional_banners pb ON bp.banner_id = pb.id
WHERE pb.campaign_id = 1
ORDER BY bp.record_date;

Step 12: Top Performing Banners

Identify best performing banners by revenue.

SELECT
    pb.name,
    mc.name as campaign,
    bpl.name as placement,
    SUM(bp.impressions) as impressions,
    SUM(bp.clicks) as clicks,
    SUM(bp.conversions) as conversions,
    SUM(bp.revenue) as total_revenue,
    SUM(bp.revenue) / NULLIF(SUM(bp.clicks), 0) as revenue_per_click
FROM promotional_banners pb
INNER JOIN marketing_campaigns mc ON pb.campaign_id = mc.id
INNER JOIN banner_placements bpl ON pb.placement_id = bpl.id
INNER JOIN banner_performance bp ON pb.id = bp.banner_id
GROUP BY pb.id, pb.name, mc.name, bpl.name
ORDER BY total_revenue DESC
LIMIT 10;

Cleanup (Optional)

DROP TABLE IF EXISTS banner_performance;
DROP TABLE IF EXISTS promotional_banners;
DROP TABLE IF EXISTS banner_placements;
DROP TABLE IF EXISTS marketing_campaigns;

Expected Outcomes

• Campaigns organized
• Banners scheduled
• Performance tracked
• CTR calculated
• Revenue attributed

Campaign Types

Key Concepts Learned

• Campaign-banner relationships
• Placement targeting
• Performance tracking
• CTR and conversion metrics
• Revenue attribution