Weekly DDQ and RFP Due-Date Digest: One Monday Email of Requests That Need You

What This Builds

A consultant's questionnaire arrives, gets a due date in someone's inbox, and sits behind the quarter's other work. This build keeps one line per request in a small sheet and sends you one email every Monday listing the requests due inside your alert window, the requests already past their due date and not submitted, and the lines with no date. The outcome it serves is no request reaching its due date without you having seen it coming. Each list item also carries whether your CCO review has been sent, so the email shows which requests are waiting on review.

Prerequisites

• A Google spreadsheet named "DDQ and RFP Deadlines", separate from any other tracker
• A Zapier account on a paid plan, because the Zap has three steps and the free plan allows two-step Zaps only. The plan is Professional ($29.99/month). That is the total ongoing cost, since the Google account is one you already have.
• A Google account connected to Zapier for Sheets and Gmail
• A check of your firm's policy on request and consultant codes in an outside automation tool, with your CCO

The Concept

An air traffic board shows each flight, its slot and whether it is cleared. Nobody on the floor memorizes it. This sheet is that board for your requests, and the Monday email reads the board out. The sheet works out what is due. You answer the questionnaires.

One technical point shapes the design. A Zapier search step that finds no row halts the Zap. Zap History shows the run as safely halted, and no email is sent. A quiet week would then look exactly like a broken Zap. So the Zap searches a Summary tab with one row that always exists. Its Key cell says summary, and formulas fill the rest of the row from the Requests tab. A quiet week produces an email that says none and zero. There is no AI step, no Formatter step, no loop, no second lookup and no row trigger, and nothing in this build reads from or writes to your portfolio, order, risk, performance or CRM system.

Every due date is typed from the request itself, and the alert window is a number you choose. This guide states no deadline and no rule about how long an answer may take, and the digest is a backstop that never replaces your firm's own calendar or CRM.

Data caution. The sheet holds request codes (such as RQ-26-014), consultant codes (such as CN-03), dates and yes or no answers only. Consultant names, investor names, the questions, the answers, scores and fee terms stay in your firm's systems, and the key from a consultant code to a name lives in your CRM and nowhere in this sheet. Material non-public information never goes into any AI tool, and this build has no AI step, but the same discipline applies to Zapier and the sheet. Undisclosed holdings, trade ideas and orders, your models, investor and client names, account numbers, fee terms, side letters and consultant scores stay in your firm's systems. Zapier is an outside tool, so ask your CCO whether your firm's policy allows these codes to pass through it and how business use of outside tools is recorded. Check Zapier's own pages for how long Zap History is kept and for its data settings. If the answer is no, use the sheet as a manual Monday checklist and skip the Zap.

Build It Step by Step

Part 1: Build the Requests tab

Name the first tab Requests. Lay out the columns first.

Format column C as a date (Format, then Number, then Date, or the closest match). Type dates as yyyy-mm-dd. Add the dropdown to D2 through E301 (Insert, then Dropdown, or Data, then Data validation, with the list yes and no). Menu names change, so look for the closest match.

F2, filled down to row 301:

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

Read the formula from the outside in, then run each case through it:

• A blank row (no request code) returns blank, so rows filled down past your last request stay empty.
• A submitted request (Submitted is yes) returns blank, whatever its date. It has left your plate.
• A request that is not submitted and has no due date returns MISSING DATE.
• A request not submitted with a due date before today returns PAST DUE.
• A request not submitted, due today or later and no more than Alert days away, returns DUE SOON. A request exactly Alert days away counts.
• Everything else returns blank.

The five closing parentheses at the end match the five IF functions still open.

Part 2: Build the Summary tab

Add a second tab named Summary. Type these cells first.

B2:

=COUNTIF(Requests!F2:F301,"DUE SOON")

C2:

=IFERROR(TEXTJOIN(" | ",TRUE,FILTER(Requests!A2:A301&" for "&Requests!B2:B301&" (due "&TEXT(Requests!C2:C301,"yyyy-mm-dd")&") CCO review sent "&Requests!D2:D301,Requests!F2:F301="DUE SOON")),"none")

D2:

=COUNTIF(Requests!F2:F301,"PAST DUE")

E2:

=IFERROR(TEXTJOIN(" | ",TRUE,FILTER(Requests!A2:A301&" for "&Requests!B2:B301&" (due "&TEXT(Requests!C2:C301,"yyyy-mm-dd")&")",Requests!F2:F301="PAST DUE")),"none")

F2:

=COUNTIF(Requests!F2:F301,"MISSING DATE")

Check the parentheses in each formula before you move on. Every range inside the FILTER runs from row 2 to row 301, so the heights match. The dates sit inside TEXT() so they join as 2026-11-05 and not as a serial number. A row only reaches the list when it carries the flag, and that flag needs a date, so TEXT() never sees a blank date for a listed row. IFERROR turns the empty-result error into the word none. The counts look for the exact flag words, so rows the formula fills down past your last entry are never counted.

The value in B4 is a starting choice, and it is yours to change. It is your own working window and not a rule.

Keep Calculation Mode on Automatic (File, Settings, Calculation). Google's calculation settings page offers Automatic and Manual only, with no scheduled recalculation, and TODAY() depends on recalculation. If a count looks stale, open the sheet before relying on the email. The spreadsheet's time zone (File, Settings, General) also decides what TODAY() means.

Part 3: Load the test rows

The rows below are invented. They assume a pretend TODAY() of Monday 2026-10-26 and B4 = 10. On another day, keep the same offsets from your real today and retype the dates.

The Summary row should then read:

