Template Guide and Library

EN - CS-CZ - DE-DE - ES-ES - ES-LATAM - FR-CA - FR-FR - IT-IT - JA-JP - KO-KR - NL-NL - PL-PL - PT-BR - SV-SE

August 2023



Introduction

The Geotab Data Connector (GDC) is a tool designed for fleet managers to import curated data from numerous Geotab data sources, sourced from their own fleet, into their preferred BI/visualization tool. The tool allows fleet managers to access aggregated data in their preferred BI tool without having to manually leverage MyGeotab reports. In addition, unlike the MyGeotab SDK, this tool allows fleet managers to customize their reports without the need for coding.

This document provides instructions on how to initially download and access the templates, along with an overview of the information available within them.

Template Library

Before getting started, please make sure Geotab Data Connector is enabled in your database as mentioned in the Requirements section of the User Guide.

Below is a complete list of currently available Geotab Data Connector templates - to download simply click the respective link you are interested in. New additions will be announced first via the Geotab Data Connector Community page so make sure to check in there regularly.

Getting Started

Power BI template

1. Open the Power BI template directly.

✱ NOTE: When opening the template for the first time, all sheets will be blank with some connection errors as the user credentials have not yet been entered.

2. Click Refresh.



3. Enter login credentials (if you have accessed GDC through Power BI previously, your credentials may already be saved locally):
4. 1. User name: <MyGeotab Database Name>/<MyGeotab Username>
   2. 1. Example: Database123/johnsmith@geotab.com
   2. Password: <My Geotab Password>
   3. Level to apply settings: https://data-connector.geotab.com/

   

4. Click Connect.



5. If you need to edit the credentials at a later time, you can do so by clicking File > Options and then Settings > Data source settings to edit the permissions for each table.

Tableau template

1. Open the Tableau template directly.

✱ NOTE: When opening the template for the first time, all sheets will be blank with some connection errors as the user credentials have not yet been entered.

2. Find your base access URL from MyGeotab > Administration > Geotab Data Connector. For more info about access URL, refer to the Geotab Data Connector User Guide.

If your base access URL is https://odata-connector-1.geotab.com/odata/v4/svc/, do the following:

3. 1. Click Refresh All Extracts.

   

3. Click Refresh in the window that appears.



4. Tableau will prompt you to enter credentials for all four source tables. Enter your MyGeotab credentials as the windows pop up:
5. 1. Username: <MyGeotab Database Name>/<MyGeotab Username>
   2. 1. Database123/johnsmith@geotab.com
   2. Password: <MyGeotab Password>

   

5. After credentials are entered for each source table, your data will be loaded into the template.

If you have a different base access URL, do the following:

6. 1. Under Data, navigate to the first data source and select Edit Data Source:

   

   2. Update the Server to match your Access URL, making sure to keep the table identifier at the end of the URL. Enter your MyGeotab credentials and click Sign In:

   

   3. After the connection summary loads, navigate to any of the dashboard tabs at the bottom of the screen.

   

   4. Repeat steps 1-3 for the four remaining data sources.
   5. Click Refresh All Extracts.

   

6. Click Refresh in the window that appears:

Excel template

1. Open the Excel template directly.

✱ NOTE: When opening the template for the first time, all sheets will be blank with some connection errors as the user credentials have not yet been entered.

2. Under the Data menu, click Refresh All.



3. Enter your credentials (if you have accessed GDC through Power BI previously, your credentials may already be saved locally):
4. 1. User name: <MyGeotab Database Name>/<MyGeotab Username>
   2. 1. Database123/johnsmith@geotab.com
   2. Password: <My Geotab Password>
   3. Level to apply settings: https://data-connector.geotab.com/

   

4. Click Connect.



5. If you need to edit the credentials at a later time, you can do so by clicking Data > Get Data > Data source settings to edit the permissions for each table.

Vehicle KPI Template Overview

Templates were designed to provide an out of the box insight ready solution demonstrating how the Data Connector can be used to answer key questions about your fleet. Keep in mind that once the templates are downloaded, you are free to customize your dashboard however you see fit to best suit your insight needs.

