/
Point of Sale Upload

Point of Sale Upload

Owned by Jason Luther
Last updated: Oct 24, 2024 by Anees Afzal
17 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.