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 Looker Studio / 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 Looker Studio.

Note: We have recently released an official community connector. This is now the recommended way for most users to connect to Looker Studio. Learn more about the new connector here.

With Matomo On-Premises – especially when you have access to the database, it is easy to connect Matomo to Looker Studio using MySQL connector. The challenge is when you do not have access to the database. Here is the workaround of importing data in Looker 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.

If you are using Matomo for WordPress then this guide will not work as Matomo for WordPress doesn’t support the regular Matomo HTTP Reporting API and instead utilises the WordPress Rest API see our WordPress Rest API reference. You may be able to connect Matomo for WordPress by either choosing “MySQL” as a data source or importing the data into a Google Spreadsheet using the WordPress REST API. The “token_auth” feature is not available in Matomo for WordPress.

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”.

Important note: The token_auth that you see on this export screen is a session token_auth that will only be valid while you are logged in. Please follow our guide to generate a token_auth.

Once you’ve generated an token_auth, you will need to insert the new token_auth that you created in the URL below, for example: &token_auth=your_new_auth_token_here

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

4. Click on “Extensions > Apps Script”.

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.

7. Append to the “EXPORT_URL” the following URL parameter: &convertToUnicode=0 at the end of the URL.

8. Rename the Project and click the save icon.

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

10. 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. Hover over the “Clock icon” on the left. Click on “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 and Click on “Connect”.

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.

Previous FAQ: How do I make the Matomo Analytics RAW data available to my data warehouse?