Staffing Upload

This page details the CSV file format used for uploading Staff Hour data in order to calculate Shopper to Labor Hour ratios and other staffing related metrics.

Below is a list of general topics and Admin Settings that relate to file uploads.

  • File Upload Configuration to configure what the system should expect from file uploads, such as the timestamp format and column order

  • File Upload Access provides the details to use when uploading files over HTTP or SFTP from an automated system

  • File Upload Status for viewing previously uploaded files, their upload status', or to manually upload a file

  • Using CSV Files to learn more about generating and uploading CSV files to RetailNext

File Format

Filename: Begins with “staff_ “ and ends with “.csv”. Files are often named for the time period they cover, eg: “staff_YYYYMMDDHHMMSS.csv”

Column

Value

Column

Value

Store ID*

Store identifier that matches the configured Store ID in Admin Settings > Locations. If POS Store ID is enabled, you must use that value.

Employee ID*

Unique identifier of the employee, which must match with the Cashier ID in the POS Transaction Info file

Employee Name*

Name of the employee

Clock‐in Time*

Date and time the employee started a work period

Clock‐out Time

Date and time the employee completed a work period

Division ID

Additional store identifier that matches the store’s configured Division ID in Admin Settings > Locations. Only required when Division ID is enabled to allow for duplicate Store IDs.

* required column

Staffing File Format Notes

  • Provide one file per time period that contains clock-in and clock-out times for employees at each store

  • The staffing file must contain one line for each time range an employee worked during the export period

  • The store’s timezone will be used for clock-in and clock-out times

Example

In this example; we’re uploading staffing data for Peter, Neil, and Roberto, who are employees at Store ID: SAN454 that worked on October 9, 2020.

  • Peter worked from 10:44 AM to 3:06 PM, with a break from 12 PM to 1 PM; his Employee ID is 300

  • Neil worked from 10:59 AM to 5:50 PM; his Employee ID is 301

  • Roberto worked from 3:42 PM to 7:19 PM; his Employee ID is 302

SAN454,300,Peter Smith,2020-10-09 10:44:00,2020-10-09 12:00:00 SAN454,301,Neil Roberts,2020-10-09 10:59:00,2020-10-09 17:50:00 SAN454,300,Peter Smith,2020-10-09 13:00:00,2020-10-09 15:06:00 SAN454,302,Roberto Hernandez,2020-10-09 15:42:00,2020-10-09 19:19:00

Download the example CSV file

Validation

When uploading staffing files, you will receive feedback about any errors that were found during validation. If a file fails validation, it will not be processed. You can review previously uploaded files and any related errors by navigating to the Staffing tab at Admin Settings > File Upload Status.

The possible errors are listed below that will result in failed validation. 

Error Message

Meaning

Error Message

Meaning

Invalid time

Invalid characters, like symbols and letters within a timestamp; hours greater than 24; minutes greater than 60, etc

Start after end

Clock-in time listed on a given entry is after the Clock-out time

Empty column

There is no entry for a required field

Hours overlaps previous shift

The start timestamp for a shift for employee XXX is before the end timestamp of another shift for employee XXX

Employee name inconsistent

For the same Employee ID, you have two different staff names

Wrong number of columns

The number of columns in the uploaded file do not match the configuration specified in Admin > File Upload Configuration

 

Other conditions are listed below for which the system will pass through validation, sometimes by making an assumption.

Condition

System Handling

Missing clock-out time

If a clock‐out time is not provided (eg. if an employee forgot to clock out), the system assumes an employee worked until the end of that day’s configured store hours.

Hours over day boundaries

Hours spanning day boundaries are supported, for example: SAN454,300,Peter Smith,2020-09-21 18:00:00,2020-09-22 04:30:00

Zero clock-in

If the clock-in time is 00:00:00, the labor hours will start from midnight when viewing 24 hours period and from store opening time when store hours period is selected.

Zero clock-in and clock-out times

If the clock-in and clock-out times are both 00:00:00 for a line in the staffing file, the system processes the file and that employee will not have any labor hours for that day.

Same clock-in and clock-out times

If the clock-in and clock-out times are the same for an entry, that entry will be skipped during processing and the rest of the file will process as usual.

Employees with same name, but different IDs

Two separate entries will be created.

Store not found

Warning will be shown

Additional Details

Overlapping Entries in a File

The Combine overlapping shifts checkbox on the File Upload Configuration page controls what happens if there are overlapping entries for an employee in a single file.

The option is checked by default so when this case occurs, the file will pass validation and any 2 or more shifts in the file that overlap for an employee/store will be combined into a single, larger shift.

For example, say a file contains the following shifts for an employee for one day:

  • 1-3 PM

  • 5-8 PM

  • 2-6 PM

This will result in one shift from 1-8 PM.

The shift can then be corrected in a subsequent file if necessary (see File Re-Upload Handling below).

Alternatively, an admin may choose to uncheck the Combine overlapping shifts option, so that if there are overlapping entries for an employee in a single file, the file will fail validation and will not be processed (see validation error above). Overlapping shifts could be indicative of an issue, so this option is preferred when inaccurate data is a concern and shifts are not corrected in subsequent uploads. By causing the file to fail validation, the issue can be noticed and fixed before incorrect data makes it into the system.

File Re-Upload Handling

When uploading a file in which some or all entries are duplicated or overlap with entries from a previously uploaded file (whether the filenames are the same or not), the behavior is as follows:

  • Duplicate staff hour entries are ignored: If the clock-in and clock-out dates/times are exactly the same as an existing record for that employee, a new record will not be created.

  • Entries with hours that overlap existing records' hours will overwrite the existing records: If there is any overlap in the hours between an existing entry and an entry in the new file, a new record will be created and the existing one will be deleted. This is useful if, for example, you need to update an existing record with a new end time. Note that nothing will be kept from the old record in this case (for example, even if the new record is missing a clock-out time, the clock-out time from the old record will not be used).

  • Entries that do not overlap create new records: If there are entries in a new file that does not overlap with any existing records, then new records will be created.