In the C2 cell the two items are separated by a vertical bar, shown here with a backslash only so the table renders. RQ-26-015 shows the boundary: ten days is within a ten-day window. RQ-26-016 sits at eleven days and waits. RQ-26-011 is past due and not submitted. RQ-26-012 is submitted, so it drops off even though its date is close. RQ-26-017 has no due date and is counted as missing.

If C2 shows one item only, or the same date for every item, the first argument of the FILTER is not being read as an array in your sheet. Wrap the text-joining part of the FILTER (the part that joins codes and dates, before the final condition) in ARRAYFORMULA and compare again. The values above are the check.

Part 4: Create the Zap

1. In Zapier, create a new Zap.
2. Choose Schedule by Zapier as the trigger and the Every Week event.
3. Choose Monday for Day of the Week and set the Time of Day for the email. Leave Timezone Override alone unless your spreadsheet uses a different time zone.
4. Add an action. Choose Google Sheets and the Lookup Spreadsheet Row event.
5. Connect your Google account if asked. Pick the spreadsheet DDQ and RFP Deadlines and the worksheet Summary.
6. For Lookup column choose Key (column A). Labels may be worded a little differently.
7. For Lookup value type summary, in lowercase.
8. Leave Supporting lookup column, Supporting lookup value, Search from last row and Row count at their defaults.
9. Add a second action. Choose Gmail and the Send Email event.
10. In To, type your own address only. The digest goes to you and no one else.
11. Type the subject Requests due this week.
12. Type the body below. Replace each bracketed name with the field of that name from the Google Sheets step, using Zapier's field picker.

Due soon [Due Soon Count]: [Due Soon Items]
Past due [Past Due Count]: [Past Due Items]
Missing due dates [Missing Date Count]
Backstop only. Your own request calendar is the record.

1. Test the Google Sheets step. It should return the whole Summary row, with a value for each header in row 1.
2. Test the Gmail step and read the email.
3. Turn the Zap on.

The lookup returns the entire matching row, and that row is all Zapier sees. The Requests tab never leaves Google. Row 4 holds your window, so it is not part of the row the lookup returns.

What leaves your sheet

Zapier receives the Summary row and the email text. That means the word summary, the Due Soon Count, the Due Soon Items text (request codes, consultant codes, due dates and the CCO review answer of yes or no), the Past Due Count, the Past Due Items text (request codes, consultant codes and due dates) and the Missing Date Count. Zap History stores the data each step received and returned, so those values stay in your Zapier account. The rest of the Requests tab stays in Google.

Zapier bills only successful action steps as tasks. The trigger, a search that halts and a failed action are not counted. A normal run has two action steps, the lookup and the email, so check Zapier's own pages for how your plan counts them.

Use codes only. A consultant name, an investor name, a question, an answer or a score does not belong in this sheet.

Real Example: Monday 2026-10-26

Setup: The six invented rows from Part 3 and B4 set to 10. Input: On Monday at the scheduled time, the Zap fires and finds the summary row. Output:

Due soon 2: RQ-26-014 for CN-03 (due 2026-10-30) CCO review sent no | RQ-26-015 for CN-01 (due 2026-11-05) CCO review sent yes
Past due 1: RQ-26-011 for CN-03 (due 2026-10-23)
Missing due dates 1
Backstop only. Your own request calendar is the record.

Check it by hand. 2026-10-30 is four days after 2026-10-26, and 2026-11-05 is ten days after it. 2026-11-06 is eleven days out, so RQ-26-016 waits. 2026-10-23 is three days before today and RQ-26-011 is not submitted. RQ-26-012 is submitted. RQ-26-017 has no due date. What you do next: You send RQ-26-014 to your CCO first, since the email shows its review has not been sent. You ask where RQ-26-011 stands, and you find the due date for RQ-26-017 in the request itself and type it in. The rows are invented. Real dates come from each request.

What to Do When It Breaks

• A Monday passed with no email → This failure is silent, so build the habit of noticing it. Check that the Zap is switched on. Open Zap History and look for a run that Monday. A "safely halted" run means the lookup found no row, so check that A2 on the Summary tab still says summary, because a renamed Key cell stops the lookup. Check that the spreadsheet and the Summary tab still have their original names. If Zapier reports an expired Google connection, reconnect it. A Monday with no email never means that nothing needs attention.
• A request never appears → Confirm column C holds a real date and not text, and that Submitted is not yes.
• A submitted request still shows → Submitted must read yes, spelled exactly that way. The dropdown prevents typos.
• A due date moved → Update column C. The sheet does not learn of a changed date.
• An item shows nothing after CCO review sent → The CCO review sent cell was left empty. Fill it with yes or no.
• Dates show as numbers in the email → The TEXT wrapper was removed from the formula in C2. Restore it from Part 2.
• Counts look stale → Open the sheet and keep Calculation Mode on Automatic.

Variations

• Simpler version: Drop the CCO review column and the shorter item text, and flag on the due date and Submitted only.
• Extended version: Raise the aim from a due date to a finished answer. Add a column for whether the draft answers come from your approved library (yes or no), and add a count and an items cell to the Summary in the same pattern. Every new formula follows the same rules as above.

What to Do Next

• This week: Run the test rows. Then replace them with your live entries and compare the first email with your firm's request calendar.
• This month: Adjust Alert days to the lead time your CCO review actually needs.
• Advanced: Pair it with the thesis review digest and the investor follow-up digest, each in its own spreadsheet.

Advanced guide for investment fund manager professionals. This build needs a paid Zapier plan. Zapier and Google update their screens often, so if a label differs from this guide, look for the closest match and check the vendor's help pages.