Re-Service Due Digest: A Weekly Zapier Email That Lists Recurring Customers Coming Due

What This Builds

Recurring customers drift. A lawn plan, a monthly clean or a seasonal tune-up comes due, the schedule is full, and the visit slips a week and then three. This build sends you one email each week listing the customer codes whose next visit falls within your look-ahead window, including any that are already overdue.

The sheet adds up the dates. Zapier carries two cells to your inbox, so the email arrives every week, even when nothing is due. There is no AI step. The email goes to you alone. You book the visits in your brand's system yourself, and the Zap never contacts a customer.

Prerequisites

• A Google account that holds the sheet and a Gmail address you send from. If your franchisor controls the account or limits outside apps, ask your franchise business consultant before you connect Zapier.
• A Professional plan or better ($29.99/month). The Zap has a trigger plus two actions, which makes three steps, and Zapier's free plan allows two-step Zaps only. Check Zapier's plan page before you build.
• Total ongoing cost of the finished build: the Zapier plan above. Google Sheets and Gmail add nothing on a Google account you already have. Each weekly run uses two tasks (the lookup and the email), so about 8 to 10 tasks a month. Zapier counts only successful action steps as tasks.
• Comfort writing a spreadsheet formula and copying it down a column.
• Your brand's policy on customer information read, and an answer on whether customer codes may pass through an outside tool (see "What Zapier Sees and Keeps" below).
• A visit interval in days for each customer, chosen by you. This guide states no service schedule. Your own plan with each customer sets it.

The Concept

Think of a tickler file on the office wall with one card on top that says how many customers are coming due and who they are. The sheet rewrites that top card as dates pass. Zapier reads the card each week and mails you a copy.

The card is always there, and that matters because of how Zapier's search step behaves. A lookup that finds zero rows halts the Zap ("Safely halted" in Zap History). No later step runs and no email goes out. If the Zap searched the Customers tab for "due", a week with none due would send nothing, and you could not tell a calm week from a broken Zap. By searching for one Summary row whose Key is always summary, the lookup always finds its row. The email arrives every week and says "none" when nothing is due.

The Zap has no AI step, no formatting step and no loop. The sheet decides what is due and Zapier only moves text.

What Zapier Sees and Keeps

Zapier handles whatever the Zap reads, and Zap History stores the data of each step. The lookup step stores the whole Summary row, and the Gmail step stores the subject and body. In this build that means customer codes, due dates, a count and your look-ahead setting.

Keep everything else out of the sheet. A customer is a code such as "C-12" and nothing more. Never enter a name, phone number, address, gate code, access note, payment detail or anything about a customer's home or health. Those details stay in your brand's required system. In senior care, tutoring and similar brands, even a service label can say too much, so keep the Service column to a plain word such as "Lawn" or "Clean". Ask your franchise business consultant whether codes may pass through an outside tool at all. If the answer is no, stop after Part 3 and read the Summary tab each week.

Build It Step by Step

Part 1: Lay Out the Two Tabs

Create a new spreadsheet named Re-Service Due. Name one tab exactly Customers and another exactly Summary. Tab names are case-sensitive in formulas, so match them. This digest has its own spreadsheet because its columns differ from your other digests.

On Customers, row 1 holds the headers and each customer gets one row from row 2 down.

Add the dropdown with Data, then Data validation, then Add rule. Pick Dropdown and enter yes and no for F2:F501. Format C as a date and D as a plain number.

On Summary, row 1 holds the headers and row 2 holds the one data row.

Below those, type Look ahead (days) in A4 and a number in B4, for example 7. Keep a number in B4 at all times.

Part 2: Paste the Formulas

Next due, cell E2 on Customers. Paste it and copy it down to row 501. That leaves room for 500 customers.

=IF(OR(A2="",C2="",D2=""),"",C2+D2)

The result is blank when the code, the last service date or the interval is missing. Otherwise it is the last service date plus the interval.

Flag, cell G2 on Customers. Paste it and copy it down to row 501.

=IF(A2="","",IF(F2="yes","",IF(OR(C2="",D2=""),"MISSING DATE",IF(E2-TODAY()<=Summary!$B$4,"DUE",""))))

The formula reads left to right and stops at the first test that is true.

• Customer code empty: blank. This is why a formula filled down to row 501 stays silent on empty rows.
• Booked is yes: blank. A customer you have already booked never appears.
• Last service date or interval empty: "MISSING DATE".
• Next due minus today is at most the look-ahead in Summary!$B$4: "DUE". An overdue date gives a negative number, which is below the look-ahead, so overdue customers count as DUE.
• Anything else: blank.

Due Count, Summary!B2

=COUNTIF(Customers!G2:G501,"DUE")

Due Lines, Summary!C2

=IFERROR(ARRAYFORMULA(TEXTJOIN(", ",TRUE,FILTER(Customers!A2:A501&" (due "&TEXT(Customers!E2:E501,"yyyy-mm-dd")&")",Customers!G2:G501="DUE"))),"none")

All three ranges inside FILTER run from row 2 to row 501, so they are the same height. The date is wrapped in TEXT with the format "yyyy-mm-dd", because a date joined into text without it shows as a serial number. ARRAYFORMULA makes the join work down the whole range. Only DUE rows reach the join, so the date part is never blank. When nothing is due, FILTER returns an error and IFERROR puts "none" in the cell.

Missing Date Count, Summary!D2

=COUNTIF(Customers!G2:G501,"MISSING DATE")

The counts read the labels the Flag column writes for filled rows. Empty rows return blank, so they are never counted.

Re-read each formula once after pasting. Count the opening and closing parentheses, confirm every range ends at row 501 and confirm the tab names match.

