Uploading Cost Data into Google Analytics

in Google Analytics API

In this second part of my series on uploading cost data into Google Analytics from third-party ad networks, I'm going to expand on my first post with a tutorial explaining how to write a cost analysis upload script. I'll show you how to transform a performance report into an upload file then upload it to a custom data source ready for viewing in your reports. You might want to check out my overview of the cost data import tool for a higher level view of the process before you start.

An Example Ad Network

To show you how to use the cost upload tool, I'm going to use a fictional advertiser, called “Acme Pet Food”, who is running ads on a fictional search engine with an ad engine called “Acme Ads”. Our advertiser is running two campaigns, “Cat Food, UK” and “Dog Food, UK”, with the following structure:

  • Cat Food, UK
    • Ad Group: Kitty's Crunchy Bites
      • Ads: Crunchy Cat Treats
      • Keywords: kitten food, cat food
    • Ad Group: Cheap Cat Food
      • Ads: Low Price Cat Food, Cheap Cat Food
      • Keywords: cheap cat food
  • Dog Food, UK
    • Ad Group: Puppy Lunch
      • Ads: Nutritious Dog Food, Healthy Dog Food
      • Keywords: dog food, cheap dog food

Acme Pet Food has tagged their destination URLs with tracking parameters:

http://test.ewanheming.com/?utm_source=acme+ads&utm_medium=cpc
&utm_campaign=Cat+Food,+UK&utm_term=kitten+food&utm_content=Crunchy+Cat+Treats
http://test.ewanheming.com/?utm_source=acme+ads&utm_medium=cpc
&utm_campaign=Cat+Food,+UK&utm_term=cat+food&utm_content=Crunchy+Cat+Treats
http://test.ewanheming.com/?utm_source=acme+ads&utm_medium=cpc
&utm_campaign=Cat+Food,+UK&utm_term=cheap+cat+food&utm_content=Low+Price+Cat+Food
http://test.ewanheming.com/?utm_source=acme+ads&utm_medium=cpc
&utm_campaign=Cat+Food,+UK&utm_term=cheap+cat+food&utm_content=Kitty+Meals+for+Less
http://test.ewanheming.com/?utm_source=acme+ads&utm_medium=cpc
&utm_campaign=Dog+Food,+UK&utm_term=dog+food&utm_content=Nutritious+Dog+Food
http://test.ewanheming.com/?utm_source=acme+ads&utm_medium=cpc
&utm_campaign=Dog+Food,+UK&utm_term=dog+food&utm_content=Healthy+Dog+Food
http://test.ewanheming.com/?utm_source=acme+ads&utm_medium=cpc
&utm_campaign=Dog+Food,+UK&utm_term=cheap+dog+food&utm_content=Healthy+Dog+Food

After running these campaigns on the 10th and 11th of April, 2013, Google Analytics reported that the ads had made $194.67 of sales from 259 visits. The following screenshots show a breakdown of this by date, ad, and keyword:

Ad and Keyword Revenue: 10th Apr, 2013
Ad and Keyword Revenue: 10th Apr, 2013
Ad and Keyword Revenue: 11th Apr, 2013
Ad and Keyword Revenue: 11th Apr, 2013

Acme Ads provided us with the following report for the two days:

"Report Name:", "Ad Performance Report" 
"Report Date:", "Apr 12, 2013" 
"Account Name:", "Acme Pet Food" 
 
