1. The importance of clean data entry
Welcome! My name is Luke Pajer and I am a Digital Marketing Specialist that tackles spreadsheet issues on a daily basis.
In this first chapter, we will dive into a spreadsheet and explore various ways to mitigate bad data entry.
2. Campaign performance spreadsheet
Put yourself in the shoes of a digital marketer. Now, imagine you are tasked with building a spreadsheet to store both Google and Facebook Ad campaign performance metrics.
This spreadsheet must contain the entry date, source of the campaign, campaign name, impressions, clicks, and click-through rate or CTR. Impressions represent the number of times the campaign ads were viewed, while clicks represent the number of times the ad was clicked on. CTR is the percent of impressions that resulted in clicks.
These metrics are a variety of data types: entry date is a date, source and campaign name are strings, impressions and clicks are integers, and CTR is a percent.
Now that we know what we need, let us take a look at the spreadsheet.
3. The data problem
In the digital marketing world, it is not common to have someone run all of the campaigns across different sources. Instead, we tend to find one party working for each of the sources. In this case, two parties would be contributing to a single spreadsheet; one for Google Ads and one for Facebook Ads. This often results in unclean data entry.
4. The data problem
Each group may have slightly different naming conventions and formats that can hinder a spreadsheet virtually useless. Here are some issues with this spreadsheet.
Date formatting differences are common, as highlighted in yellow in the entry date column. Placing the month and day in a different order is a common difference between contributors. There may also be slight variations in naming conventions, as highlighted in red in the campaign name column.
Regardless of the number of people contributing to the sheet, from time to time, we may also make mindless mistakes. This includes misplacing impressions and clicks in the wrong cells, which are highlighted in blue.
5. The solution: data validation
Building a spreadsheet with no bad data is impossible, especially with multiple parties contributing data. However, using data validation is a great way to mitigate bad data entry.
Data validation is a set of rules you apply to a selection of cells. A flag will appear in the cells that do not meet the criteria. You can hover over both flagged and non-flagged cells in a data validation selection to find one of two helpful messages.
The validation message appears for valid cells and explains why the cell was not flagged. This validation type is optional in the settings.
The invalid message appears in flagged cells and explains why the cell is flagged. For instance, if a date is in the wrong format, the message will show that the input must be a valid date. Both types of data validation messages will help correct cells.
6. Let's get to work!
Remember, no one should ever have to manually check each and every cell. When used effectively, data validation will flag problematic cells, making mitigation of bad data entry a breeze!
Let's get to work!