Piwik Custom Data Tracking

In this post I will describe how I used the excellent open source engine Piwik for tracking my own custom data. Piwik is a platform for tracking and displaying website analytics. I use it to track the traffic of eLibrary, so I thought, why not also use it to monitor my own data?

My goal is to display the evolution graph of my registered and active users, over time. In addition, I want the data to be in correltation with the selected date’s period (day, week, etc.)

So, I’ll start by showing the end result:

Data Source

The data I want to display comes from several tables, hosted on a different database than the Piwik’s one.
I will describe what I did with one table, registered_users, as the concept for other tables is very similar.

The table structure is as follows:

mysql> desc registered_users;
+------------+--------------+------+-----+---------------------+----------------+
| Field      | Type         | Null | Key | Default             | Extra          |
+------------+--------------+------+-----+---------------------+----------------+
| when       | datetime     | NO   |     | 0000-00-00 00:00:00 |                |
| email      | text         | NO   |     | NULL                |                |
| can_use    | tinyint(1)   | YES  |     | 0                   |                |
| valid      | tinyint(1)   | YES  |     | 0                   |                |
| activation | varchar(40)  | YES  |     | NULL                |                |
| idx        | int(11)      | NO   | PRI | NULL                | auto_increment |
+------------+--------------+------+-----+---------------------+----------------+

Now, to create graph, we need a list of values and dates, so I started with this query:

SELECTDATE_FORMAT(`when`, '%Y-%m-%d'), COUNT(idx)
FROM registered_users
WHERE `when` >= ? AND `when` <= ?
GROUP BY DATE_FORMAT(`when`, '%Y-%m-%d')
ORDER BY DATE_FORMAT(`when`, '%Y-%m-%d') DESC

But the output lacked dates with 0 values, so I created another table, calendar:

CREATE TABLE `calendar` (
    `datefield` date DEFAULT NULL
)

And I filled it with values using the following code:

#!/usr/bin/php
<?php
echo "Starting\n";

$mysqli = new mysqli("localhost", "dont",  "think", "so");

$start_date = '2006-01-01';
$check_date = $start_date;
$end_date = '2015-01-01';

while ($check_date != $end_date) {
        $check_date = date ("Y-m-d", strtotime ("+1 day", strtotime($check_date)));
        $query = "INSERT INTO calendar (datefield) VALUES('$check_date');";
        #echo "$query\n";
        $mysqli->real_query($query);
}
echo "Done\n";
?>

So, the polished query would be:

SELECT datefield, COUNT(idx)
FROM calendar LEFT JOIN registered_users
		ON datefield = DATE_FORMAT(`when`, '%Y-%m-%d')
WHERE datefield >= ? AND datefield <= ?
group by datefield
ORDER BY datefield ASC

Creating a New Piwik Plugin

There are two ways to create a plugin. The first would be to write everything from scratch, good luck with that one. The second would be to duplicate the example plugin that comes with the framework.
I used the ExampleUI as the basis for my plugin, as it was the most convenient to work with.

Start by running the following commands from the shell to duplicate the plugin, and make sure it can run.

cd piwik/plugins
cp -R ExampleUI eLibrary
cd eLibrary
mv ExampleUI.php eLibrary.php
# Rename the classes from ExampleUI to eLibrary
sed -i s/ExampleUI/eLibrary/ API.php
sed -i s/ExampleUI/eLibrary/ Controller.php
sed -i s/ExampleUI/eLibrary/ eLibrary.php

To check everything is ready for the next step, enable the plugin in the setting menu (Settings->Plugins).

Pouring Content Into the Plugin

