by Ben Rogojan

Automation isn’t always about automated cars and drones that will deliver our purchases to our doorsteps. The goal of automation is to make people’s lives easier and not have them come in on work on Saturdays. There is a lot of tasks in the workplace that still can be automated to avoid having late nights and long weekends stuck at work. This doesn’t mean that automation in itself is easy to implement. Developing automated systems is a discipline, it requires patience, robust design and clearly thinking through the process.
​
There are good practices that can be overlooked when implementing an automated systems that can create a lot of tech debt. This will weigh down your system and possibly just waste the time for future developers who either have maintain or recreate the system. In order to avoid this an automated system should have solid error tracking, QA and be easy to maintain. Here are a few great tips for developing an automated system.

Error And Logging Tracking
Logging and error tracking does not come across as exciting work. Even developing basic tracking functionality can seem tedious. However, it is a necessary step for developing an automated system. Logging and error tracking can take place in several layers.

The simplest form is to track in raw files everything that occurs. This has the least overhead because it doesn’t need to be stored in a specific data system. This allows for low-level maintenance and future data parsing if required. Creating standardized logs allows for large amounts of unstructured data to be stored outside of a database and with modern technology and data storage systems searching through these files is much faster.
Having higher level logs with less information stored in standard data warehouse allows for tracking of issues and unhealthy systems. This can be used to detect issues such as slow load times, persistent bugs, etc. Unlike unstructured data, storing high-level issues in a data warehouse allows for easy views into trends and allows future analysis.

Especially as more complex automated systems come to fruition, being able to track and analyze errors and trends in usage will be a game changer. Whether this is for automated cars, e-commerce, and the world of IOT devices. The only way to manage the sheer volume of transactions and communication protocols between the various systems will be with automation. Error tracking and logging can’t be an afterthought in this automated systems. Otherwise, we will never be able to manage the holes in the systems we create.

QA And Alerting
Error logging is for issues that occur after release. The hope is we squash as many bugs before they occur. The problem is that automation leaves a lot of trust in the programmers and developers that set up the systems. Even trying to think through all the possible ways automated systems can go wrong, there are always failures and making sure to develop a QA suite that sweeps through the data and logs to make sure whatever automated processes are occurring, are occurring correctly.
​
There are basic QA tests that just make sure systems are running and then there are more complex systems that can take more of a machine learning approach. Looking for patterns and data profiles that could speak to process issues. For instance, sometimes null values are normal but sometimes they occur because of a specific issue. This could be because of an outage in a particular region, an update that works well on one OS and not another, and various other issues that might not be detected by standard integration and unit tests.

Thus, going more in depth and developing a QA system that can do basic tests just to ensure systems are functioning as well as test for more complex issues. Again, this is somewhere we would agree in implementing a form of a machine learning/data science system that can profile data to detect non-specific issues.

Simple Vs. Complex

One of the reasons for this comic above is the fact that automation, when poorly designed quickly, becomes a nightmare. Anti-patterns, over customizability, and poor design in general makes code difficult to maintain. This will take more time to manage instead of saving time. Instead of dealing with a hands off system you are managing a tech debt-ridden monstrosity that sucks up developer time.
​
If anyone recalls the Rube Goldberg machines we developed in middle school or elementary school you will recall the fact that in order to do simple tasks you over complicated and designed some system. This is a great example of some automated systems. In some cases, the task you might have been attempting to automate could have been done simpler or the system you developed is just too complex. Over-engineering can be a serious problem that makes it very difficult for future engineers to maintain. Over-abstracting code, developing too many functions, too many plausible paths can make code unmaintainable and untestable.

This often won’t be apparent until the developer that wrote the code leaves and the other programmers need to pick up where they left off.

This means good design, that doesn’t try to be too smart and over-engineered. Most professions suffer from this issue. Chefs try to put too much on one dish or an architects try to put too many features into a building. At the end of the day, the best Chefs, Architects and engineers understand that sometimes simple exceeds elegant. This doesn’t mean that concepts like abstraction and object-oriented are bad. It’s just that some developers place these concepts over the maintainable code. There is always a balance.

Dependency Management
Automated systems and processes have a lot of moving parts. This means at some point some components will need to occur in sequence and some will need to wait until multiple dependencies have completed in order to ensure the safety and health of the system. As more and more automated systems get developed to interact with people, beyond just robot arms behind safety walls and yellow tape, their systems will have to have multiple dependencies to ensure that the tasks they are performing are both correct and safe.

No matter if the system is interacting with people or if it is operating in the confines of a computer, having simplistic dependency management that allows for the developers to easily draw the paths makes it much easier to ensure that the system won’t run when it shouldn’t.

Automation does make life simpler when it is developed purposefully and fully thought through. This means planning how the error will be tracked and how the system will manage and alert larger problems. Designing a robust and maintainable system is not easy. However, when correctly implemented it can improve your team’s productivity and simplify your life.

If instead, your automation project goes a bit off the rails, then please feel free to contact our team!

Interested In Reading More About Being A Better Data Scientist?
How To Grow As A Data Scientist
Boosting Bagging And Building Better Algorithms
How To Survive Corporate Politics As A Data Scientist
8 Top Python Libraries For Machine Learning
What Is A Decision Tree
4 Ways To Calculate A Rolling Sum With SQL

The ubiquity of data makes SQL one of the most utilized technical skills whether you are a data engineer, analyst, or data scientist.

Even as companies that rely on larger data sets that are in data systems and tools like Hadoop, Hive, and Presto. SQL remains an important. It is one of the few “languages” you can learn once and only have to make minor syntax changes as you switch data storage systems. SQL can seem limited when you first start out because you haven’t approached a diverse set of questions. Once you get past JOINs, Loops and some other basic concepts in SQL like subqueries analytic functions, and aggregation, it can seem like you understand everything you will need to know.

In an odd way, you are right. You know all the basic building blocks to do a lot of complex work. But just because you know the building blocks doesn’t mean you know all the applications. It is like saying you understand addition, multiplication, division, and subtraction thus you must also understand how algebra works.

Algebra uses all the basic concepts but applies them in a new way. In the same way, basic SQL concepts can be applied to complex problems, but it doesn’t mean you know how.
How these problems are solved isn’t always consistent. How you may answer a question in Oracle and Mysql could be different in Redshift or SQL Server. These problems can be analytical, performance and optimization or even design.

