and the number of active customers within a given month This template returns a basic monthly recurring revenue number as the product of the average subscription revenue per user (ARPU) Other_transactions/100.0 as other_transactions Other_adjustments/100.0 as other_adjustments, Sum(daily_balance) over(partition by currency order by day)/100.0 as current_balance,Ĭhargeback_withdrawals/100.0 as chargeback_withdrawals,Ĭhargeback_reversals/100.0 as chargeback_reversals, Compute the current_balance for each day and format output Sum(case when type not in ('payout', 'transfer', 'charge', 'payment', 'refund', 'payment_refund', 'adjustment') then net else 0 end) as other_transactions Sum(case when type = 'adjustment' and lower(description) not like 'chargeback withdrawal%' and lower(description) not like 'chargeback reversal%' then net else 0 end) as other_adjustments, Sum(case when type = 'adjustment' and lower(description) like 'chargeback reversal%' then net else 0 end) as chargeback_reversals, Sum(case when type = 'adjustment' and lower(description) like 'chargeback withdrawal%' then net else 0 end) as chargeback_withdrawals, Sum(case when type = 'transfer' then net else 0 end) as transfers, Sum(case when type in ('payment_refund', 'refund', 'payment_failure_refund') then net else 0 end) as refunds, Sum(case when type in ('charge', 'payment') then net else 0 end) as payments, - net = amount - fee Sum(case when type 'payout' then net else 0 end) as net_transactions, Sum(case when type = 'payout' then net else 0 end) as payouts, payouts are considered when they are posted (available_on)ĭate(case when type = 'payout' then available_on else created end) as day, balance_transaction types on a daily basis for each currency daily_balance_transactions is a temporary table that aggregates and pivots different This template returns the balance at the end of every day in the UTC time zone Order by transfers.date desc, transfers.id, balance_transactions.created desc On refunds.id = balance_transactions.source_idĪnd transfers.date >= date_add('day', -30, current_date) ![]() On disputes.id = balance_transactions.source_id On charges.id = balance_transactions.source_id - balance_transactions.source_id can be used to join on charges, disputes, and refunds table. On balance_tomatic_transfer_id = transfers.id 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, ![]() 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, This template returns a monthly summary of your activities, including all payments in the UTC time zoneĭ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 This template returns a complete summary of activities for every month with live transactions
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |