Exam Study Plan Digest: A Zapier Automation That Emails You Every Monday

What This Builds

Every Monday morning an email lands in your own inbox listing the study sections that need you this week: the ones behind plan, the ones that start inside your look-ahead window, and the ones missing a date. The subject line carries two counts, so you know before opening it whether the week starts on track.

The sheet does all the thinking. Formulas flag each section, a Summary tab collects the flagged lines into one cell, and Zapier carries that cell to your inbox. There is no AI step, so nothing about your studying goes to an AI vendor. The email goes to you alone.

Prerequisites

• A Google account that holds the sheet, and a Gmail address you are allowed to send from. If your company runs Google Workspace, ask IT whether outside apps such as Zapier may connect to it.
• A Professional plan or better ($29.99/month). The Zap has a trigger plus two actions, which is three steps, and Zapier's free plan allows two-step Zaps only. Check Zapier's current plan page before you build.
• Total ongoing cost of the finished build: the Zapier plan above. Google Sheets and Gmail cost nothing extra on a Google account you already have. Each weekly run uses two tasks (the lookup and the email), so roughly 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 company's AI and data policy read, and an answer on whether Zapier is an approved tool for even this coded data (see "What Zapier Sees and Keeps" below).
• A personal Google account and a personal Gmail address for this build if your company does not allow personal use of work tools. A study plan is your own business, and keeping it on a personal account keeps it off company systems. If you would rather use a work account, ask IT first.
• The exam body's published syllabus for your sitting and your own calendar. Confirm every detail about the exam on the SOA or CAS site. This guide states no exam date, syllabus content, format or study-hours figure.

The Concept

Think of the Summary tab as a noticeboard with one card pinned to it. The sheet rewrites the card whenever anything changes: the Plan tab. Zapier is the person who walks to the noticeboard every week, copies the card and drops it in your inbox.

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 Plan tab for "sections starting this week", a quiet week would return no rows and no email, and you could not tell a quiet week from a broken Zap. By searching for one Summary row whose Key is always "summary", the lookup always finds a row, so the email arrives every week even when the card says nothing needs you.

The Zap contains no AI step and no formula logic of its own. The sheet decides what is flagged. Zapier only moves one cell's text from the sheet to your inbox.

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 section codes, short labels you write yourself, planned dates and status values.

Keep these out of the sheet entirely: anyone's real name, any text copied from study material, any practice question or answer, and any score. Use codes such as "Sec-07" and a short label of your own, such as "Reading block 3", and never a copied chapter title or passage. Keep your notes and materials where you already keep them. Results, figures and assumption values stay in your workbook and your model and never go in this sheet. Even coded data belongs to your company, so check the company's AI and data policy and whether Zapier is an approved tool before you connect it. If it is not approved, stop after Part 2 and use the sheet alone: the Summary tab works as a dashboard you open on Monday. If you build this on a personal account, the data stays outside company systems, but Zapier and Google still hold it under their own terms.

Build It Step by Step

Part 1: Build the Plan Tab

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

On Plan, row 1 holds the headers and each study section gets one row from row 2 down. Build the sections from the exam body's published syllabus and your own calendar.

Add the Status dropdown with Data, then Data validation, then Add rule, pick Dropdown, and enter the three values on E2:E201. Format C and D as dates and F as a plain number.

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

Look Ahead Days is your own setting. Choose it from how far ahead you like to see your plan.

Part 2: Add the Formulas

Paste each formula into row 2 of its column and copy it down to row 201. That leaves room for 200 sections. Every range below ends at row 201, so extend them all together if you ever need more rows.

Days to Finish, cell F2

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

Blank when there is no section code or no planned finish. Otherwise the planned finish minus today, so a finish in the past gives a negative number.

Flag, cell G2

=IF(A2="","",IF(E2="Done","Done",IF(OR(C2="",D2=""),"Missing date",IF(F2<0,"BEHIND",IF(C2<=TODAY()+Summary!$B$2,"THIS WEEK","Later")))))

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

• Section empty: blank. This is why a formula filled down to row 201 stays silent on empty rows.
• Status Done: "Done".
• Planned Start or Planned Finish empty: "Missing date".
• Days to Finish below 0: "BEHIND".
• Planned Start on or before today plus Look Ahead Days (Summary!$B$2): "THIS WEEK".
• Anything else: "Later".

Done comes first so a finished section never shows as BEHIND because its planned finish has passed. BEHIND is tested before THIS WEEK so a section that should already be finished is not described as merely current. The label THIS WEEK assumes a Look Ahead Days of 7. With another number it means "inside your look-ahead window", and it covers a section that starts inside the window as well as one already under way. A section that has not started and begins after the window is Later.

Digest Line, cell H2

=IF(A2="","",A2&" | "&B2&" | finish "&IF(D2="","no date",TEXT(D2,"yyyy-mm-dd")&", "&IF(F2<0,-F2&IF(F2=-1," day"," days")&" past",IF(F2=0,"today","in "&F2&IF(F2=1," day"," days"))))&" | "&G2)

The line reads Section | Label | finish date with a day count | Flag. The date is wrapped in TEXT so it prints as 2027-03-05 and not as a serial number. A blank finish date prints "no date", and a blank row returns blank.

Action Count, Summary!C2

=COUNTIF(Plan!$G$2:$G$201,"BEHIND")+COUNTIF(Plan!$G$2:$G$201,"THIS WEEK")+COUNTIF(Plan!$G$2:$G$201,"Missing date")

Behind Count, Summary!D2

=COUNTIF(Plan!$G$2:$G$201,"BEHIND")

Both count the labels the Flag column writes for filled rows. Empty rows return blank, so they are never counted.

