Independent Educational Consultant Software: An AI-Built Tracker

An independent educational consultant can build a student deadline tracker in Google Sheets with AI: one tab for students, one for deadlines, formulas that flag what is due in 7 and 14 days, a weekly email digest, and templated parent updates. It takes about a morning, needs only a Google account, and can replace a paid platform for a small caseload.

The idea is not to out-engineer a purpose-built platform. It is to own a small system that does exactly what your practice needs, which for most consultants is three things: never miss a deadline, keep families informed without writing the same email twice, and keep student information tidy and private. AI helps you write the formulas and scripts, which is the part that usually stops people from building their own.

Follow me on Instagram@sagnikteaches

When to build, when to buy

Paid planning platforms exist, and some are good. Pricing models differ (GuidedPath, for example, listed one flat annual fee of $489 with no per-student charge when checked in November 2026), so compare each platform's current pricing page and what it includes. Building makes sense when your workflow is distinctive, when you have fewer than a few hundred active students, and when you are the only user. Buying makes sense when families need logins, when several consultants share one system, or when you want integrated document upload and messaging. If you already know you need those, skip the build and read the vendor's privacy terms carefully; what to check in an AI tool's privacy policy and terms applies equally to planning software.

Connect on LinkedInSagnik Bhattacharya

Build for a season first. Run one application cycle in the sheet, note what annoys you, and decide at the end whether to keep it or move on. The data is yours in either case and exports cleanly as a spreadsheet.

Subscribe on YouTube@sagnikteaches

A worked example: a consultant with eighteen students

Say a consultant works alone with 18 students, each applying to about eight schools or programmes, with roughly four dated items per school: the application itself, an essay draft, a recommendation request and a test or document submission. That is 18 x 8 x 4 = 576 dated items in one cycle. Without a system, that means 576 chances to miss a date in a calendar, a notes app and an inbox. With the sheet, every item sits in one table, sorted by due date, with a status and a flag. The figures here are illustrative; scale them to your own caseload.

The consultant spends about three hours building the tracker in the first week, then about 30 minutes each Monday reviewing the digest, updating statuses and sending parent updates. In busy weeks near the deadlines the digest might list 20 items; in quiet weeks, five. The practical benefit is a single list that answers "what must happen this week, and for whom?" in seconds.

Design the tabs before you open Sheets

Good trackers are small. Resist adding columns for things you will not look at again. Start with four tabs.

Use a student code such as S01 to S18 as the key rather than a name in every row. The names and contact details sit in one place, the Students tab (or better, in your email contacts), so the Deadlines tab can be shared with an assistant or opened on a shared screen without exposing anyone. That small design choice is the most useful privacy measure in the whole build.

Fill the Deadlines tab with realistic rows

Here is an illustrative slice. Dates are written in a clear format so the sheet reads them as real dates.

item,student,school,type,due,owner,status
D001,S03,School A,Application,2026-12-01,Student,In progress
D002,S03,School A,Essay draft,2026-11-18,Student,Not started
D003,S03,School A,Recommendation request,2026-11-10,Parent,Submitted
D004,S07,School B,Test registration,2026-11-12,Parent,Not started
D005,S07,School C,Application,2026-12-15,Student,Not started

Add data validation to the type, owner and status columns (Data, Data validation, Add rule, then the criterion "Dropdown from a range" pointing at the Lists tab) so entries stay consistent. Typed variations such as "submitted" and "Submitted " break formulas.

Let AI write the formulas, then test them

Describe what you want in plain words and give the real column letters. A chat assistant is good at this, and the Google Sheets AI prompts tutorial has more patterns. A prompt for the first helper column:

In Google Sheets, the Deadlines tab has: A item, B student, C school,
D type, E due date, F owner, G status (Not started / In progress /
Submitted). In column H, write a formula for row 2 that gives the days
until the due date, blank if status is Submitted. In column I give a
flag: "Done" if Submitted, "Overdue" if due date is past, "This week" if
within 7 days, "Next 14 days" if within 14, otherwise "OK".
Both columns must stay blank when the due date is empty.
Return plain formulas I can paste.