Just because you are using many of the same commands does not mean you are using the same approach.

Let’s talk about some of these problems.

Analytic Functions
Before we start, we wanted to talk about a useful tool to help solve a lot of problems.

These are called analytic functions. These might not be the first things you learn, but once you do, you will probably start using them everywhere. If you don’t already have a decent grasp of them then we recommend you get a quick intro.

One quick note: Not all SQL engines have analytic functions built in so you will have to use work arounds. We will discuss a few below.

Calculating a rolling sumLet’s say you want to calculate a rolling sum for the profits of a food truck on a daily basis. For now we won’t complicate it by adding in month or year. Simply a rolling sum for all time.

How would you do that with SQL? Any ideas?

Well, there is actually a really simple way to do this with analytic functions.


Select
    SUM(Profit) over (partition by food_truck_id order by day)  
   ,food_truck_id ,day  
FROM f_daily_sales

That is it…you’re done!
Wow! That was really simple. Using this quick script you were able to calculate the rolling sum for all the food trucks. That was almost too easy!

Now, in case you don’t know, using this style of “function() over (partition by x order by y) “ clause is what is known as either an analytic or window function. It allows the end user to do something similar to a group by, but instead of grouping under a specific set of fields, it partitions by that set of fields and then applies a function using the order by. This function can be a row_number, rank, sum, avg, etc. All of these functions essentially work in a rolling fashion based on the order by statement. Row_number will add a row number for the group of fields and restart at one once the group of fields is different.

As we said, analytic functions are very useful. So useful that sometimes interviewers don’t like you using them. So let’s say you’re in an interview and the interviewer says that they want you to resolve this problem without the analytic function…

Did your stomach just drop? Maybe this was the only way you knew how to solve this problem. Take a moment and think about how you could solve this problem with the basic SQL tools you have. Think about what the SQL statement is actually asking.

SELECT
   day ,SUM(Profit)
FROM  
      (
      Select  
           t1.day
         ,t2.Profit
     FROM f_daily_sales t1
     JOIN f_daily_sales t2
   On t1.day >=t2.day
   ) t3
Group by
   day
​
In this case you can use the self join with a greater than or equal to statement creates a table like the one below

As you can see you will be able to group on the t1.day field and get a rolling sum effect. One of the major downsides depends on the amount of data. Because depending on how the data is structured and how far back you would like to go, this method could cause a system to run out of memory.
If by any chance this is a problem. Here is another thought. You could create a summary table that basically aggregates the total per day.

This depends on you are partitioning the rolling sum. For instance, this will work well as long as you know you will constantly be reporting the rolling sum on a monthly and yearly basis. This would lock your design in, but add both speed and a reduction of the computer when you actually query. Again this is more of a design question. What is the right answer depending on your overall goals?

Joining consecutive events
Another question you might be asked is to provide insight into consecutive events for patients, customers, or other entities. For instance, you might want to calculate the number of days between the time a customer visits a store, or your website. Maybe you want a simple flag that dictates whether or not a patient is readmitted in x days after each their initial visit. Well at a basic level, these have a similar goal.

You need to know how many days are between two different transactions.

Most transactions are lined up row by row, so you can’t simply go to the next row to figure out this information.
This is where an analytic function can work again. Using the row number function you are able to partition the data by each customer and patient and add a row number to each event. What would like below?

Using this as your base data you can self join this table to the next row using a +1 on the row number row to join on the following row.

Select  
    Customer_ID
   ,row_number() over (partition by Customer_ID order by date) r_num  
   ,date ,total_paid
Into #Customer_Transactions  
From Customer_Transactions


Select
     t1.Customer_ID
    ,datediff(dd,t1.date ,t2.date) --this will calculate  the difference in days
From #Customer_Transactions t1
Join #Customer_Transactions t2
on t1.Customer_ID = t2.Customer_ID
​And t1.r_num = t2.r_num+1

With this self-joined the table and each row can be looked at side by side you can tell more information. You can answer questions like, how many days were in between each event on average, or how many times did customers return in less than 15 days.

This can be a great starting point into some analysis focused on retention or policy improvement. You can start to see where your problem areas are, and possibly notice trends between stores or doctors based on they perform.

Another approach that some people like to take is looping through the data. This can get a little more complex and is usually slower, but it is another solution we won’t go over in this post.

These were two examples of solving SQL problems. We will continue to post various problems and different ways you can solve them. We might even re-approach the same problems above. That is what is great about SQL, depending on how your data is laid out and your limitations force you to use different solutions. You could have worked somewhere with SQL Server and get accustomed to temporary tables that allow you to put indexes on them. Then you go to the database that no longer allows temp tables and your performance goes way down.

We would love to come and help increase your teams analytical and SQL abilities. Please feel free to reach out! Also, please let us know if you have any questions or problems you are working through. We would love to poke around on new problems!

Interested In Reading More About Being A Better Data Scientist?
How To Grow As A Data Scientist
Boosting Bagging And Building Better Algorithms
How To Survive Corporate Politics As A Data Scientist
8 Top Python Libraries For Machine Learning
What Is A Decision Tree

During our years working for hospitals and insurance providers, there were many similar issues that caused unnecessarily increased costs. Healthcare providers, like many other industries, are facing drastically increased costsand decreased margins. Unlike tech companies that had the ability to develop technology to help their basic operations automate and scale, hospitals and healthcare providers have not. This has caused multiple administrative and analytical costs to grow year over year because of the lack of automation and process improvement. Some of the biggest costs that we have seen heavily weigh on healthcare providers are billing and financial processes, fraud detection and third-party contract management. Automation and process improvements are needed in these three areas if healthcare providers want to start reducing some of their biggest costs.


Billing And Financial Analysis
Managing billing and financial analysis can be very tedious tasks that require a combination of accounting discipline and entrepreneurial spirit. They are necessary practices that need to happen to manage expenses and revenue.

The problem is that even in billion dollar healthcare organizations lack automated billing and financial processes. Instead, hundreds of thousands to millions of dollars are often manually managed in excel spreadsheets. The processes to get the data and then slice and dice wastes analysts time. There are lots of ways that these financial tasks can start to weigh on the financial teams as they are not scalable. As hospitals and insurance providers merge and grow the problem amplifies and becomes even more difficult to manually manage. Typically, hospitals will approach this problem by increasing their staff rather than create a process or system that can continue to scale basic financial analytics and billing. This leads to increased operational costs that are difficult to manage without laying off employees(this isn’t even considering the costs of hiring, firing, rehiring…).

