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.
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 before reading this article.
- 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 - this article
- Purchase History data
- Content data
- Creating the Database Connection - creating the Connection in Distil
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 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 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 valid examples are:
- distilProducts
- distil-products
- distil_products
- v_distil_products
- v_distil_product
Any table or view 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 Product table must represent one Product 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 Product 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 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 fields marked as Required in the Core Attributes table below are not present in the source table or view, then no data from the table will be imported into Distil.
Core Product Attributes
Distil uses Core Attributes for specific purposes. These include information such as 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.
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 Product 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 |
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 the Attributes 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.
Comments
0 comments
Article is closed for comments.