This article offers guidelines for importing and using customer order data in BlueConic.
Importing order data into BlueConic
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.
|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|
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.
|event_id||TEXT||Event ID generated by BlueConic or the connection|
|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|
|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.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
- 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:firstname.lastname@example.org||123 Boston Ave.|
|54321||11-06-2018 13:18:email@example.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 firstname.lastname@example.org 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.
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.
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 Aemail@example.com||123 Boston Ave.|
|12345||11-04-2018 15:16:17||200.00||Product B||Fashion||Brand Bfirstname.lastname@example.org||123 Boston Ave.|
|12345||11-04-2018 15:16:17||200.00||Product C||Entertainment||Brand Cemail@example.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.
Please note that for these models to work, the "order_id" and "revenue" properties need to be populated for each order event on the timeline
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 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..
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
|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 2 files: a header and an item file. In case of a single file feed, a pre-processor is required to do the 'rollup'. Please contact the Solutions team for assistance if this is needed.
- The refunds are part of the regular order feed. In this case a pre-processor plugin is required to separate the regular from the refund orders. This pre-processor is activated at both the regular and the refund import goal. Please contact the Solutions team for assistance if this is needed.
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.