Daily NetSuite collections digest in Slack with email drafts

Every weekday at 8am, run an accounts receivable collections review in NetSuite and report it to our collections channel in Slack.

Start by running a SuiteQL query in NetSuite that returns every open invoice whose due date has already passed. For each invoice pull the invoice number, the customer, the invoice date, the due date, the original amount, and the remaining balance due, then calculate how many days late it is relative to today. SuiteQL returns a maximum of 1000 rows per page, so page through the results using limit and offset until you have the complete ledger. NetSuite money fields are plain decimal amounts in the record currency, so there is no cents or minor unit conversion to do. Always count the unpaid remaining balance, not the original invoice total.

Bucket every overdue invoice by how late it is: 1 to 30 days, 31 to 60 days, 61 to 90 days, and more than 90 days. Then roll the invoices up per customer, so each customer has a total overdue balance, a breakdown across those four buckets, the oldest open invoice with its number and days late, and a count of open overdue invoices. Rank the customers by total overdue balance.

Skip any customer already flagged as in dispute or on a payment plan. Check the customer record for a dispute or payment plan indicator, which is usually a custom field or a status or category on the record, and drop those customers entirely from both the ranking and the email drafting step. If the account does not carry such a field, treat every customer as eligible and say so in the Slack message so the team knows that filter did not apply.

For the worst offenders, meaning the top 10 by overdue balance plus any customer that qualifies for a dunning draft, get the customer record in NetSuite to read the account owner, which is the sales rep on the record, and the billing contact's email address. Do not fetch the full record for every customer in the ledger, only the ones being reported on or written to.

Post one message to our collections channel in Slack. Lead with the headline number: total AR at risk across all overdue invoices, and the change versus last week. Record this week's total so the next run can calculate that change, and on the very first run say that the run is establishing the baseline. Under the headline show the totals in each aging bucket, then a ranked list of the top 10 accounts by overdue balance. For each account give the customer name, the total overdue, the oldest invoice number, how many days late that invoice is, which aging bucket it falls in, and the account owner so it is clear who should chase it. Keep it to a single message and prioritize rather than dumping the whole ledger, so the accounts that need personal attention stand out instead of being buried in a list of everything overdue.

For any account with more than $10,000 overdue, or with any invoice more than 60 days late, create a draft in Gmail of a dunning email addressed to that customer's billing contact. The tone should be polite but firm: a short reminder, the specific invoice numbers with their amounts and due dates, the total now outstanding, and a clear request for either payment or a reply with a payment date. Always name the actual invoices rather than sending a generic note that the account is overdue.

Never send collections email automatically. Always leave it as a Gmail draft for a human to review, edit, and send, and do not use any send action, only draft creation. In the Slack message, note which accounts had a draft prepared so a person knows to open Gmail and review them. If no accounts cross the thresholds, still post the Slack summary and say that no drafts were needed.