Tracking PayPal Payments in Google AdWords

in PPC Tracking

This article presents a solution to track conversions from PayPal Instant Payment Notifications (IPN) in AdWords. The most common way to track PayPal payments is using a tracking pixel on the “thank-you” page that PayPal sends users to after completing a transaction, but this is error prone if the user doesn't return to the page or payment is later declined. After writing a tutorial exploring the new AdWords API Offline Conversion Tool, I was thinking of problems that could be solved using it and one idea was for tracking server-side conversions like IPN notifications.

Tracking Server-side Conversions with the Conversion Upload

With the offline conversion import, you can now send conversion events to AdWords from server-side code using the AdWords API. You can read my previous post to find out how it works, but I'm going to explain the solution to the present problem using the following steps:

  • Capture the Google click id and store it in a cookie.
  • Insert the click id into the custom parameter of a PayPal “Pay Now” button.
  • Retrieve the click id from the custom parameter of a completed PayPal payment IPN.
  • Save the click id and payment amount in a file.
  • Retrieve the payments from the file and upload each conversion to AdWords.

There are several reasons why you should store the notification data instead of uploading the conversions while processing each IPN:

  • You can't upload offline conversions too soon after a click: you need to wait four to six hours or else you'll get an error.
  • You could loose the conversion data if the upload fails.
  • PayPal prefers your IPN listener to respond quickly without running slow backend processes.
  • It's better to batch AdWords API operations where possible.

Instead of using a file, you'll probably want to use a database or maybe a message queue in a production system to avoid any possible locking errors, but I'm using a file here to keep the explanation simple.

Inserting the Click Id into a PayPal Buy Now Button

You can capture the click ids on your landing pages using the JavaScript I outlined in my last post, which will place the click id into a cookie that you can then place into a PayPal button:

<form action="https://www.paypal.com/cgi-bin/webscr" method="post">
    <input type="hidden" name="business" value="[email protected]">
    <input type="hidden" name="cmd" value="_xclick">
    <input type="hidden" name="item_name" value="Your Product">
    <input type="hidden" name="amount" value="8.55">
    <input type="hidden" name="currency_code" value="USD">
    <input type="hidden" name="notify_url" value="http://www.example.com/paypal_ipn.php">    <input type="hidden" name="custom" value="<?php print $_COOKIE["gclid"]; ?>"> 
    <input type="image" name="submit" border="0"
    src="https://www.paypalobjects.com/en_US/i/btn/btn_buynow_LG.gif"
    alt="PayPal - The safer, easier way to pay online">
    <img alt="" border="0" width="1" height="1"
    src="https://www.paypalobjects.com/en_US/i/scr/pixel.gif" >
</form>
A Buy Now Form with a Click Id

This is a standard button with an additional custom parameter containing the click id saved in the gclid cookie, and a notify_url parameter that is pointing to a script that I'll discuss in the next section. If you're already using IPN, the notify_url should be your existing script; otherwise, you need to create a new script and point the notify_url to it.

Extracting the Click Ids from the IPN Notifications

You now need to collect the conversions from the payment notifications sent by PayPal. We only want to report conversions for completed payments, so I'll start by checking if the IPN is for a completed payment:

if (isset($_REQUEST["payment_status"]) && $_REQUEST["payment_status"] === "Completed") {
   // …
}
Checking if the Payment has Completed

You can extract the click id and payment amount from the custom and mc_gross parameters:

$clickId = $_REQUEST["custom"];
$paymentAmount = $_REQUEST["mc_gross"];
Extracting the Click Id and Amount

Next we need to extract the payment date:

define("DATE_FORMAT", "H:i:s M d, Y");
 
$paymentDate = $_REQUEST["payment_date"];
$timezonePos = strrpos($paymentDate, " ");
$timezone = substr($paymentDate, $timezonePos + 1);
$paymentDate = substr($paymentDate, 0, $timezonePos);
 
$conversionDate = DateTime::createFromFormat(
    DATE_FORMAT, $paymentDate, new DateTimeZone($timezone)
);
$conversionDate = $conversionDate->getTimestamp();
Extracting the Payment Date

This code converts the payment_date parameter to a timestamp by matching it to a date format; the script also extracts the timezone as it can vary depending on the season.

Once the parameters have been extracted, you can append them to a file ready for uploading to AdWords. Here's the full script:

define("IPN_CONVERSIONS_FILE", "/tmp/ipn-conversions.csv");
define("DATE_FORMAT", "H:i:s M d, Y");
 
