PHP Analytics

Web intelligence with Urchin & PHP Analytics

Urchin and PHP Analytics are commonly used in conjunction: Urchin for the standard data collection and PHP Analytics for its flexibility. Let’s get started!

 

Prepare the SQL datamart

Let’s say you have a social network. We want to track down the number of clicks on the “like” button, and be able to count by the type of media liked (image, video) and some characteristics of the user that liked the link (sex, age).
The model should look like:

CREATE TABLE tracking
(
"key" bigserial NOT NULL,
user_id integer,
media_type integer,
count_like_click integer
);

CREATE TABLE user
(
id bigserial NOT NULL,
name VARCHAR(50)
);

CREATE TABLE media_type
(
id bigserial NOT NULL,
name VARCHAR(50)
);

 

Virtual url in Google Analytics

Write this snippet of Javascript. Here is a sample corresponding to what we built:

<script type="text/javascript">
function like_click_listener(){
user_id = "42";
media_type = "1";
urchinTracker("/share_click/" + user_id + "/" + media_type);
}
</script>

 

Bring Urchin data to PHP Analytics

All you have to do is to query the API: https://secure.urchin.com/helpwiki/en/Data_API.html

The url follows this template:

http://server.com/api/urchin/v1/ReportService/data?login=$login&password=$password&ids=$PROFILE_ID&start-index=$start-index&max-results=$max-results&start-date=$start-date&end-date=$end-date&dimensions=u:request_stems&metrics=u:pages&table=1

Notice we query table 1, dimension u:request_stems and metrics u:pages. Then you can use your favorite tool to load this webservice data to the PHP Analytics database. It can be Talend Open Studio, Kettle or any ETL. We prefer a home made PHP script for such a simple example.

 

The metamodel

Now we will put analytical info over the database: there we turn our standard SQL into a full R-OLAP cube.


<?xml version="1.0"?>
<Schema name="MyWebsite">
<Cube name="Mega track">
<Table name="tracking" />
<Dimension name="User" foreignKey="user_id">
<Hierarchy hasAll="true" primaryKey="id">
<Table name="user" />
<Level name="User Name" column="name" type="String" uniqueMembers="true" />
</Hierarchy>
</Dimension>
<Dimension name="Media type" foreignKey="media_type">
<Hierarchy hasAll="true" primaryKey="id">
<Table name="media_type" />
<Level name="Media type" column="name" type="String" uniqueMembers="true" />
</Hierarchy>
</Dimension>
<Measure name="Like Clicks" dimension="Measures" column="count_like_click" aggregator="sum" formatString="#,##0" />
</Cube>
</Schema>

Now you can easily enrich your report with referential data, add a timeline …

Happy hacking!