Database Sync: Product data

  • Updated

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.

  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:
  3. 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.


Was this article helpful?



Article is closed for comments.

Still have questions?

Contact us