Zapier Automation: A Weekly Proposal and Proof-Routing Digest

What This Builds

A proposal that has gone quiet, or a proof sitting unapproved, rarely gets chased on a fixed schedule. It gets chased whenever you happen to remember, which is often three days late. This build watches a tracker sheet you already keep and emails you, and only you, one weekly digest listing every open item that has gone quiet long enough to need a nudge, with a suggested follow-up line for each one. You still decide what to send and when.

You end up with a tracker sheet with two new formula columns, a five-step Zap, and a weekly email that replaces the mental list of who still owes you a response.

Prerequisites

• A Professional Zapier plan or higher, since this Zap has more than two steps. Free plans support two-step Zaps only.
• A Google account with Sheets and Gmail, personal or your employer's Google Workspace account
• A tracker sheet already in use, or ten minutes to build one from the template in Part 1
• 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

A Zap is a small relay: something happens (a trigger), and one or more steps react to it (actions). This one does not watch for something new. It wakes up on a schedule, once a week, and asks your tracker sheet one question: which rows have been sitting quiet long enough to need a nudge? A formula column you add to the sheet answers "yes" or "no" for every row, and the Zap only pulls the "yes" rows into the digest. Nothing in this build emails a client. Every message lands in your own inbox for you to read and act on.

Client names and proposal status pass through every step of this Zap, and Zapier keeps a copy of that step data in Zap History, not just in whatever the AI step reads. Keep exact budgets and your station's discount floors off the tracker sheet entirely, since those never need to be there for this build to work. Check with your sales manager before connecting a work Google account to Zapier, the same way you would before connecting any tool that touches account data.

Build It Step by Step

Part 1: Build or Update the Tracker Sheet

Your tracker needs one row per open proposal or proof, with these columns:

Add the Needs Nudge? column with this formula in row 2. Fill it down every row.

=IF(ISBLANK(C2), "check date", IF(AND(D2="Sent", TODAY()-C2>=3), "yes", "no"))

This assumes Client is column A, What Was Sent is B, Date Sent is C, Status is D, Air/Publish Date is E, and Needs Nudge? is F. Adjust the column letters to match your sheet.

Then add the Digest Line column (G) and fill it down as well. It builds the one line of text per row that the Zap will carry into the email:

=IF(F2="yes", A2&" | "&B2&" | sent "&TEXT(C2,"m/d/yyyy")&" | quiet for "&(TODAY()-C2)&" days"&IF(ISBLANK(E2),""," | airs or publishes "&TEXT(E2,"m/d/yyyy")), "")

The sheet builds this line because Zapier's Formatter step joins one column of values, not several columns side by side. Rows that do not need a nudge get an empty Digest Line.

Walk through what the formula does on three kinds of rows before you trust it:

• A row sent 5 days ago with Status "Sent": ISBLANK(C2) is false, TODAY()-C2 is 5, which is at least 3, so the formula returns "yes." This row belongs in the digest.
• A row sent yesterday with Status "Sent": TODAY()-C2 is 1, which is less than 3, so the formula returns "no." Too soon to nudge.
• A row with a blank Date Sent (someone forgot to log it): ISBLANK(C2) is true, so the formula returns "check date" instead of quietly treating the blank as an enormous number of days overdue. A blank date in Google Sheets is stored as day zero, which would otherwise make the row look decades overdue. Scan the sheet for "check date" every so often, since those rows will not surface in the automated digest on their own.

Change Status to anything other than "Sent" (for example "Responded" or "Closed") once a client replies, and the formula returns "no" on its own. A row keeps reappearing in the weekly digest for as long as its Status stays "Sent," which is the point: nothing drops off the list just because a week went by.

Part 2: Build the Zap

1. Trigger: Schedule by Zapier, "Every Week." Pick the day and time you want the digest, for example Monday at 8 a.m.

