Configuring Analytics Reporting For Checkout By Amazon Orders

Today’s blog post comes to us from Miva Merchant Associate Web Developer, Kyle Newbrough.

If you are familiar with Checkout By Amazon, you may know that the common methods for reporting order information to Google Analytics are not readily available to you. I recently had an opportunity to tackle this problem, and created an effective workaround. I’ll explain the problem and the process for the benefit of anyone interested in selling with Amazon, but if you aren’t looking for the technical details you can skip to the end for the ready-to-use code.

Dependencies

• Tool Belt by PCI Net
• Tool Kit by Emporium Plus

When a customer places an order with Checkout By Amazon, all of the order information which would normally be processed by your store in real-time is instead processed by Amazon and then sent to your store in the form of an order around 90 minutes later. This means that when the customer lands on your Amazon success page, there is no actual order data in the store and the tracking information, which is usually available to send to your analytics profile, does not exist yet.

So what do we have access to at the time when the customer reaches your “Amazon Order Success” page? It turns out the store does remember some of the order information in a set of temporary tables in the database. This includes all product information, as well as a “Shadow ID” which is a temporary Order ID and corresponds to each order’s contents. The same Shadow ID variable is available on the Amazon order Thank You page, which we’ll use as a hook to get the information we need.

This is good news because the product information is probably the most important thing to be reporting to Analytics. This is the information which should partially drive your business decisions for your product line and for advertising. There is some data we can’t obtain, however.

Since the customer enters their personal information and shipping selection on Amazon’s servers, the address information, shipping method/price and tax information cannot be accessed at the time of checkout. To get around this we’ll be sending dummy data (fees of “0.00”) or leaving these fields blank when modifying the reporting code. If you have flat rate shipping, you can use that charge amount as a static value for shipping.

In order to retrieve the product information prior to sending it to the analytics profile, we’ll need to talk directly to the database using SQL queries via PCI Net’s Tool Belt module.

The database tables we’ll be working with are named s01_CBAmazonBasketItems and s01_CBAmazonBasketOptions.

<mvt:item name="toolkit" param="vassign|shadow_id|g.Shadow_Basket:SHADOW_ID">
<mvt:item name="toolkit" param="vassign|order_id|g.amznPmtsOrderIds">
<mvt:item name="ry_toolbelt" param="assign|g.sql|'SELECT * FROM s01_CBAmazonBasketItems 
WHERE (shadow_id = `' $ g.shadow_id $ '`)'">
<mvt:item name="ry_toolbelt" param="assign|g.sql2|'SELECT * FROM s01_CBAmazonBasketOptions 
WHERE (shadow_id = `' $ g.shadow_id $ '`)'">
<mvt:item name="ry_toolbelt" param="query|g.sql|CBA_items">
<mvt:item name="ry_toolbelt" param="query|g.sql2|CBA_options">

First, we grab the order’s Shadow ID and rename it to a variable which is easier to work with, using Tool Kit vassign. Then we do the same with the 17-digit Order # which Amazon sends us. Since we don’t yet have an Order # from your store, we may as well use Amazon’s Order #. Next, we declare the SQL queries, which we’ll need to create our product arrays and then run them using Tool Belt assign and query, once for the list of products and again for the product attributes.

Now we need to generate an order total:

<mvt:item name="toolkit" param="sassign|order_total|0">
<mvt:foreach iterator="CBA_item" array="CBA_items">
  <mvt:if expr="l.settings:CBA_item:quantity GT 1">
    <mvt:item name="toolkit" param="math_multiply|item_subtotal|l.all_settings:CBA_item:
quantity|l.all_settings:CBA_item:price">
    <mvt:item name="toolkit" param="math_add|order_total|g.order_total|g.item_subtotal" >
  <mvt:else>
    <mvt:item name="toolkit" param="math_add|order_total|g.order_total|l.all_settings:CBA_item:price">
  </mvt:if>
  <mvt:foreach iterator="CBA_option" array="CBA_options">
    <mvt:if expr="(l.settings:CBA_item:line_id EQ l.settings:CBA_option:line_id) AND 
(l.settings:CBA_option:price NE '0.00')">
      <mvt:if expr="l.settings:CBA_item:quantity GT 1">
        <mvt:item name="toolkit" param="math_multiply|option_subtotal|l.all_settings:CBA_
item:quantity|l.all_settings:CBA_option:price">
        <mvt:item name="toolkit" param="math_add|order_total|g.order_total|g.option_subtotal">
      <mvt:else>
         <mvt:item name="toolkit" param="math_add|order_total|g.order_total|l.all_settings:CBA_option:price">
      </mvt:if>      
    </mvt:if>
  </mvt:foreach>
</mvt:foreach>

This block of code declares a variable for the order total which we can perform math on, and then we cycle through each product in the product array we created above, adding the price of each product to the order total as we go using Tool Kit’s math_add. We are also cycling through all attributes for each product, checking only the ones which have a matching Line ID with the current product in the array, and if an attribute has a price that isn’t “0.00” then the price is added to the order total. Once all products and attributes have been processed, we’re left with a variable containing the order total.

From here it is just a matter of adapting your data reporting scripts to use the new arrays and variables we’ve set up. Let’s use Google Analytics as an example, since any store owner who collects order data will likely be using that service.

 

 <script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-XXXXX-X']);
