Up until version 8.1, it was not possible for users to import cards and initiatives with historical metrics data and therefore replicate their previous work items' data when migrating from another tool.
The built-in import tool now allows for this scenario by enabling users to use below card properties as designated columns in their excel file in order to specify the exact dates on which a card was created, moved along the workflow according to the process and finally archived.
- Created at - date on which the card was created
- Start date - date on which the card was moved to the 'In Progress' area
- End date - date on which the card was moved to the 'Done' area
- Archived at - date on which the card has been archived
Note: "Creation date" and "Archivation date" are also accepted column headers for the "Created at" / "Archived at" properties.
Important: this functionality is only possible whenever new work items are generated via import. Already existing work items' metrics data cannot be altered.
2. Importing cards and initiatives with historical metrics data
When importing cards and initiatives, you would need to include the above-mentioned date properties and the time spent in each of the respective sections of the imported cards/initiatives will be calculated as follows:
Start date - Created at = Time spent in the 'Requested' area
End date - Start date = Time spent in the 'In Progress' area
Archived at - End date = Time spent in the 'Done' area
Disclaimer: If you would like to recreate the history for a card prior to importing it, your spreadsheet file must be formatted in a way so all dates are in the past and all of the above date fields follow the below sequence in chronological order:
Created at-> Start date-> End date and Archived at (if applicable).
Please note that the above properties are of type 'datetime' and can be used with the following format YYYY-MM-DD HH:MM:SS where the Time value is optional - if no such value is explicitly stated, cards will be imported with a value of 00:00:00 for the according dates.
For more precise metrics, users can set an exact date/time value e.g. Created At = 2021-01-01 09:00:00 and Start date 2021-01-01 10:00:00 which would create a card that has spent 1h in the Requested area.
If there is a mismatch in the dates e.g. Start Date is before "Created at", a detailed error message will be displayed inside the import window, prior to performing the import action.
For more info on all system problems, warnings and error messages that will be returned by the import validator, please refer to this article.
Based on what date values are set for the above fields, the import tool can determine in which column to position the card, even if it has not been explicitly specified.
For example, if the file contains values for "Created at", "Start date" and "End date", but there is no 'column' value for that row, the card will be created in the first column of the "Done" section (as it has a value for the "End date" column).
Kindly refer to below sample screenshot showing how a file should be formatted properly to cover the scenarios described in the cards' titles:
|Test card in Requested
|Test card In Progress
|Test card in Done (processed)
|Test card that has been processed and archived from the Done section
|Test card in Ready to Archive which is yet to be archived
|Ready to Archive
|Test card that has been processed and archived
|Ready to Archive
When loaded into the import tool, the file would look like this:
Open the image in a new tab for better visibility.
After the import has been performed, the last card from the file (row 6) would be imported with the following metrics data (calculated according to the above-mentioned formulas):
3. Importing Timeline initiatives with historical metrics data
In addition to the previously specified time/date-related properties, Timeline initiatives have 2 additional properties that can be specified when importing initiatives with historical metrics data - the "Planned Start" and "Planned End" fields.
These fields are of type 'date' and accept the previously mentioned formats:
MM/DD/YYYY, DD-MM-YYYY, YYYY-MM-DD.
Planned Start would be required if the initiative has not been started i.e. there has not been any Start date value explicitly specified.
Planned End would be required if the initiative has not been finished i.e. there has not been any End date value explicitly specified.
If the Start / End dates have been specified, the import logic is as follows:
If the initiative has been imported with a "Start date" but no "Planned Start" date was specified, the "Planned Start" value will be taken from the "Start date" field.
If the initiative has been imported with an "End date" but has no "Planned End" date explicitly specified, the "Planned End" value will be taken from the "End date" field.