--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 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 complete summary of activities for every month with live transactions -- monthly_balance_transactions is a temporary table that aggregates and pivots different -- balance_transaction types on a monthly basis for each currency -- This template returns a monthly summary of your activities, including all payments in the UTC time zone 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 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 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 not invoices.forgiven 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

  

    ~
  

  

        
      

-- This template returns the balance at the end of every day in the UTC time zone -- daily_balance_transactions is a temporary table that aggregates and pivots different -- balance_transaction types on a daily basis for each currency with daily_balance_transactions as ( select -- payouts are considered when they are posted (available_on) date(case when type = 'payout' then available_on else created end) as day, currency, sum(net) as daily_balance, sum(case when type = 'payout' then net else 0 end) as payouts, sum(case when type <> 'payout' then net else 0 end) as net_transactions, sum(case when type in ('charge', 'payment') then net else 0 end) as payments, -- net = amount - fee sum(case when type in ('payment_refund', 'refund', 'payment_failure_refund') then net else 0 end) as refunds, sum(case when type = 'transfer' then net else 0 end) as transfers, sum(case when type = 'adjustment' and lower(description) like 'chargeback withdrawal%' then net else 0 end) as chargeback_withdrawals, 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) not like 'chargeback withdrawal%' and lower(description) not like 'chargeback reversal%' then net else 0 end) as other_adjustments, sum(case when type not in ('payout', 'transfer', 'charge', 'payment', 'refund', 'payment_refund', 'adjustment') then net else 0 end) as other_transactions from balance_transactions group by 1, 2 ) -- Compute the current_balance for each day and format output select day, currency, -- use SUM Window Function to calc. running total sum(daily_balance) over(partition by currency order by day)/100.0 as current_balance, payouts/100.0 as payouts, net_transactions/100.0 as net_transactions, payments/100.0 as payments, refunds/100.0 as refunds, transfers/100.0 as transfers, chargeback_withdrawals/100.0 as chargeback_withdrawals, chargeback_reversals/100.0 as chargeback_reversals, other_adjustments/100.0 as other_adjustments, other_transactions/100.0 as other_transactions from daily_balance_transactions order by 1 desc, 2

  

    ~
  

  

        
      

-- This template returns a basic monthly recurring revenue number as the product of the average subscription revenue per user (ARPU) -- and the number of active customers within a given month -- calendar_days is a temporary table with a row and value for every day for the past 1460 days (2 years) -- the calendar_days temporary table is used to apply the correct amount to each month for invoices that span across multiple months with calendar_days as ( select day from ( select calendar_days_array from ( -- values creates an anonymous table with column calendar_days_array. It has 1 row with an array of timestamps generated by sequence values(sequence(date_add('day', -1460, date_trunc('day', current_date)), date_trunc('day', current_date), interval '1' day)) ) as t1(calendar_days_array) ) cross join unnest(calendar_days_array) as t1(day) -- CROSS JOIN UNNEST will expand the calendar_days_array into individual rows with column days ), -- invoice_update is a temporary table that returns the net captured amount for each invoice (this may change over time) invoice_update as ( select invoices.id, invoices.customer_id, invoices.period_start, invoices.period_end, greatest(1, date_diff('day', invoices.period_start, invoices.period_end)) as duration, -- duration will be used to calculate a per-day cost invoices.currency, invoices.amount_due/100.0 as amount_due, (charges.amount - charges.amount_refunded)/100.0 as captured_amount from invoices join charges on invoices.id = charges.invoice_id -- join subscriptions -- this filter may be included to remove subscriptions that end during the invoice month -- on invoices.subscription_id = subscriptions.id -- and (subscriptions.ended_at is null or date_trunc('month', subscriptions.ended_at) > date_trunc('month', invoices.period_end)) where invoices.subscription_id is not null and charges.captured ), -- monthly_summary is a temporary table that calculates monthly billed_amount, captured_amount, and customer_count monthly_summary as ( select date_trunc('month', calendar_days.day) as month, invoice_update.currency, sum(invoice_update.amount_due/invoice_update.duration) as billed_amount, sum(invoice_update.captured_amount/invoice_update.duration) as captured_amount, count(distinct invoice_update.customer_id) as customer_count from invoice_update join calendar_days on calendar_days.day between invoice_update.period_start and invoice_update.period_end group by 1, 2 ) -- Calculate ARPU by dividing aggregated captured_amount by customer_count and format output select date_format(month, '%Y-%m') as month, currency, cast(billed_amount as decimal(32, 2)) as billed_amount, cast(captured_amount as decimal(32, 2)) as captured_amount, customer_count, cast(captured_amount/customer_count as decimal(32, 2)) as ARPU from monthly_summary order by 1 desc, 2

  

    ~
  

  

        
      

