Collect Google Analytics data for Mysql tables

Question

I have seen many examples of how to export data from Google Analytics to other formats as well as keep the exported data updated, but none so far served to export the data to Mysql given the following problems:

• Exceeding the limits for consultations;
• Process takes hours to keep records up to date;
• Process becomes unviable from a certain number of records because the day only has 24h.

So far my implementation is in this format:

         ┌─────────────────────────────────────────┐
         │ Por cada registro na base de dados local │
         └────────────────────┬────────────────────┘
                              │
            ┌─────────────────┴──────────────────┐
            │ Enviar URL para o Google Analytics │
            └─────────────────┬──────────────────┘
                              │
┌─────────────────────────────┴─────────────────────────────┐
│ Com estatísticas recebidas, atualizar base de dados local │
└───────────────────────────────────────────────────────────┘

This works well, but to overcome the problems listed, you need to take breaks:

while ($registoLocal = $sth->fetch()) {

    if (time_nanosleep(0, 500000000) === true) {
        // comunicar com o Google Analytics
    }
}

This is reflected in a +/- fixed time per $registoLocal, currently in 2 seconds which leaves us with a maximum of 43200 queries per day.

In addition we have the limits of API of Google Analytics:

• 50,000 requests per project per day
• 10 queries per second per IP

Code in use

Include dependencies and instantiate the API:

/* API dependencies
 */
require_once('caminho/para/Google_Client.php');
require_once('caminho/para/contrib/Google_AnalyticsService.php');

/* Create client object and set app name
 */
$client = new Google_Client();
$client->setApplicationName('O nome da minha aplicação'); // app name

/* Set assertion credentials
 */
$client->setAssertionCredentials(
    new Google_AssertionCredentials(
        '[email protected]', // API Service Account Email Address
        array('https://www.googleapis.com/auth/analytics.readonly'),
        file_get_contents('caminho/para/o/ficheiro/O nome da minha aplicação.p12')  // downloaded keyfile
    )
);

/* Other settings
 * Access the Google Cloud Console and view the Web App-project details:
 * https://cloud.google.com/console
 */
$client->setClientId('[e-mail]'); // Client ID for web application Email Address
$client->setAccessType('offline');
$client->setUseObjects(true);

Link to local database:

/* Connection access details
 */
$dbcon = array(
    "host"     => "localhost",
    "dbname"   => "nomeBaseDados",
    "username" => "utilizador",
    "password" => "password"
);

/* Connect to the database
 */
$dbh = new PDO(
    'mysql:host='.$dbcon['host'].';dbname='.$dbcon['dbname'].';',
    $dbcon['username'],
    $dbcon['password'],
    array(
        PDO::ATTR_PERSISTENT               => false,
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
        PDO::ATTR_ERRMODE                  => PDO::ERRMODE_EXCEPTION,
        PDO::MYSQL_ATTR_INIT_COMMAND       => "SET NAMES utf8"
    )
);

/* Prepare query for Insert
 */
$sqlInsert = "
INSERT INTO press_statistic_analytics
    (id, press_id, ga_profile_id, ga_sessions, ga_pageviews, ga_percentNewVisits, date_created, date_updated)
VALUES
    (null, :press_id, :ga_profile_id, :ga_sessions, :ga_pageviews, :ga_percentNewVisits, :date_created, :date_updated)";

$queryInsert = $dbh->prepare($sqlInsert);

/* Prepare query for Update
 */
$sqlUpdate = "
UPDATE press_statistic_analytics SET
    ga_sessions = :ga_sessions,
    ga_pageviews = :ga_pageviews,
    ga_percentNewVisits = :ga_percentNewVisits,
    date_updated = :date_updated
WHERE press_id = :press_id
AND ga_profile_id = :ga_profile_id ";

$queryUpdate = $dbh->prepare($sqlUpdate);

/* Get all Articles
 */
