--This template returns an itemized list of disputed charges for the past 30 days, --along with information such as whether evidence has been submitted and the status of the dispute -- Note: if you have currencies that do not have cents (e.g. JPY), you should not divide by 100.0 select charges.id as charge_id, date_format(disputes.created, '%Y-%m-%d') as dispute_date, date_format(charges.created, '%Y-%m-%d') as charge_date, charges.card_brand, disputes.reason, disputes.status, disputes.amount / 100.00 as dispute_amount, disputes.currency, disputes.evidence_details_has_evidence as evidence_saved, case when disputes.evidence_details_submission_count = 0 then false else true end as evidence_submitted from disputes join charges on charges.id = disputes.charge_id where date(charges.created) >= date_add('day', -30, current_date) order by 2 desc

  

    ~
  

  

        
        
        
        
          
        

-- This template returns a monthly summary of your activities, including all payments in the UTC time zone -- Note: this query may need to be modified to handle additional payout/transfer types if you are using Connect -- monthly_balance_transactions is a temporary table that aggregates and pivots different -- balance_transaction types on a monthly basis for each currency -- Note: only months with at least 1 balance transaction will be returned with monthly_balance_transactions as ( select date_trunc('month', case when type = 'payout' then available_on else created end) as month, -- payouts are considered when they are posted (available_on) currency, sum(case when type in ('charge', 'payment') then amount else 0 end) as sales, sum(case when type in ('payment_refund', 'refund') then amount else 0 end) as refunds, sum(case when type = 'adjustment' then amount else 0 end) as adjustments, 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 <> 'payout' and type not like '%transfer%' then amount else 0 end) as gross_transactions, sum(case when type <> 'payout' and type not like '%transfer%' then net else 0 end) as net_transactions, sum(case when type = 'payout' or type like '%transfer%' then fee * -1.0 else 0 end) as payout_fees, 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 net end) as monthly_net_activity, count_if(type in ('payment', 'charge')) as sales_count, count_if(type = 'payout') as payouts_count, count(distinct case when type = 'adjustment' then source_id end) as adjustments_count from balance_transactions group by 1, 2 ) -- Compute the month_end_balance for each month and format output -- Note: if you have currencies that do not have cents (e.g. JPY), you should not divide by 100.0 select date_format(month, '%Y-%m') as month, currency, sales / 100.0 as sales, refunds / 100.0 as refunds, adjustments/100.0 as adjustments, other / 100.0 as other, gross_transactions / 100.0 as gross_transactions, net_transactions / 100.0 as net_transactions, payout_fees / 100.0 as payout_fees, gross_payouts / 100.0 as gross_payouts, monthly_net_activity/100.0 as monthly_net_activity, sum(monthly_net_activity + gross_payouts) over(partition by currency order by month)/100.0 as month_end_balance, -- use SUM Window Function sales_count, payouts_count, adjustments_count from monthly_balance_transactions where month < date_trunc('month', current_date) -- exclude current, partial month order by 1 desc, 2

  

    ~
  

  

        
        
        
        
          
        

-- This template returns all unpaid invoices and relevant customer and plan information -- Note: if you have currencies that do not have cents (e.g. JPY), you should not divide by 100.0 select date(invoices.date) as invoice_date, invoices.amount_due / 100.0 as amount_due, invoices.attempt_count, invoices.customer_id, customers.email, plans.name as plan_name from invoices join subscriptions on invoices.subscription_id = subscriptions.id join plans -- join subscriptions on plans to get plan name on subscriptions.plan_id = plans.id join customers -- join on customers to get customer email on invoices.customer_id = customers.id where not invoices.paid -- filter out paid and forgiven invoices and invoices.status != 'uncollectible' order by 2 desc, 1

  

    ~
  

  

        
        
        
        
          
        

-- This template returns itemized information for transfers/payouts linked to -- automatic transfers within the past 30 days select date(transfers.date) as transfer_date, transfers.id as transfers_id, transfers.amount/100.0 as transfer_amount, transfers.status as transfers_status, balance_transactions.id as balance_transaction_id, balance_transactions.source_id as balance_transaction_source_id, balance_transactions.type as balance_transaction_type, balance_transactions.currency, balance_transactions.amount/100.0 as balance_transaction_amount, balance_transactions.net/100.0 as balance_transaction_net, charges.statement_descriptor as statement_descriptor, disputes.reason as disputes_reason, refunds.reason as refund_reason from transfers join balance_transactions on balance_transactions.automatic_transfer_id = transfers.id left join charges on charges.id = balance_transactions.source_id -- balance_transactions.source_id can be used to join on charges, disputes, and refunds table. left join disputes on disputes.id = balance_transactions.source_id left join refunds on refunds.id = balance_transactions.source_id where transfers.type = 'bank_account' and transfers.date >= date_add('day', -30, current_date) order by transfers.date desc, transfers.id, balance_transactions.created desc

  

    ~
  

  

        
        
        
        
          
        

-- This template returns gross processing volume for each currency per month, assuming a local timezone of PT -- Note: charges can change over time, for example if a charge gets refunded. -- Always use the balance transactions table if you need to create reports for accounting purposes -- charges_timezone_conversion is a temporary table that converts timestamp with UTC timezone to 'America/Los_Angeles' timezone with charges_timezone_conversion as ( select date_trunc('month', created at time zone 'America/Los_Angeles') as month, currency, amount from charges where captured -- filter out uncaptured charges ) -- Compute the monthly gross charges for each month and currency select date_format(month, '%Y-%m') as month, currency, sum(amount)/100.0 as gross_charges from charges_timezone_conversion where month >= date_add('month', -24, date_trunc('month', current_timestamp at time zone 'America/Los_Angeles')) group by 1, 2 order by 1 desc, 2