if (isset($_REQUEST["payment_status"]) && $_REQUEST["payment_status"] === "Completed") {
    $clickId = $_REQUEST["custom"];
    $paymentAmount = $_REQUEST["mc_gross"];
 
    $paymentDate = $_REQUEST["payment_date"];
    $timezonePos = strrpos($paymentDate, " ");
    $timezone = substr($paymentDate, $timezonePos + 1);
    $paymentDate = substr($paymentDate, 0, $timezonePos);
 
    $conversionDate = DateTime::createFromFormat(
                    DATE_FORMAT, $paymentDate, new DateTimeZone($timezone)
    );
    $conversionDate = $conversionDate->getTimestamp();
 
    $fp = fopen(IPN_CONVERSIONS_FILE, "a");
    fputcsv($fp, array($clickId, $paymentAmount, $conversionDate));
    fclose($fp);
}
An IPN Listener Script for AdWords Conversions

If this script runs correctly, you should end up with a file something like the following (headings added for clarity):

Click ID Amount Date
CMbBkZi12roCFZlq1tAdwaXYADD 7.95 1384385133
SJFKS23a12roCFZMdtAodeaaYAaQ 7.95 1384385054
ABbBkZi12roCDDSdtAodeWQAvW 8.55 1384385211

In the next section I'll show you how to upload these conversions.

Uploading the Conversions to AdWords

Before uploading the conversions, you need to create a new Import conversion type in AdWords, which is discussed in my previous article so I won't repeat it here. Once you have a conversion type, you can upload your conversions using the following script:

define("CONVERSION_NAME", "Sale");
define("IPN_CONVERSIONS_FILE", "/tmp/ipn-conversions.csv");
 
$fp = fopen(IPN_CONVERSIONS_FILE, "r");
 
$offlineConversionService = $user->GetService("OfflineConversionFeedService", API_VERSION);
 
$operations = array();
while ($row = fgetcsv($fp)) {
    $timestamp = $row[2]; 
    $gclid = $row[0]; 
    $convValue = $row[1];
    $convDate = date(DATE_FORMAT, $timestamp) . " " . DATE_TIMEZONE;
    $offlineConversion = new OfflineConversionFeed(
            $gclid, CONVERSION_NAME, $convDate, $convValue
    );
    $operations[] = new OfflineConversionFeedOperation($offlineConversion, "ADD");
}
fclose($fp);
 
$result = $offlineConversionService->mutate($operations);
 
unlink(IPN_CONVERSIONS_FILE);
Uploading the File to AdWords

This script opens the file, creates an OfflineConversionFeed for each row, and then uploads the conversions to AdWords; once the API call completes the script deletes the conversions file ready for the next batch of payment notifications.

A problem with this script is that you'll get an error if you try to import a too recent click. You can remove this problem by only uploading conversion events that happened at least six hours ago:

$maxDate = strtotime("-6 hours");
$skippedConversions = array();
while ($row = fgetcsv($fp)) {
    $timestamp = $row[2]; 
    if ($timestamp <= $maxDate) {
    // Create conversion object….
    } else {
        $skippedConversions[] = $row;
    }
}
Filtering Out Recent Conversions

This version checks that the timestamp is at least six hours old, and stores newer conversions in the $skippedConversions array. You can now drop the old file and place the skipped conversions into it:

unlink(IPN_CONVERSIONS_FILE);
 
$fp = fopen(IPN_CONVERSIONS_FILE, "w");
foreach ($skippedConversions as $row) {
    fputcsv($fp, $row);
}
fclose($fp);
Storing the Skipped Conversions

This should run without any errors, but to protect your conversion data in case any unforeseen errors occur, you should use the AdWords API Partial Failure feature. This registers all your successful conversions and returns any that have failed. To use it, set the partialFailure header then process the partialFailureErrors property of the API call result:

$user->SetHeaderValue("partialFailure", true);
 
// Rest of script….
 
$result = $offlineConversionService->mutate($operations);
 
if (isset($result->partialFailureErrors)) {
    foreach ($result->partialFailureErrors as $error) {
        $index = ErrorUtils::GetSourceOperationIndex($error);
        if (isset($index)) {
            $offlineConversion = $operations[$index]->operand;
            $conversionDate = substr($offlineConversion->conversionTime, 0, 15);
            $conversionDate = DateTime::createFromFormat(
                            DATE_FORMAT, $conversionDate, new DateTimeZone(DATE_TIMEZONE)
            );
            $skippedConversions[] = array(
                $offlineConversion->googleClickId,
                $offlineConversion->conversionValue,
                $conversionDate->getTimestamp()
            );
        }
    }
}
Processing the Failed Conversions

This loops through each error—if there are any—extracts the conversion object, and uses it to reconstruct the original row from the file (some work is also done to parse the date into a timestamp). The script will then write these rows back to the file together with the skipped conversions.

Here's a full script that puts all of this together with additional boilerplate API access code:

<?php
 
set_include_path(get_include_path() . PATH_SEPARATOR .
        dirname(__FILE__) . "/adwords_api_php_4.6.1/src");
