Exporting reports to SFTP server

The Reports feature 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 transformation which changes the name of a column in the report before sending it.

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 a transformation.
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 a transformation later.

1. Go to Decision Hub > 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 transformation.

Create a transformation

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 transformation which is the output of this part of the process is used later to automate sending the data.

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.

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

1. Upload the file prepared in this step.
2. Set Delimiter to ; and provide Quotation mark and Escape character.

   

3. Click Apply.

Change the name of the column

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 Value column.
   2. Under Rename by, from the dropdown list, select Replacing.
   3. In the text field, enter number of clicks.

      

   4. 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: You have saved a transformation and it can now be used in a workflow.

   

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 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. In this use case, it's every day at 6:00 AM. The workflow will automatically launch everyday at 6:00 AM.

   

   4. 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 Get File node which retrieves a CSV file with report data. The full documentation of this request is available here.

1. Click THEN and from the dropdown list, select HTTPS - Get File.
2. In the configuration of the node:

   1. Choose HTTPS connection type:

      1. Select Synerise API key.
      2. Below, in the select connection field, choose the API key with the required permissions (according to the documentation).

         

      3. Click Next.
   2. From the Method dropdown menu, select POST.
   3. Enter the URL: https://api.synerise.com/analytics/analytics/v4/reports/preview-csv
   4. From the File format dropdown menu, select CSV.
   5. Change the default Delimiter to ;.

   6. 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.
         
   7. Leave the rest of the settings as default.

   

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.

   

4. 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 the transformation, can be stored in the external resource.

1. Add the Send File node by clicking THEN > SFTP.
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 that the report will be saved as.
   6. 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.
      
   7. From the File format dropdown list, select the CSV format.
   8. Change the default Delimiter to ;.
   9. Confirm by clicking 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 configuration of use case in Synerise Demo workspace:

• Created workflow
• Transformation

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
• Reports