Methodology

1. Data sources

Acuity Scheduling = all appointments + redemption certificate codes. Nova = all sales transactions (subtotal pre-tax). Manual entries used only for notes/follow-ups (Phase 4).

2. Funnel model — 4 acquisition funnels + 1 bookkeeping bucket

• Purple = paid-ad lead. Two agencies operate inside: First Touch Beauty (❄️ snowflake in service name) and [IL] ([IL] prefix). See Item 15 for the per-agency split shown in the breakdown table.
• Blue = organic everything else (incl. organic $79.95 promo).
• Pink = free consultation / complimentary.
• Green = destination + conversion event only (BZ Facial Program + cert code, OR Collagen LED). Green is NOT an origin — a client whose first arrived appointment is Green-classified gets Walk-in Retail origin instead (see below).
• Walk-in Retail = bookkeeping bucket, not a marketed funnel. Triggers when EITHER (a) the client has no Acuity appointment matched by phone (walk-in product buyer, anonymous Nova row), OR (b) their first-ever arrived Acuity appointment is Green-classified (they walked in off the street and went straight to a package, no Pink/Purple/Blue acquisition step). See Item 13 for the full spec.

3. Classifier priority

Green → Pink → Purple → Blue. Order matters. Snowflake is the only definitive Purple signal — price / calendar / "Scheduled By" are not. Walk-in Retail is not assigned by the classifier — it's assigned by the origin rules in Item 4.

4. Origin lock

A client's origin funnel is locked at their first ARRIVED appointment (Label = "Arrived" AND not canceled). Forever immutable. Return visits are classified per appointment (a Purple-origin client returning for BZFP is 1 Green arrival in that month), but their cohort stays Purple. Special case: if the first arrived appointment is Green-classified, origin = Walk-in Retail (not Green). And if the client has no Acuity appointment at all, origin = Walk-in Retail by default.

5. Conversion = becoming Green

"Ever converted" = any Green appointment (booked or attended) OR any Nova hard sale ≥ $600. Purple/Blue/Pink first conversion attributes to the origin month. After a client's first hard sale, EVERY subsequent Nova row for that phone — hard, soft, promo, or refund — attributes to Green retention in its sale-month, regardless of their original funnel (locked rule 2026-05-21). Conversion rate can exceed 100% by design (Green retention adds new conversions in later months).

6. Sale tiers

Hard ≥ $600 (the conversion trigger) · Soft $100–$599.99 · Promo $60–$99.99 · Other < $60 · Refund < $0.

Supplementary "$100+" column (Purple only). The Per-Month Funnel Breakdown shows an extra $100+ column inside each Purple agency band (First Touch / [IL]). It counts Purple-origin leads who spent any amount ≥ $100 (Soft or Hard) or had a Green appointment — i.e. became a paying customer, not just a $79.95 promo-chaser. It is informational only and does NOT change the official Conv number (which stays "became a package client": Green / hard ≥ $600). $100+ is always ≥ Conv. Source: v_purple_paying_conversion (additive view; no official number is affected).

7. Revenue rules

Revenue per month per funnel uses the prior-hard-sale rule (see Item 5): 1st hard sale stays in origin funnel; every subsequent Nova row buckets to Green. AOV per funnel = funnel revenue ÷ funnel arrivals. Walk-in Retail sales (no Acuity match OR first arrived appointment was BZFP — see Item 13) count in Monthly Gross AND in the Walk-in Retail column. Subtotal (pre-tax) is the dollar field — not Total.

8. Refunds

