World of Data Series: Part One

In today’s world, our systems have come a very long way. We capture data about our businesses that we want to track and can pull simple reports on that data. Overall, though, you may be in the very beginning of trying to take that data, analyze it, and be able to make decisions or take actions off that data.

If this is where your business is and your system does not have a way to analyze your data, you’re probably trying to figure out how to:

• Work with your business to assess what information is needed to help make better decisions.
• Pull data from your system.
• Clean up that data and potentially combine with other data sets.
• Build your own data visualization that can be used to see and understand your data.
• Deploy your visualization and train users.
• Gather feedback from your users and make updates.

Let’s break down each of the above items:

Business Need
Before you start pulling data and making data visualizations, you really need to understand what is needed, and the best way to do that is with a few questions:

• Document what questions need to be answered with the data.
  • What are the sales by region?
  • How are our sales trending by quarter by region?
• Are the questions worth answering?
  • Meaning, what can we do now that we couldn’t before now that we know the sales by region?
  • Does that help pinpoint where we have resource issues?
  • If we see that a region has fewer sales than another, do we need to drill down to sales by state or sales staff to see if that notifies us of an issue?
• For each question, you will need to:
  • Know what data is needed in your system to be able to answer that question.
  • Have an idea of how you might visualize the data and what categories/groupings are needed in case those aren’t in the data you pull and you need to create those categories based on the data you do have. For example, if you would like sales by region and you only have state in the data, you will have to create a region field based on the states.

Pull Data from System
To pull data from your system, you will need to research your options by seeking answers to the following questions:

• Are there existing reports that have the data you’re looking for?
• If there aren’t existing reports, what options do you have from your system to pull data?
  • Reporting solutions?
  • APIs (application programming interface)? – If you have a programmer on staff, this might be a good option.
  • SQL access? – If you have someone on staff who knows SQL, this might be an option.
  • The above three items will require additional questions if they are options:
    • How do we connect to each?
    • What security is in place and are user logins necessary?
    • Is there a need to remote into a machine or workspace to do this?
• Is there a way to have the data (either via report or other option above) automatically pulled/generated and saved to a file directory (if necessary)?
  • You also might have an idea of how often it would be good to have access to this data.  
  • Daily, weekly, monthly, quarterly, etc.
• If there isn’t a way via your system to pull the data automatically, does your organization have access to RPA (robotic process automation) software?
  • The nice thing about RPA is that it can be set up to mimic a user using a system, so if you have a report that can be run manually by a user, it is likely something that can be set up to have a bot do.
  • You also can set it to run on a scheduled basis to pull the data as often as you need.
• What type of format will the data be in?
  • You want to make sure you can get the data in a format that can be ingested into analytics software easily.
  • Easy: Excel, CSV, Text (depending on format).
  • Not quite as easy: PDF.
  • Depending on skills of staff:  SQL Access, XML, JSON, HTML.

Data Cleaning
Once you have pulled the data you plan to use, it will need to be reviewed field by field to make sure it is in the format and type needed. Usually this process is referred to as cleaning your data. Here are some things to consider:

• When doing data cleaning, you need to have an idea of:
  • The fields needed to build your visualizations in the end.
  • The fields you might want to show in summary form, e.g., if your organization has regions that are based off of states of the offices, you can create a region field in your cleaning step based off of the state field and then use that in the visualizations to show sales by region.
  • If you have more fields or rows of data than you need, then filter the data down and only keep the needed fields.
• Tools – There are many tools that can help you clean your data or put it into the format you need to do your visualizations on, but they generally fall into two categories:
  • Ad Hoc – Perform manual steps that you will have to repeat with every new pull/refresh of the data. Excel is the classic example of software in this category.
    • If your raw data are in Excel or CSV format, you can easily use Excel to open the files and add columns with formulas that adjust your data the way you see fit.
    • Excel also can import column-delimited text files by going to the Data tab and clicking Get Data > From File > From Text.
    • Once imported, you can use Excel’s native functions or add-in functions with software such as ASAP Utilities or TeamMate Analytics to do things like remove leading and trailing spaces, convert data from numeric to text, etc.
• Repeatable – Build a script/flow that allows you to repeat the cleansing process over and over. These tools allow you to save what modifications you are making to the data set, so if you do get a new pull/refresh of the data, you can run it back through the same process to produce data that has been cleansed in the same way as the original data.  Alteryx, Tableau Prep, and SQL are a few tools BKD Analytics uses to help clean data.  These tools can help:
  • Create new fields based on data in other fields.
  • Summarize data within a data set.
  • Combine data from multiple data sets/sources/files by joining on common fields or combining the data together.
  • Pivot your moving columns to rows or rows to columns.
  • Import a number of different file types or databases to the input and output of the solution.

• Automation
  • If this is something you want to repeat on a daily or weekly basis, you might consider having this task be automatically done when your report/file/data is provided.
  • Alteryx, Tableau Prep, and SQL Server all have different ways of executing on a scheduled basis but can all help if you’re trying to reduce repetitive work.

Data Visualization
Once you have the data in a good spot, now you can begin visualizing the data. To do this, you should:

• Look into tools that can visualize data, unless your organization already has an existing tool.  There are several tools out there:
  • Tableau
  • Qlik Sense
  • Domo
  • Microsoft Power BI
  • Others
• Our Data Analytics team mainly uses Tableau, but there are a lot of options out there.
• Make sure you pick something where you can share your visualizations with others in your organization so they can not only view the visualizations but also interact with them.
• When building your visualizations, make sure you’re answering the questions with your visualizations of the data. There are a number of different graphs/charts that can be used to see data differently. Below are some examples of graphs/charts our BKD Analytics team built on our COVID-19 Recovery Dashboard.

Maps

• Can use color on a state to show an increase or decrease in COVID-19 cases from the past week. 

Line Charts

• Can show how the numbers are trending for a given data point.  

Bar Charts

• Can show the daily number of cases; it also includes a trendline.

• Can rank stats by unemployment rate.

Stacked Bar Charts

• Can show the number of loans per loan size.

Heat Maps

• Can show construction spending by category and sector, and the color of the heat map shows the percentage difference from the prior month.

Deploy Visualization & Train Users
When providing users access to your visualizations, here are some things to think about:

• Make sure you have a process—and follow that process—for how to provide users access to your data visualization platform.
• You will want to make sure they have access to everything they need to use your visualization, i.e., the visualization, data sources, etc.
• Assuming you created visualizations that make it clear what questions you’re answering, there really shouldn’t be a lot of training involved.
• Make sure your visualization gives a clear understanding of what information is being communicated. For example, if different colors mean different things, be sure to include a color key. If clicking on something is required to make an action happen, put a note on the dashboard so the user is aware.
• If your user might have questions of where the data is being pulled from, put the information on the dashboard somewhere or make it available through a “hover over” option, such as an information icon that displays that information once you place your mouse over the icon.

User Feedback & Updates
Once users have been using the visualization:

• Ask for what they like about the visualization you built and what they don’t like or understand.
• Ask for any new questions they might like to gain insights from looking at your visualization.
• All of this information can lead to adjustments to the visualization that can make it better for all.

There will be other series where we’ll go into more details on some of the sections above. For example:

• If you receive an XML or JSON file, how can you read it?  
• If pulling data from a website, how do you read it?

If you have any questions or need assistance, please contact your advisor or submit the Contact Us form below.