How to Create New Cards with Historical Metrics Data via Import?

In this article:

1. Importing Items with Historical Metrics Data
2. Importing Cards and Initiatives with Historical Metrics Data
    2.1. Formatting Guidelines
    2.2. Automatic Positioning
3. Importing Timeline Initiatives with Historical Metrics Data

The import tool in Businessmap allows you to migrate work item data, including historical metrics such as creation date, start date, and end date. Here is how to format your Excel file for a successful import of historical metrics data.

1. Importing Items with Historical Metrics Data

To import historical metrics data, ensure your Excel file includes the following card properties as designated columns. This will allow you to specify the exact dates when a work item was created, moved through the workflow, and archived:

• Created at: The date the card was created.
• Start date: The date the card was moved to the “In Progress” area.
• End date: The date the card was moved to the “Done” area.
• Archived at: The date the card was archived.

Note: The column headers "Creation date" and "Archivation date" are also accepted for the "Created at" and "Archived at" properties.

Important: This functionality is only available when new work items are generated via import. Metrics data for existing work items cannot be altered.

2. Importing Cards and Initiatives with Historical Metrics Data

When importing cards and initiatives, include the necessary date properties, which will calculate the time spent in each section of the workflow as follows:

• Time spent in the "Requested" area = Start date - Created at
• Time spent in the "In Progress" area = End date - Start date
• Time spent in the "Done" area = Archived at - End date

Important: To accurately recreate the history of a card, all dates must be in the past and follow this chronological sequence:

Created at → Start date → End date → Archived at (if applicable).

2.1. Formatting Guidelines

• Date format: All date properties are of type datetime and should follow the format YYYY-MM-DD HH:MM:SS. If the time is not provided, the system will assign a default value of 00:00:00 for the respective dates.
• Example: For precise tracking, use a specific datetime, such as 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 1 hour in the "Requested" area.

If there are discrepancies (e.g., Start Date before Created at), a detailed error message will appear before proceeding with the import. You can learn more about the types of import errors here.

2.2. Automatic Positioning

The import tool uses the provided date values to determine the card’s position in the workflow. For example, if "Created at," "Start date," and "End date" are provided without a column value, the card will be placed in the first column of the "Done" section (based on the "End date").

Check the example below to correctly format your import file:

When loaded into the import tool, the file would look like this:

After the import is completed, the last card from the file (row 4) will be imported with the following metrics data, calculated based on the formulas outline above:

3. Importing Timeline Initiatives with Historical Metrics Data

In addition to the time/date-related properties described above, there are two additional properties you can specify when importing timeline initiatives with historical metrics data: Planned Start and Planned End.

These fields are of type "date" and accept the formats: MM/DD/YYYY, DD-MM-YYYY, or YYYY-MM-DD.

• Planned Start is required if the initiative hasn't been started (i.e., no explicit Start Date is provided).
• Planned End is required if the initiative hasn't been finished (i.e., no explicit End Date is provided).

If Start or End Dates are provided, the following import logic applies:

• If only a Start Date is given and no Planned Start date is specified, the system will use the Start Date for the Planned Start field.
• If only an End Date is given and no Planned End date is specified, the system will use the End Date for the Planned End field.