Display Top URLs With Google Analytics API (PHP)

We can use Core Reporting API to fetch top (Popular) URLs from Google Analytics. Using Google API PHP Client let’s create a simple PHP page that pulls website’s top URLs from Google Analytics and updates MySql table. Once you are familiar, I am sure you can do lot more, I mean just checkout the information you can retrieve in this Reporting API.

Let’s start by creating MySql table called google_top_pages, table contains 4 columns (id, page_uri, page_title, total_views). You can run this query in your PhpMyAdmin to have this table created.

1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS `google_top_pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `page_uri` varchar(60) NOT NULL,
  `page_title` varchar(60) NOT NULL,
  `total_views` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

Configuration

This is important part:

  1. Create Google OAuth API Keys and get Client ID and secret.
  2. Enter redirect url in Google Redirect URI, or you will get redirect mismatch error.
  3. Enable Analytics API in Google APIs Console->Services Page.

In Google Analytics Settings below, enter your site’s Analytics profile id like this : “ga:ProfileID”. Enter number of results you want to fetch, you can play around with dimensions and metrics later and don’t forget to enter MySql details.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?php
########## Google Settings.. Client ID, Client Secret #############
$google_client_id               = '12345678901112.apps.googleusercontent.com';
$google_client_secret           = 'XYZ_1234_abcdXYZ';
$google_redirect_url            = 'http://yoursite.com/update_pages.php';
$page_url_prefix                = 'http://www.sanwebe.com';

########## Google analytics Settings.. #############
$google_analytics_profile_id    = 'ga:123456'; //Analytics site Profile ID
$google_analytics_dimensions    = 'ga:landingPagePath,ga:pageTitle'; //no change needed (optional)
$google_analytics_metrics       = 'ga:pageviews'; //no change needed (optional)
$google_analytics_sort_by       = '-ga:pageviews'; //no change needed (optional)
$google_analytics_max_results   = '20'; //no change needed (optional)

########## MySql details #############
$db_username                    = "db_user_name"; //Database Username
$db_password                    = "xxxxxx"; //Database Password
$hostname                       = "localhost"; //Mysql Hostname
$db_name                        = 'xxxxxx'; //Database Name
###################################################################

$mysqli = new mysqli($hostname,$db_username,$db_password,$db_name);

Updating Top Pages

If everything is set correctly in configuration file, PHP page below should run without any trouble. We can run this page once or twice a month to fetch top pages from Google Analytics, and store them in MySql database for later retrieval.
We need to authenticate user first, and once we have the access token, we can proceed further with Analytics services. Please go though comment lines to understand the process.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
<?php
//start session
session_start();

//include configuration file
include_once("config.php");

//include google api files
require_once 'src/Google_Client.php';
require_once 'src/contrib/Google_AnalyticsService.php';

$gClient = new Google_Client();
$gClient->setApplicationName('Login to saaraan.com');
$gClient->setClientId($google_client_id);
$gClient->setClientSecret($google_client_secret);
$gClient->setRedirectUri($google_redirect_url);
$gClient->setScopes(array('https://www.googleapis.com/auth/analytics.readonly'));
$gClient->setUseObjects(true);


//check for session variable
if (isset($_SESSION["token"])) {

    //set start date to previous month
    $start_date = date("Y-m-d", strtotime("-1 month") );
   
    //end date as today
    $end_date = date("Y-m-d");
   
    try{
        //set access token
        $gClient->setAccessToken($_SESSION["token"]);
       
        //create analytics services object
        $analyticsService = new Google_AnalyticsService($gClient);
       
        //analytics parameters (check configuration file)
        $params = array('dimensions' => $google_analytics_dimensions,'sort' => $google_analytics_sort_by,'filters' => 'ga:medium==organic','max-results' => $google_analytics_max_results);
       
        //get results from google analytics
        $results = $analyticsService->data_ga->get($google_analytics_profile_id,$start_date,$end_date, $google_analytics_metrics, $params);
    }
    catch(Exception $e){ //do we have an error?
        echo $e->getMessage(); //display error
    }
   
    $pages = array();
    $rows = $results->rows;
   
    if($rows)
    {
        echo '<ul>';
        foreach($rows as $row)
        {
            //prepare values for db insert
            $pages[] = '("'.$row[0].'","'.$row[1].'",'.$row[2].')';
           
            //output top page link
            echo '<li><a href="'.$page_url_prefix.$row[0].'">'.$row[1].'</a></li>';
        }
        echo '</ul>';
       
        //empty table
        $mysqli->query("TRUNCATE TABLE google_top_pages");
       
        //insert all new top pages in the table
        if($mysqli->query("INSERT INTO google_top_pages (page_uri, page_title, total_views) VALUES ".implode(',', $pages).""))
        {
            echo '<br />Records updated...';
        }else{
            echo $mysqli->error;
        }
    }
   
}else{
    //authenticate user
    if (isset($_GET['code'])) {    
        $gClient->authenticate();
        $token = $gClient->getAccessToken();
        $_SESSION["token"] = $token;
        header('Location: ' . filter_var($google_redirect_url, FILTER_SANITIZE_URL));
    }else{
        $gClient->authenticate();
    }
}

Retrieving Top URLs from DB

When the top URLs are stored in database, here’s how we can retrieve the records from database. You can display it anywhere you like, such as on the sidebar or footer of your website etc.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
//include configuration file
include_once("config.php");

//get all records from db table
$results = $mysqli->query("SELECT page_uri, page_title, total_views FROM google_top_pages ORDER BY total_views ASC");

//list all top pages on screen
echo '<ul class="page_result">';
while($row = mysqli_fetch_array($results))
{
   echo '<li><a href="'.$page_url_prefix.$row['page_uri'].'">'.$row['page_title'].'</a></li>';
}
echo '</ul>';

//link to update page for admin
echo '<div class="update-button"><a href="update_pages.php">Update top pages list!</a></div>';
?>

Demo

If you haven’t noticed, there’s a Highlights widget on the right sidebar of this page, that’s exactly how this script pulls the popular links of your website.

Download

  1. Thanks You, great work….

     Reply
  2. Hi there,

    at first I would like to say thank you for this tut!
    I try to fetch my own dimensions and metrics. so I tryed this
    in config.php
    $google_analytics_dimensions = ‘ga:city,ga:networkDomain,ga:networkLocation,ga:pagePath,ga:pageTitle,ga:date,ga:country’; //no change needed (optional)
    $google_analytics_metrics = ‘ga:visits’; //no change needed (optional)
    $google_analytics_sort_by = ‘-ga:networkLocation’; //no change needed (optional)

    in update_pages.php
    //prepare values for db insert
    $pages[] = ‘(“‘.$row[0].'”,”‘.$row[1].'”,’.$row[2].'”,”‘.$row[3].'”,’.$row[4].'”,”‘.$row[5].'”,’.$row[6].'”,”‘.$row[7].’)’;

    //output top page link
    echo ‘‘.$row[2].’.’.’.’.$row[7].”.”;
    }
    echo ”;

    //empty table
    // $mysqli->query(“TRUNCATE TABLE google_top_pages”);

    //insert all new top pages in the table
    if($mysqli->query(“INSERT INTO `google_top_pages`(`city`, `networkDomain`, `networkLocation`, `pagePath`, `page_title`, `date`, `country`,`visits` ) VALUES “.implode(‘,’, $pages).””))
    {
    echo ‘Records updated…

    But I get this error
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘it gmbh”,”/2015/04/im-wahrsten-sinne-ausgezeichnet-gleich.html”,yourIT DOCUframe’ at line 1

    any idears?

    Thanks for your help

    Alex

     Reply
  3. Hi,

    I keep getting the error(Cannot modify header information – headers already sent by…)
    But when i fix that it keep asking me to authenticate user even after I click allow. Any ideas?

    Best regards,
    Wan

     Reply
  4. It sure would be nice to get this updated for the new library! 1.0.0 Thanks!

     Reply
  5. thats place where im getting stuck ::

    1
    2
    3
    4
    5
    $rows = $results-&gt;rows;
        if($rows)
        {
            echo 'c';   //thats i put just for testing that im in that loop or not
    // i am not getting anything in output....not even any error......help me
     Reply
    • i needed a script to get google search for some of my keywords and top 10 result of that search i want…..

      can you help me regarding this ?

      Thank You…..

  6. Demo
    If you haven’t noticed, there’s a Popular Page widget on the sidebar of this page.

    Which one, left, right, top ?
    Thanks

     Reply
  7. can u suggest what name give two send ,third code and how to check the results

     Reply
  8. Hi,

    I have followed your tutorial through and just seem to get:

    (403) User does not have sufficient permissions for this profile.

    Can you offer any advice please?

    Thanks, Matt

     Reply
  9. what are the changes to be made for fetching individual page view/ counts? can i use =filters=ga:pagePath=’ in the api link?
    please help.
    thanks,

     Reply
  10. If I don’t want use a redirect uri?

    tks

     Reply
Message Type : Question Comment ?
  • Question : Can include code, jsFiddle, codePen etc using Markdown Syntax.
  • Comment : Short comments and questions.