← AI Career Launcher
0/15 complete

Module 05 · Job-Search Automation

Tracking Applications in a Simple AI-Assisted Spreadsheet

15 minfocused lesson6practical steps4grounded questions4source links
Open lesson + course map

On this lesson

Course outline

An application tracker is a pipeline, not a list of jobs you once clicked. Each row should show what you sent, where the process stands, and which dated action comes next.

By the end, you will have a working spreadsheet of your live applications and a weekly AI review that suggests one evidence-based change without guessing why employers replied or stayed silent.

// concept

Build the Sheet Around Decisions

Use Google Sheets, Excel, or LibreOffice Calc. One row represents one role at one company. Freeze row 1, enable a filter, and use this exact schema:

ColumnHeaderEntry
ARoleEmployer's exact job title
BCompanyPublished employer name
CSourceLinkedIn, Rozee.pk, company site, or referral
DJob URLDirect vacancy link
EVariant SentExact resume filename
FDate AppliedYYYY-MM-DD
GStageApplied, Screen, Interview, Assessment, Offer, or Closed
HNext ActionVerb-led task, such as Send one follow-up email
INext Action DateDate the task becomes due
JNotesFactual contact or interview note
KFurthest StageApplied, Screen, Interview, Assessment, or Offer
LOutcomeActive, Rejected, Withdrawn, Accepted, or No response

Columns K and L preserve the funnel. If an interview ends in rejection, Stage becomes Closed, Furthest Stage remains Interview, and Outcome becomes Rejected. Never store CNIC numbers, home addresses, references' phone numbers, passwords, or salary slips here.

In Google Sheets, select G2:G, choose Insert > Dropdown, and enter the fixed stage values. Repeat for K and L. Confirm the current menu path in official help because interfaces change. Plain text with identical spellings is a valid offline alternative.

// concept

Make Active Rows Actionable

Filter Outcome to Active during a daily two-minute check. Every visible row needs a dated next action or a factual note explaining why you are waiting. The employer's written response window should control your follow-up; do not automate repeated messages.

Apply conditional formatting to I2:I with this custom formula. It highlights an overdue date only for an active application:

// prompt — copy me1 line
=AND($I2<TODAY(),$L2="Active",$I2<>"")

Keep the sheet private and review cloud sharing. On a phone, keep Notes short and store interview preparation elsewhere so the pipeline remains scannable.

// concept

Review Your Personal Funnel With AI

Conversion is the share of applications reaching the next stage. It describes your records, not a hiring-market benchmark. Always show numerator and denominator; a percentage based on two applications is weak evidence.

On a Weekly Review tab, use formulas like these. Later stages count as having reached earlier stages:

// prompt — copy me5 lines
Applications =COUNTIF(Applications!K2:K,"<>")
Screens =COUNTIF(Applications!K2:K,"Screen")+COUNTIF(Applications!K2:K,"Interview")+COUNTIF(Applications!K2:K,"Assessment")+COUNTIF(Applications!K2:K,"Offer")
Interviews =COUNTIF(Applications!K2:K,"Interview")+COUNTIF(Applications!K2:K,"Assessment")+COUNTIF(Applications!K2:K,"Offer")
Screen rate =IFERROR(B2/B1,"")
Interview-from-screen rate =IFERROR(B3/B2,"")

Adjust cell references, format rates as percentages, and verify counts manually. Then export only needed rows or paste a redacted table into this weekly prompt:

// prompt — copy me14 lines
Review my application tracker for the seven days ending [DATE]. Use only <tracker>.
Do not infer employer motives or invent stages. These are personal baselines, not benchmarks.

1. Count applications by source, role family, current stage, and furthest stage.
2. Show each conversion as numerator/denominator and percentage.
3. List active rows with an overdue or missing Next Action Date.
4. Give at most two observed patterns; label limited evidence "tentative".
5. Recommend one next-week test that changes only one variable.
6. State what data is missing for a stronger conclusion.

Output: Counts | Funnel | Due actions | Tentative patterns | One test
<tracker>
[PASTE REDACTED CSV]
</tracker>

// worked_example

Worked Example

Sample data: A hypothetical Lahore graduate records six analyst applications: three from LinkedIn, two from company career pages, and one from Rozee.pk. All reached Applied, two reached Screen, one reached Interview, and two active rows have overdue actions. No real employer is represented.

The first AI review says, “Company career pages are your best source, so stop using LinkedIn.” The sheet cannot support that claim: each source group is tiny, while roles and resume variants also differ.

The corrected output reads: “Observed snapshot: 2/6 applications reached Screen; 1/2 screened applications reached Interview. Evidence is too limited to rank sources. Next-week test: hold the role family and resume variant consistent while continuing to record source and furthest stage.” The two overdue actions remain the immediate priority. This review reports the pipeline and proposes a controlled test; it does not invent a cause.

// failure_cases

Failure Cases to Diagnose

5 cases to diagnose

  • Link dump

    Role exists, but Stage and Next Action are blank. Count a row as applied only after Date Applied and Variant Sent exist.

  • Split stage labels

    Phone call, HR call, and Screening fragment one stage. Replace them with the fixed dropdown values.

  • Progress disappears

    Closing a rejected interview erases the interview count. Preserve Interview in Furthest Stage and record Rejected in Outcome.

  • Invented cause

    AI calls silence a “resume failure.” Restate it as an observation and identify the evidence needed to explain it.

  • Follow-up spam

    Multiple reminders target one unanswered role. Keep one justified follow-up, respect the employer's timeline, and then close the action.

// pakistan_angle

Pakistan Angle

Keep separate source labels for LinkedIn, Rozee.pk, Mustakbil, company career pages, university placement offices, and referrals. This lets you inspect your own sourcing mix without claiming one board works best across Karachi, Lahore, Islamabad, or every profession. Record Onsite, Hybrid, or Remote only when the vacancy states it.

Pakistani recruiters may move a process to WhatsApp. Record Interview time confirmed by WhatsApp, but do not paste the chat, phone number, CNIC image, address, or family details into an AI prompt. Redact contacts before exporting rows and check the AI service's current data controls. Keep a local .xlsx or .csv copy for load-shedding, and sync when the connection is stable.

// hands_on

Hands-On Exercise

6 steps

Build the tracker with your real, authorized application data:

  1. Create Applications and enter all twelve headers in the stated order.

  2. Add dropdowns for Stage, Furthest Stage, and Outcome; freeze row 1 and enable a filter.

  3. Enter your current applications with the exact resume filename and a dated action for every active row.

  4. Add the overdue-date rule. Test it with a temporary past date, then remove that value.

  5. Create Weekly Review, add the funnel formulas, and verify two counts by hand.

  6. Run the prompt on a redacted view and save its one-variable test below the formulas. Done means another reviewer can locate every live application, see its next action, reproduce the stage counts, and identify next week's single test.

// completion_rubric

Completion Rubric

5 checks — tick as you verify

0/5

// sources

Sources

4 official sources — check every claim yourself

// check_yourself

Check yourself

4 questions · answers and options are taken word-for-word from this course

0/4

  1. 1 / 4 · diagnose

    Your work shows this failure mode: “Split stage labels.” The lesson describes it like this: “`Phone call`, `HR call`, and `Screening` fragment one stage.” What does the lesson tell you to do about it?