BrandaCare Metrics & Dashboards

Purpose

The system generates, per billing cycle and per client, a WordPress-embedded dashboard (brandacare.com) with: insurance money collected, A/R recovered, insurance verifications, and no-shows. The widget is ONE and modular: turns cards on/off based on the client's "tier". Everything runs itself, daily, via GitHub Actions, and publishes via SFTP to Cloudways.

Restricted access: contains OD, Stedi, SFTP and GitHub keys. Do not share with operations team.

Section 1 — Architecture, tiers, and client roster

General flow

Open Dental (API) + OPS Google Sheet (tab "ALL DATA", filtered by "Doc")
                              ▼
                     recovery_metrics.py
                              ▼
                output/metrics_<id>.json
                              ▼
        widget in /dashboards/ on Cloudways
                              ▼
         iframe in WordPress (email-gated)

run_pipeline.py orchestrates all clients; GitHub Actions runs it daily and uploads the JSONs + widget via SFTP.

Tiers (define which cards the client sees)

Tier	Cards	Examples
`billing+insurance` (full)	Total recovered lifetime + A/R 6m chart + Insurance collected + A/R recovered + Verifications + No-shows	Hallandale, Casas
`billing+ar`	Total recovered lifetime + A/R chart + Insurance collected + A/R recovered. NO verifications	Benitez
`verifications` (verif-only)	ONLY verifications + Solstice + No-shows. Hides all money numbers, even if OD has them. Skips payment extraction → runs fast	A+

Client roster (jun 2026)

Client	Tier / Source	Dashboard status	Notes
Hallandale Dental Care	billing+insurance (OD API)	LIVE	MVP
Benitez Dental Center	billing+ar (OD API)	LIVE	Lifetime A/R $13,192
A+ Dental of Aventura	verifications (OD API)	LIVE	No-show via Unscheduled List
Dr Casas Family Dentistry	billing+insurance (OD API)	PENDING	Waiting for more A/R to reflect work
G-Dental	Dentrix (no API)	FUTURE	Cross-match OPS + Dentrix reports
Le Dentiste	Dentrix (no API)	FUTURE	Cross-match OPS + Dentrix reports

Section 2 — Money metrics (`recovery_metrics.py`)

The 3 figures (exact definitions)

Figure	Definition
TOTAL RECOVERED (lifetime)	Accumulated A/R recovered since `service_start_date`.
INSURANCE COLLECTED (cycle)	ALL insurance collected in cycle, regardless of age.
A/R RECOVERED (cycle)	Subset collected with DOS of 45+ days — old claims that required follow-up.

"Recovered" rule (crystal clear)

A/R recovered = payment posted MORE than 45 days after DOS (CheckDate − ProcDate > 45).
The date floor is on PAYMENT DATE (>= service_start_date), NOT on the DOS. A claim with old DOS (even Dentemax era) collected during our engagement DOES count.
Code by code: each claimproc counts literally. NO deduplication by amount.

Sources and filter

Open Dental (claimpayments → claimprocs) for payments; appointments for visits.
OPS Google Sheet, tab "ALL DATA", FILTERED by "Doc" column = ops_doc_name (the tab mixes all clients).

New flags

Flag	Function
Verif-only (`tier=verifications`)	SKIPS `od_insurance_payments` (`do_billing=False`) → much faster and shows no money.
`--as-of YYYY-MM-DD`	Cuts the count to that date (day snapshot, e.g. to compare with a manual report).

Command and outputs

python recovery_metrics.py --client <id> \
    --ops-sheet <SHEET_ID> --ops-tab "ALL DATA" \
    [--as-of 2026-06-28] --debug

output/metrics_<id>.json → consumed by widget. Includes "tier" and "modules" (cards on/off).
Audit CSVs: recovered_by_patient, recovered_lines, verifications_detail, broken_appts_detail.

Section 3 — Insurance verifications

What counts

Source: OD appointment notes with INS ACTIVE/INACTIVE - BC UNIONED with OPS ("Result" column = Active/Inactive).
ONLY patients who SHOWED UP (appointment AptStatus = Complete). No-shows, future, and unconfirmable are excluded.
Deduplicated by patient (1 per patient/cycle).

Value (ADA reference — updated)

Metric	Value	Note
Time saved per verification	10 minutes	`MINUTES_SAVED_PER_VERIFICATION = 10`, same as the web
Money saved per verification	$5.63	ADA. Implies ~$33.78/hour — higher than raw salary ($20-22) because it captures errors/denials avoided
Client-editable	`verification_value_per_usd`	Client config

Solstice (courtesy) — NEW

Key rule:

Identified by carrier: the verifications detail now brings "Ins Carrier" (from OPS) and a "Solstice?" flag.
solstice_count = verifications whose carrier contains "solstice".
Carrier is crossed by patient from OPS; may be unknown for verifications only from OD notes (no OPS match), so solstice_count is a floor.