Part 3: Keep Calculation on Automatic

TODAY() changes every new day. Google's calculation settings page lists only two choices, Automatic and Manual, and no scheduled recalculation. In the sheet, open File, then Settings, then the Calculation tab, and keep the mode on Automatic. If the counts in an email look stale, open the sheet before the run and check the Flag column against today's date.

Part 4: Build the Zap

In Zapier, create a new Zap with three steps.

1. Trigger. Choose the app Schedule by Zapier and the event Every Week. Pick the day of the week you plan your bookings, and a morning time for Time of Day.
2. Action. Choose Google Sheets and the event Lookup Spreadsheet Row. Connect your Google account. Choose the Re-Service Due spreadsheet and the Summary tab. Set the lookup column to Key and the lookup value to summary. Add no other condition. The sheet already did the work, and you want a lookup that always finds its row.
3. Action. Choose Gmail and the event Send Email. In To, enter your own address and nobody else's. Build Subject in this order: the text Re-service due: followed by the Due Count field from step 2. Build Body in this order, using the fields from step 2 where the square brackets are:

Due for re-service: [Due Lines]. Customers missing a date or interval: [Missing Date Count]. Open the Customers tab for details.

Zapier lists the Summary columns as fields named after your row 1 headers. If a header is missing from the field list, run the lookup test again so Zapier reloads the headers.

Test each step. The lookup test should return the summary row with the count and the lines. The email test should reach your inbox. Then turn the Zap on.

Part 5: Keep It Current

After each visit, update Last service date to the visit date and set Booked back to no. Without that reset the customer stays hidden, or stays flagged, for the wrong reason. Intervals are your own choice per service. Check the interval against your own plan with that customer.

Real Example: A Wednesday Morning at BrightNest Cleaning (invented)

Invented setup: today is Wednesday 2026-10-14 and Look ahead (days) is 7. Every code, service and date below is invented, and the brand is invented.

Working through the Flag formula:

• C-12: next due is today, so the difference is 0. 0 is at most 7, so DUE.
• C-15: next due was 2026-10-08, which is 6 days ago, so the difference is -6. That is at most 7, so DUE. This is an overdue customer.
• C-17: next due is 2026-10-21, exactly 7 days away. 7 is at most 7, so DUE. This is the boundary case.
• C-18: next due is 2026-10-23, 9 days away. 9 is above 7, so blank.
• C-20: Booked is yes, so blank, even though the date has passed.
• Row 7: no customer code, so blank, and it is never counted.

Summary: B2 is 3, C2 is "C-12 (due 2026-10-14), C-15 (due 2026-10-08), C-17 (due 2026-10-21)" and D2 is 0.

The email arrives with the subject "Re-service due: 3" and this body:

Due for re-service: C-12 (due 2026-10-14), C-15 (due 2026-10-08), C-17 (due 2026-10-21). Customers missing a date or interval: 0. Open the Customers tab for details.

If nothing were due, the subject would read "Re-service due: 0" and the body would start "Due for re-service: none." The email still arrives.

Setup: one spreadsheet, one Zap, three steps. Input: the weekly schedule and the dates you keep current. Output: one email to you with a count in the subject and the customer codes and due dates in the body. Time saved: a few minutes each week compared with scanning a list, though the digest is only as current as your last entry. Updating the last service date after each visit is the price.

Before you book from the list, match each code to your brand's system. The sheet knows dates, not whether the customer paused service.

What to Do When It Breaks

• No email arrived this week. This is the failure you will not see, because a stopped Zap makes no noise. Check that the Zap is switched on. Then open Zap History for the run. A "Safely halted" lookup means no row had Key equal to summary, usually because the Key cell was renamed, its text changed or the Summary tab moved. Restore the text summary in A2 exactly. A run that never appears usually means the Zap was turned off or the Google connection expired. Reconnect the account under your Zapier connected accounts and re-test the lookup.
• Set a standing reminder. Put a calendar reminder for mid-morning on your Zap day. If no digest has arrived by then, treat that as the alarm and check the Zap that day.
• Counts look wrong or stale. Open the sheet and confirm Calculation is on Automatic. Then check the Next due column against today's date.
• A customer never shows up. Check that the last service date is a real date and not text, that the interval is a number and that Booked is not yes. A customer you forgot to enter cannot be flagged.
• A customer keeps showing up after a visit. The Last service date was not updated, or Booked was not reset to no. Fix the row.
• The Due Lines cell shows an error or "none" when you expect names. A range was edited to a different height. Every range inside the FILTER must run from row 2 to row 501.
• A formula shows #REF! or #NAME?. A tab was renamed. Rename it back, or fix the tab name inside the formulas.

Variations

• Simpler version: skip Zapier and open the Summary tab each week. The count and lines are the same.
• Extended version: add a Google Form for logging a new recurring customer. Form answers land on their own responses tab, and you copy each one into Customers yourself. Never point a Form at the Customers tab, because it would overwrite your formulas. The Zap still runs on its schedule.
• Wider window: raise the number in B4 when your schedule books further ahead.

What to Do Next

• This week: build the tabs and test with dummy rows. Then switch the Zap on and compare the first real email with your brand's system.
• This month: adjust the look-ahead after you see how far ahead you actually book.
• Advanced: pair it with the quote follow-up digest, each in its own spreadsheet with its own Summary tab.

Advanced guide for franchise owner-operators. The digest flags dates you have typed. It does not decide who to book, when or on what terms, and it never contacts a customer. Your operations manual and your own judgment decide that. Zapier and Google may change their screens, so follow the labels you see.