Digest Lines, Summary!E2

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Plan!$H$2:$H$201,(Plan!$G$2:$G$201="BEHIND")+(Plan!$G$2:$G$201="THIS WEEK")+(Plan!$G$2:$G$201="Missing date"))),"Nothing planned in the look-ahead window")

The three ranges inside FILTER all run from row 2 to row 201, so they are the same height. FILTER keeps the Digest Line of every row whose Flag is one of the three action labels, and TEXTJOIN stacks those lines with a line break between them. When nothing matches, FILTER returns an error and IFERROR substitutes the fallback sentence, so the cell is never empty.

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

Part 3: Keep Calculation on Automatic

TODAY() recalculates as the sheet is used. 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 that the day-count column matches 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 Monday and a morning time, for example before your first study block.
2. Action. Choose Google Sheets and the event Lookup Spreadsheet Row. Connect your Google account. Then choose the 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. In Subject, build the line in this order: the text Study digest: , the Action Count field from step 2, the text actions,, the Behind Count field and the text behind. In Body, insert the Digest Lines field from step 2. If the action offers a Body type setting, choose plain text so the line breaks from the sheet survive.

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 counts and the digest text. The email test should reach your inbox. Then turn the Zap on.

Part 5: Test and Refine

Add a dummy section with a planned finish in the past and Status In progress. Confirm that Flag reads BEHIND, that both counts rise by one and that the line appears in Digest Lines. Then change its Status to Done and confirm that it disappears. Delete the dummy row when done.

Real Example: A Monday in February

Invented setup: today is Monday 2027-02-22 and Look Ahead Days is 7, so a section is THIS WEEK when its planned start falls on or before 2027-03-01. The sections and labels are invented and say nothing about any real syllabus.

Working through the Flag formula:

• Sec-01: Status is Done, so Done, although its finish is 10 days past.
• Sec-02: Days to Finish is 2027-02-19 minus 2027-02-22, which is -3. Not done, both dates present, below 0, so BEHIND.
• Sec-03: 2027-03-05 minus 2027-02-22 is 11 (6 days left in February plus 5 in March), so it is not behind. Its start, 2027-02-22, is on or before 2027-03-01, so THIS WEEK.
• Sec-04: the finish is 18 days away (6 plus 12). Its start, 2027-03-01, is on or before 2027-03-01, so THIS WEEK. The window includes its last day.
• Sec-05: the finish is 25 days away (6 plus 19). Its start, 2027-03-02, is after 2027-03-01, so Later.
• Sec-06: no planned start, so Missing date. Its Days to Finish is 32 (6 plus 26), but the date test comes first.
• Sec-07: Days to Finish is 0, which is not below 0, so it is not behind. Its start, 2027-02-15, is before 2027-03-01, so THIS WEEK.
• Row 9: no section code, so every formula returns blank and nothing is counted.

Counts: BEHIND is 1 (Sec-02), THIS WEEK is 3 (Sec-03, Sec-04 and Sec-07) and Missing date is 1 (Sec-06). Action Count is 1 + 3 + 1 = 5. Behind Count is 1.

The Digest Lines cell holds five lines in sheet order:

Sec-02 | Reading block 2 | finish 2027-02-19, 3 days past | BEHIND
Sec-03 | Practice set 1 | finish 2027-03-05, in 11 days | THIS WEEK
Sec-04 | Practice set 2 | finish 2027-03-12, in 18 days | THIS WEEK
Sec-06 | Mock paper | finish 2027-03-26, in 32 days | Missing date
Sec-07 | Reading block 3 | finish 2027-02-22, today | THIS WEEK

The email arrives with the subject "Study digest: 5 actions, 1 behind" and those five lines in the body. If every section were Done or Later, both counts would be 0 and the body would read "Nothing planned in the look-ahead window". The email still arrives.

Setup: one spreadsheet, one Zap, three steps. Input: the Monday schedule and the rows you keep current. Output: one email to you with two counts in the subject and the flagged sections in the body. Time saved: the weekly check of your plan against your calendar. The digest is only as current as your last edit, so updating Status and dates is the price.

What to Do When It Breaks

• No email arrived on Monday. 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 someone renamed the Key cell, changed its text or moved the Summary tab. Restore the text summary in A2 exactly. A run that never appears at all 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.
• Put a standing reminder in your calendar. If no digest has arrived by mid-morning on Monday, 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 that Days to Finish matches today's date arithmetic.
• The email says nothing needs you, but you know something is close. The digest only reflects the rows. Check that the section is in the tab, that its dates are real dates and not text, and that the Status is not Done.
• Dates show as long numbers. A date was joined without TEXT. Re-paste the Digest Line formula exactly.
• A formula shows #REF! or #NAME?. A tab was renamed. Rename it back, or fix the tab name inside the formulas.
• Line breaks collapse in the email. Switch the Gmail step to plain text if it offers a body type. Then re-test.

Variations

• Simpler version: skip Zapier and open the Summary tab each Monday. The counts and lines are the same.
• Extended version: add a Google Form for logging a new section. Form answers land on their own responses tab, and you copy each one into Plan yourself. Never point a Form at the Plan tab, because it would overwrite your formulas. The Zap still runs on its schedule.
• A second weekday: copy the Zap and set the copy to run on another day.

What to Do Next

• This week: build the tab and test with dummy rows. Then switch the Zap on and read the first real email against your plan.
• This month: adjust Look Ahead Days and your planned dates after you see how the Monday emails compare with your real pace.
• Advanced: build the sister digests for close and valuation tasks, peer review backlog and model change documentation, each in its own spreadsheet with its own Summary tab.

Advanced guide for actuarial analysts. The digest flags dates you have typed. It does not set a study plan, state what an exam covers or judge your pace. Confirm exam details on the exam body's own site, and note that Zapier and Google may change their screens, so follow the labels you see.