This 200-location franchise brand had a measurement problem: marketing data lived in 14 different systems with no unified view. Each monthly marketing report required 40+ hours of manual data aggregation from a team of analysts. By the time reports were assembled, the data was already 2 to 3 weeks old. We built a unified marketing ROI dashboard that connected all 14 data sources into a real-time, location-level analytics environment. The result: marketing decisions that previously took weeks now happen in minutes, and the franchise identified $680K in budget reallocation opportunities within 30 days of dashboard launch.
The Data Challenge
Data sources requiring unification: Google Ads (5 accounts), Meta Ads (3 accounts), Google Analytics 4, CRM (Salesforce), POS (3 different systems across the franchise), call tracking (CallRail), reputation management (Birdeye), Google Business Profile (via API), email platform (Mailchimp), social media management (Sprout Social), connected TV (The Trade Desk), SEO rank tracking (Semrush), website chatbot (Drift), and direct mail tracking (custom system). Each system had different data formats, different location identifiers, and different reporting periods. No system alone could answer the question: 'What is the marketing ROI for Location X this month across all channels?'
Solution: Cloud Data Warehouse and BI Layer
We built the solution in three layers. Layer 1: Data Ingestion. Connected all 14 data sources to a BigQuery data warehouse using Fivetran connectors and custom API integrations. Standardized location identifiers across all sources (mapping different naming conventions to a canonical location ID). Layer 2: Data Modeling. Built a data model in dbt (data build tool) that transformed raw data into analysis-ready tables. Key models: marketing spend by channel by location by day, leads and conversions by channel by location by day, revenue by location by day (from POS integration), and customer-level journey data connecting touchpoints to outcomes. Layer 3: BI Dashboard. Built interactive dashboards in Looker Studio with three views: executive (system-wide KPIs), regional (comparative location performance), and location (individual location detail).
Impact: $680K Budget Reallocation in 30 Days
Within 30 days of dashboard launch: identified 23 locations where Google Ads CPA exceeded 3 times the system average (campaigns paused, audited, and restructured). Discovered 15 locations where Facebook campaigns had been running to geographic areas with no franchise presence (wasted spend eliminated). Found that connected TV was driving 3 times more attributed conversions at 5 locations than last-click reports showed (budget increased). Identified email as the highest-ROI channel for 6 locations but with the lowest budget allocation (budget increased). Total budget reallocation opportunity identified and executed: $680K annual redistribution from low-performing to high-performing channel/location combinations. The dashboard reduced monthly reporting from 40+ analyst hours to automated real-time access.
Key Takeaways
- 14 data sources unified into a single BigQuery data warehouse
- Standardized location identifiers resolved cross-system data matching challenges
- $680K in budget reallocation opportunities identified within 30 days
- Monthly reporting reduced from 40+ analyst hours to real-time automated access
- Three dashboard views serve executives, regional managers, and franchisees distinctly
Want to implement these strategies?
Get a free franchise marketing audit from our team.
