A number of Distil’s features require knowledge of your Customers’ Purchase History. For example the Product Recommendations Enrichment suggests follow-on Purchases and finds Products that are commonly purchased together. This can be used to drive the Hyper-Personalised Marketing Destination as well as provide fuel for automated Analytics Newsfeed Cards such as Life Time Value (LTV) and the Daily, Weekly and Monthly performance reports.
Just a reminder that setting up Database Sync involves the three steps described below. If you haven't already done so, we'd suggest reading the article on Preparing for Database Sync first.
- Preparing for Database Sync - readying your infrastructure and applying best practices.
- Preparing your tables or views - this is covered in seperate articles for each Data Category:
- Customer data
- Product data
- Purchase History data - this article
- Content data
- Creating the Database Connection - creating the Connection in Distil
This section describes how to prepare tables or views in your database so that they are recognised as containing Purchase History data and can be used to sync into Distil.
Once you have set up your Database Connection, Distil will sync the available data once per day. As part of this sync activity, Distil will interrogate the schema for your chosen database. It looks for any tables or views that conform to the following criteria – any that do will be sync'd into Distil. If an Attribute is removed from the source tables or views, then Distil will keep any existing data (but not try to import any new data).
Table or View Naming Convention
For a Table or View to be identified as containing Purchase History data it must contain the words Distil, Purchase and History, in that order. Any combination of those words will get recognised, and case is not important. Some examples are:
- distilPurchaseHistory
- distil-purchase-history
- distil_purchase_history
- v_distil_purchase_history
Any table that does not conform to this naming convention will not be shown in the Distil interface, and the data will not be imported into Distil.
Unique IDs for Records
Each row in the Purchase History table must represent one order line. This means the combination of Customer ID, Order ID and Product ID must be unique. Additionally an Order ID can only belong to one Customer. If a table does not conform to this requirement then it will not appear in the Distil interface, and the data cannot be imported into Distil.
Required Fields
If any of the field marked as Required in the Core Purchase History Attributes table below are not present in the source table or view, then no data from the table will be imported into Distil.
Core Purchase History Attributes
It is typical to store Purchase History (Order) data using a number of tables; Orders, Line Items, Address for both billing and delivery, postage and packing and so on. In Distil, the Purchase History dataset is stored as a de-normalised list of items a Customer has purchased on a line item level, tied together by a common Order ID.
This dataset should link Products to Customers via a matching Customer ID and matching Product ID.
Ensuring you have provided good quality values for these Core Attributes will enable you to get the most out of Distil.
The table below shows the standard Core Purchase History Attributes that are available within Distil. In order for the columns in your database to be successfully matched to these Core Attributes, your column names must be one of the Matching Column Names in the table below, and the data type in your database must be one of those in the Allowed Type column in the table below.
If a field does not conform to the required name and type in this table, then that field will not be recognised as a Core Attribute and will instead be imported as a Custom Attribute.
If you haven't already, have a read of our guide to Preparing for Database Sync, which covers key topics such as using view or tables, and using read-replica databases.
| Friendly Name | Matching Column Name(s) | Allowed Type(s) | Required | Description |
| Id | id | decimal, bigint, integer, long, double, uuid, string, text | Yes | A unique id for this row. Each row in this dataset should be unique. It is expected that an Order Id has multiple line items, each with their own Id. |
| Customer Id | customerid | decimal, bigint, integer, long, double, uuid, string, text | Yes | The ID of the Customer. This should match to a Customer Id provided in the Customers dataset. |
| Product Id | productid | decimal, bigint, integer, long, double, uuid, string, text | Yes | The ID of the Product purchased in this Line Item. The quantity of the Product Purchased in the order should be reflected in the Qty attribute; thus the combination of Customer Id, Order Id and Product Id should be unique. |
| Quantity | quantity / qty / lineitemquantity / lineitemqty | bigint, integer, long, double, float | Yes | The quantity of the Product Purchased |
| Order Timestamp | timestamp / lineitemtimestamp / ordertimestamp | date, timestamp | Yes | The Timestamp/Date of the Order in UTC. |
| Order Id | orderid | decimal, bigint, integer, long, double, uuid, string, text | No – But helpful reference tying all items of the same order together. | The ID of the Order. This should be the same for all Products ordered during one checkout. |
| Line Value – Excluding Tax | linevalueexcludingtax / linevalueextax | bigint, integer, long, double, float | Yes | The value of the line, excluding any Tax. |
| Line Value – Including Tax | linevalueincludingtax / linevalueinctax | bigint, integer, long, double, float | Yes | The value of the line, including Tax. |
| Payment Method | paymentmethod | string, text | No | String for storing the payment method. |
| Currency | currency | string, text | No | ISO Currency Code. |
| Order Status | orderstatus / status | string, text | No | A value to indicate the status for the order line item |
| Discount Code | discountcode / vouchercode / couponcode | string, text | No | A Discount / coupon code applied to either the whole order, or just this order line item |
| Discount Amount | discountamount / voucheramount / couponamount | bigint, integer, long, double, float | No | A Discount / coupon amount applied to this order line item |
| Delivery Address Address Line 1 | postaladdressline1 | string, text | No | The First Line of the delivery address. |
| Delivery Address Address Line 2 | postaladdressline2 | string, text | No | The Second Line of the delivery address. |
| Delivery Address Address Line 3 | postaladdressline3 | string, text | No | The Third Line of the delivery address. |
| Delivery Address Town | postaladdresslinetown | string, text | No | The Town for the delivery address |
| Delivery Address Region | postaladdressregion | string, text | No | The Region for the delivery address |
| Delivery Address Postcode | deliveryaddresspostcode/ postaladdresspostalcode | string, text | No | The Postcode for the delivery address |
| Delivery Address Country | postaladdresscountry | string, text | No | The Country code for the Billing address |
| Billing Address Line 1 | billingaddressline1 | string, text | No | The First Line of the Billing address. |
| Billing Address Line 2 | billingaddressline2 | string, text | No | The Second Line of the Billing address. |
| Billing Address Line 3 | billingaddressline3 | string, text | No | The Third Line of the Billing address. |
| Billing Address Town | billingaddresslinetown | string, text | No | The Town for the Billing address |
| Billing Address Region | billingaddresslineregion | string, text | No | The Region for the Billing address |
| Billing Address Postcode | billingaddresslinepostalcode | string, text | No | The Postcode for the Billing address |
| Billing Address Country | billingaddresscountry | string, text | No | The Country code for the Billing address |
Custom Purchase History Attributes
You can bring as many Attributes as you wish into your Purchase History datasets. It is likely that you will have a set of Attributes that are highly bespoke to your organisation. Equally, if there are any internal calculated measures or external datasets these can be included in the source table or view and they will become available for sync in Distil as Custom Attributes.
As long as they are contained within a table or view is recognised by Distil as a Purchase History dataset (i.e. the table or view meets the requirements detailed above), then these attributes will be available within Distil.
Distil will also attempt to form a “friendly” name from the raw field name in the source data – e.g. coupon_code will become Coupon Code.
Comments
0 comments
Article is closed for comments.