How Fast Does Your AdWords Data Update? Find Out

search-computer-analytics-data-ss-1920

Ever wondered how up-to-date your AdWords data is?

Back in the old days, it could take up to a day for AdWords data to update. These days, there is a warning in the interface that says “Clicks and impressions received in the last three hours may not be included.” However, we’ve noticed that data is usually updated more regularly than that. In fact, we’ve built tools that respond in real time, so it’s important to know how recent that data is.

Some of the guys at Brainlabs (my employer) decided to run a little experiment that we’ve shared below. We poll the AdWords API repeatedly so we can actually know how recent the data is. We’ve open-sourced the code so you can run it yourself, but if you just want to know how real-time our AdWords data is, then check out the live graph we’re updating here.

At the time of writing, we have seen AdWords:

  • Update every 2:43 minutes.
  • Provide data from just 13:22 minutes previously.

That’s incredibly fast for such a complex system – hats off to the phenomenal AdWords API team! The methodology centers on pulling a report of today’s impressions segmented by hour, then comparing it against the data in the previous report. You can tell how often the API information updates by seeing when the impressions for the most recent hours increase. (We’re looking at data from our biggest accounts, so we can assume there are always impressions coming in.)

You can tell how recent the information is by seeing when the data for the current hour appears. If there are no impressions showing for 11 a.m. in the report downloaded at 11:13, but there are impressions for 11 a.m. when you download a report at 11:14, you know the data is between 13 and 14 minutes old.

If you have API access, you can test this with your own accounts. Below is a PHP script to record when the API’s data changes. It finds out how frequently your AdWords data is changing (to the nearest 30 seconds). To run the script, you’ll need PHP installed, and you’ll need the AdWords API client library (with authentication set up).

Before you run the script, change this part:

require_once ‘/googleads-php-lib/examples/AdWords/v201502/init.php'; 

to have the path of the client library’s init.php file. You’ll also need to input your account’s time zone (so that the script can create dates correctly) and your account’s name (used for file names). You can also put in the account’s customer ID, although this is only necessary if you did not specify a customer ID when setting up the authorization.

You can change the timeToRun if you want, but note that this script is an easy way to use up your quota of reports! If you have basic API access, you can only download 1,000 reports a day, so you can’t run this script for more than nine hours. We suggest that if you have basic access, you only run it for a couple of hours so you can still download any other reports you want.

Also note that this process works on the assumption that the account always gets impressions — if you’ve got a smaller account, or if you run the script at a time when there’s not much traffic, it will not work as accurately.

The script should create four files:

  • Log.csv contains all of the impression and click data, and at what time it was downloaded.
  • Summary.csv lists the time at which the updates took place, and the time since the last update.
  • Hour-data.csv reports when data for each hour first appeared, and when that hour’s data last changed. (Sometimes the data can change well after the hour has ended, if Google removes invalid impressions.)
  • Results.txt is written at the end and gives the average, maximum and minimum time between updates, and the average age of the data (which is the average time between an hour starting and the hour’s data appearing in the reports).

(The script also uses tempdatastore.csv while it’s running, but should remove it at the end.)

We’ve found that the data tends to update every three minutes (although that can vary between 1:50 and 6:00 minutes), and that the data is around 14 minutes old. Is it the same for you?

 

<?php

require_once '/googleads-php-lib/examples/AdWords/v201502/init.php';
require_once ADWORDS_UTIL_PATH . '/ReportUtils.php';

$accountTimezone = "Europe/London";
// Change to your account's timezone

$accountName = "Which Technology2";
// Change to your account's name

$accountId = "xxx-xxx-xxxx";
// Change to your account's customer ID, if required.

$timeToRun = "02:00:00";
// The approximate time for the script to download data.
// The default is to run for 2 hours


//////////////////////////////////////////////////////////////////////////////////
// Main body

date_default_timezone_set($accountTimezone);
$cooldown = 0;

$fileHandles = makeFileHandles();
if ($fileHandles == null) {
 // Files could not be opened, so the script cannot run
 die;
}

$AdWordsUser = new AdWordsUser();
if ($accountId != "xxx-xxx-xxxx") {
 $AdWordsUser->SetClientCustomerId($accountId);
}

// Convert $timeToRun into seconds
$timeBits = explode(":",$timeToRun);
$secondsToRun = ($timeBits[0]*60*60) + ($timeBits[1]*60) + $timeBits[2];

for ($n=0; $n 0) {
// The data was different within the last minute, so there's no point looking now
$cooldown--;
} else {
// Get the latest report, compare with the previous report.
$different = compareNewReport($AdWordsUser, $fileHandles, $n);
if ($different === true) {
// If the new data was different then there should be a cooldown period
$cooldown = 2;
}
}
// Sleep for 30 seconds, before checking again
sleep(30);
}

echo "Finished fetching data n";

// Gets final averages and outputs into the summary document.
$updateTimes = getAverageTimes($fileHandles);
$hourUpdateTime = getHourAverage($fileHandles);

