Exporting reports to SFTP server

Published May 27, 2022
Modules
Difficulty
Selected Clients
mig logoccc logomediaexpert logo

The Reports module in Synerise gives lots of opportunities to create custom reports adjusted to your business needs and the data you store in the workspace. Thanks to data transformation and automation, you can create the mechanism of exporting reports to your external resource cyclically.

In this use case we will describe how to export a report to an SFTP server every day. We will also create a data transformation diagram which changes the name of a column in the report before sending it.

Note: Completing this procedure requires some knowledge on sending API requests using cURL, Postman, or similar tools.

Prerequisites


  • Create a catalog in your SFTP server in which your reports will be stored.
  • Create a report that will be exported to the SFTP server.
    Tip: The time scope of the report should be adjusted to the frequency of exporting data. For example, if you export the report every day, it should have the time scope set to Yesterday - thanks to this, you will export data each day from the previous day.
  • Create a workspace API key with the permission required by the “Preview report as CSV method.

Process


  1. Prepare a file with sample data.
  2. Create data transformation diagram.
  3. Create a workflow.

Prepare a file with sample data


In this part of the process, you need to prepare a file with sample data which will be used to create Data Transformation later.

  1. Go to Analytics icon Analytics > Reports.
  2. From the list, select the report you created for exporting.
  3. Click the Preview tab.
  4. Click Download CSV.
  5. Open the file in text editor and remove rows from it until 10 are left. This copy is only needed as a file sample for creating the Data Transformation.

Create data transformation diagram


In this part of the process, we define the rules of modifying data before sending it to the SFTP server. Each report exported from Synerise has a column named Value, which contains the value of metric used in the report. For the purposes of this use case, we will change the name of this column to number of clicks.

The data transformation diagram which is the output of this part of the process is used later to automate sending the data.

  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

The Data input 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 prepared in this step.
  3. Change the default delimiter to ;.
    The configuration of the Data input node
    The configuration of the Data input node
  4. Click Generate.

Change the name of the column

  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. Click Add column.
    3. Select the Value column.
    4. Under Edit values by, from the dropdown list, select Replacing.
    5. In the text field, enter number of clicks.
      The configuration of the Rename column node
      The configuration of the Rename column node
    6. Confirm by clicking Apply.

Add the finishing node

This node lets you preview the output of the modifications to the sample data.

  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: You have published a Data Transformation diagram and it can now be used in a Workflow.
    The diagram of data transformation
    The diagram of data transformation

Create a workflow


In this part of the process, you will create the workflow that exports report data to SFTP server every day at 6:00 AM.

  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. In this use case, it’s every day at 6:00 AM. The workflow will automatically launch everyday at 6:00 AM.
    The configuration of the Scheduled Run node
    The configuration of the Scheduled Run node
    1. Confirm by clicking Apply.

Define the report that will be exported

In this part of the process, we need to define an HTTPS request in the HTTPS - Get File node which retrieves a CSV file with report data. The full documentation of this request is available here.

  1. Add a HTTPS - Get File node.
  2. In the configuration of the node:
    1. From the Method dropdown menu, select POST.
    2. Enter the URL: https://api.synerise.com/analytics-backend/analytics/v4/reports/preview-csv
    3. From the File format dropdown menu, select CSV.
    4. Change the default Delimiter to ;.
    5. Add the JSON body of the request:
      1. In a new browser tab, open the report you want to export.
      2. Open the browser’s developer tools and go to the tab where you can preview HTTP requests.
      3. In the report, go to the Preview tab.
      4. In the developer tools, locate the request to the analytics/v4/reports/preview-csv endpoint and copy its request body.
      5. Paste the request body into the Body field of the automation node.
        Tip: You can also build the request body manually, according to the documentation.
    6. In the Authorization section, choose the API key with the required permissions (according to the documentation).
    7. Leave the rest of the settings as default.
    The configuration of the HTTPS - Get File node
    The configuration of the HTTPS - Get File 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.

    The configuration of the Data Transformation node
    The configuration of the Data Transformation node
  3. Confirm by clicking Apply.

Configure settings for SFTP protocol

In this part of the process, we will configure the settings for an SFTP connection so that your report, after passing through transformation rules, can be stored in the external resource.

  1. Add the SFTP - Send 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 that the report will be saved as.
    5. If needed, in the File name suffix, select the suffix of the file name.
      Tip: We recommend choosing the Date suffix, which adds the current date to the file name, so that the names of your files do not repeat in the catalog (files will be sent every day) and are easier to browse.
    6. From the File format dropdown list, select the CSV format.
    7. Change the default Delimiter to ;.
    8. In the Authentication section, select the method of authentication.
    9. Confirm by clicking Apply.

Add the finishing node

  1. Add the End node.
  2. In the upper right corner, click Save & Run.
    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 report created for the purposes of this use case in Synerise Demo workspace.

Also, you can check the created workflow, and data transformation rules.

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