A good answer looks like this:

H2:  =IF(OR(E2="",G2="Submitted"),"",E2-TODAY())
I2:  =IF(E2="","",IF(G2="Submitted","Done",IF(E2<TODAY(),"Overdue",IF(E2-TODAY()<=7,"This week",IF(E2-TODAY()<=14,"Next 14 days","OK")))))

Copy both down the column, a few rows past your data. Then test with four rows you can judge by eye: one due yesterday (Overdue, -1), one due in five days (This week, 5), one marked Submitted (Done, H blank) and one empty row (both blank). The empty row is the test assistants most often fail: a first draft without the E2="" check counts an empty due date as a long-past date and labels every blank row "Overdue", which the colour rule below then paints red. If a row says "OK" when it is due tomorrow, the date was probably entered as text. Select the column, format it as a date and retype one cell to check.

Highlight what needs attention

Ask the assistant for conditional formatting rules by flag value: Overdue in red, This week in amber, Done in grey. In Format, Conditional formatting, choose "Custom formula is" and use a formula such as =$I2="Overdue" applied to A2:I. The row then colours itself, and you can scan 500 rows in seconds.

Build a "next 14 days" view

Add a tab called Dashboard with one formula in A2 that pulls the next two weeks of open items, soonest first.

=IFERROR(SORT(FILTER(Deadlines!B2:G, Deadlines!G2:G<>"Submitted", Deadlines!E2:E<>"", Deadlines!E2:E-TODAY()<=14), 4, TRUE), "Nothing due in the next 14 days")

Paste it as one line. The columns come back as student, school, type, due, owner, status. The fourth column is the due date, which is why the sort index is 4. The blank-row test Deadlines!E2:E<>"" keeps empty rows out; without it you would see a long list of nothing. Overdue items stay in the view, because their days left are negative, and they sort to the top. The IFERROR wrapper matters on quiet weeks: Google's help notes that FILTER returns #N/A when no rows match, which looks like a broken sheet. If the due column shows plain numbers, format that column of the Dashboard as a date. If the assistant gives you a QUERY formula instead, test it the same way, because QUERY can behave oddly when a column mixes dates and blanks.

Send yourself a weekly digest

A view you must remember to open is a weaker habit than an email that arrives on Monday morning. Google Sheets can run a small script on a schedule, and an AI assistant can write it from a description. Ask for "a Google Apps Script that emails me, every Monday at 7am, a plain-text list of unsubmitted deadlines due in the next 14 days from the Deadlines tab, sorted by due date".

function weeklyDigest() {
  const sh = SpreadsheetApp.getActive().getSheetByName('Deadlines');
  const rows = sh.getDataRange().getValues();
  rows.shift(); // drop the header row
  const today = new Date(); today.setHours(0, 0, 0, 0);
  const soon = [];
  const notDates = [];
  rows.forEach(r => {
    const due = r[4];
    if (r[6] === 'Submitted' || due === '') return;
    if (!(due instanceof Date)) { notDates.push(r[0]); return; }
    const d = Math.round((due - today) / 86400000);
    if (d <= 14) soon.push([d, r[1], r[2], r[3]]);
  });
  soon.sort((a, b) => a[0] - b[0]);
  let body = soon.map(s => s[0] + ' days: ' + s[1] + ', ' + s[2] + ', ' + s[3]).join('\n') || 'Nothing due.';
  if (notDates.length) body += '\n\nCheck the date format on: ' + notDates.join(', ');
  MailApp.sendEmail(Session.getEffectiveUser().getEmail(), 'Deadlines, next 14 days', body);
}