$resultText = "Results of the API Update Checker for " . $accountName . " (at ". date("Y-m-d H:i:s") . ")rn"
. "Average time between updates: ".$updateTimes["avg"]. "rn"
. "Maximum time between updates: ".$updateTimes["max"]. "rn"
. "Minimum time between updates: ".$updateTimes["min"]. "rn"
. "Average age of data: " . $hourUpdateTime . "rn"
."rn";
fwrite($fileHandles["Results"],$resultText);

// Close all the file handles
foreach ($fileHandles as $name => $handle) {
fclose($handle);
}

unlink("tempdatastore.csv");

//////////////////////////////////////////////////////////////////////////////////
// Functions

function makeFileHandles() {
// Opens or creates the required files
// Returns an array containing the file handles

try {
$fileHandles = array();

if (!file_exists("log.csv")) {
$fileHandles["Log"] = fopen("log.csv","c+");
fputcsv($fileHandles["Log"],array("Date","Hour of day","Impressions","Clicks", "Time Downloaded", "Time Since Last Update"));
} else {
$fileHandles["Log"] = fopen("log.csv","c+");
}

if (!file_exists("hour-data.csv")) {
$fileHandles["Hour Data"] = fopen("hour-data.csv","c+");
fputcsv($fileHandles["Hour Data"],array("Hour of Day","Data First Appeared","Data Last Updated"));

$hour = "";
$startOfYesterday = date("Y-m-d",time() - 60 * 60 * 24)." 00:00:00";
for ($i=1; $igetMessage() . "n";
$working = false;
}

// Check the handles are working, exit if any are not
foreach ($fileHandles as $name => $handle) {
if ($handle === false) {
echo $name . " could not be opened. The file might be in use by another program. n";
$working = false;
}
}
if ($working) {
return $fileHandles;
} else {
return null;
}
} // end function makeFileHandles

function compareNewReport($AdWordsUser, $fileHandles, $n) {
// Downloads an AdWords report and compares it to the previous download
// (in the temp data store).
// If there is a difference:
// * the data is recorded in the log file
// * the newer data replaces the old in the temp data store file
// * the time of the update is recorded in the summary file
// * information on when each hour's data is updated is recorded in the hour data file
// The function returns TRUE if the most recent report was different to the last

try {
$fileSafeDate = date("H_i_s");
$fullDate = date("Ymd H:i:s");
$today = date("Ymd");
$yesterday = date("Ymd", time() - 60 * 60 * 24);

// Downloads an account performance report to get impressions and clicks by the hour, for today and yesterday
$AdWordsReportFilePath = $fileSafeDate . "_report.csv";
$reportDownloadedSuccessfully = DownloadCriteriaReport($AdWordsUser, $AdWordsReportFilePath, $yesterday, $today);
if (!$reportDownloadedSuccessfully) {
echo "Report could not be downloaded. n";
return;
}

// Reads the report
$handleAdWordsReport = fopen($AdWordsReportFilePath, "r");
$newData = array();
$j = 0;
$latestHour = "19700101 00:00:00";

if ($handleAdWordsReport === false) {
echo "Could not open report file. n";
return;
}

while(!feof($handleAdWordsReport)){
$a = fgetcsv($handleAdWordsReport);
if($j === 0){
$j = 1;
continue;
}
if($a[0] === "Total"){
break;
}

$currentHour = $a[0]." ".str_pad($a[1], 2, "0", STR_PAD_LEFT).":00:00";
$newData[$currentHour] = $a;

if (strtotime($currentHour) > strtotime($latestHour)) {
$latestHour = $currentHour;
}
}
fclose($handleAdWordsReport);
unlink($AdWordsReportFilePath);

// Read the last set of data, in $fileHandles["Temp Data Store"], and see if there are differences
$different = false;
$tempDataExists = true;
$newHourData = false;
$j = 0;
$hoursChanged = array();
rewind($fileHandles["Temp Data Store"]);
if (feof($fileHandles["Temp Data Store"])) {
$different = true;
$tempDataExists = false;
}

while(!feof($fileHandles["Temp Data Store"])) {
$a = fgetcsv($fileHandles["Temp Data Store"]);

if($a[0] === "Total" || $a[0] == ""){
if ($j === 0) {
$different = true;
$tempDataExists = false;
}
break;
}

$currentHour = $a[0]." ".str_pad($a[1], 2, "0", STR_PAD_LEFT).":00:00";

if ($j == 0 ) {
if (strtotime($latestHour) > strtotime($currentHour)) {
$newHourData = true;
echo "New hour data for " . $latestHour . "n";
$different = true;
$oldTime = strtotime($a[count($a)-1]);
$hoursChanged[] = $latestHour;
}
$j = 1;
}

if (!isset($newData[$currentHour])) {
$different = true;
$oldTime = strtotime($a[count($a)-1]);
$hoursChanged[] = $currentHour;
} else {
for ($i=1; $i 0) {
// If $n is 0, this is the first time the function has run,
// so we don't record the time difference
$timeDifference = secondsToHours($newTime - $oldTime);
}

// Read the "Hour Data" file and update if the data for an hour has changed,
// or a new hour has started
rewind($fileHandles["Hour Data"]); //get the pointer to the start of the file
while (!feof($fileHandles["Hour Data"])) {
$a = fgetcsv($fileHandles["Hour Data"]);
if (!$a) {
break;
}
if (array_search($a[0],$hoursChanged) !== false) {
$hourData[$a[0]] = array($a[0], $a[1], $fullDate);
} else {
$hourData[$a[0]] = $a;
}
}

if ($newHourData) {
foreach ($newData as $hour => $row) {
if (!isset($hourData[$hour])) {
if ($tempDataExists) {
$hourData[$hour] = array($hour, $fullDate, $fullDate);
$startHour = new DateTime($hour);
$endHour = new DateTime($fullDate);
$diffHours = $startHour->diff($endHour);
} else {
// We can't be sure that this is the earliest the hour's data appeared
// so we don't record a 'First Update' time
$hourData[$hour] = array($hour, "-", $fullDate);
}
}
} // end foreach
krsort($hourData);
}

rewind($fileHandles["Hour Data"]);
$h = 0;
foreach ($hourData as $row) {
fputcsv($fileHandles["Hour Data"], $row);
$h++;
if ($h > 48) {
break;
}
}
ftruncate($fileHandles["Hour Data"],ftell($fileHandles["Hour Data"]));

// Write the temp data store, log and summary only when there are changes
rewind($fileHandles["Temp Data Store"]);
fseek($fileHandles["Log"],0,SEEK_END);

fseek($fileHandles["Summary"],0,SEEK_END);
fputcsv($fileHandles["Summary"],array($fullDate,$timeDifference));

krsort($newData);
foreach ($newData as $hour => $row) {
$row[] = $fullDate;
fputcsv($fileHandles["Temp Data Store"],$row);
$row[] = $timeDifference;
fputcsv($fileHandles["Log"],$row);
}
ftruncate($fileHandles["Temp Data Store"],ftell($fileHandles["Temp Data Store"]));

getAverageTimes($fileHandles);
getHourAverage($fileHandles);
} else {
echo "Data is the same. n";
}

return $different;

} catch (Exception $e) {
echo $e->getMessage() . "n";
return false;
}
} // end function getNewData

function getAverageTimes($fileHandles) {
// This reads the summary file and calculates the average
// time between updates

$count = 0;
$total = 0;
$max = 0;
$min = 999999;
$output = array();

rewind($fileHandles["Summary"]);
while (!feof($fileHandles["Summary"])) {
$a = fgetcsv($fileHandles["Summary"]);
if (!empty($a[1]) && $a[1] != "-" && $a[1] != "Time Since Last Update") {
$count++;

$timeBits = explode(":",$a[1]);
$timeInSeconds = ($timeBits[0]*60*60) + ($timeBits[1]*60) + $timeBits[2];
$total += $timeInSeconds ;
if ($timeInSeconds > $max) {
$max = $timeInSeconds;
}
if ($timeInSeconds 0 && $total > 0) {
$output["avg"] = secondsToHours($total/$count);
} else {
$output["avg"] = "-";
}
if ($max > 0) {
$output["max"] = secondsToHours($max);
} else {
$output["max"] = "-";
}
if ($min 0 && $total > 0) {
$avg = secondsToHours($total / $count);
echo "Avg time for hour to appear: " . $avg ."n";
} else {
$avg = "-";
}

return $avg;
} // end function getHourAverage

function secondsToHours($secondsDifference) {
// Converts a number of seconds into a string formatted as H:i:s

return str_pad(floor($secondsDifference/3600), 2, "0", STR_PAD_LEFT) . ":" .
str_pad(floor(($secondsDifference % 3600)/60), 2, "0", STR_PAD_LEFT) . ":" .
str_pad(($secondsDifference % 60), 2, "0", STR_PAD_LEFT);
}

function DownloadCriteriaReport($AdWordsUser, $filePath, $dateRangeMin, $dateRangeMax){
// Downloads an account performance report from AdWords

try {

$reportQuery = 'SELECT Date, HourOfDay, Impressions, Clicks FROM ACCOUNT_PERFORMANCE_REPORT DURING ' . $dateRangeMin .",". $dateRangeMax;

$dateRange = array(
'min' => $dateRangeMin,
'max' => $dateRangeMax
);

$options = array('version' => ADWORDS_VERSION);

$options['skipReportHeader'] = true;
$options['skipReportSummary'] = false;

ReportUtils::DownloadReportWithAwql($reportQuery, $filePath, $AdWordsUser,
'CSV', $options);

echo "Report was downloaded. n";

return true;
} catch (Exception $e) {
printf("An error has occurred: %sn", $e->getMessage());
return false;
}
} // end function DownloadCriteriaReport

?>

The post How Fast Does Your AdWords Data Update? Find Out appeared first on Search Engine Land.