Skip to main content
All CollectionsDataData import and export
Create Custom data tables
Create Custom data tables

Upload data to your account using Custom data tables.

Updated over 4 months ago

Custom data tables offer a flexible and powerful way to store and utilize customer-specific information. This data can then be used to enhance targeting and deliver personalized content to your customers using SmartBlocks.

1. Create a custom data table

To create a Custom data table:

  1. Go to Data >Custom Data.

  2. Select CREATE DATA TABLE and enter the following details:

    1. Name
      Enter the table’s name.

    2. Table ID
      Enter the table’s ID or select Make Table ID after entering a name to autofill this field.

    3. Type
      Expand the Type drop-down menu and choose the table type: Custom, Brands, or Categories.
      ​

  3. Select CREATE.

2. Create table fields

To create the table fields for your Custom data table:

  1. Select CREATE TABLE FIELD and enter the following details:

    1. Name
      Enter the field’s name.

    2. Field ID
      Enter the field’s ID or select Make Field ID after entering a name to autofill this field.

    3. Field Type
      Expand the Field Type drop-down menu and select the field type: String, Number, Date, or URL.
      ​

  2. Select CREATE.

Edit a table field

If you want to edit a table field:

  1. Expand the field settings icon (⋮) menu and select the edit icon.

  2. Change the name or field type.

  3. Select SAVE.

You cannot change the field’s ID after it has been created.

Delete a table field

If you want to delete a table field:

  1. Expand the field settings icon (⋮) menu and select the delete icon.

When a table field is deleted, any data stored under that field is also deleted.

3. Select a Lookup field

To use the table, you need to select a Lookup field:

  1. Select SAVE to open the Lookup field menu.

  2. Expand the Lookup field drop-down menu and choose a string type field name, this uses the field name as the lookup field.
    ​

  3. Select SAVE.

Saving the table without a Lookup field will store the table, but it can’t be used. Once a lookup field is saved, it cannot be changed later.

4. Import data

You can either manually import data to your Custom data table with a CSV file upload, or import data using the import channels.

Manual import

To manually import your data using a CSV file:

  1. Select MANUAL IMPORT to open the Upload Data menu.

  2. Select browse and choose the file you want to upload, or drag and drop the file onto the menu.

  3. You can choose if you want the upload to append or replace data in the table:

    1. Append new data to the table
      Clear the Replace All Data checkbox

    2. Replace all data in the table
      Select the Replace All Data checkbox to checked.

  4. Select IMPORT.

Import channel

To import your data using import channels:

  1. Select Import Channels for Data Tables.

  2. Under the heading Set up a new channel, expand the import drop-down menu and choose data you want to import.

  3. Select CREATE IMPORT CHANNEL.

  4. On the next page, specify the settings for the import channel.

    • Refer to the Custom data table import settings below for more information. For FTP import channel settings, consult your IT team if you’re unsure.

  5. Select SAVE.

Custom data table import settings

Setting

Description

Channel Name

An easy-to-remember name for your channel.

FTP Type

The type of FTP.

For large files (10 MB or more), we recommend using ftpes

FTP Host

The address of your FTP server.

FTP Port

The FTP port used:

  • FTPes server

    Leave this field empty.

  • SFTP

    This should usually be set to 22. The default port for FTPes is to leave it blank.

If you're not sure what port you need to use, leave this field empty or to its default value, or contact your ESP or FTP support provider.

FTP User Name

The user name for connecting to your FTP server.

FTP Password

The password for connecting to your FTP server.

FTP Interval

The number of minutes between runs.

We suggest entering a value of 60, which means the FTP server is accessed every 60 minutes.

FTP Directory

Where files on your FTP server are read from.

FTP Allowed Files Pattern

The file pattern allowed on the FTP server. For example, d*.csv.

A comma separated list is accepted, for example, d*.csv,t*.csv - you must specify a pattern, such as person*.csv, transaction*.csv, product_import*.csv.


​CSV and TSV formats are supported. Import filenames specified on segments which import from this channel are also be read from the FTP server.

FTP Field Mapping

The JSON object that describes how fields from the CSV are mapped into the Custom Data Table. All field names are case sensitive. The outer key contains the logical field names (the field names that go into the system).