✱ NOTE: Templates for insight-ready data visualizations are currently available for Power BI and Tableau. The Excel template only provides sample data for design and testing purposes.

Distance & Time

Monthly aggregated metrics focusing on GPS distance and driving time for the last 6 completed months.

1. How has my fleet’s driving distance and driving time trended over the last 6 completed months?
2. What is my fleet’s average monthly driving distance?
3. What is my fleet’s average monthly driving time?
4. Which vehicle drove the longest distance or time last month?
5. Which vehicle drove the smallest distance or time last month?
6. How did last month’s driving time and distance compare to the previous month?



Metric	Calculation
Average Monthly GPS Distance (mi)	Sum of VehicleKpi_Monthly[GPS_Distance_km] multiplied by 0.621371 to convert km to mi, divided by total number of months
Total GPS Distance (mi)	Sum of VehicleKpi_Monthly[GPS_Distance_km] multiplied by 0.621371 to convert km to mi
GPS Distance Last Month (mi)	Sum of VehicleKpi_Monthly[GPS_Distance_km] multiplied by 0.621371 to convert km to mi and filtered to only include the last completed month
Average Monthly Time (hrs)	Sum of VehicleKpi_Monthly[DriveDuration_Seconds] divided by 3600 to convert seconds to hours, divided by total number of months
Total Time (hrs)	Sum of VehicleKpi_Monthly[DriveDuration_Seconds] divided by 3600 to convert seconds to hours
Time Last Month (hrs)	Sum of VehicleKpi_Monthly[DriveDuration_Seconds] divided by 3600 to convert seconds to hours and filtered to only include the last completed month

Idling

Monthly aggregated metrics focusing on idling time and idling fuel usage for the last 6 completed months.

7. How has my fleet’s idling fuel usage and idling time trended over the last 6 completed months?
8. What is my fleet’s average monthly idling fuel usage?
9. What is my fleet’s average monthly idling time?
10. Which vehicle had the largest idling fuel usage or idling time last month?
11. Which vehicle had the smallest idling fuel usage or idling time last month?
12. How did last month’s idling fuel usage and idling time compare to the previous month?



Metric	Calculation
Average Monthly Idling Fuel (gal)	Sum of VehicleKpi_Monthly[IdleFuel_Litres] multiplied by 0.264172 to convert litres to gallons divided by total number of months
Total Idling Fuel (gal)	Sum of VehicleKpi_Monthly[IdleFuel_Litres] multiplied by 0.264172 to convert litres to gallons
Idling Fuel Last Month (gal)	Sum of VehicleKpi_Monthly[IdleFuel_Litres] multiplied by 0.264172 to convert litres to gallons and filtered to only include the last completed month
Average Monthly Idling Time (hrs)	Sum of VehicleKpi_Monthly[IdleDuration_Seconds] divided by 3600 to convert seconds to hours divided by total number of months
Total Idling Time (hrs)	Sum of VehicleKpi_Monthly[IdleDuration_Seconds] divided by 3600 to convert seconds to hours
Idling Time Last Month (hrs)	Sum of VehicleKpi_Monthly[IdleDuration_Seconds] divided by 3600 to convert seconds to hours and filtered to only include the last completed month

Fuel Economy

Monthly aggregated metrics focusing on fuel economy for the last 6 completed months. Note the fuel distance may be different from GPS distance because fuel distance is only recorded when the device has also recorded fuel consumption. This ensures that fuel economy calculations are accurate in the case where fuel usage is not reported for certain vehicles or trip segments, and is reasonable to assume is representative of total performance in the vast majority of cases.

13. How has my fleet’s fuel economy trended over the last 6 completed months?
14. How has my fleet’s fuel usage and fuel distance trended over the last 6 completed months?
15. What is my fleet’s average fuel economy?
16. What is my fleet’s average fuel economy by fuel type?
17. How did last month’s fuel economy compare to the previous month?
18. How did last month’s fuel usage compare to the previous month?



