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.

Data_Devon

Wow!! Thanks a ton for all that. I think we are super close but I must've missed something somewhere.

You can see my raw data at far left. Then, I did a "Group By" per your instruction. The output was all the non-surgical appointments. I then joined this back to the original dataset because I will need the actually surgeries so I can compare to the whole.

Along the bottom line of the ETL, I filtered down to "Surgical Patients" as you instructed, added the constants, and then joined it back up with the original dataset.

Finally, I added the IFNULL()

Unfortunately, there were duplicates created somewhere in my joins, so the output dataset no longer matches the original dataset. I.e.:

Count(Surgeries) != Count(Surgeries)

Any ideas??

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.

Data_Devon

Wow!! Thanks a ton for all that. I think we are super close but I must've missed something somewhere.

You can see my raw data at far left. Then, I did a "Group By" per your instruction. The output was all the non-surgical appointments. I then joined this back to the original dataset because I will need the actually surgeries so I can compare to the whole.

Along the bottom line of the ETL, I filtered down to "Surgical Patients" as you instructed, added the constants, and then joined it back up with the original dataset.

Finally, I added the IFNULL()

Unfortunately, there were duplicates created somewhere in my joins, so the output dataset no longer matches the original dataset. I.e.:

Count(Surgeries) != Count(Surgeries)

Any ideas??

Data_Devon

I just had to add additional join keys on the final join to alleviate the duplications that were happening.

This is good to go! Thanks a ton @ColemenWilson for your help.