Analyze weekly marketing campaign performance data across channels. Use when analyzing multi-channel digital marketing data to calculate funnel metrics (CTR, CVR) and compare to benchmarks, compute cost and revenue efficiency metrics (ROAS, CPA, Net Profit), or get budget reallocation recommendations based on performance rules.
SKILL.md
Marketing Campaign Analysis
Automated analysis of multi-channel marketing campaign data from BigQuery.
Data Source
Query data from BigQuery using the bigquery:execute_sql tool.
The user must specify a week to analyze. Accept formats like:
"Dec 9-15" or "December 9-15, 2024"
"2024-12-09 to 2024-12-15"
"week of Dec 9" or "last week"
If the date range is ambiguous, ask the user to clarify before querying.
Querying Data
Installs
0
Always filter by date range—never pull the entire table. Example query structure:
SELECT
channel,
SUM(impressions) as impressions,
SUM(clicks) as clicks,
SUM(conversions) as conversions,
SUM(spend) as spend,
SUM(revenue) as revenue,
SUM(orders) as orders
FROM `marketing-analytics-483823.marketing.campaign_performance`
WHERE date BETWEEN '2024-12-09' AND '2024-12-15'
GROUP BY channel
Adjust the query as needed for the specific analysis (e.g., group by segment, include daily breakdown).
Data Quality Check
Check for NULL values (Email channel won't have impressions)
Verify no negative values in numeric columns
Flag anomalies (e.g., conversions without clicks)
Funnel Analysis
Calculate per channel:
Click Through Rate (CTR) = clicks / impressions × 100
Efficiency Analysis Table:
| Channel | ROAS | Status | CPA | Status | Net Profit | Status |
Status indicators:
ROAS: "[OK] Above" if >= target, "[X] Below" if < target
CPA: "[OK] Below" if <= max, "[X] Above" if > max
Net Profit: "[OK] Positive" if > 0, "[X] Negative" if <= 0
Follow each table with brief channel-by-channel interpretation.
Budget Reallocation
If user asks about budget reallocation, read references/budget_reallocation_rules.md for the complete decision framework including eligibility rules, performance-based actions, and constraints.
