Point of Sale Upload

Owned by Jason Luther
Last updated: Oct 24, 2024 by Anees Afzal
16 min read

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 statuses, or to manually upload a file

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

POS data may also be uploaded via the RetailNext API. This allows you to upload individual (or a small set of) transactions as they happen, and thus see near real-time updates to metrics like conversion. The API also has much higher frequency/quantity limits compared to CSV upload. For instructions on using the RetailNext API, please see API | Point of Sale.

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

Identifier in POS Upload API

Required for POS Exceptions?

Value

Example Value

Column

Identifier

Identifier in POS Upload API

Required for POS Exceptions?

Value

Example Value

Store ID*

StoreID

store_id

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

receipt_number

Y

Unique identifier of the transaction.

ARO502218097

Terminal ID*

WorkstationID

workstation_id

Y

Identifier of the POS terminal where the transaction was performed.

Terminal 13

Timestamp*

ReceiptDateTime

receipt_date_time

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

line_items_total

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

transaction_total

Y

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

-27.651

Cashier ID

OperatorIDs

operator_ids

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

transaction_type

Y

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

RETURN

Transaction Status

TransactionStatus

transaction_status

Y

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

Note that Post-Void transactions must be ordered after the associated Finish transaction in uploads, whether it appears in a later line in the same file or a later file.

Successful

Payment Type

PaymentType

payments

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

tax_amount

N

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

-1.650

Sales Associate ID

SalesAssociateID

sales_associate_ids

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

currency_code

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

transaction_sub_type

N

Used only for custom reporting purposes; not visible anywhere in the cloud interface. Valid values and formatting detailed below this table.

Walk-in

Category

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

voided_receipt_number

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. Should be left blank for transactions that are not post voids. See Transaction Status Details below this table for more information.

ARO502218097

Division ID

DivisionID

division_id

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. Post-Void transactions must be ordered after the associated Finish transaction in uploads, whether it appears in a later line in the same file or a later file. 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, which will be retained and its status will be set to post-voided)

      • 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.

  • Valid values are (case-insensitive):

    • Walk-in

      • Alternate supported values that map to Walk-in subtype are: Walkin and w

    • Phone

      • Alternate supported values that map to Phone subtype are: p

    • BOPUS

  • If the subtype value is not one of those listed above, it will remain Not Set

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.

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”. Note that duplicated filenames will result in issues with line item matching - each transaction and line item pair must have a unique name versus other pairs.

Column

Identifier

Identifier in POS Upload API

Required for POS Exceptions?

Value

Example Value

Column

Identifier

Identifier in POS Upload API

Required for POS Exceptions?

Value

Example Value

Item Price*

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

N/A

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

sku

N

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

SKU1001

Description

Description

description

N

Brief description of the item.

Brown Winter Jacket

Quantity

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

discount_amount

N

Total discount for the line item. This value should be positive.

18.35

Discount Percentage

DiscountPercentage

discount_percentage

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

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/A

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/A

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/A

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

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

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.

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.

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

Download the example CSV file

Line Item File

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.

Transaction File

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.

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.

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.

POS configuration also allows you to choose how certain errors should be handled in the file uploads. See File Upload Configuration for details.

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. Note that this behavior applies regardless of the source of the transactions (e.g. a transaction uploaded via CSV can be overwritten by a transaction uploaded via the API).

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 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. Select “Show Transaction Status details” under File Format - Transaction earlier in this document for more information and examples for indicating post-voids.

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