_gaq.push(['_trackPageview']);
_gaq.push(['_addTrans',
'&mvt:global:order_id;',
'',
'&mvt:global:order_total;',
'0.00',
'0.00',
'',
'',
''
]);
<mvt:foreach iterator="CBA_item" array="CBA_items">
_gaq.push(['_addItem',
'&mvt:global:order_id;',
'&mvt:CBA_item:code;',
'&mvt:CBA_item:name;',
'',
'&mvt:CBA_item:price;',
'&mvt:CBA_item:quantity;'
]);
<mvt:foreach iterator="CBA_option" array="CBA_options">
<mvt:if expr="l.settings:CBA_item:line_id EQ l.settings:CBA_option:line_id">
_gaq.push(['_addItem',
'&mvt:global:order_id;',
'&mvt:CBA_option:attr_code;',
'&mvt:CBA_option:opt_code;',
'',
'&mvt:CBA_option:price;',
'1'
]);
</mvt:if>
</mvt:foreach>
</mvt:foreach>
_gaq.push(['_trackTrans']);
</script>

There are various other reporting scripts out there and they have different formats but they all use the same pieces of information. You should be able to adapt most other reporting scripts to work with Checkout By Amazon using the above method.

Below is the code all together. You can paste this code just before the </body> or the closing tag of the body mvt item, and it should work right out of the box. Make sure you have Tool Kit and Tool Belt assigned to the page, and then run a test order (you’ll have to place a real order and then refund yourself to test Checkout By Amazon). Don’t forget to enter your Google Analytics profile number and “Amazon Order Success” page code denoted by comments in the script below.

<mvt:item name="toolkit" param="vassign|shadow_id|g.Shadow_Basket:SHADOW_ID"/>
<mvt:item name="toolkit" param="vassign|order_id|g.amznPmtsOrderIds" />
<mvt:item name="ry_toolbelt" param="assign|g.sql|'SELECT * FROM s01_CBAmazonBasketItems WHERE 
(shadow_id = `' $ g.shadow_id $ '`)'"/>
<mvt:item name="ry_toolbelt" param="assign|g.sql2|'SELECT * FROM s01_CBAmazonBasketOptions 
WHERE (shadow_id = `' $ g.shadow_id $ '`)'"/>
<mvt:item name="ry_toolbelt" param="query|g.sql|CBA_items"/>
<mvt:item name="ry_toolbelt" param="query|g.sql2|CBA_options"/>
<mvt:item name="toolkit" param="sassign|order_total|0"/>
<mvt:foreach iterator="CBA_item" array="CBA_items">
  <mvt:if expr="l.settings:CBA_item:quantity GT 1">
    <mvt:item name="toolkit" param="math_multiply|item_subtotal|l.all_settings:CBA_item:quantity|l.all_
settings:CBA_item:price"/>
    <mvt:item name="toolkit" param="math_add|order_total|g.order_total|g.item_subtotal"/>
  <mvt:else>
    <mvt:item name="toolkit" param="math_add|order_total|g.order_total|l.all_settings:CBA_item:price"/>
  </mvt:if>
  <mvt:foreach iterator="CBA_option" array="CBA_options">
    <mvt:if expr="(l.settings:CBA_item:line_id EQ l.settings:CBA_option:line_id) AND (l.settings:CBA_option:
price NE '0.00')">
      <mvt:if expr="l.settings:CBA_item:quantity GT 1">
        <mvt:item name="toolkit" param="math_multiply|option_subtotal|l.all_settings:CBA_item:quantity|l.all_
settings:CBA_option:price"/>
        <mvt:item name="toolkit" param="math_add|order_total|g.order_total|g.option_subtotal"/>
      <mvt:else>
         <mvt:item name="toolkit" param="math_add|order_total|g.order_total|l.all_settings:CBA_option:price"/>
      </mvt:if>      
    </mvt:if>
  </mvt:foreach>
</mvt:foreach>
<script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-XXXXX-X']); //Enter your Google Analytics profile # here
_gaq.push(['_trackPageview', 'CBA-INVC']); // Enter your CBA success page code here
_gaq.push(['_addTrans',
'&mvt:global:order_id;',
'',
'&mvt:global:order_total;',
'0.00',
'0.00',
'',
'',
''
]);
<mvt:foreach iterator="CBA_item" array="CBA_items">
_gaq.push(['_addItem',
'&mvt:global:order_id;',
'&mvt:CBA_item:code;',
'&mvt:CBA_item:name;',
'',
'&mvt:CBA_item:price;',
'&mvt:CBA_item:quantity;'
]);
<mvt:foreach iterator="CBA_option" array="CBA_options">
<mvt:if expr="l.settings:CBA_item:line_id EQ l.settings:CBA_option:line_id">
_gaq.push(['_addItem',
'&mvt:global:order_id;',
'&mvt:CBA_option:attr_code;',
'&mvt:CBA_option:opt_code;',
'',
'&mvt:CBA_option:price;',
'1'
]);
</mvt:if>
</mvt:foreach>
</mvt:foreach>
_gaq.push(['_trackTrans']);

  (function() {
    var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
    ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'https://www') + '.
google-analytics.com/ga.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
  })();
</script>