Importing sales and staff data from a file
Importing data from a third party software which T.M.A.S. does not directly integrate with.
Table of Contents
Important - Prior to continuing.
Verify if you are currently using a supported POS Integration and if so, follow the appropriate article to setup. Otherwise continue to determine if your third-party application meets the following criteria in order to facilitate the process of importing a file into T.M.A.S.
|T.M.A.S. provides the ability to import sales and staff data files which have been exported from another third-party application. This feature is used best when there is a means to completely automate the process. When determining if this process can be automated, the other third-party application should meet the following criteria, as this could complicate as certain steps in the process requiring the importing user to perform tasks manually.
Third-Party application criteria
If you are uncertain whether you meet the above criteria, please contact firstname.lastname@example.org to discuss the possibilities.
|Example of flat file headers for Summary File Type
|Date & Time
|Staff Hours (optional)
|This location identifier needs to match the location ID in T.M.A.S. The RefID is usually a 1-4 digit store number. T.M.A.S. software can also apply a “mask” from the beginning of the RefID of the store, as long as it is uniform across all locations. For example, if the ID name is SB0007, this field could contain the complete ID name or simply the last 3 or 4 digits which is the store number (ex: 007 or 0007)
|Date and Time:
|Date for which the data is provided. Date must be in one of the following accepted formats. Verify the configuration wizard in the following steps for the accepted formats.
|The time (in hour) for which the sales information are provided. This needs to be 2 digits, military format. The number represents the start of the interval. So for sales for 13:00 to 14:00 the number would be 13.
This field needs to contain the value of the sale for the specified hour. No minimum of digit is required. The number must also contain cents and the cents needs to be delimited by a “.”, as this is a fixed decimal value. For example: 600.76. Do not use commas to represent values in thousands.
Number of invoices generated. This is an integer value. This field contains the number of transaction for the specified hour. No minimum of digits is required. Example, for 6 transactions, we would simply put “6” there. Also:
|This field contains the total number of items for all transactions for the hour. If there are no transactions for this hour indicate it as 0.
Number of employees on the sale floor for the specific hour. This can be a real value or integer. A decimal value would be generated when a staff is only present on the sales floor for a part of the hour. If there are 10 employees on the floor then we would put “10” as value for this field for the hour. 0 is accepted in this field if there really is not one on the floor for this hour.
How to setup file importation
|Step 1. Creating an FTP account
1. Expand Administration
E-mail NOT received!
Contact SMS Storetraffic Techsupport@storetraffic.com
SMS Storetraffic FTP usage information
Click here for more information
|Step 2. Accessing the Feature
|Step 3. Complete General Information
|Step 4. Input FTP Settings
The user has the ability to use the default integrated SMS FTP provider or indicate their own private FTP account by bulleting the following option.
|FTP - Use Default
|FTP - Custom
Please contact SMS Tech Support and be ready to provide information related to your specific FTP provider as we will need to whitelist it on our firewall in order for it to be used. Also, in the event that alternate ports are used than your typically default ports for the FTP transport protocols, additional fees related to the opening of these ports and testing may apply.
(Common ports are FTP=21, FTP/SSL(aka FTPS)=21 or 990, SFTP=22).
NOTE* - A connection test will be performed at the end of the add import setup wizard - if the connection test fails, an error message will be displayed, and the import job will NOT be added.
|Step 5 - Setting a Schedule
Indicate a Schedule for when and how often a file will be uploaded to the FTP. TMAS Application will take into consideration your current time zone. Best to schedule the import job for 5 minutes after your file to uploads to the FTP server.
|Step 6 - Complete Columns Information
|The purpose of this steps is to indicate to T.M.A.S. which type of file is being imported and which column of data contains which type of value.
|Depending on the Store ID selected for your locations the store # may be padded with Characters indicating your company. If this is not present you would need to indicate a mask for the Store ID column so that the TMAS application can match it to a location. If the Store ID is present in your file and exactly as indicated in the location ID of the location, leave this empty.
Selection MUST match the date column of the imported file. Available options are indicated in the dropdown.
|Summary Information File: The most common file type can be used for both Sales and Transactions as well as Traffic from another software.
|Staff log file
|Use when importing staff from a different source which could not be included in a Summary Information File.
|Transactions log file
|When importing transaction summaries (aka invoices) if items are not supplied, UPT will appear as 0 otherwise, we import what is supplied. For transaction details (aka invoice lines), by default one line means one item. You can specify items and we will use what you supply. Should you use non whole units like weight, they will be rounded off per time laps.
Then click Preview file and Match the example to what you will be uploading to the FTP
Preview Example for Selections above: Click ADD when it matches and you are satisfied with the selections
How to Edit or Delete an Import
Running an Import on Demand
Use the following option when you would like files to import quicker than the scheduled time (Daily or Hourly) has indicated
Click the Cloud and arrow icon to run - a message will be displayed indicating it has ran
F.A.Q. about data
|Q: Should the file be sent hourly or daily?
|A: Recommended to send the file hourly accumulative so that the T.M.A.S. dashboard features will be able to be made use of. LEARN MORE ABOUT DASHBOARDS
Q: How should the import file be named?
A: Even though T.M.A.S. software will import any ASCII based file, the file name should include the complete date as well as the store ID. This is to facilitate our backup procedure and to be able to better locate a file. The file name does not have any impact on the import process. The recommended extension is .txt or .csv
If you send the traffic hourly you could resend the same file, which would get overwritten, or you could add an underscore and the hour at the end of the name. For 13:00 this could be: 1010-04012014_13.txt
Q: Should the file contain only data for the previous interval?
A: Recommendation is as follows dependent on the upload frequency for import.
Q: Should the file be limited to business hours?
A: The file should NOT be limited to business hours only as some sales take place just after or before opening. Also, entries for non-business hours are preferable as it will ensure accurate data for any special events that takes place.
Q: Which types of transactions should be included in the import data?
A: Other type of transaction like gift cards, are irrelevant to SMS hosted software. It needs to be decided by the customer/POS provider when this transaction is applied. In any case a gift card should only applied once, either when it is used or when it is purchased, as it would corrupt the data to include it twice.
Q: Can T.M.A.S. accept 2 files one for Sales and Transactions and the other for Staff?
A: If Sales and Staff information comes from 2 different systems, it can be split in 2 files. Simply send a Sales & Transaction file without column #6, and a staff file without column #4 and #5 (column #6 becomes column #4)
Q: How does T.M.A.S. read the data in the file? Can more than 1 location be included in the same file?
A: T.M.A.S. imports data from a from each row based on the corresponding location ID which is existing in T.M.A.S. and the date/time indicated. The import is passive so whatever data is appearing in the files is what is. Always uses the last data received. If you have duplicate entry (same store, same date and time), the last data imported will be used, the other will be overwritten, not appended or added. There is no minimum or maximum delay between each file transfer and T.M.A.S. software (Less than 50 files per import scheduled). A file could be updated hourly with the latest completed hour, and sent to T.M.A.S. software. This will result in up to the last hour statistic. Because new values will delete old entries, in this case you can re-use the same file only with the last hour appended to it.