89 lines
3.8 KiB
Markdown
89 lines
3.8 KiB
Markdown
# Database Views Summary
|
|
|
|
## Overview
|
|
Updated the public schema with new views to make advertising analytics data from Meta, Google, and combined sources more accessible.
|
|
|
|
## Changes Made
|
|
|
|
### 1. Adapted `explore_schemas.py` Script
|
|
- Modified to save schema exploration output to `schema_info.txt`
|
|
- Provides persistent reference for table structures across meta, google, and alpinebits schemas
|
|
- Usage: `uv run explore_schemas.py`
|
|
|
|
### 2. New Views Added
|
|
|
|
#### **account_insights_by_country** ✓
|
|
- Aggregates Meta campaign insights by country to account level
|
|
- **Columns**: time, account_id, country, impressions, clicks, spend, link_click, landing_page_view, lead
|
|
- **Purpose**: Analyze Meta advertising performance by geographic region
|
|
- **Source**: meta.custom_campaign_country
|
|
|
|
#### **g_campaign_insights** ✓
|
|
- Google campaign-level insights with calculated performance metrics
|
|
- **Columns**: time, campaign_id, campaign_name, clicks, impressions, interactions, cost_micros, cost, conversions, all_conversions, conversions_value, ctr, cpm, cpc, cost_per_conversion
|
|
- **Purpose**: Unified Google campaign performance view with key metrics (CTR, CPM, CPC, Cost per Conversion)
|
|
- **Source**: google.campaign_metrics
|
|
|
|
#### **unified_account_insights_by_device** ✓
|
|
- Combines Meta and Google account insights broken down by device type
|
|
- **Columns**: time, google_account_id, meta_account_id, device, google_impressions, meta_impressions, total_impressions, google_clicks, meta_clicks, total_clicks, google_cost, meta_spend, total_spend, meta_link_clicks, meta_leads
|
|
- **Purpose**: Compare Meta vs Google performance by device (DESKTOP, MOBILE, TABLET)
|
|
- **Requires**: account_metadata table for linking Meta and Google accounts
|
|
|
|
#### **unified_account_insights_by_gender** ✓
|
|
- Meta audience insights broken down by gender
|
|
- **Columns**: time, meta_account_id, gender, impressions, clicks, spend, link_clicks, leads
|
|
- **Purpose**: Analyze Meta advertising performance by audience gender demographics
|
|
- **Source**: meta account_insights_by_gender
|
|
|
|
### 3. Existing Views (Not Modified)
|
|
The following views were already present and working correctly:
|
|
- **campaign_insights** - Base Meta campaign insights
|
|
- **campaign_insights_by_gender/age/device/country** - Meta campaign breakdowns
|
|
- **account_insights** - Aggregated Meta account insights
|
|
- **account_insights_by_gender/age/device/gender_and_age** - Meta account breakdowns
|
|
- **ads_insights** (materialized) - Individual ad performance
|
|
- **adset_insights** - Ad set level insights
|
|
- **g_account_insights** - Google account-level insights
|
|
- **g_account_insights_device** - Google account insights by device
|
|
- **unified_account_insights** - Combined Meta+Google account-level view
|
|
|
|
## Testing
|
|
All views have been tested and verified to:
|
|
- Execute without errors
|
|
- Return valid data with correct column structures
|
|
- Support LIMIT queries for performance verification
|
|
|
|
## Usage Examples
|
|
|
|
```sql
|
|
-- View Meta performance by country
|
|
SELECT time, country, SUM(impressions), SUM(spend), SUM(lead)
|
|
FROM account_insights_by_country
|
|
WHERE account_id = '1416908162571377'
|
|
GROUP BY time, country;
|
|
|
|
-- View Google campaign metrics
|
|
SELECT time, campaign_name, clicks, impressions, cpc, cpm
|
|
FROM g_campaign_insights
|
|
WHERE ctr > 2.0
|
|
ORDER BY time DESC
|
|
LIMIT 10;
|
|
|
|
-- Compare device performance across Meta and Google
|
|
SELECT time, device, total_impressions, total_clicks, total_spend
|
|
FROM unified_account_insights_by_device
|
|
ORDER BY time DESC;
|
|
|
|
-- Analyze Meta audience by gender
|
|
SELECT time, gender, impressions, clicks, spend, leads
|
|
FROM unified_account_insights_by_gender
|
|
WHERE time > CURRENT_DATE - INTERVAL '30 days'
|
|
ORDER BY time, gender;
|
|
```
|
|
|
|
## Next Steps
|
|
- Set up automated refreshes for materialized views if needed
|
|
- Create additional unified views for campaign-level comparisons
|
|
- Consider indexing frequently queried views for performance optimization
|