Importing sales and staff data from a file

Third-Party application criteria

• Ability to generate a summarized sales file by the hour, (30min or 15min also works). OR generate a transaction log detailing total qty of items sold and total of transaction per line. For more information related to what data should be included in the verify the samples below.
• Ability to output comma, semi-colon or tab delimited file in txt, csv or xls* format.
• Ability to schedule the file to be generated daily or hourly (30min or 15min also works).
• Ability to deliver to the T.M.A.S. FTP server (Your custom FTP server and E-mail are also an options)

If you are uncertain whether you meet the above criteria, please contact techsupport@storetraffic.com to discuss the possibilities.

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.

• If there is no sale for this hour the value needs to be 0.00.
• If the total sale for the hour is negative, a minus “–“ must be added before the sales amount. Example: -88.67

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:

• If there is no transaction for this hour the value needs to be 0.
• If you want to deduce credit, just subtract credits/returns from the transaction number.
• If the total amount of transaction (invoices minus credit) for this hour is negative, a minus sign “–“ must be added before the transaction amount. If only 2 credits were made during an hour, we would put “-2” in this field for that hour.

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.

• It accepts decimal. But these will not be shown in the report, they will be rounded up.
• An employee is considered to be a staff when he is actually on the sales floor and ready to answer or assist a customer.
• A manager can be counted here if he is actually working on the floor and assisting customers. If he is doing work in the back store we would recommend not counting him in this statistic.
• 0 is accepted in this field if there really is no one working on the sales floor.

How to setup file importation

1. Expand Administration
2. Select Manage Locations
3. Select Import / Export Data / P.O.S. Integration
4. Select Add
5. Click Create new Storetraffic FTP Account
6. Input the E-mail address for the user account
7. Click Add - User will receive an e-mail from noreply@smssoftware.net with the password. This account will then be selectable in the StoreTrafficFTP* dropdown.
 

1. Login to TMAS
2. Select Configure
3. Select Manage Locations
4. Select Import / Export data
5. Select Add

1. Indicate Label (Name to be given to import job)
2. Indicate Import Type
   • Fixed Width - Rarely Used 
   • Delimited  - Most Common
   • STC File - Used to import traffic data from another source into T.M.A.S.

1. Indicate file mask (*.csv or *.txt)
2. (Optional) Indicate delimiter (Comma, Semi-Colon; or Tab) if Delimiter import type was selected
3. Status Active as default

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.

• Use Default
• Custom

1. Bullet Use Default - For SMS Provided FTP site
2. Indicate an Existing FTP User or create a NEW FTP user for the SMS FTP Site
   • To "Create new BFTP Account" do the following
     1. Click the link to Create new FTP Account
     2. Input the e-mail address you want associated to the account
     3. Click Add
        • You will receive an e-mail with the password from noreply@smssoftware.net.
        • To login to this FTP account proceed to https://files.smssoftware.net and use the e-mail address used for account creation and password sent by e-mail.

1. Verify / Update - FTP address path points to the Import folder or root by default.  (Optional) check off change default path and indicate a new path if other folders were created in the SMS FTP site. Note* This process will not create folders on the FTP, you would need to login separately to create this.
2. Click Next

1. Bullet Custom for FTP
2. Input the URL of the FTP address
3. Indicate a port *See warning above
4. Input username
5. Input password
6. Indicate file path
7. Click Next

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.

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.

1. Select schedule type (Daily, Hourly, 30 min or 15min)
2. Set Hour to Start Import
3. Click Next 

Selection MUST match the date column of the imported file. Available options are indicated in the dropdown.

• Check Off & Indicate Column # of the Location ID - the Store ID is present in your file and exactly as indicated in the location ID of the location
• Check Off & Indicate Column # of the Date - Day you would like to import for
• Check Off & Indicate Column # of the Hour - Hour you would like to import for
• Check Off & Indicate Column # any other columns such as Sales, Transactions and items 

• Check Off & Indicate Column # of the Location ID - the Store ID is present in your file and exactly as indicated in the location ID of the location
• Check Off & Indicate Column # of the Date - Day you would like to import for
• Check Off & Indicate Column # of the Hour - Hour you would like to import for
• Check Off & Indicate Column # any other columns such as Staff id, Punch IN , Punch OUT

Example: 

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

1. Expand Administration
2. Select Manage Locations
3. Click Import/Export data
4. Check off the desired import
5. Click the Pencil Icon to Edit or Trash Icon to Delete

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: 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
Example, a file from store ID 1010 containing data for April 1st 2014 could be named 1010-04012014.txt

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.

• Hourly - File should be accumulative for the current day.
• Daily - File should include all hours of the day.

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.