Loading the Sales Items File

  • What happens if there is no order ID in the sales file?
    The loading fails. This is a mandatory field. The file must be corrected and sent again.

  • What happens if the order date is missing from the sales file?
    The loading fails. This is a mandatory field. The file must be corrected and sent again.

  • What happens if the customer ID is missing from the sales file?
    We ‘create’ a pseudo contact for this order (basically all such orders are attached to one pseudo contact). This contact would appear as ‘generated’ on the Smart Insight screens and could not be matched with any contact in the Emarsys application.

  • Could the above issue be fixed by re-sending the same order with the customer ID added?
    No. This would duplicate the order, one would be loaded for the ‘pseudo’ contact and the other to the ‘real’ contact. The file must be corrected by you and a new complete reload is done by us. You are intended to communicate the problematic file load to Support and we do the reload.

  • What happens if the customer ID is missing from Emarsys/the sales file? (customer ID = unique customer identifier field that we use to match orders with Emarsys contacts)
    An order with a customer ID missing from the Emarsys contact database will be loaded to the data warehouse and the related contact will be generated with only a customer ID and flagged as ‘generated’ in Customer Lifecycle. Using the ‘generated’ filter, this contact can be separated from the ones where the connection could be made between the orders file and contact DB.

  • Could the above issue be fixed by populating the missing customer IDs in the Emarsys application?
    Yes. If the unique customer identifier is added to a contact who previously was loaded as ‘generated’, they will turn to a ‘valid’ contact by the next data load.  

  • What columns are mandatory in the sales file?

    • order – The order ID of the order that this item was purchased in.
    • date – The date of the purchase.
    • customer – The ID of the customer who made the purchase. This must be contact data from the Emarsys database.
    • item – The ID of the product that was purchased.
    • c_sales_amount – The total amount of this sales item (i.e. unit price * quantity).
    • quantity – The number of items bought.
  • What date formats are accepted?
    YYYY-MM-DD is preferred. If HH:MM:(SS) is also sent, time will not be taken into account.

  • Can we have additional columns?
    It is possible to add custom fields to the sales file as long as the field name starts with prefix “c_” (e.g. c_brand, c_webshop). Please note that the order of the columns matters. Columns can be sent in any order, but it must always be the same. End of columns must not contain empty rows.

  • Is it possible to have the unit item price and the number of purchased items (quantity) in the files and would SI calculate the total sales amount based on these?
    No, the files we load contain the total price for items (e.g. 2 items cost 5 EUR altogether). No unit price is displayed and we don’t support such calculations in SI.

  • If there is an error in the sales file, will the good records still be loaded and the bad ones omitted from the load?
    No, the load process stops and nothing from the sales file will be loaded. The error has to be fixed by you and the file needs to be reloaded after the fix by us.

  • What happens with the contacts who have purchase(s) which are negative (have only refunded items) or zero (have only free items)?
    They will be loaded as leads (contacts who have not bought anything yet). If they are not identifiable (cannot be matched with any Emarsys contacts) they will be ‘generated leads’ with ‘Unknown’ lead source and ‘New lead’ lifecycle status.

  • What happens if there is no header in the sales (or products) file?
    If there is no header in the file, the first row will be lost as this will count as a header. Header is required.

  • What happens if we get a purchase without a product ID in the sales file? Do we load that as an unknown item?
    No, in this case the loading would fail. We only create ‘unknown products’ when the product ID for an order is missing from the product catalog. The file must be corrected and sent again.

Loading the Products File

  • How should the products file be sent?
    Either:

    • initial product catalog containing all products + daily deltas with the changes, or
    • full product catalog each day, or
    • daily feed only with the products that were ordered that day?

    You should upload your full product catalog every time, to ensure consistency.

  • What columns are mandatory in the products file?
    These columns are needed for SI, only the itemID is mandatory.

    • item (string) – The unique ID used in your system to identify an item. This is the ID that all data-collection and rendering scripts will refer to.
    • title (string) – The name of the product.
    • category (list of hierarchy levels) – Describes the category that a given item belongs to (e.g. Electronics>Phones>iPhone|Telecommunication>Smart Phones>iPhone). A hierarchy can consist of maximum 5 categories, but any number or hierarchies can be included.
  • What happens if there is no item (productID) in the products file?
    The loading fails. The file must be corrected and sent again.

  • What happens if the item is not unique within a single file?
    The loading fails. The file must be corrected and sent again.

  • What happens if multiple products files are loaded at once and the same item can be found in several files?
    The files are loaded one by one (in alphabetic order) thus the last occurrence of the item will overwrite all the previous ones.

  • What happens if there is no name for the product in the products file?
    The item will be loaded and displayed as Unknown product ‘product ID’ on the second graph of the Past Purchases screen. In this case, a product is created by us in the Emarsys database with only a product ID, no other data is provided for it. Note that if an item is not included in the product catalog, it will also be an unknown product.

  • What happens if there is no category in the products file?
    These items will be loaded, but they will not appear under any of the categories on the Past Purchases screen.

  • What happens if a product comes with an item (unique ID) already loaded to SI?
    If an item that was loaded earlier comes again in a new file, the old item will be overwritten by the new one (e.g. modified product category or correction in item name).

  • Which category levels can be displayed on the first chart of the Past Purchases, Top Products, Average Order Value and the Dashboard screen?
    By default, it is the first (top) level but any of the category hierarchy levels can be chosen for visualization. However, please note that the specified level cannot change from item to item (if it is set for category level 2, it applies to each and every item).

