Customer View Script

This section describes how to prepare these Views in your database so that they are recognised as containing Customer data and can be used to sync into Distil.ai.

There are two parts to this process. The first is to assess which Customer attributes you would like to be brought into Distil.ai, the second is write the scripts that create the database View to do this.

Planning

Identifying the Customer Attributes to be used.

The Customer List is the central concept of Distil.ai. It is simply a list of Customers with Attributes pulled from multiple systems and joined together to form a single record per Customer. Your Distil.ai Users can then Segment the Customer list by any Attribute. 

Unlike third-party systems where the Customer attributes are determined by the system and its API, with the Distil.ai Database Sync, as it’s your system, you need to make this decision yourself. 

Therefore it is worth speaking to the people who you plan to use Distil.ai within your business and understanding which measure they are likely to want to Segment Customers by. While First Name, Last Name and Email Address are essential, especially when forwarding data to other third-party systems, there will be other measures that are unique to you that will be useful. For example, what questions do you ask on sign-up, do you have a preference centre, can you create any useful aggregations? Consider things that turn your Customer base from one amorphous mass into a set of more nuanced groups.

If you are not the person who will be writing the SQL to create these Views it is worth doing the attribute defining legwork prior to handing over to the View creation, then working closely with the nominated SQL guru to refine the dataset. It is likely that they will be able to surface more useful data as well. 

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

One View to Rule them all, or Multiple Views

The Customer List within Distil.ai is split up into sections with each section representing a set of data. 

< CUSTOMER LIST VIEW />

You are able to create multiple Customer Views. This could be because you have data spread across multiple Databases; for instance, you might have different database for your Web Platform, another for your Mobile App, or one database per country, or one per shop etc. Each Customer View you create will translate into one Section (or Tab) in the Customer List.

Even if you have one database you may wish to split your Customer data up into multiple logical sections, just for ease of use. For example you may wish to create a Section for personal data, create another for information provided during registration or checkout, and another for aggregation and analytics. The choice is yours, but everybody loves nicely organised data!

Creating the Views

View Rules

  • Each record in the View should represent one Customer.
  • Each Customer should only be represented by one record in each Customer View.
  • Each Row must have a Text column called “ID” that contains a unique identifier. This could be your internal ID or if you don’t easily have those used across multiple systems you could use another common field, such as Email address, telephone number, facebook slug etc.

Core Attributes

You can bring as many different and bespoke attributes as you wish into your Customer datasets. However there are common Customer Attributes that Distil.ai will pull together to form a “Core Data” set i.e. First Name, Last Name, Postcode, Email Address, Telephone number etc. 

These are used to drive the Enrichments as well as to integrate with third-party systems so it is worth mapping to these where you can. For example Postal Code is used to identify the Customer’s location to drive the Demographic Enrichment.

Identification of the core attributes is done by naming convention, for example First Name should be contained in an attribute in your View as first_name; the last name attribute should be last_name.

The full list of the attributes and their database names is shown in the Customer data definition document

Custom Attributes

You can bring whatever custom attributes in you wish. Case is not important. Distil.ai will attempt to form a friendly name from the raw attribute name. To make best use of this use an underscore “_” to separate out the words in the Attribute.

For example join_offer_code will become a friendly name of “Join Offer Code” 

Once you have set up your Database Connection Distil.ai will interrogate the schema of your database and look for any Views that conform to the following criteria. Any that are found will become available for Sync into Distil.ai. All others will be ignored.

View Naming Convention

As described at the start of this document a naming convention is used by Distil.ai to identify the Views to be used in the sync. For a View to be identified as containing Customer data it must contain the words “Distil” and then “Customer”, in that order. 

There are many different database naming conventions out there, and who are we to dictate what you use, so any combination of those words will get picked up. Case is not important.

Some suggestions are:

  • distilCustomers
  • distil-customers
  • distil_customers
  • v_distil_customers
  • v_distil_customer

Each Customer View will be displayed in their own Section (or Tab) in the Distil.ai Customer List. 

<CUSTOMER LIST VIEW />

The name of the Tab will be determined by the following rules:

  1. If the View name only contains the words distil and customers then the Section will be called Customers.
  2. If the View name contains words after Customers then they will be used as the Section name. For example v_distil_customers_shop_stats will have the name “Shop Stats”

Example 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, that are also aliased. 

create view v_distil_customer
as
select
pkid as id
,email as email_address
,mobtel as mobile_phone_no
,title
,fname as first_name
,surname as surname
,subscribed as gdpr_subscribed
,right_access as gdpr_status_right_of_access_requested
,deleted as gdpr_status_anonymise_data
,facebook_url as facebook_slug
,twitter as twitter_handle
,instagram as instagram_slug
,joiningCode as joining_code
,shoppingRegion as shopping_region
,tradeAccount as trade_account
from tbl_users
;