As a content marketer, I often use Google Spreadsheets to collect and sort data. There are some formulas that you can use to get your job done faster. In this article, I will show 6 ways to use Google Sheet like a Pro for marketing.
Extract Domain from URL
When I want to do email outreach, I would extract a few URLs from Google Search, Then I want to get the root domain and then you can get Moz domain ranks, Alexa ranking, or even contact info. In that case, you can use the following formula to extract domain:
=trim(REGEXEXTRACT(REGEXREPLACE(REGEXREPLACE(A2,”https?://”,””),”^(w{3}\.)?”,””)&”/”,”([^/?]+)”))
Find Contact Info from Multiple Domains
You can get contact emails for email outreach from multiple domains in a sheet. To do that, you can get Hunter for Sheet Speadsheet Addon. This addon can scrape the contact emails from multiple domains.
Once you get the Hunter for Sheets, you need to also register a free account from Hunter. You need also to get a Hunter API that needs to be used in the spreadsheet. For a free account, you can scrape 50 domains. For each domain, you can get 10 or more emails.
From the Spreadsheet, you can click Add-ons and select Hunter for Sheets. Under Domain Search, select Bulk from the Hunter screen and click Launch the bulk search. This will create a new sheet and get all the available email contact info.
Merge Two Sheets
Now, you have a separate sheet with contact information, so you may also want to merge these two sheets together. You can use VLOOKUP to search a range using a search key and then return matching values from a specific cell in that range, which can be another sheet.
Syntax: =VLOOKUP(search_key, range, index_key)
By default, the new sheet created by Hunter would be named as “Bulk Domain Search (50 domains)”
You can use the following formula:
VLOOKUP(A2:A,’Bulk Domain Search (50 domains)’!A:D,2)
To fill out all the data without dragging it down, you can use this formula:
=IFERROR(ARRAYFORMULA(VLOOKUP(A2:A,’Bulk Domain Search (50 domains)’!A:D,2,false)),””)
You can check the example sheet to see all the details. As you can see from the Example sheet, the index_key 2 is to get the emails. You can use index_key 3 to get First Name and 4 for the Last Name.
Free Mail Merge
With all the information collected in one Spreadsheet, now you can also create mail merge to send multiple emails with Google Sheets. To do that, you just need a custom script provided by Google Experts.
If you are intimidated by the codes, you can also use a Google Sheets Add-on called Mail Merge with Attachments to do mail merge.
It is also a free Add-on. You can check this post to see the detailed steps.
Scrape YouTube Data
You may also want to contact YouTube influencers for influencer marketing. I also created a spreadsheet to scrape data from YouTube. You can enter a search term and then this sheet will bring you 300 hundred results (you can add more).
For each result, there is channel title, channel URL, subscriber count, country, channel description, and email (if it is in the channel description). You can also add more info like views count, videos counts, etc.
To use this spreadsheet, you just make a copy of it and replace the API key with your own API key. You can refer to this post to how I created this spreadsheet from zero.
Export Google Analytics Data to Google Sheets
Google provides a free Google Sheet Add-on that allows you to pull data from Google Analytics to Google Sheets. Once installed, you can select this add-on to create a task to import Google Analytics data. In addition, you can also create a schedule to run this report.
After that, you can use the Vlookup formula addressed before in this post to add the data to the proper location. With the schedule enabled, these data will update automatically.
Conclusion
Google Sheets is a powerful tool for content marketers that can help them collect and sort data. By using some formulas, add-ons, and scripts, you can use Google Sheets like a pro and save time and effort. Google Sheets can also integrate with other tools and platforms to enhance your marketing campaigns.