I recently led a webinar about Vigillo’s new powerful export tools (recording here). There was a LOT of enthusiasm about taking these robust new export tools and using the power of Microsoft Excel to create new interactive data tools. I want to walk you through the first step of this process in this post: How to get the driver-related data you want exported into Excel. In later posts, I will show you different ways to use the power of Excel to see this data in new and important ways.
Here’s how to get the Vigillo driver-related data you want and get it into Excel:
Before You Start:
- You need to be a current Vigillo CSA scorecard customer
- You need a spreadsheet program such as Micrsoft Excel.
- To include non-FMCSA data, such as driver number, active/inactive status, terminal or manager kinds of information, you need to activate Vigillo Custom Groups. Custom Groups allow you to view groups of drivers the way they are organized in your company: by manager, by terminal, by region, by driver type. Custom groups means customized to your organization. Very useful! Learn more about Vigillo Custom Groups here.
Step 1 – View the base data you want in Vigillo
- Log into Vigillo. Go to My Reports. Click the “Driver Scorecards” icon – soon to be renamed “Driver Essentials”
- Choose options for the data you want to see:
– DSMS or CSMS: Choose the 2-year Carrier View (CSMS) or the 3-year Driver View (DSMS). FAQ here.
– Snapshot Date: Choose the data as of the most recent FMCSA update or earlier historical versions for data from the past. FAQ here.
– Active/Inactive Drivers: In the Filter Data section, choose all drivers or inactive drivers. FAQ here.
– Custom Group: In the Filter Data section, click “By Custom Group.” Click on the Custom Group you want to see – For example: By Manager or By Terminals. This may a be different label depending upon how your Custom Groups were setup. FAQ here.
Step 2: Choose your Export Type
- In the Export Data section, click one of the three choices that you want:
1) Driver Summaries: Each driver’s data is summarized in one row across in up to 75 columns of data. Great for comparing summaries of drivers or grouping into Custom Group related visualizations. FAQ here.
2) Inspections & Violations: Each row represents an inspection and a specific violation from that inspection. In other words – a clean inspection is one row, an inspection with three violations would take up three rows. Very helpful when you want see inspection and violation related information – including by BASIC. FAQ here. Two special notes about inspections and violations:
– Time Frame: Rather than exporting the full 2 years (CSMS) or full 3 years (DSMS), you can choose to narrow the time frame of the inspections and violations you export. For example – export just the last week or the last 6 months of inspections and violations.
– See What Falls Off: You can set the time-weight date for the export. This allows you to see which violations will “fall off” or no longer be counted as of next month or at the end of the calendar quarter for example. You will also see when the time-weight of violation are reduced from triple to double or from double to single.
3) Crashes: A straightforward export of crash data – one row per crash, including information such as date, injuries/fatalities, HazMat release, and other key crash information. FAQ here.
Step 3: Export the Data Into Excel
- Once you’ve made your choice from the previous step, a new screen will load. Click the blue export icon and choose “Excel.”
The data will now open into Excel. That’s it!
Give it a try! You may find, like me , that once you have the specific data you want in a very friendly real-time interactive environment for analyzing data and pursuing what-if scenarios, you may see more than you expected.
In future posts I will provide examples of what you can do with this data in Excel. Readers – let me know what specific things you’d like to see: top violations by terminals, charts with points by driver by manager? Comment below…