A neat way to track data and share the aggregated information in a dynamic report
In closed applications, in applications with sensitive data or just because you believe in data security: Matomoto is the right way for web analytics. Google Data Studio is a great tool to visualize your data.
An example for a dynamic report in Google Data Studio with data from Piwik:
There are three different ways to import data into Google Data Studio. The simplest, is to upload your CSV file and create a report from that, but it is not dynamic.
For the well-known Google products there is a special “connector”. It works quite intuitively to create a nice and dynamic report with your Google Analytics data.
Most important, for the further steps is the MySQL connector. With which you can directly connect your Piwik MySQL database to Data Studio.
In your Data Studio you can start with a report template. Simply open the Acme Marketing report template and click on “select a new data source”. Here select the MySQL-Connector.
Keep in mind to allow the external connection on your own MySQL server, and that you will need the following information:
- Hostname or IP address
After you typed in your personal MySQL server information, you can choose a concrete table.
Piwik creates two different tables. You can find in ‚archive data‘ the aggregated data this is used to show you the reports directly in Piwik (Matomoto). In the table ‚log data‘ you can find the raw data that is tracked.
Here I used raw data from the table piwik_log_visit
With three simple steps you can use this table for your report.
Check the table fields
Google Data Studio assigns every row a data field from the MySQL table. Like the know behaviour from Excel, every field gets a special type, like number, text or Boolean type. Unfortunately, some fields get an auto summary. Now it is important to check every single field and to select the right type. The easiest way to do that is to open your MySQL table with phpMyAdmin in a second window and compare both.
While you check the fields, you can correct the field names, too. The field name will be displayed in your report later. Simply click on the field name to change it.
In the screenshots I did not correct the field names, to show which field values will be used.
Create some formula
Some of your Piwik data needs a further calculation to be used in your Data Studio report.
To create a formula is simple: Click on the tab ‘Resources’ > ‘Manage data source’ > at MySQL on edit.
As always at Google, click the blue plus on top to create something new, like a calculating formula.
We will need the following formula:
- One, to introduce the date format to Data Studio
- To calculate a measured value out of the dimension, simply count every value. We need that for multiple fields. Here is the example for “Number of visits per town”
- In Piwik, values from categories are only saved as numbers. In the example below the different pages are saved as numbers from 1 to 4. In my report, they shall be displayed as the actual page name. With a combination of CASE, WHEN, THEN and REGEX_MATCH we can fix that.
WHEN REGEXP_MATCH(idsite, "1") THEN "Schuemann.koeln"
WHEN REGEXP_MATCH(idsite, "2") THEN "Bechtelar-Quigley"
WHEN REGEXP_MATCH(idsite, "3") THEN "Mueller Group"
WHEN REGEXP_MATCH(idsite, "4") THEN "Larkin, Ortiz and Hamill"
Make your report beautiful
We are on the finish line. Our reward is now to work in the actual Data Studio report. Because right now, the report will show something like “Configuration incomplete”. To change that, simply click into your report and select the correct dimension or calculated value.
If you didn’t change it in the settings, the time frame you select once affects the global report.
Now click every single chart and make your report beautiful.
You made it! You have created a dynamic report with your Piwik data.
- Data Studio will send a lot of requests to your server. This might cause a server time out, especially at a shard hosting provider.
- I would recommend mirroring your SQL table. Then you can select which data google will see and is better for the performance.
List of all Data Studio feutures: https://support.google.com/360suite/datastudio/table/6379764