ai-analyst-agent

### SQL Query
```sql
SELECT campaign_name, impressions, clicks, spend, conversions, ROUND((conversions / clicks) * 100, 2) AS conversion_rate
FROM ad_campaigns
WHERE campaign_status = 'active'
  AND start_date BETWEEN '2024-07-01' AND '2024-09-30'
  AND spend > 1000
ORDER BY conversion_rate ASC;
```

### Visualization Code (Python with matplotlib)
```python
import matplotlib.pyplot as plt
import pandas as pd

# Sample data (replace with actual query results)
data = {
    'campaign_name': ['Summer Sale', 'Holiday Boost', 'New Product Launch', 'Back to School'],
    'conversion_rate': [1.2, 0.8, 3.5, 2.1],
    'spend': [5000, 8000, 12000, 6500]
}
df = pd.DataFrame(data)

plt.figure(figsize=(10, 6))
plt.bar(df['campaign_name'], df['conversion_rate'], color=['red' if x < 2 else 'green' for x in df['conversion_rate']])
plt.title('Conversion Rates by Campaign (Q3 2024)', fontsize=14)
plt.xlabel('Campaign Name', fontsize=12)
plt.ylabel('Conversion Rate (%)', fontsize=12)
plt.xticks(rotation=45)
plt.grid(axis='y', linestyle='--', alpha=0.7)
plt.tight_layout()
plt.show()
```

### Insights
1. **Underperformance Alert**: The 'Holiday Boost' campaign has the lowest conversion rate (0.8%) despite being the second-highest spend ($8,000). This suggests a misalignment between ad creative and audience targeting.
2. **High ROI Opportunity**: The 'New Product Launch' campaign stands out with a 3.5% conversion rate and $12,000 spend, indicating strong performance that could be scaled further.
3. **Budget Reallocation**: The 'Summer Sale' campaign ($5,000 spend) underperforms in conversion rate (1.2%) but may benefit from creative refreshes or audience segmentation.

### Recommendations
1. **Pause or Optimize 'Holiday Boost'**: Allocate $4,000 of its budget to the 'New Product Launch' campaign and reallocate the remaining $4,000 to test new creatives for 'Summer Sale' with a focus on mobile users.
2. **A/B Test Creatives**: Run a 2-week A/B test for 'Holiday Boost' using two new ad variants (video vs. carousel) to identify which format drives higher conversions.
3. **Scale 'New Product Launch'**: Increase the daily budget for 'New Product Launch' by 30% ($3,600/month) and expand targeting to lookalike audiences based on past converters.

*Note: Replace the sample data with your actual query results for precise insights.*