Weekly Co-op Claim Deadline Digest Emailed to You Before Claims Expire

What This Builds

A co-op claim fails on its deadline, and the deadline is usually discovered when the documents are still missing. This build emails you once a week with every claim that has passed its deadline, has no deadline recorded, is inside your warning window with documents still being gathered, or is inside the window and ready to submit.

The email goes to you alone. The automation never submits a claim and never contacts a retailer. The sheet also works as your claim log.

Prerequisites

• A Google account with Sheets and Gmail. If your company uses Microsoft 365 for mail, ask whether a Google account may hold even coded claim data.
• 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 current plans at zapier.com. The Professional plan is listed at $29.99/month. That is the total ongoing cost, since Sheets and Gmail ride on your Google account.
• Your company's AI and software policy, checked for whether Zapier and a Google account may hold coded claim data
• Each retailer's current co-op program document, which is the only source for a deadline

The Concept

Imagine a paper tickler file with one card per claim, and a sticky note on the front of the drawer listing the cards that need attention this week. You keep the cards. Zapier reads the sticky note out to you each week.

The sticky note is a Summary row that always exists. If Zapier searched the claims directly and found nothing, the Zap would halt ("Safely halted" in Zap History) and send no email, so a calm week and a broken Zap would look the same. Reading one fixed row means the email arrives every week, even if it only says no claims are inside the window.

What Zapier Sees and Keeps

Zap History stores each step's data. Here that is claim codes, retailer codes, deadlines, Docs Status values, counts and the digest text. Keep these out of the sheet:

• Claim amounts, funds balances or accrual figures
• A retailer's real name where its program terms are confidential. Use Retailer A, Retailer B.
• Unreleased product or promotion names. Use a code such as COOP-A-Q2.
• Any consumer's personal data

Check your policy to confirm Zapier and a Google account may hold even this coded data.

Build It Step by Step

Part 1: The Claims tab

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

Add the dropdowns with Data > Data validation and set column C to accept only dates. Format F2:F200 as Format > Number > Number. Re-check a deadline against the program document whenever the retailer reissues it, because programs change.

Part 2: The Summary tab and your setting

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

Cell B2 is your setting. A claim whose deadline is that many days away or fewer enters the digest.

Part 3: The formulas

Fill each Claims formula down to row 200. Extend every range below if you pass 200 rows.

Days Left, Claims!F2:

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

Stage, Claims!G2:

=IF(A2="","",IF(E2="Yes","Closed",IF(C2="","Missing deadline",IF(F2<0,"DEADLINE PASSED, check with retailer",IF(F2<=Summary!$B$2,IF(D2="Complete","READY TO SUBMIT","Docs not complete"),"Later")))))

Digest Line, Claims!H2:

=IF(A2="","",A2&" | "&B2&" | "&IF(C2="","no deadline","deadline "&TEXT(C2,"yyyy-mm-dd")&IF(F2<0,", "&ABS(F2)&" days ago",", in "&F2&" days"))&" | docs: "&D2&" | "&G2)

Action Count, Summary!C2:

=COUNTIF(Claims!G2:G200,"DEADLINE PASSED, check with retailer")+COUNTIF(Claims!G2:G200,"Missing deadline")+COUNTIF(Claims!G2:G200,"Docs not complete")+COUNTIF(Claims!G2:G200,"READY TO SUBMIT")

Digest Lines, Summary!D2:

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Claims!H2:H200,(Claims!G2:G200="DEADLINE PASSED, check with retailer")+(Claims!G2:G200="Missing deadline")+(Claims!G2:G200="Docs not complete")+(Claims!G2:G200="READY TO SUBMIT"))),"No claims inside the warning window")

The label "DEADLINE PASSED, check with retailer" contains a comma. That is fine inside COUNTIF and FILTER, because the comma sits within quotation marks. Type the label identically in the Stage formula and in both Summary formulas, since a mismatch means the claim silently drops out. All ranges are rows 2 to 200, the same height.

Part 4: Walk the Stage formula through every case

Tests run from the top and the first true one wins.

