How to Find Which Clients had the Most Treatments for a Time Period

  • 1
  • Question
  • Updated 4 years ago
  • Answered

Is there any way of determining the clients with the highest number of treatments for a period? We have a reward system for our top clients each year, and the Birthday List report allows us to establish which clients have spent the most with us for the past year. However, we provide a variety of treatment programs (usually involving multiple therapists), and would prefer to include clients in our rewards system based on treatment numbers rather than dollars spent. That way we exclude any new clients who may have just purchased a $2,000 treatment package and haven’t had any treatments at all.

Photo of Peter Cent Nat Ther Clinic

Peter Cent Nat Ther Clinic

  • 11 Posts
  • 9 Reply Likes

Posted 4 years ago

  • 1
Photo of Andrew

Andrew, Community Manager

  • 375 Posts
  • 114 Reply Likes

The Billings Report can be used to summarise the number of transactions per item code for each practitioner, however not per patient directly.

The closest report is the Billings Report viewed By Patient, which will display all the patients who have had a transaction within a period and list those transactions.

It won't provide you with an exact count, but it can help you to see at a glance who has had more transactions in that period.

If you are comfortable with Microsoft Excel, you could also Export this report to Excel and use formulas to count the duplicate names.

I would achieve this by adding two additional columns.
One to combine the Firstname and Surname into a single entry (Full Name), and a second to Count the number of times that combined name is listed.

Please note this does require an understanding of some more advanced Excel functions, and while I have provided a method below this would be considered outside the scope of our support. If you need help with your Excel formulas or using Excel in general I would recommend a suitable online forum dedicated to Microsoft products. Please be mindful if you are posting online not to publish any of your patient data or other confidential information.

1) Export the report to Excel, and add the column titles Full Name and Count as below.

2) In the Full Name column [P], add the formula =B2&C2 and expand down to the end of the column.  This gives us a full name column to check duplicates against.

3) In the Count column [Q], add the formula =COUNTIF(P:P,P2) and again expand down.
This counts the number of times the full name is found in the previous [P] column.

4) Highlight the Count column and go to Sort & Filter to sort by the highest or lowest count. Be sure to Expand the Selection when prompted, which will re-order all columns in-line.

5) Complete. We now have a list of patients who have had a transaction within a period, sorted by number of transactions.

I hope this helps.
Photo of Peter Cent Nat Ther Clinic

Peter Cent Nat Ther Clinic

  • 11 Posts
  • 9 Reply Likes
Thank you. We have been exporting the Transaction Report (under Banking Sheets), ordering by item code, and deleting all the product sales, so using the Billings Report is simpler. Thanks also for the helpful Excel instructions. It is a shame there isn't an easier way to get this information.