The solution is automation(mic drop….).

Okay, automation is easier said than done.

It requires a combination of buy-in from multiple stakeholders to financially back the projects and trust from directors and manager who read the reports and output of these automated systems. However, when executed well, these systems can reduce hundreds of hours of manual work.
​
It seems complicated, but often times automation, when done well really isn’t. Many times it actually simplifies the overall workload on your analysts. There might need to be some upskilling in basic SQL. However, we are living in a data driven world and SQL is the data language(even NOSQL databases have SQL layers because that is what we humans understand).

If your team doesn’t have that the skill set to build the tools themselves, then find it. Automation consultants and engineers can be found both internally and externally. Once the main systems are built it can be maintained by people with a lesser knowledge in programming and automation. Automation will help provide consistency, reduce waste and allow your analysts to focus on more important work.

Fraud Detection
Fraud detection and adjudication are necessary practices of insurance providers. This is because healthcare fraud costs billions of a dollars a year. it comes from patients and also healthcare providers that charge millions of dollars of upcoded and wasteful procedure purely to bolster their bottom line. In fact, there are even consultants and websites that specialize in helping healthcare providers bill creatively and max out their claims. These practices don’t increase costs for the insurance provider, they increase cost for the patients at the end of the day.

Insurance providers have to pay analysts to spend the time to manually go through claims to look for possible patterns of abuse. If you notice, the same problem in billing is occurring here. The problem is the manual step. Healthcare billing and claims processing have gotten too big to effectively handle manually. The solution of manually processing claims just don’t scale. This is where the tools of automation, big data, data warehousing, and analytics work well. They allow a specialist to create systems to munge the data effectively and scale even as the data grows.

Currently, most insurance providers have teams for fraud detection but they often can only go through a small percentage of claims( even billion dollar companies usually only go through about 5–10% of claims manually).

Sometimes they will even hire outside firms to again manually go through and look for low hanging easy fruit. For instance, billing consultants found that 78% of 99215 codes in Wisconsin (highest level established patient office visit) were incorrectly used. This is a very easy issue to spot and it becomes very expensive very fast.
​
All of this is usually limited to manual processes. Getting data pulls from databases into excel and then slicing and dicing the output. The beauty of automated methods is that they can quickly reduce the number of claims required to manually process.

​

What is even better, once you have created a system, you can replicate the results at a regular cadence. Even as analysts leave and new hires take their place, it is much easier to train them to understand the results of a system that works rather than have them have to relearn what to look for in the raw data. This increases the number of claims an insurance provider adjudicates while at the same time improving the efficiency of the process.

Many think you need complex machine learning algorithms to detect fraud when you first start developing models and your data isn’t even classified yet. However, when starting out, it is important to focus on cutting the number of targets claims your analysts will look at by 60,70 or 80%. This doesn’t take a complex algorithm. It requires developing basic business rules that can help sort through the false positives.

After properly managing claims and tracking which ones are fraud and which aren’t then it is easier to develop a machine learning model because your data is classified(fraud, not fraud).

Fraud detection and adjudication is a slow and costly process for many healthcare providers. Automation and well-executed analytics offer thousands to hundreds of thousands of dollars of saving.

Third Parties And Contract Management
One of the ways to get some systems automated and integrated is to use third-parties. Hospitals, insurance providers and other healthcare institutions are not tech companies. They don’t focus on developing technical tools to help in their day to day operations and this is ok! What is not ok is constantly signing contracts with new third parties for redundant features. For instance, paying for multiple data visualization tools is pointless like Tableau, Quilk, and OBIEE all provide report features and yet some companies use all three and more. Similar things can be said about relying on multiple financial systems.

Having redundant third-party contracts causes several issues. One, and most obvious is uncontrolled operational costs. Besides the upfront costs of signing multiple contracts it also requires more employee to manage the systems, manage the contracts and deal with the billing. These contracts are also difficult to break without paying a hefty fee for breaking the contract early. Thus, it is very important to reduce redundancy because the costs will be more than the cost of the contract.

The second issues are that these systems don’t have easy ways to access the data behind them. Lacking access to your own data is a problem. It is difficult to make good financial decisions if you don’t know what is happening in your institution. When you rely on third-party software, you need to be aware of the terms of your contracts and the features of the software. Otherwise, your expenses will grow from nowhere and you won’t be able to make good decisions.
​
Administrative costs like billing, fraud detection, and contract management are driving up health care costs for insurance providers, healthcare systems and patients. With a little bit of automation and process management, many of these costs can start to be mitigated. Automating processes like billing and financial analysis can be done with a combination of SQL and python. Contract management requires a combination of process improvement and analysis of the previous contracts. In the end, it can all lead to scalable cost savings that can occur systematically and don’t constantly need manual intervention.

Does your team need help automating your data systems and analytics? Then please contact us today!

Read more about data science, healthcare analytics and automation below:
How To Use R To Develop Predictive Models
Web scraping With Google Sheets
What is A Decision Tree
​How Algorithms Can Become Unethical and Biased
How To Develop Robust Algorithms
4 Must Have Skills For Data Scientists

​In 2018 it is estimated the US spent 3.5 trillion dollars on healthcare. A cost that is growing at nearly 5% a year. Rapidly growing pharmaceutical and administrative costs are just some of the driving factors behind this growth.

​This leaves the question, how are we going to reduce healthcare costs? We can’t ignore the problem. Otherwise, we will be spending way more than $15 for a Tylenol in 20 years.

The first part starts with recognizing some of the largest problems in healthcare. We pointed these out in an infographic here(Fraud, waste, and administrative costs). There are nearly a trillion dollars of spending that could be improved if the correct processes and policies are put into place. However, simply knowing what the problems are is not enough. We need to know where and why they are occurring. This brings us to our first step in starting a healthcare analytics project.
​

​Develop Accessible and Accurate Data Systems
In order to find out where the problems are, you need data that accurately represents what is occurring in your hospital/healthcare system. Relying purely on anecdotal evidence is no longer sufficient. It’s great to start and can guide data scientists and engineers to develop to correct data warehouses and dashboards. All of which requires quality data that is accessible to the correct users.

