Help Center

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. Contact your Customer Success Manager for more information.

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 for example you need to combine data from multiple columns to create a unique id, a data processor plugin needs to be developed. Contact your Customer Success Manager for more information. 

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-2022 15:16:17 500.00 3 customer_email@email.com 123 Boston Ave.
54321 11-06-2022 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-2022 15:16:17 100.00 Product A Tech Brand A 1 customer_email@email.com 123 Boston Ave.
12345 11-04-2022 15:16:17 200.00 Product B Fashion Brand B 1 customer_email@email.com 123 Boston Ave.
12345 11-04-2022 15:16:17 200.00 Product C Entertainment Brand C customer_email@email.com 123 Boston Ave. 

A custom bundle events data processor plugin needs to be created to roll up the order lines into an order. Contact your Customer Success Manager for more information.

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 for 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. These models use order events stored on the profile's timeline. 

Note: In order for these models to work, the "order_id" and "revenue" properties need to be populated for each order event on the 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 time frame. 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 to calculate customer lifetime value using AI modeling.

  • 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 to calculate customer RFM scores..

Order refunds

An order refund describes a cancellation or return flow. Order refunds have to be imported as separate timeline events in BlueConic. The order refund event type can be loaded into BlueConic using this URL: https://plugins.blueconic.net/eventtype_order_refund/index.xml

Order refund model

Property Display Name  Type Description
event_id Event Identifier  TEXT Event ID generated by BlueConic
source_order_id Source Order  TEXT Source Order ID from the original order
status Refund Status  TEXT One of 'order_refund_requested', 'order_returned', 'order_refunded'
refund_id Refund Identifier TEXT Reference ID of a specific refund process
quantity Quantity NUMBER Number of items refunded
total_value Refund Value  DECIMAL Total value of refunded order
currency Currency  TEXT Currency used for the refund
product   - All products returned and refunded
product.id Product ID  TEXT Internal product ID of product ordered
product.sku Product SKU  TEXT Product SKU (https://en.wikipedia.org/wiki/Stock_keeping_unit)
product.upc Product UPC  TEXT Product UPC (https://en.wikipedia.org/wiki/Universal_Product_Code)
product.category Product Category  TEXT Product Category
product.name Product Name  TEXT Name of the product
product.variant Product Variant  TEXT Variant of the product (eg white, black, 32GB, etc.)
product.brand Product Brand  TEXT Brand of the product
product.netprice Product Net Price  DECIMAL Net price paid for this orderline
product.quantity Product Quantity  NUMBER Number of products ordered
product.url Product URL  TEXT Webpage that gives more information about the product
product.image_url Product Image URL  TEXT URL that shows an image of the product

 

Importing order refunds

A separate import goal has to be set up to import the order refund events into BlueConic. There are two ways to set this up:

  • There is a separate order refund feed, preferably consisting of two files: a header and an item file. In case of a single file feed, a data processor is required to do the 'rollup'. Contact your Customer Success Manager for more information.
  • The refunds are part of the regular order feed. In this case a data processor plugin is required to separate the regular from the refund orders. This data processor is activated at both the regular and the refund import goal. Contact your Customer Success Manager for more information.

Regardless of the way the refunds are exposed, the feed has to adhere to the "Guidelines for importing order data into BlueConic", as described above. 

Using refund order data in AI Workbench

Refund order data is automatically used in CLV and RFM calculations if the 'source_order_id' and 'total_revenue' are filled. The 'source_order_id' property needs to have the same values as the 'order_id' property of the regular order that was refunded.

Was this article helpful?
1 out of 1 found this helpful