Most Organisations will use one or more backend databases to store their application and analytical datasets. Typically this will be in a relational database such as MySQL, PostgreSQL, SQL Server, Oracle, DB2, Redshift etc.
This section describes how to prepare databases and datasets for Customers, Products, Purchase History and Content so they can be synchronised with Distil.
Database preparation
We understand that Databases are critical elements to any solution and that connecting external systems to them needs to be done with a great deal of care and attention. We have ensured that the Distil database sync treats your infrastructure with respect and operations are kept as lightweight as possible, something that is very important when dealing with multiple millions of records.
The following describes some basic database practices we recommend when preparing your database and infrastructure to connect to Distil.
Read-only user account
It is always advisable to set the least amount of permissions required and so we recommend that you provision a dedicated database user account with ‘read-only’ access. It is also advisable to set permissions only to the “Distil” tables described in this document. Distil has no requirement to access any other information.
SSH
SSH security with a public key provides an additional layer of safety and is supported by most databases. We suggest that if you have the facility to set this up you do so. Distil can easily connect to your database over SSH.
IP White Listing
The Distil database sync will always connect from one of two fixed IP Addresses. These are:
- 52.169.82.244
- 40.127.201.179
In order for Distil to connect to your database it is likely you will need to enable connections to your database from this IP.
Read-Replica Database
We have designed the Distil Database Sync to be lightweight and minimise calls to your database as much as possible. However, if you are concerned about additional load being placed on your database infrastructure then setting up a separate read-replica database is advisable.
Table or Views?
Once a connection has been established to your database Distil will interrogate the schema and identify any Tables or Views that match the naming conventions laid out later in this document.
Distil is comfortable with either a Table or a View. However it is likely that the Customer data in your database is set to a particular format which will differ from that required by Distil. Therefore it is more efficient to create a database View containing Customer data and joining any other interesting values you wish to be present as attributes in your Distil dataset.
If you are concerned with heavy loading of your database you may wish to consider copying your Customer data into a table (or if your database supports it, a Materialised View). This may reduce the load on your system during sync, however there is a potential for the Distil Sync and the Table update query to get out of step with each other.
Column Naming
The matching of column names to attributes in Distil is done after first ‘cleaning’ the original column name in the following ways:
- Converting to Upper Case (emailaddress matches EMAILADDRESS)
- Trimming any white space (First Name matches FIRSTNAME)
- Removing any non alphanumeric characters (Last_name matches LASTNAME)
Customer Data
Customers are at the core of work you do within Distil. As such Customer Lists are the most important dataset. It is typical for an Organisation to have Customer data stored in multiple places. Distil will use key fields to perform Identity Resolution across multiple datasets in order to match Customers’ details held in one system to that held in another. While it is unlikely that you can supply all these fields in every dataset, the more fields supplied will increase the accuracy of the Distil Identity Resolution algorithms.
This section describes how to prepare Tables and/or Views in your database so that they are recognised as containing Customer data and can be used to sync into Distil.
Once you have set up your Database Connection 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 become available to sync into Distil.
Table/View naming convention
For a Table or View to be identified as containing Customer data it must contain the words “Distil” and “Customer”, in that order. Any combination of those words will get recognised, and case is not important. Some examples are:
- distilCustomers
- distil-customers
- distil_customers
- v_distil_customers
- v_distil_customer
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 records and row identifier
Each row in any Customer table must represent one Customer only. Each row must have a column called “ID” that contains a unique primary key. The type of this field does not matter, however it will be converted to a String in Distil. Again, case is unimportant. If a table does not contain a unique ID then it will not appear in the Distil interface, and the data cannot be imported into Distil.
Different IDs in different systems
If you need to bring in data from a number of different databases that do not have a common unique ID, it is advisable to use an attribute such as Email Address, mobile telephone number etc as a unique identifier if at all possible.
Required Fields
If any of the field(s) marked as Required in the Core Attributes table below are not present in the source table/view, then no data will not be imported into Distil from this table/view.
Core Customer Attributes
There are a set of Common Customer Attributes that Distil uses for specific purposes. These include things like First Name, Last Name, Postcode, Email Address, Telephone number etc. Uses for these include setting up Retargeting Audiences; creation of Mail Merges in third-party mail tools, such as MailChimp or Campaign Monitor; or for use as parameters in Enrichments.
By ensuring you have provided good quality values to these core fields that will enable you to get the most out of the platform.
The table below shows the standard core Customer attributes that are available within Distil. In order for these attributes to be matched to the columns in your database table or view they must conform to the Column Name and Type shown against each attribute in the table.
| Friendly Name | Matching Column Name(s) | Allowed Type(s) | Required | Description |
| Customer ID | id/ customerid | decimal, bigint, integer, long, double, uuid, string, text | Yes | Unique Customer Identifier |
| First Name | firstname/ givenname | string, text | No | The Customer’s First Name |
| Last Name | lastname/ surname | string, text | No | The Customer’s Last Name |
| Email Address | email / emailaddress | string, text | No | The Customer’s Email address |
| Mobile Phone No | mobilenumber / telephone / telnumber / phonenumber | string, text | No | The Customer’s mobile phone number |
| GDPR Status – Marketing Subscribed | gdprstatussubscribed | boolean, integer(if using an integer for this field, we are expecting values of 0 or 1) | No | True/false indicating if the Customer has consented to receiving marketing communications |
| Country | country / countrycode | string, text | No | This value is used to indicate if this Customer wishes their data to be Deleted. If this is True no personal information will be stored against this customer, no matter what is passed through. Any historic data will also be anonymised |
| Postcode | postcode / postalcode / postaladdresspostalcode / postaladdresspostcode / zip | string, text | No |
If any 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 be imported as a custom attribute.
Custom Customer Attributes
You can bring as many attributes as you wish into your Customer 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 Customer 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. join_offer_code will become Join Offer Code.
Example View SQL
The snippet below shows a simple SQL Statement that creates a view that selects from the Users table “tbl_users”, creating aliases for each attribute so they match both the core attribute naming conventions and also brings in a few custom attributes, which are also aliased.
| 1 create view v_distil_customer 2 as 3 select 4 pkid as id 5 ,email as email_address 6 ,mobtel as mobile_phone_no 7 ,title 8 ,fname as first_name 9 ,surname as surname 10 ,subscribed as gdpr_status_subscribed 11 ,country 12 ,postcode 13 ,joiningCode as joining_code 14 ,shoppingRegion as shopping_region 15 ,tradeAccount as trade_account 16 from tbl_users 17; |
Product Data
A number of Distil’s features require knowledge of the Products that your Organisation sells. Product data (as well as Content data) is typically used to create personalised Customer experiences where having attributes such as the Product Image URL, Link, Price and Availability are vital.
This section describes how to prepare Tables and/or Views in your database so that they are recognised as containing Product data and can be used to sync into Distil.
Once you have set up your Database Connection 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 become available to sync into Distil.
Table/View naming convention
For a Table or View to be identified as containing Product data it must contain the words “Distil” and “Product”, in that order. Any combination of those words will get recognised, and case is not important. Some examples are:
- distilProducts
- distil-products
- distil_products
- v_distil_products
- v_distil_product
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 records and row identifier
Each row in any Product table must represent one Product only. Each row must have a column called “ID” that contains a unique primary key. The type of this field does not matter, however it will be converted to a String in Distil. Again, case is unimportant. If a table does not contain a unique ID then it will not appear in the Distil interface, and the data cannot be imported into Distil.
Different IDs in different systems
If you need to bring in data from a number of different databases that do not have a common unique ID, it is advisable to prefix a unique string to the product ID to distinguish the overlapping IDs from each other. This could either be another field, such as the product slug, or the dataset identifier.
Required Fields
If any of the field(s) marked as Required in the Core Attributes table below are not present in the source table/view, then no data will not be imported into Distil from this table/view.
Core Product Attributes
There are a set of Common Product Attributes that Distil uses for specific purposes. These include things like Product Name, Thumbnail URL, Available, Price, etc. These values are used in Mail Merges in third-party mail tools, such as MailChimp or Klaviyo; or for use as parameters in the Enrichments.
By ensuring you have provided good quality values to these core fields that will enable you to get the most out of the platform.
The table below shows the standard core Product attributes that are available within Distil. In order for these attributes to be matched to the columns in your database table or view they must conform to the Column Name and Type shown against each attribute in the table.
| Friendly Name | Matching Column Name(s) | Allowed Type(s) | Required | Description |
| Product ID | id / productid | decimal, bigint, integer, long, double, uuid, string, text | Yes | The Account’s Unique Identifier for a Product |
| Product Name | name / title / productname | string, text | Yes | The name of the Product |
| Product Url | url / producturl / link / shopurl | string, text | Yes | URL to the Product |
| Available | productavailable / available / isavailable | boolean, integer(if using an integer for this field, we are expecting values of 0 or 1) | Yes | If the Product is currently available or not |
| Product Precis | precis / productprecis / description / productdescription | string, text | No – But helpful for hyper-personalised email marketing. | Introductory description for the Product |
| Product Thumbnail URL | productthumbnailurl / productthumbnail / thumbnail / thumbnailurl / thumbnailimage / thumbnaillink | string, text | No – But required for hyper-personalised email marketing. | URL to the Product Thumbnail |
| Product Image Url | productimageurl / productfullimageurl / productimage / image / imageurl / imagelink | string, text | No – But helpful for hyper-personalised email marketing. | URL to a full image of the Product |
| List Price Excluding Tax | listpriceextax / listpriceexctax / productlistpriceextax / productlistpriceexctax / priceexctax | bigint, integer, long, double, float | No – But helpful for hyper-personalised email marketing. | List price excluding Tax |
| List Price Including Tax | listpriceinctax / productlistpriceinctax / listpriceintax / productlistpriceintax / priceintax / price | bigint, integer, long, double, float | No – But helpful for hyper-personalised email marketing. | List price including Tax |
| Price Breaks Description | productpricebreaksdescription / pricebreaksdescription | string, text | No | Price breaks description text. This is used for informational purposes only so can be formatted as the Distil Account wishes |
| Product Category | productcategory / category / productcategories / categories / categoryname | string, text (csv delimited) | No – but enhances the Spend Brackets enrichment | |
| Product Stock Units | productstock / stock / stocklevel / stockunits | bigint, integer, long, double, float | No | The number of stock units available for this product |
Custom Product Attributes
You can bring as many attributes as you wish into your Product 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 Product 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. country_of_manufacture will become Country Of Manufacture.
Purchase History Data
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.
This section describes how to prepare Tables and/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 interrogate the schema for your chosen database. It looks for any tables or views that conform to the following criteria – any that do will become available to sync into Distil.
Table/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 records and row identifier
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(s) marked as Required in the Core Purchase History Attributes table below are not present in the source table/view, then no data will not be imported into Distil from this table/view.
Core Purchase History Attributes
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 packing etc. In Distil The Purchase History dataset is stored as a denormalised 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.
The table below shows the standard core Purchase history attributes that are available within Distil. In order for these attributes to be matched to the columns in your database table or view they must conform to the Column Name and Type shown against each attribute in the table.
| 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 Order Attributes
You can bring as many attributes as you wish into your Purchase Order 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 Order 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.
Content Data
A number of Distil’s features require knowledge of the Content that your Organisation publishes on your Platform(s). Content data is typically used to create highly personalised Customer experiences where having attributes such as the Content Title, a cover image, Link and Precis are vital.
This section describes how to prepare Tables and/or Views in your database so that they are recognised as containing Content data and can be used to sync into Distil.
As with Customer and Product data, once you have set up your Database Connection 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 become available to sync into Distil.
Table/View naming convention
For a Table or View to be identified as containing Product data it must contain the words “Distil” and “Content”, in that order. Any combination of those words will get recognised, and case is not important. Some examples are:
- distilContent
- distil-content-articles
- distil_content
- v_distil_content
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 records and row identifier
Each row in any Product table must represent one Content item only. Each row must have a column called “ID” that contains a unique primary key. The type of this field does not matter, however it will be converted to a String in Distil. Again, case is unimportant. If a table does not contain a unique ID then it will not appear in the Distil interface, and the data cannot be imported into Distil.
Different IDs in different systems
If you need to bring in data from a number of different databases that do not have a common unique ID, it is advisable to prefix a unique string to the content ID to distinguish the overlapping IDs from each other. This could either be another field, such as the content title, or the dataset identifier.
Required Fields
If any of the field(s) marked as Required in the Core Attributes table below are not present in the source table/view, then no data will not be imported into Distil from this table/view.
Core Content Attributes
There are a set of Common Content Attributes that Distil uses for specific purposes. These include things like Content Title, Thumbnail URL, Public URL, Published On, etc. These values are used in Mail Merges in third-party mail tools, such as MailChimp or DotDigital; or for use as parameters in the Enrichments.
By ensuring you have provided good quality values to these core fields that will enable you to get the most out of the platform.
The table below shows the standard core Content attributes that are available within Distil. In order for these attributes to be matched to the columns in your database table or view they must conform to the Column Name and Type shown against each attribute in the table.
| Friendly Name | Matching Column Name(s) | Allowed Type(s) | Required | Description |
| Content ID | id / contentid | decimal, bigint, integer, long, double, uuid, string, text | Yes | The unique Content Item identifier – in the case that you use more than one Content database, this ID should uniquely identify the Content Item across all Content Datasets. |
| Title | name / title | string, text | Yes | The Content Item Title. |
| URL | url / contenturl / link | string, text | Yes | URL to the Content Item. |
| Image URL | image / coverimageurl / imageurl / imagelink | string, text | No | URL to a full image for the Content Item. |
| Precis | description / summary / body / precis | string, text | No | Short into paragraph describing the Content. |
| Content Available | available / currentlyavailable / contentavailable / isavailable / enabled / isenabled | boolean, integer(if using an integer for this field, we are expecting values of 0 or 1) | No | Whether the content is available |
| Published on | published / publishedon | date, timestamp | No | Published on Timestamp/Date in UTC |
| Keywords | keyword / keywords | string, text(csv separated values) | No | Any Meta Keywords for the Content separated by commas. |
Custom Product Attributes
You can bring as many attributes as you wish into your Content 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 that is recognised by Distil as a Content 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. will become Tickets Left.