$sth = $dbh->query("
SELECT
    press_i18n.press_id,
    press_i18n.title,
    press_i18n.slug
FROM press_i18n
ORDER BY press_i18n.id DESC");

$sth->setFetchMode(PDO::FETCH_OBJ);

For each local record, communicate with Google Analytics and collect information:

while ($article = $sth->fetch()) {

    if (time_nanosleep(0, 500000000) === true) {

        /* Create service and get data
         */
        $gaProfileID = 'ga:11111111';
        $startDate = '2000-01-01';
        $endDate = date("Y-m-d", time());
        $metrics = 'ga:sessions,ga:pageviews,ga:percentNewVisits';
        $optParams = array(
            "filters" => "ga:pagePath==/".$article->slug
        );

        $service = new Google_AnalyticsService($client);

        /* Query Google and process results
         * to a more usable array.
         */
        try {

            // Get data
            $results = $service->data_ga->get($gaProfileID, $startDate, $endDate, $metrics, $optParams);

            $googleAnalyticsArr = array();

            if (isset($results) && is_object($results) && count($results->getRows()) > 0) {

                $headersArr = (Array)$results->getColumnHeaders();

                $i=0;

                foreach ($results->getRows() as $row) {

                    foreach ($row as $cell) {

                        $googleAnalyticsArr[$headersArr[$i]->name] = htmlspecialchars($cell, ENT_NOQUOTES);

                        $i++;
                    }
                }
            }

            /* Update or insert the collected data
             */
            $sthSelectExistent = $dbh->query("
                SELECT *
                FROM press_statistic_analytics
                WHERE press_id = ".$article->press_id."
                AND ga_profile_id = '".$gaProfileID."'
            ");
            $sthSelectExistent->setFetchMode(PDO::FETCH_OBJ);
            $rowSelectExistent = $sthSelectExistent->fetch();

            /* Prepare data
             */
            $ga_sessions = isset($googleAnalyticsArr["ga:sessions"]) ? $googleAnalyticsArr["ga:sessions"] : 0;
            $ga_pageviews = isset($googleAnalyticsArr["ga:pageviews"]) ? $googleAnalyticsArr["ga:pageviews"] : 0;
            $ga_percentNewVisits = isset($googleAnalyticsArr["ga:percentNewVisits"]) ? $googleAnalyticsArr["ga:percentNewVisits"] : 0.00;

            if (is_object($rowSelectExistent)) {

                $queryUpdate->execute(
                    array(
                        "ga_sessions"         => $ga_sessions,
                        "ga_pageviews"        => $ga_pageviews,
                        "ga_percentNewVisits" => $ga_percentNewVisits,
                        "date_updated"        => date("Y-m-d H:i:s"),
                        "press_id"            => $article->press_id,
                        "ga_profile_id"       => $gaProfileID
                    )
                );
            }
            else {
                $queryInsert->execute(
                    array(
                        "press_id"            => $article->press_id,
                        "ga_profile_id"       => $gaProfileID,
                        "ga_sessions"         => $ga_sessions,
                        "ga_pageviews"        => $ga_pageviews,
                        "ga_percentNewVisits" => $ga_percentNewVisits,
                        "date_created"        => date("Y-m-d H:i:s"),
                        "date_updated"        => "0000-00-00 00:00:00"
                    )
                );
            }
        }
        catch (apiServiceException $e) {

            // Handle API service exceptions.
            //$error = $e->getMessage();
            var_dump($e);
        }
        catch (Google_ServiceException $e) {

            // Handle API service exceptions.
            //$error = $e->getMessage();
            var_dump($e);
        }
    }
}

Question

How to optimize this process of collecting statistics from each URL present in Google Analytics to the local Mysql database in order to avoid forced breaks on script and without exceeding the limits currently imposed by Google ?

Answer 1

Proposed solution:

1 . Create a column with the date of the last time you google the data from article

ALTER TABLE press_i18n ADD COLUMN date_ga DATE NULL

2 . Change the script that searches all articles to pick up by the last date not updated

$sth = $dbh->query("
SELECT
    press_i18n.press_id,
    press_i18n.title,
    press_i18n.slug
FROM press_i18n
WHERE date_ga < DATE(NOW()) OR date_ga IS NULL
ORDER BY press_i18n.id DESC LIMIT 1");

3 . Give UPDATE of date_ga from the database search log just below

// Pegamos o artigo
$article = $sth->fetch();

if(!$article) {
    echo "Não encontrou article";
    break;
}

echo "Article: " . $article->press_id;

$sthUpdate = "UPDATE press_i18n SET date_ga = DATE(NOW()) WHERE press_i18n.press_id = :press_id";
$queryUpdateGa = $dbh->prepare($sthUpdate);

$queryUpdateGa->execute(
    array(
        "press_id" => $article->press_id
    )
);

4 . Change communication code with Google to always run an article record at a time

$gaProfileID = 'ga:11111111';
$startDate = '2000-01-01';
$endDate = date("Y-m-d", time());
$metrics = 'ga:sessions,ga:pageviews,ga:percentNewVisits';
$optParams = array(
    "filters" => "ga:pagePath==/".$article->slug
);

$service = new Google_AnalyticsService($client);

try {

    // Get data
    $results = $service->data_ga->get($gaProfileID, $startDate, $endDate, $metrics, $optParams);

    $googleAnalyticsArr = array();

    if (isset($results) && is_object($results) && count($results->getRows()) > 0) {

        $headersArr = (Array)$results->getColumnHeaders();

        $i=0;

        foreach ($results->getRows() as $row) {

            foreach ($row as $cell) {

                $googleAnalyticsArr[$headersArr[$i]->name] = htmlspecialchars($cell, ENT_NOQUOTES);

                $i++;
            }
        }
    }

    /* Update or insert the collected data
     */
    $sthSelectExistent = $dbh->query("
        SELECT *
        FROM press_statistic_analytics
        WHERE press_id = ".$article->press_id."
        AND ga_profile_id = '".$gaProfileID."'
    ");
    $sthSelectExistent->setFetchMode(PDO::FETCH_OBJ);
    $rowSelectExistent = $sthSelectExistent->fetch();

    /* Prepare data
     */
    $ga_sessions = isset($googleAnalyticsArr["ga:sessions"]) ? $googleAnalyticsArr["ga:sessions"] : 0;
    $ga_pageviews = isset($googleAnalyticsArr["ga:pageviews"]) ? $googleAnalyticsArr["ga:pageviews"] : 0;
    $ga_percentNewVisits = isset($googleAnalyticsArr["ga:percentNewVisits"]) ? $googleAnalyticsArr["ga:percentNewVisits"] : 0.00;

    if (is_object($rowSelectExistent)) {

        $queryUpdate->execute(
            array(
                "ga_sessions"         => $ga_sessions,
                "ga_pageviews"        => $ga_pageviews,
                "ga_percentNewVisits" => $ga_percentNewVisits,
                "date_updated"        => date("Y-m-d H:i:s"),
                "press_id"            => $article->press_id,
                "ga_profile_id"       => $gaProfileID
            )
        );
    }
    else {
        $queryInsert->execute(
            array(
                "press_id"            => $article->press_id,
                "ga_profile_id"       => $gaProfileID,
                "ga_sessions"         => $ga_sessions,
                "ga_pageviews"        => $ga_pageviews,
                "ga_percentNewVisits" => $ga_percentNewVisits,
                "date_created"        => date("Y-m-d H:i:s"),
                "date_updated"        => "0000-00-00 00:00:00"
            )
        );
    }
}
catch (apiServiceException $e) {

    // Handle API service exceptions.
    //$error = $e->getMessage();
    var_dump($e);
}
catch (Google_ServiceException $e) {

    // Handle API service exceptions.
    //$error = $e->getMessage();
    var_dump($e);
}

5 . Assuming that this page has a name (and can be accessed externally) we can create a PHP page to call it or schedule in CRON for this page to be called every X time Since I do not know which is your server I will create a page that calls the page of communication with google every 1 second, theoretically we can duplicate the requests in google.

<meta http-equiv="refresh" content="1">

<?php 
    @file_get_contents( "http://seuservidor/pasta/arquivo_que_comunica_com_o_google.php" );     
?>

• The solution was not tested because I do not have the database of the question, but I do a similar process here in my company and works by crontable approximately 150 thousand requests/day
• I believe using the CRONTAB You can increase the number of requests on google.
• In case of doubt I am available in the chat

Answer 2

This is not a limitation of the tool by capacity but by price ( https://developers.google.com/analytics/devguides/config/mgmt/v3/limits-quotas ).

They set the search limit based on a request every 1.7 seconds that would be a good time for a routine. So swindling this is going to be a little tricky if you’re not going to pay for the api. Adwords works the same way and DSP pays good money to use Exchanges.

In the API screen the billing can be linked to increase this quota. And remembering that if you are using the free account you still have the data sample problem in the API ( In pay too, but they say no ). Remember that if using premium you can use the Big Query that will give you the most chewed files.

Some tools you can use like: - Webtrends; - Piwik ( Open Source Analytics ); - At Internet;

I know that’s not exactly the answer you were hoping for, but the limit is there for them to be able to monetize what they support from data. Try with Piwik will have full control of the FREE data, and can still implement with Javascript via tag manager in the same way.

---

I forgot to comment outside the real time, the other reports take 24 hours ( No SLA ) in the free version and 4 hours in the premium version. You can request every hour when there is a difference in data.