Database Sync: Customer data

Customers are at the core of every organisation. Distil can help unlock insights into your Customers to help you serve them better, and drive your business towards your goals.

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.

1. Preparing for Database Sync - readying your infrastructure and applying best practices.
2. Preparing your tables or views - this is covered in seperate articles for each Data Category:
   • Customer data - this article
   • Product data
   • Purchase History data
   • Content data
3. 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 Customer 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). 

Multiple Sources of Customer Data

If your organisation is like most, then you'll have Customer data stored in multiple places. This can lead to all sorts of headaches and result in poor quality insights. If this sounds like you, then you probably don't need us to tell you the problems this can cause.

The great news is that Distil comes with a sophisticated and powerful Identity Resolution algorithm that will join multiple datasets, without creating duplicates.

Identity Resolution, also known as entity resolution or record linkage, is the process of identifying and matching records that correspond to the same real-world entity or individual across different data sources.

Distil will use key fields to perform Identity Resolution across multiple datasets in order to match Customer data 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.

Table or 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 valid 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 IDs for Records

Each row in any Customer table or view must represent one Customer only.

Each row must have a column called ID that contains a unique primary key. The data type of this field does not matter, however it will be converted to a String in Distil. The case is unimportant.

If a Customer table does not contain a unique ID column then it will not appear in the Distil interface, and the data will not 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 that is typically unique as the identifier, such as email address or mobile telephone number.

Required Fields

If any of the fields marked as Required in the Core Customer Attributes table below are not present in the source table or view, then no data from the table will be imported into Distil.

Core Customer Attributes

Distil uses Core Attributes for specific purposes. These include information such as 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.

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 Customer 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.

The GDPR Status Attribute is used to indicate whether this Customer wishes their data to be deleted. If the value of this Attribute is True then no personal information will be stored against this Customer, no matter what is passed through. Any historic data will also be anonymised.

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, then they will become available for sync in Distil as Custom Attributes.

As long as the Attributes are contained within a table or view that 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;