Open the editor from the sheet with Extensions, Apps Script, paste the function, save, and run it once by hand to approve the permissions. Then choose Triggers in the left panel, add a trigger for weeklyDigest, and set it to time-driven, weekly, Monday, in the 7am to 8am slot. Google's documentation says the exact run time is slightly randomised within that hour, so expect the email some time before 8. Two details in this version matter. It sends to Session.getEffectiveUser(), which under an installable trigger is the account that created the trigger, so the email reaches you even when nobody has the sheet open. And it lists any row whose due date is not a real date at the bottom of the email, instead of silently leaving it out, which is the failure you would otherwise never notice. On a sample sheet with an item two days overdue, one due in five days, one Submitted, one due in 20 days and one with a date typed as text, the email lists the overdue and five-day items, skips the Submitted and 20-day ones, and names the text-date row for checking. Read the script before you run it. Never run code you do not understand against a sheet that holds student records; ask the assistant to explain each line and confirm that it sends mail only to you. The digest uses student codes, so even if an email is forwarded by mistake it names no one. An overdue item appears with a negative number, which is a useful nudge.

Parent update emails that do not take an hour each

Parents want regular, calm updates. Writing them from scratch each week takes time, and the same structure works every time: what happened, what is next, what you need from them and by when. A chat assistant can turn a few facts into a draft, which you then check and send from your own email.

Write a short, warm email to a parent. Use [first name] for the parent
and [student] for the student.
Facts: three items are due in the next two weeks: the School A essay
draft on 18 Nov (student is writing it), the test registration on
12 Nov (parent needs to book), and recommendation letters, which are
requested. Needed from parent: book the test by Friday.
Tone: friendly, clear, no jargon. Under 150 words.
Do not promise any admission outcome.

An illustrative result:

Hi [first name],

A quick update on [student]'s applications. Three things are coming up:
the School A essay draft is due on 18 November and [student] is working
on it; the recommendation requests have gone out; and the test
registration closes on 12 November.

The one thing I need from you is to book the test by Friday. Let me
know if the booking page gives you any trouble.

I'll be in touch again next Monday.

[your name]

Check the draft against the tracker before sending. The common AI mistake is a confident date that is not in your facts. Also look for hidden promises: a phrase like "this will strengthen the application" implies an effect you cannot guarantee. Put the facts into the prompt from your sheet; do not paste the whole row if you can avoid it, and use a business-plan tool that does not train on your content by default.

For a family that reads in another language, ask for a plain-English version first, then translate it, and consider having a native reader check anything important. The tutorial on language school emails and translations with AI covers the method. Record each update on the Updates tab so you can see at a glance which families have not heard from you in ten days.

Fill the tracker from school pages, then verify every date

Typing 576 dates is the dull part of the build, and AI can speed it up if you treat its output as a draft. Copy the text of a school's admissions page into a chat assistant and ask for a table of dates. Include the source in the prompt so it does not fill gaps from memory.

From the text below only, list every admissions date as a table:
item, due date, notes. If a date is missing or unclear, write "not stated".
Do not guess the year. Text: [paste the page]

Then open the school's page beside the result and tick each date against it. Typical slips are a date with the wrong year, a "priority" deadline merged with a regular one, and a time zone or cut-off hour that the page states and the table drops. These are the errors that cost a student an application, so make the tick-through a rule: nothing goes into the Deadlines tab until a person has matched it to the school's own page, and put the page address in a Notes column so you can recheck it in the autumn when schools update their dates.

A season in the tracker, week by week

Here is how the sheet is used across one illustrative cycle, so you can judge whether it fits the way you work.

• Early autumn, setup. Add each student on the Students tab with consent recorded. Build each target list with school names and dates verified as above. About 20 minutes per student.
• Mid autumn, steady state. The Monday digest lists 8 to 15 items. You mark statuses at the end of each student call, which takes a minute, and send one parent update per family every second week using the template prompt.
• Crunch weeks. Say 22 items fall in the 10 days before a popular deadline. The capacity view shows it three weeks ahead, so you move two essay reviews earlier and warn four families to book tests sooner. Without the view you would have found out in the week itself.
• After submission. Mark items Submitted and keep a Notes line for the confirmation received. If a school says it has not received a recommendation, the tracker shows the date you requested it, which makes the follow-up call short.
• Close, in spring. Export each student's record to your secured storage, delete or archive the rows that your retention rule says to remove, and rebuild the template for the next cohort.