Raw sum of negative Nova rows. Aurora refund-pairing rule is documented but NOT implemented (Roy's locked decision). To get the true net effect, look at the "Net" column in Monthly Gross.

9. Data parsing invariants

Excel serial dates decoded as UTC midnight (the Tracy Shappee fix). Nova rows missing Timestamp → hard error. Phone normalization = last 10 digits, non-digits stripped.

10. Financing

CareCredit is the ONLY financing partner. Never reference Cherry, Affirm, or Klarna.

11. Package status (Packages Watchlist + Rebook)

Green-only redemption: only Green-classified arrived appointments (BZFP + cert or Collagen LED) deplete a package. Purple/Blue/Pink visits don't count even if they share the cert code. Package size = leading integer parsed from Acuity's product_name ("5 Appointment Package" → 5). Completed when Acuity's uses_left = 0 (trusts Acuity over arithmetic — handles no-shows / late cancels) OR arrived ≥ package size. Delete a cert in Acuity → the row disappears from our database on the next 5-minute sync and the client falls off Watchlist + Rebook automatically. Appointment history is preserved in appointments for KPI continuity.

12. Sync cadence

Acuity appointments + certificates sync every 5 minutes via cron. Nova uploads are manual replace-all (each upload becomes the new source of truth; pre-wipe snapshot kept in Storage for 90 days). The dashboard subscribes to Supabase Realtime — changes on one computer appear on every other open dashboard within ~1 second.

13. Walk-in Retail

A client's origin funnel = Walk-in Retail if EITHER (a) they have NO Acuity appointment matched by phone (pure walk-in product buyer, anonymous Nova row, phone mismatch), OR (b) their first-ever arrived Acuity appointment is Green-classified (BZFP+cert or Collagen LED) — meaning they walked in off the street and went straight to a package with no Pink/Purple/Blue acquisition step.

Walk-in Retail is an origin bucket, not an appointment type: arrivals stay 0, but conversions and revenue are counted normally (per the prior-hard-sale rule in Item 5). Phone is the only matcher — name-fallback is intentionally not used. Green stops existing as an origin — it's now purely a destination + conversion event.

In the Per-Month Funnel Breakdown table, Walk-in Retail appears as a gray column group with 3 sub-cells (Conv · Gross · AOV) positioned between Green and Refund. The Arrivals sub-cell is omitted (always 0). Conv = unique phones with any positive Nova sale bucketed as walk_in_retail that month (anonymous rows excluded — different definition than the cohort-based Conv rule used for the 4 marketed funnels). AOV = monthly gross ÷ that same unique-buyer count. Both metrics reflect that Walk-in Retail is buyer-driven, not lead-driven.

14. No-Shows / Cancels win-back (Packages Watchlist → 🚫 sub-tab)

Purpose: let Roy + Brenna pull a clean list of clients who booked but never came in, so they can call them or send the SUMMER20 20%-off offer without accidentally discounting to existing package customers.

Source view: v_winback_no_show_cancel (migration 031). A client appears in the list when ALL of the following hold:

• Has at least one Acuity appointment with no-show or canceled status.
• Zero arrived appointments ever — strict "never came" rule. One arrival in the client's history disqualifies them permanently.
• Never held a certificate code — if any appointment in their history has a non-empty certificate_code, they're excluded (they bought a package at some point).
• Funnel of their most recent booked appointment is Blue or Purple (Pink/Green excluded by definition — Green requires arrival + cert).
• Phone is not present in the winback_dismissed table.

Dismissal: the "Remove" button per row inserts the phone into winback_dismissed so the client disappears from the list for everyone (shared state across machines). To bring someone back, delete their row from that table in Supabase.

Export: three buttons that act on the currently-filtered rows — Copy emails (comma-separated, BCC-ready), Copy names + emails (TSV for Sheets / mail-merge), Download CSV (9-column full export).

15. Purple agency split — First Touch vs [IL]

Purple funnel covers all paid-ad acquisition. Two agencies operate inside Purple: First Touch Beauty (services prefixed with the ❄️ snowflake) and [IL] (services prefixed with the literal [IL] tag). The classifier recognizes either signal as Purple (locked 2026-05-26). In the Per-Month Funnel Breakdown table, the Purple column group splits into two sub-groups (4 metrics each: Arr · Conv · Gross · AOV) so each agency's performance reads independently. Sum of (First Touch + [IL]) per metric per month = total Purple to the cent. A client's agency sub-tag is locked to the agency of their FIRST arrived Purple appointment. View: v_purple_agency_split (migration 033). No other funnel or metric affected.