Matomo tracks a lot of valuable data about the behaviour of your website visitors. If you’ve ever wondered about how to visualise the data tracked by Matomo in a third party tool like Google Data Studio then this is the guide for you. In this guide, we are going to take your through the steps to connect your Matomo to Google Data Studio.

With Matomo On-Premises – especially when you have access to the database, it is easy to connect Matomo to Google Data Studio using MySQL connector. The challenge is when you do not have access to the database. Here is the workaround of importing data in Google Data Studio using Google sheets.

Note: Some Matomo reports contain a lot of data that Google sheets may not accept without further modification of the sheet. The code supplied is not supported by Matomo as this is just an example of how to achieve the connection.

Creating a script that calls Matomo API and import data in Google Spreadsheets

Matomo API gives you the ability to export all the data into multiple formats. How to export?

In the example below, we’re going to export a Custom report.

1. Click on “Export” icon.

2. Choose “CSV” under “Export Format”, choose “All” under “Row Limit”, click “Show Export URL” and then copy the “Export URL”.

3. Open a blank “Google Sheet” and name it appropriately.

4. Click on “Tools > Script Editor”.

5. In the “Script Editor”, paste the code find below. The code supplied is not supported nor created by Matomo as this is just an example of how to achieve the connection.

function myFunction() {        
  var csvUrl = "EXPORT_URL";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

6. Replace the “EXPORT_URL” in the above code with the URL copied in STEP 2 and append &convertToUnicode=0 at the end of the URL. Note: you can use &date=yesterday if you just want to report on “yesterday’s” data
Or if you want to report on several time periods (the last few days, or last weeks, or last months) you can use &period=month&date=last30. Learn more about date and period API parameters.

7. Click the save icon and give your script a name when prompted.

8. Click on the Run icon and when prompted, proceed and give the script authorisation to connect to an external service.

9. Data should be imported in the Google sheets at this instance. You would need to run this manually each time to have data imported into the Data Studio.

Note: To automate the above task or schedule to import the data in a timely manner, you may need to configure a trigger in Data Studio. For example, create a report daily for the previous day so you could run the script at Midnight every night. Please note, the script replaces any existing data, you may want to alter the script to create a different spreadsheet each time or append this spreadsheet.

Next, we’re going to create a trigger to automate the import process into the Google Sheet

Creating a Trigger (Optional)

If you don’t wish to create a trigger, you can skip to Connect to Data Studio

1. Click on “Current project triggers”.

2. Click on “Add Trigger”.

3. Choose the frequency to run the import.

4. Save the Trigger.

Connect the Google Sheet to Google Data Studio

1. Open Data Studio.

2. Click on “Create”.

3. Choose “Data source”.

4. Click on “Google Sheets”, you may need to authorise Data Studio to access Google Sheets.

5. Select the Spreadsheet, which was created earlier (see section above) and leave the default options selected.

6. For each field, check that the correct data type is selected or update it appropriately.

7. Click “Create Report”.

8. Click on “Add to report”.

9. You should now see the report with the data added.

Congratulations, you can now import and visualise your websites and visitors data within Google Data Studio.