📊 Marketing Performance Star Schema Dataset
Type: Performance Marketing Data Warehouse
Model: Star Schema
Rows (Fact Table): 12,000
Granularity:
👉 One row per Ad × Date × Geography × Device
1️⃣ Dataset Purpose
This dataset simulates real-life paid media performance across:
Social Media (Meta, TikTok, Snapchat)
Search (Google Search)
Display (Google Display)
It allows analysis of:
Funnel performance
Channel efficiency
Campaign profitability
ROAS
Device & geography performance
Audience targeting impact
2️⃣ Fact Table
🟦 Fact_Marketing_Performance
📌 Grain
One record represents:
Performance of a single Ad on a specific Date, in a specific Geography and Device.
🔑 Keys (Foreign Keys)
Column Connects To
Date_ID Dim_Date
Campaign_ID Dim_Campaign
AdSet_ID Dim_AdSet
Ad_ID Dim_Ad
Channel_ID Dim_Channel
Geo_ID Dim_Geography
Device_ID Dim_Device
Objective_ID Dim_Objective
📈 Metrics (Measures)
💰 Spend Metrics
Column Description
Amount_Spent Media spend in local currency
CPM Cost per 1,000 impressions
CPC Cost per click
Frequency Average number of impressions per user
📊 Traffic Metrics
Column Description
Impressions Total ad impressions
CTR Click-through rate (%)
Link_Click Number of link clicks
Link_Click_CTR Same as CTR (link-based)
Landing_Page_View Clicks that loaded page successfully
🛒 Funnel Metrics
Column Description
Add_To_Cart Users who added to cart
Checkout Users who reached checkout
Purchase Completed purchases
Purchase_Value Revenue generated
3️⃣ Funnel Logic Used
The dataset follows realistic marketing funnel behavior:
Impressions
↓ CTR
Link Click
↓ Landing Page Rate
Landing Page View
↓ ATC Rate
Add To Cart
↓ Checkout Rate
Checkout
↓ Purchase Rate
Purchase
↓ AOV
Purchase Value
Conversion rates vary by:
Objective type
Channel
Geography
Device
4️⃣ Dimensions
📅 Dim_Date
Time intelligence dimension.
Includes:
Day
Month
Quarter
Year
Week Number
Day Name
Supports:
MoM analysis
QoQ
Week trends
Seasonality
📢 Dim_Campaign
Campaign-level configuration.
Includes:
Campaign_Name
Campaign_Type (Prospecting / Retargeting)
Budget
Status
Start_Date
End_Date
Business Use:
Budget pacing
Campaign lifecycle analysis
Prospecting vs Retargeting performance
🎯 Dim_AdSet
Targeting layer.
Includes:
Audience_Type (Broad / Lookalike / Interest / Custom / Remarketing)
Age_Range
Gender
Placement
Business Use:
Audience performance comparison
Placement optimization
🖼 Dim_Ad
Creative-level analysis.
Includes:
Creative_Type (Image / Video / Carousel)
Copy_Version (A/B/C/D)
CTA_Type (Shop Now / Learn More / etc.)
Business Use:
Creative testing
A/B performance
CTA optimization
📱 Dim_Channel
Traffic source.
Includes:
Meta Instagram
Meta Facebook
Google Search
Google Display
TikTok
Snapchat
Platform Type:
Social
Search
Display
Business Use:
Channel efficiency
Cross-channel ROAS
Budget allocation decisions
🌍 Dim_Geography
Market dimension.
Includes:
Country
Region
City
Currency
Markets:
Egypt
UAE
Saudi Arabia
Jordan
Kuwait
Supports:
Multi-country analysis
Currency-adjusted performance
💻 Dim_Device
Device breakdown.
Includes:
Mobile (Android/iOS)
Desktop (Windows/macOS)
Tablet
Supports:
Device optimization
Mobile vs Desktop ROI
🎯 Dim_Objective
Campaign objective layer.
Includes:
Traffic (TOFU)
Conversions (MOFU)
Sales (BOFU)
Leads (MOFU)
This dimension directly impacts:
CTR
Conversion rates
AOV behavior