A crunch-week example with numbers

Say it is the first Monday in November and the digest lists 19 items due in 14 days: 6 for one popular deadline on the 15th, 5 owned by parents, 4 essay drafts and 4 others. The consultant sorts the 5 parent-owned items first, since those depend on someone else's time, and sends three emails that morning with the update template. By Wednesday, 3 of the 5 are marked In progress. The consultant moves an essay review from the 14th to the 11th for the student with the heaviest week. Nothing is missed. The value is not clever technology: it is that the list existed, was sorted by date, and was in front of the consultant on Monday morning.

Consent, privacy and what never goes in the tracker

You hold information about minors, often with sensitive details about grades, family finances and wellbeing. Treat it that way. Rules differ by place and student age, so take advice from a qualified adviser on what you must do; the practical habits below are sound in any case.

• Get written agreement before you start. Your client agreement should say what you keep, why, who can see it, how long you keep it, and whether you use AI tools for drafting or checking. Record consent in the Students tab with a date, so you know who has agreed.
• Keep personal details out of the tracker where you can. Names and contact details live in your email or address book. The tracker uses codes.
• Do not store sensitive items. No passwords to student portals, no identity numbers, no medical or financial documents, no recommendation letter text. Reference them by file name in a secured drive.
• Limit access. Share the tracker with no one by default. If an assistant needs the next-14-days view, do not publish the sheet to the web, which makes it readable by anyone with the link. Make a separate spreadsheet that pulls only the Dashboard range with IMPORTRANGE, and share that file with view access only: Google's help notes that once access is granted, any editor of the destination sheet can pull data from any part of the source.
• Use AI with care on student work. Essays are the student's own work. AI may suggest structure or point to unclear paragraphs, but it should not write the essay, and a school may prohibit AI-written text. The tutorial on using AI on student essays in a counselling business goes through where the line sits.
• Delete when the cycle ends. Decide a retention date in your agreement, such as 12 months after the last application, and remove the rows or archive them offline.

No guarantees of admission

A tracker makes you look organised, and it can tempt you to over-promise. Admission decisions belong to the schools. Use wording like: "I advise on process, fit and presentation. I do not guarantee any admission, scholarship or other outcome." Put it in your agreement, in your website copy and in the footer of parent emails. Do not let AI draft marketing lines that claim results, and do not cite acceptance statistics unless you can document them exactly and say what they cover.

Extend it in small steps

Once the basic sheet works through a month, add one thing at a time. Each of these is a prompt away.

• A checklist per application type. Make a Templates tab listing the standard items for each school type, such as application, essays, two recommendations, test scores. A formula or short script then creates the dated rows when you add a new target school.
• A parent-facing summary. Export the next-14-days view for one student as a PDF for a monthly call. Use student first names only if the family agreed.
• A capacity view. Count items due per week across all students to see the crunch weeks and warn families early. Add a helper column with =IF(E2="","",E2-WEEKDAY(E2,2)+1), which gives the Monday of each item's week (type 2 counts Monday as day 1), and use it as the rows of a pivot table. A Monday date avoids the jump a plain week number makes when a season runs from November into January.
• A risk flag. Mark any student with three or more "Not started" items due within 14 days. On the Students tab, with the student code in A2, =COUNTIFS(Deadlines!B:B,A2,Deadlines!G:G,"Not started",Deadlines!E:E,"<="&(TODAY()+14)) gives the number, overdue items included, and a colour rule can turn the cell red at 3 or more. Test it on a copy with a student you can count by eye.
• A form for intake. A Google Form linked to the spreadsheet, with the consent question at the top, saves retyping. Keep its responses apart from the Students tab and copy each new student across once you have checked the consent answer.

