Point of Sale Upload

This page details the CSV file format used for uploading Point of Sale (POS) data in order to integrate transaction information with RetailNext for analysis and to derive additional metrics; such as conversion, shopper yield, etc.

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 to setup direct POS integration (supported vendors only) or to get 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

Uploading POS Data

For each period of transaction data, the system requires two uploaded files.

  • Transaction - high-level information for each transaction occurring within that period (eg. the transaction total)

  • Line Item - detailed information about the products pertaining to each transaction (eg. the product SKU)

In order to make optimal use of the Asset Protection Dashboard, you’ll need to provide some additional transaction and line item fields. The columns required for POS Exceptions are noted in both file format instructions below.

File Format - Transaction

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

Column

Identifier

Required for POS Exceptions?

Value

Example Value

Column

Identifier

Required for POS Exceptions?

Value

Example Value

Store ID*

StoreID

Y

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

RNP

Transaction ID*

ReceiptNumber

Y

Unique identifier of the transaction.

ARO502218097

Terminal ID*

WorkstationID

Y

Identifier of the POS terminal where the transaction was performed.

Terminal 13

Timestamp*

ReceiptDateTime

Y

Date and time of the transaction, with default format YYYY‐MM‐DD HH:MM:SS (format configurable in Admin Settings > File Upload Configuration > POS). Timestamps are interpreted per the store’s configured timezone.

2016-06-22 11:01:00

Line Items Total*

LineItemsTotal

Y

Total before tax and transaction-level discounts; i.e. the sum of all line items that were part of the transaction, including line-item discounts. This value can be negative for return transactions.

-39.58

Transaction Total*

TransactionTotal

Y

Amount the customer pays, including tax and transaction-level discounts. This value can be negative for return transactions.

-27.651

Cashier ID

OperatorIDs

Y

Unique identifier of the employee who perfomed the transaction. This must be included and must match the value used when integrating staffing data. Multiple Cashier IDs are supported when separated with a semicolon.

Emp1005_Justin

Transaction Type

TransactionType

Y

The type of transaction; e.g. Sale or Return. Valid values and formatting detailed below this table.

RETURN

Transaction Status

TransactionStatus

Y

Indication of transaction success; e.g. Finish or Post-Void. Valid values and formatting detailed below this table.

Successful

Payment Type

PaymentType

Y

Method of payment used for the transaction. See the Appendix for a list of supported payment types.

  • If multiple methods of payment were used for the same transaction, create one line for each payment type and leave all other fields the same. This only works for lines within the same file.

  • If payment type is not one of the supported ones, it will be set as NA.

cash

Tax Amount

TaxAmount

N

Total tax paid on transaction. This value can be negative for return transactions.

-1.650

Sales Associate ID

SalesAssociateID

N

Unique identifier of the employee who should be recognized for the transaction, used when integrating staffing data. Multiple Sales Associate IDs are supported when separated with a semicolon.

1112 Sally Smith

Currency Code

CurrencyCode

N

The currency used for the transaction. See the list of supported currency codes.

  • If the value for currency is unrecognized or Null in some line(s), the file will fail validation and will not process.

  • If you don’t included this value, the system defaults to the currency specified in Admin Settings > File Upload Configuration > POS > Default currency code, as detailed lower on this page. This may only be used if all transactions across all stores use the same currency.

USD

Transaction Subtype

TransactionSubType

N

Used only for custom reporting purposes; not visible anywhere in the cloud interface. Valid values are case-insensitive: WalkIn (default), Phone.

WalkIn

Category

Category

N

Indication of whether to include the transaction in data mining calculations. Valid values and formatting detailed below this table.

Retail

Voided Transaction ID

VoidedReceiptNumber

N

The ID of the transaction being post-voided. This is used when the transaction ID of the post-voiding transaction (the one with status set to post-void) is different than the ID of the original transaction that is being voided. See Transaction Status Details below this table for more information.

ARO502218097

Division ID

DivisionID

N

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.

037

