The workflow export feature allows you to gain deeper insights into how workflows are being completed across your organization or group. By exporting and analyzing your data in tools like Excel, you can track key metrics such as completion rates, identify trends by date, person, or workflow type, and ensure compliance across operations. This guide will walk you through how to prepare your export data and use pivot tables to create meaningful reports that help drive decision-making.
You can find a dictionary of the extract fields here.
1. Prepare Your Data
Before creating your pivot tables, it's important to ensure your data is properly formatted for accurate analysis. Below are some key best practices to help you prepare your workflow export data:
Date Formatting
Dates in the extract are formatted in UTC (Coordinated Universal Time) and follow this structure:
2024-08-28T10:42:38-04:00
Where:
2024-08-28is the date (YYYY-MM-DD),T10:42:38is the time (HH:MM),-04:00indicates the time zone offset (in this case, 4 hours behind UTC).
Best Practice:
To convert the date/time into your local time zone, use Excel’s TEXT formula to reformat it or adjust the time zone with the following steps:
Convert Text to Date: If your dates are not recognized as a proper date format, use
=DATEVALUE(LEFT(W2,10)) + TIMEVALUE(MID(W2,12,8))to convert them.
ReplaceW2with the cell containing your date/time value as needed.Adjust Time Zone
Workflow Start Date/Time (Column W): To convert UTC to your local time zone, add or subtract the time difference using a simple formula:
For example, to adjust to a time zone that is 5 hours behind UTC (EST):
=$W2 + TIME(5, 0, 0)Replace
W2with your cell reference as needed.
Workflow Complete Date/Time (Column X): To convert UTC to your local time zone, add or subtract the time difference using a simple formula:
For example, to adjust to a time zone that is 5 hours behind UTC (EST):
=$X2 + TIME(5, 0, 0)Replace
W2with your cell reference as needed.
Note this conversion will not automatically adjust for daylight savings time.
You can find a list of all time zones and their UTC offset here: Time Zones
Example Time Zone Conversions
Abbreviation | Time Zone | Offset (Standard) | Offset (Daylight Savings) | Formula (Standard) |
EST/EDT | Eastern Time | -5 | -4 | =W2 + TIME(5, 0, 0) |
CST/CDT | Central Time | -6 | -5 | =W2 + TIME(6, 0, 0) |
MST/MDT | Mountain Time | -7 | -6 | =W2 + TIME(7, 0, 0) |
PST/PDT | Pacific Time | -8 | -7 | =W2 + TIME(8, 0, 0) |
Remove Duplicates
If you are combining multiple data sets or exports, it is usually worth removing any potential duplicates.
Use the Remove Duplicates feature in Excel:
Highlight your data.
Go to Data > Remove Duplicates.
Select the Workflow Link column (S) and press OK.
2. Insert a Pivot Table
Once your data is ready, you can create a pivot table to analyze it. Here’s how:
Select all the data you want to include in the pivot table.
Go to the Insert tab on the Excel ribbon.
Click on PivotTable.
Choose whether you want the pivot table in a new worksheet or the current one, and click OK.
3. Track Workflow Completion by Date
A useful way to analyze workflow usage is to track how many workflows are completed by date.
Rows: Drag the Workflow Complete Date/Time field into the "Rows" section. Excel will group the dates automatically (e.g., by day, month, or year).
Values: Drag the Workflow ID or Work Name into the "Values" section. Ensure it's set to Count.
This will show you the number of workflows completed on each date.
4. Track Workflow Completion by Person
If you want to analyze performance by worker:
Rows: Drag the Worker First Name (or Worker Username) into the "Rows" section.
Values: Drag the Workflow ID or Work Name into the "Values" section, and set it to Count.
Now you can see how many workflows each worker has completed.
5. Track Workflow Completion by Workflow Type
To analyze different types of workflows completed:
Rows: Drag the Workflow Template into the "Rows" section.
Values: Drag the Workflow ID or Work Name into the "Values" section, and set it to Count.
This will give you a breakdown of how many workflows of each type were completed.
6. Adding Filters to Your Pivot Table
For additional insights, you can filter your data by dragging fields like Region, Supervisor Name, or Workflow Status into the Filters area. This allows you to focus on specific subsets of your data, such as workflows in a particular region or workflows assigned to a specific supervisor.
7. Grouping Data by Week or Month
If you want to group workflow completion counts by week or month rather than by individual dates:
Right-click any date within the pivot table.
Select Group.
Choose to group by Months, Quarters, or Years.
This is useful for identifying trends over longer periods.
Best Practices for Using Pivot Tables:
Refresh the Data: If your workflow export is updated frequently, make sure to refresh your pivot table to include the latest data by right-clicking the table and selecting Refresh.
Use Slicers: Slicers provide an interactive way to filter your pivot tables. To add a slicer, select the pivot table, go to the Insert tab, and choose Slicer. You can filter data by dimensions such as region or supervisor with just a click.