Two of Growthmill’s clients recently reached out to better understand the various fees associated with transactions that eventually transferred to Connect accounts. In this situation, Destination Charges were being used so the Platform account was absorbing the fees. Both clients were interested in understanding which fees could be attributed to specific Connect accounts.
Stripe has some out-of-the-box reporting that helps solve for this but current solutions are not friendly or intuitive to infrequent Stripe users. Asking a non-Stripe person – like a CFO – to “self-serve” from the Reports section of the Stripe Dashboard is unrealistic. Adding to the confusion, success in answering questions from Stripe Reporting is dependent on the Report Category selected and the columns selected. The end product is a CSV which inevitably gets dumped into Excel for manipulation and a Pivot Table.
The more viable long-term solution for custom or even refined reporting is to implement Stripe Sigma. Sigma exposes the various Stripe entities as tables that can be joined and queried via SQL statements. The SQL syntax is based on Presto, which is ANSI SQL and should be familiar to anyone who knows SQL language. A link to the syntax can be helpful, especially for understanding nuances with the functions and operators.
Using Sigma, there are several approaches to querying fees. The first thing to understand is in Stripe the term fees covers a lot of different fees associated with a transaction. Fees in Stripe run the gamut from the Stripe fee associated with the IC+ transaction to fees associated with Radar screening, to Connect payout fees, to 3DS fees, to Authorization Optimization Fees, Card Update Fees, Chargeback Fees, Connect Account Volume Billing, Connect Active Account fees, to Klarna Fees and Affirm Fees.
Most of these fees are available in a few different locations in the Sigma schema
Fees associated with card transactions, broken down by the fee category, card brand, card type, the type of plan etc. Icplus_fees is a super detailed place to break down the fees associated with transacting a card payment. Each record here is tied to a Stripe Connect account and can be joined to the connected_accounts table to pull in a name
Any fee associated with a balance transaction, which is everything really, can be found here. Network fees are rolled up a bit relative to the breakout in the icplus_fees table but other fees, like chargeback fees, Radar, Connect Payout fees, Card Updater fees, etc…even the fee of using Sigma is here. This isn’t the place to associate fees with Connect accounts since these are balance changes related to the platform account. Brining in the Connect Account requires joins through additional tables, as described in the next example.
Want to know the processing fees associated with a buy-now-pay-later transaction?
Query the charges tables joined to the balance_transaction_fee_details table on balance_transaction_id and filter by payment_method_type = ‘klarna’. The amount field from the balance_transaction_fee_details will give you the processing fee. An additional join from the charges to the connect_account table can bring in the Connect Account name.
This table is a denormalized table containing pieces of all three examples previously reviewed. It is the simplest way to query all the fee data related to a Connect account. There are reporting categories and balance transaction components that describe the fee. It also has information on the payment_method_type for pulling out things like BNPL fees. A little bit of string manipulation on the balance_transaction_description field can give you further detailed grouping information. Growthmill will often use the functions split_part(ari.balance_transaction_description,' (',1) and split_part(ari.balance_transaction_description,'):',2) to add two additional groupings for things like Radar fees vs Card Account Updater fees.
One solution we’re still working towards is to associate the Radar / 3DS / Authorization Optimization Fees with specific transactions. Right now, all those fees are only associated with the Platform account and we’ve yet to understand if the Sigma schema supports associating those with specific transactions. If that can be solved, we can associate individual transactions with the Connect accounts.
