Automated Import of Customer Complaints as Custom Events via SFTP

You can use Automation Hub to synchronize your customers' events thanks to the HTTPS and SFTP integrations that allow you to import data from your external sources to Synerise.

This use case describes how to prepare an automated workflow that launches every day and imports data about customers' complaints to Synerise as custom events. 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 the data transformation that consists of adding and renaming columns in the CSV file.

Prerequisites


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

Process


  1. Create a transformation that will transform data from the .csv file.
  2. Prepare a workflow that imports events from the SFTP server to Synerise.

Create a transformation


In this part of the process, you define the rules of modifying data. Each of the following sub-steps describes the individual changes performed on the file. The transformation 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 transformation and preview its results. With a library of transformation steps 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 Add Column and Rename Columns steps to transform a complaint file so it meets Synerise's import requirements.

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

Add file with sample 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. Upload the file with the sample data. This is the sample used in this article:
    reporter,complaint,date
    test@test.com,the item is faulty,2022-03-09T10:22:06.305+01:00
    
  2. Set Delimiter and provide Quotation mark and Escape character.
    Transformation file upload showing sample complaint data with reporter, complaint, and date columns
    The configuration of the file upload
  3. Click Apply.

Add Column

You can use the Add Column step to create a new column to the file, with a defined value. In this example, we need to add following columns: type, label, action.

  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 unique column name. In our case: type.

    2. Select the Static value option.
      The rows in the new column will be filled with the value you define.
    3. As the value, enter custom.
      Example of the configuration of the Add Column step, type
      Example of the configuration of the Add Column step, type
    4. Before you save the settings, you can check the preview of the file after changes in the Results tab.
    5. Confirm by clicking Apply.
  5. Add two more Add Column steps by repeating steps 1-4 with the following changes:
    • as the first column, add label and as the value to insert, enter complaint
    • as the second column, add action and as the value to insert, enter complaint.reported
Example of the configuration of the Add Column step, label
Example of the configuration of the Add Column step, label
Example of the configuration of the Add Column step, action
Example of the configuration of the Add Column step, action

Rename Columns

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 Columns step to rename the reporter column to client.email.

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

    1. Select the reporter column.

    2. Under Rename by, select Replacing.
    3. In the text field, enter client.email.
      Example of the configuration of the Rename Columns step
      Example of the configuration of the Rename Columns step
  5. Before you save the settings, you can check the preview of the file after changes in the Results tab.
  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:
    Transformation for importing complaint data as custom events
    The transformation configuration

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

Prepare a workflow


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

  1. Go to Automation Hub icon 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 Repeat runs option.
    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, every day at 6.00 A.M.).
      The workflow will automatically launch every day at the specified time.
    Automation Hub Scheduled Run node configuration for triggering complaint data synchronization as custom events
    The configuration of the Scheduled Run node
  3. Confirm by clicking Apply.

Configure settings for SFTP protocol

Use the Get File node to retrieve the file from your server using the SFTP protocol.

  1. Add the Get File node by clicking THEN > SFTP - Get File.
  2. In the configuration of the node:

    1. Select the SFTP connection type as Public key, choose the connection in the Select connection field, and click Next.
    2. Enter the path to your server.
    3. Select the port.
    4. Enter the path to the directory.
    5. Enter the name of the file to import.
    6. From the File format dropdown list, select CSV.
    7. Verify and modify the delimiters if needed.
    The configuration of the SFTP Get File node for importing complaint data
    The configuration of the SFTP Get File node
  3. 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.
    Example of the configuration of the Data Transformation Studio node
    Example of the configuration of the Data Transformation Studio node
  4. Confirm by clicking Apply.

TIP: You can also create a transformation directly in the Data Transformation Studio node instead of selecting a template.

Import events

  1. Add the Import Events node. In the settings of the node, you can check the list of the required 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:
    Automation Hub workflow for synchronizing complaint data as custom events
    The workflow configuration

Check the use case set up on the Synerise Demo workspace


You can check the transformation directly in Synerise Demo workspace.

Also, you can check there the created workflow.

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 6 events per workflow execution: automation.clientStartPath (~1), automation.clientPathStep (~3), automation.clientEndPath (~1), complaint.reported (~1).

Read more


Canonical URL: https://hub.synerise.com/use-cases/synchronize-complaint-data-as-custom-events