Email Stripe delinquent customers to your finance team
A weekly list of customers whose latest invoice is unpaid past its due date, with what they owe. Stripe goes into your data warehouse first, where customers are read next to their invoices and subscriptions, and only then reaches email.
%20scale(1.25)%22%20fill%3D%22%23fff%22%20d%3D%22M13.976%209.15c-2.172-.806-3.356-1.426-3.356-2.409%200-.831.683-1.305%201.901-1.305%202.227%200%204.515.858%206.09%201.631l.89-5.494C18.252.975%2015.697%200%2012.165%200%209.667%200%207.589.654%206.104%201.872%204.56%203.147%203.757%204.992%203.757%207.218c0%204.039%202.467%205.76%206.476%207.219%202.585.92%203.445%201.574%203.445%202.583%200%20.98-.84%201.545-2.354%201.545-1.875%200-4.965-.921-6.99-2.109l-.9%205.555C5.175%2022.99%208.385%2024%2011.714%2024c2.641%200%204.843-.624%206.328-1.813%201.664-1.305%202.525-3.236%202.525-5.732%200-4.128-2.524-5.851-6.594-7.305h.003z%22%2F%3E%0A%3C%2Fsvg%3E%0A)
StripeConnectedBalance transact…ChargesCustomersDisputes
EmailIdAccou…Livem…Daily 09:00Stripe Endpoints
14 Stripe endpoints you can import. Each lands in its own table in your data warehouse.
Balance transactions
Every movement of money through the Stripe balance: charges, refunds, disputes, fees, payouts, adjustments.
16 fieldsTime seriesCharges
One row per charge attempt: amount, outcome, card details, who paid and which invoice or payment intent it belongs to.
33 fieldsTime seriesCustomers
Every customer object: identity, billing country, delinquency and balance.
17 fieldsDisputes
One row per dispute (chargeback) with its status and evidence deadline.
14 fieldsTime seriesEvents
The audit trail: every change Stripe recorded (invoice.paid, customer.subscription.updated, charge.refunded, …) with the id of the object it concerns.
11 fieldsTime seriesInvoice line items
Every line on every invoice: what was billed, for which price and product, for which period.
19 fieldsInvoices
One row per invoice with amounts, status and period.
31 fieldsTime seriesPayment intents
One row per payment attempt lifecycle: how much was asked for, how much was received, and where it stands.
22 fieldsTime seriesPayouts
One row per transfer from the Stripe balance to the bank account: amount, arrival date, status.
18 fieldsTime seriesPrices
Every price (plan) of every product, active or not, with amount, currency and billing interval.
17 fieldsProducts
The catalogue: every product, active or not.
13 fieldsRefunds
One row per refund: how much was returned on which charge, why, and the balance transaction that reversed the money.
13 fieldsTime seriesSubscription items
The lines of every subscription: which price, how many units.
14 fieldsSubscriptions
One row per subscription in any status (status=all), full refresh every run so cancellations and past-due states are current.
23 fields
Why Stripe does not email the list on its own
Stripe flags a customer as delinquent, but a list that finance can act on needs the invoice that is overdue and the subscription that will renew. They are joined in the warehouse, and the email is built from there.
- A delinquent flag does not say which invoice is overdue
- Finance acts on a list with an amount, not a flag
- The data analyst controls what the message says
StripeCustomers, balanceEmail
Data warehouseJoined by idStep 1: Load Stripe into your data warehouse first
Create a Data Mart with Stripe as the source and choose where the data lands. The Stripe connector is a custom connector built in OWOX Data Marts, and the data goes into any popular data warehouse.
- Customers is one of 14 endpoints, each in its own table
- Authorize with a restricted Stripe API key that has read access
- Open the Stripe connector for your data warehouse below
StripeData MartGoogle BigQuery
AWS Athena
Snowflake
AWS Redshift
Databricksstripe.customersStep 2: Link Stripe customers to invoices and subscriptions
In your data warehouse the model joins Invoices and Subscriptions on customer, so a delinquent customer is listed with the overdue invoice and the subscription that continues.
- Customers, invoices and subscriptions join on customer
- delinquent comes from Stripe; the amount comes from the invoices
- Account owners from the CRM can join by email
Step 3: Email Stripe reports to your recipients
Add an email destination with the recipients' addresses, separated by commas, semicolons or new lines, and attach a report of the Stripe Data Mart. The subject and the body are Markdown, and a table placeholder drops the rows into the body.
- The Customers table carries name, email, delinquent, balance
- Allow-list the sending address insights@e.owox.com so the message reaches inboxes
- Run it once with Create & Run report to see the email before the schedule starts
EmailWeeklyWeekly delinquent customersnameemaildelinquentbalanceStep 4: Schedule the Stripe email and add a short analysis
Add a trigger in the report's Automate Report Runs section or on the Data Mart's Triggers tab, for example every weekday morning or Monday and Wednesday at a set hour in your time zone. Write the reading under the table.
- The reading is drawn from the report's fields and says only what the data shows
- Send always, only when the result is not empty, or only when it is empty
- Attach a pre-built Insight instead of plain text when you want it written for you
EmailWeekly delinquent customersnameemaildelinquentbalanceAnalysisdelinquent marks customers whose latest invoice is overduebalance shows the account credit or debit Stripe holdsaddress_country shows whether the problem is localStep 5: Reuse Stripe delinquent lists
The Data Mart behind your email report also works in OWOX, where your assistant for data can read it. Ask about any report, then get it in Google Sheets or Excel in one click.
- Ask which delinquent customers also cancel soon
- A question worth repeating becomes the next scheduled email
Google SheetsOpen
ExcelOpen
- 0.41.0Dates from Google Sheets arrive as dates, not text
- 0.41.0Rotating a connector key is one change: Sources share reusable Credentials
- 0.40.0Connecting a plan from Google Sheets takes fewer guesses
- 0.39.0Reload a year of history in one backfill, not twelve
- 0.39.0Long Facebook Ads imports no longer fail on a corrupted response
Why OWOX for Stripe in email
A Stripe email says a payment failed. OWOX sends the finance team the list that matters, with the invoice and the amount, from your own data warehouse.
- A list with amounts. Customers join invoices, so each name comes with what is owed.
- You own the data. Stripe lands in your own data warehouse, in tables you control.
- Only when needed. The email goes out only on weeks the list has someone on it.
- One place to fix. When Stripe's API changes, you publish one new connector version, so the schedule keeps running.
You are booked. The invitation is on its way to , with the calendar invite and the meeting link.