Fixing Bad Data with Data Preparation Tools

Data Goes Bad as Easily as Bananas in the Sun

Data goes bad for all kinds of reasons, from changes in processes or standards (or a lack thereof) to basic typos. It takes time and attention to maintain your data, and if you're not actively using data for insights and reporting, it's easy to deprioritize maintenance.

Without clean data, it's virtually impossible to understand your organization's performance. Sure, you can spend time trying to fix issues as they come up, but that takes time and isn't the most efficient way to address your data issues. Once your data starts to go bad, it's hard to keep up with corrections and updating all the locations where the mistakes were made. For example, one change to a campaign code travels from your marketing platform to your POS to your reporting, and once it's there, it's hard to correct.

Once data hygiene goes down, how can we fix it? In the world of data analytics, we call this process data preparation. Typically, an analyst will connect, clean and standardize, and structure the data so it can be processed and analyzed by whatever tools they use. A lot of larger organizations have teams of data analysts and wranglers that specialize in data preparation, but if you're a smaller team, you might not have the resources to spend on it. That's where tools like Tableau Prep come in.

Tableau Prep as a Data Preparation Tool

Sample flow that comes with Tableau Prep

Tableau Prep helps you prepare data without needing to know SQL, Python, or any other technical skill set. Its visual interface lets you clearly see the changes you've made and the resulting dataset, and you can do most things an analyst would do during data preparation, like combine datasets, update or correct data entries, and create new data fields. Best of all, once you make a change to your data in Tableau Prep, that change will be applied every time you run the flow, so you only have to make the change once.

I've seen plenty of real-world examples with clients, and the situation is always the same: a small team that doesn't have time to maintain their data, and when processes change, they don't have time to update the historical data. Data preparation tools not only simplify data maintenance but also make it more accessible to smaller, less technical teams.

Data Connections

Tableau Prep has just about every connect you could possibly need.

The first step with Tableau Prep is always connecting to some kind of dataset. Tableau Prep has direct connections to over 100 different source types, ranging from cloud-based servers like Google BigQuery and Microsoft SQL Server to tools like Google Analytics and Salesforce.

If you're unfamiliar with how to connect directly to any of those sources, Tableau Prep also allows you to load a static extract via Excel or a CSV. If a direct connection is possible, I typically recommend using it; however, there are some instances where an extract is a better option. For example, the Google Analytics connection provides raw data without aggregation, which can cause a lot of duplicate rows. If you know what you're doing, you can fix the duplicates, but in a lot of cases, it's easier to just load an extract and let Google Analytics do the aggregation and deduplication for you. The biggest downside to using an extract file is having to reload it every time you update your flow; however, in most cases, that doesn't take too long.

If you've got multiple sources, you can create multiple connections and combine the data in Tableau Prep. What's really cool is that combining the data is done visually instead of through lines of SQL or code, so it's really easy to understand what's going on, especially if you're not used to working with data. In Tableau Prep, you can select multiple tables, apply the correct join, and visually see a sample of the result. If you made a mistake or want to check the join clause, it's easy to see exactly what needs to change, and you don't need to sift through rows of data to find potential errors.

Alongside joins, you can union tables together, which is particularly useful when you need to update extracts. Rather than having to replace the same extract over and over again, you can simply load the new data and union it with the existing tables. On top of that, if you add the new data to a folder on your computer, Tableau Prep will automatically detect it and add it to the dataset the next time you run an update.

Preparing the Data

Users can see previews of the data, make changes, and create new fields all within a singular view.

Once you've made all the necessary connections, it's time to make changes to the data. Changes to data can be made via a variety of methods, depending on what you want to do. If you're wanting to do something simple—like change the format from an integer to a string, for example—you just need to click a few things in the profile pane, and the change will be applied. Additionally, if you have typos or misspellings in your data, you can make corrections that will be saved and automatically applied to the entire dataset wherever that error occurs.

In addition to updating existing fields, you can create new ones using calculated fields. This feature becomes especially useful when the data will be used in dashboards or to feed other reporting tools because the new field will be published as part of the dataset. For example, if you use open rate as a benchmark for your email program but your tool's extract doesn't include that metric, rather than creating it every time you make a new dashboard or report, you can create the field in Tableau Prep, and it will automatically be added whenever you run the flow.

Now that you finished preparing your data, you'll need to use an output step to create the new data source. The output can publish back to the cloud server you originally used, a Tableau data source for dashboards, or even an Excel or CSV file. This new data source will have all of your changes, updates, and calculated fields, and you can use it as if it came from the original source.

There's a lot of tedious and repetitive work that can be automated with Tableau Prep, and when automation takes over, your data quality and hygiene can improve, giving you more time to focus on what's important rather than wrangling data.

Need Help?

If data preparation sounds interesting to you but you don't know where to start, the Backcountry Draft team is here to help! We specialize in helping organizations not only understand their data but put it work by creating relevant and impactful insights that help you and your organization reach its goals. When you're ready to take the next steps in truly applying your data, send us a note!

Contact Us