Customers' data import from SFTP server

Published June 02, 2022
Modules
Difficulty
Selected Clients
empik logodecathlon logoorange logocarrefour logo

If you gather your customers’ data in several sources, you can use the Automation module to synchronize it with the customers’ profiles in Synerise. It’s possible thanks to the HTTP and SFTP integrations that allow you to send data to Synerise from external sources and the other way round.

This use case describes how to prepare an automated workflow that launches every day and donwloads an updated list of customers with their agreement for email communication from an external resource to Synerise. The automation connects to an external resource using the SFTP protocol and imports data from a CSV file. One of the challenges addressed in this use case is renaming a column in the CSV file while importing the data.

Prerequisites


  • Host the .csv file with customers’ marketing agreements data on an SFTP server.
  • Prepare a sample of data that will be used in data transformation. To do this, you can take the real file and remove rows until about 10 are left.

Process


  1. Create data transformation rules that will transform data from the .csv file.
  2. Prepare a workflow that imports data about customers’ marketing agreements from the SFTP server to Synerise.

Create data transformation rules


In this part of the process, you define the rules of modifying data. The data transformation diagram which is the output of this part of the process is used later to automate sending the data.

The sample file is used to configure the data transformation diagram and preview its results. With a library of nodes, you can modify the file by adding, renaming, and merging columns, as well as editing the values in the rows, and so on.

In this example, we will use the Rename column node to transform a customers’ marketing agreements file so it meets Synerise’s requirements.

  1. Go to Automation icon Automation > Data Transformation > Create transformation.
  2. Enter the name of the transformation.
  3. Click Add input.

Add file with sample data

This node allows you to add a data sample. In further steps, you define how the data must be modified. Later, when this transformation is used in the Automation workflow, the system uses the rules created with the sample data as a pattern for modifying actual data.

  1. On the pop-up, click Add example.

  2. Upload the file with the sample data.
    Below is the sample used in this article. It consists of two columns, where the first contains a customer’s email address, and the second contains the marketing consent: 0 means disabled, 1 means enabled.

    email;newsletter agreement
    john.doe@synerise.com;1 
    
  3. Click Generate.

The configuration of the Data input node
The configuration of the Data input node

Result: The Data input view is filled with data from the sample.

Data input of the sample file
Data input of the sample file

Rename column

The column name is the key under which the data will be imported to Synerise and appear in the output file. In this example, we will use the Rename column node to rename the newsletter agreement column to agreements.email.

  1. On the canvas, click the right mouse button.
  2. From the dropdown list, select Rename column.
  3. Click the Rename column node.
  4. In the configuration of the node:
    1. Click Add rule.
    2. Select the Include these option. This is the default option. All column names you select will be changed as you specify in the further configuration.
    3. Click Add column.
    4. From the dropdown list, select the columns you want to rename.
    5. Under the Edit values by subheader, select the Replacing option. This option finds values matching the conditions and replaces them with the value you specify.
    Example of the configuration of the Rename column node
    Example of the configuration of the Rename column node
  5. Before you save the settings, you can check the preview of the file after changes in the Output data tab.
  6. Confirm by clicking Apply.

Add the finishing node

This node ends the transformation and passes the modified data to the automation where the Data Transformation is used.

  1. On the canvas, click the right mouse button.
  2. From the dropdown list, select Data output.
  3. In the upper right corner, click Save and publish.
    Result:
    The diagram of data transformation
    The diagram of data transformation

After the data transformation is published, you can use it in the Data Transformation node while preparing a workflow that imports the files.

Prepare a workflow


As the second part of the process, create a workflow which imports the custom events every day to Synerise.

  1. Go to Automation icon Automation > Workflows > New workflow.
  2. Enter the name of the workflow.

Define the launch date

  1. As the trigger node, add Scheduled Run.
  2. In the configuration of the node:
    1. Leave the Run trigger option at default (all time).
    2. From the Timezone dropdown list, select the time zone consistent with the timezone selected for your workspace.
    3. Define the frequency of the workflow (for example, everyday at 6.00 A.M.).
      The workflow will automatically launch everyday at the specified time.
    The configuration of the Scheduled Run node
    The configuration of the Scheduled Run node
  3. Confirm by clicking Apply.

Configure settings for SFTP protocol

Use the SFTP - Get file node to transfer the files from the workflow to your server using SFTP protocol.

  1. Add the SFTP - Get file node.

  2. In the configuration of the node :

    1. Enter the path to your server.
    2. Select the port.
    3. Enter the path to the directory.
    4. Enter the name of the file where the data will be saved.
    5. From the File format dropdown list, select the CSV format.
    6. Verify and modify the delimiters if needed.
    7. In the Authentication section, select the method of authentication.
    The configuration of the SFTP node
    The configuration of the SFTP node
  3. Confirm by clicking Apply.

Select the data transformation rules

  1. Add a Data Transformation node.
  2. In the configuration of the node, select the data transformation you have created before.
    Example of the configuration of the Data Transformation node
    Example of the configuration of the Data Transformation node
  3. Confirm by clicking Apply.

Import customers

  1. Add the Import customers node. In the settings of the node, you can check the list of the optional columns.
  2. Confirm by clicking Apply.

Add the finishing node

  1. Add the End node.
  2. In the upper right corner, click Save & Run.
    Result:
    The workflow configuration
    The workflow configuration

You can monitor the flow of the workflow in the Transformation logs tab. It contains information about each execution of the workflow.

The logs for the workflow
The logs for the workflow

Check the use case set up on the Synerise Demo workspace


You can check the data transformation rules directly in Synerise Demo workspace.

Also, you can check there the created workflow.

If you don’t have access to the Synerise demo workspace, please leave your contact details in this form, and our representative will contact you shortly.

Read more


πŸ˜•

We are sorry to hear that

Thank you for helping improve out documentation. If you need help or have any questions, please consider contacting support.

πŸ˜‰

Awesome!

Thank you for helping improve out documentation. If you need help or have any questions, please consider contacting support.

Close modal icon Placeholder alt for modal to satisfy link checker