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

Tanin Na Nakorn
6 min readApr 2, 2024

--

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

--

--

Tanin Na Nakorn
Tanin Na Nakorn

Written by Tanin Na Nakorn

I'm the creator of Superintendent.app, an local data analytics notebook for exploring and transforming CSV using SQL.

No responses yet