-- This template returns itemized subscription information for each customer and plan select subscriptions.customer_id as customer_id, customers.email as customers_email, subscriptions.plan_id as plan_id, plans.name as plan_name, subscriptions.quantity as quantity, case when subscriptions.canceled_at is null and subscriptions.ended_at is null then 'active' else 'inactive' end as state, date_format(subscriptions.created, '%Y-%m-%d') as created_date, date_format(subscriptions.start, '%Y-%m-%d') as start_date, date_format(least(subscriptions.canceled_at, subscriptions.ended_at), '%Y-%m-%d') as end_date from subscriptions join plans -- join subscriptions on plans to get plan name on subscriptions.plan_id = plans.id left join customers on subscriptions.customer_id = customers.id order by 2

  

    ~
  

  

        
      

-- This template returns the number of disputes grouped by reason and dispute status select reason, -- reason given by cardholder for dispute status, -- current status of dispute count(id) as disputes, count_if(evidence_details_submission_count > 0) as disputes_with_evidence_submitted from disputes group by 1, 2 order by 3 desc

  

    ~
  

  

        
      

-- This template returns the average subscription revenue per user (ARPU) by month and currency for subscription -- invoices. ARPU is calculated by dividing monthly recurring revenue (MRR) by the number of active customers -- within a given month -- calendar_days is a temporary table with a row and value for every day for the past 1460 days (2 years) -- the calendar_days temporary table is used to apply the correct amount to each month for invoices spanning multiple months with calendar_days as ( select day from ( select calendar_days_array from ( -- values creates an anonymous table with column calendar_days_array. It has 1 row with an array of timestamps generated by sequence values(sequence(date_add('day', -1460, date_trunc('day', current_date)), date_trunc('day', current_date), interval '1' day)) ) as t1(calendar_days_array) ) cross join unnest(calendar_days_array) as t1(day) -- CROSS JOIN UNNEST will expand the calendar_days_array into individual rows with column days ), -- invoice_update is a temporary table that returns the net captured amount for each invoice (this may change over time) invoice_update as ( select invoices.id, invoices.customer_id, invoices.period_start, invoices.period_end, greatest(1, date_diff('day', invoices.period_start, invoices.period_end)) as duration, -- duration will be used to calculate a per-day cost invoices.currency, (charges.amount - charges.amount_refunded)/100.0 as captured_amount from invoices join charges on invoices.id = charges.invoice_id where invoices.subscription_id is not null and charges.captured ), -- monthly_summary is a temporary table that calculates monthly billed_amount, captured_amount, and customer_count monthly_summary as ( select date_trunc('month', calendar_days.day) as month, invoice_update.currency, sum(invoice_update.captured_amount/invoice_update.duration) as captured_amount, count(distinct invoice_update.customer_id) as customer_count from invoice_update join calendar_days on calendar_days.day between invoice_update.period_start and invoice_update.period_end group by 1, 2 ) -- Calculate ARPU by dividing aggregated captured_amount by customer_count and format output select date_format(month, '%Y-%m') as month, currency, cast(captured_amount as decimal(32, 2)) as captured_amount, customer_count, cast(captured_amount/customer_count as decimal(32, 2)) as ARPU from monthly_summary order by 1 desc, 2

  

    ~
  

  

        
      

-- This template returns itemized subscription information for each customer and plan select subscriptions.customer_id as customer_id, customers.email as customers_email, subscriptions.plan_id as plan_id, plans.name as plan_name, subscriptions.quantity as quantity, case when subscriptions.canceled_at is null and subscriptions.ended_at is null then 'active' else 'inactive' end as state, date_format(subscriptions.created, '%Y-%m-%d') as created_date, date_format(subscriptions.start, '%Y-%m-%d') as start_date, date_format(least(subscriptions.canceled_at, subscriptions.ended_at), '%Y-%m-%d') as end_date from subscriptions join plans -- join subscriptions on plans to get plan name on subscriptions.plan_id = plans.id left join customers on subscriptions.customer_id = customers.id order by 2

  

    ~
  

  

        
      

-- This template returns the number of charges and amounts for each card type, by currency select coalesce(card_brand, 'Non-card or Other') as card_brand, currency, count(id) as charge_count, sum(amount)/100.0 as total_amount from charges where captured group by 1, 2 order by 4 desc

  

    ~
  

  

        
      

-- This template returns itemized information for charges and associated customer metadata (from charges_metadata) -- charges_metadata_dictionary is a temporary table that creates a row for every charge_id with a dictionary of associated metadata with charges_metadata_dictionary as ( select charge_id, map_agg(key, value) metadata_dictionary -- MAP_AGG creates a key:value dictionary from charges_metadata group by 1) select charges.created, charges.id, charges.customer_id, charges.amount/100.0 as amount, metadata_dictionary['customer_source'] as customer_source -- 'customer_source' is the key we are accessing and returning the associated value (provided via metadata) from charges left join charges_metadata_dictionary on charges.id = charges_metadata_dictionary.charge_id where date_trunc('year', charges.created) = date_trunc('year', current_date) and charges.captured order by charges.created desc limit 1000

  

    ~