Offline Imports

When it is not possible to establish a real-time data stream from a source, such as an offline system, historic orders or certain CRM providers, Velocidi supports data onboarding through secure file uploads or specific third-party API integrations.

Quick-Start#

Upload Files#

You need to make your files available to the CDP prior to importing them. We recommend using the S3 bucket dedicated to your CDP. You can learn more about this process on the Upload Files documentation page.

How to Import#

Offline imports can be configured using the Dashboard under Collect > Data Imports, by clicking the New Import button.

Dashboard Import list

From here, you can configure these types of imports:

caution

Before starting an import, make sure that:

  • Your files are properly encoded as UTF-8;
  • The IDs (user IDs, order IDs, product IDs...) in the imported files match the ones sent by events;
  • Required fields are present in all lines;
  • The fields have the correct keys (case sensitive).

Orders#

Export your CRM orders and import them into the CDP.

Use Cases#

Importing orders into the CDP has 3 main use cases, as described below.

Bootstrap Your System with Historic Order Data#

Importing orders plays an important role when bootstrapping a system. While the Velocidi CDP builds user profiles with attributes produced from the users' interaction with the website, there's usually a large amount of information businesses have on orders that were made before events started being collected. These orders can add valuable information to produce user attributes. The attributes that benefit the most from a bootstrap from past orders are the ones related to next purchase predictions, RFM and lifetime value. The attributes related to the likelihood to buy are produced using different types of events from the users' interaction with the website (besides the actual purchases), so they don't benefit much from an import of orders previously unseen in the system.

Ensure the Orders Used by the CDP Are in Sync with Your CRM Orders#

Orders can change their state (for example, becoming canceled or refunded) without an explicit event from the user on the website. In those scenarios, having a periodic import of orders from your CRM backend can help the Velocidi CDP have a more accurate view of existing orders.

Complement Order Information Collected by Tracking Events with Details Private to the CRM Backend#

Sometimes, the data that is available when producing events to be collected by the Velocidi CDP lacks some details that are private to the CRM backend, and are therefore not directly accessible by the website. Typical scenarios are information missing from specific line items (like product-related data) or incomplete payment information due to actions that happen after the event is triggered. To make sure that the Velocidi CDP has access to relevant order details, even those that are private to the CRM backend, it's helpful to setup a periodic import of orders.

Formats#

We currently have support for the following 1st and 3rd party formats:

Supported schemas

Velocidi CSV#

This format is intended for CSV imports where each line is a Velocidi's Order/LineItem objects serialized to CSV (Example file).

Relevant notes:

  • The export file should be encoded using UTF-8.
  • All Line items for the same Order must be listed sequentially;
  • Given an order with an arbitrary number of rows, only the first row is used to extract Order details and all rows are used to extract Line item details. This means that Orders with more than 1 Line item do not need to populate the order columns for all columns and can be left empty.
  • If the provided userId is not hashed (E.g. idType=email), you can configure the import to hash it (to a idType=email_sha256) beforehand.