Developing a data system (ETLs, data warehouses, etc) that is both accurate and is accessible to the right people is a necessary step in confirming what the biggest cost-saving opportunities are.
This means thinking about what data you have, what data you need, who needs to access it and what questions are you hoping to answer. Not all of these can be answered right away. So it would be unwise to start by developing data warehouses that heavily adulterated from their original formats. That is why most data teams develop systems with the same basic pattern(example below).

You start with raw flat files that get ingested into a raw database. That data is run through a QA process just to double check that the data is clean and makes sense. One the data is QAed it is processed into a set of staging tables with minimal business logic added. Typically, this is more of a mapping phase. Anything that needs to be standardized is, data that is duplicated is cleaned up and data normalization may occur. The goal here is to limit the amount of business logic and focus more on creating an accurate picture of what the data represents. Whether this is procedures done on patients, supplies used by a hospital or computers and devices used by employees. This data is just an abstract depiction of what is occurring at the healthcare provider.

Once the data is loaded successfully and again QAed (yes, QA should happen any time data has an opportunity to be adulterated) then it can be loaded into the tables that everyone that should have access to them can have access to them.

This base data layer will provide your data scientist the ability to create analytical layers that sit on top and populate various aggregate and metric tables.
​
This basic outline is tried and true. More than likely, your data warehouse team is already supporting a system similar to the one we described. Typically the problem lies in accessibility and silos. The data might be produced for various teams like finance, or IT. However, they might all exist in separate systems with different keys and structures. This makes it difficult to find value.

At this point, there needs to be a much larger initiative to provide the correct accessibility as well as clear connections between the data systems. This is why using different keys for the same data across systems is a bad idea. Going into this would require a whole other set of blog posts and discussions so we will leave this with the basic data system.

Define Problems Clearly
As data engineers and data scientists we often learn about the subject matter we are working on through osmosis. We have to understand what the data represents to a point. However, it is difficult for us to know all the nuances of a specific subject and to understand all the context around the problems we are trying to solve.

That is why it is important to try to clearly state the questions you are trying to answer as well as provide as much context as possible. It gives data professionals a better picture of the problem they are trying to solve. The better they understand the problem, the clearer the solution becomes.

Otherwise, they could spend hours going in circles or answering the wrong questions. This is actually pretty common because sometimes what a stakeholder says is understood completely differently by a data professional. Especially if they don’t know why. Then they have to come up with their own why that drives their analysis. That means they could provide the wrong support, or answers at the wrong granularity.

Create Concise Metrics
With all the modern verbiage floating around it can be tempting to attempt to create algorithms and metrics that require too much adulteration of the original data and the value it could offer.

Metrics that are concisely stated are also more easily understood by everyone. This allows managers to make better decisions because they understand what the metrics abstractly represent. Rather than having to struggle to get how some random ratio or calculated value means they should target customer x or y.

This starts with a well-defined population. Whether that population is procedures, people, transactions, etc. It represents a specific group of entities. It is not always wise to look at an entire population first. Larger populations are more likely to have confounding factors that are harder to eliminate with simpler metrics. Developing metrics focused on specific populations to start also makes it easier to notice patterns in larger groups.

Review The Outcome
Analyzing the outcome and trends in the metrics that are developed can help drive new insights and policies. This requires that the outcomes are actually studied. Not just when the results are initially released but on a regular cadence. Typically, the first few times the metrics are examined there can be immediate benefits as policies are changed, entities are reached out to (like in the case of fraud) and hopefully cost savings are found.

After the initial release of the dashboards, there needs to be a plan on how often the results will be reviewed. To often will cause unnecessary actions to be made before the previous ones have impacted the outcomes and not often enough (or none at all) will waste both the dashboard and the time spent developing it.

Make a plan with the necessary team members (usually a director or manager, some subject matter experts for context and one of the data team members). Having this mix will provide the correct amount of perspective while informing the director and data team member of any needs required from the subject matter team. The data team might need to update the metrics based on the findings
Present The Results SimplyData, programming, and algorithms can all get very complicated. As data scientists and engineers we focus on the problem for so long we can begin to think everyone understands the problem as well as we do. Yet, this is not usually the case.

That is why we need to take a step back from our work and attempt to view our research, models, and analytics from the perspective of a teammate who hasn’t stared at the same problem for the past three months. It can be tempting to put all the graphs and logic that were analyzed over the past three months into a presentation or report. But this is likely to confuse a stakeholder who hasn’t been involved in your healthcare analytics project.
 
Usually, all that is required is a few key numbers to drive decisions. When you present too many numbers you risk burying the lead behind. It is important to focus on the key facts and figures first and if support is needed to provide it in a follow-up. This can be hard to do because it feels like we as engineers didn’t do a lot of work when we only show such a small amount of work. However, the key here is an impact, not showing off.
​
Quality data and analytics helps target and track savings opportunities in healthcare. When you start with accurate data and then develop concise metrics your team has the chance to make informed decisions on policies that can positively impact patients lives and at the end of the day that should theoretically be the goal.

We hope this helps you in your healthcare analytics project. Please feel free to reach out to our team if we can go in depth on any point! Our consulting team would be happy to help.

To read more data science and analytic articles see the below!
How To Use R To Develop Predictive Models
Web scraping With Google Sheets
What is A Decision Tree
​How Algorithms Can Become Unethical and Biased
How To Develop Robust Algorithms
4 Must Have Skills For Data Scientists

One of the few things democrats and Donald trump might agree on is reducing pharmaceutical costs.

Why? Healthcare costs continue to rise every year. Increasing pharmaceutical costs, technology improvements and wasteful procedures are costing the US more every year.

This increase in healthcare costs is starting to be felt by everyone. Some can’t afford it, while others struggle to keep up with their medical bills. Those of you who are younger might not understand now, but in 30 years when you need to go to the hospital in 2048 and you’re charged $200 dollars for a single Tylenol pill it might make sense. You might understand how ridiculous healthcare costs have become if we don’t start fixing problems now.
​
Why are healthcare costs continually going up?

There are really many reasons driving up medical costs. With many causes comes many solutions to improve the situation. However, let’s start by defining some of the biggest problems from a financial perspective. Below is an infographic of areas where there are massive opportunities to reduce healthcare costs. There are billions of dollars spent that provide opportunities to reduce the overall US healthcare bill.

