top of page

CASE STUDY

Vehicle Tracking Report for a Construction Company

Industry: Construction & Real Estate
Solution Area: Field Operations Optimization
Tools Used: YouTrac API, Azure Data Factory, Microsoft SQL Server, Power BI, Power Automate

Map View.png

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

Architecture Diagram.png

Key Capabilities

Timeline Visual per Supervisor (with Traffic Peak Shading)

Timeline Visual per Supervisor (with Traffic Peak Shading).png

Distance Traveled and Time Spent by Job Type

Distance Traveled and Time Spent by Job Type (1).png

Supervisor Performance Ranking

image (2).png

Map View

Map View.png

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

-- 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 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

-- 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

bottom of page