• Blank row. Claim is empty, so Stage is blank, nothing is counted, nothing is filtered in.
• Submitted is Yes. "Closed", whatever the deadline. It is tested first so a submitted claim with a deadline in the past never shows DEADLINE PASSED.
• Deadline blank. "Missing deadline". Days Left is blank here, so nothing compares a blank to a number.
• Days Left below 0. "DEADLINE PASSED, check with retailer". The digest never decides whether anything can still be done. Ask the retailer.
• Days Left from 0 to Warn Days, Docs Status is Complete. "READY TO SUBMIT". A claim inside the window with its documents complete still appears, because it is waiting on you to submit it.
• Days Left from 0 to Warn Days, Docs Status is anything else. "Docs not complete".
• Days Left above Warn Days. "Later", kept out of the digest, even when the documents are complete.

Part 5: Build the Zap

1. In Zapier, create a Zap and choose the trigger Schedule by Zapier, event Every Week. Pick a weekday and hour.
2. Add an action: Google Sheets, event Lookup Spreadsheet Row. Choose this spreadsheet and the Summary worksheet. Set the lookup column to Key and the lookup value to the typed word summary. Test it and confirm you see Warn Days, Action Count and Digest Lines.
3. Add an action: Gmail, event Send Email. Send it to your own address only. Build the subject with the field picker: Co-op claims digest: [Action Count] need attention. Put the Digest Lines field in the body. If line breaks collapse in the test email, look for a plain-text body option.
4. Add nothing else. No AI step, no retailer address. The Zap submits nothing.

Only successful action steps count as Zapier tasks. A normal weekly run uses two, the lookup and the email. The trigger is not billed and neither is a halted search. Confirm the current rules on Zapier's pricing page.

Part 6: Test and refine

Open the sheet so it recalculates. Then run the test and compare the email with the Claims tab. Check File > Settings > Calculation is set to recalculate on change and every hour, because TODAY() only moves when the sheet recalculates. Treat the digest as a pointer. Before you submit or give up on any claim, read the retailer's current program document. For anything about money, ask finance.

Real Example: Monday 2026-10-12, Warn Days 21

The date used for TODAY() is invented, as are all codes.

Checking by hand from 2026-10-12: October 30 is 18 days ahead. November 2 is 19 days to October 31 plus 2, which is 21 and sits exactly on the Warn Days boundary. November 3 is 22, one day outside, so row 8 stays Later even though its documents are complete. October 5 is 7 days back. December 15 is 19 plus 30 for November plus 15, which is 64. September 20 is 22 days back, and row 7 reads Closed because Submitted is tested first.

Action Count: passed 1 (row 4), Missing deadline 1 (row 5), Docs not complete 1 (row 3), READY TO SUBMIT 1 (row 2), so 4. The subject reads Co-op claims digest: 4 need attention, and the body reads:

COOP-A-Q2 | Retailer A | deadline 2026-10-30, in 18 days | docs: Complete | READY TO SUBMIT
COOP-A-Q3 | Retailer A | deadline 2026-11-02, in 21 days | docs: Collecting | Docs not complete
COOP-B-Q2 | Retailer B | deadline 2026-10-05, 7 days ago | docs: Collecting | DEADLINE PASSED, check with retailer
COOP-B-Q3 | Retailer B | no deadline | docs: Not started | Missing deadline

Time saved: Instead of rebuilding the list of claims at risk from retailer emails and tabs, you read four lines on Monday and spend the time on the documents.

What to Do When It Breaks

• No email arrived and nothing looks wrong → This is the silent failure. Open Zap History and look for the scheduled run. No run means the Zap is off or the schedule changed. 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. Add a calendar reminder to confirm the email arrived each week for the first month.
• A claim you expect is missing from the digest → Check Submitted is No, the Deadline is a real date, and Warn Days is large enough. If the Stage label looks right but the claim is absent, compare the label's spelling with the Summary formulas.
• Stage shows an error → The Deadline was typed as text. Re-enter it as a date.
• Digest Lines is empty while Action Count is above zero → One FILTER range is a different height. Make them all rows 2 to 200.
• The digest stays the same after you edit a date → The sheet has not recalculated. Open it and rerun the test.

Variations

• Simpler version: Send only the Action Count in the subject and a link to the sheet in the body.
• Extended version: Add a Google Form for logging new claims. Answers land on a responses tab that you copy into Claims by hand after checking them.

What to Do Next

• This week: Enter every open claim with its deadline from the current program document and send a test.
• This month: Tune Warn Days to the time it takes you to collect documents.
• Advanced: Use the Level 1 checklist prompt on each new claim. Then copy the finished checklist status into Docs Status.

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