AI-Powered Campaign Intelligence Engine
The Challenge
Every Monday morning starts the same. A marketing manager opens HubSpot and searches for the campaign. They export lead data to CSV. Then they log into Monday.com, find the right board, and export budget and spend numbers. Another CSV file. Back to Google Sheets. They start copying data over. The CSV formats don't match. Campaign names are different across platforms. 'Q2 Lead Gen' in one place, 'Q2 Lead Gen Campaign' in another. They spend an hour just getting the data to line up. Then the real work starts: calculate conversion rates, figure out ROI, determine cost per lead, compare performance to goals. They write an executive summary. They identify strengths, weaknesses, opportunities, and threats(SWOT). They create recommendations for what to do next. They format everything so it looks professional. This takes 5-10 hours every week. By the time they finish, the team has already missed chances to adjust underperforming campaigns or double down on winners. The data is accurate, but it's too late to be useful. The real problem isn't the spreadsheet work. It's that talented marketing people spend their time doing data entry instead of making decisions that grow the business.
Our Approach
I built an automation system that connects all three platforms and uses AI to do the analysis. Type a campaign name in Google Sheets. The system pulls data from Monday.com and HubSpot, feeds it to AI, and returns a complete analysis with metrics, insights, and recommendations.
How It Works:
- •Trigger: Type a campaign name in Google Sheets
- •Data Collection: Python pulls data from Monday.com and HubSpot at the same time
- •Analysis: AI calculates all metrics (conversion rates, ROI, cost per lead), writes an executive summary, identifies strengths, weaknesses, opportunities, and threats, and creates action recommendations
- •Delivery: System creates a report in Monday.com and adds a link to Google Sheets
Automation Workflow
Google Sheets Trigger
Campaign entry detected via webhook
FastAPI Backend
Async processing and data retrieval
Monday.com & HubSpot
Concurrent API data aggregation
OpenAI Analysis
Structured analysis with Pydantic
Final Delivery
Automated report creation & logging
Results & Impact
Time Saved:
- •5-10 hours down to 5 minutes: What used to take half a workweek now runs automatically with no errors.
- •No more manual mistakes: Data stays consistent across all platforms
- •Marketing staff can do marketing: No more spreadsheet busy work
Business Impact:
- •Faster decisions: Get insights right away instead of waiting for manual reports
- •Better data trust: Reports are accurate and consistent every time
- •Real-time adjustments: Spot issues and fix campaigns immediately
Why This Matters:
- •Saves 250+ hours per year: One marketing manager gets half their time back to focus on strategy instead of spreadsheets
- •Works for other workflows: The same pattern can automate sales reporting, financial analysis, or any multi-platform data integration
- •Scales easily: Add more campaigns or platforms without changing the core system
Technical Deep Dive
FastAPI Backend
Built on FastAPI with Python 3.9+. I chose FastAPI over Flask because it has native async support, which I needed for concurrent API calls. It also generates automatic API documentation and integrates directly with Pydantic for validation.
The webhook endpoint at /webhook receives POST requests from Google Apps Script. When a campaign name comes in, the system searches both platforms, pulls data, generates analysis, creates a Monday.com document, and returns the URL.
Fuzzy Matching for Campaign Names
Campaign names differ across platforms. Marketing teams might write "Q2 Lead Gen" in Monday.com but "Q2 Lead Gen Campaign" in HubSpot. Exact string matching would fail.
I use the thefuzz library with 4 different matching strategies: basic ratio, token sort ratio, partial ratio, and token set ratio. Each returns a score. The system takes the highest score.
Default threshold is 75% match confidence. If a match hits 95% or higher, the system stops searching early. This handles typos, word order changes, and extra words without manual intervention.
Concurrent API Calls
Monday.com and HubSpot APIs respond in 2-4 seconds each. Running them sequentially would take 4-8 seconds. I use asyncio.gather() to run both at the same time. Total time: 2-4 seconds.
Each API call has its own error handling. If Monday.com fails but HubSpot works, the system generates a partial report with available data. Users get something useful instead of an error message.
Data Aggregation
Monday.com (GraphQL): Pulls board structure, budget columns, spend columns, and ROI data. Calculates total budget, total spend, and budget utilization. Filters out empty placeholder tasks.
HubSpot (REST API): Searches contacts using the CRM v3 Search API. Matches by custom property q2_lead_gen_campaign. Tracks traffic sources (paid search, paid social, email) for attribution analysis.
Structured AI Output
The AI does all the analytical work. It receives raw data from both platforms and calculates conversion rates, ROI, cost per lead, and engagement scores. It writes the executive summary. It performs the SWOT analysis. It creates actionable recommendations.
To keep this reliable, I define a Pydantic schema that specifies the exact structure: campaign name, executive summary, metrics (with specific fields like total_leads, conversion_rate, roi_percentage), insights (strengths, weaknesses, opportunities, threats), and recommendations (immediate actions, optimization strategies, budget adjustments).
OpenAI's response_format={"type": "json_object"} forces the response to match this schema. Temperature is set to 0.1 for consistent responses. Token limit is 2048, which keeps analysis concise.
Complete Data Flow
Google Sheets trigger → FastAPI webhook → Fuzzy match across Monday.com (GraphQL) and HubSpot (REST) → Concurrent data aggregation → OpenAI analysis with structured Pydantic schema → Monday.com document creation → URL returned to Google Sheets.