English
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:
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_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 hello@piwik.org ; we would love to publish your tutorial, Piwik hack or other Piwik web analytics findings!
Matthieu Aubry
While studying in India in 2006, Matthieu had the idea of creating an open source web analytics alternative to Google Analytics. In 2007 he released the first public beta version of Piwik. Matthieu now leads the project from New Zealand, where he likes to spend his days coding new features from the Piwik Roadmap, and helping users and customers use Piwik effectively. Find him on twitter or github.
Subscribe to our rss feed: 
Posts or you can Suggest a topic to write about in the blog or See list of Features
Zaahid Kara Says:
o among the cool option of this pad is it may know whenever metal, water or hand is lying on its floor and it will spontaneously shuts off the pad Adauga Anunturi Gratuite Bacau
devlet hastaneleri Says:
of the progressive/socialist/democrats continues on and
Lazer Epilasyon Seansı Says:
This weekend, look into how you can extend Piwik’s functionality to benefit your site.
herbs for weight loss Says:
Why is this Blog Engine Blogs brimful of Email.? I score seend lot’s of parcel same this… Few of them has lineament of Articles but noneffervescent equal…
Arts Blog Says:
This is a great little website I can not believe that I didn’t find it earlier.
referencement entreprise drôme Says:
merci pour ce tutoriel excellent
la martina Says:
Besten Polos können Sie je sehen Sie alle sehen können BuyLa martina
Ropa la martina Says:
Besten Polos können Sie je sehen Sie alle sehen können Ropa La martina
opinie Says:
This thing is cool, works flawlessly until know and looks great. This is really good job!
Best regards
Opinie
hostel Says:
I`m using Piwik and custom reports are really usefull for me . ( I was using GA for long time but … I dont wan`t BIG BROTHER to know everything )
Tata z Wyprawki Says:
Great toot, easy to install, easy to configure, available in my language :) Thi is really good job!
Cheers
Rummy City Says:
Too much complicated for me :(
I guess I have to read more about the subject.
porady Says:
very very good!
great yes beatiful job
kosmodisk Says:
We downloaded the software and will now
graffiti letters Says:
i used it, and i can say it is better and easy than google
la martina Says:
I had no idea that Google docs could be used this way.
türk sex Says:
thanx admin
onlinesikis Says:
tnx
onlinesikis Says:
thanx you admin
referencement Says:
oui un grand merci pour ce post
Nice days Says:
thanx you admin
referencement naturel Says:
Merci pour ce post tr
My Review Says:
More than that, I think it should be encouraged
Pharmacy Reviews Says:
Written at the begining of the year yet still very relevant, you should add your twitter link, ill follow for sure :)
Marco Menke Says:
Looks pretty good to me :) byebye Excel
Arkadaş Says:
i need to this editgrid, its so good.
EnjoySilk Says:
It’s just good to see open source products making their way into the mainstream, especially in the web analytics field.
Bramki obrotowe Says:
I’m seriously into it.
Goran Web Says:
Definitely going to download the software and give it a go. I am excited to know that there are other free analytics software available other than Google. Personally EditGrid is a better alternative.
Download Firefox Says:
Keep on the good work, I think about how to contribute…
Kind regards,
Amit Pbx Says:
That’s sounds very interesting!!
I’m going to download the software and give it a test drive.
King of tired from using Google and I’m happy to see an open source project that covers these issues.
Kagimusha Says:
Блин чего только не бывает везде и вокруг. Я бы сказал, как то…
hi Says:
very very bad
and
very very good
Raffael Luthiger Says:
If you want to switch away from Google why not implement http://www.codeplex.com/PHPExcel ? You can generate many different spreadsheet outputs with it.
Matthieu Says:
keusta, we’re always very interested to receive marketing help, logo design, etc. We are still not sure about the Piwik logo and definitely looking for alternatives and new propositions. Please send your propositions to hello att piwik.org !
Navin Poeran Says:
I have a question though, if i publish the stats, via google docs, will it be updated automatically? when i put the code in my webpage
Branica Says:
Yeah – this is a bit of a timing issue of when this blog post was released. I think it depends of what you want to accomplish.
Simple, easy with not much data, I would probably use Google Docs Spreadsheets.
For more data, I would use EditGrid until Google ups the limit on grabbing external XML calls.
James Says:
Arthur,
Is there a reason why you are only giving an example for Google Spreadsheets and not EditGrid.
From the forum I have used your explanation of using EditGrid to great success, and wonder why you don’t mention them here?
All the best
James