Growth Department · Specialty
Referral Growth Engine SOP
For Jayme & the Specialty PRD team · v2 process · updated 2026-09-21
Referral Growth Engine · Daily Automation

How to drop new practice files so they get built and deployed automatically

The automation runs every weekday at 09:00 Mountain. It dispatches by file type: a raw referrer CSV goes to Phase 1 (the sweep builds the prospect list for PRD review); a PRD-approved xlsx goes to Phase 2 (build the HTML engine + deploy to sga-growth.dev/growth-engine/). Every outcome comes back to you as a task in sga3p.

Input folder You drop files here →
Output folder Prospect lists come back here →

What the automation does — and what it needs from you

One scheduled run every weekday morning:

  • Weekdays 09:00 (Mountain) — the sweep reads both Drive folders (Input + Output) and dispatches by what it finds in each Input file:
    • .csv without a Keep? (Y/N) column → Phase 1. The sweep runs the prospect scan itself, writes the prospect-list Excel to the Output folder, and sends you a task.
    • .xlsx whose name contains prospect list → Phase 2. The sweep reads your approved list, builds the HTML engine, publishes it, and sends you a task with the URL.
    • .csv with a Keep? (Y/N) column → approved-CSV bridge. Same as Phase 2, but the sweep first enriches the Keep = Y rows via Google Places (address, phone, website, drive time) and converts them into an approved xlsx.

Three things the automation needs to do its job:

  1. The file in the Input folder — both the raw roster and, later, the approved list
  2. The filename starts with the practice's Paylocity code (or, for the approved xlsx, the practice slug the sweep gave it)
  3. The original referrer roster CSV left in the Input folder, unrenamed, so the engine can populate the Referrers tab
✓ You always get a task. Prospect list ready, engine deployed, multi-location hold, or failure — each one lands in sga3p assigned to you the morning it happens. If a weekday passes after a drop and no task arrives, tell Amy. Silence is not success.
📁

1 · The Drive drop zone

Two folders — Input and Output

Input folder — everything you drop goes here: the raw referrer roster CSV to kick off a practice, and the PRD-approved prospect list (xlsx) when the review is done.

Output folder — the sweep drops the generated prospect-list Excel here for you to review with the PRD. You never need to put anything in this folder.

Drop files any day of the week; the sweep checks the Input folder every weekday at 09:00 Mountain. Drop on a Friday afternoon and it processes Monday.

ℹ️ One file per practice. Don't combine multiple practices into one CSV. If you need to re-scan a practice, drop a new version of the roster — the sweep tracks each Drive file by ID and last-modified time, so a changed file re-runs on the next sweep. Don't rename the original roster after you've dropped it (see section 7).
🏷️

2 · Filename convention

Roster CSV: Paylocity code + underscore + anything

The sweep reads the leading token of a CSV filename (everything before the first underscore) and looks it up in the SGA crosswalk. What comes after the underscore is up to you.

<PAYLOCITY_CODE>_<Practice_Words>_Provider_List.csv

Approved xlsx: keep the name the sweep gave it, or code + "prospect list"

The easiest thing is to leave the filename exactly as it came out of the Output folder. If you rename it, it must start with the Paylocity code (or the practice slug) and contain the words prospect list. Underscores, hyphens, or spaces are all fine.

<slug>_prospect-list_v1_YYYY-MM-DD.xlsx   (unchanged from the Output folder)
<PAYLOCITY_CODE>_prospect_list_approved_YYYY-MM-DD.xlsx
✓ Correct
JAXDSPFLPP_Jacksonville_Dental_Specialists_Provider_List.csv
roster → next weekday the sweep builds the prospect list
pensacola-perio-and-implant-dentistry_prospect-list_v1_2026-08-17.xlsx
approved list, name unchanged from the Output folder
PENSACFLPP_prospect_list_approved_2026-09-15.xlsx
approved list renamed with the code — also fine
✗ Will be skipped
Jacksonville Dental Specialists Provider List.csv
no Paylocity code prefix
jaxdspflpp_provider_list.csv
lowercase code — must be uppercase
CJS_Referrals_by_Doctor_L12M.xlsx
xlsx without "prospect list" in the name — treated as a reference file and ignored, with no task
❓ Not sure of the Paylocity code? Use the Internal Listing in sga3p — it's the Paylocity Code column, usually 6–12 uppercase letters or letters-with-hyphens (e.g. AAS-SC). Don't take codes from the PGP assignment spreadsheet — about half its rows carry old or shifted codes. Abbreviations like CJS aren't codes. If in doubt, ask Amy before dropping the file.
🚦

3 · The three lanes

The file type tells the automation what you want

Three lanes, decided by what's in the file — there are no filename tags.

