Weekly Promotion Milestones Digest: A Monday Email From Your Own Sheet

What This Builds

Every Monday morning an email lands in your own inbox listing the promotion milestones that are overdue, have no date, or fall inside the next stretch of days you chose. A proof date, a legal review date and a printer file date no longer depend on you remembering to open the tracker. Nobody else receives it, and no AI step reads your plan.

The sheet does all the thinking with ordinary formulas. Zapier only reads one finished row and mails it to you.

Prerequisites

• A Google account with Sheets and Gmail. If your company runs on Outlook and Microsoft 365, ask whether a Google account may hold even coded milestone data before you start.
• A Zapier account on a plan that allows a three-step Zap. Zapier's free plan has been limited to two-step Zaps, so check the current plans at zapier.com. The Professional plan is listed at $29.99/month. That is the whole ongoing cost, because Gmail and Sheets are part of your Google account.
• Your company's AI and software policy, checked for two questions: may Zapier process this data, and may the Google account hold it.
• A list of your milestones with a promotion code on each

The Concept

Think of a whiteboard with one line at the bottom that a colleague copies onto a sticky note every Monday and hands you. The whiteboard is your Milestones tab. The bottom line is the Summary tab, a single row that always exists. The colleague is Zapier, who looks only at that one line and never edits the board.

Why one fixed row and not a search for overdue rows? When a Zapier search step finds nothing, the Zap stops there and logs "Safely halted" in Zap History. No later step runs, so no email goes out, and a quiet week would look exactly like a broken Zap. A Summary row with Key "summary" always matches, so the email arrives every week, even when it says nothing is due.

What Zapier Sees and Keeps

Zapier stores each step's data in Zap History. Here that means promotion codes, milestone labels, owner initials, dates, the counts and the digest text. Keep these out of the sheet entirely:

• Unreleased product names. Use a code such as SPRING-A.
• Real names of retailers or agencies where your agreements are confidential.
• Amounts, budgets and offer depths.
• Anything about a consumer, such as names, emails or entries.

Use short labels. Then check the policy question above, because even coded plan data is commercially sensitive.

Build It Step by Step

Part 1: The Milestones tab

Create a new spreadsheet for this digest only. Rename the first tab Milestones and put these headers in row 1:

Add dropdowns with Data > Data validation. Column B accepts: Brief to agency, Proof due, Legal review due, Printer file date, Ship to stores, In-store date, Media start, Offer ends. Column E accepts: No, Yes. Set column C to accept only dates, so a typed word cannot break the Days Left formula.

Select F2:F200 and set Format > Number > Number, so the result shows as a plain count and not as a date.

Part 2: The Summary tab and your setting

Add a second tab named Summary. Row 1 holds headers and row 2 holds the single data row:

Cell B2 is your setting. Change it and the next digest follows.

Part 3: The formulas

Fill each formula down to row 200 in its column. Rows 2 to 200 leave room for a year of milestones. If you outgrow that, extend the ranges in every formula below.

Days Left, Milestones!F2:

=IF(OR(B2="",C2=""),"",C2-TODAY())

It is blank when the milestone or its date is blank, so empty rows stay empty.

Stage, Milestones!G2:

=IF(B2="","",IF(E2="Yes","Closed",IF(C2="","Missing date",IF(F2<0,"OVERDUE",IF(F2<=Summary!$B$2,"DUE SOON","Later")))))

Digest Line, Milestones!H2:

=IF(B2="","",A2&" | "&B2&" | "&IF(C2="","no date","due "&TEXT(C2,"yyyy-mm-dd")&", "&IF(F2<0,ABS(F2)&" days overdue","in "&F2&" days"))&" | "&D2&" | "&G2)

The TEXT function turns the date into 2026-10-09 style text. Without it the joined line would show a serial number such as 46304.

Overdue Count, Summary!D2:

=COUNTIF(Milestones!G2:G200,"OVERDUE")

Action Count, Summary!C2:

=COUNTIF(Milestones!G2:G200,"OVERDUE")+COUNTIF(Milestones!G2:G200,"Missing date")+COUNTIF(Milestones!G2:G200,"DUE SOON")

Digest Lines, Summary!E2:

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Milestones!H2:H200,(Milestones!G2:G200="OVERDUE")+(Milestones!G2:G200="Missing date")+(Milestones!G2:G200="DUE SOON"))),"Nothing due in the look-ahead window")

Both ranges in the FILTER run from row 2 to row 200, so they are the same height. When no row matches, FILTER returns an error and IFERROR substitutes the sentence, so the email still has a body.

Part 4: Walk the Stage formula through every case

The formula tests in a fixed order, and the first test that is true wins.