Never use "free": Solstice and no-shows are shown as "complementary · on us" / "no charge".

Section 4 — No-shows (two definitions per client)

Default (broken on the calendar)

No-show = patients we VERIFIED and then didn't come (appointment Broken in OD).
Deduplicated by patient.
JSON also brings broken_appointments_all (all broken in cycle, not only verified ones) for cross-check.

A+ and similar: `noshow_source = "unscheduled"` — NEW (key)

Problem solved: A+ doesn't mark "Broken": sends the appointment to Unscheduled List (AptStatus=UnschedList), which has no calendar date, so day-by-day scanning returned 0.

fetch_od_unscheduled_noshows() brings AptStatus=UnschedList filtering by AptDateTime (the missed slot) within cycle, with dateStart/dateEnd server-side, and deduplicates by patient. Reproduces the client's manual count.
Full OPS UnschedList has tens of thousands (historical planned treatments); the AptDateTime-in-cycle filter isolates the real no-shows of the period.

Cut to TODAY (don't count the future) — jul-2026 fix

Critical fix:

In an ONGOING cycle we count only up to today: ce_eff = min(end_of_cycle, today).
Before it took the whole cycle (until the end), and grabbed Unscheduled appointments with future AptDateTime (planned treatments not yet occurred) as if they were no-shows.
False positive signature: DateTStamp BEFORE AptDateTime (appointment edited before its date = future). A real no-show is marked on/after its appointment day.
The --as-of flag uses the same logic to reproduce a past day snapshot.

Section 5 — A+ Model (verifications-only) — NEW

A+ pays ONLY for verifications (no billing or A/R). The dashboard shows the billable number clearly and adds courtesies to inflate the total value, validating the price.

Category	Formula
Billable (what they pay)	Completed verifications − Solstice
Courtesy (no charge)	Solstice + No-shows (verified, didn't come)
Total verified	Completed + no-shows

Value math (hours and $) goes over the TOTAL.

Example (Jun 2026):
294 billable + 30 Solstice + 137 no-show = 461 total verified ≈ 76.8 hrs ≈ $2,595 (vs ~$1,000/month they pay → 2.4x)

JSON fields

billable_verifications, solstice_count, broken_appointments,
total_verified, verification_value_total_usd, hours_saved_total

Section 6 — A/R Recovery (unbilled procedures)

Finds completed procedures WITHOUT a claim in Open Dental (post-Dentemax), identifies billable ones and allows sending to insurance.

Script	Function
`find_unbilled_procedures.py`	Finder
`monthly_report.py`	Client Excel report
`send_unbilled_week.py`	837D submission

Only real dental insurance (excludes self-pay / "Unknown Carrier")
Valid CDT (D + 4 digits). Excludes D9986/D9987/D9310/D9311
Timely filing flag
When in doubt, send; 276 when payer supports it; NO mass bulk

Section 7 — Dashboard widget (modular)

File and modularity

ONE template for all: web/brandacare_recovery_widget.html reads the JSON via ?data= and turns cards on/off based on d.modules (derived from tier). Backward compatible: if modules is missing, it's inferred from tier.

Layouts

Tier	Layout
full	Left LIFETIME (total + 6m A/R chart); right THIS CYCLE (collected, A/R, verifications, no-shows)
verif-only (A+)	Big billable card (blue number) + "Total verified/hours/$"; on the right 2 gray cards: Solstice ("complementary · on us") and No-shows ("no-show · no charge")

Gray background #E2E6F0
Reports its height via postMessage so the iframe auto-adjusts (no scroll)
Cache-buster in URL: ?v=N (bump N on each design change) + &t=time()

Section 8 — WordPress embed (WPCode + Elementor)

PHP snippet (WPCode)

web/wordpress_dashboard_snippet.php: shortcode [brandacare_dashboard]. A $clients array maps login email → client_id, and shows the iframe with the correct metrics_<id>.json.

Add a client: one new line in the array. Admin can preview any with ?client=<id> in the URL.

Cache-buster

The ?t=time() goes in the JSON URL (metrics_<id>.json?t=...), NOT just in the widget. That way the web never serves old data. After a change, purge Breeze (Purge All Cache) and refresh with Cmd+Shift+R.

Full-width gray

Wrapper with box-shadow + clip-path (full-bleed) and lateral padding to center/shrink content. Native alternative: Elementor section with #E2E6F0 background and Content Width = Boxed.

Publish steps in WordPress

1WPCode → edit snippet → paste new version → Update

2If Breeze installed, Purge All Cache

3Refresh with Cmd+Shift+R

4Client page: shortcode `[brandacare_dashboard]`

One page works for everyone.

Section 9 — Auto deploy (GitHub Actions + Cloudways)

Components

File	Function
`run_pipeline.py`	Lists `CLIENTS=[...]` and runs `recovery_metrics` for each (email alerts if something fails)
`.github/workflows/daily.yml`	Daily cron (08:00 UTC) + `workflow_dispatch` (manual button). Restores credentials from secrets, runs the pipeline, uploads via SFTP (`lftp`) the `metrics_*.json` + widget HTML to Cloudways

Secrets (Settings → Secrets and variables → Actions)

Secret	Content
`SERVICE_ACCOUNT_JSON`	Google service account (for OPS Sheet)
`ENV_FILE`	`.env` with general keys
`CLIENT_HALLANDALE`, `CLIENT_BENITEZ`, `CLIENT_APLUS`	Per-client config JSONs
`SFTP_HOST/USER/PASS/PATH`	Cloudways credentials

Repo hygiene (PRIVATE): .gitignore excludes keys (clients/*.json, .env, service_account.json) and patient data (*.pdf, *.csv, BCBOT/, out/, screenshots). Never push PHI. Use git add carefully (add -A grabs EVERYTHING).

Manual deploy (when code or widget changes)

git add -A && git commit -m "..." && git push

Then: GitHub → Actions → BrandaCare daily metrics → Run workflow → wait for green check (5-8 min).

Git auth: token (PAT classic) with repo + workflow scope (user ybrandacare, password = ghp_...).

Section 10 — Replicate to a new client

1Create `clients/<id>.json`

With: open_dental (keys), stedi, provider, google_sheet (sheet_id, tab_prefix), tier, cycle_start_day, service_start_date, ops_doc_name, verification_value_per_usd (5.63).

For verif-only: tier="verifications", noshow_source="unscheduled", tier_verifications_included, monthly_fee.

2Confirm EXACT name of OPS "Doc" column

Put it in ops_doc_name.

3Share OPS with service account (reader)

claims-audit-bot@claims-audit-495602.iam.gserviceaccount.com

4Run `recovery_metrics` with `--debug`

Validate 2-3 payments/verifications against Open Dental.

5Add `client_id` to `CLIENTS` in `run_pipeline.py`

6Create the `CLIENT_<NAME>` secret in GitHub

And restore it in daily.yml (printf to clients/<id>.json).

7Add client's email to `$clients` array

In the WPCode snippet.

8`git push` → Run workflow → verify the dashboard

With ?client=<id>.

Widget v1 (frozen, reference)

The frozen v1 widget lives on as reference for Hallandale's original layout. It's at brand/widgets/brandacare_recovery_widget_v1.html.

Note: v1 is NOT what runs in production — the current one is modular (web/brandacare_recovery_widget.html). v1 is kept as a historical snapshot.

Purpose

Section 1 — Architecture, tiers, and client roster

General flow

Tiers (define which cards the client sees)

Client roster (jun 2026)

Section 2 — Money metrics (recovery_metrics.py)

The 3 figures (exact definitions)

"Recovered" rule (crystal clear)

Sources and filter

New flags

Command and outputs

Section 3 — Insurance verifications

What counts

Value (ADA reference — updated)

Solstice (courtesy) — NEW

Section 4 — No-shows (two definitions per client)

Default (broken on the calendar)

A+ and similar: noshow_source = "unscheduled" — NEW (key)

Cut to TODAY (don't count the future) — jul-2026 fix

Section 5 — A+ Model (verifications-only) — NEW

JSON fields

Section 6 — A/R Recovery (unbilled procedures)

Section 7 — Dashboard widget (modular)

File and modularity

Layouts

Section 8 — WordPress embed (WPCode + Elementor)

PHP snippet (WPCode)

Cache-buster

Full-width gray

Publish steps in WordPress

1WPCode → edit snippet → paste new version → Update

2If Breeze installed, Purge All Cache

3Refresh with Cmd+Shift+R

4Client page: shortcode [brandacare_dashboard]

Section 9 — Auto deploy (GitHub Actions + Cloudways)

Components

Secrets (Settings → Secrets and variables → Actions)

Manual deploy (when code or widget changes)

Section 10 — Replicate to a new client

1Create clients/<id>.json

2Confirm EXACT name of OPS "Doc" column

3Share OPS with service account (reader)

4Run recovery_metrics with --debug

5Add client_id to CLIENTS in run_pipeline.py

6Create the CLIENT_<NAME> secret in GitHub

7Add client's email to $clients array

8git push → Run workflow → verify the dashboard

Widget v1 (frozen, reference)

Section 2 — Money metrics (`recovery_metrics.py`)

A+ and similar: `noshow_source = "unscheduled"` — NEW (key)

4Client page: shortcode `[brandacare_dashboard]`

1Create `clients/<id>.json`

4Run `recovery_metrics` with `--debug`

5Add `client_id` to `CLIENTS` in `run_pipeline.py`

6Create the `CLIENT_<NAME>` secret in GitHub

7Add client's email to `$clients` array

8`git push` → Run workflow → verify the dashboard