Tagging Customers on Services

Data_Devon

Hi Community, excited to see what you think up!

BLUF: How can I analyze what nurse saw a patient prior to a surgery or a non-surgery?

See my simplified dataset below. My hope is to be able to see which nurses saw which patients/customers that led to a surgery (or not).

1. My current thought is a CASE WHEN to create a label for "surgical patients", but this doesn't capture pre-operative testing and evaluations. For instance:

CASE

WHEN Treatment = 'Surgery' THEN 'Surgical Patient'

ELSE 'Non-surgical Patient'

This logic missed the Consult, the testing, the evals, etc. that lead up to the surgery.

2. If I take all Patients that had a surgery and then do a large "CASE WHEN" on them, then I get duplicates downstream, which also isn't ideal. For instance:

CASE

WHEN Patient = 1, 2, OR 4 THEN 'Surgical Patient'

ELSE 'Non surgical patient'

This fails because then it shows FIVE "Surgical Patients" for Patient 1, even though only 1 surgery was performed.

I feel as though I'm very close but just need someone to get me across the finish line 😊🏁🏁

ColemenWilson

Here is the solution to your BLUF:
1. Use Group By tile in Magic ETL and the following formula: GROUP_CONCAT(DISTINCT CASE WHEN Treatment IN('Consult','Testing','Evals','Preop') THEN Name END)

You don't have a nurse identifier in your sample data so I added one called "Name"

Results:

This will show which nurses saw each patient. Let me know if this isn't what you're looking for.

For the patient count piece, you need to use COUNT(DISTINCT ) with a CASE statement:

COUNT(DISTINCT CASE WHEN Treatment IN('Consult','Testing','Evals','Preop') THEN Patient END)

For analyzing which nurses saw patients that led to surgery vs. no surgery, you could add a flag to every patient that led to surgery in Magic ETL as shown below:

Then create some kind of card in analyzer using this beastmode:
COUNT(DISTINCT CASE WHEN Had Surgery = 'true' THEN Patient END) / COUNT(DISTINCT Patient)

Result:

75% of patients John saw ended up in surgery, 67% of patients Jacob saw ended up in surgery.