* required column

  • Valid values are (case-insensitive):

    • Sale

    • Return

    • Presell - not counted as part of sales

    • Post - counted as part of sales and displayed as type Sale

  • If the Transaction Type value is not one of the above, it will be automatically set to the default type, which is Sale.

  • Prefix matches are also accepted for Return, Presell, and Post; where the provided type starts with one of those terms. For example, Return or Exchange would map to Return.

  • In POS systems where a sale and a return can be performed as part of the same transaction, and a separate Return transaction type is not supported, you may set the type to Sale. In such cases, you should set the quantity of the returned items to negative in the line item file. Note that when using this method the transaction will still count as a Sale and will increment the number of sales transactions, not return transactions.

  • In POS systems where separate items are sold and returned, classify the transaction type using the logic below.

    • Classify as Sale if the transaction total is greater than $0.00

    • Classify as Return if the transaction total is less than or equal to $0.00. Note that if the transaction type is Return, but the totals are positive for an entry, those totals will not be negated when they’re saved in the system.

    • Note: Transactions are not automatically set to Return type if the total is negative.

  • Valid values are (case-insensitive):

    • Finish

    • Deliver - status is stored but these transactions are handled just like Finish

    • Fail - does not count towards sales metrics

    • Cancel - status is stored but these transactions are handled just like Fail

    • Post-void or Postvoid

    • Void - status is stored but these transactions are handled just like Fail

    • Transactionvoid - status is stored but these transactions are handled just like Fail

    • Substring matching is also supported; i.e. if the transaction status value is something longer it will still match (e.g. “delivered” will match Deliver; “failed” will match Fail).

  • If the transaction status value is not one of those listed above (e.g. "Successful"), it will be automatically set to the default status, which is Finished

  • Post-void status is supported to void a transaction which was previously defined as Finish. There are two ways to specify which previous transaction should be voided, detailed below.

    • If the transaction ID of the post-voiding transaction (the one with status set to post-void) is the same as the ID of the original transaction that is being voided, then no additional fields need to be set. The transaction ID of the post-voiding line is used to find the original successful transaction in the system and mark it as post-voided. The transactions must have matching Transaction ID, Store ID, and Terminal ID to be considered a valid post-void. For example:

      • AR0502218090,Store1002,Terminal 12,Emp1001,2015-10-12 09:44:00,SALE,Successful,CreditCard,73.41,78.92,5.51,Emp1001 (original successful transaction)

      • AR0502218090,Store1002,Terminal 12,Emp1002,2015-10-13 07:45:00,SALE,POST-VOID,CreditCard,73.41,78.92,5.51,Emp1002 (post-void of original transaction)

    • If the transaction ID of the post-voiding transaction is different from the ID of the original transaction that is being voided, then the Voided Transaction ID field should be set, and that ID is used to find the original successful transaction in the system and mark it as post-voided. For example:

      • AR0502216051,Store1002,Terminal 12,Emp1001,2015-10-12 09:44:00,SALE,Successful,CreditCard,73.41,78.92,5.51,Emp1001, (original successful transaction)

      • AR0502218090,Store1002,Terminal 12,Emp1002,2015-10-13 07:45:00,SALE,POST-VOID,CreditCard,73.41,78.92,5.51,Emp1002,AR0502216051 (post-void of original transaction including the Voided Transaction ID field)

  • Valid values are (case-insensitive):

    • RETAIL - Include in all data mining calculations

      • Alternate supported values that map to RETAIL category are: include, 1 and true

    • BILLPAY - Bill payments not to be included in data mining calculations

    • EXCLUDE_RETAIL - Other transactions not to be included in data mining calculations

      • Alternate supported values that map to EXCLUDE_RETAIL category are: exclude, 0 and false

  • If the category value is not one of those listed above, it will be automatically set to the default category, which is RETAIL.

Example - Transaction

In this example, we’re uploading a transaction file for a cash return transaction performed by Justin at Store ID: RNP and at the POS terminal 13. All transaction file fields are included up until the currency code.

1 ARO502218097,RNP,Terminal 13,Emp1005_Justin,2016-06-22 11:01:00,RETURN,Successful,cash,-39.58,-27.651,-1.650,Emp1005_Justin,USD

Download the example CSV file

