Zapier Automation: A Weekly Past-Due Account Digest

What This Builds

Collections reminders get put off because they feel like they undercut the relationship, and tracking which account is on which stage by memory falls apart past a handful of names. This build reads a past-due sheet built from your business office's aging report and emails you a weekly digest with draft reminder wording for each account, grouped by how overdue it is. Disputed accounts get listed separately with no drafted wording at all, since those belong to the business office, not you. You still send every reminder yourself.

You end up with a past-due sheet with four formula columns, a five-step Zap, and a weekly email you can work from in one sitting instead of chasing accounts one at a time from memory.

Prerequisites

• A Professional Zapier plan or higher, since this Zap has more than two steps
• A Google account with Sheets and Gmail, personal or your employer's Google Workspace account
• Access to the business office's aging report to build the past-due sheet from, and their sign-off that you may keep a working copy in your own Google account
• Total ongoing cost: $29.99/month on the Professional plan. Google Sheets and Gmail run on the Google account you already have, so they add nothing on top of that.
• About 1 to 2 hours to build and test

The Concept

This works the same way as a Zap that watches a tracker sheet on a schedule instead of waiting for something new to happen. Once a week, it asks your past-due sheet which accounts are actually overdue right now, using formula columns instead of asking Zapier or the AI to compare dates. The sheet works out which stage each account has reached, an AI step drafts a short reminder line to match the stage, and disputed rows are listed separately with no drafted wording at all. Everything lands in your own inbox. Nothing goes to a client automatically, and this build never states a late fee, a legal threshold, or a collections deadline as fact, since those vary by station, publisher, and state, and only your business office or legal staff can confirm them for a specific account.

Client names, invoice dates, and dispute status pass through every step of this Zap, and Zapier keeps that step data in Zap History, not only whatever the AI step reads when it drafts a line. Past-due account information is exactly the kind of detail a sales manager and the business office should know is sitting in a personal Google account and a Zapier automation before you build this, so get that confirmation first rather than after the fact.

Build It Step by Step

Part 1: Build the Past-Due Sheet

Build this from the aging report your business office already sends, with one row per past-due account:

You type the first five columns (Yes or No for Disputed and Paid). Add four formula columns, filled down every row. Assume Client is A, Invoice Date is B, Due Date is C, Disputed is D, and Paid is E.

Days Past Due (column F):

=IF(ISBLANK(C2), "", TODAY()-C2)

Overdue? (column G):

=IF(ISBLANK(C2), "check date", IF(AND(TODAY()>C2, E2<>"Yes"), "yes", "no"))

Stage (column H):

=IF(G2<>"yes", "", IF(D2="Yes", "disputed", IF(F2<=30, "stage 1", IF(F2<=60, "stage 2", "stage 3"))))

Digest Line (column I):

=IF(G2="yes", A2&" | "&F2&" days past due | "&H2, "")

The sheet builds the Digest Line because Zapier's Formatter step joins one column of values, not several columns side by side. The 30 and 60 day bands are a starting point. Change them in the Stage formula to match how your business office escalates.

Walk through three cases before trusting the formulas:

• A row due 20 days ago, not paid: TODAY()-C2 returns 20 in Days Past Due, Overdue? returns "yes" since today is later than the due date, and Stage returns "stage 1" (or "disputed" if the Disputed column says Yes). This row belongs in the digest.
• A row due 5 days from now: Days Past Due would show a negative number, which is a useful signal on its own that the account is not due yet, and Overdue? correctly returns "no." Excluded from the digest.
• A row with a blank Due Date (a gap in the aging report): the first two formulas check ISBLANK(C2) first, so Days Past Due stays blank, Overdue? returns "check date", and Stage and Digest Line stay empty, instead of the huge false number you would get from subtracting today's date from an empty cell, which Sheets treats as day zero. A blank due date must never silently compute as thousands of days overdue. Scan for "check date" rows by hand, since the automated digest will not surface them.

A row's Overdue? stays "yes" for as long as today is later than its due date and Paid is not Yes, no matter how many weeks pass. That is deliberate. An account does not fall out of the digest just because it has been overdue for a while. It leaves only when you mark it Paid or remove the row. A disputed account stays in the digest too, in its own section, until the business office resolves it.

Part 2: Build the Zap

1. Trigger: Schedule by Zapier, "Every Week." Pick a day and time, for example Monday at 9 a.m.

2. Action: Google Sheets, "Lookup Spreadsheet Rows (Advanced)." Search the Overdue? column for the value yes. This pulls in every overdue, unpaid row regardless of Disputed status. The Stage column already marks which rows are disputed, so no second lookup is needed.

   As with any lookup step, zero matching rows means the run shows as Halted in Zap History, and nothing after the Lookup step runs, including the email. No digest goes out on a week with nothing overdue. That is correct, not broken. See "What to Do When It Breaks" for an always-send option if you want a weekly email regardless.

3. Action: Formatter by Zapier, "Utilities" action, "Line-item to Text" transform. Feed it one field from the Lookup step's output: Digest Line. The step turns the Digest Line values of every matched row into a single block of text. If the step offers a separator setting, pick one that keeps each account apart from the next. The default comma-separated text also works, because each Digest Line uses the | character inside it instead of commas.

