Building a CSV File for Bulk Recognition Follow
This article is intended to walk system administrators through the process of building a bulk recognition file for the purpose of uploading recognitions to their platform en masse for scheduling recognitions in the future. It is exhaustive and thorough. The columns contained in the file template found in this article will be discussed individually with examples provided throughout.
As discussed in the help article on support.awardco.com, navigate to the queues tab in the admin section of your account. After verifying you are on the bulk recognition page, click the Upload CSV button in the top right corner. You will be taken to the bulk recognize upload page. On this page, click the “Download Template” hyperlink to export a template from the platform. This will generate a .CSV document with several column headers prefilled. Open the template file.
Now that you have the template open on your computer, you will begin migrating the recognition details needed into the file. Make sure you are able to access your recognition platform during this process in case you need to access information stored in the platform. Remember, the file you are building will be loaded into the recognition platform and will be referencing the data contained therein. Any discrepancies between the file and platform data will generate errors.
For example, if you are awarding Alexis Henderson and her employee email in your HRIS is ‘email@example.com’ and she has changed her email in the Awardco platform to ‘firstname.lastname@example.org,’ the system will not be able to associate ‘email@example.com’ with her account.
Not only does the user data entered into the bulk recognition file need to match user account information in the platform, the budgets and email templates referenced in the file must also match the information contained in the platform.
For example, if a budget is named “Service Awards Budget” and you enter “Service Award” in the budget column in the file, the system will not recognize the budget name and an error will generate. It’s important to be precise when building this file and to make sure you have access to the information you’re migrating to the file.
Be aware, the file template you download is in a .CSV format and will need to be uploaded in the same .CSV format. This means any formatting done or formulas created in the file (e.g. cell fill color, font color, formulas, etc.) will not be preserved when you close the file, even if the file is saved. .CSV files only preserve the basic data contained in a cell when the file is closed.
Template Column Walkthrough
Column A - Recognition Date (ISO 8601) – Required Field
Recognition date is the most important section of the template. As soon as you master this column, you’ve mastered the bulk recognition file upload.
The time format in Column A is formatted as date, time, and time zone offset. This means the date will come first, YYYY-MM-DD. Next is the time of day using hours, minutes, and seconds, 00:00:00. Even though minutes and seconds are displayed in this format, these recognitions will only be sent on the hour. Finally, you will enter four numbers, +/-00:00, that describe how many time zones are between you and Coordinated Universal Time or UTC.
UTC is the time standard by which the world regulates its clocks and time. Some time zones are ahead of UTC and so use a ‘+’ sign before the four numbers. Some are behind UTC and so use ‘-‘ to indicate they are behind. Here is a quick list of which time zone offset you will use in the US and Canada. You can also visit the following article which lists every UTC time offset used around the world: https://en.wikipedia.org/wiki/List_of_UTC_time_offsets
US Time Zones:
-05:00 CDT, EST*
-06:00 MDT, CST*
-07:00 PDT, MST, Arizona* -08:00 PST*
*Daylight Savings Time in the US is typically March-November
Looking at the example below you’ll notice a ‘T’ in between the date and the time sections. This tells the system reading the file that you’re no longer typing a date and are now typing a time. Always remember to include the ‘T’ here. The complete recognition date format is shown below:
Date, T, time, +/- time zone offset from UTC YYYY-MM-DDTXX:XX:XX +/-XX:00
Let’s break this example down. 2019-07-04T09:00:00-04:00 means the recognition is scheduled to go out on July 4, 2019 at 9am UTC-04:00, which is Eastern Daylight Time.
The last thing we’ll say about dates is that all recognitions loaded with a recognition date that has passed will be sent out at the top of the hour following the upload. For example, if 2018- 01-01T09:00:00-05:00 is entered, the recognition will send out within an hour, as this date and time has already passed.
Column B - From User (Employee Id/Email) – Required Field
The ‘From User’ column designates who the award is coming from. This is the name that will be attached to recognition on the email sent to the recipient and on the recognition in your platform’s recognition feed. Typically, service awards and birthdays are sent from the company account. This account is often called ‘Company Recognition.’ For safety, performance, or other incentive awards, it is common for the employee’s direct manager to be the user referenced in this column. Who you enter here is up to you!
Enter the employee’s employee ID or email to designate in the file who will be identified as the sender in communications to the employee. We recommend using the employee ID.
Column C - To User (Employee Id/Email) – Required Field
The ‘To User’ is the recipient of the recognition. While it is possible to use the employee’s email address as the identifier here, we recommend using the employee ID.
Column D - Note – Required Field
The ‘Note’ is the message that will be displayed with the recognition in the recognition feed and may be sent in an email to the employee depending on your email template configuration. These notes are typically kept short but it is possible to include a lengthy message in this cell.
If you would like to type a longer message to the employee, we recommend discussing this with your Client Success Manager. If you are typing a long message to a single employee, it may be better to send the recognition through recognition programs instead of through the file upload. If you are using the same long message for many employees, it may be better to create a new email template that is built for this particular message. This will ensure the email template formatting is matched correctly with the length of the message.
Column E - Tags - Optional Field
The "Tags" column is for the use of tagging your recognitions. These tags will appear as hashtags when uploaded in the platform. Usually these tags are used to reference company values that can help drive specific behaviors.
Column F - Year – Optional Field
The ‘Year’ column is strictly for service/tenure awards when the email template being sent requires a year value for the body of the email. There is a variable field in certain email templates that require this value. Speak with your Client Success Manager to see if your service award email templates require this field.
Column G – Amount (USD/CAD/GBP/EUR, etc.) – Required Field
The ‘Amount’ column contains the value of the award you would like sent to the employee. The value entered is not the point value but is the cash value of the award. If your platform uses USD as the base currency, you will enter how many US dollars you would like to send the employee. To make sure you understand the value of the award, the base currency of your platform will be displayed next to Amount in the column header. E.g. Amount (USD) or Amount (CAD). If you are sending a non-monetary recognition, enter 0. Negative amounts may not be entered.
Column H- Budget Name – Required Field (When Amount is greater than 0)
The ‘Budget Name’ column is where you will enter which budget you would like to pull the value of the award from. If the recognition is non-monetary, you may leave this cell blank. The budget entered in this cell must match the budget name in the platform exactly. “Service Award” and “Service Awards” will be recognized as different by the platform.
Column I - Public/Private – Required Field
By writing either “Public” or “Private” in this column, you designate whether or not the recognition should be shared publicly in your company’s recognition feed. If the recognition is marked “Private,” the recognition will still be visible in system reporting.
Column J - Email Template – Optional Field
The email template column is where you designate which email template will be used to notify the recipient of the recognition. While this is not a required field, we highly recommend making use of this feature to ensure your employee is immediately made aware of their new recognition.
The email will be sent out at the time the recognition is processed, as designated by the date and time specified in Column A.
Be exact when matching the email template entered here with the name of the email template in the system. For example, if you enter “Service Awards” and the email template you’re trying to use is named “Employee Service Awards” in the platform, you will receive an error when trying to load the file. Any variation will create an error, so be precise.
Note: If information is entered in one of columns J, K, or L, information must be entered into all of them. The system cannot accept information in only one or two of these columns. Data must be entered into all three if any is entered.
Column K– Program Name – Optional Field
The Program Name column is where you will designate the program that the award is being submitted under. This will allow you to stay consistent in your reporting and restrict the award to certain award networks. For example, if your Safety Training Manager submits the Q1 safety awards but leaves a recipient off their list, you have the ability to award a safety award tied to your safety program through this feature.
To utilize this column, you will first need to make sure programs have been created in your Recognition Programs page in Admin Settings. The value entered in this cell must perfectly match the name of the program you are tying the recognition to. If the program you are tying the recognition to has an award network other than standard, the points loaded for this recognition will be restricted to the award network specified in that program’s settings. For more information on restricted award networks, search for our Smart Awards Network article or speak to your Client Success Manager.
Column L - Notify Manager (Employee Id/Email) – Optional Field
In this column, you will choose an individual (typically a supervisor or manager) who will be notified of this recognition via email. To designate this individual, you must enter their email address or employee ID and not their name. As we briefly discussed above, entering their employee ID in this field is the best practice. Remember, this user must exist in the Awardco system for the system to be able to process a notification email.
Column M - Notify Date (ISO 8601) – Optional Field
Here you will specify the date and time at which the manager notification will be sent. See "Column A - Recognition Date (ISO 8601)" above for details on date format.
This notification email can be sent independently from the recognition. As it is a separate email with a separate date and time column, you can send the email for this notification whenever you want. Just remember, the same rules that apply to column A apply here. If you enter a date or time that has already passed, the email will be sent at the top of the hour that it is entered into the system.
Column N - Notify Email Template – Optional Field
Finally, in column L you will choose the notify template that will be sent to the user listed in column J. Choose a template to be used to notify the manager. Remember, the text entered in this field must match the name of the email template in the platform exactly. Any variation will cause an error to be generated.
Saving and Uploading the File
Once your file has been created, be sure to save as. Doing so will give you full control over where the file is saved and the format the file is saved in. Because the file was downloaded in a .CSV format, you shouldn’t need to change the format when you save the file. Remember, the system will only allow a .CSV when it’s uploaded.
Save the file in an area that will make it easy to find. Once you’re ready, return to the platform and on the bulk recognize upload page, click ‘Choose File.’ Select the CSV file and click open. Once the file is attached to the choose file field, click ‘Save.’
If your file is loaded successfully, you will receive a dialogue window that says, "The import completed successfully." If there are any errors in the file, the file will not load until those errors are addressed. If your file has errors, they will be individually listed below the save button.
Errors can look intimidating but are usually pretty easy to work through. You have all of the data you need right in front of you in the platform. When troubleshooting errors, we recommend opening a new tab in your platform. An easy way to do this is to right-click on the users tab and click, ‘Open in New Tab.’ This allows you to look up information you need in the platform while still being able to reference the error log generated on the bulk recognition upload screen. If you require any assistance while troubleshooting these errors, please contact your Awardco Client Success Manager.
NOTE: If you are uncomfortable with building this document without hands-on direction, please schedule a time to discuss the creation and upload process with your assigned Client Success Manager.