File Format - Line Item

For each period of transaction data, the system requires a Line Item file in addition to a Transaction file. Although the Line Item file must be present, it may be empty if line item data is not available.

Filename: Begins with “LineItemInfo_” and ends with “.csv”. The custom identifier in the filename between “…Info_” and “.csv” must match up exactly with the custom identifier used in the corresponding Transaction filename, which is recommended to be the timestamp of the transaction; e.g. “LineItemInfo_YYYYMMDD_HHMMSS.csv”.

Column

Identifier

Required for POS Exceptions?

Value

Example Value

Column

Identifier

Required for POS Exceptions?

Value

Example Value

Item Price*

Price

Y

The unit price of the item that was sold or returned. This price includes any applied promotions or discounts.

73.41

Transaction ID*

ReceiptNumber

Y

ID of the transaction to which this line item belongs. This value must match exactly with the TransactionID used in the corresponding Transaction file. Valid values and formatting detailed below this table.

AR0502218090

SKU

SKU

N

SKU # of the item. This value must match exactly with the SKU value used when integrating product locations.

SKU1001

Description

Description

N

Brief description of the item.

Brown Winter Jacket

Quantity

Quantity

N

The number of items of the specific SKU that were purchased. This value can be negative for return transactions. (Must be a number between -32,767 and 32,767)

1

Discount Amount

DiscountAmount

N

Total discount for the line item.

18.35

Discount Percentage

DiscountPercentage

N

Percent of discount for the line item. If both DiscountAmount and DiscountPercentage are supplied then both values will be stored, but DiscountAmount is used to determine the total discount for the transaction. If only one is specified, the other is calculated from the specified value.

25

Void

Void

Y

Indication of whether the line item has been voided; e.g. Y or N. Valid values and formatting detailed below this table.

Y

Store ID

StoreID

N

Only required when duplicate TransactionID exist. Details below this table. Store identifier that matches the configured Store ID in Admin Settings > Locations. If POS Store ID is enabled, you must use that value.

AR0502218090

Terminal ID

WorkstationID

N

Only required when duplicate TransactionID exist. Details below this table. Identifier of the POS terminal where the transaction was performed.

Terminal 13

Timestamp

ReceiptDateTime

N

Only required when duplicate TransactionID exist. Details below this table. Date and time of the transaction, with default format YYYY‐MM‐DD HH:MM:SS (format configurable in Admin Settings > File Upload Configuration > POS). Timestamps are interpreted per the store’s configured timezone.

2016-06-22 11:01:00

* required column

  • Valid values are (case-insensitive):

    • Y - the line item has been voided

    • N - the line item has not been voided

    • PAIR - used if when your system specifies two duplicate lines for voided items

    • Substring matching is also supported; i.e. if the void value is something longer it will still match (e.g. “Yes” will match Y; “No” will match N).

  • If the transaction status value is not one of those listed above (e.g. "Successful"), it will be automatically set to the default status, which is Finished

Void Example - Single Line

1 2 SKU1002,Train Set,73.41,1,AR0502218090,Y SKU1003,Red Ball,10.99,1,AR0502218090,N

In the above example, the Train Set item will be marked as voided and the Red Ball item as not voided.

Void Example - Duplicate Lines

1 2 3 SKU1002,Train Set,73.41,1,AR0502218090,N SKU1002,Train Set,73.41,1,AR0502218090,PAIR SKU1003,Red Ball,10.99,1,AR0502218090,N

In the above example, a single line item will be recorded in the system for the Train Set item and it will be marked as voided. Such cases require that the duplicated line fields match up exactly, except for the void field.

If there is no duplicate line in the file, the PAIR entry will be skipped. If there are multiple lines that duplicate the PAIR line, only one of them will be voided. The original line item and voiding duplicate line item must be in the same file.

Ideally, within any pair of transaction and line item files, each transaction will have a unique TransactionID value. The exception is for lines that indicate multiple payment types for the same transaction or that void a transaction, in which case you would need to use additional line item fields to associate each line item with the correct transaction.

