How Montgomery Homes Used Data to Improve Vehicle Tracking

• Industry: Construction & Real Estate

• Solution Area: Field Operations Optimization

• Tools Used: YouTrac API, Azure Data Factory, Microsoft SQL Server, Power BI, Power Automate

Business Impact

• Manual Coordination Reduced 

  Saved ~10–12 hours/week by eliminating phone calls and manual log updates for field workforce tracking​

• Faster Scheduling & Response

  Improved supervisor efficiency by ~15%, enabling quicker allocation of vehicles and teams

• Cost Savings

  Reduced fuel wastage and idle trips, saving an estimated ~$1,500/month in operational expenses​

The Challenge

A construction company needed a way to monitor the efficiency of their construction supervisors, who travel between job sites across NSW. Their existing workflow involved:

• Receiving weekly zipped tracking logs from YouTrac via email

• Using Excel macros to classify visits by job, office, or hardware store

• Producing PDF reports with minimal insights or consistency

The process was manual, error-prone, and made it difficult to evaluate team performance or optimize schedules.

Our Solution

We developed a fully automated, real-time Power BI dashboard using the YouTrac API. The solution combined:

• Azure Data Factory for API ingestion

• SQL Server for transformation and logic modeling

• Power BI for visualizing field activity

• Power Automate for scheduled report delivery

Key Capabilities

Timeline Visual per Supervisor (with Traffic Peak Shading)

Distance Traveled and Time Spent by Job Type

Supervisor Performance Ranking

Map View

KPIs Delivered

• Total Hours Worked

  Includes job site, office, and hardware visits

• Driving Hours & Distance

  Captured from GPS traces

• Job Site Visits

  Count and duration by job type

• Supervisor Ranking

  Based on point system

• Visit Classification

  Construction, Maintenance, Hardware, Office, Unknown

• Route Optimization

  Based on driving-to-site ratio​

• Start/Finish Times

  With color-coded activity blocks

Insights Unlocked

✅ Identified under-visited or inactive job sites

✅ Pinpointed supervisors with peak-hour driving inefficiencies

✅ Time allocation trends across job phases​

✅ Weekly performance trendline per team and individual

Technical Highlights

Data Ingestion

YouTrac API → ADF Pipelines → SQL Server

Purpose: To extract tracking data from YouTrac and convert raw JSON into structured SQL fields. This makes GPS coordinates, distances, and status values usable for reporting.

sql
-- Parse JSON records from YouTrac into structured fieldsSELECTJSON_VALUE(d.value, '$.status') AS status,
  JSON_VALUE(d.value, '$.dist') AS distance,
  JSON_VALUE(r.value, '$.lat') AS latitude,
  JSON_VALUE(r.value, '$.lng') AS longitude
FROM dbo.event_logs el
CROSS APPLY OPENJSON(el.data) d
CROSS APPLY OPENJSON(d.value, '$.records') r

Location Assignment Logic

Purpose: To classify each GPS point into a meaningful location such as job site, office, or hardware store. The code uses spatial distance (≤ 300m) and supervisor rules to determine the closest valid location.

sql

sql
-- Assign location based on GPS proximity (<= 300m)SELECT TOP 1 *FROM v_combined_locations loc
WHERE GEOGRAPHY::Point(@lat, @lng, 4326).STDistance(GEOGRAPHY::Point(loc.latitude, loc.longitude, 4326)) < 300ORDER BYCASEWHEN loc.supervisor = @supervisor THEN 1ELSE 2END, 
  loc.priority_order

Supervisor Ranking Logic

Purpose: To score supervisors based on productivity. Points are awarded for working 40+ hours and visiting assigned jobs. Deductions apply when a high percentage of driving occurs during peak traffic hours.

sql
-- Points for working 40+ hours and visiting assigned jobsSELECT 
  supervisor,
  SUM(CASE WHEN hours_worked >= 40 THEN 35 + (hours_worked - 40) ELSE 0 END) +SUM(CASE WHEN job_visit_pct = 1 THEN 15 WHEN job_visit_pct >= 0.85 THEN 5 ELSE 0 END) +SUM(CASE WHEN driving_peak_pct > 0.4 THEN -10 ELSE 0 END) AS total_score
FROM v_supervisor_performance

Impact

• Manual Hours Saved

  ~6–8 hours/week

• Insights

  Enhanced accountability & scheduling

• Report Accuracy

  +100% (removed Excel errors)

• Data Freshness

  Real-time via API

• Adoption

  Across construction, maintenance & teams