Data Connector templates

Explore the dashboard templates that show your fleet data in Power BI and Tableau.

Note: The Geotab Data Connector is a paid app available via the Marketplace for an additional cost. See the Apps in Marketplace help article to learn how to manage paid apps.

Templates give you an out-of-the-box, insight-ready way to see how the Geotab Data Connector can answer key questions about your fleet. After you download a template, you can customize the dashboard to suit your needs. The topics in this article explain how to open each template and describe the information it contains.

Template gallery

You can find the templates on the Data Connector page. Navigate to Reports > Data Connector. Each template is available in both metric and imperial units.

Note:

Insight-ready data visualizations are currently available for Power BI and Tableau. The Excel template only provides sample data for design and testing purposes.


Data Connector page in MyGeotab showing the Power BI template gallery.

For definitions of the columns the templates use, see Data Connector data dictionary.

Opening the Power BI template

Open the Power BI template and connect it to your fleet data.

Follow these steps, or watch the Power BI template setup video.

  1. Open the Power BI template.
    Note:

    When you open the template for the first time, all sheets are blank and show connection errors because you have not entered your credentials yet.

  2. Click Refresh.

    Power BI Home ribbon with the Refresh button.
  3. Enter your login credentials. If you previously accessed the Geotab Data Connector through Power BI, your credentials may already be saved locally.

    • User name: <MyGeotab Database Name>/<MyGeotab Username>

      • Example: Database123/johnsmith@geotab.com

    • Password: your MyGeotab password

    • Level to apply settings: https://data-connector.geotab.com/


    OData feed window with empty user name and password fields.
  4. Click Connect.

    OData feed window with credentials entered and the Connect button.
  5. To edit your credentials later, click File > Options and settings > Data source settings to edit the permissions for each table.

Opening the Tableau template

Open the Tableau template and connect it to your fleet data.

Your base access URL determines how you connect the template to your data.

  1. Open the Tableau template.
    Note:

    When you open the template for the first time, all sheets are blank and show connection errors because you have not entered your credentials yet.

  2. Open the Data Connector page:
    1. In Classic MyGeotab, navigate to Reports > Data Connector.
  3. Find your base access URL on the page. For more information, see Finding your Data Connector access URL.
  4. If your base access URL is https://odata-connector-1.geotab.com/odata/v4/svc/, do the following:
    1. Click Refresh All Extracts.

      Tableau Data menu with the Refresh All Extracts option selected.
    2. In the window that appears, click Refresh.

      Refresh All Extracts window listing the data sources to refresh.
    3. When the sign-in windows appear, enter your MyGeotab credentials for each source table:

      • User name: <MyGeotab Database Name>/<MyGeotab Username>

      • Password: your MyGeotab password


      Tableau Sign In window asking for a user name and password.
    4. After you enter credentials for each source table, your data loads into the template.
  5. If you have a different base access URL, do the following:
    1. Under Data, go to the first data source and select Edit Data Source.

      Tableau Data menu with the Edit Data Source option for the first data source.
    2. Update the Server to match your access URL, and keep the table identifier at the end of the URL. Enter your MyGeotab credentials and click Sign In.

      Tableau OData window showing the server URL and sign-in fields.
    3. After the connection summary loads, go to any of the dashboard tabs at the bottom of the screen.

      Tableau worksheet that opens after the connection summary loads.
    4. Repeat the previous three substeps for each remaining data source.
    5. Click Refresh All Extracts.

      Tableau Data menu with the Refresh All Extracts option selected.
    6. In the window that appears, click Refresh.

      Refresh All Extracts window listing the data sources to refresh.

Opening the Excel template

Open the Excel template and connect it to your fleet data.

The Excel template provides sample data for design and testing purposes.

  1. Open the Excel template.
    Note:

    When you open the template for the first time, all sheets are blank and show connection errors because you have not entered your credentials yet.

  2. Under the Data menu, click Refresh All.

    Excel Data tab with the Refresh All button.
  3. Enter your credentials. If you previously accessed the Geotab Data Connector through Power BI, your credentials may already be saved locally.

    • User name: <MyGeotab Database Name>/<MyGeotab Username>

      • Example: Database123/johnsmith@geotab.com

    • Password: your MyGeotab password

    • Level to apply settings: https://data-connector.geotab.com/


    Excel OData feed window with empty user name and password fields.
  4. Click Connect.

    Excel OData feed window with credentials entered and the Connect button.
  5. To edit your credentials later, click Data > Get Data > Data source settings to edit the permissions for each table.

Vehicle KPI template

Review the metrics in the Vehicle KPI template and how they are worked out.

The Vehicle KPI template is designed to answer key questions about your fleet. After you download the template, you can customize your dashboard to suit your needs.

