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.

Location: marketing-analytics-483823.marketing.campaign_performance

Schema:

Required Input

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

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

1. Check for NULL values (Email channel won't have impressions)
2. Verify no negative values in numeric columns
3. Flag anomalies (e.g., conversions without clicks)

Funnel Analysis

Calculate per channel:

• Click Through Rate (CTR) = clicks / impressions × 100
• Conversion Rate (CVR) = conversions / clicks × 100

Compare to user-provided benchmarks, report difference in percentage points. If benchmarks not provided, use:

Efficiency Analysis

Calculate per channel:

• Return On Ad Spend (ROAS) = revenue / spend
• Cost Per Acquisition (CPA) = spend / conversions
• Net Profit = revenue - Total Costs
  • Total Costs = spend + (orders × Shipping Cost) + (revenue × Product Cost %)
  • Defaults: Shipping Cost = $8/order, Product Cost = 35% of revenue

Compare to user-provided targets. Defaults:

• Target ROAS: 4.0x minimum
• Max CPA: $50

Output Format

Present results as tables with status indicators:

Funnel Analysis Table: | Channel | CTR Actual | CTR Benchmark | CTR Diff | CVR Actual | CVR Benchmark | CVR Diff |

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.