Each field is an object which contains an ftp_field_name property, which contains the name of the field in the CSV. For example, to map TeamName from the CSV, to team_name for the system would look like this: {"team_name":{"ftp_field_name":"TeamName"}}.

You must include all the fields you want to import in the field mapping, even if they are the same.

You can also specify a date_format to go with a date field as follows: {"date":{"ftp_field_name":"date", "date_format":"%Y-%m-%d"}}

If you do not specify a date format we will try to automatically recognise it for the import.

Import Type

The type of import method:

  • Append

    Update existing data and append new data.

  • Replace

    Delete all existing data and replace with new data.

Is Service Paused?

Optionally select this checkbox to stop this service.

Import using the Fresh Relevance API

You can also use the Fresh Relevance API to import data to a Custom Data Table. To do this:

CSV file field mappings

  • You can’t create field mappings using this method. Only the data in the CSV file is uploaded.

  1. You first need an access token. To get this, expand the User menu, and go to Settings > Other Integrations > Fresh Relevance Access Tokens.

  2. Either:

    1. Create a new token and copy the token’s name and key for later. Learn more in Create and edit API access tokens.

    2. Copy an existing token’s name and key for later.

  3. Make a POST request to /api/<website_id>/custom_data_tables/import/<table_id>/

  4. You can include the token as either:

    1. A header. For example, HTTP_X_API_KEY

    2. A query parameter. For example, api_token

  5. Next, you need to add the CSV file you want to import to the form-data of the request under the key files[]. You also need to provide the key importType which is either append or replace, depending on which option you want.

View import jobs

To view all current and scheduled import jobs, select Import Jobs.

To view Custom data table import jobs, expand the User menu, and go to Settings > Imports > Custom Data Tables Import Jobs.

View data

To view the Custom data table’s data, select the View Data tab.

Select the download icon in the top right corner of the table to download the data.

Properties

To view the Custom data table’s details, select the Properties tab.

Use Custom data tables in SmartBlocks

After creating your Custom data table and importing data into it, you can use it in a SmartBlock.

Add Custom data table to SmartBlock

  1. To get started, either create a new SmartBlock or edit an existing one.

  2. On the Edit SmartBlock page, expand the settings (⋯) drop-down menu at the top and select Table Lookup.
    ​

  3. Select Add Lookup + to open the Custom data table Lookup menu.
    ​

  4. Expand the CUSTOM DATA TABLE drop-down menu and choose your Custom data table.

  5. Expand the Lookup Field Type drop-down menu and choose either:

    1. Merge Parameter

    2. Person

    3. Product

      • The Lookup Field Type is the type of the data that is used as an input to the Custom Data Table Lookup field

  6. For Lookup Field, enter the Lookup field for the Custom data table. For Person or Product fields enter the prefixes extend. or ex. before the field name.:

    1. Merge Parameter

      Enter fieldname

    2. Person

      Enter extend.fieldname

    3. Product

      Enter ex.fieldname
      ​

  7. Select ADD.

After adding the Custom data table to the SmartBlock you can reference it using Jinja in either the SmartBlock editor or the SmartBlock’s Layout:

Add data using SmartBlock editor

  1. In the SmartBlock editor, select the Design tab.

  2. Expand the drop-down menu for the element you want to edit.

    • The element must be compatible with Jinja. For example, an Image Headline.

  3. For the element’s text field enter the table’s data using the following formats for a Person field, Merge field, or Product field:

    • Person field or Merge field

      {{ table.[table_id].[table_field_id] }}

    • Product field

      {{ product.table.[table_id].[table_field_id] }}
      ​

  4. Select SAVE.

Add data using the SmartBlock Layout

  1. In the SmartBlock editor select the Edit layout (</>) icon.

  2. Find the area of the Layout you want the table’s data to display.

  3. Enter the table’s data into the Layout using the following formats for a Person field, Merge field, or Product field:

    • Person field or Merge field

      {{ table.[table_id].[table_field_id] }}

    • Product field

      {{ product.table.[table_id].[table_field_id] }}
      ​

  4. Select VALIDATE OUTPUT and SAVE.

Related Articles
App output
Import person data
Import transactions
Data Capture SmartBlocks
Create and edit a SmartBlock
Did this answer your question?