Note:

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

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

  • How has my fleet's driving distance and driving time trended over the last 6 completed months?

  • What is my fleet's average monthly driving distance?

  • What is my fleet's average monthly driving time?

  • Which vehicle drove the longest distance or time last month?

  • Which vehicle drove the shortest distance or time last month?

  • How did last month's driving time and distance compare to the previous month?


Distance and time dashboard showing driving distance and time trends for six months.

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, including:

  • How has my fleet's idling fuel usage and idling time trended over the last 6 completed months?

  • What is my fleet's average monthly idling fuel usage?

  • What is my fleet's average monthly idling time?

  • Which vehicle had the largest idling fuel usage or longest idling time last month?

  • Which vehicle had the smallest idling fuel usage or shortest idling time last month?

  • How did last month's idling fuel usage and idling time compare to the previous month?


Idling dashboard showing idling fuel and time trends for six months.

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. It is important to note that 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.

  • How has my fleet's fuel economy trended over the last 6 completed months?

  • How has my fleet's fuel usage and fuel distance trended over the last 6 completed months?

  • What is my fleet's average fuel economy?

  • What is my fleet's average fuel economy by fuel type?

  • How did last month's fuel economy compare to the previous month?

  • How did last month's fuel usage compare to the previous month?


Fuel economy dashboard showing fuel economy and fuel usage trends for six months.

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.

  • How has my vehicle utilization trended over the last 6 completed months?

  • How has my fleet utilization trended over the last 6 completed months?

  • How did last month's vehicle utilization compare to the previous month?

  • How did last month's fleet utilization compare to the previous month?


Utilization dashboard showing vehicle and fleet utilization trends for six months.

Metric

Calculation

Vehicle Utilization

Sum of VehicleKpi_Monthly[TotalEngine_Hours] divided by distinct count of vehicles in fleet multiplied by total time period in hours

Vehicle Utilization Last Month

Sum of VehicleKpi_Monthly[TotalEngine_Hours] divided by distinct count of vehicles in fleet multiplied by total time period in hours and filtered to only include the last completed month

Fleet Utilization

Distinct count of vehicles in fleet with VehicleKpi_Monthly[GPS_Distance_Km] > 0 divided by distinct count of vehicles in fleet

Fleet Utilization Last Month

Distinct count of vehicles in fleet with VehicleKpi_Monthly[GPS_Distance_Km] > 0 divided by distinct count of vehicles in fleet and filtered to only include the last completed 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. It includes a map visual plotting each vehicle's last known location coordinates for each day.

  • How have my fleet's key metrics trended by day over the last 30 days?

  • Where is the last recorded daily location of each of my vehicles?


Daily analysis dashboard showing 30-day trends and a map of last known vehicle locations.

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

Safety Benchmark template

Learn how the Safety Benchmark template compares your fleet with benchmarks.

The Safety Benchmark template provides a high-level overview of your fleet's overall safety performance and a comparison with benchmark and peer group leaders. Interactive visuals follow the overview and show where vehicles are performing better or worse than the benchmarks.

The quadrant chart helps you identify the individual drivers 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, and a detailed overview of performance at the vehicle level.

Vehicle Exception Events template

Review the metrics in the Vehicle Exception Events template and how they are worked out.

The Vehicle Exception Events template helps fleet managers track rule violations by asset. Use it to identify which rules are triggered most often, which vehicles are accumulating the most exceptions, and how exception activity trends day over day.

The template includes filters for Group, Asset, VIN, Rule, and Rule ID, and supports Daily, Weekly, and Monthly views. It can help you answer questions such as:

  • Which exception rules are triggered most often across the fleet?

  • Which vehicles have the highest exception event counts or longest durations?

  • How many unique rules and assets are generating exceptions?

  • Which exception category (Safety, Compliance, Productivity, etc.) drives the most activity?

  • How is exception activity trending over time relative to distance driven?


Vehicle exception events dashboard showing event counts by rule, group, and day.

Metric

Calculation

Exception Events

SUM(DeviceExceptionEvent_Daily[Event_Count])

Rules with Exceptions

DISTINCTCOUNT(DeviceExceptionEvent_Daily[RuleId])

Assets with Exceptions

DISTINCTCOUNT(DeviceExceptionEvent_Daily[DeviceId])

Avg Exception Duration (min)

DIVIDE(SUM(DeviceExceptionEvent_Daily[Duration_Seconds]), COUNTROWS(DeviceExceptionEvent_Daily)) / 60

Avg Exception Distance (km)

DIVIDE(SUM(DeviceExceptionEvent_Daily[Distance_Km]), COUNTROWS(DeviceExceptionEvent_Daily))

