How to Import your Data in REVEAL

Overview

The data needed by Reveal needs to be delivered within data feeds served via HTTP, into JSON format. A separate file is needed for each type of data that Reveal imports: customers, categories, products, orders.

Security can be enforced by using either IP-based restrictions and/or user and password protection.

Please note that ideally, you would have one set of feeds for the initial import, and a different set of feeds for the daily import, containing less information, by including only relevant records. Please see the daily import optimization section for details.

Feed files – structure and contents

Customer data structure

Attribute	Type	Limits	Description
customer_eid	string	255	[required] The identifier you have defined for the customer
email	string	255	[required] The email of the customer
date_registered	string	10	[required] YYYY-MM-DD (UTC)
first_name	string	255	[optional]
last_name	string	255	[optional]
country	string	255	[optional]
region	string	255	[optional]
city	string	255	[optional]
gender	string	1	[optional] m, f, o
yob	int	1900-2010	[optional] Year of birth
accepts_marketing	int	0/1	[optional] 1 if marketing communication is allowed, 0 otherwise
custom_attributes	object	–	[optional] Key value pairs. Key should be a snake_case string and the value string, int or float. All the customers should have the same custom_attributes list

Why do we ask this info?

• email – for creating the NPS Invitations. We export also with customer_eid, so if the client doesn’t want to import the email in our system, he will need to process the NPS Invitations list. Still he must provide us a unique and valid format email per customer (e.g. [email protected]_domain)
• date_registered – we will allocate acquisition cost provided from that month
• first_name – Further development
• last_name – Further development
• country/region/city – you can see many reports per location (RFM, NPS, buying patterns)
• gender – you can see many reports per gender
• yob – you can see reports per age intervals
• custom_attributes – you can see many reports per each custom_attribute (RFM per custom_attribute, NPS per custom_attribute, …)

Example feed:

{
    "customers": [
        {
            "customer_eid": "1234",
            "email": "[email protected]",
            "date_registered": "2018-01-25",
            "first_name": "John",
            "last_name": "Doe",
            "country": "Romania",
            "region": "Bucharest",
            "city": "Bucharest",
            "gender": "m",
            "yob": 1984,
            "custom_attributes": {}
        },
    ]
}

(Product) Category data structure

Attribute	Type	Limits	Description
category_eid	string	255	[required] The identifier you have defined for the category
parent_category_eid	string	255	[required] The identifier for the parent category; null for root category
level	int	0-255	[required] 0 means root category
name	string	255	[required]
margin	float	≥0	[optional] avg margin (as percent) the shop makes per category
url	string	1024	[optional]

Why do we ask this info?

• category_eid/parent_category_eid/level – we are reconstructing the category trees, in order to generate reports per categories
• name – in order to see the names in the reports
• margin – in order to see accurate reports about profit structure
• url – Further development

Example Feed:

{
    "categories": [
        {
            "category_eid": "1234",
            "parent_category_eid": "1",
            "level": 1,
            "name": "Category name",
            "margin": 4.56,
            "url": "https://domain.com/category_name",
        },
    ]
}

Product data structure