If you must upload a transaction file that uses the same TransactionID for two separate transactions, you must enable and specify the line item fields: StoreID, TerminalID and Timestamp. If you do not enable or include the additional fields in the line items file, all line items with that TransactionID will be associated with the first transaction in the transaction file with that transaction ID.

Common Duplicate Transaction ID Scenarios

  • Upload two transactions with the Same TransactionID, Same StoreID, Different TerminalID: One transaction created 

  • Upload two transactions with the Same TransactionID, Different StoreID: Two transactions created

Example - Line Item

In this example, we’re uploading a line item file corresponding to the transaction with ID: AR0502218090. The line item information includes the details of the voided Brown Winter Jacket item, the only item pertaining to this transaction with no associated discounts.

1 SKU1001,Brown Winter Jacket,73.41,1,AR0502218090,Y

Download the example CSV file

Combined Example

In this example, we’re uploading the transaction file as well as the corresponding line item file for the sale and return of the Red Ball item.

Transaction File

1 2 AR0502218090,Store1002,Terminal 12,Emp1001,2015-10-12 09:44:00,SALE,Successful,CreditCard,73.41,78.92,5.51,Emp1001 AR0502218091,Store1002,Terminal 12,Emp1001,2015-10-12 10:44:00,RETURN,Successful,CreditCard,-73.41,-78.92,-5.51,Emp1001

Download the example CSV file

Line Item File

1 2 SKU1001,Red Ball,1,73.41,AR0502218090 SKU1001,Red Ball,-1,-73.41,AR0502218091

Download the example CSV file

The sale and subsequent return of the Red Ball item will result in two respective transaction entries in the system, with a net combined sales value of $0.00.


Keeping with the above example, let’s assume a scenario where the POS system is set up to list sales and returns/discounts in separate lines using the same transaction ID value and it submits blank line item files (no receipt information).

Transaction File

1 2 AR0502218092,Store1002,Terminal 12,Emp1001,2015-10-12 11:44:00,SALE,Successful,CreditCard,73.41,78.92,5.51,Emp1001 AR0502218092,Store1002,Terminal 12,Emp1001,2015-10-12 11:44:00,RETURN,Successful,CreditCard,-13.41,-16.92,-3.51,Emp1001

Download the example CSV file

In this scenario, the system will consider the transaction as a Return and will just show a sales value of -$16.92 for the transaction because the system simply overwrites line1 entry with line2. The unfavorable result is due to the absence of additional fields in the line item file to handle the duplicate transaction ID lines. The system simply overwrites line1 entry with line2.

Validation

When uploading POS files (transaction and line item), you will receive feedback about any errors that were found during validation. If a file fails validation, even just for a single line, it will not be processed. You can review previously uploaded files and any related errors by navigating to the POS 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

Required column missing

Reported once for each required column that is not in the header. Includes the name of the missing column.

Unknown column

Reported once for each column in the header that wasn’t recognized. Includes the name of the unknown column.

Wrong number of columns

Reported for every line that has a different number of columns than the header.

Required column empty

A required column has an empty value.

Invalid value

Generally refers to improper syntax in a field. For example, having a transaction total value of 50.2F instead of 50.25.

Bad date format, or letters or characters in a field that requires numbers.

You may also receive this error if Skip header line is unchecked in Admin Settings > File Upload Configuration > POS and the first line of the uploaded file includes the column headers.

Quote error

Reports improper use of a double quote in a line. Includes the character position at which the first invalid quote appears. The CSV file format specifies that double quote characters can only appear in a quoted field, and within a quoted field, two consecutive double quotes represent one double quote character.

Value contains newline

Refers to a newline character detected within the field, which improperly formats a line into multiple lines.

Empty file

The file doesn’t contain anything.

Internal error

Something went wrong when validating the file. There will be a corresponding log message that indicates what the error was.

Multiple currencies

The store is using multiple currencies.

Transaction has too many line items

A transaction exceeds the limit of 10,000 line items associated to it.

 

In other cases, warnings will be listed when there is data in a POS file that appears incorrect and may not be processed as expected.  Files with warnings will validate and process, but in some cases the offending line(s) will be skipped.

Warning

Meaning

