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:

Column	Type	Description
date	DATE	Campaign date
campaign_name	STRING	Campaign identifier
channel	STRING	Marketing channel
segment	STRING	Customer segment
impressions	INTEGER	Ad impressions (NULL for Email)
clicks	INTEGER	Number of clicks
conversions	INTEGER	Number of conversions
spend	FLOAT	Marketing spend in dollars
revenue	FLOAT	Revenue generated in dollars
orders	INTEGER	Number of orders

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