Metric	Calculation
Total Fuel Distance (mi)	Sum of VehicleKpi_Monthly[FuelEconomy_Distance_Km] multiplied by 0.621371 to convert km to mi
Total Fuel (gal)	Sum of VehicleKpi_Monthly[TotalFuel_Litres] multiplied by 0.264172 to convert litres to gallons
Total Fuel Economy (mpg)	Sum of VehicleKpi_Monthly[FuelEconomy_Distance_Km] divided by Sum of VehicleKpi_monthly[TotalFuel_Litres] multiplied by 2.35215 to convert km/L to mpg
Fuel Economy Last Month (mpg)	Sum of VehicleKpi_Monthly[FuelEconomy_Distance_Km] divided by Sum of VehicleKpi_monthly[TotalFuel_Litres] multiplied by 2.35215 to convert km/L to mpg and filtered to only include the last completed month
Fuel Usage Last Month (gal)	Sum of VehicleKpi_Monthly[TotalFuel_Litres] multiplied by 0.264172 to convert litres to gallons and filtered to only include the last completed month

Utilization

Monthly aggregated metrics focusing on utilization for the last 6 completed months. Two methods of calculating utilization are demonstrated on this tab.

19. How has my vehicle utilization trended over the last 6 completed months?
20. How has my fleet utilization trended over the last 6 completed months?
21. How did last month’s vehicle utilization compare to the previous month?
22. How did last month’s fleet utilization compare to the previous month?

Daily Analysis

Daily aggregated metrics displaying the main metrics from the first four monthly aggregated tabs as a daily trend for the last 30 days. Includes a map visual plotting each vehicle’s last known location coordinates for each day.

1. How have my fleet’s key metrics trended by day over the last 30 days?
2. Where is the last recorded daily location of each of my vehicles?



Metric	Calculation
Driving Time (hrs)	Sum of VehicleKpi_Daily[DriveDuration_Seconds] divided by 3600 to convert seconds to hours
Driving Distance (mi)	Sum of VehicleKpi_Daily[GPS_Distance_Km] multiplied by 0.621371 to convert km to miles
Idling Time (hrs)	Sum of VehicleKpi_Daily[IdleDuration_Seconds] divided by 3600 to convert seconds to hours
Idling Fuel (gal)	Sum of VehicleKpi_Daily[IdleFuel_Litres] multiplied by 0.264172 to convert litres to gallons
Fuel Economy (mpg)	Sum of VehicleKpi_Daily[FuelEconomy_Distance_Km] divided by Sum of VehicleKpi_Daily[TotalFuel_Litres] multiplied by 2.35215 to convert km/L to mpg
Fuel Usage (gal)	Sum of VehicleKpi_Daily[TotalFuel_Litres] multiplied by 0.264172 to convert litres to gallons
Vehicle Utilization	Sum of VehicleKpi_Daily[TotalEngine_Hours] divided by distinct count of vehicles in fleet multiplied by total time period in hours
Fleet Utilization	Distinct count of vehicles in fleet with VehicleKpi_Daily[GPS_Distance_Km] > 0 divided by distinct count of vehicles in fleet

Hourly Analysis

Hourly aggregated metrics displaying the main metrics from the first four monthly aggregated tabs as a daily trend for the last 14 days. Includes a map visual plotting each vehicle’s last known location coordinates for each day.

3. How have my fleet’s key metrics trended by hour over the last 14 days?
4. Where is the last recorded hourly location of each of my vehicles?

Safety Benchmark Template Overview

The template is designed to provide a high-level overview of the fleet’s overall safety performance and comparison to benchmark and peer group leader. The template is followed by some interactive visuals to show the areas where vehicles are performing better or worse than the benchmarks.

The quadrant chat is designed to help you identify the individuals that need the most attention. For example, the top-right quadrant includes the cases that have a higher collision rate than the predicted benchmark. The top-left quadrant includes cases that have a lower collision rate than the predicted benchmark.

The template also provides a relative comparison of the different groups based on their average predicted collision rate, as well as a detailed overview of the performance at the vehicle level.

Maintenance Template Overview