Overview
Trovant Media, a performance marketing agency specializing in health and beauty clients, was drowning in manual reporting. Every week, their team spent hours creating spreadsheets comparing month-over-month performance across TikTok, Google Ads, and Facebook Ads, then manually reconciling lead data from their Liine CRM system.
MetricMaven delivered a comprehensive automated reporting solution in weeks, integrating all marketing platforms and CRM data into a centralized BigQuery warehouse with real-time Looker Studio dashboards.
Result: Eliminated manual weekly reporting, provided real-time visibility into cost per lead and cost per booking by platform and service type, and freed the team to focus on optimization instead of spreadsheet work.
The Challenge
They had the data, but reporting it was slow, manual, and error-prone.
Trovant Media struggled with:
- Manual spreadsheet reporting every week
- Month-over-month comparisons built by hand
- Reconciling ad platform leads with Liine CRM bookings
- No automated cost per booking metric
- No visibility into performance by service type (Pilates, laser hair removal, microneedling, etc.)
- Hours lost to reporting instead of optimization
Each new report required rebuilding charts, manually matching leads, and double-checking calculations week after week.
The Solution
MetricMaven delivered a complete automated reporting solution in three weeks, structured across three development phases:
Phase 1: Data Integration & Modeling (Week 1)
Built the foundational data infrastructure:
- Airbyte Connectors: Configured automated data extraction from TikTok Ads API, Google Ads API, and Facebook Marketing API
- BigQuery Warehouse: Set up a secure data warehouse with proper schema design for marketing and CRM data
- dbt Models: Developed data transformation logic for business metrics and cost calculations
- Automated Refresh: Established daily data sync schedules, ensuring reports always reflect the latest performance
Phase 2: CRM Integration (Week 2)
Connected advertising spend to actual business outcomes:
- Liine CRM Integration: Direct connection to capture lead and booking data
- Lead Matching Logic: Mapped leads from ad platforms to CRM records for accurate attribution
- Cost Calculations: Built automated cost per lead and cost per booking metrics using CRM conversion data
- Data Validation: Implemented error handling and discrepancy detection between platforms
Phase 3: Dashboard Development (Week 3)
Created four comprehensive dashboard views:
1. Overview Dashboard
- Platform-level performance metrics (TikTok, Google Ads, Facebook Ads)
- Real-time cost per lead and cost per booking using Liine data
- Daily trend metric views
2. Week-over-Week Performance Tracking
- Trending metrics for leads, cost per lead, bookings, and cost per booking
- Visual trend lines replacing manual graph creation
- Month-over-month comparisons
3. Facebook/TikTok Forms Analysis
- Breakdown by service type (Pilates, laser hair removal, microneedling, etc.)
- Cost per lead and cost per booking by form/service
- Performance ranking for optimization insights
4. Day-over-Day Trend Tables
- Comprehensive tabular view of all key metrics
- Exportable data for additional analysis
- Historical performance tracking
Technical Architecture
MetricMaven delivered a lean, modular reporting infrastructure that connects ad platforms to CRM outcomes with minimal ongoing maintenance.
- Google BigQuery for data warehousing
- Airbyte for ETL orchestration
- dbt for data modeling and transformations
- Looker Studio for responsive dashboards with export capabilities
The Outcome
The automated reporting system was delivered in just 3 weeks and immediately replaced manual workflows.
- Manual reporting eliminated: Weekly spreadsheet creation removed entirely
- Real-time visibility: Metrics now update daily instead of weekly
- True cost per booking unlocked: Automated CRM matching replaced guesswork
- Service-level insights: First-time clarity into which services perform best by platform
- Scalable foundation: Infrastructure supports additional clients, platforms, and metrics
- 4-6+ hours saved per week: Time redirected from reporting to optimization