Some tools and tricks for cleaning and automating data
VBA will unlock the secret productivity functions within Excel
Excel stands as an extremely powerful program on its own; whether it’s manipulating data or building reports, I use it daily for tasks I need to complete. There are so many hidden functions in Excel that most people don’t know about. Just wait till you discover VBA and how to write a macro – you’ll be blown away by its capabilities.
VBA is a fairly simple scripting language to pick up, and it works right in Excel for automating tedious tasks. If you’re spending all day performing repetitive tasks and you’re thinking to yourself, There must be a better way, you’re probably right. You can often automate these processes with a few lines of code, saving you hours.
There are many things you automate through VBA once the macro is written and a template is built. Here are just a few of the things we have done previously:
- Creating millions of keywords and ads in minutes from a simple list
- Header checking thousands of URLs
- Scraping websites for data (which also requires HTML/CSS knowledge)
But the options really are endless.
Regular Expressions cuts down time spent ‘Find and Replace’-ing
While not technically coding, Regular Expressions (RegEx) has a lot of power for cleaning up messy data. It still requires the same logical thinking in required for coding, and to the untrained eye may look very much the same.
RegEx uses pattern matching within a large amount of data to find and replace very specific text within strings. RegEx is also very useful in conversion tracking, as discussed in Part 2, by matching a large number of different URLs that have the same structure, such as store location pages. It’s much more powerful than the simple “Find and Replace” functionality available through Excel.
One tool I recommend that supports Regex and is a easy to code in is Sublime Text, and is one of the most popular code editors.
If you work in online marketing, I highly recommend learning how to code, or at least understanding how programming works and being able to read a simple script. It’s a great skill that gives you a big picture view of your role in the larger scheme of things. There is an abundance of free (and paid) courses and tutorials online that can teach you and guide you from absolutely zero knowledge to becoming an expert. I have personally utilised many of these, but there is nothing quite as good as getting stuck in once you have the basics.
Maybe you’ll improve so much you can utilise APIs and start building your own custom programs to automate your entire job; maybe you’ll enjoy coding so much you will even want to swap careers; or maybe you’ll just save yourself 5 minutes everyday with an automated report you put together. The choice is up to you, but remember, even kids are learning to code in schools these days. So jump on board or be left behind, because in 15 years time they’ll be wanting your job, and they will take it if you are not prepared.