To see what Databox can do for you, including how it helps you track and visualize your performance data in real-time, check out our home page. Click here.
Data is the lifeblood of effective marketing, essential for solving problems and identifying opportunities.
“You can’t manage what you don’t measure.” This quote, commonly attributed to management guru Peter Drucker, is especially relevant in marketing products and services. For many professionals, poring over analytics dashboards and campaign reports is a way of life.
That’s where the humble spreadsheet comes in. This handy little tool combines simple database functions with formulas and mathematical operations. Since the introduction of VisiCalc in 1979, marketers have relied upon spreadsheets to turn mountains of numbers into useful information.
We asked marketers to share their Google Sheets secrets: useful formulas, effective integrations, and pro tips for getting the most out of this versatile platform.
A recent Databox roundup of the best marketing tools included recommendations for 80 products and services. They’ve all found appreciative audiences among professional marketers. But when it comes to general versatility and all-around usefulness, you can’t beat Google Sheets.
Here is what marketers have to say about their favorite Swiss Army knife tool.
Looking for specific info? Jump to:
One secret of the Spreadsheet’s power is that you can write formulas to sort, summarize, and analyze data in any way you wish. From marketing and sales to project management and finance operations, Google Sheets has a rich set of functions for creating custom applications and solving ad hoc problems. But, let’s start from the basics:
Worksheets are great for storing small databases, but when you’ve got a lot of data, finding and organizing a subset can be a pain.
Maven Wave’s Agnes Horvath has a clever solution for creating organized subsets of larger datafiles. She uses the Google Sheets QUERY function: “You can easily select one or more criteria in a dropdown list to build a sublist of users,” she says. The function looks like this:
=QUERY(Sheet1!A1:G7,”SELECT A,B,C WHERE E CONTAINS ‘”&A3&”‘ AND F CONTAINS ‘”&B3&”‘ AND G CONTAINS ‘”&C3&”‘ ORDER BY A”,1)
Google Sheets conditional formatting is recommended by Adam Jansen from Cheap SSL Security.
“Conditional formatting is a great hack,” he says. “Conditional formatting adjusts the text format or background color of any cell, row, or column based on criteria you define.” Just select “Format > Conditional formatting” from the Sheets menu and Google will walk you through the process.
These simple cases are just the beginning. At TechACake, Darina Lynkova has used conditional formatting to turn a Google worksheet into a homespun content management system. Lynkova tracks blog posts in a worksheet and color codes the titles to indicate status: assigned, received, edited, published, and so on.
Lastly, Expert Online Business Management’s Geoffrey Swetz uses a simple formula to make sure no one overlooks client expenses that are running too high:
=if((CELL1)-CELL2>=0, “UNDER”, “OVER”)
The formula isn’t complicated, but it does the job, Swetz says: “When client expenses are over budget, it’s easy to see.”
Looking for contact information for a single customer in a huge worksheet? Deepak Shukla of Pearl Lemon Leads says the VLOOKUP function may be just what you need.
“VLOOKUPs are used when you have a piece of data you know—a client’s name, for instance—and you want the client’s phone number or email address.”
You can leave a blank line at the top of the sheet to hold the VLOOKUP:
=VLOOKUP(A2, A3:ZZ999, 2, false)
Copy the formula into B2, B3, B4…across as many columns as you like.
“Just type the client’s name into A2 and you’ll get the entire row with all the data,” Shukla says. “VLOOKUP saves a lot of time.”
According to marketers surveyed, VLOOKUP is also the most popular Google Sheets formula. Right after VLOOKUP, the most useful formulas are COUNTIF and IMPORTRANGE.
Automate.io’s Saurabh Wani regularly imports hundreds of names from his email finding tool. The problem is that the tool puts the first name and surname into the same cell.
“I use SPLIT to divide the data into multiple cells so I can address prospects by first name in email campaigns,” Wani says. The SPLIT function has lots of optional parameters, but this is enough to get you started. If the full names are in Column A, just paste this formula into Column B:
=SPLIT(A1,” “)
The first names will appear in Column B and the surnames in Column C.
“One function I can’t live without is ISEMAIL,” says Lauren Hawksworth of Founders Legal. “This simple function checks whether an email address is valid or not.”
The function is simple to use. If email addresses are stored in Column A, copy this formula into Column B:
=ISEMAIL(A1)
Cells in Column B will say TRUE if the email address is formatted correctly or FALSE if it doesn’t recognize the text as a valid email address.
“Most people don’t know you can translate languages inside Google Sheets,” says HVACJudge’s David Sheppard.
Try it for yourself:
=GOOGLETRANSLATE(“je ne sais quoi”, “fr”, “en”)
HVACJudge works with clients across multiple countries and languages, so Sheppard relies on this handy function to help generate appropriate text during mail merge operations.
The world is getting smaller every day—especially since the global pandemic has accelerated the trend toward remote work. It’s increasingly common for team members, clients, and suppliers to be distributed across multiple countries and continents.
SEO specialist Josien Galama has found a clever way to make the world a little smaller.
Multiple currencies can be a problem with distributed operations, Galama notes. “My business runs on euros (EUR), while my Bali-based suppliers accept only Indonesian Rupiah (IND). Others prefer American dollars (USD).” Galama uses the Google Finance CURRENCY function to make all the financial figures clear to everyone:
=GOOGLEFINANCE(“Currency:USDEUR”)
“With this formula, I have a better and faster understanding of costs and value,” Galama says.
Do you wonder how many clients’ contracts expire in the next 90 days or how many of your newsletter subscribers are in France? Nick Chernets of Data for SEO relies on the COUNTIF function to answer these and countless other questions.
“I oversee lots of complex sheets that must contain precise, correct information,” he says. “COUNTIF helps me check all cells for different criteria and verify totals in data we’ve prepared for a report. I use COUNTIF whenever needed and I rely on it for final data checkups as well.”
If column G contains your subscribers’ home countries, you can get a count of those in France with this function:
=ARRAYFORMULA(COUNTIF(G2:G,”France”))
In this case, the COUNTIF function is contained within ARRAYFORMULA, which instructs COUNTIF to report results from the starting cell, G2, to the bottom of Column G.
According to our research, Google Sheets is an indispensable tool for marketers. Over 56% experts we surveyed, marketing is their primary use case for Google Sheets.
It’s no wonder because even without complex formulas, there are lots of ways Google Sheets can help you access, analyze, visualize, and share data.
When it comes to content marketing, Google Sheets can help you build a content plan, tack the results of some of your campaigns, and even scope out the competition.
Stacy Caprio of Conversiono recommends Google Sheets for planning and tracking content marketing efforts.
“I manually pull keyword data from Google Keyword Planner,” she says. “Then I use Google Sheets to track target keywords, publish dates, number of internal and external links, keyword competition, and page ranks.”
“We all deal with limits regarding how many words or letters we can use in ad placements, titles, and other content,” says Matt Lally of TheGiftYak. “The easiest way to programmatically ensure that your text complies with character limits is to use the LEN function, which tells you how long the text is.”
If your titles are in Column A, just put =LEN(A1) in Column B. “Voila!” Lally says. “You’ve saved yourself a lot of time and headaches.”
Referral Rock’s Megan Mosley uses Google Sheets to create comparison tables when testing apps for buyer’s guides. “A spreadsheet makes it easy to compare data when I’m writing an article on tools,” she says. “I even create columns that let me know whether or not I have reached out to them on social media after publishing the content.”
“Google Sheets is great for things like competitor research, content gap analysis, and content topic planning,” says Kyle Menchaca of WorkWave.
“My process is pulling the top articles from competitors’ websites and blogs using a tool like Ahrefs or BuzzSumo. I add columns to the spreadsheet for organic traffic, referring domains, content length, social shares, and publish dates.”
Menchaca then sorts the list by organic traffic rates, which gives him a quick view of his competitors’ best content and most popular topics. “I generally add my own site to the mix,” he says, “to get a visual of how we stack up.”
Editor’s note: Another way to scope out the competitors is with the AccuRanker: Competitors Overview Dashboard that focuses on “Share of Voice”, or how you compete with competitors over popular keywords.
“I use Google Sheets to see if changes I make to content are effective in boosting metrics like on-page click-through rates, time on page, and bounce rates,” says Best Company’s Chad Zollinger.
“Since our landing pages aren’t the final destination on our site, we want users to read our content and click through to our pillar pages. So I’ve recently been testing the effectiveness of shorter paragraphs, bolded sentences, and higher CTAs.”
Zollinger tracks these on Google Sheets, which allows him to assess the effectiveness of each change.
“By the way, I’ve found that shorter paragraphs are the most effective of the three changes in boosting time on page and reducing bounce rates,” Zollinger says.
“I manage about two dozen social media accounts for my clients, and I wouldn’t be able to do it without Google Sheets,” says Ana Cvetkovic of BLOOM Digital Marketing.
“There are a lot of social media planning and scheduling tools, but none of them are as flexible or all-encompassing as a simple, collaborative spreadsheet through Google Sheets,” she says.
Cvetkovic has created a template that she uses with clients to plan social content for the month. The template includes links to images stored in a Google Driver folder. “I color-code posts as I’m working on them—yellow for works in progress, green for scheduled posts, and red for posts that require attention,” she says.
Blog posts are great for enticing readers to convert to newsletter subscribers, but it is often hard to tell which articles are most effective at generating subscriptions.
“The conversion rate per blog post rate is calculated using data from two reports: Reverse Goal Path and All Pages,” observes Orbit Media Studios’ Andy Crestodina. “To calculate the rate, you divide the conversions from a page that appears as Goal Previous Step – 1 from the pageviews for that page.”
As Crestodina notes, “That’s spreadsheet work.”
Orbit Media uses the Google Analytics add-on for Google Sheets to import data from the two reports. The team then creates a third sheet that matches data using the VLOOKUP function and calculates the conversion rate for each article.
“This is some of the most valuable data imaginable for any content marketer,” Crestodina says. “It shows which pieces of content you should promote most heavily.”
“I use Google Sheets to keep track of prospects that I’ve contacted when promoting content,” says ATYNTK’s Finn Hayden. “It’s much easier for me to check a Google Sheet than it is for me to trawl through my email outbox trying to figure out if I’ve communicated with a prospect previously.”
Nina Krol and the team at Zety also use Google Sheets for outreach and content promotion: “Our researchers have composed a database of leads and contacts for our outreach team in Google Sheets. It allows us to keep track of who we’ve contacted and when,” she says.
“The coolest thing for us is that we’ve connected Google Sheets to our email accounts using Zapier,” Krol says. “Now we can update the status of our outreach email activities and Zapier updates our Google worksheets automatically. This allows us to create quick, detailed reports on our tasks and how successful they were.”
Most PPC and SEO tools are designed to work with spreadsheets. Why? When you’re analyzing thousands of keywords, checking rankings for hundreds of pages of content, or looking for issues in your site’s metadata, a spreadsheet’s ability to sort, filter, and highlight data can save you a lot of time. These disciplines are all about the numbers.
Here are some ways our respondents use Google Sheets to expedite keyword analysis, boost rankings, adjust PPC bids, and identify technical SEO issues.
“We use Ahrefs’ Google Sheets template to identify keyword cannibalization on our website,” says Jim Milan of Auto Accessories Garage.
“We pull in search engine ranking data from Ahrefs and enter it into the template. The template finds pages on our site that are competing with one another for the same keyword,” he says.
“By removing extraneous pages or combining pages with 301 redirects, we are able to reduce keyword cannibalization issues on the site—and boost our organic traffic.”
Andrew McLoughlin of Colibri Digital Marketing uses Google Sheets to find title tags and meta descriptions that are too long or duplicated across multiple pages:
“After running a site crawl for a client, one of the first ways we organize that data is to import the title tags and meta descriptions, then we use Google Sheets to color-code them like a heatmap,” McLoughlin says.
“As they approach the optimal character limit, they gradually shift from green to yellow. They turn red when they exceed the limit. Duplicates show in blue. It’s a great way to take in the whole domain’s meta information at a glance.”
“We use Google Sheets for a lot of things,” says GeekSmash’s Joe Delbridge, “but one that we find particularly useful is to scrub pages for content and then export that information into a Google spreadsheet.”
GeekSmash uses the Google worksheet to analyze the imported data, Delbridge says. “For example, if we want to see if all images have alt attributes on a page with a lot of images, this method reveals that much faster than checking one by one.”
Mxt Media’s T.J. Kelly collects the titles of his blog posts in a Google Sheets worksheet. Kelly tracks competitors’ titles and analyzes them in search of a competitive SEO edge.
Kelly has developed a formula to count the words in each title:
=(len(A2)-len(SUBSTITUTE(A2,” “,””))+1)
“Title length may correspond to search query length,” Kelly explains, “and long-tail queries are a goldmine for organic traffic acquisition.” Kelly adds a title length column to data exported from Google Search Console to identify which long-tail terms are generating the most visibility and traffic.
You may not have access to the data in your sales team’s CRM (and you may not want to open Pandora’s box by asking for it). With Google Sheets, you can avoid the CRM altogether.
Use these ideas to track and nurture leads, then send leads data to the sales team through your CRM.
“We use Google Sheets to assign leads by area code or round-robin,” says Advice Media’s Joe Sloan. “We connect our Zapier account to a spreadsheet that has each area code. When we assign a state to a rep, any area code in that state allows us to automatically assign the lead.”
This is the flow Advice Media uses:
“This may sound confusing to set up,” Sloan says. “And it was. But now we can automatically assign leads to the correct rep—all thanks to Google Sheets.”
“We use Google Sheets as an overlay on top of leads that come in from multiple sources—website forms, emails, chats, and so on—to consolidate leads on a single list,” says Eric Quanstrom of CIENCE. “That list is also updated with subsequent appointments booked with our sales team.”
Quanstrom says the Google Sheets solution allows CIENCE to monitor all contacts with potential customers. “One of the main benefits of using Sheets is that we have multiple inbound SDRs and contributors,” he says. “Sheets offers really advanced collaboration features where you can see who’s logged in to a specific tab—even who’s entering data into a single cell.”
One of the most common ways to use a spreadsheet is for tracking, reporting, and visualizing data. In fact, 72% of marketers say they spend time visualizing the data they store in Google Sheets:
Between its built-in tools and its integration with platforms like Databox, there are lots of ways to transform your spreadsheet’s raw data into valuable insights and accessible reports.
“A great way to use Google Sheets is by connecting it to Google Analytics,” says Jonathan Aufray of Growth Hackers.
“Thanks to the power of the Google Analytics API, you can access all of your website analytics directly in Google Sheets and select exactly what data appears in your spreadsheet,” he says
The integration allows Growth Hackers to create custom dashboards, generate automated reports, and compare Google Analytics statistics to data collected from other sources.
Editor’s note: Use this free Google Analytics Website Engagement dashboard template to track your most important website engagement metrics, from average time on page, bounce rate, and average session duration, to the number of unique visitors to your website and goal conversion rate.
Managing the marketing budget is no simple task, especially when you’ve got a large team working on a variety of campaigns, says Nate Masterson of Maple Holistics.
“We decided to use Google Sheets for our marketing budget,” Masterson says. “It allows each team member to add their expenses on the go, which gives us a constantly updated view of our spending.”
Reports started as printed pages, and we still think of them as static data. Fundera’s Catherine Giese embraces the future by creating dynamic reports that are always based on current data.
“The first sheet is pure data that I can easily look at and analyze,” Giese says. “Each category is automatically updated whenever data is entered into the system. I never have to calculate numbers manually or scroll through endless worksheets to create a report. The numbers I want are on the first sheet whenever I need them.”
Christabelle Tani of Brand chemistry uses linked Google Sheets to maintain identical agency and client reports. “Our clients have their own reporting dashboards in both a custom dashboard software like Databox and Google Sheets,” she says.
“Our hack: We link separate spreadsheets into one big master spreadsheet as separate tabs. Each time we update the individual client spreadsheets, the tabs get updated in our master spreadsheet, eliminating double handling.” It’s a huge time saver!”
At Lean Labs, Google Sheets are the key to tracking clients’ growth rates.
“We chart both the minimum growth rate and the reach goal for organic traffic, leads, and opportunities,” says Lean’s Kevin Barber.
“As a team, this has pushed us to stay laser-focused on the right activities,” he says. “It’s also propelled us a company because we have example after example of 10x growth for our clients, as well as our average growth rate, in an easy-to-understand chart.”
“We like to use Google Sheets to communicate with certain clients,” says ClearPivot’s Chantelle Stevenson.
“Links to photos that will go live on social media for a certain month are posted for clients to review, and notes are highlighted in yellow for items that need to be altered. We use it to plan out our month quite often via social media content and premium content.”
“It’s a wonderful tool to use to communicate with clients outside of our organization and have them see our work before it actually gets published. Every update is seen by all parties, and links can be updated accordingly after they’re dropped into their corresponding Google Drive folders.”
Spitfire Inbound’s Sarah Mills also recommends Google Sheets for collaborating with clients:
“We use Google Sheets throughout our business, but I’d say the one I would highlight would be the use of Sheets by our content team to build content strategies. It means that all information can be depicted in text format and graphically, which enables easy collaboration with clients.”
The IMPORTRANGE function has simplified life for Brice Gump of Major Impact Media. “We used to spend a lot of time manually moving data from one worksheet to another,” he says. “Or we’d copy and paste data from one worksheet to another to perform a different kind of analysis.”
That all changed when the Major Impact team discovered IMPORTRANGE. “The function lets you connect spreadsheets no matter where they’re located,” Gump says. “It has saved us hours every month with analysis and reporting. IMPORTRANGE is a game changer.”
Inspired yet? Those are just the beginning. Here are some more inventive ways marketing pros have applied the power of Google Sheets.
Google Sheets can be as simple as you want or as complex as you need.
There are tons of apps, plugins, and add-ons that make Google Sheets endlessly extensible by connecting it with the other tools you use to do your work. And most marketers are taking advantage of that, with 79% reporting that they pull data from other tools into Google Sheets:
And while most marketers pull data from only one other app into their worksheets, many use Google Sheets to collect data from 11 or more sources:
What tools are they pulling data from? The long list includes everything from reporting tools like Google Analytics to accounting tools like QuickBooks:
“When it comes to marketing analytics and Google Sheets, there’s no limit to what you can do to improve workflows and the accessibility/usability of campaign and audience data,” says Andrew Becks of 301 Digital Media.
“Sadly, not all platforms and tools seamlessly integrate with Google Sheets, so we sometimes have to get creative.”
“The hackiest thing I’ve done lately is leveraging Mailparser to download CSV files containing analytics that are emailed to me daily, parse through the email attachments’ contents, and upload the details into Google Sheets.”
“In Google Sheets, the data is able to be further manipulated, and a daily running log of the relevant detail is created,” Becks says.
Gray Group International’s Alejandra Melara offers another solution: “We’ve merged Google Sheets with Zapier because it lets us automate the import and export of data in a much more results-driven way. This allows us to save money and time by automating processes.”
Isabella Federico of WeBizz says her agency even creates Google Sheets scripts for customers:
“If they frequently need to update some information on their websites, we help them do this through pivot tables in Google Sheets that are embedded on their sites. Thanks to a proprietary script we developed, they can add or edit the information directly from the spreadsheet with no need to access the admin panel of their sites.”
“This is also a really collaborative way to create and edit content since multiple people can work on it—both inside and outside of the company.”
And Louis John Murray of Futurety uses a variety of tools and custom solutions:
“We use Google Sheets as a unified repository to power our digital reporting in tools. Also, there are a few existing add-on services that allow users to build interactive queries for common tools like Google Analytics or Facebook ads.”
“We take it a step further by building automated API extracts for platforms like Yext, Salesforce, or HubSpot. Automating the data extract process allows us to focus on proactive analysis and drastically decreases the amount of time spent on exporting and reporting in a given month.”
“The ability to analyze and blend data from so many disparate sources allows us to present our customers with a complete picture of digital performance across a variety of paid and unpaid channels,” Murray says.
VisiCalc was called the “first killer app of the computer era,” and it’s easy to understand why. Forty years later, generations of business professionals still rely on worksheets to keep eye on operations.
As Eric Quanstrom of CIENCE says: “Despite the negative perception of ‘just a spreadsheet,’ there are few other tools as evolved for real-time collaboration as Google Sheets. And since time is of the essence with inbound leads, using the appropriate tool for the job is actually quite crucial.”
Are you maximizing your business potential? Stop guessing and start comparing with companies like yours.
At Databox, we’re obsessed with helping companies more easily monitor, analyze, and report their results. Whether it’s the resources we put into building and maintaining integrations with 100+ popular marketing tools, enabling customizability of charts, dashboards, and reports, or building functionality to make analysis, benchmarking, and forecasting easier, we’re constantly trying to find ways to help our customers save time and deliver better results.
Hey, we’re Databox.Our mission is to help businesses save time and grow faster. Click here to see our platform in action.
Get practical strategies that drive consistent growth