How Do You Reduce Healthcare Costs?
Reducing healthcare costs starts with recognizing the biggest problems that will have the biggest impact when solved. Problems like fraud, waste and inefficient systems are some of the largest costs on the healthcare systems.

How do you recognize the biggest costs? Well, that is where data comes in! When we examine the successes of some of the large corporations today like Amazon, one of the differences they have between less data driven companies is they avoid terms like “I think” without having the data to back it up.
​
Our team will be posting on how you can reduce your healthcare costs using data in the next few days. If you have any questions on the subject matter before then, please reach out!

​Read More On Data Analytics Below!

How To Develop A Successful Healthcare Analytics Product
How To Use R To Develop Predictive Models
Web scraping With Google Sheets
What is A Decision Tree
​How Algorithms Can Become Unethical and Biased
How To Develop Robust Algorithms
4 Must Have Skills For Data Scientists

Our team was recently asked how data analytics and data science can be used to improve bottlenecks and patient flows in hospitals. Healthcare providers and hospitals can have very complex patient flows. Many steps can intertwine, resources have to shift in between tasks all the time, and severity of patients and new patients push the order of who needs to be seen all the time. This does not make it easy to approach process improvement in a hospital. This problem is a process problem. Something industrial engineers and six sigma practitioners love. They love looking at healthcare process problems in Excel sheets with thousands of tabs and thousands of rows of data. However, now we no longer are limited to doing our analysis and model development in Excel spreadsheets on thousands of rows of data. We now access to more complete data sets that in our experience can be in upwards of the billions of rows and more powerful computational systems we can analyze patient flows and bottlenecks much accurately and effectively.

Now with these tools like SQL, R and python we can analyze these data sets quickly.
It’s not just about the tools. In fact, with such powerful tools it can be tempting to try to make models and algorithms that can solve all the problems in one go. One of the big issues with this approach when looking at patient flows and bottlenecks in hospitals ( or really any problem) is it is far too general of an angle. It makes it very difficult to assess when an analysis is finished and often keeps the data scientists and analysts spinning for weeks without getting a real answer.

The problem here is the scope of looking at everything is very difficult to manage and pinpoint issues. Instead of trying to attack all the processes and procedures a hospital has. It is a better idea to break down several general categories of procedures/patient flows/processes that you believe are likely to have bottle necks. This is because hospitals have so many different possible paths and processes (I am going to use the word process to describe the patient flow below) that blindly looking for some sort of bottleneck will take forever (it is like looking for fraud in healthcare, if you try to do it too generally, then it will be near impossible to find).
​
The first step is to find out the problem areas. Without knowing what you want to target it can be very difficult to know what the solution is. In a perfect world your hospital has a database that tracks all the processes and procedures that are done. This will make it easy to develop a query or Jupyter notebook that can point out the main choke points. This will further help your team limit the amount of unnecessary work that is required. Once your team knows where the problems are, then there are low hanging fruit your teams can use to look for issues.

Abnormalities

Abnormalities, like inconsistent times for patient flows, whether that is specific doctor or in general can state that there is a problem. Finding these specific outliers can be quite simple.

For instance, let’s say you look for outliers in times for patient flow x and you hypothesize that specific days of the week, or times of the day are more likely to have longer times for certain steps. Then you pull out those steps and analyze time at a time granularity to flag each process individually. You might find that summer’s see decrease in productivity or that during the 4th of July your ERs overflow or perhaps some much less obvious data points.

One key point here, is that you come up with a theory first. Because having a clear question and hypothesis makes it much easier to look for evidence. With a clear question you know where to target further analysis. You can use a query to clean up your data and break it down to the granularity required. This might be on a hospital level, doctor level or maybe even down to the procedure level.
​
From there you could apply a basic algorithm that is good at highlighting outliers(like a basic IQR calculation or something more complex). Once you have found outliers then it can lead to further analysis into why there were longer times or inconsistent times in specific processes. There are many plausible causes, but now you have decided on a category of procedures, hypothesized and found a plausible weak point. Having these basic steps makes it much easier to move forward.
Following these steps you can repeat a similar process. Theorize why you are seeing the outliers, what might be causing it, and further research the data. This could be caused by bad processes, having too few staff during times of day when you need more people on hand (think queue theory). Once you know which steps to look at, you can start putting next steps in place such as process improvement teams who are now pinpointed towards the exact problem rather than simply sending in a team of analysts to follow around doctors and guess where the problem area is.

Chokepoints

Besides abnormalities, another common issue is that some processes might need the same resource. Now one way to locate these bottlenecks is based on the first point of abnormalities. As a bottleneck might be one of the problems causing the the abnormality. However, choke points might also hide themselves in the fact that the step in question always runs long and thus there is no abnormality. Instead, this analysis will require asking a simple question. Are there steps in patient flows that overlap and seem to take a long time or at least longer than expected. Certain steps might take a long time, like certain labs that take a while to run. There are others that shouldn’t. Analyzing these steps could lead to hospitals putting in new suites or hiring new specialists to deal with the heavy load in certain areas.

​Conclusion

​Improving patient flows is an important step to reducing patient costs and improving their satisfaction. By reducing the time they spend in the hospital and healthcare system you reduce the amount of hours required for staff to take care of them. This should help reduce the overall costs. Our team always looks as it as a reduction to the patient even though it should also in turn be a reduction in cost to the healthcare system. From our perspective, anything hospitals, insurance providers and consultants can do to help reduce healthcare costs in our current system need to be done.
How To Develop A Successful Healthcare Analytics Product
The Problems With Machine Learning In Healthcare
How To Use R To Develop Predictive Models
Web scraping With Google Sheets
What is A Decision Tree
​How Algorithms Can Become Unethical and Biased
How To Develop Robust Algorithms
4 Must Have Skills For Data Scientists

By Benjamin Rogojan
​
Recently an article by the Wall Street Journal has been floating around online that discussed how models will run the world. I believe there is a lot of truth to that. Machine learning algorithms and models are becoming both ubiquitous and more trusted across industries. This, in turn will lead to us as humans spending less time questioning the output and simply allowing the system to tell us the answer. We already rely on companies like Google, Facebook and Amazon to inform us on ideas for dates, friends birthdays and what the best products are. Some of us don’t even think twice when it comes to the answers we receive from these companies.