• Blank row. Milestone is empty, so Stage is blank. COUNTIF never matches a blank, and FILTER never picks it up. A formula filled down past your last row is harmless.
• Done is Yes. Stage is "Closed", whatever the date says. Closed is tested before the date checks on purpose. A finished milestone with a date in the past would otherwise read OVERDUE forever, and a finished milestone with no date would read "Missing date" and nag you.
• Due Date blank, Done is No. Stage is "Missing date". The date is missing, so Days Left is blank, and the formula never compares a blank to a number.
• Days Left below 0. Stage is "OVERDUE".
• Days Left from 0 up to your Look Ahead Days. Stage is "DUE SOON". The boundary is inclusive: with 14, a milestone 14 days out is DUE SOON.
• Anything further out. Stage is "Later", and it stays out of the digest.

Part 5: Build the Zap

1. In Zapier, create a Zap and choose the trigger Schedule by Zapier, event Every Week. Pick Monday and a morning hour.
2. Add an action: Google Sheets, event Lookup Spreadsheet Row. Connect your Google account, choose this spreadsheet and the Summary worksheet, set the lookup column to Key and the lookup value to the typed word summary. Run the test. It should return the Look Ahead Days, Action Count, Overdue Count and Digest Lines fields from row 2.
3. Add an action: Gmail, event Send Email. Set the recipient to your own address only. Build the subject by mixing text with the lookup fields: Promotions digest: [Action Count] actions, [Overdue Count] overdue, inserting the two counts from step 2 with Zapier's field picker. Put the Digest Lines field in the body. If the line breaks collapse in your test email, check whether the step offers a plain-text body option.
4. Add no AI step, no filter and no other recipients. The Zap never contacts agencies, printers, legal or finance.

Zapier counts only action steps that succeed as tasks. The schedule trigger is not an action, and a search that halts is not billed. A normal weekly run uses two tasks, the lookup and the email. Confirm the current rules on Zapier's pricing page.

Part 6: Test and refine

Open the sheet once before testing, so the formulas have recalculated for today. Then run the Zap test from the Gmail step and read the email against your Milestones tab. Check File > Settings > Calculation and confirm the sheet recalculates on change and every hour, because TODAY() only moves when the sheet recalculates.

Real Example: SPRING-A and SUMMER-B on Monday 2026-10-12

An invented date, used for TODAY(), with Look Ahead Days set to 14. All codes and dates are invented.

Checking the day counts by hand: from 2026-10-12, the 9th is 3 days back and the 5th is 7 days back. The 20th is 8 days ahead and the 26th is 14 days ahead, which sits exactly on the boundary. The 27th is 15 days ahead, one past it. The 16th of November is 19 days to October 31 plus 16 more, 35 days.

The counts: OVERDUE 1 (row 3), Missing date 1 (row 4), DUE SOON 2 (rows 5 and 7). Action Count is 1 + 1 + 2 = 4, and Overdue Count is 1. Closed rows 2 and 9, the two Later rows and the blank row stay out.

The email subject reads Promotions digest: 4 actions, 1 overdue, and the body reads:

SPRING-A | Proof due | due 2026-10-09, 3 days overdue | MR | OVERDUE
SPRING-A | Legal review due | no date | LG | Missing date
SPRING-A | Printer file date | due 2026-10-20, in 8 days | JT | DUE SOON
SUMMER-B | Media start | due 2026-10-26, in 14 days | AK | DUE SOON

Row 9 shows why Closed is tested first. It has no date, so without that order it would appear as "Missing date" in every digest.

Time saved: The weekly scan across the calendar, the agency thread and the printer email shrinks to reading four lines. Keeping the tab current is still your job, and that takes a few minutes whenever a date moves.

What to Do When It Breaks

• No email arrived on Monday and nothing looks wrong → This is the silent failure. Open Zap History in Zapier and look for Monday's run. No run at all means the Zap is turned off or the schedule was edited. A "Safely halted" lookup means the Key cell no longer reads exactly summary, or the Summary tab was renamed. An error on the Sheets step usually means the Google connection expired, so reconnect it. Put a recurring Monday reminder on your calendar to confirm the email arrived, at least for the first month.
• The email says "Nothing due" but you know a proof is close → Check that the milestone's Done cell is No, that Due Date is a real date (right-aligned in the cell), and that Look Ahead Days is large enough. Then open the sheet so it recalculates and rerun the test.
• Days Left or Stage shows an error → A Due Date was typed as text. Re-enter it as a date. The date-only rule on column C should stop this.
• The counts are right but Digest Lines is empty or odd → The FILTER ranges must be the same height. If you extended one range to row 300, extend all of them.
• Dates in the digest look like long numbers → The TEXT wrapper was lost when the formula was edited. Restore it.

Variations

• Simpler version: Skip the Digest Lines and mail only the two counts. Open the sheet when either number is above zero.
• Extended version: Add a second Zap on Every Day for a short list of overdue items only, reading a second column on the same Summary row. Keep it one row and one lookup.

What to Do Next

• This week: Enter every open milestone for the next promotion and run the test email.
• This month: Adjust Look Ahead Days after a few digests. A longer window suits a printer with long lead times.
• Advanced: Add a Google Form that writes date changes to its own responses tab, and copy confirmed changes into Milestones by hand.

Advanced guide for Advertising & Promotions Manager professionals. These techniques use more sophisticated features that may require paid subscriptions.