Thursday, February 26, 2009

Data from Google Checkout

So I recently started selling things using Google Checkout and wanted an easy way to check my sales metrics. I thought it would be simple, after all, this is Google, they do gdata for most of their products. Turns out I'm wrong. gcheckout does not have a gdata API and as a result I could not use the existing google-gdata-python API to do my queries. In fact, until recently you couldn't even get an nice report of your sales using any API. Never-the-less, the available API implementations are not of much use to me, a python programmer guy. There aren't any python api examples either. So, I ended up using a Google provided RUBY implementation, some awk and some shell to get the information I needed.
Having never written Ruby this took much longer than I had wanted to spend on it. I still don't know any ruby even though I wrote ten line... heh. I also had to learn a bit of awk. I bet there are better ways of doing what I'm doing but I was in a rush.
Nasty Awk
BEGIN{
  voided_total = 0
  voided_orders = 0

  charged_total = 0
  charged_orders = 0

  new_total = 0
  new_orders = 0

  total = 0
  total_orders = 0

  raw = 0
  raw_orders = 0
}

{
  raw += $6
  raw_orders++

  if ($8 == "CHARGED") {
 charged_total += $7
 charged_orders++

    total += $6
    total_orders++
  }

  if ($8 != "PAYMENT_DECLINED" && $9 == "NEW") {
 new_total += $6
 new_orders++

    total += $6
    total_orders++
  }

  if ($8 == "CANCELLED" || $8 == "PAYMENT_DECLINED") {
 voided_total += $6
 voided_orders++
  }
}

END{
  print "Voided:\t\t" voided_total "\t(" voided_orders ")"
  print "Charged:\t" charged_total "\t(" charged_orders ")"
  print "New:\t\t" new_total "\t(" new_orders ")"
  print "Raw:\t\t" raw "\t(" raw_orders ")"
  print ""
  print "Valid:\t\t" total "\t(" total_orders ")"
}
Nasty Ruby
require "google4r/checkout"

class Test
  def myorders
    # Use your own merchant ID and Key, set use_sandbox to false for production
    configuration = { :merchant_id => '675887550112686', :merchant_key => 'AAsdffRGSgGSSHFDDAsfghj', :use_sandbox => false }
    @frontend = Google4R::Checkout::Frontend.new(configuration)
    checkout_command = @frontend.create_order_report_command(Time.local(2009,"feb",2,10,15,1), Time.new())
    response = checkout_command.send_to_google_checkout    
    puts response
    # puts response.redirect_url    
  end
end

t = Test.new
t.myorders
Nasty Shell
#!/bin/bash
ruby -I /var/lib/gems/1.8/gems/money-2.1.3/lib/ -I ~/src/google-checkout-ruby-sample-code/lib ~/get_order_info.rb |\
  awk --field-separator=',' --exec ~/get_order_info.awk
Nasty Data Sample
Google Order Number,Merchant Order Number,Order Creation Date,Currency of Transaction,Order Amount,Amount Charged,Financial Status,Fulfillment Status
328708331237305,328708331237408,"Feb 20, 2009 3:35:32 AM",USD,0.99,0.00,CANCELLED,WILL_NOT_DELIVER
521483258992859,521481258996859,"Feb 20, 2009 4:38:07 PM",USD,0.99,0.99,CHARGED,DELIVERED
141649937446182,145649937456182,"Feb 20, 2009 10:07:17 PM",USD,0.99,0.00,PAYMENT_DECLINED,NEW
211001643228570,148041643218570,"Feb 21, 2009 2:05:02 PM",USD,0.99,0.00,CHARGEABLE,NEW