As a data engineer who works in healthcare, this is both exciting, and terrifying. In the past year and a half, I have spent my time developing several products to help healthcare professionals make better decisions. Specifically targeting healthcare quality, fraud and drug misuse.

As I was working on the various metrics and algorithms I was constantly asking myself a few questions.

How will this influence the patient treatment?
How will it influence the doctor’s decision?
Will this improve the long-term health of a population?

In my mind, most hospitals are run like businesses, but there is some hope that their goal isn’t always the bottom line. My hope is that they are trying to serve their patients and communities first. If that is the truth, then the algorithms and models we build can’t just be focused on the bottom line (as many are in other industries). Instead, they really need to take a moment to consider how will this impact the patient, how will this impact their overall health, will this metric change the behavior of a doctor in a way that might be negative?

For instance, the Washington Health Alliance, which does a great job at reporting on various methods to help improve healthcare from a cost perspective as well as a care perspective wrote a report focused on improving healthcare cost by reducing wasteful procedures. That is a great idea!

In fact, I have worked on a similar project. That is when I started to think. What happens when some doctors take and over-adjust? I am sure many doctors will appropriately recalibrate their processes. However, what about the ones that try to over adjust?

What happens when some doctors try to correct their behavior too much and cause more harm than good because they don’t want to be flagged as wasteful.
​
Could we possibly cause doctors to miss out on obvious diagnoses because they are so concerned about costing the hospital and patients too much money? Or worse, perhaps they rely to strongly on their models to diagnosis for them in the future. I know I have over-adjusted my behaviors in the past when I was provided criticism, so what is to stop a doctor from doing the same? There is a fine line somewhere between allowing a human to make a good decision and forcing them to rely on the thinking of a machine(like Google Maps, how many of us actually remember to get anywhere).

There is a risk to focus more on the numbers and less on what the patients are actually saying. Doctors focusing too much on the numbers and not on the patient is a personal concern of mine. If a model is wrong for a company that is selling dress shirts or toasters that means missing out on a sale and missing a quarterly goal.
​
A model being wrong in healthcare could mean someone dies or isn’t properly treated. So as flashy as it can be to create systems that help us better make decisions. I do often wonder if we as humans have the discipline to not rely on them for the final say.

As healthcare professionals and data specialists, we have an obligation not just to help our company, but to consider the patient. We need to not just be data driven but be human-driven.

We might not be nurses and doctors but the tools we create now and in the future are going to be directly influencing nurse and doctor decisions and we need to take that into account. As data engineers, data scientists and machine learning engineers, we have the ability to make tools to amplify the abilities of the medical professionals we support and we can make a huge impact.
Because I agree, models will slowly start to run our world more and more (they already do for actions like trade, some medical diagnoses, purchasing at Amazon and more). But that means we need to think through all the operational scenarios. We need to consider all the possible outcomes both good and bad.

How To Develop A Successful Healthcare Analytics Product
How To Use R To Develop Predictive Models
Web scraping With Google Sheets
What is A Decision Tree
​How Algorithms Can Become Unethical and Biased
How To Develop Robust Algorithms
4 Must Have Skills For Data Scientists

By Ben Rogojan

Data science is just one of the modern data driven fields in our new data world. Another job that is even more prevalent than data scientist is data engineer. Now being a data engineer does not have all the hype behind it of being a data scientist. However, companies like Google, Facebook, Amazon and Netflix all need great data engineers!
Data engineering requires a combination of knowledge from data warehousing, to programming in order to ensure the data systems are designed well and are as automated as possible.
The question is: How do you prepare for an interview for a data engineering position?

Many of the questions will require you to understand data warehouses , scripting, ETL development, and possibly some NO-SQL if the company uses a different form of data storage system like CouchDB.

In case you are preparing for a data engineering interview here are some questions that might help you. We are focusing on conceptual questions. However, you should also work on some technical skills like SQL, python, etc.

How would you approach developing a new analytical product as a data engineer?

As a data engineer you control what is possible in the final product. A data scientist can’t build algorithms or metrics without having the data at the right granularity and the right data.
This means a data engineer needs to understand the entire product. A data engineer can’t just get away with building systems based off requirements. They need to ask why they are building certain tables and objects.

If the stakeholders already have a general outline of what they want it is very helpful. If they don’t have one, we would want to work with them to develop a general idea of what metrics and algorithms will exist. This drives all the major decisions. What data should be pulled it, how long should it be stored, should it be archived, etc.
Once a general outline exists, then the next step would be drilling into the why of each metric. This is because as you’re building different tables at different data granularities, certain issues might arise. Should the unique keybe on columns A and B, or A, B and C. Well, that depends, why is this important? What does that row signify? Is it customer level, store level, maybe brand level?
Once your team has gone through the process of working on the outline with your stakeholders and gained an understanding of the why. The next step is to think through as many operational scenarios as possible.

Will you need to reload data ever? Do your ETLs allow for it? Is it efficient? What happens when X occurs? How do you handle case Y?

You can’t spend all day doing this, but trying to think of as many issues that could occur helps develop a more robust system. It also helps create a system that actually meet requirements.
From there, it’s about developing the design, creating test cases, testing the tables, stored procedures and scripts and then pushing to production. How that occurs usually depends team to team.

What is the difference between an operational database and a data warehouse?

If you took a database course in college you probably learned about how to set up a standard normalized database. This style of database is optimized for transactions that involve Insert, Update and Delete SQL statements. This is a standard operational databases. They need to focus on making transactions quickly without getting bogged down by calculations and data manipulations. Thus, their design is a little more cumbersome for an analysts. Generally you will have to join several tables just to get a single data point.

A data warehouse is not concerned as much with dealing with millions of fast transactions every second. Instead, a data warehouse is usually built to support a data analytics product and analysis. This means performance is not geared towards transactions, but aggregations, calculations and select statements. A data warehouse will have a slightly denormalized structure compared to an operational database. In most data warehouses a majority of tables will take on two different attributes, a historical transaction table and tables that contain categorical style data. We reference these as fact and dimension tables. The fact table is essentially in the center. Unlike in a normalized database where you might have to join across several tables to get one data point. A standard data warehouse usually has a focus on the fact tables and all the dimension tables join to provide categorical information to the fact table(it’s also typically bad practice to join fact table to fact table, but sometimes it can occur if the data is created correctly). Here is an example of the structure of a data warehouse.

