top of page

CASE STUDY

How a Real Estate Firm Saved $250K with Sales Forecasting

Industry: Real Estate & Construction
Solution Area: Sales Performance & Pipeline Forecasting
Tools Used: HubSpot, Fivetran, Microsoft SQL Server, Power BI

Lead Generation Dashboard.png

Business Impact

10+ hours/week saved​

​

by automating lead tracking across regions and sources.

​

~20% faster forecasting cycles

enabling leadership to identify pipeline gaps earlier.

​

$250K+ revenue safeguarded annually​

from improved marketing efficiency and reduced wasted ad spend.

The Challenge

The client was generating a high volume of leads and sales enquiries through HubSpot across multiple regions and channels (web, display homes, paid ads, referrals, etc.). However, they lacked a unified reporting view to answer critical questions:

  • How are enquiries trending across regions and channels?

  • Which sources (Google, display homes, referrals) are driving real deposits?

  • What are the conversion rates across stages (Enquiry → Consideration → Tender → Deposit)?

  • How far is the pipeline from hitting revenue targets?

Forecasting was manual, error-prone, and disconnected from real-time performance.

Our Solution

We built a centralized SQL model (HubSpot → SQL Server via Fivetran) and layered Power BI dashboards that combined Lead Trends with Forecasting Views.​​​

  • Unified pipeline data (leads → opportunities → tenders → deposits).

  • Forecast visuals with confidence bands to project deposit shortfalls.

  • Drilldowns by form, region, and source to pinpoint growth drivers and gaps.

  • Conversion scorecards at every stage (Enquiry → Consideration → Tender → Deposit).

Sales & Forecasting (1).png

Dashboard Walkthrough

1. Lead Generation and Conversion Dashboard

What’s on this page

  • Top KPIs: Total New Enquiries, Leads Deposited, Deposited %, Leads Lost/Archived.

  • Temporal trends: Monthly and Weekly charts for Enquiries, Considering, Tenders Presented, Deposited.

  • Form Type views: Enquiries by Form Type (monthly & weekly).

  • Regional views: Enquiries by Region (monthly & weekly).

  • Leaderboards:

    • New Enquiries by Sales Executive (last 30 days)

    • New Enquiries by Source (last 30 days)

  • Outcome distributions: Deals Deposited by Source (YTD), Deals Lost by Reason (YTD).

  • Operational table: Active Sales Pipeline by Executive (last 30 days).

​

​

Key questions it answers

  • Which form types and regions are driving volume lately?

  • What loss reasons dominate, and where should we intervene?

  • Which sources convert to deposits most reliably YTD?

​

Decisions & actions

  • Simplify or promote high-yield form types; fix forms with high drop-off.

  • Target loss-reason playbooks (e.g., price objection scripts, faster follow-ups, finance options).

  • Double down on sources that win deposits; trim low-yield sources.

 

This view from the dashboard highlights demand patterns across form types, regions, and sources.

Lead Gen.png

2. Forecasting Dashboard

What’s on this page

  • Progress cards: Targets vs Actuals and “Required to reach Budget” for New Deals and Tenders.

  • Forecast visuals: Monthly trend + forecast for New Enquiries, Leads Considering, Tenders Presented, and Deposited (with confidence bands).

  • Conversion scorecards: Overall conversion tables and ratio by stage (Enquiry→Considering→Tender→Deposit).

  • Regional/Source breakdowns: Conversion and stage counts by Region and Source.

  • Sales Exec conversion: Stage counts and ratios by Sales Executive.

  • Velocity summary: Median time between stages.

​

​

Key questions it answers

  • Are we ahead/behind the run-rate for budget?

  • If behind, how many additional tenders/deals do we need this month/quarter?

  • Where is conversion leaking—by stage, region, source, or executive?

​

​

Decisions & actions

  • Pull forward tender volume if forecast shows a shortfall; target regions/sources with better conversion.

  • Coach reps whose stage ratios or velocity lag peers.

  • Adjust budget pacing and campaign mix based on forecast deltas.

 

This forecast dashboard shows whether the funnel is on pace to hit deposit targets. Leaders can instantly see how many additional tenders or deals are required to meet monthly or quarterly budgets.

Forecasting (1).png

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 _fivetran_synced, _fivetran_deleted.

  • 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

Power BI Forecast visuals​

for predictive pipeline tracking.​

Dynamic budget parameters

for “Required to Hit Target” insights.​

Drill-down hierarchy​

 from year → quarter → month → week → day.

Incremental refresh

for near real-time updates.

Conclusion

The dashboard transformed lead management by unifying enquiries, opportunities, and deposits into a single view. Forecasting with confidence bands gave leaders forward visibility on revenue pacing. With automated insights, they can now intervene early, allocate resources effectively, and stay on track with business targets.

bottom of page