2. Action: Google Sheets, "Lookup Spreadsheet Rows (Advanced)." Connect your Google account, choose the tracker spreadsheet and worksheet, and set the search column to Needs Nudge? with the value yes.

   Read this carefully before you build anything else: if this step finds zero matching rows, the entire Zap stops right there. Zapier calls this a Halted run. Open Zap History for that run and you will see the Halted status on the Lookup step, and no steps after it will show as having run at all. No email goes out that week, and no task gets used, because nothing after a Halted step executes. This is normal behavior when nothing needs a nudge, not a bug, but do not mistake a quiet week for a broken Zap. See "What to Do When It Breaks" below for how to add an always-send option if you want a weekly email even on quiet weeks.

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 row 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 step runs on Zapier's own built-in model, billed through your Zapier plan. You do not need a separate ChatGPT or Claude account for it. If you would rather use your own Claude or ChatGPT account's API key through this step's Bring Your Own Key option, that needs an API key from that vendor's developer console, not your regular Claude.ai or ChatGPT Plus login.

   Prompt to use in this step:

   Copy and paste this

   Here is a list of advertising proposals or proofs that have gone quiet:
   
   [insert the Formatter step's output field here]
   
   For each one, suggest one short, specific follow-up line the sales rep could send, referencing what was sent and how long it has been quiet. Do not invent any client details beyond what is listed. Keep each line under 25 words.
   

5. Action: Gmail, "Send Email." Send to your own email address only. Subject: "This week's proposal and proof follow-ups." Body: the output from the AI by Zapier step. 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 with Test step, confirm the email arrives in your own inbox, and turn the Zap on.

Real Example: A Monday Morning Digest

Setup: A rep's tracker has eleven open rows. Six show Status "Sent" with a Date Sent more than 3 days old, three were sent within the last two days, one shows Status "Responded," and one has a blank Date Sent from a rushed Friday afternoon.

Input: Monday at 8 a.m., the Schedule trigger fires. The Lookup step finds the six rows marked "yes" in the Needs Nudge column.

Output: The rep's inbox gets one email listing six suggested follow-up lines, one per open proposal, each referencing what was sent and how long it has been quiet. The rep edits two lines, deletes one because that client already called in the meantime and the sheet has not been updated yet, and sends the other five from their own email client. The blank-date row and the responded row never appear, since the formula correctly excluded both.

Time saved: What used to be a mental scan through eleven rows and a guess at who to chase becomes a five-minute read and edit pass.

What to Do When It Breaks

• The digest does not arrive some weeks (expected, not broken) → Check Zap History. If the Lookup step shows Halted, zero rows matched "yes" that week and nothing after it ran. This is correct behavior, not an error, since a quiet week with nothing to chase should not send an empty email.
• You want a digest every week even when nothing needs a nudge → 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(Tracker!F2:F,"yes") under Count, and =IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Tracker!G2:G,Tracker!F2:F="yes")),"Nothing to follow up on this week") under Lines (replace Tracker 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. On a quiet week the email arrives and says there is nothing to follow up on.
• The digest goes quiet for weeks and you do not notice (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 notification can land in a folder you do not check daily. Open Zap History every few weeks regardless of whether digests have been arriving, and confirm the Zap still shows as On.
• The suggested follow-up line is generic or wrong → The AI step only knows what the Formatter step handed it. Add more tracker columns (last contact method, a one-line note) and add them to the Digest Line formula so the AI has more to work with.
• A row you already handled still shows up → The Needs Nudge formula only reads the Status column. Update Status to something other than "Sent" the moment a client responds, and the row drops out of the next digest on its own.

Variations

• Simpler version: Skip the AI by Zapier step. Have the Formatter step's joined list go straight into the Gmail body as a plain checklist, and write your own follow-up lines.
• Extended version: Make proofs more urgent than proposals. Change the Needs Nudge formula so a row whose Air/Publish Date is within the next seven days returns "yes" after one quiet day instead of three, and ask the AI step to list rows with an air or publish date first.

What to Do Next

• This week: Add the Needs Nudge column to your real tracker sheet and build the Zap using your own columns.
• This month: Watch two or three weekly digests, and adjust the 3-day threshold in the formula if it is catching rows too early or too late for how your clients actually respond.
• Advanced: Connect this to the past-due account digest build so both emails arrive on the same morning, giving you one weekly admin pass instead of two separate check-ins.

Advanced guide for advertising sales agents who want proposal and proof follow-ups to surface on their own instead of living in memory. Zapier's free plan does not support the multi-step version of this build.