These are not the only tables that exist in a data warehouse. There are aggregate tables, snapshots, partitions, and more. The goal is usually a report or dashboard that can be automatically updated quickly.

Data warehouses also have a lot of other nuances like slowly changing dimensions. However, that is a whole other can of worms.

Tell us about a time you had performance issues with an ETL and how did you fix it?

As a data engineer you will run into performance issues. Either you developed an ETL when the data was smaller and it didn’t scale or you’re maintaining older architecture that is not scaling. ETLs are built up of multiple components, multiple table inserts, merges, and updates. This makes it difficult to tell exactly where the ETL issue is occurring. The first step is identifying the problem, so you need to figure out where is the bottleneck occurring.

Hopefully..whoever set up your ETL has an ETL log table somewhere that tracks when components finish. This makes it easy to spot bottle necks and the biggest time sucks. If not, it will not be easy to find the issue. Depending on the urgency of the issue, we would recommend setting up an ETL log table and then rerunning to identify the issue. If the fix is needed right away, then you will probably just have to go piece by piece of the ETL to try to track down the long running component. This also depends on how long the ETL takes to run. There are ways you can approach that as well depending on the what component relies on what component

Issues vary wildly from table locks, slow transactions, loops getting stuck, etc.
Once you have identified the issue, then you need to figure out a fix. This depends on the problem but the solutions could require adding an index, removing an index, partitioning tables, batching the data in smaller pieces (or sometimes even larger pieces it seems counter intuitive but this would depend on table scans and indexes). Depending on the storage system you are using it is good to look into the activity monitor to see what is happening on an I/O level. This will give you a better idea of the problem.

When you look at the activity monitor you can see if there is any data being processed at all? Is there too much data being processed, none, table locks? Any of these issues can choke an ETL and would need to be addressed.

If you Google some of the performance issues then you will find some people blaming the architecture for a lot of the problems. We don’t disagree with them. However, this doesn’t mean you throw in the towel. There are always various ways to manage performance if you can’t touch the actual structure. Even beyond indexes there are some parallel processing methods that can be used to speed up the ETL or adding temporary support tables to lighten the load.

Question Number 4? Are you experienced in python, powershell, bash, and or Java?

This question is more to note that it is important to have a scripting language on your side as a data engineer. Typically, most shops we have worked in won’t just rely on ETL tools like SSIS. Instead, they will often use custom scripts and libraries to load data. It might seem like overkill, but it is often easier. Using tools like SSIS are great if you need all the fancy functions like sending emails and some of the other add-ons. Even these could be scripted instead of written.
So we do recommend having some scripting abilities as well. It allows you to easily automate data flow and analytical tasks.

These are just some general questions to help get ready for your data engineering interview. Besides these questions you should also look into the concept of slowly changing dimensions, automation with python or powershell, some basic linux commands and design concepts. If you do have a data engineer interview, then we wish you good luck!

Call To Action:
Our team has experience developing multiple types of data pipelines from start to finish. We have experience creating effective data architecture and thinking about the end use cases. If you need help building a data warehouse or an entire data analytics products please reach out!
​
How To Grow As A Data Scientist
How To Survive Corporate Politics As A Data Scientist
Boosting And Bagging: How To Develop More Robust Machine Learning Models
8 Top Python Libraries For Machine Learning
What Is A Decision Tree
Other Data Science Services Our Team Provides

By Ben Rogojan

Analytics and data science are  important tools in healthcare. Not just because they might be able to predict when someone will have a heart attack. Good data science and analytics are  important tools because they can help make better decisions when it comes to spending and reducing inefficiencies in healthcare. Being able to reduce the cost of healthcare for the providers would in turn allow them to allow more people access to healthcare.

The temptation healthcare has when looking at all the other industries that have been using analytics and algorithms for a long time like finance/banking and ecommerce is to try to take on all the data problems in healthcare in their entirety. This in turn leads to large investments in data teams, data lakes, and other data centric initiatives that don’t always succeed. Similar to one of the reason the DC movies are failing to capture audiences attention like Marvel. They rushed into trying to create something similar to what Marvel created in a decade in a third of the time. Similarly, many healthcare companies try to set up data science shops and attempt to replicate what Google, Amazon and some of the other big tech companies have been doing for more than a decade in a few years.

It takes time to set up analytical teams and data systems at that proficiency level (let’s not even get started on data labeling). Instead of trying to create an entire universe of data analytics and algorithms in 1 year. We have had success by picking one or two problems to start with (like selling books online but in healthcare), pick a point of view to approach the problem and just tell a simple story. If your data teams are able to do this and succeed, then you will be that much closer to success and have started to gain supporters and buy in from managers. Healthcare is complex, so start by focusing on a few problems, and a limited perspective before investing millions of dollars in a large team.

​

​Pick One Problem To Take One

When it comes to healthcare analytics(or any analytics), it is important to pick a specific problem and then break that problem into further bite sized pieces. For example, if you are trying to develop an algorithm that detects fraud. It can be very difficult to design a model, algorithm or system to abstract the concept of fraud as a whole into a single mathematical expression. Especially when the healthcare interactions are rarely classified fraud or not fraud.

That doesn’t stop you from doing analytics, or predictive modeling. It just changes your approach. Instead of developing complex data science models, focus on what has a good ROI first. A basic system that can help first reduce the amount of claims analysts have to wade through for fraud or point out larger systemic problems at a higher level can help know where to first focus your efforts. In addition, they can be labeling and noting patterns through the interactions they get through.

After spending some time looking at those cases, your analytics teams will have a further understanding of the problem as well as a lot of great best practices and labeled data. In the end, this might seem slow, but slow is better than failing.

Focus On One Perspective 

When developing analytics of any kind there are lots of different angles and points of view that you can take. For instance, in healthcare you could focus on the healthcare providers (hospitals, ERs, etc), patients or types of procedures. Your team might not have the resources to develop a tool, dashboard or algorithm to approach all these different angles right away. Thus, when taking on a new project  like “predicting patient readmissions”, pick a category that will benefit your team the most. Often times focusing on the provider first is one of the better options. This is because it is much easier to change provider behavior compared to patient behavior. When you alter one providers behavior the impact it has is much larger compared to changing one patients behavior. Both of these are two reasons why often focusing on analytics on the provider level first can be helpful. In addition, like was discussed above, it could help focus and pinpoint some of the more specific problems at the patient level.

