Growth Department · Specialty
Referral Growth Engine SOP
For Jayme & the Specialty PRD team
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 extension: raw CSVs go to Phase 1 (prospect-list scan for Amy → PRD review); PRD-approved xlsx files go to Phase 2 (build the HTML engine + deploy to sga-growth.dev/growth-engine/).

Drop Folder Referral Growth Engine · Google Drive →

What the automation does — and what it needs from you

One scheduled run every weekday morning:

  • Weekdays 09:00 (Mountain) — the sweep pulls new files from both Drive folders (Input + Output) and dispatches by what it finds in each file:
    • .csv in the Input folder without a Keep? (Y/N) column → Phase 1. Amy runs a prospect scan and drops the resulting Excel back for PRD review.
    • .csv in the Input folder with a Keep? (Y/N) column → approved-CSV bridge. The sweep enriches the Keep=Y rows via Google Places (address, phone, website, drive-time), converts them into a Phase 2-shaped xlsx, and builds the engine in the same run.
    • .xlsx in the Input folder → Phase 2. Automation reads the approved xlsx directly, builds the HTML engine, and publishes to sga-growth.dev/growth-engine/.

Three things the automation needs to do its job:

  1. The file in the correct drop folder (Input folder for both raw scans and PRD-approved lists)
  2. The filename starts with the practice's Paylocity code
  3. The matching referrer roster CSV (any file in the same folder starting with the same Paylocity code that doesn't have _approved_ in the name) is present so the engine can populate the Referrers tab
✓ Approved CSV → engine, no manual bridge needed. The sweep detects any CSV whose header includes Keep? (Y/N) and treats it as PRD-approved. It enriches the Y-rows via Google Places, writes a per-practice xlsx under docs/specialty/referring-docs/referring-doc-output/<slug>_prospect-list_approved_YYYY-MM-DD.xlsx, then runs build_engine.py against it — all in one sweep. Column I Keep? (Y/N) = Y is the ingest signal. Toggle off with SGA_REFERRAL_SWEEP_SKIP_APPROVED=1 or force skeletal (no Places $) with SGA_REFERRAL_SWEEP_SKIP_ENRICH=1.
📁

1 · The Drive drop zone

One folder for everything

Referral Growth Engine · Google Drive

Every CSV — for a prospect-list build, an engine-only build, or a PRD-approved rebuild — goes in this folder. Drop files any day of the week; the automation checks them at the next scheduled run (Monday for untagged, Friday for tagged).

ℹ️ One file per practice. Don't combine multiple practices into one CSV. If you update a file that's already been processed, drop the new version in the same folder — the automation detects the change and rebuilds on the next run.
🏷️

2 · Filename convention

Paylocity code + practice name + tag before .csv

The automation reads the leading token in the filename to look up the practice in Scott's master crosswalk. It reads the tag before .csv to decide which lane the file is in.

<PAYLOCITY_CODE>_<Practice_Words_With_Underscores>_Provider_List[_engine-only|_approved].csv
✓ Correct
JAXDSPFLPP_Jacksonville_Dental_Specialists_Provider_List.csv
no tag → Monday scan queues it for PRD review
ALPTTAGAPP_Alpharetta_Perio_Provider_List_engine-only.csv
engine only, no prospect list needed
BEACHESLGA_Beaches_Perio_Provider_List_approved.csv
PRD approved the prospect list → Friday builds the full engine
✗ Will be skipped
Jacksonville Dental Specialists Provider List.csv
no Paylocity code prefix
alpharetta_perio_referrers.csv
lowercase code — must be uppercase
JAX_Dental_Specialists.csv
"JAX" isn't a real Paylocity code
❓ Not sure of the Paylocity code? Check Scott's master crosswalk (Microsoft List) — it's the Paylocity Code column, usually 6–12 uppercase letters or letters-with-hyphens (e.g. AAS-SC). If you can't find one, ping Amy before dropping the file.
🚦

3 · The three lanes

The tag before .csv tells the automation what you want

Three lanes. Pick one when you drop the file — you can always retag later.

No tag
_Provider_List.csv
Full flow — you want a prospect list + engine. Monday morning the automation emails Amy that this file needs a Phase 1 prospect scan. Amy runs the scan, sends the Excel to the assigned PRD. PRD reviews (marks Keep? / Override tier / notes on the QA tab). When PRD signs off, the file gets renamed with _approved and Friday builds the full engine.
Engine only
_Provider_List_engine-only.csv
You just want the referring-doctor engine — no prospect list needed. Friday morning the automation builds and publishes the engine with only the referring-doctor tab. The Prospects tab shows a friendly "not commissioned — contact Amy to add one" placeholder. Use this when the office already has a saturated referrer pool or opted out of prospecting.
PRD-approved
<slug>_prospect-list_approved_YYYY-MM-DD.xlsx
PRD has signed off on the prospect list. The next weekday morning the automation builds the full engine — referring-doctor tab from the original referrer CSV, Prospects tab from the approved xlsx. The xlsx MUST have two sheets:
  • Sheet 1 · Prospects — every candidate row with full data (name, address, phone, website, tier, drive-time to home location, strategy note).
  • Sheet 3 · PRD QA Checklist — one row per prospect with name, Keep? (Y/N), Override tier, PRD notes. Only rows where Keep? = Y are ingested into the engine's Prospects tab. Column I of Jayme's roster CSVs (Keep? (Y/N)) is the authoritative approval signal; Y or Yes = build into the engine.
Tier overrides and PRD notes on the checklist merge onto the corresponding Prospects row before render.
✓ Same folder, three outcomes. The Input Drive folder holds all three lanes side-by-side — untagged CSVs waiting for scan, engine-only CSVs, and approved xlsx files. Extension + tag together determine what the sweep does with each file.
🏢 Multi-location practices — split by Paylocity code, or consolidate into a single master when one PRD owns the whole territory. If a referral network spans two SGA offices with separate crosswalk entries (e.g., AAS-SC Aiken Oral Surgery + AAG-GA Augusta Oral Surgery), you have two options: (a) split the approved prospect list by whichever office each prospect is closer to (drive-time) and produce two xlsx files — one keyed on each Paylocity code — the single-location build_engine.py then produces two engines under sga-growth.dev/growth-engine/<slug>_referral_growth_engine.html; or (b) if one PRD manages both offices as one territory, consolidate into a single master engine with both locations declared. Reference build (option b): aiken-augusta oral & facial surgery — master, built 2026-08-07 from Jayme's AAS-SC_aiken-augusta-prospect-list_approved_2026-07-24.csv + AIKEN_aikenaugusta_PartnerProviders_2026-07-16.csv.
📋

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 (tools/referral-engine/run_daily_sweep.sh, LaunchAgent com.sga.referral-engine-daily-sweep) runs one dispatch loop over all new files:

Phase 1 · .csv in Input folder

  1. For each untagged raw CSV it hasn't seen, the sweep queues that practice for a Phase 1 prospect scan.
  2. Amy gets a ticket in sga3p listing every practice waiting for Phase 1, plus the run-log path.
  3. Amy runs Phase 1 for each, delivers the resulting Excel to the assigned PRD, and rolls approvals back into the Input folder as an .xlsx (with the two required sheets — see section 3).

Phase 2 · .xlsx in Input folder

  1. For each PRD-approved xlsx, the sweep looks up the practice in Scott's crosswalk by the leading Paylocity code.
  2. Inspects the xlsx: reads the Prospects sheet, counts Keep? = Y rows on the PRD QA Checklist, checks for multi-location driveMin_* columns.
  3. Builds the HTML engine — referring-doctor tab from the referrer CSV, Prospects tab from the Keep=Y subset with tier overrides applied.
  4. Auto-bumps version (v1, v2, v3…) so previous builds stay reachable at dated URLs.
  5. Publishes to sga-growth.dev/growth-engine/<slug>_referral_growth_engine.html via a single wrangler pages deploy at the end of the sweep.
  6. Posts a summary ticket to sga3p listing every build, waiting file, and skip.

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

✓ No confirmation needed. If you don't hear back after the next weekday run, the file was processed cleanly. Amy will ping you if anything got skipped or if a Phase 1 result needs your review.
🛑 Multi-location xlsx files get blocked, not built. If an approved xlsx has more than one driveMin_<LocationName> column, the sweep pauses that file and tickets Amy for a manual split into per-office xlsx files (one per Paylocity code). See the multi-location callout in section 3.
🚧

7 · When a file gets skipped

Reasons a file can get skipped or wait

  • No Paylocity code prefix. The filename doesn't start with uppercase letters + _. → Rename and re-drop.
  • Paylocity code not in the crosswalk. Typo, or Scott hasn't added the practice yet. Common trap: composite/fake codes like AIKENAUGSCDD for a two-office network — use the real per-office codes (AAS-SC, AAG-GA). → Fix the code or add to crosswalk; next run picks it up.
  • Missing or corrupt CSV/xlsx data. Practice column empty on every row, unparseable file, xlsx missing the Prospects or PRD QA Checklist sheet. → Reissue the export.
  • Approved CSV with no matching referrer roster. The bridge finds the approved CSV (Keep column present) and builds the xlsx, but Phase 2 can't locate a companion referrer roster (any <code>_*.csv without _approved_ in the name). → Drop the referrer roster CSV in the same folder with the matching Paylocity code prefix.
  • Multi-location xlsx (two or more driveMin_* columns). The sweep blocks and tickets Amy to split into per-office xlsx files. → Wait for Amy's manual split.
  • Untagged CSV (waiting). Not an error — the file is queued for Phase 1. Nothing to fix on your end unless you meant engine-only or already-approved (see section 3).

Skips and waits get logged to the daily sweep summary at data/output/referral-engine-daily-sweep-YYYY-MM-DD.md and a ticket lands in sga3p. Fix the underlying issue and the file processes on the next weekday run — no need to re-drop 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.