Reporting Application / Dashboard

# Build: Multi-client reporting app / dashboard for a dental marketing agency ## Context We are an agency doing paid ads and CRM for private dental practices. I need one app that pulls data from several tools, matches each lead through to treatment, and shows each client their full funnel from ad spend to revenue. ## Data sources 1. **GoHighLevel (GHL)** – main entry point for every lead. Contact created date, source, UTM / campaign, treatment interest, pipeline stage. One sub-account per client. 2. **Dentally** – practice management system. Consultation appointments (booked, attended, FTA, cancelled), payments, and where available, patient communications / notes and treatment plans. 3. **Pipedrive** – some clients track treatment coordinator deals here (deal won = gone ahead, deal value = plan value). 4. **Google Sheets** – manual inputs and overrides (e.g. marking a patient as gone ahead, adding plan value, clients with no API access, corrections). 5. **Meta Ads + Google Ads** – ad spend by campaign. Not every client uses every source. Each client needs a settings page where I choose which source is the "source of truth" for each metric. ## Lead matching (critical) - Match a GHL lead to a Dentally patient / Pipedrive deal by email and phone number. - Normalise UK phone numbers (07…, +447…, 447…) before matching. - Show a list of unmatched records so I can match them manually. ## "Gone Ahead" logic (important) Dentally has no clear "gone ahead" status, so detect it in this order, configurable per client: 1. **Payment-based (default, all clients)** - A matched lead is Gone Ahead when they make their first qualifying payment after their consultation. - Exclude consultation fees and routine items (check-ups, hygiene) — make the excluded payment types / minimum amount configurable per client. 2. **Message-based (optional, e.g. Leicestershire Dental Studio)** - Some practices send a message into Dentally saying the patient has gone ahead plus the final treatment sum. - Detect these by keyword / template match per client, mark the patient Gone Ahead on that date and store the stated sum as Plan Value. - Confirm whether the Dentally API exposes these messages / notes before building. 3. **Treatment plans (optional, if the API allows)** - Read accepted treatment plans for Plan Value where available. 4. **Manual override** – Sheets or in-app edit always wins. Show which method flagged each Gone Ahead so I can audit it. ## Metrics (starting point) | Metric | Definition | |---|---| | Leads | New unique contacts created in GHL in the period (deduplicated) | | Cons Booked | Leads with a consultation appointment booked | | Book Rate | Cons Booked ÷ Leads | | Cons Att | Consultations marked attended (exclude FTA / cancelled) | | Gone Ahead | Matched leads flagged Gone Ahead using the logic above | | Revenue | Cash collected from Gone Ahead patients (£), updating as they pay | | Avg Sale | Revenue ÷ Gone Ahead | | Conversion to Sale | Gone Ahead ÷ Cons Att | | Ad Spend | Meta + Google spend in the period (£) | | ROAS | Revenue ÷ Ad Spend | | Plan Value (where known) | Total treatment value from messages, plans or Pipedrive — shown separately, never mixed into Revenue | Build metrics so I can add more later (e.g. cost per lead, cost per acquisition, FTA rate, show rate). ## Reporting logic - Toggle between: - **Cohort view** – all outcomes and revenue tied back to the date the lead came in (revenue for a cohort keeps growing as patients pay instalments) - **Activity view** – each event and payment counted on the date it happened - Default to cohort view. - Per-client mapping for: which Dentally appointment types count as a consultation, which statuses mean "booked" and "attended", which payment types qualify for Gone Ahead. ## Dashboard features - Agency view: all clients side by side, sortable by any metric. - Client view: KPI cards + funnel chart (Leads → Booked → Attended → Gone Ahead). - Filters: date range, lead source (Meta / Google / organic / other), campaign, treatment type (Invisalign, implants, composite bonding, whitening, etc.). - Compare to previous period (% change on each card). - Trend chart by week / month. - Read-only client logins that only see their own practice. - "Last synced" timestamp per source; auto-sync at least daily, manual refresh button. - Export to PDF / CSV for monthly client reports. ## Compliance - UK GDPR: this touches patient data. Store the minimum personal data needed for matching, host in the UK/EU, encrypt tokens and data at rest. - Client-facing views show aggregated numbers only, no patient names. ## Delivery - Phase 1: one client ([CLIENT NAME]), GHL + Dentally + Pipedrive, all metrics working. - Phase 2: Meta Ads, Google Ads, Sheets, message-based detection, multi-client agency view, client logins. - Before building, list any API access or permissions you need from me (GHL private integration token / OAuth, Dentally API access, Pipedrive token, ad account access), and flag any metric you can't calculate from the available data. Suggested stack is flexible — recommend one and explain why. My preferred tools: Lovable + Supabase / Looker Studio