"Date","Campaign","AdGroup","Headline", "Keyword","Impressions","Clicks","Cost" 
"Apr 10, 2013","Cat Food, UK","Kitty's Crunchy Bites","Crunchy Cat Treats","kitten food",2182,24,9.45 
"Apr 10, 2013","Cat Food, UK","Kitty's Crunchy Bites","Crunchy Cat Treats","cat food",762,16,9.45 
"Apr 10, 2013","Cat Food, UK","Cheap Cat Food","Low Price Cat Food","cheap cat food",719,23,15.12 
"Apr 10, 2013","Cat Food, UK","Cheap Cat Food","Kitty Meals for Less","cheap cat food",545,12,6.30 
"Apr 10, 2013","Dog Food, UK","Puppy Lunch","Nutritious Dog Food","dog food",326,14,26.46 
"Apr 10, 2013","Dog Food, UK","Puppy Lunch","Healthy Dog Food","dog food",424,14,7.27 
"Apr 10, 2013","Dog Food, UK","Puppy Lunch","Healthy Dog Food","cheap dog food",0,0,0.0 
"Apr 11, 2013","Cat Food, UK","Kitty's Crunchy Bites","Crunchy Cat Treats","kitten food",792,19,6.87 
"Apr 11, 2013","Cat Food, UK","Kitty's Crunchy Bites","Crunchy Cat Treats","cat food",1061,35,9.86 
"Apr 11, 2013","Cat Food, UK","Cheap Cat Food","Low Price Cat Food","cheap cat food",805,33,8.27 
"Apr 11, 2013","Cat Food, UK","Cheap Cat Food","Kitty Meals for Less","cheap cat food",1182,13,7.27 
"Apr 11, 2013","Dog Food, UK","Puppy Lunch","Nutritious Dog Food","dog food",528,19,16.80 
"Apr 11, 2013","Dog Food, UK","Puppy Lunch","Healthy Dog Food","dog food",860,37,13.50 
"Apr 11, 2013","Dog Food, UK","Puppy Lunch","Healthy Dog Food","cheap dog food",0,0,0.0 
"Totals:","--","--","--","--",10186,259,136.62
Example Ad Performance Report

This is what it looks like in a spreadsheet:

