Using the Google Analytics API to build site visit statistics

When working on one of the “patronized” sites, it became necessary to build a kind of internal statistics system for visiting the site’s pages. Google Analytics came to the rescue, or rather Google Analytics API.


Actually, the task was as follows:

• there is a leased dedicated server with a small supply of resources, a self-written (inherited) CMS, implemented using PHP and MySQL, “not very much” optimized for high loads
• it is necessary to build the correspondence of the document id => number of unique views of the document per day
• keep statistics also for week, month, year

Immediately it should be noted that for a long time the “built-in” mechanism of counting and storing these statistics was used, which was periodically copied, optimized, added, transferred and so on ...
')
The result was the decision to use as a repository statistics of third-party services (the final version was a hybrid with a buffer table in the database). After some deliberation, it was decided to use Analytics through the API.

In order not to reinvent the wheel, I decided to use this library to access Analytics data.

Further, let the URL of all pages for which statistics are needed is unified - www.site.ru/%id%.html , where id is a strictly integer parameter. If necessary, replacing one regexp this requirement is easily bypassed.

For example, let all materials for which statistics are needed are in the same table art (id, name, ...) in the database and the id of this table matches the id in the URL.

Next, create a buffer table for storing statistics for yourself:

CREATE TABLE `art_stat` (
`art_id` INT NOT NULL ,
`day_stat` INT DEFAULT '0' ,
`week_stat` INT DEFAULT '0' ,
`month_stat` INT DEFAULT '0' ,
`year_stat` INT DEFAULT '0' ,
UNIQUE (
`art_id`
)
);

* This source code was highlighted with Source Code Highlighter .

Further, I provide the code itself with minimal comments.

<?php

set_time_limit(0);

require_once 'start.php' ; //

require_once 'gapi.class.php' ; //

// -
define( 'ga_email' , '...' );
define( 'ga_password' , '...' );
define( 'ga_profile_id' , '...' );

//
// ,
// d -
// w -
// m -
// y -
set_stat( 'd' );

function set_stat( $time_limiter = 'd' ) {

switch ( $time_limiter ) {
case 'd' :
// Day
$start_stamp = mktime( date( "H" ), date( "i" ), date( "s" ), date( "n" ), date( "j" )-1, date( "Y" ) );
$field = 'day_stat' ;
break ;
case 'w' :
// Week
$start_stamp = mktime( date( "H" ), date( "i" ), date( "s" ), date( "n" ), date( "j" )-7, date( "Y" ) );
$field = 'week_stat' ;
break ;
case 'm' :
// Month
$start_stamp = mktime( date( "H" ), date( "i" ), date( "s" ), date( "n" )-1, date( "j" ), date( "Y" ) );
$field = 'month_stat' ;
break ;
case 'y' :
// Year
$start_stamp = mktime( date( "H" ), date( "i" ), date( "s" ), date( "n" ), date( "j" ), date( "Y" )-1 );
$field = 'year_stat' ;
break ;

} // End switch

$end_date = date( 'Ym-d' );
$start_date = date( 'Ym-d' , $start_stamp );

echo "From " . $start_date . " to " . $end_date . "<br>" ;

$ga = new gapi(ga_email,ga_password); //

$start_index = 1;
$max_results = 500;

$filter = null ; // . , ,

// GA
while ( $ga->requestReportData(ga_profile_id, array( 'pagePath' ), array( 'pageviews' , 'uniquePageviews' ), array( '-Pageviews' ), $filter, $start_date, $end_date, $start_index, $max_results) ) {

$request = $ga->getResults();
foreach ( $request as $page ) {
if ( preg_match( "/^\/(\d+)\.html$/is" , $page, $matches ) ) { // URL

$art_id = $matches[1]; // , ID URL ID

//
mysql_query( "UPDATE art_stat
SET " . $field . " = " . $page->getuniquePageviews() . "
WHERE art_id = " . $art_id
);

if ( mysql_affected_rows() == 0 ) {
mysql_query( "INSERT INTO art_stat ( art_id, " . $field . " )
VALUES ( " . $art_id . ", " . $page->getuniquePageviews() . " )"
);
} // End if

} // End if

} // End foreach

$start_index += $max_results;

} // End while

} // End function set_stat

require_once 'end.php' ; //

?>

* This source code was highlighted with Source Code Highlighter .

We hang the script on crowns, having previously prepared it, and we obtain regularly updated statistics on the materials on our website.

Finally, I will give an example of a request to get the most popular for the week.

SELECT a.id, a.name, s.week_stat AS cnt
FROM art a LEFT JOIN art_stat s ON ( s.art_id = a.id )
ORDER BY cnt DESC

* This source code was highlighted with Source Code Highlighter .

Also in this way, you can try to assess the decrease / increase in user interest in specific materials of the site and other related parameters.

PS This is the first post, so please do not judge strictly