How Real Estate Firm Optimized Marketing ROI by 30%

Industry: Real Estate & Construction

Solution Area: Marketing ROI & Channel Performance

Tools Used: HubSpot, Fivetran, Facebook Ads, Google Ads, TikTok Ads, Microsoft SQL Server, Power BI

Business Impact

8–10 hours/week saved

by eliminating manual campaign reporting and spreadsheet consolidation.

 30% better budget allocation

by identifying high-ROI channels and pausing low-performing ones.​

 $200K+ annual savings​

from improved marketing efficiency and reduced wasted ad spend.

The Challenge

The client invested heavily in digital marketing campaigns across Facebook, Google, and TikTok Ads, but had no unified visibility into which campaigns were truly generating deposits. Reporting was fragmented across platforms, and leaders couldn’t easily link spend to real revenue. Key unanswered questions included:

• Which channels and campaigns actually turn enquiries into deposits?

• Are paid conversion rates improving month over month?

• What is the ROI and CPL (Cost Per Lead) for each channel and campaign?

Our Solution

We set up automated Fivetran pipelines to sync ad data into SQL Server alongside HubSpot leads and revenue data. Power BI dashboards then unified the full funnel — from spend → enquiries → tenders → deposits → revenue.​

Fivetran connectors automatically pull data from Facebook Ads, TikTok Ads, and Google Ads into SQL Server every few hours. This ensures the marketing ROI dashboards always reflect the latest spend, clicks, and conversion performance without manual exports.

• Channel-level ROI reporting with conversion funnels.

• Monthly spend vs. revenue trends with ROI overlays.

• Campaign drilldowns showing clicks, spend, impressions, enquiries, deposits.

• CPL and ROI metrics calculated consistently across all platforms.

Dashboard Walkthrough

Purpose

Show which channels and campaigns actually turn enquiries into Deposits and revenue, and at what cost.

What’s on this page

• Top KPIs: New Enquiries, Paid-sourced Enquiries, Marketing Spend, Attributed Revenue, Fixed Cost per Lead.

• Channel funnels (YTD): Enquiries → Tenders Presented → Deposits by channel; Conversion Rate by Paid Channel.

• Monthly trends: Paid-generated enquiries; Paid → Tender and Paid → Deposit trends.

• Cost vs Revenue (monthly): Spend and revenue with ROI% overlay.

• Campaign drilldown: Facebook/Google/TikTok campaign table with clicks, impressions, spend, enquiries, deposits.

Key questions it answers

• Which channels/campaigns are creating enquiries that become deposits?

• Are we improving paid conversion rates month over month?

• What is ROI and CPL by channel and campaign?

Decisions & actions

• Scale high-ROI campaigns; pause/fix low-ROI ones.

• Reallocate spend toward channels with superior Tender→Deposit rates (not just clicks/leads).

• Share top-performing creatives/keywords with the team; test lookalikes.

Representative metrics

• CPL = Spend ÷ Paid Enquiries

• Channel Conversion = Deposits ÷ Enquiries (same channel)

• ROI% = (Attributed Revenue − Spend) ÷ Spend

• Paid Attribution uses last non-direct paid touch from campaign UTMs / HubSpot campaign mapping.

Technical Architecture & Process

1) Extract & Load with Fivetran

• Connector: HubSpot → SQL Server (destination).

• Objects synced: contacts, companies, deals, pipelines, deal_pipelines, engagements (calls/emails/meetings), marketing_emails, campaigns, web_analytics.

• Sync mode: Incremental (high-watermark on updatedAt), history/soft deletes via fivetransynced, fivetrandeleted.

• Schedule: 15–60 mins.

• Landing schemas/tables:

  • hubspot_raw.contacts, hubspot_raw.deals, hubspot_raw.companies, hubspot_raw.contact_company, hubspot_raw.engagements_*

  • fivetran_audit._metadata

Result: clean, append-safe raw HubSpot data in SQL Server with CDC.

2) Model in SQL Server (unify leads → opps → proposals → conversions)

• Dimensions: dim_contact, dim_company, dim_campaign, dim_date.

• Facts: fact_lead, fact_opportunity, fact_activity, fact_revenue.

• Keys: HubSpot native IDs; surrogate keys for dims.

• Unification View:

Result: a consistent SQL model tying leads → opps → proposals → won deals.

3) Publish to Power BI

• Connectivity: Import + Incremental Refresh (DirectQuery optional).

• Model: Star schema (facts → dims, raw tables hidden).

• Core Measures (DAX):

  • Pipeline: SUM(fact_opportunity[Amount])

  • Win Rate: DIVIDE([Won Deals], [Qualified Deals])

  • Velocity: avg days lead → opp → close

  • ROAS/CAC (if spend available)

  • Forecasting: Power BI Forecast or FORECAST.ETS DAX

• Security: RLS by owner, team, or region.

Power BI Functionality Used

Drill through

to move from high-level channel ROI to individual campaign results.

What-if analysis​

to simulate ROI impact of shifting spend between channels.

Dynamic parameters

to adjust budget targets and CPL thresholds.

Incremental refresh

for near real-time ad performance monitoring.

Conclusion

The Marketing ROI dashboard gave Montgomery Homes a single source of truth for spend, performance, and ROI across all digital channels. With campaign-level visibility, they optimized budget allocation, reduced wasted spend, and focused on the platforms that truly drive deposits and revenue growth.