Daily Transactions Integration via Batch Mode
With this article, learn how to award points for offline purchases.
Introduction
Batch Mode Integration is a convenient way to award or deduct points for offline purchases performed via in-store Point-of-Sale or telephone. Please familiarize yourself with Batch Mode Integration before proceeding with this article.
To award points for any offline purchase activity or to deduct points for purchases that are returned, you must upload the daily transactions file to the designated FTP Server in CSV format.
Note:
Our system records all date and time fields in UTC time. This applies to the system generated fields such as enrollment date as well as the date time fields passed through the batch files.
This file contains a list of all purchase transactions and/or return transactions with the order details necessary to award points for purchases or deduct points for returns. In addition to purchase or return details, custom attributes for particular purchase or return transactions can also be added to the file in respective columns.
The file expects one row per purchase item for an order OR one row per returned item. So, if the order contains 5 items, there will be 5 rows for that order in the CSV file.
The same file can have both purchase transactions as well as return transactions.
The naming convention for this file is ZN_DAILY_TRANSACTIONS_YYYYMMDD.csv. For example, a file with orders for July 21, 2020, should be named ZN_DAILY_TRANSACTIONS_20200721.csv.
Please Note:
The csv file name should not contain any special characters.
CSV Fields
The fields contained in this CSV file are described in the table below:
| Field | Description | Mandatory (Yes/ No)? | Example |
|---|---|---|---|
| order_timestamp | Date in YYYYMMDD format followed by time in HH:MM:SS format (24 hours) on which the order was placed. | Yes | 20200721/ 00:00:00 |
| timezone | The timezone in which the purchases were made. This field is case-sensitive. The value should always be in all caps. | No | PST, CST, IST, etc. |
| points_expiration_date | Pass the date and time for point expiration. This will override the global settings of point expiration. Format: YYYYMMDD/HH:MM:SS (20241221/18:00:04). Passing the time is optional. | No | 20241221/ 18:00:04 |
| order_id | The order ID of the order which is being placed. This field is used to uniquely identify the order. | Yes | FX-250123-110-12431 |
| order_type | This field tells whether this row is a purchase or return. The value of this field can be "purchase" or "return." | Yes (If no value is provided in the column, it will be interpreted as a 'purchase.' Therefore, it is essential to provide a value, particularly when the order type is 'return.) | purchase/ return |
| order_total | Gross total amount of the order including shipping. discount, etc. | Yes Note: If it is not passed, the system will rely on order_subtotal. | 53.49 |
| order_subtotal | Net total amount of the order excluding shipping, discount. Points will be awarded to the member based on this amount. Should be greater than 0. | Yes Note: If it is not passed, the system will rely on product price and quantity. | 39.99 |
| coupon_code | Value of the coupon_code used while making this purchase. | No | 15OFF |
| currency | Three letter currency code of the order placed. | No | USD |
| user_name | Name of the member (first name and the last name) | Yes | John Doe |
| member_id | Unique Identifier (Member ID) for the member object in the client’s system | Yes (if this column is passed in CSV) | Customer ID/ GUID/ Email Address/ or Phone Number |
| user_email | Email address of the member. | Yes (If member_id is passed in CSV, user_email address becomes optional.) | [email protected] |
| client_user_id | User ID assigned to the member by the admin. If no value was assigned any random value can be passed. | Yes | JOH08976 |
| approval_date | Date and time on which the transaction will be auto approved. Format: YYYYMMDD/HH:MM:SS. | No | 20231221/18:00:04 |
| user_phone | The phone number of the buyer without country code. | No | 9713332246 |
| product_id | The product ID of the product purchased. | No | 333215 |
| product_price | Price of the product. Value should be a Float (default 0.0) | No (If the fields "order_total" and "order_subtotal" are not passed, this field becomes mandatory.) | 39.99 |
| quantity | Quantity of the ordered product. Value should be a Int (default1) | No (If the fields "order_total" and "order_subtotal" are not passed, this field becomes mandatory.) | 1/ 1.25 |
| product_title | Name of the ordered product. | No | Fuji Instax Camera |
| product_category | Category to which ordered product belongs. Multiple categories can be passed in comma separated format without space. | No | promotion,camera |
| product_tag | Tags which describe the product. Multiple tags can be passed in comma separated format without space. | No | sale item,clearance |
| {activity_id}.{client_internal_id} | This is activity specific custom attributes. To know more, click here. You can pass in custom attributes that you want Zinrelo to associate with the transaction. The types of attributes you can create: - String - Date - List - Numerical The expected value for date formal: MM/DD/YYYY HH:MM:SS (passing the time is optional). | No | |
| {global}.{client_internal_id} | This is global attributes or activities global attributes. To know more, click here. You can pass in custom attributes that you want Zinrelo to associate with the transaction. The types of attributes you can create: - String - Date - List - Numerical The expected value for date formal: MM/DD/YYYY HH:MM:SS (passing the time is optional). | No |
Please Note:
- If your program does not utilize an email address as the primary identifier, the 'member_id' field will be included in the CSV. Otherwise, it will not appear in the CSV file.
- To calculate points for an order, priority is given to the product prices. If product prices are not present, the order subtotal will be used to award points. If there is a discount applied on the order, the product prices should also reflect the discount i.e. the discounted product prices should be sent in the API instead of the full price.
The sum of product prices should match the order subtotal - the amount on which points are expected to be awarded.
Some systems have limitations in sending the discounted prices of products. To handle such situations, we compare the order subtotal with the sum of product prices. In the event that the order subtotal is lesser, we assume that the order discount doesn't reflect in the product prices. So we prorate the product prices and then use those prorated prices for awarding points.
If you do not have any data for the optional columns, don't pass the column name.
If the merchant is unable to provide a comma separated string, then columns like product_category1, product_category2 etc., can be used. Based on the prefix (ie- product_category), all the product categories will be converted to a single comma separated string.
Similar approach will be followed for product_tags- product_tags1, product_tags2 etc., should be combined.
You can find the error message here.
Note:
- The CSV file should not exceed 5000 records. If you wish to upload a larger volume, please create a new CSV file with the additional records.
- Please ensure that you review the "numeric data" before adding it to any field. There is a possibility that it could be converted to a scientific data format when inserted into the CSV file.
- The downloaded CSV will contain activity attributes, activity global attributes and global attributes as it specifically pertains to activities. Reward and reward global attributes will not be included.
- The CSV file must not have any rows that solely consist of white spaces.
- When uploading a CSV file through FTP, it undergoes processing every 8 hours. However, for manual processing, the file is processed immediately.
- CSV files should not contain double quotes.
- You can process a maximum of 50 files per day.
- The file must be encoded in UTF-8 format and saved as a CSV file.
Download Sample CSV
To download the sample CSV file, go to General >> Settings >> Batch Mode within the Zinrelo admin console.
Choose 'Daily Transaction File' then, click on 'Download'."
Updated about 2 months ago