require_once("Google/Api/Ads/AdWords/Lib/AdWordsUser.php");
require_once("Google/Api/Ads/Common/Util/ErrorUtils.php");
 
define("OAUTH_CLIENT_ID", "[YOUR CLIENT ID]"); 
define("OAUTH_CLIENT_SECRET", "[YOUR CLIENT SECRET]"); 
define("OAUTH_ACCESS_TOKEN", "[YOUR ACCESS TOKEN]"); 
define("OAUTH_REFRESH_TOKEN", "[YOUR REFRESH TOKEN]"); 
define("CUSTOMER_ACCOUNT_ID", "[YOUR ACCOUNT ID]"); 
define("DEVELOPER_TOKEN", "[YOUR DEVELOPER TOKEN]"); 
define("API_VERSION", "v201309");
 
define("DATE_FORMAT", "Ymd His");
define("DATE_TIMEZONE", "UTC");
define("CONVERSION_NAME", "[YOUR_CONVERSION_NAME]");
define("IPN_CONVERSIONS_FILE", "/tmp/ipn-conversions.csv");
 
$user = new AdWordsUser();
$user->SetDeveloperToken(DEVELOPER_TOKEN);
$user->SetClientCustomerId(CUSTOMER_ACCOUNT_ID);
 
$user->SetOAuth2Info(array(
    "client_id" => OAUTH_CLIENT_ID,
    "client_secret" => OAUTH_CLIENT_SECRET,
    "access_token" => OAUTH_ACCESS_TOKEN,
    "refresh_token" => OAUTH_REFRESH_TOKEN
));
 
$handler = $user->GetOAuth2Handler();
$oauth2Info = $handler->RefreshAccessToken($user->GetOAuth2Info());
$user->SetOAuth2Info($oauth2Info);
 
// Select the partial failure option
$user->SetHeaderValue("partialFailure", true);
 
// Read your conversions from the file
$fp = fopen(IPN_CONVERSIONS_FILE, "r");
 
// Check that the conversions were loaded succesfully
if (!$fp) {
    throw new Exception("Error opening conversions file: " . IPN_CONVERSIONS_FILE);
}
 
// Load the conversion upload service
$offlineConversionService = $user->GetService("OfflineConversionFeedService", API_VERSION);
 
// Don't upload clicks that are less than six hours old, or you might get an error
$maxDate = strtotime("-6 hours");
 
// An array of AdWords API operations
$operations = array();
 
// The conversions that have been skipped because they're too new
$skippedConversions = array();
 
// Create an offline conversion for each row in the file
while ($row = fgetcsv($fp)) {
    $timestamp = $row[2]; // The conversion date from the third column
    // Check that the conversion was at least six hours ago
    if ($timestamp <= $maxDate) {
        $gclid = $row[0]; // The click id from the first column
        $convValue = $row[1]; // The payment amount from the second column
        // Format the date as reuqired by the API
        $convDate = date(DATE_FORMAT, $timestamp) . " " . DATE_TIMEZONE;
        // Create the conversion object
        $offlineConversion = new OfflineConversionFeed(
                $gclid, CONVERSION_NAME, $convDate, $convValue
        );
        // Create an operation for the object
        $operations[] = new OfflineConversionFeedOperation($offlineConversion, "ADD");
    } else {
        // Store the skipped rows to upload next time
        $skippedConversions[] = $row;
    }
}
fclose($fp);
 
// Call the API
$result = $offlineConversionService->mutate($operations);
 
// Add any error rows to the skipped conversions to try again next time
if (isset($result->partialFailureErrors)) {
    foreach ($result->partialFailureErrors as $error) {
        $index = ErrorUtils::GetSourceOperationIndex($error);
        if (isset($index)) {
            // Recreate the conversion row, and add it to the skipped rows
            $offlineConversion = $operations[$index]->operand;
            $conversionDate = substr($offlineConversion->conversionTime, 0, 15);
            $conversionDate = DateTime::createFromFormat(
                            DATE_FORMAT, $conversionDate, new DateTimeZone(DATE_TIMEZONE)
            );
            $skippedConversions[] = array(
                $offlineConversion->googleClickId,
                $offlineConversion->conversionValue,
                $conversionDate->getTimestamp()
            );
        }
    }
}
 
// Save the skipped conversions
unlink(IPN_CONVERSIONS_FILE);
 
$fp = fopen(IPN_CONVERSIONS_FILE, "w");
foreach ($skippedConversions as $row) {
    fputcsv($fp, $row);
}
fclose($fp);
Importing IPN Conversions into AdWords

← Using the AdWords API Offline Conversion Import Tool

To help me decide what to write about, I'd like to asses the value of my blog posts to see which ones people find most beneficial. If you found the information here useful then could you please +1 it, but if you didn't find what you were looking for then please leave a comment and I'll be happy to help where I can. Thanks!

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.