Weld
Start free
Recurly model

Billing by month

What was invoiced, credited, collected and still owed, by month. The cash-side companion to the MRR models, and the closest match to Recurly's invoice export totalled by month.

See the SQLAll Recurly models
Grain
month x currency
Built from
  1. 1
    Raw tables
  2. 1
    Staging
  3. 1
    Core
  4. 1
    Analytics
Reads from Recurly
invoice
Ships as
Weld and dbt SQL, open source on GitHub
core.recurly.billing_by_month
1-- core.recurly.billing_by_month
2-- What was invoiced, credited, collected and still owed, by month. The cash-side
3-- companion to the MRR models, and the closest match to Recurly's invoice export
4-- totalled by month.
5--
6-- Grain: month x currency. Currency: the invoice currency, major units.
7-- Dated on the invoice's created_at. Voided invoices are excluded.
8-- Depends on: staging.recurly.invoice
9--
10-- CHARGE AND CREDIT INVOICES are kept apart. Charge invoices (and pre-2021
11-- `legacy` invoices) bill the customer; credit invoices give money back and
12-- carry negative amounts, so net_billings is a plain sum:
13--   charge_subtotal - charge_discounts = charge_net         (ex tax)
14--   charge_net + credit_net = net_billings                  (credit_net is negative)
15-- `charge_total` adds tax back on, so it is what customers were asked to pay.
16--
17-- `failed` invoices (collection gave up) stay in, because they were billed;
18-- `failed_total` shows how much of the month's billing that was.
19
20SELECT
21    DATE_TRUNC(invoice_date, MONTH)                                     AS month,
22    currency,
23    COUNTIF(invoice_type IN ('charge', 'legacy'))                       AS charge_invoices,
24    SUM(IF(invoice_type IN ('charge', 'legacy'), subtotal, 0))          AS charge_subtotal,
25    SUM(IF(invoice_type IN ('charge', 'legacy'), discount, 0))          AS charge_discounts,
26    SUM(IF(invoice_type IN ('charge', 'legacy'), subtotal - discount, 0)) AS charge_net,
27    SUM(IF(invoice_type IN ('charge', 'legacy'), tax, 0))               AS charge_tax,
28    SUM(IF(invoice_type IN ('charge', 'legacy'), total, 0))             AS charge_total,
29    COUNTIF(invoice_type = 'credit')                                    AS credit_invoices,
30    SUM(IF(invoice_type = 'credit', subtotal - discount, 0))            AS credit_net,
31    SUM(IF(invoice_type = 'credit', total, 0))                          AS credit_total,
32    SUM(subtotal - discount)                                            AS net_billings,
33    SUM(IF(invoice_type IN ('charge', 'legacy'), paid, 0))              AS charge_paid,
34    SUM(IF(invoice_type IN ('charge', 'legacy'), balance, 0))           AS charge_balance_due,
35    SUM(IF(state = 'failed', total, 0))                                 AS failed_total
36FROM {{staging.recurly.invoice}}
37WHERE state <> 'voided'
38GROUP BY 1, 2

This model and the 2 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().

Notes

How it counts. And where it stops.

CHARGE AND CREDIT INVOICES are kept apart. Charge invoices (and pre-2021 `legacy` invoices) bill the customer; credit invoices give money back and carry negative amounts, so net_billings is a plain sum: charge_subtotal - charge_discounts = charge_net (ex tax) charge_net + credit_net = net_billings (credit_net is negative) `charge_total` adds tax back on, so it is what customers were asked to pay. `failed` invoices (collection gave up) stay in, because they were billed; `failed_total` shows how much of the month's billing that was.

Raw tables it reads

  • raw.recurly.invoice

More Recurly models

Put Recurly in front
of your agents.

Connect Recurly, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.

Start for freeTalk to us