The goal is to build a dashboard that connects every new patient lead to the marketing source that generated them, then follows that patient through their actual activity in the dental office so we can calculate true marketing ROI.



We receive leads from multiple sources, including:



Google Ads
Meta/Facebook/Instagram Ads
CallRail phone numbers
Website forms
Organic Google
Direct/referral sources
Other campaigns such as print or mailers that may use dedicated CallRail numbers



The system needs to capture each lead and identify the original source, campaign, ad, keyword, phone number, or landing page whenever that information is available.



Then, that lead needs to be matched to the patient record inside Oryx.



Once matched, the system should continuously pull updated patient data from Oryx, including:



Whether the lead scheduled an appointment
Appointment date
Whether they actually showed up
Whether they cancelled or no-showed
Treatment presented
Treatment accepted, if available
Production
Collections / actual money paid
Total lifetime value of the patient
Future collections as they continue treatment



The most important part is that this information should remain linked to the patient's original marketing source.



For example:



Patient calls a CallRail number assigned to a Google Ads campaign → schedules → shows up → accepts treatment → pays $8,500.



The dashboard should attribute that $8,500 back to the correct Google Ads campaign and calculate the ROI automatically.



We should be able to see this at multiple levels:



Source → Campaign → Ad → Lead → Patient → Appointment → Revenue



The dashboard should show metrics such as:



Marketing spend
Number of leads
Cost per lead
Number of appointments scheduled
Lead-to-appointment conversion rate
Number of patients who showed up
Show rate
Number of new patients
Cost per acquired patient
Treatment presented
Treatment accepted
Collections generated
Average revenue per patient
Lifetime patient value
ROAS
True marketing ROI



For example:



Google Ads Spend: $5,000
Leads: 100
Scheduled: 40
Showed: 30
Became Patients: 25
Collections Generated: $40,000



Cost per Lead = $50
Cost per Acquired Patient = $200
ROAS = 8x



The dashboard should also allow us to compare Google Ads vs Meta vs organic vs CallRail campaigns vs print/mail campaigns so we can determine which marketing channels are actually producing patients and revenue—not just clicks or leads.



The data should update automatically rather than requiring us to manually enter revenue.



The main technical challenge will be patient matching. Ideally, leads should be matched to Oryx patients using identifiers such as:



Phone number
Email address
Name
Appointment information



We also need to make sure the system avoids duplicate patients or duplicate revenue attribution when the same person contacts us through multiple sources.



Ultimately, I want to be able to open one dashboard and answer:



"Where did this patient come from?"



"Did they schedule?"



"Did they actually show up?"



"How much money have they spent with us?"



"How much did we spend to acquire them?"



"What is the actual ROI of that campaign?"

let me know which items you need access for and i'll try to make it happen

on oryx you can find out which patient came from which advertising channel (says reffered by in the profile section)



-----------------------------------------------------------------------------------------------





I want to build one simple marketing ROI dashboard for the practice.



The basic idea is:



Marketing Source → Lead → Appointment → Showed Up → Became Patient → Revenue Collected



We need to pull data from:



Google Ads — spend, campaign, clicks/leads
Meta Ads — spend, campaign, leads
CallRail — phone calls and which marketing source/campaign generated the call
Website/forms — form submissions and their source
Oryx — patient name/contact info, appointment status, whether they showed up, treatment accepted, production, and ideally actual collections



The most important part is matching the marketing lead to the patient in Oryx, preferably using phone number and/or email.



For every source/campaign, I want the dashboard to automatically show:



Ad Spend → Leads → Scheduled → Showed → New Patients → Treatment Accepted → Production → Collections → ROI



For example:



Google Ads
Spend: $5,000
Leads: 80
Scheduled: 35
Showed: 25
New Patients: 20
Treatment Accepted: $40,000
Actual Collections: $25,000
ROI = ($25,000 - $5,000) / $5,000 = 400%



I also want to see:



Cost per lead
Cost per scheduled patient
Cost per patient who actually showed
Cost to acquire a new patient
Lead → appointment conversion %
Appointment → show %
Show → treatment acceptance %
Average revenue/collections per acquired patient
ROAS (Collections ÷ Ad Spend)
ROI ((Collections − Marketing Cost) ÷ Marketing Cost)



I want to be able to filter this by date, marketing source, campaign, and ideally service type (implants, All-on-X, veneers, general dentistry, etc.).



Most important: I don't want a dashboard that just tells me how many leads Google or Meta generated. I want to know which marketing dollars resulted in real patients who showed up and actually spent money in our office.



Ultimately I should be able to open one screen and immediately answer:



“We spent $X on this campaign, it produced X leads, X showed up, X became patients, they generated $X in collections, and our ROI was X%.”