Problem: How would a clinic know it’s online scheduling stats?

Discussion: The best way to figure out this information is to extract this information from OSCAR directly, as this facilitates the incorporation of data not found within Veribook.

We recommend using OSCAR’s Query By Example tool. You can then run any of the following queries to see your corresponding data, and copy and paste the data into Excel to perform additional visualizations as required.

If there’s any questions, or if you’d like help extracting information other than the example provided below, contact us!

Clinic Wide: Using the following query supplies the monthly billed appointments booked online vs. manually, for the last 12 months, along with the monthly conversion rate.

SELECT  CONCAT(YEAR(appointment_date),"-",LPAD(MONTH(appointment_date),2,"0")) AS "Month", SUM(CASE WHEN (notes LIKE "%veribook%") THEN 1 ELSE 0 END) AS "Appts Booked Online", SUM(CASE WHEN (notes LIKE "%veribook%") THEN 0 ELSE 1 END) AS "Appts Booked Manually", CONCAT(ROUND((SUM(CASE WHEN (notes LIKE "%veribook%") THEN 1 ELSE 0 END) / COUNT(*))*100,0),"%") AS "Conversion Rate" FROM appointment  WHERE LEFT(STATUS,1) = "B" AND appointment_date >= CURDATE() - INTERVAL 12 MONTH  GROUP BY YEAR(appointment_date), MONTH(appointment_date);

Per Provider: Using the following query supplies the monthly billed appointments per provider booked online vs. manually, for the last 12 months, along with the monthly conversion rate.

SELECT  CONCAT(YEAR(appointment_date),"-",LPAD(MONTH(appointment_date),2,"0")) AS "Month", concat(provider.last_name,", ",provider.first_name) as name, SUM(CASE WHEN (notes LIKE "%veribook%") THEN 1 ELSE 0 END) AS "Appts Booked Online", SUM(CASE WHEN (notes LIKE "%veribook%") THEN 0 ELSE 1 END) AS "Appts Booked Manually", CONCAT(ROUND((SUM(CASE WHEN (notes LIKE "%veribook%") THEN 1 ELSE 0 END) / COUNT(*))*100,0),"%") AS "Conversion Rate" FROM appointment join provider on appointment.provider_no = provider.provider_no WHERE LEFT(appointment.STATUS,1) = "B" AND appointment_date >= CURDATE() - INTERVAL 12 MONTH  GROUP BY YEAR(appointment_date), MONTH(appointment_date),concat(provider.last_name,", ",provider.first_name) order by name,month;