Raw roster
<CODE>_…_Provider_List.csv
Kick off a practice — you want a prospect list + engine. Next weekday morning the sweep scans the market around the practice, builds the prospect-list Excel (Prospects · Saturation Check · PRD QA Checklist sheets), drops it in the Output folder, and sends you a task titled "Prospect list ready for PRD review — <Practice>". Review it with the PRD.
PRD-approved list
…prospect-list….xlsx
PRD has signed off. On the PRD QA Checklist sheet, set Keep? (Y/N) = Y on every office the PRD wants to pursue (optionally Override tier and PRD notes), save, and drop the xlsx in the Input folder. Next weekday morning the sweep builds the engine — Referrers tab from your original roster CSV, Prospects tab from the Keep = Y rows — publishes it, and sends you "Referral Growth Engine deployed — <Practice>" with the live URL. Only Keep = Y rows are ingested; tier overrides and PRD notes merge onto those rows.

Engine only? If the PRD doesn't want a prospect list, mark no rows Keep = Y and drop the xlsx anyway. The sweep sees zero approvals and ships the engine with a "not commissioned" placeholder in the Prospects tab.
Approved CSV
<CODE>_….csv with a Keep? (Y/N) column
Alternative to the xlsx when the PRD worked the approval in a CSV instead. The sweep spots the Keep? (Y/N) column, enriches the Y rows via Google Places (address, phone, website, drive time), converts them into an approved xlsx, and builds the engine in the same run. The filename must start with the Paylocity code, and the original roster CSV must still be in the Input folder.
✓ Same folder, three outcomes. The Input folder holds rosters and approved lists side by side. The sweep remembers what it has already processed, so files can stay where they are — nothing needs to be moved, renamed, or deleted after a run.
🏢 Multi-location practices go on hold, not to the builder. If the approved list covers more than one home office (more than one driveMin_<Location> column on the Prospects sheet), the sweep pauses it and sends you a task titled "Referral Engine — multi-location practice needs Amy's review". Reassign that task to Amy. Amy either splits the list by office (one engine per Paylocity code, e.g. AAS-SC Aiken + AAG-GA Augusta) or builds a single master engine with all locations declared — reference: aiken-augusta oral & facial surgery — master, built 2026-08-07.
📋

4 · CSV column format

Two supported schemas — the automation auto-detects

The automation reads the CSV header row and picks the matching parser. Both schemas produce the same engine output; the differences are what data each carries and how tiering is derived.

Preferred · Referral-Lab Export

What Jayme's most recent Jacksonville drop uses. Doctor-level rows with rank and specialty; the automation groups rows by practice address, picks the top-ranked doctor as the card lead, and lists other doctors in the notes.

Legacy · Office Manager Roster

The original format from Q2. Practice-level rows with office manager contact info. Still supported for older data pulls; tiering is driven by a suffix on the Practice column (see section 5).

Referral-Lab Export columns

Column headerNotes
Overall_RankInteger rank (1 = top referrer). Drives tier — see section 5.
First_NameDoctor first name
Last_NameDoctor last name
NicknameOptional
IsInternalTrue / False. True → Green Internal tier.
AffiliatedPartnerOptional
SpecialtyGeneral Dentist, Endodontist, Oral Surgeon, Orthodontist, Pediatric Dentist, etc.
Mobile_PhoneDoctor mobile (optional)
Email_AddressDoctor email (optional)
PracticePractice / office name
Address, Address2, City, State, ZipcodePractice address (used to dedupe doctors at same office)
Phone_Number, Fax_NumberPractice main lines
Study_Club_Name, Birthday_Date, Graduated_YearOptional — surfaced in notes when present
Last_Note, Next_Task, Last_ReferralOptional — surfaced on the card

Legacy Office-Manager columns

Column headerNotes
PracticePractice name. Suffix rules drive tier — see section 5.
Address, Address2, City, State, ZipcodePractice address
Phone_Number, Fax_Number, Email_AddressPractice contact
Office_Manager_First_Name, _Last_Name, _Email_AddressOffice manager contact
Hours_of_OperationFree-text (optional)
💡 Which one do I have? If your CSV has an Overall_Rank column and separate First_Name / Last_Name for the doctor, it's the Referral-Lab Export (preferred). If it has Office_Manager_First_Name and no rank column, it's the legacy Office-Manager Roster. Both work.
🎨

5 · How tiering works

Green / Yellow / NeedsReview — driven by whichever schema you dropped

Referral-Lab Export → Rank drives tier

IsInternal = True
Green · Internal. 4×/year cadence (Jan · Apr · Jul · Oct). Protect data flow.
Overall_Rank 1–25
Green · Top referrer. Monthly (12×/year). Protect the relationship, deliver post-treatment reports.
Overall_Rank 26–75
Yellow · Established. Bi-monthly (6×/year). Identify upgrade path to top-25.
Overall_Rank 76+
Yellow · Lower volume. Quarterly (4×/year). Test case-fit alignment.
Overall_Rank blank
NeedsReview. Not yet ranked. PRD sets cadence after first visit.

Legacy Office-Manager → Suffix on Practice column drives tier