Driver Exception Events template

Review the metrics in the Driver Exception Events template and how they are worked out.

The Driver Exception Events template helps fleet managers track rule violations by driver. Use it to identify which drivers are generating the most exceptions, compare exception rates across your workforce, and monitor trends over time.

The template includes filters for Group, Driver, Rule, and Rule ID, and supports Daily, Weekly, and Monthly views. It can help you answer questions such as:

  • Which exception rules are triggered most often at the driver level?

  • Which drivers have the highest exception event counts or longest durations?

  • How many unique rules and drivers are generating exceptions?

  • Which exception category (Safety, Compliance, Productivity, etc.) drives the most activity?

  • How does exception activity compare across drivers within a group?

Metric

Calculation

Exception Events

SUM(DriverExceptionEvent_Daily[Event_Count])

Rules with Exceptions

DISTINCTCOUNT(DriverExceptionEvent_Daily[RuleId])

Drivers with Exceptions

DISTINCTCOUNT(DriverExceptionEvent_Daily[DriverId])

Avg Exception Duration (min)

DIVIDE(SUM(DriverExceptionEvent_Daily[Duration_Seconds]), COUNTROWS(DriverExceptionEvent_Daily)) / 60

Avg Exception Distance (km)

DIVIDE(SUM(DriverExceptionEvent_Daily[Distance_Km]), COUNTROWS(DriverExceptionEvent_Daily))

IFTA template

Review what the IFTA template shows to help you prepare your fuel tax filing.

Fleet managers use this report to prepare and validate their quarterly International Fuel Tax Agreement (IFTA) fuel tax filing without manually reconciling mileage and fuel receipts by hand. It rolls up jurisdiction-by-jurisdiction distance and fuel purchase data at the vehicle and fleet level, so a manager can catch data gaps or unusual variances before submitting the return to their base jurisdiction. It is best used each quarter as a pre-filing review, and as a reference when reconciling against a state or provincial audit.

The template can help you answer questions such as:

  • Which jurisdictions did my fleet drive the most taxable distance in this quarter?

  • How do fuel purchases compare to distance/fuel consumed per jurisdiction (tax surplus or shortfall)?

  • Which vehicles have missing or anomalous distance/fuel data that needs fixing before filing?


IFTA dashboard showing taxable distance totals and a distance by jurisdiction map.

Metric

Calculation

IFTA Distance

Sum of taxable jurisdiction distance from DeviceJurisdictionTraversalSummary

Total Distance

Sum of all recorded distance, taxable and non-taxable

Taxable Distance %

IFTA Distance ÷ Total Distance

Fuel Purchased

Sum of FuelTransaction volume by jurisdiction and quarter

Fuel Purchased vs. Distance

Fuel Purchased compared to IFTA Distance, flagging jurisdictions with a tax surplus or shortfall

Charge Sessions template

Review what the Charge Sessions template shows about EV charging.

The Charge Sessions template helps fleet managers track electric vehicle (EV) charging activity across their fleet. Use it to understand how vehicles are charging (AC versus DC), which vehicles consume the most energy, and whether charging behavior keeps batteries in a healthy state of charge (SOC) range.

The template includes filters for Period, Charge Type (AC/DC), and Vehicle, and supports Daily, Weekly, Monthly, and Quarterly trend views. It can help you answer questions such as:

  • How much charging energy did my fleet consume, and how does it compare to the previous period?

  • Which vehicles or vehicle models consume the most charging energy or charge most often?

  • How is my fleet's charging split between AC and DC charging, in both session count and time spent charging?

  • How long do charging sessions typically last, and what share are short top-ups versus long dwell sessions?

  • Are vehicles routinely starting charges at a low battery level, and are sessions ending with a healthy SOC gain?


Charge sessions dashboard showing energy use, AC and DC charging, and state of charge.

Metric

Calculation

Total Energy (kWh)

Sum of ChargeSession[EnergyConsumed_kWh]

Total Sessions

Count of charge session records (COUNTROWS of ChargeSession)

Avg Duration (min)

Sum of ChargeSession[ChargeDuration_Seconds] divided by 60 and by the number of sessions

Avg SOC Gain

Average across sessions of (EndStateOfCharge − StartStateOfCharge)

Avg Energy per Session (kWh)

Sum of ChargeSession[EnergyConsumed_kWh] divided by the number of sessions

Avg Peak Power (kW)

Average of ChargeSession[PeakPower_kW]

AC / DC Sessions

Count of sessions filtered to ChargeType = "AC" or "DC"

AC / DC Duration (min)

Sum of ChargeSession[ChargeDuration_Seconds] divided by 60, filtered to ChargeType = "AC" or "DC"