Purchase History View Script

A number of Distil.ai features require knowledge of the Products purchased by your Customers. For instance features such as Lifetime Value (LTV) calculations, Product Recommendations, Spend Bracket and Demographic Profiling all use Customer purchase behaviour to enrich your Customer data

Planning

It is typical to store Purchase history (Orders) data using a number of tables; Orders, Line Items, Address for both billing and delivery, postage and packaging and so on.

So as to not have to create and maintain multiple Views just for Purchase History data the View we ask you to create is denormalized into one flat table at the line-item granularity. This means that each record is an order line item. For example:

<EXAMPLE TABLE />

The View should match the Purchase History data definition. And, as this dataset is used only to drive the enrichment features of Distil.ai and is not forwarded to any third-party systems no customer attributes are allowed. 

If you would like some guidance during this process, please see our Supported Onboarding process. We have our own SQL gurus that can help should you need it.

Creating the Views

Core and Custom Attributes

Details of the Purchase History dataset can be found in the Purchase History data definition. Unlike Customers and Products scripts, it is not possible to include any custom attributes into Purchase History.

Unique records and row identifier

Important: each row in the Purchase History table must represent one order line item. 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.  

Table / View Naming Convention

As with other datasets, once you have connected your database Distil.ai will interrogate the schema and look for Purchase History datasets. For a View to be identified as containing Purchase History data it must contain the words “Distil”, “Purchase” and “History” in that order. 

Some suggestions are:

  • distilPurchaseHistory
  • distil-purchase-history
  • distil_purchase_history
  • v_distil_purchase_history