ColumnRequiredTypeDescriptionDetailsExample
orderIdRequiredStringOrder identifierMust be unique by Orderor123
userIdTypeRequiredStringThe type of user identifier (e.g. "email")email
userIdRequiredStringThe user identifierjohn@email.com
currencyRequiredStringThe currency used in monetary valuesCheck the supported currenciesEUR
totalRequiredDoubleTotal value of the order with all discounts already considered, including subtotal, tax and shippingtotal = subtotal + tax + shipping102.5
subtotalRequiredDoubleThe value of the order, when tax and shipping are excludedsubtotal = total - tax - shipping85
taxRequiredDoubleThe total applied tax value for the order11.5
shippingRequiredDoubleThe total shipping costs for the order6
discountRequiredDoubleThe total discounted value for the order5
refundRequiredDoubleThe total refunded value for the order0
paymentMethodStringThe payment methodVisa
shippingMethodStringThe shipping methodUPS
shippingCountryStringThe destination countryFrance
promotionsList of StringsVouchers applied in the purchase.Strings separated by ;WINTERSALE
createdAtRequiredDateTimeWhen the order was createdDate format: ISO-86012020-09-16T10:38:06+0100
lastUpdatedDateTimeWhen the order was last updatedDate format: ISO-86012020-09-16T20:13:31+0100
lineItem_idStringA unique identifier for the line item within the orderli125zc
lineItem_productIdRequiredStringProduct identifierp125zc
lineItem_itemGroupIdStringIdentifier for a group of products that come in different versions (variants)p125
lineItem_nameStringProduct nameMy Product
lineItem_skuRequiredStringProduct SKUp125zc-5
lineItem_brandStringProduct brandNike
lineItem_categoryStringProduct categoryMen > Shirts > Short Sleeve Shirts
lineItem_categoryListList of StringsA hierarchically sorted list of the sub-categories that this product belongs to. The first item on this list should be the most general sub-category, while the last should be the most specific. Any item on this list should correspond to a sub-category of the item immediately preceding it.Strings separated by ;Men;Shirts;Short Sleeve Shirts
lineItem_currencyRequiredStringThe currency used in monetary valuesCheck the supported currenciesEUR
lineItem_totalRequiredDoubleTotal value of the line item with all discounts already considered, including subtotal, tax and shippingtotal = subtotal + tax + shipping102.5
lineItem_subtotalRequiredDoubleThe value of the line item, when tax and shipping are excludedsubtotal = total - tax - shipping85
lineItem_taxRequiredDoubleThe total applied tax value for the line item11.5
lineItem_shippingRequiredDoubleThe total shipping costs for the line item6
lineItem_discountRequiredDoubleThe total discounted value for the line item5
lineItem_refundRequiredDoubleThe total refunded value for the line item0
lineItem_quantityRequiredIntNumber of purchased items1
lineItem_promotionsList of StringsVouchers applied in the purchase or displayed together with the productStrings separated by ;WINTERSALE
lineItem_adultBooleanWhether the product includes sexually suggestive content.Must be true or false. Defaults to falsefalse
lineItem_subscriptionDurationLongIf the purchase relates to a subscription, captures the subscription duration, in milliseconds.15552000000
lineItem_sizeStringProduct sizeS
lineItem_sizeSystemStringCountry of the size system used by your product.EU
lineItem_statusRequiredStringLine item status.
  • placed the order has been placed but the item is yet to be paid
  • paid the item has been paid but not yet delivered
  • fulfilled the item has been paid and delivered
  • refunded the item has been refunded
  • cancelled the order has been modified to exclude this line item
  • Supported status: placed, paid, fulfilled, refunded, cancelledfulfilled

    There's an example file available, showing how each field can be used.

    Shopify CSV#

    Velocidi supports CSV order exports from Shopify. The documentation for generating a Shopify export can be found here. Please ensure you select Export orders to export all order information, instead of Export transaction histories, which only exports the transactions.

    Relevant notes:

    • Shopify uses the Name column to distinguish between different orders inside the CSV file, so it must be unique per Order;
    • All Line items for the same Order must be listed sequentially;
    • Given an order with an arbitrary number of rows, only the first row is used to extract Order details and all rows are used to extract Line item details. This means that Orders with more than 1 Line item do not need to populate the order columns for all columns and can be left empty.
    • If the provided userId is not hashed (e.g. idType=email), you can configure the import to hash it (to a idType=email_sha256) beforehand.
    • It's important that the extracted order identifier from the shopify import matches the orderId provided via tracking events. When importing, you'll be able to select which field to use as the order identifier between the Id and Name columns.

    Velocidi will consider the following attributes while importing a Shopify file:

    ColumnRequiredTypeDetails
    NameRequiredStringMust be unique by Order and can be the same as Id (Can be used as an order identifier)
    EmailRequiredString
    IdRequiredString(Can be used as an order identifier)
    CurrencyRequiredStringCheck the supported currencies
    TotalRequiredDouble
    SubtotalRequiredDouble
    TaxesRequiredDouble
    Payment MethodString
    Financial StatusRequiredStringSupported status: authorized, paid, partially_paid, pending, refunded, partially_refunded, voided
    Refunded AmountRequiredDouble
    Discount AmountRequiredDouble
    Discount CodeString
    Shipping MethodString
    Shipping CountryString
    ShippingRequiredDouble
    Created atRequiredDateTimeDate format: yyyy-MM-dd HH:mm:ss Z
    Paid atDateTimeDate format: yyyy-MM-dd HH:mm:ss Z
    Fulfilled atDateTimeDate format: yyyy-MM-dd HH:mm:ss Z
    Lineitem quantityRequiredInt
    Lineitem nameRequiredString
    Lineitem priceRequiredDouble
    Lineitem discountRequiredDouble
    Lineitem fulfillment statusRequiredStringSupported status: pending, partial, restocked, fulfilled
    Lineitem skuRequiredString
    VendorString

    Customers#

    Export your CRM data and import them into the CDP.

    Use cases
    • Import CRM data to use in activations;
    • Complement the users' profile with their CRM data.

    We support importing CRM data from different Sources.

    Files Sources#

    The following formats are supported:

    Supported schemas

    The import will import every field as a custom User Attribute of the User Profile. Some notes:

    • The file must have at least one field which identifies the user (See User Identifiers). You can select which field contains the User ID as well as the associated User ID type.
    • By default, the attribute merge strategy is merge, which means the imported attributes will be merged with the existing attributes so that non-imported attributes will remain unaffected in the User Profile.
    • If importing a CSV file, all attributes will be imported as text. This restricts the ability to segment the user using numeric rules (e.g. numberOfOrders > 20 won't work). As a workaround, you can apply castTo transformations to known numeric fields. If importing a JSON-lines file, the attributes will use the original json types.

    E-goi Contacts Source#

    This is a specific third-party integration which loads customer data directly from an E-goi Contact List and imports it as attributes and user ids. If the contact information contains an email, it will be transformed into sha256 representation and merged into any existing profiles. The E-goi contact id is also imported and allows you to make use of the E-goi Destination which will make use of that ID to attach tags to activated users.

    To configure this import you should first select E-goi Contacts from the Source Type dropdown and then provide an E-goi API key with permissions to List All Contacts and Get My Account Info, as well as the List ID from which you want to download and import contact data.

    An API key can be created in you Integrations dashboard by choosing to Create a New Key and making sure the necessary permissions are selected:

    Where to create an API key in E-goi

    The list id can be taken from your E-goi dashboard and should be the numeric ID before its title:

    Where to retrieve the List ID in E-goi

    Notice that the customer import from this type of Source does not allow you to specify any transformations.

    E-goi Attribute Mapping#

    This source imports a subset of the available E-goi Contact fields, and maps them either to CDP user IDs or to user attributes. User attributes are mapped from E-goi original fields using the egoi.account<ACCOUNT-ID>.list<LIST-ID>.<E-GOI-ATTRIBUTE> format. <ACCOUNT-ID> is the E-goi account ID to which the provided API key refers to and is automatically determined, so you don't have to provide it explicitly. <LIST-ID> corresponds to the List ID provided in the form and <E-GOI-ATTRIBUTE> corresponds to the original Contact field this source will import into the CDP.

    For example, importing the status field from an E-goi Contact might result in egoi.account123.list321.status, if the API key used belongs to the E-goi account 123 and the user submitted 321 as the List to import.

    The following describes the fields that are consumed from the E-goi Contacts and how they are mapped into User Attributes:

    E-goi Contact FieldCDP User ID or AttributeDescription
    contact_idUser ID: Internal E-goi User ID TypeThe contact_id field is mapped into an internal user ID type whose main purpose is to be used by the E-goi Destination
    emailUser ID: Email SHA256The E-goi plain-text email field, if available, hashed and mapped to a SHA256 User ID
    statusAttribute: egoi.account<ACCOUNT-ID>.list<LIST-ID>.statusThe Contact status
    consentAttribute: egoi.account<ACCOUNT-ID>.list<LIST-ID>.consentThe Contact consent
    email_statusAttribute: egoi.account<ACCOUNT-ID>.list<LIST-ID>.emailStatusThe Contact email status, if an email is available
    cellphone_statusAttribute: egoi.account<ACCOUNT-ID>.list<LIST-ID>.cellphoneStatusThe Contact cellphone status, if a cellphone is available
    phone_statusAttribute: egoi.account<ACCOUNT-ID>.list<LIST-ID>.phoneStatusThe Contact phone status, if a phone is available

    Please refer to E-goi Contact API documentation for more information about the existing Contact fields and their possible values.

    Products#

    Export your products' information and import them into the CDP.

    Use cases
    • Complement tracking events.

    We have support for importing product information in the following formats:

    Importing product data allows for product-related tracking events to be complemented with the products' information even if the original event did not include them.

    For more information, see the Product Feed documentation.

    Advanced Configurations#

    File Sources#

    We have support for importing files with the following protocols: http, https, s3 and sftp.

    info

    For convenience, each Velocidi CDP system has a pre-created AWS S3 bucket that the client can use to upload files, to later be imported, securely. The credentials for these bucket will be supplied to the client during onboarding.

    caution

    In order to use a s3 bucket other than then provided one, or an authenticated sftp you should contact Velocidi support.

    Import Schedules#

    You can configure an offline import and specify the import periodicity in days.

    You can also verify the status of your periodically configured imports.

    Data Transformation#

    Data transformation allows you to rename fields and even map values without having to edit the original file.

    The most basic transformations are:

    • Rename field (e.g. renaming the field "number_of_orders" to "completedOrders". The "number_of_orders" field will disappear).
    • Copy field (e.g. copying the field "number_of_orders" to "completedOrders". The "number_of_orders" field will be unaffected).
    • New field (e.g. adding the field "completedOrders" with the value 0 set for all lines)
    • Delete field (e.g. Deletes the field "completedOrders")

    For the previous (Rename/Copy) transformations, you can also specify some value transformations to apply (i.e. transformations that will affect the values of the fields):

    Mapping Field Values#

    Accepts an exhaustive list of from -> to values to transform. This transformation will iterate over the provided mappings and if the field's values matches the from value, it will be replaced with the to value. If no value matches, the field value will remain unaltered.

    Example:
    Given the mappings "1" -> "one" and "2" -> "two":

    Original valueResulting valueDescription
    "1""one"Value is transformed
    "2""dois"Value is transformed
    "other""other"Value is unaltered because it doesn't match any of the from values
    11Value 1 is different from value "1" (number instead text) so it doesn't match the "1" -> "one" mapping
    note

    When dealing with CSV files, when this transformation takes place, all field values are interpreted as text.

    Extracting a Custom Timestamp#

    Allows parsing a non-standard DateTime format into a ISO 8601 format. Requires the user to specify the custom DateTime pattern which the file to import is using.

    Example:
    Given the pattern yyyy/MM/dd hh:mm, the value 2021/01/21 14:25 will be converted to the ISO 8601 format 2021-01-21T14:25:00Z.

    Casting Field Value Types#

    Allows casting values to a specific type. This is useful mostly for Customer Imports where the values are directly set as user attributes and the type (most commonly, whether we're dealing with text, or a number) is relevant (e.g. to create segments with numeric rules with that attribute). When dealing with Order Imports or Product Imports, casting the fields to the correct types isn't as import since there imports have well-defined schemas. These means that the schemas will perform the transformation implicitly if needed.

    The supported cast types are: string, integer, float and bool.

    Example:

    Original valueCast to StringCast to IntegerCast to FloatCast to bool
    "2.4""2.4"22.4true
    "0""0"00.0false
    "car""car"ErrorErrorError
    "false""false"ErrorErrortrue
    3.8"3.8"33.8true

    API Reference#

    For advanced, programmatic control, see the API reference for details.

    Last updated on