Store not found

The Store ID (or if enabled, the POS Store ID) referenced in the transaction file does not match any existing stores in RetailNext.  Ensure that the store ID is configured correctly in Admin Settings > Locations.

Incompatible currency

The specified currency does not match the currency set for the store. The values are processed in the store currency and are not converted.

Missing/Invalid Files and Lines

The points below describe how the system handles a few additional scenarios when uploading POS files that have missing information.

  • If there is a file TransactionInfo_x.csv, there should be a corresponding file LineItemInfo_x.csv (where x is a consistent value) along with it in order for the system to process those transactions. If the files are empty (0 bytes), they will be ignored.

  • If there are line item lines with a Transaction ID that is not present in the transaction file, those lines will be ignored as “orphan item lines of tx Id:x” and the file will be processed.

  • If there is a transaction line with no line items with the same transaction ID, the transaction will be processed with an empty receipt.

Additional Details

Re-uploading and Updating Transactions

If the same transaction is uploaded more than once in separate files, the new file transaction data will always overwrite the existing transaction data, meaning that you won’t get duplicate data. In order for a new transaction to update an existing one (versus creating an additional transaction), they must have the same Store ID, Terminal ID, Timestamp, and Transaction ID.

Currency Code Handling

Organizations that only transact in a single currency won’t need to include the Currency Code column in transaction files. In that case, a default currency must be configured in Admin Settings > File Upload Configuration > POS prior to uploading POS files, whereby all transactions are allocated to the specified default currency.

Organizations that transact in multiple currencies must specify the Currency Code value in each transaction file line to ensure that the correct currency gets allocated for each transaction. Note that a store can only have one currency code across all its transactions. By default, files containing a transaction with a currency that doesn’t match the store currency will be rejected. Alternatively, you can select the Change incompatible currencies to store currency option, which will update the currency for the offending transaction to the store currency, however it will not convert the values.

Direct POS Integration

If your organization uses a third party POS vendor like Lightspeed, Shopify, Vend or Square; you can easily integrate your transactions without needing to upload files from an automated system as described throughout this page.

To connect one of the listed POS vendors with your RetailNext account, navigate to Admin Settings > File Upload Access > POS. Visit File Upload Access for more setup instructions.

Additional Notes

  • If you use Shopify and have enabled POS Store ID in RetailNext, load Shopify IDs as per the store's POS Store ID.  You should only need to enable the POS Store ID if you need to use a different Store ID for other integrations.

  • If you use Square, you must enable and use POS Store ID in RetailNext

  • RetailNext starts pulling all historical POS transaction after connecting to the third party POS platform. Note that it may take several days to catch up with current transactions.

  • You may notice differences between the sales data reported in RetailNext and the sales data reported in other dashboards, like the one provided by your POS vendor directly.  There can be several causes for this, including:

    • RetailNext displays sales data for the configured store hours by default.  If transactions are processed before the store opens or after it closes, these may not be included.  Change your data query to 24 hours to compare more effectively.

    • RetailNext does not include tax when calculating sales metrics.  Some other systems may include tax.

Transaction-Level Discounts

There is currently no explicit support for specifying transaction-level discounts, but they can be inferred from the difference between the transaction total and the line items total plus tax (labelled as Adjustments in the cloud interface).

POS Exception Requirements

In order to make optimal use of the Asset Protection Dashboard, you’ll need to provide some additional transaction and line item fields that aren’t necessarily required for standard POS reporting.

The Asset Protection Dashboard analyzes three different POS exception types, where certain fields are required in uploaded POS files for each exception type and for the overall usage of the dashboard, as listed below.

General Requirements

The risk scores on the Asset Protection Dashboard, assigned to the relevant cashier, are based on the percentage of exception sales (voids and cash refunds) to total sales. It follows that the transaction file fields listed below are required to calculate all exceptions for this dashboard.

  • Transaction Total

  • Cashier ID

Post-Void Exceptions

A post-void is a transaction that cancels, or voids, a previously completed transaction. While post-voids may occur in the usual course of business from time-to-time, they may be worth investigating as suspicious activity, especially when occurring frequently at a store or more often with certain cashiers than others.