Refunds Handling

  • How should the refunds be included in the sales file?
    The returned orders should be represented in the sales file either on the same order ID as the original order or on a new ID (it doesn’t really matter) but with a negative sales amount.

  • Can we send the refunds in a separate file?
    As long as the file containing refunds is named similar to the sales file (sales_items*.csv and sales_items_refunds*.csv) and the content meets the requirements (contains the necessary columns with the right order and data), it can be sent as a separate file.

  • How do refunded orders affect customer lifecycle and customer turnover (total spent of a contact) calculations?
    The customer would not change lifecycle status by refunding (i.e. a first time buyer refunding would not go back to the lead status). This would only happen in the case of zero or negative purchases. The refund type order would not affect the number of purchases or last purchase date calculation. Moreover, the refund transaction won’t count as an engagement type so it is not calculated in the last engagement definition. The refund only affects the total turnover of the contact and thus their buyer status.

Loading Contacts

  • What happens if the custom registration date field is empty or doesn’t exist in the Emarsys application, and the customer has older order dates than his/her first registration date in the Emarsys application?
    The registration date of these contacts will be set to the same as the first order date in SI by us. This won’t affect the Emarsys side since we don’t write back to Emarsys from SI, so the custom registration date field will remain empty in the Emarsys application.

  • What happens if the custom registration date field is newer than the Created date/Date of first registration?
    The registration date of these contacts will be the date defined by Created date/Date of first registration. The custom registration field will not be applied in the last engagement in this case.

  • What happens if a contact has only click data but any other data of this contact is missing?
    The contact was deleted but its click data remained. This contact will appear on the Lead lifecycle page, its lifecycle status will depend on the date of click and the lead source will be Unknown.

Filters

  • What are ‘Total Amount spent’ and ‘Number of purchases’ on the Contacts/Segmentation page?
    These filters can be used to create segments like ‘total spend or number of purchases in a given time period and /or to a given product.’ These filters differ somehow from the two filters with similar names on Customer Lifecycle (Total lifetime spend and Total lifetime purchases) because while those work from contact data, these two work from purchase data. Those contacts who register and purchase on the same day are regarded as leads for the first day and would turn to customer only the second day.
    However, their orders would be loaded to the data warehouse on the first day already, while they are leads from the system’s point of view. This can result in slightly different numbers between the segments created with these similar filters.  

Duplication

  • What does the error message “duplicate key violates unique constraint "con_product_affinity_pkey"” in Settings/Data Load History mean?
    The "predict top categories" and the unique identifier (which identifies the purchase) fields has the same value for 2 contacts. You must amend the unique identifier for one of the contacts under the Other tab in the Emarsys application, or delete the unnecessary contact in order to make contact load possible.

Dashboard Notifications

  • Outdated purchase data
    No new purchases have been received in recent days; please check that you are sending us your daily order data. – If you had no purchases in the last 3 days.
  • ERFM scoring is not yet set up
    Your orders are not old enough; because the inactive recency limit is older than the oldest order, we could not yet set up the parameters as requested. – If the oldest order is within the inactive recency limit (e.g. if the limit is 365 days and 200 days is the oldest order).
  • Missing order data
    • You don’t have any order data; please start uploading it.
    • You do not have a sales file uploaded.
  • Inconsistent order data
    • The items in your orders have order IDs; please check that the order IDs in your files are consistent and do not refer to items but orders.
    • If items are sent with order IDs, it can occur that a contact becomes active right before the lead status and leaves out first-time buyer status resulting in distorted information about first-timers and active buyers.
  • Inconsistent conversion rates
    • Some of your potential customers are categorized as defecting or inactive; your ERFM parameters need to be updated.
    • When the defecting or inactive conversion rate is higher than the first timer or active one. Defecting or inactive recency limit must be reviewed.