Guide: Build the Account Receivable (A/R) Aging report in Stripe using SQL

This guide shows how to build the Account Receivable (A/R) Aging report based on the invoice export functionality (free) and Stripe Sigma (paid).

There is one caveat in using the invoice export functionality (free). While we can export the list of invoices with due dates and amounts, there’s no functionality to export credit notes, which are the integral part of invoicing. This means that we won’t be able to accurately build an AR aging report for the past month. However, building the AR aging report as of today is still feasible. We’ll see later why it is limited to as of today.

Stripe Sigma (paid), on the other hand, has the credit notes table. This means that, with Stripe Sigma, we can build accurate AR aging reports even for past months.

Using the invoice export

In this section, we will utilize the desktop app: Superintendent.app, which is an offline SQL notebook. You can open up CSV files and start writing SQL. No database installation is needed. Disclaimer: I’m the creator.

First of all, you will need to get the list of invoices by visiting https://dashboard.stripe.com/invoices and click on “Export”:

Then, you will select a timezone (UTC works!) and “All” because a really old invoice might not be paid yet:

Then, you will select the below columns:

• ID — the ID of the invoice.
• Amount due — the amount that is unpaid.
• Due date — because AR age starts from the due date.
• Paid At (UTC) — the paid date. If the invoice is not paid, it will be null.
• Marked Uncollectible At (UTC) — the mark uncollectible date. Marking uncollectible reduces AR to 0.
• Voided At (UTC) — the void date. Voiding reduces AR to 0.
• Finalized At (UTC) — the invoice date. When the due date doesn’t exist, we use the finalized date instead. There are certain cases in Stripe where an invoice doesn’t have a due date. An invoice that charges automatically is one such case.

After downloading the CSV file, we will load it into Superintendent.app. Here’s how it looks like:

Let’s recap what AR is a bit. When an invoice is issued (i.e. finalized), AR would increase by its due amount. When the invoice is either paid, voided, or marked uncollectible, its due amount would go to 0, which means AR would also go to 0.

With the above logic in mind, we can turn the list of invoices into a list of events that either increase or decrease AR with the following SQL:

select
  id,
  Amount_Due as ar,
  Finalized_At__UTC as event_date
from
  invoices
union all
select
  id,
  - Amount_Due as ar,
  least(
    Paid_At__UTC,
    Marked_Uncollectible_At__UTC,
    Voided_At__UTC
  ) as event_date
from
  invoices
where
  Paid_At__UTC is not null
  or Marked_Uncollectible_At__UTC is not null
  or Voided_At__UTC is not null

Then, we can group events by invoice IDs and sum up the AR amounts. Then, for each invoice, we get its due date (through a join) and compute its AR age.

with ar_per_invoice as (
  select
    id,
    sum(ar) as ar
  from
    events
  group by
    id
  having
    sum(ar) != 0
)

select
  a.id,
  a.ar,
  greatest(
    datediff(
      'day',
      coalesce(i.Due_Date__UTC, i.Finalized_At__UTC),
      current_date -- We use the current date to compute the current age
    ),
    0
  ) as age_in_days
from
  ar_per_invoice a
  left join invoices i on a.id = i.id

After we have AR amount and age for each invoice, we can now group them into the bucket of “not due yet”, “30 days”, “60 days”, “90 days”, and “older than 90 days”

select 
  *, 
  case when age_in_days = 0 then 'not_due_yet'
  when age_in_days <= 30 then '30_days'
  when age_in_days <= 60 then '60_days'
  when age_in_days <= 90 then '90_days'
  else 'more_than_90_days'
  end as bucket
from ar_with_age

Then, we can group each row by bucket and produce a nice chart:

select
  bucket,
  sum(ar)
from
  ar_with_age_and_bucket
group by
  bucket

And there it is, the AR aging report based on the invoice export!

As I mentioned earlier, this approach has the flaw where we don’t have the list of credit notes. Without the credit note data, we cannot trace the history of due amount reduction. This means we can only produce the AR aging report as of today using the invoice export approach. However, Stripe Sigma (paid) has the credit note data, and therefore we should be able to product the AR aging report as of any date!

Using Stripe Sigma

Stripe Sigma fortunately offers the credit note data. Credit notes only reduce AR since Stripe doesn’t support debit notes.

Extending the SQL above is quite straightfoward. We convert a credit note into an event that reduces AR. Then, we follow the same logic of grouping AR by invoice, calculating AR, and calculating age.

Here’s the SQL to generate the events from credit notes:

select 
  invoice_id,
  -amount as ar,
  created as event_date
from 
  credit_notes
union all
select 
  invoice_id,
  amount as ar,
  voided as event_date
from credit notes
where voided is not null

-- Below are the events from invoices
union all
select
  id,
  Amount_Due as ar,
  Finalized_At__UTC as event_date
from
  invoices
union all
select
  id,
  - Amount_Due as ar,
  least(
    Paid_At__UTC,
    Marked_Uncollectible_At__UTC,
    Voided_At__UTC
  ) as event_date
from
  invoices
where
  Paid_At__UTC is not null
  or Marked_Uncollectible_At__UTC is not null
  or Voided_At__UTC is not null

In order to offer the AR aging report as of a certain date, we can simply filter for the events that are older that the wanted date as shown below:

with ar_per_invoice as (
  select
    id,
    sum(ar) as ar
  from
    events
  where event_date < timestamp '2023-12-01' -- Specify the date that you want
  group by
    id
  having
    sum(ar) != 0
)

select
  a.id,
  a.ar,
  greatest(
    datediff(
      'day',
      coalesce(i.Due_Date__UTC, i.Finalized_At__UTC),
      timestamp '2023-12-01' -- Specify the date that you want
    ),
    0
  ) as age_in_days
from
  ar_per_invoice a
  left join invoices i on a.id = i.id

Here is the full SQL:

with

events as (
  select 
    invoice_id,
    -- the credit note amount reduces the due amount. Therefore, a positive credit note amount reduces AR.
    -amount as ar, 
    created as event_date
  from 
    credit_notes
  union all
  select 
    invoice_id,
    amount as ar,
    voided as event_date
  from credit notes
  where voided is not null
  union all
  select
    id,
    Amount_Due as ar,
    Finalized_At__UTC as event_date
  from
    invoices
  union all
  select
    id,
    - Amount_Due as ar,
    least(
      Paid_At__UTC,
      Marked_Uncollectible_At__UTC,
      Voided_At__UTC
    ) as event_date
  from
    invoices
  where
    Paid_At__UTC is not null
    or Marked_Uncollectible_At__UTC is not null
    or Voided_At__UTC is not null
),

ar_per_invoice as (
  select
    id,
    sum(ar) as ar
  from
    events
  where event_date < timestamp '2023-12-01' -- Specify the date that you want
  group by
    id
  having
    sum(ar) != 0
),

ar_with_age as (
  select
    a.id,
    a.ar,
    greatest(
      datediff(
        'day',
        coalesce(i.Due_Date__UTC, i.Finalized_At__UTC),
        timestamp '2023-12-01' -- Specify the date that you want
      ),
      0
    ) as age_in_days
  from
    ar_per_invoice a
    left join invoices i on a.id = i.id
),

ar_with_bucket as (
  select 
    *, 
    case when age_in_days = 0 then 'not_due_yet'
    when age_in_days <= 30 then '30_days'
    when age_in_days <= 60 then '60_days'
    when age_in_days <= 90 then '90_days'
    else 'more_than_90_days'
    end as bucket
  from ar_with_age
)

select
  bucket,
  sum(ar)
from
  ar_with_age_and_bucket
group by
  bucket