Post-void transactions must be indicated in the relevant transaction file’s Transaction Status column, using a case-insensitive value of post-void or postvoid. The transactions must have matching transaction ID, Store ID, and Terminal ID to be considered a valid post-void.

In the example below, the original transaction will be retained and its status will be set to post-voided.

1 2 AR0502218090,Store1002,Terminal 12,Emp1001,2015-10-12 09:44:00,SALE,Successful,CreditCard,73.41,78.92,5.51,Emp1001 AR0502218090,Store1002,Terminal 12,Emp1002,2015-10-13 07:45:00,SALE,POST-VOID,CreditCard,73.41,78.92,5.51,Emp1002

Post-Void Search Strategy - In rare cases, you may wish to specify a non-default time range that the system should use when searching for previous transactions to match up with a post-void transaction. Three options are available that apply to an entire subscription:

  • Site Creation (default): The system looks at all transaction data from the beginning of the site creation date to find the original transaction and void it.

  • Timeout: Only check transactions within the specified time range (default value is 24 hrs)

  • Daily: Only check transactions that occurred on that same calendar day

Should you wish to change your search strategy to use something else than the default option, please contact RetailNext Support with your request.

Cash Refund Exceptions

Cash refunds are when previous transactions are partially or wholly refunded in cash. For the system to identify cash refunds and calculate those exceptions, you must specify valid values for Payment Type (in particular, cash) and Transaction Type (Sale or Return) when uploading transaction files.

Line Item Void Exceptions

Line item voids are when one or more items within a transaction, but not the entire transaction, get voided by a cashier.

Line item voids are indicated in the Void column. This column must be included and with a correct value in line item file uploads to calculate exceptions. Although required for standard POS reporting, the Item Price column must also have a correct value for accurate risk score calculations.

Appendix

Supported Payment Types

?i = case insensitive
.* = ignore anything before or after (.* represents any 0 or more characters)

Match

Value

Match

Value

(?i)VI

TenderType_CreditDebit

(?i)MC

TenderType_CreditDebit

(?i)AX

TenderType_CreditDebit

(?i)DC

TenderType_CreditDebit

(?i)DS

TenderType_CreditDebit

(?i).*VISA.*

TenderType_CreditDebit

(?i).*MASTER.*C.*D.*

TenderType_CreditDebit

(?i).*AM.*EX.*

TenderType_CreditDebit

(?i).*DINERS.*CLUB.*

TenderType_CreditDebit

(?i).*DSCVR.*

TenderType_CreditDebit

(?i).*DISCOVER.*

TenderType_CreditDebit

(?i).*DISC.*

TenderType_CreditDebit

(?i).*\bstore\b\s+\bcred\b.*

TenderType_StoreCredit

(?i).*\bloyalty\b.*

TenderType_Loyalty

(?i).*credit.*debit.*

TenderType_CreditDebit

(?i).*credit.*

TenderType_CreditCard

(?i).*debit.*

TenderType_DebitCard

(?i).*DEBT.*

TenderType_DebitCard

(?i).*cas.*

TenderType_Cash

(?i).*gift.*card.*

TenderType_GiftCard

(?i).*gft.*cd.*

TenderType_GiftCard

(?i).*gift.*cert.*

TenderType_GiftCard

(?i).*gft.*ct.*

TenderType_GiftCard

(?i)gift

TenderType_GiftCard

(?i)coup

TenderType_Coupon

(?i).*travelers.*

TenderType_TravelersCheck

(?i).*travellers.*

TenderType_TravelersCheck

(?i).*check.*

TenderType_Check

(?i).*house.*

TenderType_HouseAccount

(?i).*customer.*account.*

TenderType_CustomerAccount

(?i).*shopify.*payments.*

TenderType_CreditDebit

(?i).*shopify.*

TenderType_CreditDebit

(?i).*square.*

TenderType_CreditDebit

(?i).*split.*

TenderType_Split

(?i).*null.*

TenderType_NA

(?i).*none.*

TenderType_NA

(?i)na

TenderType_NA

(?i).*

TenderType_NA