Transform and import product data to a catalog

In this use case, we will focus on the process of importing and transforming product data from an .XML file using data transformation. This method of data transformation is beneficial as it eliminates the need for the data to be in a predetermined, structured format.

In this use case, we will make the following modifications to the file:

• Add 2 new columns:
  • discounted: this column will contain the true value for all products which have a value (price) assigned in the g:sale_price column.
  • percentage_discount: this column contains the percentage value of the discount based on g:price and g:sale_price columns.
• Edit Values: Replace the value of g:availability column from 1/0 to in stock/out of stock.

Input data in use case

In this use case, we use two files:

• the complete .XML file in the Google Merchant Format which contains the full set of products. The file contains the following attributes: g:id,g:title,g:description,g:image_link,g:price,g:sale_price,g:availability.
• the sample of the product data in the .XML format. To reproduce this scenario in your workspace, create a catalog and prepare the files.

Process

In this use case, you will go through the following steps:

1. Create a transformation to transform the data in the sample file.
2. Create a workflow to import the .XML file to Synerise.

Create a transformation

In this part of the process, you define the rules of modifying data before sending it to the Synerise based on the sample file. Each of the following sub-steps describes the individual changes performed on the file. We will add following rules:

• Add 2 new columns:
  • discounted: this column will contain the true value for all products which have a value (price) assigned in the g:sale_price column.
  • percentage_discount: this column contains the percentage value of the discount based on g:price and g:sale_price columns.
• Edit Values: Replace the value of g:availability column from “1/0“ to “in stock/out of stock“

1. Go to Automation Hub > Transformations > New transformation.
2. Enter the name of the transformation.
3. Click Upload a new file or drag one here.

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 without a specific goal and you know the structure of the output data, skip this step.

Goals will suggest you the required data for the import into Synerise.

Add file with sample data

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

1. Upload the .XML file created as the part of prerequisites.
2. You can preview the file, then click Apply.

Add the new column

1. On the right panel, click Add step.
2. From the dropdown list, select Add Column.
3. Click the Add Column step.
4. In the configuration of the step:

   1. In the Add Column field, enter the name of the column. In this use case, it's discounted.

   2. From the dropdown list, select Dynamic value.
   3. In the Type value box, add the Jinja code, which adds the true value for all products with g:sale_price attribute in this new column. You can use the code presented below:
      

      {% if root["g:sale_price"] is defined %}true{% endif %}

   4. Leave last select at default (Skip row if error occurred) to skip missing or invalid data which may occur during transformation.
   5. Confirm by clicking Apply.

Add the new column

1. On the right panel, click Add step.
2. From the dropdown list, select Add Column.
3. Click the Add Column step.
4. In the configuration of the step:

   1. In the Add Column field, enter the name of the column. In this use case, it's percentage_discount.

   2. From the dropdown list, select Dynamic value.
   3. In the Type value box, add the Jinja code, which counts the percentage value of the discount based on g:price and g:sale_price attributes and adds it to this new column. You can use the code presented below:
      

      {% if root["g:sale_price"] is defined %}{{ root["g:sale_price"]*100/root["g:price"] }}{% endif %}

   4. Leave last select at default (Skip row if error occurred) to skip missing or invalid data which may occur during transformation.
   5. Confirm by clicking Apply.

Edit Values

1. On the right panel, click Add step.
2. From the dropdown list, select Edit Values.
3. Click the Edit Values step.
4. In the configuration of the step:

   1. Select the g:availability column.

   2. Under Edit values by, select Replacing.
   3. From the dropdown below, select Dynamic value.
   4. In the Type value field, enter the Jinja code which replaces the value of the g:availability attribute from 1 and 0 to in stock/out of stock:
      

      {% if root['g:availability'] == 1 %}in stock{% else %}out of stock{% endif %}

   5. Leave last select at default (Skip row if error occurred) to skip missing or invalid data which may occur during transformation.
   6. Confirm by clicking Apply.

Preview and save the transformation

1. Preview the results in the Output tab.

   

2. In the upper right corner, click Save. Result:

   

Create a workflow

The scenario for this use case describes a one-time import of the .XML file with a product database 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 a 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 to catalog

1. On the Data Transformation Studio node, click THEN.
2. From the list that opens, select Synerise > Import to Catalog.
3. Open Import to Catalog node.
4. Choose the catalog from the list.
5. As a primary key, choose g:id parameter.
6. Click Apply.

Add the finishing node

1. Add the End node.
2. 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 automation process 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

• Automation Hub
• Data Transformation
• Data Transformation Studio node