We start by adding our new menu to the master file (eLibrary.php).
Find the addMenus function, and add your own button:

        function addMenus()
        {
                $menus = array(
                        'Users Evolution' => 'myEvolutionGraph', /* My menu button */
                );
                ...

The above code basically referenced the menu item with title “Users Evolution” to the (currently) undefined function myEvolutionGraph.

Next, we edit the Controller.php, and add the function we just referenced myEvolutionGraph.

        function myEvolutionGraph()
        {
                echo "</pre><h2>Users Evolution</h2><pre>";

		// Create the graph view, and get the data from eLibrary.getUsersEvolution
                $view = $this->getLastUnitGraph($this->pluginName, __FUNCTION__, 'eLibrary.getUsersEvolution');

                // Choose which columns to display, and their order
                $view->setColumnsToDisplay( array('active', 'registered') );

                // Set labels for the graphs displayed
                $view->setColumnTranslation('active', "Active Users");
                $view->setColumnTranslation('registered', "Registered Users");

                return $this->renderView($view);

        }

We just defined the controller for displaying the graph. The data is received by calling the function getUsersEvolution, so let’s write it.

Sticking to the Piwik standard, we define getUsersEvolution in the API.php file.
The principle idea behind that data fetching, is that we return a table, with several rows. The column that will be used as the X axis is named label, and the rest of the columns will be different curves on the Y axis.
The function receives two variables, which are used to define the periods of the displayed data ($period), and the final date displayed ($date).
Next, I define the queries, but in a slightly different way than previously discussed. This is because I don’t want to display a value per date, but rather a value per dates range, or period.
So i defined the queries to summarize the values for a specific period, and I iterate over the sub-periods using the Piwik API.

        public function getUsersEvolution($date, $period)
        {
                $period = new Piwik_Period_Range($period, $date);

				// The first query, returns the sum of registered users per date range
                $query1 = "SELECT SUM(CC) FROM (SELECT datefield, COUNT(idx) As CC
                        FROM calendar LEFT JOIN registered_users ON datefield = DATE_FORMAT(`when`, '%Y-%m-%d')
                        WHERE datefield >= ? AND datefield <= ?
                        group by datefield
                        ORDER BY datefield ASC) AS AA";

				// The second query, returns the sum of active users per date range
                $query2 = "SELECT SUM(CC) FROM (SELECT datefield, COUNT(distinct guid) As CC
                        FROM calendar LEFT JOIN activity ON datefield = DATE_FORMAT(`when`, '%Y-%m-%d')
                        WHERE datefield >= ? AND datefield <= ?
                        group by datefield
                        ORDER BY datefield ASC) AS AA";

				// Set my DB connection
                $mysqli = new mysqli("localhost", "dont",  "think", "so");

				// Create the data table
                $dataTable = new Piwik_DataTable();

				// Itereate over the sub-periods
                foreach($period->getSubperiods() as $subPeriod)
                {
						// Get the start and finish dates of each sub-period
                        $dateStart = $subPeriod->getDateStart()->toString('Y-m-d'); // eg. "2009-04-01"
                        $dateEnd = $subPeriod->getDateEnd()->toString('Y-m-d'); // eg. "2009-04-30"

                        $stmt=$mysqli->prepare($query1);
                        $stmt->bind_param("ss", $dateStart, $dateEnd);
                        $result = $stmt->execute();
                        $stmt->bind_result($countRegistered);
                        $stmt->fetch();
                        $stmt->close();

                        $stmt=$mysqli->prepare($query2);
                        $stmt->bind_param("ss", $dateStart, $dateEnd);
                        $result = $stmt->execute();
                        $stmt->bind_result($countActive);
                        $stmt->fetch();
                        $stmt->close();

						// Add a row to table, with all the desired columns
                        $row1 = new Piwik_DataTable_Row();
                        $dataTable->addRowFromSimpleArray( array('registered' => $countRegistered , 'active' => $countActive ,  'label' => $subPeriod->getLocalizedShortString(), 'date' => $subPeriod) );
                }
                $mysqli->close();

                return $dataTable;
        }

That’s is, now the plugins should work and display the desired data. The same concepts could be used to display several graphs on the same page, by adding more to the $view defined in the controller.
In addition, we can use the data table we created to display pie charts, tables, etc. Just go over the ExampleUI plugin for more ideas.

Making a Widget

We created our nice little evolution graph, and it’s so useful that we want to display it on the dashboard. So we start by adding a widget to the plugin.

In the master page (eLibrary.php) edit the function getListHooksRegistered and add the function addWidgets:

        function getListHooksRegistered()
        {
                $hooks = array(
                        'WidgetsList.add' => 'addWidgets', /* Add this line*/
                        'Menu.add' => 'addMenus',
                );
                return $hooks;
        }

        // Add this function
        function addWidgets()
        {
                Piwik_AddWidget('eLibrary', 'Users', 'eLibrary', 'myEvolutionGraph');
        }

Now you can add the graph to the dashboard. You’ll find it under Dashboard->Add a widget->eLibrary->Users.

Notice that there is a problem with the labels on the X axis – there are too many so they overlap. The proper value would be a label for every 7 points, rather then the default label for every 2 points. Currently this is still a problem for me, as I haven’t found a way to fix it. If I do, I’ll let you know.