SGA Growth · Reporting · August 12, 2026
Patient Funnel Reporting Project
Turning the Patient Funnel from wireframe into a monthly production report — one page per practice, delivered to ROD + Growth Manager, starting Sept 5, 2026 with August data for the 20 Marketing Focus practices.
Executive briefing · Prepared for SLT · Amy McNeill, VP of Field Marketing
Executive Summary
Where We Are, What We're Asking
The Monthly Report
What Every ROD Will Receive on the 5th
Live preview of the approved format — one page per practice, delivered as HTML + PDF. Numbers below are illustrative.
Full sample: /reports/patient-funnel-sample/ · Design already reviewed by Ron and Sarah · Locked layout — no further design cycles
The Funnel We're Measuring
Five Stages · Ten Sources · One Page Per Practice
1 · Marketing Investment
Google Ads · Promo spend
▶
2 · Demand & Leads
GA4 sessions · GHL forms
▶
3 · Opportunities
PeerLogic · Neurality
▶
4 · Scheduled
PeerLogic · Neurality
▶
5 · Completed NP
Power BI · DentalIntel
Plus two KPI overlays
Coverage Snapshot · 10 Sources · 20 Practices
Where We Have Data Today
| Stage | Metric | Source file | Total in source | Focus-20 covered | Status |
|---|---|---|---|---|---|
| 1 | Google Ads spend / clicks / CTR / CPC | gads_live_full_portfolio_2026-08-05.xlsx | 87 accounts | 11 / 20 | Gap |
| 1 | Promotional spend (monthly $) | promotional-spend-by-location-jan-jun-2026.xlsx | 262 practices | 19 / 20 | Live |
| 2 | Website sessions (GA4) | ga4_sgagmail_2026_0729.csv (roster only) | 96 properties | 9 / 20 | Build |
| 2 | GHL form submissions | ghl-general-round1-baseline (roster only) | 21 sub-accounts | 19 / 20 | Build |
| 3 | PeerLogic calls (inbound + missed) | Peerlogic Call Metrics May 2026.csv | 12 practices | 9 / 20 | Gap |
| 3 | Neurality voice opportunities | neuralitylive_20260608.csv | 129 rows | 11 / 20 | Gap |
| 4 | Scheduled (PeerLogic + Neurality) | same as Stage 3 | — | — | Same |
| 5 | Completed NP · Production · Hygiene | dentalintel_6.3.2026.csv (Power BI live via sga-data MCP is source of truth) | 214 practices | 18 / 20 | Live |
| KPI | Google Reviews (new + rating) | SGA_Google_Place_IDs.csv → SerpAPI | 236 practices | 18 / 20 | Live |
| KPI | GBP scheduling-CTA audit | 2026-08-07-focus20-gbp / gbp-scheduleappt-audit CSV | 20 practices | 19 / 20 | Live |
Live production-ready · Gap/Build roster exists but monthly extract not yet flowing · Missing source doesn't exist (none)
Metric 1 of 10 · Stage 1
Google Ads — Spend, Clicks, CTR, CPC
Coverage of Focus-20
11
of 20 practices
GAP 9 practices unmapped in Google Ads portfolio
STAGE 1 · MARKETING INVESTMENT
What the ROD sees
Total spend for the month · clicks · impressions · CTR · avg CPC · YOY comparison to the same month prior year. Powers "Are we spending more or less, and is it working?"
Source of truth
data/output/gads_live_full_portfolio_2026-08-05.xlsx · sheet all_accounts · keyed by Paylocity Code · pulled live from Railway marketing-ingest service, not from Postgres sync
Refresh cadence
Daily via Google Ads API — no build needed for cadence
Gap to close
Map the 9 unmapped GEN4-legacy accounts to Paylocity Codes
Owner
Amy · one-time mapping pass · 2 days
Metric 2 of 10 · Stage 1
Promotional Spend — Total Marketing $ by Month
Coverage of Focus-20
19
of 20 practices
LIVE Missing: BELFSTGADD (Scott crosswalk row pending)
STAGE 1 · MARKETING INVESTMENT
What the ROD sees
Total marketing $ for the month across all channels — used as the denominator for CAC. Excludes accounting credits and reversals per data-integrity rule.
Source of truth
data/output/promotional-spend-by-location-jan-jun-2026.xlsx · sheet Monthly Spend · keyed by Paylocity Code · sourced monthly from Gen4 GEN4 MM.YYYY TTM workbooks
Refresh cadence
Monthly · TTM workbook lands ~5th of month · scraper auto-rolls
Gap to close
Scott adds BELFSTGADD row to Master Crosswalk
Owner
Scott · 1 day once queue opens
Metric 3 of 10 · Stage 2
GA4 — Website Sessions
Coverage of Focus-20
9
of 20 practices (access only)
BUILD No sessions file exists yet — only property-ID roster
STAGE 2 · DEMAND & LEADS
What the ROD sees
Total website sessions for the month with YOY comparison. Answers "Is our top-of-funnel visibility growing or shrinking on the current ad spend?"
Source of truth
data/access/ga4_sgagmail_2026_0729.csv is the property-ID roster (96 properties). A monthly sessions extract via the GA4 Data API needs to be built and scheduled — target: data/output/ga4-sessions-YYYY-MM.csv
Refresh cadence
Target: monthly API pull · 2nd of each month
Gap to close
Add GA4 access for 11 practices · build GA4 Data API pull
Owner
Amy (access) + Dakota (API pull) · 2 weeks
Metric 4 of 10 · Stage 2
GoHighLevel — Form Submissions
Coverage of Focus-20
19
of 20 sub-accounts exist
BUILD Sub-accounts exist for 19/20 · form-events dump not yet built
STAGE 2 · DEMAND & LEADS
What the ROD sees
Total form submissions for the month, split into paid-media LP fills vs organic/direct site forms. Answers "Are our landing pages doing the work, or is it all organic overflow?"
Source of truth
data/input/ghl-general-round1.baseline-2026-07-10-v3 (1).xlsx is the sub-account roster. Monthly form-events extract will consume Dakota's Railway Postgres GHL mirror once live · target: data/output/ghl-form-events-YYYY-MM.csv
Refresh cadence
Target: monthly extract from GHL mirror · 2nd of each month
Gap to close
GHL Railway mirror live · MTZ-NC sub-account provisioned
Owner
Dakota (mirror) + Adam (MTZ-NC provision) · 3 weeks
Metric 5 of 10 · Stage 3 & 4
PeerLogic — Inbound Calls & Missed-Call Rate
Coverage of Focus-20
17
on PeerLogic (roster)
GAP Monthly export only covers 9/20 today
STAGE 3 · OPPORTUNITIES + STAGE 4 · SCHEDULED
What the ROD sees
Inbound calls · missed calls · missed-call % (called out on the dashboard because it's the biggest recoverable leak) · scheduled appointments from calls · call-to-schedule rate.
Source of truth
data/input/PeerLogic/Peerlogic Call Metrics by Location - May 2026.csv · keyed by Location Name. Roster: peerlogic_Main_Lines_v2.csv · blank domain ≠ not on PeerLogic
Refresh cadence
Target: monthly export from PeerLogic · 3rd of each month
Gap to close
Request expanded monthly export covering all Focus-20 practices
Owner
Amy · vendor request · 1 week
Metric 6 of 10 · Stage 3 & 4
Neurality — Voice AI Opportunities & Scheduled
Coverage of Focus-20
11
of 20 practices
GAP 9 practices not deployed on Neurality
STAGE 3 · OPPORTUNITIES + STAGE 4 · SCHEDULED
What the ROD sees
Voice opportunities captured by the AI agent · scheduled appointments split by channel (Voice · SMS · Online) · new-patient scheduled subset. Vendor-calc badge shown on the dashboard.
Source of truth
data/input/neuralitylive_20260608.csv · keyed by Location Name + City. Newer data/access/neurality-utm-links-2026-07-28-full.xlsx extends UTM tagging.
Refresh cadence
Monthly vendor delivery · already committed · 5th of each month
Gap to close
Business decision — do we deploy Neurality to the remaining 9?
Owner
SLT decision · Nathan / Brendan approve rollout
Metric 7 of 10 · Stage 5
Power BI / DentalIntel — Completed NP, Production, Hygiene
Coverage of Focus-20
18
of 20 practices
LIVE Missing: BELFSTGADD, LKMARYFLDD
STAGE 5 · COMPLETED NEW PATIENTS (HEADLINE METRIC)
What the ROD sees
Completed new patients for the month vs previous-month goal · production per NP · hygiene reappointment rate vs SGA network average. The headline number on every report.
Source of truth
data/input/dentalintel_6.3.2026.csv is the current file. Live source of truth: Power BI via sga-data MCP — offline dump is the fallback until MCP auth is stable in scheduled runs.
Refresh cadence
Live via MCP · monthly snapshot on the 4th
Gap to close
BELFSTGADD crosswalk row · Lake Mary data hygiene fix
Owner
Scott (crosswalk) + Amy (Lake Mary) · 1 week
Metric 8 of 10 · KPI Overlay
Google Reviews — New Reviews & Rolling Rating
Coverage of Focus-20
18
of 20 practices
LIVE Missing: BELFSTGADD, WYOMNGMIDD (Place ID)
KPI OVERLAY
What the ROD sees
New Google reviews received in the previous month vs 10-review monthly goal · rolling star rating. Vendor-calc badge because SerpAPI is the enrichment layer.
Source of truth
data/input/SGA_Google_Place_IDs.csv is the Place ID roster. Monthly counts pulled via SerpAPI (Places API caps at 5 reviews per pull — SerpAPI returns exact counts).
Refresh cadence
Monthly SerpAPI pull · 3rd of each month · already scripted
Gap to close
Look up WYOMNGMIDD Place ID · BELFSTGADD (crosswalk)
Owner
Amy · 1 day
Metric 9 of 10 · KPI Overlay
GBP Scheduling CTA — Is Book Online Live?
Coverage of Focus-20
19
of 20 practices
LIVE Missing: SPH (Liberty rebrand — check under Spinnaker)
KPI OVERLAY
What the ROD sees
Is a visible on-profile scheduling / appointment CTA present on the Google Business Profile? Detected platform (NexHealth, LocalMed, Modento, RevenueWell, Weave/TrueLark, Neurality, direct website). Read off the rendered profile — never inferred from the practice website.
Source of truth
docs/audits/gbp-audit/2026-08-07-focus20-gbp/gbp-scheduleappt-audit-2026-08-07.csv · monthly re-audit via gbp-scheduleappt-audit skill
Refresh cadence
Monthly audit re-run · 4th of each month
Gap to close
Re-check SPH under Liberty vs Spinnaker Google entity
Owner
Amy · 1 day
Metric 10 of 10 · Derived
CAC — Cost to Acquire a New Patient
Coverage of Focus-20
18
of 20 practices
LIVE Derived from Metric 2 ÷ Metric 7
KPI · OUTCOME
What the ROD sees
Promotional spend $ ÷ completed new patients = CAC · shown against YOY for the same month · also shown as "Google Ads-only CAC" for paid-media isolation. Excludes accounting credits and reversals per data-integrity rule.
Source of truth
Numerator: promotional-spend-by-location-jan-jun-2026.xlsx (Metric 2) · Denominator: dentalintel_6.3.2026.csv / Power BI (Metric 7). Both cover 18/20 focus practices today.
Refresh cadence
Auto-calc once both parent metrics land · no separate cadence
Gap to close
BELFSTGADD crosswalk row (unblocks both parent metrics)
Owner
Scott · 1 day once crosswalk queue opens
Gaps to Close · Before Sept 5
Four Builds Between Today and Launch
| # | Gap | Metrics affected | Fix | Owner | Deadline |
|---|---|---|---|---|---|
| 1 | GA4 monthly sessions extract | Metric 3 (sessions) | Build GA4 Data API monthly pull · request access for 11 missing practices from GA4 admins | Dakota (API) + Amy (access) | Aug 26 |
| 2 | GHL form-events dump | Metric 4 (form submissions) | Consume Dakota's Railway Postgres GHL mirror once live · provision MTZ-NC sub-account | Dakota (mirror) + Adam (MTZ-NC) | Aug 29 |
| 3 | PeerLogic monthly full export | Metric 5 (calls, missed calls, scheduling) | Vendor request for expanded scheduled monthly delivery covering all Focus-20 practices on PeerLogic | Amy (vendor req) | Aug 20 |
| 4 | Master Crosswalk — BELFSTGADD row | Metrics 2, 7, 8, 10 (promo spend, NP, reviews, CAC) | Scott adds Belfast River Dental row to Master Crosswalk once new practice code is issued | Scott | Aug 22 |
Action Plan · Owners × Tasks
Who's Doing What Between Now and Sept 5
| Owner | Task | Deadline | Unblocks |
|---|---|---|---|
| Amy McNeill VP Field Marketing | Map 9 unmapped GEN4-legacy Google Ads accounts to Paylocity Codes | Aug 19 | Metric 1 → 20/20 |
| Request GA4 access for 11 missing focus-20 properties | Aug 22 | Metric 3 | |
| Vendor request to PeerLogic — expanded monthly export | Aug 20 | Metric 5 → 17/20 | |
| Fix Lake Mary DentalIntel hygiene issue · re-audit SPH under Liberty Google entity | Aug 26 | Metrics 7, 9 | |
| Dakota Engineering | Build GA4 Data API monthly extract → data/output/ga4-sessions-YYYY-MM.csv | Aug 26 | Metric 3 |
| Complete GHL Railway Postgres mirror · expose monthly form-events extract | Aug 29 | Metric 4 | |
| Scott Data / Crosswalk | Add BELFSTGADD row to Master Crosswalk when practice code issued | Aug 22 | Metrics 2, 7, 8, 10 |
| Adam Paid Media / GHL | Provision MTZ-NC GHL sub-account | Aug 29 | Metric 4 → 20/20 |
| Nathan / Brendan VP Operations | Decision — deploy Neurality to remaining 9 focus practices? (yes/no/phased) | Sept 15 (post-launch OK) | Metric 6 → 20/20 |
| Amy McNeill | Deploy monthly report generator · dry-run Sept 3 · ship Sept 5 | Sept 5 | Launch |
Timeline · Aug 12 → Sept 5
Four Weeks to Launch
Task
Wk 1 · Aug 12-18
Wk 2 · Aug 19-25
Wk 3 · Aug 26-Sep 1
Wk 4 · Sep 2-5
PeerLogic vendor request
Scott · BELFSTGADD crosswalk
GA4 access requests (×11)
Google Ads unmapped mapping
Dakota · GA4 Data API build
Dakota · GHL mirror + events
Adam · MTZ-NC GHL provision
Amy · Lake Mary + SPH cleanup
Report generator build
Neurality coverage decision
Ask from SLT
Four Approvals · One Decision
Appendix
The 20 Marketing Focus Practices
Cohort 1 · Wave-1 · Launched May 2026
| Code | Practice | City | ROD / GM |
|---|---|---|---|
| MTZ-NC | Moskowitz | Concord NC | Millican / Caldwell |
| SOAKCOSCDD | Southern Oak | Conway SC | Millican / Caldwell |
| PCHFDGGADD | Peach Family | Fort Valley GA | Pace / Caldwell |
| SGC-MS | Smile Gulf Coast | Gulfport MS | Mangum / Freemyer |
| LKMARYFLDD | Lake Mary | Lake Mary FL | Howlett / Freemyer |
| PRYRFDTNDD | Pryor | Lebanon TN | Pace / Caldwell |
| OAKSMLGADD | Oakwood Smiles | Oakwood GA | Pace / Caldwell |
| LAKEDCFLDD | Lake Shore | Saint Cloud FL | Howlett / Freemyer |
| BTD-GA | Benner & Townsend | Winder GA | Pace / Caldwell |
| SCO-FL | Smile Center Orlando | Winter Park FL | Howlett / Freemyer |
Cohort 2 · Wave-2 · Launched August 2026
| Code | Practice | City | ROD / GM |
|---|---|---|---|
| WSTPRTKSDD | Westport Dental | Topeka KS | Knopp / Freemyer |
| BELFSTGADD | Belfast River crosswalk | Richmond Hill GA | Miller / Freemyer |
| CLAYFDKSDD | Clay Center Family | Clay Center KS | Knopp / Freemyer |
| GAGEDGKSDD | Gage Dental Group | Topeka KS | Knopp / Freemyer |
| HERITGUTDD | Heritage Dental | Syracuse UT | Sorenson / Freemyer |
| SPH | Liberty Family rebrand | Hinesville GA | Howlett / Caldwell |
| PALMCDSCDD | Main Street Family rebrand | Lexington SC | Millican / Caldwell |
| RCKHTLUTDD | Rock Hotel Dental | Farmington UT | Sorenson / Freemyer |
| HIGHDTGADD | Ways Station Family rebrand | Richmond Hill GA | Miller / Freemyer |
| WYOMNGMIDD | Wyoming Dental | Wyoming MI | Grevious / Caldwell |
Source: data/roster/focus-roster.csv · maintained by focus-roster tool
Thank you
Amy McNeill · VP of Field Marketing · amcneill@gen4dental.com
Live sample: sga-growth.dev/reports/patient-funnel-sample/ · This deck: sga-growth.dev/reports/patient-funnel-project/