4. Action: AI by Zapier, "Analyze and Return Data." This runs on Zapier's own built-in model through your Zapier plan, not a separate ChatGPT or Claude account. Use the Bring Your Own Key option inside this step only if you want to run it on your own Anthropic or OpenAI API key instead, which needs a key from that vendor's developer console, not a consumer chatbot login.

   Prompt to use:

   Copy and paste this

   Here is a list of past-due advertising accounts. Each entry gives the account, the days past due, and a stage label:
   
   [insert the Formatter step's output field here]
   
   For every account labelled stage 1, stage 2 or stage 3, write one short, professional reminder line. Stage 1 should sound like a routine nudge, stage 2 should sound more direct, and stage 3 should ask for a specific response date. Use the stage label exactly as given and do not work out a stage yourself. Do not use any legal language, do not threaten any action, and do not mention a late fee or any dollar amount.
   
   List every account labelled disputed in a separate section titled "Disputed, refer to business office" with no reminder wording drafted for them at all.
   
   Do not invent any detail not listed above.
   

5. Action: Gmail, "Send Email." Send to your own email address only. Subject: "This week's past-due account digest." Body: the AI by Zapier step's output. A week with matches uses a handful of tasks: Zapier counts successful action steps (the lookup, the AI step, which can count as more than one task, and the email), and it does not count the schedule trigger or the Formatter step.

6. Test each step, confirm the email lands correctly split between drafted reminders and the disputed list, and turn the Zap on.

Real Example: A Tuesday Digest

Setup: A rep's past-due sheet has nine unpaid rows: five are overdue and not disputed, two are overdue and marked disputed, one is not yet due, and one has a blank Due Date left over from a data entry gap in last month's aging report.

Input: The Schedule trigger fires Tuesday morning. The Lookup step finds the seven rows where Overdue? equals "yes," which includes both the five undisputed and the two disputed accounts.

Output: The digest email arrives with five drafted reminder lines, tone matched to how overdue each one is, followed by a "Disputed, refer to business office" section naming the two disputed accounts with no drafted wording. The rep personally sends three of the five reminders as drafted, edits one to reference a call already scheduled, and forwards the disputed section to the business office as a check-in. The blank-date row does not appear and gets flagged separately when the rep scans the sheet later that week.

Time saved: Sorting nine accounts into who to remind, how firmly, and who to leave alone used to mean rereading the whole aging report. Now it takes one read of a pre-sorted email.

What to Do When It Breaks

• No digest arrives some weeks (expected, not broken) → Check Zap History. A Halted status on the Lookup step means zero accounts matched "yes" that week, and nothing after it ran, including the email. That is correct when there is nothing overdue.
• You want a digest every week regardless → A second lookup placed after the first one will not help, because the first one still halts the run on a quiet week. Change what the Zap looks up instead. Add a Summary tab with a header row (Key, Count, Lines) and one data row: the word summary under Key, =COUNTIF(PastDue!G2:G,"yes") under Count, and =IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(PastDue!I2:I,PastDue!G2:G="yes")),"No accounts overdue this week") under Lines (replace PastDue with your tab's name). Point the Lookup step at the Summary tab and search the Key column for summary, which always matches. Remove the Formatter step and give the AI step the Lines field.
• The digest silently stops arriving over several weeks (the failure you won't see) → Zapier emails the account owner when a run errors and may pause a Zap that keeps failing, but that notice can go unread. Check Zap History periodically regardless of whether digests have been showing up, and confirm the Zap is still On.
• A disputed account gets a drafted reminder anyway → Check that the Disputed column in the sheet says Yes for that row. The Stage formula only labels a row disputed on an exact Yes, so a blank or mistyped value is treated as not disputed.
• An account stays in the digest after it is paid → Type Yes in the Paid column for that row, or remove the row. The Overdue? formula keeps a row in the digest until one of those happens.
• The reminder tone does not match your stage bands → Adjust the day thresholds in the Stage formula to match how your business office escalates collections. The 30 and 60 day bands here are a starting point, not a fixed rule.

Variations

• Simpler version: Drop the AI by Zapier step and send the Formatter step's joined list straight to Gmail as a plain checklist, writing your own reminder wording.
• Extended version: Add a Last Reminder Sent date column and a helper column that returns "yes" when more than 14 days have passed since that date. Add the helper's value to the Digest Line so the digest shows which accounts have gone quiet on your end.

What to Do Next

• This week: Build the past-due sheet from your business office's aging report and confirm with them that a working copy in your own Google account is acceptable.
• This month: Run the digest for a few weeks and adjust the day thresholds in the Stage formula to match how your station or publisher actually escalates a past-due account.
• Advanced: Pair this with the proposal and proof-routing digest so both weekly emails arrive the same morning as one combined admin routine.

Advanced guide for advertising sales agents who want past-due follow-up to run on a consistent schedule instead of by memory. Zapier's free plan does not support the multi-step version of this build.