The Future of my Internship

(The start of something new.)


Officially I have a new project! Luckily it overlaps a bit with the work I’ve done on the shipping bug so I have an idea where to start. The project is about closing a small, rare gap in Bandcamp’s otherwise impeccable bookkeeping, having to do with recording the fee that PayPal charges on every sale. This information is retrieved from PayPal in the final stage of the express checkout (PayPal Express Checkout seen in My First Bug). In this stage we retrieve the fee amount along with some other details like tax amount and payment status by making the API call DoExpressCheckoutPayment. The problem is that under certain (previously) mysterious circumstances the fee does not get included in this information. From observation of the logs, it looks like when the payment status variable is ‘Pending’ then the fee isn’t returned. Kinda makes sense. Then by scouring the webapp logs we can observe what kinds of transactions are pending.

Look familiar?

all do_express_checkout_payment response params: {"CORRELATIONID"=>"993388ba666c2", "PAYMENTINFO_0_TRANSACTIONTYPE"=>"cart", "PAYMENTINFO_0_AMT"=>"5.00", "PAYMENTINFO_0_PENDINGREASON"=>"unilateral", "PAYMENTINFO_0_ACK"=>"Success", "TIMESTAMP"=>"2013-06-18T15:00:00Z", "PAYMENTINFO_0_PROTECTIONELIGIBILITYTYPE"=>"None", "INSURANCEOPTIONSELECTED"=>"false", "PAYMENTINFO_0_PAYMENTTYPE"=>"instant", "TOKEN"=>"EC-12345YY678S9000TRK", "ACK"=>"Success", "PAYMENTINFO_0_ERRORCODE"=>"0", "SUCCESSPAGEREDIRECTREQUESTED"=>"false", "PAYMENTINFO_0_ORDERTIME"=>"2013-06-18T15:00:00Z", "VERSION"=>"91.0", "BUILD"=>"6420000", "PAYMENTINFO_0_TAXAMT"=>"0.00", "PAYMENTINFO_0_CURRENCYCODE"=>"USD", "PAYMENTINFO_0_PROTECTIONELIGIBILITY"=>"Ineligible", "PAYMENTINFO_0_SELLERPAYPALACCOUNTID"=>"me@bandcamp.com", "PAYMENTINFO_0_REASONCODE"=>"None", "SHIPPINGOPTIONISDEFAULT"=>"false", "PAYMENTINFO_0_TRANSACTIONID"=>"XXI12345XX12345X", "PAYMENTINFO_0_PAYMENTSTATUS"=>"Pending"}

As far as pending transactions are concerned, I’ve found that there are two reasons: echeck and unilateral. An echeck transaction means an electronic transfer, where the payment is made with a debit/bank card, and a unilateral transaction means that PayPal doesn’t have an account set up for the band yet. PayPal lets the transaction go through and holds on to the money until the band makes their account. Eventually when the payment is processed the PayPal fee is finally calculated. This can be days or weeks after the sale, and Bandcamp currently has no way to know what it is.

If you have your own band, you can look at your sales records by visiting your band’s page and clicking on stats in your admin menu bar at the top of the screen. Then select Sales/downloads and you’ll probably see a cool graph displaying all the instances of sales over time that will distract you, but what you’re really interested in is the Tools page link at the bottom of the page. On that page you can export any increment of your sales records into a csv file with all the nitty gritty details about every payment. Here is an example of what it looks like in a band’s records right now:

Screen Shot 2013-06-27 at 1.37.10 PM

See the two that I marked yellow? Those are eCheck purchases, for which we didn’t receive the fee amount. By the end of the summer those are going to be filled in!

PayPal does provide a mechanism for informing us of the final fee, called Instant Payment Notification (IPN). Bandcamp can ask PayPal to send this at some future point after its sale, and then the fee would be updated in the database. This is my project.

A couple ideas that sprung to mind about how to go about this, including investigating the PayPal fee calculation to perhaps calculate a temporary ‘expected’ fee until the real fee is calculated. Also, in the database table where the PayPal-specific data for a transaction is stored, I should add a status column indicating whether the payment  is ‘paid’ or still ‘pending’, if that’s not already noted. I definitely need to start looking into how the payment tables in the database are set up.

Some specific questions that come to mind:

  • How are we storing DoExpressCheckoutPayment results (what tables)?
  • Are we storing the status of the transaction?
  • What are the options for statuses stored?
  • Do we store the pending reason?

But before I got the chance to answer these questions, I very embarrassingly broke my development environment. Did I finally push my 5 year old laptop into mechanical failure or was my computer simply in need of a reboot? Tune in next post for the exciting conclusion!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s