Transform and import customers' data to Synerise

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. If your workspace uses non-unique emails, use customId or uuid. 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.

{ "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" }

Process

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

Create a transformation

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 Hub > Transformations > New transformation.
2. Enter the name of the transformation.

Add input data

This step 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 workflow, the system uses the rules created with the sample data as a pattern for modifying actual data.

1. Click Upload a new file or drag one here.
2. Upload the .JSON file.
3. You can preview the file, then click Apply.

Result: The editor displays data from the sample.

Handle missing data

In this part of the process, use the Edit Values step to keep the existing city and replace blank values in the city column with unknown.

1. On the right panel, click Add step and select Edit Values.

2. In the configuration of the step:

   1. Select the city column.

   2. Under Edit values by, select Replacing.
   3. From the dropdown below, select Dynamic value.
   4. In the Type value field, paste the following Jinja code:
      

      {% if root["city"] != "" %}{{ root["city"] }}{% else %}unknown{% endif %}

   

3. Preview the changes in the Result tab.

4. Confirm by clicking Apply.

Change data

As in the previous step, use Edit Values to modify the data. Add a separate rule for each of the remaining columns: language, payment_cash, and payment_online. Replace the language codes with language names and the payment flags with values that will be merged into payment_info.

1. On the right panel, select the previously created Edit Values step and click Add rule. In the configuration of the rule:

   1. Select the language column.

   2. Under Edit values by, select Replacing.
   3. From the dropdown below, select Dynamic value.
   4. 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 %}

   5. Preview the changes in the Result tab and click Apply.

   

2. On the right panel, select the previously created Edit Values step and click Add rule. In the configuration of the rule:

   1. Select the payment_cash column.

   2. Under Edit values by, select Replacing.
   3. From the dropdown below, select Dynamic value.
   4. In the Type value field, paste the following Jinja code:
      

      {% if root["payment_cash"] == "1" %}cash{% elif root["payment_cash"] == "0"%}{% endif %}

   5. Preview the changes in the Result tab and click Apply.

3. On the right panel, select the previously created Edit Values step and click Add rule. In the configuration of the rule:

   1. Select the payment_online column.

   2. Under Edit values by, select Replacing.
   3. From the dropdown below, select Dynamic value.
   4. In the Type value field, paste the following Jinja code:
      

      {% if root["payment_online"] == "1" %}online{% elif root["payment_online"] == "0"%}{% endif %}

   5. Preview the changes in the Result tab and click Apply.

After applying the changes, you will get an updated file:

Merge Columns

Use the Merge Columns step to merge payment_cash and payment_online into one payment_info column.

1. On the right panel, click Add step and select Merge Columns.
2. In the configuration of the step:
   1. Select the payment_cash and payment_online columns.
   2. In the New column name field, enter payment_info.
   3. Enable Remove original columns.

3. Preview the changes in the Result tab.

4. Confirm by clicking Apply.

Preview and save the transformation

1. Preview the final results in the Output tab.
2. In the upper right corner, click Save.

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 Hub > 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. Select the Run once option.
   2. Select Immediately.
   3. Confirm by clicking Apply.

   

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.

Select the transformation

1. Add a Data Transformation Studio node.
2. In the configuration of the node, keep the default From workflow context option.
3. In the editor, click Templates library:
   1. Select the transformation you have created before.
   2. Click Use template.
4. Confirm by clicking Apply.

Add Import Profiles and finishing node

1. Add the Import Profiles node.
2. Optionally, in Advanced settings, you can change the import mode.
3. Add the End node.
4. In the upper right corner, click Save & Run.
   

   

Check the use case set up on the Synerise Demo workspace

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

If you’re our partner or client, you already have automatic access to the Synerise Demo workspace (1590), where you can explore all the configured elements of this use case and copy them to your workspace.

If you’re not a partner or client yet, we encourage you to fill out the contact form to schedule a meeting with our representatives. They’ll be happy to show you how our demo works and discuss how you can apply this use case in your business.

Generated events

This use case generates approximately 5 events per workflow execution: automation.clientStartPath (~1), automation.clientPathStep (~3), automation.clientEndPath (~1).

Read more

• Customer properties
• Data Transformation
• Jinjava inserts