Attribute	Type	Limits	Description
product_eid	string	255	[required] The identifier you have defined for the product
parent_product_eid	string	255	[required] The identifier you have defined for the parent product, if this is a variant; null if this is the parent product itself
sku	string	255	[optional] The SKU of the product
title	string	255	[required] The name of the product
url	string	1024	[optional] The product URL
img	string	1024	[optional] A product Image URL
description	string	1024	[optional] Truncated to 1024 characters
date_added	string	10	[optional] YYYY-MM-dd
in_stock	int	0-1	[required]
bc_price	float	≥0	[required] 2 decimals; base currency price; it is equal with bc_regular_price if the product has no discounts assigned, or smaller if it has discounts
bc_regular_price	float	≥0	[required] 2 decimals; base currency regular price
bc_aq_price	float	≥0	[optional] 2 decimals; The acquisition price of the product, in base currency
alt_prices	object	–	[optional] If the shop sells in many currencies, not only in the base currency, what are the prices for each currency. Object with key value pairs, “currency”: price. Add only the currencies the product is salable for. Currency must be in ISO 4217 format (3 letters). If alt_prices has a currency, alt_regular_prices must also have that currency, and vice-versa. (handling multi-currency feeds is a future development)
alt_regular_prices	object	–	[optional] If the shop sells in many currencies, not only in the base currency, what are the prices for each currency. Object with key value pairs, “currency”:price. Add only the currencies the product is saleable for. Currency must be in ISO 4217 format (3 letters). If alt_prices has a currency, alt_regular_prices must also have that currency, and vice-versa. (handling multi-currency feeds is a future development)
categories	array	–	[required] Array with category_eids. The highest possible level category. (e.g if you have category Office, with subcategories Multifunctionals, Printers, Scanners, and the product MultiA is in Multifunctionals and Printers, you should send only multifunctionals_id, printers_id, but not office_id. Also if you have the product in the category Promotion (same level with Office, or in another category tree), you should send promotion_id too.
brand	string	255	[required] Keep case consistency for accurate reports
product_tags	array	–	[optional] Tags that you may have for a product
custom_attributes	object	–	[optional] Key value pairs. Key should be a snake_case string and the value string, int or float. All the products should have the same custom_attributes list
variant_options	object	–	[optional] Required for variants. Object with key value pairs option:value. (e.g base product Tshirt, and variants with: color:green, size:M; color:red, size:M, color:red, size: S). Both option and value should be strings.

Why do we ask this info?

• parent_product_eid – we will make reports based on products composition
• sku – maybe you want to send product_eid as the ids from your tables, which definitely ensure uniqueness, but you also want to see in reports the code of the product you see on your site; this is optional and has no uniqueness restriction, just a helpful info for you
• title – in order to see the names in the reports
• url – Further development
• img – Further development
• description – Further development
• date_added – Further development (new product)
• in_stock – Further development
• bc_price – you will see profitability reports
• bc_regular_price – you will see profitability reports, discount effects
• bc_aq_price – for profitability reports
• alt_prices – for reports per currencies, if the shop uses many currencies; (this is a future development)
• categories – for reports per product category
• brand – for reports per brand
• product_tags – this will be used in the Buying Habits table for filtering
• gender – for reports per customers gender vs products gender
• variant_options – for reports per products and variations
• custom_attributes – you can see many reports per each custom_attribute

For products we use the following terminology: simple product, configurable product and variants.

Simple product: has parent_id: null, can have variant_options, and they will be used just as description purposes, as it doesn’t have siblings to differentiate from.

Configurable product: has parent_id: null, does not have variant_options. If you don’t have a price on the configurable product, only on its variants, send the following fields with 0: bc_price, bc_regular_price, bc_aq_price, and skip the following fields (or send them with null): alt_prices, alt_regular_prices. You need to send the categories.

Variant of a configurable product: must have parent_id, may have variant_options. We allow a variant to have no variant_options, as long as they have unique product_eids, because we know it may be hard for some shops to export in this format (maybe they have the differentiator in the title field).

We hope this model will accommodate the shops with less structured products but also the ones with good parent-variant relations. This model also leads to an import process in 2 steps. First the configurable products and the simple products then the variants. We throw error if a parent is not found. The categories of the parent will be used only for automatically created products, found in order feed but not also in DB. price and acquisition_price will not be used from parent, as they may differ from variant to variant more than the categories can vary.

Example Feed:

{
    "products": [
        {
            "product_eid": "Unique product identifier",
            "parent_product_eid": "Unique product identifier of the parent",
            "sku": "J14-29X",
            "title": "Product title",
            "url": "https://domain.com/product_name",
            "img": "Thumbnail image URL (including http://)",
            "description": "description here",
            "date_added": "2018-09-23",
            "in_stock": 1, // 1 if the product is currently in stock, 0 otherwise
            "bc_price": 123.45, // Product price in base currency
            "bc_regular_price": 199.99, // Product price prior to discount
            "bc_aq_price": 100.00 //Acquisition price in base currency
            "alt_prices": {
                "EUR": 89.95
            },
            "alt_regular_prices": {
                "EUR": 219.95
            },
            "categories": [
                "cat01Id",
                "cat05Id"
            ],
            "brand": "The brand of the product",
            "custom_attributes": {
                "price_category": 2,
                "price_category_name": "Silver"
            },
            "variant_options": {
                "color": "blue",
                "size": "36"
            }
        }
    ]
}

Order data structure

Order

Attribute	Type	Limits	Description
order_eid	string	255	[required] The identifier you have defined for the order
customer_eid	string	255	[required – you must have at least one of customer_eid or customer_email] The identifier you have defined for the customer. If guest send the customer_email
customer_email	string	255	[required – you must have at least one of customer_eid or customer_email] The email of the customer if it’s a guest. You don’t have to send if you have customer_eid, but it’s ok to have it for double checking. If you have the “Merge Customers with Guests” setting enabled, we will assign this order to the customer with this email, even if the customer EID was not specified on the order itself. If the setting is not enabled (default), guest orders are assigned to a “guest” customer, created automatically with this email, and considered a completely different customer (all guest orders with a specific email to the same “guest” customer with that email)
status	string	255	[required] The status of your order as you have it in DB (but to reflect the real status of the order)
placed_at	string	19	[required] YYYY-MM-dd HH:mm:ss (UTC); dateTime when the order was placed
last_modified_at	string	19	[required] YYYY-MM-dd HH:mm:ss (UTC); dateTime when the order was last time modified (e.g status, total, orderLines, …)
currency	string	3	[required] The selling currency (for now, this should be in base_currency), in which the transaction was performed; ISO 4217 format (3 letters)
grand_total	float	>= 0	[required] in selling currency (for now, this should be in base_currency; handling multi-currency feeds is a future development); Total amount paid by the user, including shipping and discounts, incl. taxes; grand_total = sum(order.products.total) + order.shipping – order.order_discount
bc_grand_total	float	>= 0	[required] grand_total in base_currency; Total amount paid by the user, including shipping and discounts;
shipping	float	>= 0	[required] shipping in selling currency (for now, this should be in base_currency), incl. taxes;
bc_shipping	float	>= 0	[required] in base_currency;
order_discount	float	>= 0	[required] discount at order level, in selling currency (for now, this should be in base_currency), incl. taxes; If there is a discount per order (e.g. 10EUR for this order, no matter what you buy); (!) The product discounts should not be included within the order discount. This is an EXTRA discount, applied on top of the sum of discounts at Order Line level, and should be used for Order-level discounts, such as vouchers, a fixed cart promotion, deduction of shipping costs etc.
bc_order_discount	float	>= 0	[required] in base_currency;
products	array	–	[required] Array of OrderLine objects
payment_type	string	255	[optional] the payment method used for this order, any value is accepted, and you will see reports per payment type (e.g. card, cash, paypal, money order, visa, mastercard, amex,…)
shipping_provider	string	255	[optional] The shipping provider
custom_attributes	object	–	[optional] Key value pairs. Key should be a snake_case string and the value string, int or float. All the orders should have the same custom_attributes list
billing_address	object	–	[optional] An object containing the details of an address. Must be an Address type object as defined below.
shipping_address	object	–	[optional] An object containing the details of an address. Must be an OrderAddress type object as defined below.

Why do we ask this info?

• customer_email – we will make reports on guests
• last_modified_at – for accurate NPS invitation timing; we assume the last modification implies the status change
• payment_type – we will make reports on payment type (NPS/payment type, RFM/payment type)
• shipping_provider – we will make reports on shipping provider
• custom_attributes – if you have some custom information per order, and you want to see reports with them

OrderLine

Attribute	Type	Limits	Description
product_eid	string	255	[required] The identifier you have defined for the product (the variation id)
parent_product_eid	string	255	[optional] The identifier you have defined for the parent product; null if it does not exist
status	string	255	[required] The status of this order_line as you have it in DB. You should also think of a mapping to our predefined statuses: pending, delivered, returned, customer_canceled, shop_canceled. This mapping will be configured in the Settings section of the app, after import, in feed you have to send your original statuses.
placed_at	string	19	[required] YYYY-MM-dd HH:mm:ss (UTC); dateTime when the orderLine was created; say you have an order with 1 orderLine with qty=2, which has the same placed_at with the order; after 1 day the customer returns 1 product; so this first orderLine will have the same placed_at, but last_modified_at should increase with 1 day, and qty=1; and a new orderLine should appear with the same product_eid, qty = 1, status=”returned” (or what status do you have for this situation) and placed_at = last_modified_at = (order.placed_at+1 day)
last_modified_at	string	19	[required] YYYY-MM-dd HH:mm:ss (UTC); dateTime when the orderLine was last modified
qty	float	> 0	[required] Quantity
product_price	float	>= 0	[optional] (for now, this should be in base_currency) price per unit, excl. taxes; if you send this field you should send the bc_product_price too
total	float	>= 0	[required] (for now, this should be in base_currency) qty * product_price + taxes – discount
tax	float	>= 0	[optional] (for now, this should be in base_currency) taxes for all units, before applying the discount; if you send this field you should send the bc_tax too
discount	float	>= 0	[optional] discounts awarded for products, in selling currency (for now, this should be in base_currency), incl. taxes; positive number; discount = (product.alt_regular_price.[order.currency] – product.alt_price.[order.currency]) * qty; if you send this field you should send the bc_discount too
bc_product_price	float	>= 0	[optional] product_price in base currency; if you send this field you should send the product_price too
bc_total	float	>= 0	[required] total in base currency
bc_tax	float	>= 0	[optional] tax in base currency; if you send this field you should send the tax too
bc_discount	float	>= 0	[optional] discount in base currency; if you send this field you should send the discount too
bc_unit_aq_price	float	>= 0	[optional] in base currency; the acquisition price of the product you sold in this order line, incl. tax

Why do we ask this info?

• parent_product_eid – in case we don’t find the product_eid in feed, but we find the parent_product_eid, we know that this was a variant of that product that maybe now is not available anymore; but this way the reports per category will be accurate
• status – in case of a partial canceling or returning of the order, we want to know exactly what products and in which quantity they have been sold, canceled or returned; say order A, status delivered, with products: product B, qty = 3, status delivered. After 1 day the customer returns product B, qty = 1. When updating on our system we want: order A, status partial_returned, with products: product B, qty = 2, status delivered; and again product B, qty = 1, status returned. Now we know we sold just 2 products B, and we have 1 product B returned.
• placed_at, modified_at – so we can reconstruct the timeline of the order from creation until settling
• discount – it is a useful information for reports; if not provided we can compute it with the formula above, but only for new orders; for closed orders we will let it 0, because we don’t know the historical discount that product had;
• bc_unit_aq_price – if you provide this value the profitability reports will be the most accurate ones; else we will try to guess the bc_unit_aq_price from product bc_aq_price, or category margin, or even ShopAvgMargin setting, or not guessing at all if we found no data on this path;

OrderAddress

Attribute	Type	Limits	Description
type	string	1	[required] “1” for billing address, “2” for shipping address.
first_name	string	255	[optional] The first name of the customer.
last_name	string	255	[optional] The last name of the customer.
address1	string	255	[required] The address of the customer.
address2	string	255	[optional] The second address line of the customer. null if it does not exist.
phone	string	12	[optional] Phone number in the international format: +22111222111. Other formats will not be accepted.
city	string	255	[required] City name, as found in your database.
zip	string	10	[optional] Zip code, as found in your database.
province	string	255	[optional] Province, as found in your database.
country	string	255	[required] Country, as found in your database.
company	string	255	[optional] Company name. null if it does not exist.
latitude	float		[optional] The latitude of the customer’s location. null if it does not exist.
longitude	float		[optional] The longitude of the customer’s location. null if it does not exist.
name	string	255	[optional] The full name of the customer.
country_code	string	2	[required] ISO country code.
province_code	string	10	[optional] Province ISO Code. null if it does not exist.

Example Feed:

{
    "orders": [
        {
            "order_eid": "Unique order identifier",
            "customer_eid": "Unique customer identifier; empty if guest checkout",
            "customer_email": "email for guest customer; empty non-guest",
            "status": "pending",
            "placed_at": "2018-01-25 16:34:12",
            "last_modified_at": "2018-01-25 16:34:12",
            "currency": "EUR", 
            "grand_total": 32.65, // Total amount paid by the user, including shipping and discounts; e.g. 20.50 - 0.19 + 12.34
            "shipping": 12.34, // Shipping amount
            "order_discount": 0.19 // discount per order, extra than sum of order lines discount (products discount)
            "bc_grand_total": 8.08, // in base currency
            "bc_shipping": 3.08, // in base currency
            "bc_order_discount": 0.05, // in base currency
            "payment_type": "card",
            "shipping_provider": "FedEx",
            "products": [
                {
                    "product_eid": "456001",
                    "parent_product_eid": "456000",
                    "status": "pending",
                    "placed_at": "2018-01-25 16:34:12",
                    "last_modified_at": "2018-01-25 16:34:12",
                    "qty": 2.0, // Number of items purchased of this type
                    "total": 20.50, // Total amount paid for items of this type = qty * item_price; in this case, unit price is 10.25
                    "discount": 1.10 // Total amount of the order line (product) discount = qty * item_discount; in this case, unit discount is 0.55
                    "bc_total": 5.12,
                    "bc_discount": 2.75,
                    "bc_unit_aq_price": 0.95,
                    "bc_profit": 3.22
                }
            ],
            "custom_attributes": {},
            "billing_address":{
               "type":"1",
               "first_name":"Iris",
               "address1":"7292 Dictum Av.",
               "phone":"+4927096392",
               "city":"San Antonio",
               "zip":"47096",
               "province":null,
               "country":"United States",
               "last_name":"Watson",
               "address2": null,
               "company":null,
               "latitude":null,
               "longitude":null,
               "name":"Iris Watson",
               "country_code":"US",
               "province_code":null
            },
            "shipping_address":{
               "type":"2",
               "first_name":"Iris",
               "address1":"7292 Dictum Av.",
               "phone":"+4927096392",
               "city":"San Antonio",
               "zip":"47096",
               "province":null,
               "country":"United States",
               "last_name":"Watson",
               "address2": null,
               "company":null,
               "latitude":null,
               "longitude":null,
               "name":"Iris Watson",
               "country_code":"US",
               "province_code":null
            }
        }
    ]
}

Handling large feed files

If one (or more) of your files exceed 100 MB in size, we recommend to split them into multiple, smaller-sized files.

For any of the feed files, you should use a 0-based (consecutive) numbering system.
Eg: orders_0.json, orders_1.json, orders_2.json etc.

When setting the feed URLs within Reveal, just replace the index with a placeholder, namely {index}.
Eg: use orders_{index}.json for the above example. The system will automatically start with the first one (orders_0.json) and will iterate through consecutive-numbered files, one at a time, until the filename is not found anymore.

Technical details: to determine whether a file exists or not, a HEAD request is performed, and if a 404 (or 403) response status code is returned, the system concludes that the previous file is the last one.
⚠️ It is important that your system handles HEAD requests appropriately.

Optimizing daily import size & time

In order to speed up the daily import and optimize your outgoing bandwidth, we recommend you to output only relevant information within the feed files.

The initial import must contain all the information, but most of it becomes redundant for the daily imports, which could contain only information that has changed recently.

Reveal performs a differential update, meaning information that has been sent in the past and is not present anymore within the feeds, is kept as-is.

A rule of thumb for the daily import files would be to include only records created or updated within the last 5 days.

In order to protect your information, access to your feeds can be restricted. You can use one of the two methods presented below. You can even use both.

Security

IP Restriction

You can restrict access to the feed files by adding an IP restriction when serving the files. The IP list that you need to white-list may be found within the Settings section, Import Settings page, under the name of API Server IP.

Basic Authentication

You can password-protect your feed files, using the Basic Authentication method.
Once you define the user and password within your system, you can set them into Reveal, in the Settings section, Import Settings page, into the fields Basic Auth – User and Basic Auth – User.