Walkthrough: 3 Ways Social Marketers Can Banish Data Entry From Their Workflow
By: Alex Shultz, SureOak
December 26, 2019
About one billion people use spreadsheet tools for data entry. That includes marketers, who employ some variant on Excel or Google Sheets whether they’re organizing prospects, laying out content plans, putting together reports or listing SEO keywords.
But in today’s automated world, there is a solution for regaining the hours that marketers lose on manual data entry: data scraping.
What is Data Scraping?
Data scraping is the process of importing information to a spreadsheet from a website. It’s an efficient way to gather data from the web. Some marketing uses include:
- Comparing competitors’ pricing or content
- Finding sales leads
- Conducting market research
- Sending product data to an e-commerce site (e.g., Google Shopping)
- Retrieving product descriptions or images
- Providing real-time analytics
…And that’s only the tip of the iceberg. Any time you need to move data from one location to another, web scraping becomes helpful.
Getting Started With Web Scraping
There’s a simple spreadsheet function for retrieving data—and it doesn’t require a technical skillset or extra training to use.
To mine your data, you can either use Web Queries in Microsoft Excel or the ImportXML function in Google Sheets.
Scraping Data With Google Sheets
Here’s how to use ImportXML to import data from a webpage directly into a spreadsheet. It looks a bit complicated at first, but once you get the hang of it, it’s pretty magical.
Let’s say I’m looking at all the upcoming speakers at The Social Shake-Up Show.
This page gives me the names, positions, companies, and social media profiles for each speaker. These are really influential people, so I’m mostly interested in those LinkedIn profiles.
I could just click on each of the links, but let’s say I want to gather them in a centralized spreadsheet. Instead of laboriously copy/pasting each name into a spreadsheet, I can allow my computer to do it for me in about 3 seconds.
First, I need to look at the page source to find where those names are placed. In Google Chrome, I right-click on a name and click on “inspect.”
All of the speakers’ names are housed in a pair of <h3> tags, and they all seem to have the tag <a class=”speaker”>. We’ll take both of these things into account as we grab the information.
Here’s the template:
=importxml(“URL”, “//XPATH QUERY”)
To fill this in, I’ll use the URL of the webpage I’m trying to scrape, and then I’ll tell the function what information I want it to grab.
=importxml(“https://www.socialshakeupshow.com/2020-speakers”, “//h3/a[@class=’speaker’]”)
When I put this in, here’s what comes up.
Sweet! I’ve pulled all of the speaker names; now I’ll grab all of the LinkedIn profile links.
=importxml(“https://www.socialshakeupshow.com/2020-speakers”, “//a/@href[contains(., ‘linkedin’)]”)
When I plug this into the second column, it’ll tell the system to grab all links (marked with href) that contain the word ‘linkedin.’ Here’s what I got.
Awesome! I’ll clean this up now.
Boom! I’ve retrieved this information far quicker than if I had done it manually. Marketers can use that same formula to gather myriad social media profile links for any other publicly available data under the sun.
If you’re using Excel, you can use Web Queries.
- Select Data > Get External Data > From Web.
- You will see a new browser window with the name “New Web Query.”
- Type the web address in the bar.
- That page loads and highlights yellow icons with the data and tables.
- Select what you want.
- Click Import.
You will see the data entered into your Excel worksheet. It will be arranged perfectly in the columns and rows.
You can also grab the data with Excel VBA. The Visual Basic for Application is also known more commonly as Macros. It’s not for the faint of heart or those without technical knowledge. To give it a try, follow these steps.
- Enable the Developer tab by right-clicking on File > Customize Ribbon > Check Developer tab
- Set up the layout
- Build or use VBA script to send out requests and receive data
It’s worthwhile to learn Excel VBA code if you want to make Excel web scraping easier. There’s plenty of information available on writing VBA code if you want to go deeper.
You can also learn more about web scraping with Excel by watching helpful YouTube videos, such as this one.
https://www.youtube.com/watch?v=dAjw9Vu8wYg
Collecting Information with Data Scraping Tools
Most people find it’s easier to mine data with a web tool. Here are a few suggestions:
A Regulatory Warning
It’s important to note that, while web scraping is a fabulous tool to gain an advantage over the competition, it is technically illegal in some situations. You must consult the data source’s terms of service (TOS) before getting started to ensure you don’t do anything wrong.