Your Final Product Should Have A Clear Story

Once you have developed a final data points of the product, that product needs to be able to tell a story. Honestly, this is still a difficult part for me. However, it is a key concept that makes a huge difference. Even if you’re an amazing programmer, data engineer, or algorithm developer, if your product doesn’t portray a clear story to your end-users, then it could get ignored or misinterpreted. Usually, the flow I have seen that is successful for reports is have a high level of what the problem is, maybe include the cost of the problem if that is applicable. This gets everyone on the same page and also catches their attention. If you can state how big the problem is and how much it is costing them, it will draw the end users in.

Then you can either further go into the problem and break it down into subsets and then go into the next steps.

The next steps don’t have to be a direct solution. It might be a list of procedures that are problematic and causing a high amount of readmissions or fraud. This leads to the next possible project which is analyzing the why. Once you have figured out the possible why and solution, then you can refer back to the report and see if things changed. 

Overall, the way you approach healthcare analytics or developing algorithms to predict events like patient readmission is no different than any other industry. It can be tempting to try to take on entire problems like fraud, or patient readmission as a whole. Healthcare’s massive amount of data and complexity of transaction does not make this easy. In order to manage this complexity breaking down the problem and deciding the point of view you plan to take will help increase your success and the rate of results.

Does your team need help developing healthcare analytics? Perhaps you are looking to measure provider quality or analyze patient patterns. If so, then contact our team today! We have a team of healthcare analytical specialist who can help create the tools you need.

If you want to read more about data science or analytics check out the articles below:

How Men's Wearhouse Could Use Data Science
How To Use R To Develop Predictive Models
Web scraping With Google Sheets
What is A Decision Tree
​How Algorithms Can Become Unethical and Biased
How To Develop Robust Algorithms
4 Must Have Skills For Data Scientists

Being very concerned these researchers put in a proposal for millions of dollars to try to figure out how to decrease this mortality rate in Florida. However, they forgot to dig a little deeper. They forgot to exam the average age of the populations of the two states. If Florida’s average age is 52 and Washington’s is 25 that might play a role in the mortality rate. In this case, the age played the role of a confounding variable.
​
A variable that is not considered but plays a role in the outcome of an event is considered a confounding factor.

In epidemiology, a confounding variable refers to a variable that is a risk factor for a disease or is associated to the exposure of the disease but is not the actual exposure. Confounding variables are not limited to a disease. When developing algorithms for product recommendations, A/B testing or market segmentation confounding variables can creep in and mislead data scientists and analysts looking to create effective algorithms.

​

This can lead to invalid conclusions and incorrect comparisons. For instance, in the case of death rates in Washington and Florida, you are comparing two different populations. This is more like comparing apples to oranges. Although in this case you are comparing people to people, the populations have different attributes that make them unfair to compare.
​
In order to mitigate this there are two specific options. One is to develop the populations into more focused categories like age. Another method that could be used to standardize measurements like death rates is to use a direct adjustment rate. Using a direct adjustment rate allows you to create a standardized

A Real Life Example Of Confounding VariablesLet’s suppose you want to compare an outcome in two populations. In this case, let’s say you are comparing the case fatality rates of a trauma hospital to that of a general hospital. This example is actually based off real studies done because hospitals were having a difficult time fighting to keep their trauma centers open.

Let’s suppose that the trauma hospital has a case fatality rate of 8.6% where as the general hospital has a case fatality rate of 5.6%. From this perspective it would seem as if it would be safer if we went to a general hospital as exposure to a trauma hospital seems to lead to a greater chance of death. This was what some people assumed by reading these numbers and it is not such a far off conclusion based off the numbers.
​
However, in this case, we are ignoring the level of injury or trauma that the patients who visit both hospitals may have when they come in. In this case, we will refer to this as the triage level. There are technically 5 levels. In order to reduce the amount of math, I am only going to consider 1–4. Hopefully…there aren’t too many patients dying when they are considered level 5.

Looking at the spread of different patients who went to either a trauma or general hospital. You might notice that there is a distinct difference between which type of patients went where. We are seeing a much higher proportion of patients that visited trauma hospitals were triage level 1 compared to general hopsitals(320 patients for trauma hospitals and 80 patients for general hospitals). That makes sense, you would want to refer more of your triage level 1 cases to a trauma hospital.

However, the difference between the two data sets becomes more apparent in the next table.

Here we see the difference in death rates per triage level. So although the overall case fatality rate is much higher in the Trauma Hospitals, the overall percentage of deaths for triage level 1s is higher in the general hospital(9.4% for Trauma Hospitals compared to 25% for General Hospitals). That means, if there would have been more patients brought to the general hospital that are triage level 1, there is a good chance their case fatality rate would be worse.

Now the next question is, how do we adjust the rates so they more fairly and concisely compare each case fatality rate per hospital type?

That is where this final table comes into play. The goal will be to create one standardized populations by adding the two populations together. Then, we use the percentage rates per death for each of the hospital types on the new standardized population.

This creates a fairer comparison of the two populations. In the end, you end up without about 51 total deaths out of 1202 people for trauma hospitals and 123 deaths out of 1202 people for general hospitals.
This in turn provides a very different story and a more fairly compared one at that. Now the new death rates are 4.3% for trauma hospitals compared to 10.3% for general hospitals.
When these numbers are provided to hospital administrators, they will have a clearer picture on what is actually occuring. With this information they can make a more informed decision without having to ask to much about context and if they do, then you can explain your methodology.

​However, this method avoids bad assumptions being made without good questions being asked first.
Confounding variables are present in every area of research where correlations are being examined. Thus, it is important to look past the initial outputs and question whether there are possible underlying reasons for the numbers you are seeing.

When developing a report for directors and managers it is important to consider how you will simplify complex concepts like confounding variables. It is about telling a complete story. This occurs when you are able to concisely state a point across multiple data points and graphs.
If your team is looking for a team of data experts who can help develop your healthcare analytics, then feel free to reach out today.

If You’re interested in reading more about data science:

Time Series Model Development: Basic Models
How To Measure The Accuracy Of Predictive Models
What is A Decision Tree
How Algorithms Can Become Unethical and Biased
How Men’s Wearhouse Could Use Data Science Cont
Introduction To Time Series In R
How To Develop Robust Algorithms
4 Must Have Skills For Data Scientists

​