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

    AttributeTypeLimitsDescription
    customer_eidstring255[required] The identifier you have defined for the customer
    emailstring255[required] The email of the customer
    date_registeredstring10[required] YYYY-MM-DD (UTC)
    first_namestring255[optional]
    last_namestring255[optional]
    countrystring255[optional]
    regionstring255[optional]
    citystring255[optional]
    genderstring1[optional] m, f, o
    yobint1900-2010[optional] Year of birth
    accepts_marketingint0/1[optional] 1 if marketing communication is allowed, 0 otherwise
    custom_attributesobject[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. customer_eid@client_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

    AttributeTypeLimitsDescription
    category_eidstring255[required] The identifier you have defined for the category
    parent_category_eidstring255[required] The identifier for the parent category; null for root category
    levelint0-255[required] 0 means root category
    namestring255[required]
    marginfloat≥0[optional] avg margin (as percent) the shop makes per category
    urlstring1024[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

    AttributeTypeLimitsDescription
    product_eidstring255[required] The identifier you have defined for the product
    parent_product_eidstring255[required] The identifier you have defined for the parent product, if this is a variant; null if this is the parent product itself
    skustring255[optional] The SKU of the product
    titlestring255[required] The name of the product
    urlstring1024[optional] The product URL
    imgstring1024[optional] A product Image URL
    descriptionstring1024[optional] Truncated to 1024 characters
    date_addedstring10[optional] YYYY-MM-dd
    in_stockint0-1[required]
    bc_pricefloat≥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_pricefloat≥0[required] 2 decimals; base currency regular price
    bc_aq_pricefloat≥0[optional] 2 decimals; The acquisition price of the product, in base currency
    alt_pricesobject[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_pricesobject[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)
    categoriesarray[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.
    brandstring255[required] Keep case consistency for accurate reports
    product_tagsarray[optional] Tags that you may have for a product
    custom_attributesobject[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_optionsobject[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

    AttributeTypeLimitsDescription
    order_eidstring255[required] The identifier you have defined for the order
    customer_eidstring255[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_emailstring255[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)
    statusstring255[required] The status of your order as you have it in DB (but to reflect the real status of the order)
    placed_atstring19[required] YYYY-MM-dd HH:mm:ss (UTC); dateTime when the order was placed
    last_modified_atstring19[required] YYYY-MM-dd HH:mm:ss (UTC); dateTime when the order was last time modified (e.g status, total, orderLines, …)
    currencystring3[required] The selling currency (for now, this should be in base_currency), in which the transaction was performed; ISO 4217 format (3 letters)
    grand_totalfloat>= 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_totalfloat>= 0[required] grand_total in base_currency; Total amount paid by the user, including shipping and discounts;
    shippingfloat>= 0[required] shipping in selling currency (for now, this should be in base_currency), incl. taxes;
    bc_shippingfloat >= 0[required] in base_currency;
    order_discountfloat >= 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_discountfloat >= 0[required] in base_currency;
    productsarray[required] Array of OrderLine objects
    payment_typestring255[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_providerstring255[optional] The shipping provider
    custom_attributesobject[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_addressobject[optional] An object containing the details of an address. Must be an Address type object as defined below.
    shipping_addressobject[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

    AttributeTypeLimitsDescription
    product_eidstring255[required] The identifier you have defined for the
    product (the variation id)
    parent_product_eidstring255[optional] The identifier you have defined for the parent product; null if it does not exist
    statusstring255[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_atstring19[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_atstring19[required] YYYY-MM-dd HH:mm:ss (UTC); dateTime when the orderLine was last modified
    qtyfloat> 0[required] Quantity
    product_pricefloat>= 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
    totalfloat>= 0[required] (for now, this should be in base_currency)
    qty * product_price + taxes – discount
    taxfloat>= 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
    discountfloat>= 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_pricefloat>= 0[optional] product_price in base currency; if you send this field you should send the product_price too
    bc_totalfloat>= 0[required] total in base currency
    bc_taxfloat>= 0[optional] tax in base currency; if you send this field you should send the tax too
    bc_discountfloat>= 0[optional] discount in base currency; if you send this field you should send the discount too
    bc_unit_aq_pricefloat>= 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

    AttributeTypeLimitsDescription
    typestring1[required] “1” for billing address, “2” for shipping address.
    first_namestring255[optional] The first name of the customer.
    last_namestring255[optional] The last name of the customer.
    address1string255[required] The address of the customer.
    address2string255[optional] The second address line of the customer. null if it does not exist.
    phonestring12[optional] Phone number in the international format: +22111222111. Other formats will not be accepted.
    citystring255[required] City name, as found in your database.
    zipstring10[optional] Zip code, as found in your database.
    provincestring255[optional] Province, as found in your database.
    countrystring255[required] Country, as found in your database.
    companystring255[optional] Company name. null if it does not exist.
    latitudefloat[optional] The latitude of the customer’s location. null if it does not exist.
    longitudefloat[optional] The longitude of the customer’s location. null if it does not exist.
    namestring255[optional] The full name of the customer.
    country_codestring2[required] ISO country code.
    province_codestring10[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.

    Was this post helpful?