Send campaign analytics and performance data to Google Sheets

Thanks to the data context Jinjava tag, you can export campaign performance data from Synerise to Google Sheets. Automate updating Google spreadsheet with detailed report summarizing key metrics from your campaigns, this integration opens up new possibilities for data analysis and informed decision-making.

This use case shows a workflow configuration that sends daily data referencing webpush campaigns from Synerise to Google Spreadsheets.

Prerequisites

• Check the requirements you must meet to integrate Synerise with Google Spreadsheets.
• Prepare a Google Spreadsheet to which you want to send the data.

Create a workflow

In this part of the process, prepare a workflow that sends statistics to a spreadsheet.

1. Go to Automation > Workflows > New workflow.
2. Enter the name of the workflow.

Define the trigger node

At this stage, we will configure the conditions that launch the workflow. We will create a scheduled workflow, that starts every week on Monday at 9:45 A.M.

1. As the first node of the workflow, add Scheduled Run. In the configuration of the node:
   1. In the Run trigger section, leave the default all time option.
   2. In the Repeat runs section, choose suitable timezone.
   3. To launch the workflow every week at the defined time, click the Every week tab below.
   4. Select the desired day, in our case Monday.
   5. Click Add time and set it according to your needs, in our case to 9:45.
   6. Confirm by clicking Apply.

Configure the Get Statistics Node

As the next node, choose Get Statistics to retrieve webpush campaign statistics from a specified time range.

1. Select the By time range tab.

2. From the Campaign type dropdown list, select Webpush.

3. In the Set time range field, select the time range from which you would like to export the statistics. In our case, in the Relative time range section, select the Last week option.

4. The default setting includes statistics exclusively for campaigns sent through the Communication module. If you also want to include statistics for campaigns launched through the Automation module, enable the Include automation statistics option. If this option is selected, the output will include campaigns set up by Automation that meet one of the following criteria:

   • the date of creation is within the indicated time range,
   • at least one sending event has occurred in the indicated time range.

5. Optionally, if you want to export variants of one campaign as separate records, enable the Breakdown by variants toggle.

   Note: By default, if statistics of a campaign selected for export contains more than one version (due to A/B testing), the statistics for each variant are a single aggregated record.
   
   Campaign statistics data retrieved through the Get Statistics node before sending can be transformed to better align their format with specific requirements. To simplify building validation rules, you can download sample data in various formats (.csv, .json, .jsonl).

6. Name your node Campaigns sent yesterday so it corresponds with the Jinjava in the next step.

7. Confirm by clicking Apply.

Configure Upload Data to Spreadsheet node

1. Click Then and add the Upload Data to Spreadsheet node. In the configuration of the node:
   1. Click Select connection.
   2. From the dropdown list, select the connection.
   • If no connections are available or you want to create a new one, click the Select connection dropdown list, and the Add connection. Find how to do it here.
   • If you selected an existing connection, proceed to the Configure integration and add the finishing node step.

Configure integration and add the finishing node

In this step, fill in the form that allows you to send data from Synerise to a table in Google Sheets.

1. In the Spreadsheet ID field, enter the ID of the spreadsheet to which you want to upload data. You can find the ID in the URL of the spreadsheet.

2. In the Range field, define the range of cells to which the data will be uploaded. The values will be appended to the first empty cell available within the indicated range. The value in this field must be given in the A1 notation, for example Sheet1!A4:A, then the data will be added in a column to A4 and A5 cells in the Sheet1 Spreadsheet.

3. As the Dimension, select Rows.

4. In the Values field, paste the Jinjava that loops over the results of the Get Statistics node:

    
      [ {%- datareference node='Campaigns sent yesterday' maxRows=100 -%}
       {%- for r in datareference_result -%}
       ["{{ r.campaignHash }}", "{{ r.campaignTitle }}", "{{ r.campaignType }}", "{{ r.clickRate }}", "{{ r.showRate }}", "{{ r.sendingTime}}", "{{ r.sendingTimeZone}}", "{{ r.uniqueClickCount }}","{{ r.sendingTime }}", "{{ r.uniqueShowCount }}" "{{ r.uniqueCappingCount }}", "{{ r.uniqueSendCount }}", "{{ r.utm.campaign }}", "{{ r.utm.content }}", "{{ r.utm.medium }}", "{{ r.utm.source }}", "{{ r.utm.term }}"] {%- if not(loop.last) -%},{%- endif -%}
       {%- endfor -%}
       {%- enddatareference -%} ]
           
   

   

5. Click Apply.

6. After the Upload Data to Spreadsheet node, add the End node.

7. Confirm by clicking Save.

8. In the upper right corner, click Save & Run.

Check the use case set up on the Synerise Demo workspace

You can check the workflow configuration directly in Synerise Demo workspace.

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

• Automation
• Automation inserts