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.upc | TEXT | |
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
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 | 123 Boston Ave. | |
54321 | 11-06-2022 13:18:12 | 200.00 | 1 | 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 [email protected] 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.
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 | 123 Boston Ave. | |
12345 | 11-04-2022 15:16:17 | 200.00 | Product B | Fashion | Brand B | 1 | 123 Boston Ave. | |
12345 | 11-04-2022 15:16:17 | 200.00 | Product C | Entertainment | Brand C | 1 | 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.