Update 2020: a new guide is available for How to import data from Matomo API in Google Spreadsheets and connect the Google Sheet to Google Data Studio.
This post is from a Guest Blogger Arthur Lee from branica.com
You can say that I am a bit of an analytics freak since my background is financial and I typically work with analytical data and with spreadsheets. Now as a part-time webmaster, I find that using analytics is pretty important to optimize a site. I was intrigued with Matomo (Piwik) when it first arrived – especially as an alternative to Google Analytics.
When I was playing with Matomo on my site, I was looking to show my stats without having to give anonymous users view access to my stats. At the moment, it is only possible to show PiWik widgets publicly only if you give anonymous users view access. So I was curious to see if I could leverage Google Spreadsheets and the Matomo API to publish data and charts from Google Spreadsheets.
I have say that with the PiWik API is was really easy to use within Google Spreadsheets. Since Spreadsheets has the handy importXML function, it was a nice fit since the PiWik API can product its data in XML. It is also easy to change the Matomo API using normal spreadsheets functions. The result is the following Google Spreadsheet which has 3 pages to show you an example:
http://spreadsheets.google.com/ccc?key=pxxUvud3iB_ZgeTOMg64jdw#
- Page 1 – Setup / Configuration
- Page 2 – Dashboard Example: This example shows how to make dynamic reports since you can change the number of “Top Website Referrers” or “Top Keywords”.
- Page 3 – Chart Example: In this example, the chart can be published anywhere as a simple image. No need for flash like Matomo widgets.
The above spreadsheet is in view only mode so you’ll need a Google account to make a copy of the spreadsheet to play around with it. Also, I have noticed a few times that Google may at times not get the XML data – just be patient. When you look at the spreadsheet, I have put in a simple setup page so you can change the reports for your site on Matomo. Just enter in your url where Matomo is installed, siteid and token_auth (that you can find on the API page in Matomo) and the reports will refresh automatically with your data!
The nice aspect of this is if you are using Matomo for commercial customers is that you can create all kinds of dynamic reports which operate in real time. I think the possibilities are pretty endless now. The only downside is the potentially performance impact of using many Matomo API calls.
Happy Reporting & Analysis!
Thank you Arthur for your guest blog post. If you would like to be a guest blogger on the Matomo blog (more than 1,200 readers!), please send us an email at hello@piwik.org ; we would love to publish your tutorial, Matomo hack or other Matomo web analytics findings!
