Order data in BlueConic

This article offers guidelines for importing and using customer order data in BlueConic.

Importing order data into BlueConic

Importing order data into BlueConic typically means storing data in profiles properties and creating events on the profile timeline.

Order data model

Best practices for profile properties that hold order data

There are no out-of-box order profile properties. The following list offers a best practice for loading order data into profile properties.

Name ID Type Description
Order date/time (all) orderDateTimeAll Date time All order dates
Order date/time (first order) firstOrderDateTime Date time Date of the first order 
Order date/time (most recent) orderDateTime Date time Date of the last order 
Order ID (most recent) orderID Text For anonymous orders, the Order ID can be used as a unique identifier.
Ordered product categories (most recent order) orderedProductCategories Text Product categories of the most recent order
Ordered product categories (all) orderedProductCategoriesAll Text Product categories for all orders 

 

Timeline 

The order event type can be loaded into BlueConic using this URL: https://plugins.blueconic.net/eventtype_order/index.xml.

While the properties of the default type can not be changed, the default order event type can be extended. Please contact the Solutions team for assistance if this is needed.

Property Type Description
event_id TEXT Event ID generated by BlueConic or the connection
order_id TEXT Order ID
order_date DATE Date of the order
affiliation TEXT The store or affiliation where the transaction occurred
total_revenue DECIMAL Total revenue including shipping and tax, discounts, and promotions and coupons
quantity NUMBER Total number of products ordered
revenue DECIMAL Revenue excluding shipping and tax, including promotions and coupons
shipping DECIMAL Shipping cost in absolute currency
tax DECIMAL Tax in absolute currency
discount DECIMAL Discount in absolute currency
coupon TEXT Coupons used
promotion TEXT Promotions that are active for this order
currency TEXT Currency used for the transaction
tag TEXT Tags to be used for filtering and searching
product ARRAY All order lines in the order
product.id TEXT Internal product ID of product ordered
product.sku TEXT Product SKU
product.upc TEXT Product UPC
product.category TEXT Product Category
product.name TEXT Name of the product
product.variant TEXT Variant of the product (e.g. white, black, 32 GB, etc.)
product.brand TEXT Brand of the product
product.listprice DECIMAL List Price of order line (list price * number of items)
product.netprice DECIMAL Net price paid for this order line
product.quantity NUMBER Number of products ordered
product.coupon TEXT Coupon used for this order line
product.promotion TEXT Promotion used for this product (e.g. free shipping or 10% percent off)
product.tag TEXT Product tags to be used for filtering and searching
product.shippingcost DECIMAL Shipping cost for the product in absolute currency
product.shippingdate DATE Expected date of shipment of this order line
product.deliverydate DATE Expected date of delivery of this order line
product.position TEXT Position of the product (for example which level in store, or place in search or page)
product.url TEXT Webpage that gives more information about the product
product.image_url TEXT URL that shows an image of the product

 

Guidelines for importing order data into BlueConic

File requirements

  • CSV
  • UTF-8 encoded
  • RFC 4180
    • If fields are not enclosed with double quotes, then double quotes may not appear inside the fields.
    • Each value that can contain a double quote, a delimited character, a carriage return/line feed, or a leading/trailing space has to be enclosed in double quotes.
    • If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote.

Order event uniqueness

To avoid creating duplicate order events when importing the same order feed multiple times, it is essential that each order event be assigned a unique identifier. Ideally, each order in the order feed holds a unique order identifier. You can then map that unique order identifier to the order event ID at the import connection. If that is not the case, and you need to for example combine data from multiple columns to create a unique id, a pre-processor plugin needs to be developed. Please contact the Solutions team if that is needed. 

Separate order header and order details file

In the ideal situation, a separate order header and order details file are delivered. The order header file holds order details at an aggregate level (total quantity, total revenue, etc.) as well as the PII associated with the order (name, address, email address, customer ID, etc.). The order details file holds a line for each ordered product, including the retail price, paid price, quantity, shipping date, etc. 

Example order header file

Order ID Order Date Total Revenue Total Quantity Email Address Address
12345 11-04-2018 15:16:17 500.00 3 customer_email@email.com 123 Boston Ave.
54321 11-06-2018 13:18:12 200.00 user_email@email.com 123 Hello World St.

 

Example order details file

Order ID Revenue Product Name Product Category Product Brand
12345 100.00 Product A Tech Brand A
12345 200.00 Product B Fashion Brand B
12345 200.00 Product C Entertainment Brand C
56432 600.00 Product D Tech Brand D

 

In the example above, products A, B, and C will be associated with the order header line for customer_email@email.com because they have the Order ID 12345. This will create a single order event that contains all products that were a part of that order. Since product D has a different order ID, this will be associated with a different order that shares the same order ID value.

Single file

In cases where the order data is delivered in a single file, the following should be taken into account

  • For each ordered product, there has to be a separate line in the CSV file.
  • Order lines have to be grouped by order ID in the CSV file.

See the best practices for exchanging data with BlueConic via CSV files.

Example single order file

Order ID Order Date Revenue Product Name Product Category Product Brand  Quantity  Email Address  Address
12345 11-04-2018 15:16:17 100.00 Product A Tech Brand A 1 customer_email@email.com 123 Boston Ave.
12345 11-04-2018 15:16:17 200.00 Product B Fashion Brand B 1 customer_email@email.com 123 Boston Ave.
12345 11-04-2018 15:16:17 200.00 Product C Entertainment Brand C customer_email@email.com 123 Boston Ave. 

A custom pre-processor plugin needs to be created to roll up the order lines into an order. Please contact the Solutions team when this is needed.

Activating order data in BlueConic

Creating customer segments based on order data

Only the data that has been loaded into profile properties can be used for segmentation. Example segments that can be based off of this data (if using best practices profile properties described above) might include:

  • All profiles that have ordered in the last 6 months
  • All profiles that didn't order in the last 6 months but did order in the 6 months prior to that
  • All profiles that ordered products in product category X

Order events that are stored in the profile timeline cannot be used directly for segmentation. They can however be used to train AI Workbench models and to create derivative profile properties such as RFM and CLV values that then can be used for segmentation.

Using order data in AI Workbench

The following models are available out-of-box in the BlueConic AI Workbench, and will leverage order events stored on the profile's timeline:

  • CLV
    • Customer lifetime modeling is a way to describe a customers' behavior using a customers order history. Based on the order history, you can extrapolate what you expect customers to spend on you for a given timeframe. This is done by first reviewing the same scores as RFM models do, and then training a model with the behavior that you specific customers exhibit. 

      You end up with a Customer Lifetime Value, which is "the value the customer is expected to add over the next period." You can set the period as you like, but a year is pretty common. Based on the data, you also get a probability score that the customer has not yet churned, and an expected number of purchases for a specific period. Learn more about the using the CLV notebook in AI Workbench.

  • RFM
    • RFM is a way to segment buyers into different groups. For each variable (Recency, Frequency, Monetary value) customers are divided into 3 to 5 groups (low frequency, mid frequency, high frequency for example). This results in 3x3x3 to 5x5x5 different variations in scores. The idea is that someone who scores 5,5,5 for R,F,M is your best customer, a 1,1,1 your worst. You can also recognize frequent low value buyers (for example 5,5,1) or other groups. This is a common tool for retail marketers and gives them a recognizable way to do their segmentation. Learn more about using the RFM notebook in AI Workbench.