Article sections

    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
    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
    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
    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
    • categories – for reports per product category
    • brand – for reports per brand
    • 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": {
                    "culoare": "blu",
                    "marime": "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, in which the transaction was performed; ISO 4217 format (3 letters)
    grand_total float >= 0 [required] in selling currency; 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, incl. taxes;
    bc_shipping float >= 0 [required] in base_currency;
    order_discount float >= 0 [required] discount at order level, in selling 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

    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] Price per unit, excl. taxes
    total float >= 0 [required]
    qty * product_price + taxes – discount
    tax float >= 0 [optional] taxes for all units, before applying the discount
    discount float >= 0 [optional] discounts awarded for products, in selling currency, incl. taxes; positive number; discount = (product.alt_regular_price.[order.currency] – product.alt_price.[order.currency]) * qty;
    bc_product_price float >= 0 [optional] product_price in base currency
    bc_total float >= 0 [required] total in base currency
    bc_tax float >= 0 [optional] tax in base currency
    bc_discount float >= 0 [optional] discount in base currency
    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;

    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”: {}
            }
        ]
    }

    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.