Using the Piwik API and Google Spreadsheet to generate Excel-like custom reports
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 Piwik when it first arrived – especially as an alternative to Google Analytics.
When I was playing with Piwik 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 Piwik 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 Piwik API using normal spreadsheets functions. The result is the following Google Spreadsheet which has 3 pages to show you an example:
- 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 Piwik 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 Piwik. Just enter in your url where Piwik is installed, siteid and token_auth (that you can find on the API page in Piwik) and the reports will refresh automatically with your data!
The nice aspect of this is if you are using Piwik 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 Piwik API calls.
Happy Reporting & Analysis!
Thank you Arthur for your guest blog post. If you would like to be a guest blogger on the Piwik blog (more than 1,200 readers!), please send us an email at email@example.com ; we would love to publish your tutorial, Piwik hack or other Piwik web analytics findings!