A B C D E F G H
Report Name: Ad Performance Report
Report Date: Apr 12, 2013
Account Name: Acme Pet Food
`
Date Campaign AdGroup Headline Keyword Impressions Clicks Cost
Apr 10, 2013 Cat Food, UK Kitty's Crunchy Bites Crunchy Cat Treats kitten food 2182 24 9.45
Apr 10, 2013 Cat Food, UK Kitty's Crunchy Bites Crunchy Cat Treats cat food 762 16 9.45
Apr 10, 2013 Cat Food, UK Cheap Cat Food Low Price Cat Food cheap cat food 719 23 15.12
Apr 10, 2013 Cat Food, UK Cheap Cat Food Kitty Meals for Less cheap cat food 545 12 6.3
Apr 10, 2013 Dog Food, UK Puppy Lunch Nutritious Dog Food dog food 326 14 26.46
Apr 10, 2013 Dog Food, UK Puppy Lunch Healthy Dog Food dog food 424 14 7.27
Apr 10, 2013 Dog Food, UK Puppy Lunch Healthy Dog Food cheap dog food 0 0 0.0
Apr 11, 2013 Cat Food, UK Kitty's Crunchy Bites Crunchy Cat Treats kitten food 792 19 6.87
Apr 11, 2013 Cat Food, UK Kitty's Crunchy Bites Crunchy Cat Treats cat food 1061 35 9.86
Apr 11, 2013 Cat Food, UK Cheap Cat Food Low Price Cat Food cheap cat food 805 33 8.27
Apr 11, 2013 Cat Food, UK Cheap Cat Food Kitty Meals for Less cheap cat food 1182 13 7.27
Apr 11, 2013 Dog Food, UK Puppy Lunch Nutritious Dog Food dog food 528 19 16.8
Apr 11, 2013 Dog Food, UK Puppy Lunch Healthy Dog Food dog food 860 37 13.5
Apr 11, 2013 Dog Food, UK Puppy Lunch Healthy Dog Food cheap dog food 0 0 0.0
Totals: -- -- -- -- 10186 259 136.62

Once our script has imported this data into Google Analytics, we'll be able to view the ROI at each level of detail.

Preparing the Files For Upload

Before uploading the cost data into Google Analytics, we have to transform it into the appropriate format. So the script needs to take the ad performance report from Acme Ads and create CSV files that look like this:

ga:source ga:medium ga:campaign ga:adGroup ga:keyword ga:adContent ga:impressions ga:adClicks ga:adCost
acme ads cpc Cat Food, UK Kitty's Crunchy Bites Crunchy Cat Treats kitten food 2182 24 9.45
acme ads cpc Cat Food, UK Kitty's Crunchy Bites Crunchy Cat Treats cat food 762 16 9.45
acme ads cpc Cat Food, UK Cheap Cat Food Low Price Cat Food cheap cat food 719 23 15.12
acme ads cpc Cat Food, UK Cheap Cat Food Kitty Meals for Less cheap cat food 545 12 6.3
acme ads cpc Dog Food, UK Puppy Lunch Nutritious Dog Food dog food 326 14 26.46
acme ads cpc Dog Food, UK Puppy Lunch Healthy Dog Food dog food 424 14 7.27
ga:source ga:medium ga:campaign ga:adGroup ga:keyword ga:adContent ga:impressions ga:adClicks ga:adCost
acme ads cpc Cat Food, UK Kitty's Crunchy Bites Crunchy Cat Treats kitten food 792 19 6.87
acme ads cpc Cat Food, UK Kitty's Crunchy Bites Crunchy Cat Treats cat food 1061 35 9.86
acme ads cpc Cat Food, UK Cheap Cat Food Low Price Cat Food cheap cat food 805 33 8.27
acme ads cpc Cat Food, UK Cheap Cat Food Kitty Meals for Less cheap cat food 1182 13 7.27
acme ads cpc Dog Food, UK Puppy Lunch Nutritious Dog Food dog food 528 19 16.8
acme ads cpc Dog Food, UK Puppy Lunch Healthy Dog Food dog food 860 37 13.5

Let's start by looking at the performance report to see what the script needs to do. The file has the following format:

  • The newline character terminates each line, and a comma separates each column.
  • Some text columns contain commas, such as “Dog Food, UK”, but the ad network has wrapped all text in double-quotes so you know to ignore them when splitting up a row.
  • The report has meta-data in the first three rows, an empty fourth row, column headings in the fifth row, and column totals in the last row: the script will need to exclude these rows from the upload.
  • The dates start with the first three letters of the month, followed by the day of the month, followed by a comma, then the full four-digit year: the script will need to convert these dates into the correct format.

The data rows have the following properties:

  • There's a row for each combination of date, ad headline, and keyword: the script will need to divide the rows into files for each date.
  • The rows for the “cheap dog food” keyword have no impressions, clicks, or costs: the script should remove these rows as they don't contain any useful information.
  • The campaign, keyword, and headline match the utm_campaign, utm_term, and utm_content URL parameters; if they didn't then we'd need another way to specify the correct dimensions in the upload.
  • The file doesn't contain any columns that match the utm_source and utm_medium URL parameters: the script will need to add them manually.

Given these observations, we can write a script to carry out the following tasks:

  • Parse the report into rows and columns.
  • Convert the dates into the correct format.
  • Extract the data rows; ignoring the heading and total rows.
  • Divide the data into groups of rows for each date.
  • Filter out rows with no impressions.
  • Create a CSV file for each date.

Parsing the Report

The report file has a standard CSV format, the script can parse it using the PHP fgetcsv() function:

$reportFile = "acme-ads.csv";
$fp = fopen($reportFile, "r");
 
while ($row = fgetcsv($fp)) {
    // Process the rows….
}
 
fclose($fp);
Reading the Report File

This simply opens the report file and loops through each line in it; converting the line into an array of columns as it goes along. The fgetcsv() function deals with the columns enclosed in double-quotes, so we'll always get an array with the correct number of columns.

Converting the Dates

You can convert a string, such as “Apr 10, 2013”, into a PHP date using the date_create_from_format() function. This function takes a PHP date format and a date string, and returns a DateTime object:

$uploadDate = date_format($date, 'Y-m-d');
Converting the Date into the Correct Format

This is checking for a value in the first column—to avoid a warning message—then converting it into a date; if there's no value or the function can't convert it into a date, then the $date variable will contain null or false, which we'll use in the next section.

Once you have a DateTime object, you can convert it into the date format required by the API:

$uploadDate = date_format($date, 'Y-m-d');
Converting the Date into the Correct Format

This will return the date as a string such as “2013-04-10” or “2013-04-11”.

Removing the Header and Footer Rows

The easiest way to exclude the rows that don't contain data, is to check for the existence of a date:

while ($row = fgetcsv($fp)) {
    $date = isset($row[0]) ?
            date_create_from_format('M d, Y', $row[0]) : null;
    if ($date instanceof DateTime) {
       // Process the rows
    }
}
Skipping Rows without a Date

This code attempts to create a DateTime object, but if it fails—when it encounters an empty row or values such as “Report Name:” or “Totals:”—it will just skip the row.

Dividing the Data into Days

We need to send one file to Google Analytics for each day's data in the file, so the rows need to be grouped by their date; this can be done using an associative array with the date as a key:

$uploadFiles = array();
 
while ($row = fgetcsv($fp)) {
    $date = isset($row[0]) ?
            date_create_from_format('M d, Y', $row[0]) : null;
    if ($date instanceof DateTime) {
        $uploadDate = date_format($date, 'Y-m-d');
        if (!isset($uploadFiles[$uploadDate])) {
            $uploadFiles[$uploadDate] = array();
        }
        // Add the data from the report to the array for the date….
    }
}
Grouping Rows into Buckets for each Day

After extracting and formatting the date, this code checks for the existence of an element in the $uploadFiles associative array for the date, and, if it doesn't find an element, it creates a new one and initializes it with an empty array. The script can then add the row of data to the bucket of rows for the date:

$source = "acme ads";
$medium = "cpc";
$campaign = $row[1];
$adgroup = $row[2];
$headline = $row[3];
$keyword = $row[4];
$impressions = $row[5];
$clicks = $row[6];
$cost = $row[7];
 
$uploadRow = array(
    $source,
    $medium,
    $campaign,
    $adgroup,
    $headline,
    $keyword,
    $impressions,
    $clicks,
    $cost
);
 
$uploadFiles[$uploadDate][] = $uploadRow;
Adding a Row from the Report to a Bucket

This is extracting the data from the file into variables, constructing a row using them, then pushing the row onto the array of rows for the date. The $source and $medium are hard-coded because they aren't in the file, and the text columns are wrapped in double-quotes in case they contain commas. You could do this with less code, which might be a bit faster for processing large files, but I've split it into steps to make it clearer.

Filtering Rows With Zero Impressions

Uploading rows with no click and cost data to Google Analytics is a bit wasteful and could make your files larger than the 5MB limit. You can sometimes choose to exclude these rows when you download performance reports, but Acme Ads doesn't give us that option so we'll have to do it ourselves. This just involves a simple modification to the row construction process:

if ($impressions > 0) {
    $uploadRow = array(
        $source,
        $medium,
        $campaign,
        $adgroup,
        $headline,
        $keyword,
        $impressions,
        $clicks,
        $cost
    );
 
    $uploadFiles[$uploadDate][] = $uploadRow;
}
Skipping Rows without any Impressions

This is checking that the number of impressions is greater than zero before constructing the row. When this is run, it will exclude the rows for the “cheap dog food” keyword.

Creating a CSV File for Each Date

Once the script has divided the rows into daily buckets, it can merge them into files:

$headers = array(
    "ga:source",
    "ga:medium",
    "ga:campaign",
    "ga:adGroup",
    "ga:adContent",
    "ga:keyword",
    "ga:impressions",
    "ga:adClicks",
    "ga:adCost"
);
 
foreach (array_keys($uploadFiles) as $uploadDate) {
    $fp = fopen("/tmp/upload-$uploadDate.csv", "w");
    fputcsv($fp, $headers);
    foreach ($uploadFiles[$uploadDate] as $row) {
        fputcsv($fp, $row);
    }
    fclose($fp);
}
Writing Each Bucket to a File

This uses the fputcsv() function to create a CSV file for each date in the $uploadFiles array. The script writes a header row to the file before writing out each row.

Putting It All Together

Here's a full script that transforms the ads report into upload files:

// The filename of the performance report
$reportFile = "acme-ads.csv";
 
// Hard code the source and medium
$source = "acme ads";
$medium = "cpc";
 
// Upload file headers
$headers = array(
    "ga:source",
    "ga:medium",
    "ga:campaign",
    "ga:adGroup",
    "ga:adContent",
    "ga:keyword",
    "ga:impressions",
    "ga:adClicks",
    "ga:adCost"
);
$headerRow = implode(",", $headers);
 
// Create an array to store the data to upload
$uploadFiles = array();
 
// Open the performance report
$fp = fopen($reportFile, "r");
 
// Process each row in the file
while ($row = fgetcsv($fp)) {
    // Attempt to create a date from the first column of the row
    $date = isset($row[0]) ?
            date_create_from_format('M d, Y', $row[0]) : null;
 
    // If the date creation was successful, this is a data row 
    if ($date instanceof DateTime) {
        // Extract the columns from the row
        $campaign = $row[1];
        $adgroup = $row[2];
        $headline = $row[3];
        $keyword = $row[4];
        $impressions = $row[5];
        $clicks = $row[6];
        $cost = $row[7];
 
        // Don't upload rows with no impressions
        if ($impressions > 0) {
            // Format the date 
            $uploadDate = date_format($date, 'Y-m-d');
 
            // If there isn't a file for the upload date, then create one
            if (!isset($uploadFiles[$uploadDate])) {
                // Add the headers to the file
                $uploadFiles[$uploadDate] = "$headerRow\n";
            }
 
            // Add the row to the file
            $uploadRow = array(
                "\"$source\"",
                "\"$medium\"",
                "\"$campaign\"",
                "\"$adgroup\"",
                "\"$headline\"",
                "\"$keyword\"",
                $impressions,
                $clicks,
                $cost
            );
            $uploadFiles[$uploadDate] .= implode(",", $uploadRow) . "\n";
        }
    }
}
 
fclose($fp);
A Script that Creates Files Ready for Upload

There's a couple of differences between this script and the examples I gave earlier:

  • I demonstrated the script in two steps—dividing the rows into dates then creating a file for each one—but we can eliminate having to iterate over the rows twice by merging the steps together.
  • In the next step of the process, we'll need to read each file in and attach it to the API request, so we might as well just store the files as strings in the $uploadFiles array.

Uploading the Files to Google Analytics

Now that the files are ready, we can upload them to Google Analytics using the upload() method of the Google_AnalyticsService::management_dailyUploads object in the Google API Client Library for PHP. Here's what we need to do:

  • Retrieve the account, web property, and data source ids.
  • Construct an authenticated Google_AnalyticsService object.
  • Call the upload() method to upload the CSV files for each date.

You can get the account and web property ids either from your tracking snippet—the web property id is the UA-YYYYYY-XX code and the account id is the YYYYYY bit in the middle of the two dashes—or you can follow my tutorial on Selecting Google Analytics Ids; to create a custom data source and get its id, check out my overview of the cost data import. And you can learn how to construct the Google_AnalyticsService object and use OAuth to authenticate it, see my Google Analytics API Authentication post.

Once you've got that covered, you can fill in the variables in the following script and upload the CSV files to GA:

$clientId = "YOUR_OAUTH_CLIENT_ID";
$clientSecret = "YOUR_OAUTH_CLIENT_SECRET";
$oauthInfo = 'YOUR_OAUTH_TOKEN';
 
$webPropertyId = "YOUR_WEB_PROPERTY_ID";
$accountId = "YOUR_ACCOUNT_ID";
$dataSourceId = "YOUR_DATA_SOURCE_ID";
 
$appendNumber = 1;
$uploadType = "cost";
 
$client = new Google_Client();
$client->setClientId($clientId);
$client->setClientSecret($clientSecret);
$client->setAccessToken($oauthInfo);
$client->setUseObjects(true);
$analytics = new Google_AnalyticsService($client);
 
foreach (array_keys($uploadFiles) as $uploadDate) {    $analytics->management_dailyUploads->upload(            $accountId, $webPropertyId, $dataSourceId, $uploadDate,             $appendNumber, $uploadType, array(        "reset" => true,        "data" => $uploadFiles[$uploadDate],        'mimeType' => 'application/octet-stream',        'uploadType' => 'media'));}
A Script that Uploads Each Days Data to Google Analytics

Comments

I'm keen to get feedback on my posts, so if you have any questions or comments, then please send me a message and I'll be happy to help.