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
- Ad Group: Kitty's Crunchy Bites
- Dog Food, UK
- Ad Group: Puppy Lunch
- Ads: Nutritious Dog Food, Healthy Dog Food
- Keywords: dog food, cheap dog food
- Ad Group: Puppy Lunch
Acme Pet Food has tagged their destination URLs with tracking parameters:
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:
Acme Ads provided us with the following report for the two days:
This is what it looks like in a spreadsheet:
|Report Name:||Ad Performance Report|
|Report Date:||Apr 12, 2013|
|Account Name:||Acme Pet Food|
|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|
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:
|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|
|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_contentURL 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_mediumURL 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
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
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
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:
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:
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:
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:
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
$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:
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:
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:
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
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
- 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: