![]() ![]() Transfers.amount/100.0 as transfer_amount,īalance_transactions.id as balance_transaction_id,īalance_transactions.source_id as balance_transaction_source_id,īalance_transactions.type as balance_transaction_type,īalance_transactions.amount/100.0 as balance_transaction_amount,īalance_/100.0 as balance_transaction_net,Ĭharges.statement_descriptor as statement_descriptor, automatic transfers within the past 30 days This template returns itemized information for transfers/payouts linked to Where month < date_trunc('month', current_date) - exclude current, partial month Sum(monthly_net_activity + gross_payouts) over(partition by currency order by month)/100.0 as month_end_balance, - use SUM Window Function Monthly_net_activity/100.0 as monthly_net_activity, Net_transactions / 100.0 as net_transactions, Gross_transactions / 100.0 as gross_transactions, Note: if you have currencies that do not have cents (e.g. Compute the month_end_balance for each month and format output Sum(case when type = 'payout' or type like '%transfer%' then fee * -1.0 else net end) as monthly_net_activity,Ĭount_if(type in ('payment', 'charge')) as sales_count,Ĭount_if(type = 'payout') as payouts_count,Ĭount(distinct case when type = 'adjustment' then source_id end) as adjustments_count Sum(case when type = 'payout' or type like '%transfer%' then amount else 0 end) as gross_payouts, Sum(case when type = 'payout' or type like '%transfer%' then fee * -1.0 else 0 end) as payout_fees, Sum(case when type 'payout' and type not like '%transfer%' then net else 0 end) as net_transactions, Sum(case when type 'payout' and type not like '%transfer%' then amount else 0 end) as gross_transactions, Sum(case when type not in ('charge', 'payment', 'payment_refund', 'refund', 'adjustment', 'payout') and type not like '%transfer%' then amount else 0 end) as other, ![]() Sum(case when type = 'adjustment' then amount else 0 end) as adjustments, Sum(case when type in ('payment_refund', 'refund') then amount else 0 end) as refunds, Sum(case when type in ('charge', 'payment') then amount else 0 end) as sales, Note: only months with at least 1 balance transaction will be returnedĭate_trunc('month', case when type = 'payout' then available_on else created end) as month, - payouts are considered when they are posted (available_on) balance_transaction types on a monthly basis for each currency monthly_balance_transactions is a temporary table that aggregates and pivots different Note: this query may need to be modified to handle additional payout/transfer types if you are using Connect Important : Both the API token and the Client ID are required for authentication.- This template returns a monthly summary of your activities, including all payments in the UTC time zone Hover over the token’s Client ID and click copy.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |