Transform and import customers' data to Synerise

Published January 23, 2023
Modules
Difficulty

Automation capabilities in the area of data transformation can be used to update and change large amounts of data in a fast, advanced way, eliminating the process of manually adjusting the whole file. All transformations are performed on a sample file in desired format.

In this use case, you will find out how to transform and import customer data from a .JSON file with data transformation and workflows. In this use case, you will make the following modifications to the sample file:

  • Edit values:
    • replace the value of the city attribute from blank to unknown; if the city attribute is defined, keep the existing city,
    • replace the en, es, fr values of the language attribute with English, Spanish, French, respectively,
    • replace the value of payment_cash and payment_online attributes from 1/0 to cash/Null and online/Null.
  • Merge payment_cash and payment_online attributes into the payment_info attribute.

Input data in use case

In this use case, we use two files:

  • the complete .JSON file with your customer database. Make sure you include one of the required parameters: clientID, uuid, email, customId. The only requirement for JSON files is that they must be UTF-8 encoded.
  • Prepare a sample JSON file with data you want to import. Below you will find an example how such a a file can look.
Example JSON file might look like this

{
      "email": "test@test.com",
      "city": "",
      "language": "fr",
      "payment_cash": "1",
      "payment_online": "0"


    },
    {
      "email": "test2@test.com",
      "city": "New York",
      "language": "en",
      "payment_cash": "0",
      "payment_online": "1"
    }

Note: If your data is complete, you can skip transforming data. But if you need to modify the file with data before an import to Synerise, you can modify the data in Automation > Data Transformation. To do so, create a sample of your data and include all attributes you want to modify. If you miss the attributes in the sample file, but import the actual data with them, the data will be imported as delivered in the actual file.

Process


  1. Prepare data transformation rules to transform the data in the file.
  2. Prepare a workflow to import the .JSON file to Synerise.

Create data transformation rules


In this part of the process, you define the rules of modifying data before sending it to Synerise, so the data is consistent. Each of the following sub-steps describes the individual changes performed on the file.

  1. Go to Automation icon Automation > Data Trasnformation > Create transformation.
  2. Enter the name of the workflow.
Note:

Before you proceed with selecting sample data and defining transformation rules, optionally, you can select a goal to help you structure the data. If you want to create a transformation diagram without a specific goal and you know the structure of the output data, skip this step.

Goals will suggest you the required fields needed to perform the import into Synerise.

Add input 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. Click the Add input node on the canvas.
  2. On the pop-up, click Upload a new file or drag one here.
  3. Upload the .JSON file.
  4. You can preview the file, then click Apply.

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

Data input of the sample file  class=
Data input of the sample file

Handle missing data

In this part of the process, you will use the Edit values node to edit the city attribute values with Jinja code to keep the existing city and add the unknown value to the blanks.

  1. Add the Edit values node.
  2. In the configuration of the node:
    1. Click Add rule.

    2. Click Add column.

    3. Select the city column.

    4. Under Edit values by, from the dropdown list, select Replacing.

    5. Under with, from the dropdown list, select Dynamic value.

    6. In the Type value field, paste the following Jinja code:

              {% if root["city"] != "" %}{{ root["city"] }}{% else %}unknown{% endif %}
              
      The view of the configuration of the Edit values node
      The configuration of the Edit values node

      As a result, in the Output data tab, you will get an updated file:

      Output data after applying Edit values node
      Output data after applying Edit values node

Change data

In this part of the process you will use the same Edit values node created in the previous part of the process, to also edit the language, payment_cash and payment_online attributes values with jinjava code to change the data. You will change the language attribute value from “en” to “English” and so on. In case of payment_cash and payment_online attributes you will change “1/0“ values to “cash/Null“ and “online/Null” to later merge them into payment_info attribute.

  1. In the configuration of the node:

    1. Click Add rule.

    2. Click Add column.

    3. Select the language column.

    4. Under Edit values by, from the dropdown list, select Replacing.

    5. Under with, from the dropdown list, select Dynamic value.

    6. In the Type value field, paste the following Jinja code:

             {% if root["language"] == "en" %}English{% elif root["language"] == "es"%}Spanish{% elif root["language"] == "fr"%}French{% endif %}
              
      The view of the configuration of the Edit values node
      The configuration of the Edit values node
  2. Click Add rule.

    1. Click Add column.
    2. Select the payment_cash column.
    3. Under Edit values by, from the dropdown list, select Replacing.
    4. Under with, from the dropdown list, select Dynamic value.
    5. In the Type value field, paste the following Jinja code:
          {% if root["payment_cash"] == "1" %}cash{% elif root["payment_cash"] == "0"%}{% endif %}
              
  3. Click Add rule.

    1. Click Add column.
    2. Select the payment_online column.
    3. Under Edit values by, from the dropdown list, select Replacing.
    4. Under with, from the dropdown list, select Dynamic value.
    5. In the Type value field, paste the following Jinja code:
          {% if root["payment_online"] == "1" %}online{% elif root["payment_online"] == "0"%}{% endif %}
              

    As a result, in the Output data tab, you will get an updated file:

    Output data after applying Edit values node
    Output data after applying Edit values node

  4. Click Apply.

Merge columns

In this part of the process, you will use the Merge columns node to merge payment_cash and payment_online attributes into one payment_info attribute.

  1. Click THEN on the canvas.

  2. From the dropdown list, select Merge columns node.

  3. In the configuration of the node:

    1. Click Add rule.

    2. Click Add column.

    3. Select the payment_cash and payment_online columns.

    4. In the Merge values to new column section, in the New column name field, type the name for the new column: payment_info.

    5. Enable the Remove original columns option.

      The view of the configuration of the Edit values node
      The configuration of the Merge columns node

    As a result, in the Output data tab, you will get an updated file:

    Output data after applying Edit values node
    Output data after applying Merge columns node
  4. Click Apply.

  5. As the final node, add Data Output in which you will see the final result of file modifications.

  6. Click Save and publish.

Prepare a workflow


The scenario for this use case involves a one-time import of .JSON file with a customer database directly 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. Change the Run trigger option to one time.
    2. Select Immediately.
    3. Confirm by clicking Apply.
    The configuration of the Scheduled Run node
    The configuration of the Scheduled Run node

Select file to import

  1. Add the Local file node.
  2. In the configuration of the node:
    1. Upload the file.
    2. Confirm by clicking Apply.
The view of the Local file transfer
Local file transfer

Add Data Transformation node

  1. Add the Data Transformation node.

  2. In the configuration of the node, select the data transformation you have created before.

    The view of the configuration of the Data Transformation node
    The configuration of the Data Transformation node
  3. Click Apply

Add import profiles and finishing node

  1. Add the Import profiles node.

  2. Add the End node.

  3. In the upper right corner, click Save & Run.

    The view of the workflow configuration
    The workflow configuration

Check the use case set up on the Synerise Demo workspace


You can check the Data Transformation and Workflow configurations directly in Synerise Demo workspace.

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