-INTERNAL REFERRALS
Green · Internal source. 4×/year cadence. Sets a protected data-flow cadence.
- New Ref Outreach
Yellow · First-Referral office. 6×/year nurture cadence.
(no suffix)
Established referrer. Historical roster — PRD reviews and sets cadence after first visit.
⚠️ Suffix must match exactly. For the legacy format, spacing and capitalization matter: -INTERNAL REFERRALS (no space after dash) and - New Ref Outreach (space after dash) are what the parser looks for. Anything else falls into the "no suffix" bucket.
⚙️

6 · What the automation does

Every weekday 09:00 (Mountain) — one sweep, two phases

The daily sweep runs on Amy's machine (if her laptop is asleep at 09:00 it runs when it next wakes). It syncs both Drive folders, then works through every new or changed Input file:

Phase 1 · raw roster .csv

  1. Looks up the Paylocity code in the SGA crosswalk to get the practice name and home address.
  2. Scans the surrounding market (Google Places, within about an hour's drive), scores and tiers the candidates, checks saturation against your roster, and builds the Excel with the Prospects, Saturation Check, and PRD QA Checklist sheets.
  3. Uploads the Excel to the Output folder.
  4. Sends you a task: "Prospect list ready for PRD review — <Practice>", with a link to the file and the saturation verdict.

Phase 2 · approved .xlsx

  1. Resolves the practice from the filename — the Paylocity code, or the slug the sweep gave the file — using the Phase 1 record.
  2. Inspects the xlsx: counts Keep? (Y/N) = Y rows on the PRD QA Checklist and checks for multi-location driveMin_* columns (multi-location → hold, see section 3).
  3. Builds the HTML engine — Referrers tab from your original roster CSV, Prospects tab from the Keep = Y rows with tier overrides applied. Approved prospects are marked visit-ready, so they appear on the Start Here snapshot, Monthly Routes, and Weekly Execution in their cadence months (A = Jan · Jun · Sep, B = Feb · Oct).
  4. Auto-bumps the version (v1, v2, v3…) so previous builds stay reachable.
  5. Publishes to sga-growth.dev/growth-engine/<slug>_referral_growth_engine.html and adds it to the growth-engine index.
  6. Sends you a task: "Referral Growth Engine deployed — <Practice>", with the live URL. Spot-check it before the next PRD meeting.

Published engines live at sga-growth.dev/growth-engine/ (SGA SSO required).

🔔 Every run that touches your file produces a task. Ready, deployed, on hold, or failed — you'll see it in sga3p the same morning. Failures are assigned to you with the error in the description; reassign those to Amy. If you dropped a file and hear nothing by the next morning, tell Amy — the file was probably not recognized (section 7).
💾 Rebuilt an engine you'd already edited? The engine page remembers edits you make in the browser (tier overrides, notes) and shows those instead of the fresh build. Viewing doesn't do this — only editing does. After a rebuild, click ⟲ Reset once on the engine page to load the new data.
🚧

7 · When a file gets skipped

Reasons a file can get skipped, fail, or wait

  • No Paylocity code prefix on a CSV. The filename doesn't start with uppercase letters + _. → You get a failure task. Rename and re-drop.
  • Paylocity code not in the crosswalk. A typo, an abbreviation (CJS), a made-up composite code for a two-office network (AIKENAUGSCDD), or a stale code from the PGP spreadsheet. → Failure task. Fix the code (section 2) and re-drop.
  • xlsx without "prospect list" in the name. The sweep assumes it's a reference file (e.g. a referrals-by-doctor pivot) and ignores it — silently, every day, with no task. This is the one case you won't hear about. → Rename so the name contains prospect list and starts with the code or slug; it processes on the next run.
  • Missing or corrupt data. Practice column empty on every row, unparseable file, xlsx missing the Prospects or PRD QA Checklist sheet. → Failure task. Reissue the export.
  • Approved list with no matching roster. Phase 2 can't find a roster CSV with the same Paylocity code in the Input folder. → Failure task. Put the original roster CSV back (or drop it fresh) with the matching prefix.
  • Multi-location list. Two or more driveMin_* columns. → Hold task to you; reassign to Amy.
  • Nothing happened, no task. The file hadn't changed since the sweep last processed it — normal for files that stay in the folder. If you meant to re-run it, drop a new version.
⚠️ Don't rename the roster CSV to add _approved. That was the July process and it no longer applies. The roster stays exactly as you dropped it; approval is signalled by the xlsx (or a CSV with a Keep? (Y/N) column). Renaming the roster in Drive can make the sweep treat it as a new file and run a second, paid prospect scan.

Amy can see every skip and wait in the daily sweep log — if something looks wrong, send her the filename and the date you dropped it.

Questions?

Email Amy McNeill. Drops made any weekday get picked up at the next 09:00 (Mountain) sweep. If it's urgent and needs to go out the same day, drop by 08:30 Mountain.