When something breaks

Three failures account for most problems. Dates stored as text make formulas silent; reformat the column, and watch the bottom of the Monday email, where the script names any such row. Rows added below a fixed range such as E2:E500 are quietly left out; use open-ended ranges such as E2:E, as the Dashboard formula does. The weekly script stops after a permissions change; open the Apps Script editor, run it once by hand and re-authorise. When you ask AI to debug, describe the symptom and paste the formula, not the data.

What it costs and what you give up

The sheet itself costs nothing beyond your existing Google account, and a business-tier chat assistant that does not train on your content by default is the only likely extra. Against a flat-fee platform at around $489 a year, that saving is real but modest, so judge the build on fit rather than price. Your time: about three hours to build, then 30 minutes a week plus the time to write updates, which AI shortens. What you give up compared with a paid platform: family logins, built-in document upload, automatic reminders to students, and support when something breaks. If those become important, you will know from the cycle, and the sheet will show exactly which features you need. For other ways to use AI with student records and parent messages, see private schools: admissions, fees and parent communication and how tutoring centres write parent progress reports.

Questions consultants ask before building their own tracker

When is a spreadsheet no longer enough?

Usually when several people need to edit at once, when you want students or parents to log in and see their own page, or when you pass a few hundred active students. Until then a well-built sheet is cheaper and easier to change than a paid platform.

Can I paste student essays into an AI tool?

Only with the student's and parent's agreement, and with care. Essays are the student's work and often personal. Use a business plan that does not train on your content by default, remove names, and never let AI write the essay. Feedback on structure is a different job from authorship.

Do I need parental consent to keep student records?

For minors, plan on written agreement from a parent or guardian covering what you keep, why, who sees it and whether AI tools are used. Rules differ by place and by the student's age, so check with a qualified adviser and put the answer in your client agreement.

How do I word the admission-outcome disclaimer?

State that you advise on process and fit, that admission decisions belong to the schools, and that you cannot guarantee any outcome. Put it in your agreement, on your website and in the footer of parent updates. Do not quote success rates unless you can evidence them exactly.

What should the tracker never contain?

Passwords to student portals, full identity document numbers, health or financial details beyond what you strictly need, and the text of recommendation letters. Keep sensitive documents in a properly secured storage system and refer to them in the tracker by name only.

Further reads

• Student Information System for a Small School: What AI Adds — What AI adds to a school student information system.
• AI for Flight Schools: Scheduling, Enquiries and Progress Records — Another training business tracking progress records and scheduling.
• How to Anonymise Client Data Before You Paste It Into AI — Remove identifiers before pasting anything into AI.
• Can AI Answer Student Questions for Your Online Course? — Let AI handle routine student questions with limits.
• Using AI for Childcare Observations and Learning Journals — Keeping records about children with extra care.
• How to Get Your Staff to Actually Use AI Tools — Get an assistant or co-consultant using the tracker.
• Language School Software Compared: Enrolment, Timetables and AI — Fidelo School, Teach 'n Go and ElsieApp checked on enrolment, timetables, agent commissions, invoicing and the AI features that actually exist.
• How Can AI Help School Administrators? Ten Jobs, With Prompts — Ten school office jobs AI handles well, each with a copyable prompt, a sample output and a check, plus the placeholder rule and where AI must not be used.
• How to Start a Language School: Accreditation, Agents and AI — What a new language school costs in year one, how accreditation and agents fit together, and where AI saves admin time from the first term.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Google Docs Editors Help (FILTER, SORT, WEEKDAY, IMPORTRANGE, dropdowns), Google Apps Script documentation (Session, installable triggers, container-bound scripts), GuidedPath pricing page, OpenAI and Anthropic business privacy pages, checked 4 November 2026. Student examples are invented.