PiWik & Google Spreadsheets - More Integration

[branica]

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 webmaster, I find that using analytics is pretty important to optimize a site. I was intrigued with PiWik when it first arrived - nothing against Google but sometimes having an open source alternative is very attractive.

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 Google 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:

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 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 and the reports will refresh automatically with your data instead of PiWik.

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 potentially is the performance impact of using many PiWik API calls.

Happy Reporting & Analysis!