Spreadsheets Don't Have to Suck
Let's talk about how to make managing your marketing data in spreadsheets much less of a headache.
Thanks for subscribing to Work the Funnel, a newsletter by marketer Chantelle Marcelle (me!) that highlights best practices and trends in marketing.
A big welcome to this week’s 112 new subscribers — it’s great to have you here.
A quick look at today’s issue:
5 tips for managing your marketing spreadsheets
A roundup of interesting stuff from around the web
Clever campaign of the week
I cringed a little writing the subject line of this email.
My mom would hate it — specifically the word selection.
But sucks was the first thing that came to mind when thinking of spreadsheet management.
We marketers rely on spreadsheets for a variety of use cases, including:
Combining data from different systems
Drilling into multi-channel performance metrics
Creating budget and spend trackers
I like using spreadsheets over other tools for the straightforwardness of the experience.
No frills; just focusing on the data.
But not going to lie — they present challenges, especially for those who feel less comfortable with numbers and graphs.
I gathered 5 solid tips for improving how you manage data in spreadsheets, along with the help of my friends over at Coefficient. (They offer great tools to help marketers maximize their use of Google Sheets. They’re also doing a $2000 giveaway if you think you’re already pretty spreadsheet-savvy. 💰😉)
Without further ado, 5 tips for managing marketing data in spreadsheets.
1. Harness Data Connectors
The copy-and-paste method gets old pretty quickly.
When I managed paid channels for a popular ecommerce brand, I’d sometimes bounce between platforms to grab different nuggets of data.
Completely inefficient.
With data connectors and integrations, your top tech platforms (HubSpot, Salesforce, Meta Ads Manager, etc) connect directly to your spreadsheets. Then you automatically pull data without wasting resources.
2. Perform Data Cleansing
Data cleansing lets you trust that you’re using and sharing the best quality version possible. Spreadsheet platforms such as Google Sheets and Excel feature built-in functionality to help with this.
Some key ways you can clean data in your spreadsheet:
Delete duplicate data - Spreadsheets make identifying and deleting dupes straightforward. You can employ conditional formatting or the UNIQUE function to isolate and delete duplicates.
Format data - Use the TRIM function in Google Sheets and Excel to remove unnecessary spacing in contact names, addresses, and other marketing data points.
Verify email addresses - Marketing data is rife with email addresses, and spreadsheets make it easy to weed out the invalid ones. Deploy the ISEMAIL function on any email to confirm its validity.
Correct dates - You can correct dates in Excel and Google Sheets by splitting text into columns.
3. Search for Free Templates
A wide variety of free templates are available for Google Sheets and Excel.
Use them! You’ll save time on figuring out design and presentation.
They cover a broad range of marketing use cases, like ad budgets, website performance, lead tracking, and social media reports.
And, if you’re using a data connector, you can stream real-time marketing data directly into the template to operate off the freshest insights possible.
4. Create Sorted Sublists
Spreadsheets can store pretty large volumes of marketing data, but locating and organizing the data is often tricky.
By combining the QUERY function and dropdown lists, you can create a sublist of marketing data based on certain criteria.
This way, your spreadsheet stores a larger database while still giving you the ability to narrow down quickly to specific metrics.
5. Build Your Own Search Engine — Easily
Marketing spreadsheets can become massive and overwhelming.
I remember wanting to toss my laptop out the window when trying to upload and analyze years of customer data into Google Sheets.
You should know that spreadsheets come with a pre-built search engine type function called VLOOKUP. This makes it much easier for marketers to find data within a spreadsheet.
VLOOKUP is a search function that finds data in one table and uses this data within another table. The tables can occupy the same sheet, a different tab, a separate sheet, or a spreadsheet file. The function searches a specified column vertically for a search key (hence VLOOKUP), then returns the value from the same row.
Ok, that was a little complicated.
Let’s break down the basics of a VLOOKUP formula.
First, here’s an overview of the standard syntax:
VLOOKUP(search_key, range, index, [is_sorted])
search_key is the item or value you want to find. For instance, if you want to find a prospect’s email address, the search_key could be the prospect’s name.
range refers to the cell range you want to include in the VLOOKUP search.
index is the column containing the value you’re looking for. For instance, if you want to search for an address in a table, this would be the column number containing the addresses. The index value should be between 1 (first column) and the total number of columns in the table.
is_sorted is true or false (true by default) and is used to indicate whether the index column has been sorted.
So you can use VLOOKUP to create mini-search engines in your company’s spreadsheets to find the data you need faster.
💰👉🏽 Now Show Me the Money
If you want to have a little fun with this, I recommend you check out Cofficient’s Spreadsheet Escape Room.
It’s an interactive game with a fabulous prize for the winner. Huge thanks to them for sharing this with me! 🙏
Other Interesting Things From Around the Web
I’ve visited Target a bajillion times this holiday season. 🛍️ Their evolution over the years continues to wow me, 📈 from their partnerships with companies like Ulta to their acquisition of mobile commerce app Shipt. They went from being just another neighborhood store to now becoming a community commerce center. Here’s a look at how they’re strategizing to replace the role previously held by shopping malls.
Want more spreadsheet stuff? 📊📈 Check out these templates from spreadsheet expert Blake Burge for everything from tackling debt to holiday gift planning.
Remember a few years back when everyone started quoting new neuroscience research that said our brains 🧠 don’t fully develop until we’re 25 years old? Apparently that oversimplifies the topic. Dig into this article if you want to geek out about the beauty and complexity of our brains.
The ongoing Twitter 🐦 saga looks more and more like an attempt to send a political and cultural message. As soon as I hear words like “woke” or “free speech” thrown around facetiously, my guard goes up about the intention of the presenter. An interesting article offering one perspective about how executives seem to be excited about The Musk Way™ of doing business.
What’s the point of building a personal brand? The amount of time and effort it takes to consistently create content can feel daunting. Check out this example of why it can be beneficial, despite the work.
Clever Campaign of the Week
Thanks for reading again this week!
Next time we’ll focus on the best distribution methods to peppermint-spice up your growth strategy.
Enjoy your week, and don’t forget to take some time for self-care during this crazy, hectic time of year.
P.S. The next season of my (much delayed) podcast releases soon. Check it out and subscribe here to be notified. Or join me on either YouTube or Instagram for more regular content.