breaking down Payouts into products, clientIds, or other "sources"

[Nicholas Jackiw]

I am a newbie and this feels to me like a newbie question.

I am using Checkouts and have an e-commerce model in which each checkout is for a single purchasable product. I markup the Session at creation with the "type of product" both using session.client_reference_id and session.lineItems[0].product_data as in the following fragment. (Both $regId and $productData are product-type-specific codes in my inventory.)

$checkout_session = \Stripe\Checkout\Session::create([

  'payment_method_types' => ['card'],

  'customer_email' => $reg['billEmail'],

  'line_items' => [[

    'price_data' => [

      'currency' => 'cad',

      'unit_amount' => 100*$reg['price'],

      'product_data' => $productData,

    ],

    'quantity' => 1,

  ]],

  'client_reference_id' =>$regId,

  'mode' => 'payment',

  'success_url' => 'myserver/stripe_success.php?stripe_id={CHECKOUT_SESSION_ID}',

  'cancel_url' => 'myserver/checkout.php',

]);

A bunch of customers buy a bunch of products creating a bunch of Payments which eventually get bulked together and dispersed to my bank account as a Payout. Now I would like to report in the reverse direction. I want to report on a date-span of Payouts, and see how many of which of my products I am actually earning money for.

I realize I compute this in the forward direction from my proprietary sales records, but I want to actually audit the post-fees Stripe Payout data to verify my sales records.  

I can't figure out how to do this using any stock reports. Payout Reconciliation Detail sounds like the right report---"Detailed breakdown of the total payouts line from the above Balance Summary report. Includes all activity that makes up the payouts in this time range, as well as itemized downloads."--and it definitely breaks down payments into their separate discrete charges and payoutIntents. But none of the 42 possible custom fields I can request for that report seem to contain either of the Session data I'm using above to code that charge's actual product type.  (I *can* see the type of product ordered if I open up an individual paymentIntent in dashboard; but I can't see how to report it.)

I'm equally lost how to build a custom report. From the API I can easily get all the payouts. But how do I then find all the paymentIntents bundled into a specific payout? Reading the docs, I can see how to reason from a paymentIntent to the CheckoutSession and from that to the lineItems that will describe my product, but I don't see how to determine which paymentIntents contribute to an actual Payout that I've fielded in my bank account?  I must be missing something obvious.

Thanks for your help,

Nick

[Remi J.]

Hey Nick,

We don't have a specific pre-built report that would have details about the Checkout Sessions directly tied to the individual Payouts unfortunately today. It's definitely something we want to improve in the future as we add more features to those reports but for now they focus more on the payments themselves and the flow of funds in your account. This means that you will need to reconcile those individual payments back to their corresponding Checkout Sessions on your end one way or another.

There are two approaches that you can take in that case. The easiest is to use the List Checkout Sessions API [1] and pass a specific PaymentIntent id (pi_123) in the `payment_intent` parameter. This will simply return one Checkout Session that is associated with that specific PaymentIntent. As you do this, you need to also use the Expand feature [3] because you want to retrieve that Session's list of line items to be able to tie down that specific payment to what the customer paid for in Checkout. This is explained in more details here [4]. The alternative approach is to list all Sessions first and track this in memory and then map them back to all your payments in memory directly.

While you can use the Reporting APIs to figure out what's in a given Payout, you can also use the API for this. The process I'll describe can seem complex at first but it really ends up being fairly simple once it clicks. It's important to understand how most objects in our API are linked together.

The way you start is by using the List Balance Transactions API [5] and passing a specific Payout id (po_123) in the `payout` parameter [6]. If you do this, you will get every BalanceTransaction object [7] that is included in that specific Payout. A BT object represents the money that moves into your account such as getting $100 minus our fee for a payment or getting debited $50 for a partial refund.  Now, getting BT is useful to start but what you really want is to get to the original object that caused the money movement. Every BT has a `source` property [8] that is basically the id of the object that caused it so you can have `source: "ch_123"` or `source: "re_abc"` for example. What you care about is the whole object so you would use the Expand feature again to turn, in that same request, the source as the real underlying object. This is covered in more details in this doc [9].

This is definitely a lot of docs to parse and grasp. But overall, the code would look something like this in PHP (in a simplified version):

// Filter all the BTs of type Charge in that Payout

$balanceTransactions = \Stripe\BalanceTransaction::all([

'payout' => 'po_123',

'type' => 'charge',

'expand' => ['data.source'],

]);

// For each BT of type charge

foreach ($balanceTransactions->data as $bt) {

// Get the Charge's PaymentIntent id (pi_123)

$paymentIntentId = $bt->source->payment_intent;

// Find the Session for that PaymentIntent

$sessions = \Stripe\Checkout\Session::all([

'payment_intent' => $$paymentIntentId,

'expand' => ['data.line_items'],

]);

// Extract the line item's product and price (assuming just one)

$priceId = $sessions->data[0]->line_items->data[0]->price->id;

$producId = $sessions->data[0]->line_items->data[0]->price->product;

echo "{$bt->id} - {$paymentIntentId} - {$priceId} - {$productId}\\n";

}

I hope this helps get you started. You likely should reach out to our support team for follow up questions though as they will get quite into the weeds of your own specific needs and integration details. You can contact them here: https://support.stripe.com/contact

Best,

Remi

[1] https://stripe.com/docs/api/checkout/sessions/list

[2] https://stripe.com/docs/api/checkout/sessions/list#list_checkout_sessions-payment_intent

[3] https://stripe.com/docs/expand

[4] https://stripe.com/docs/expand#includable-properties

[5] https://stripe.com/docs/api/balance_transactions/list

[6] https://stripe.com/docs/api/balance_transactions/list#balance_transaction_list-payout

[7] https://stripe.com/docs/api/balance/balance_transaction

[8] https://stripe.com/docs/api/balance_transactions/object#balance_transaction_object-source

[9] https://stripe.com/docs/expand/use-cases#charges-in-payout

--
To unsubscribe from this group and stop receiving emails from it, send an email to api-discuss...@lists.stripe.com.

[Nicholas Jackiw]

Remi,

This is super helpful and looks entirely straightforward. Thanks; I was missing the bridge role BT plays between payouts and paymentIntents -- and specifically the ability to enumerate BTs bundled into a given payout.  With that, the PHP follows clearly --- and also seems to take care of a lot of the mechanics. I am impressed (and delighted) by the speed and thoroughness of your response (I posted less than an hour ago; you replied with 9 useful references and topical code); the fact that it is an easy yes---or at least looks like one---is icing on the cake! Thank you.  I'm sure I'll be back and will try sup...@stripe.com again; I started there with this but didn't get any real technical engagement with my (perhaps ill-phrased) query.

Nick

[Remi J.]

I'm glad I could unblock you and that it ended up making sense! Those links between API resources and the Expand feature in the API are like a superpower once you grasp how they work. They are basically like a foreign key to another table in a database with the ability to just do a magic join at the same time!

If you ever need help about your code, Stripe has an IRC channel where my team helps developers! We're on Freenode in #stripe and we'd be happy to answer questions there as we're here most business days (and have people around the world). Just one small exception because tomorrow is a bank holiday in multiple countries so there won't be someone officially right now but you should find us easily tomorrow on US hours :)