How To Use Google Sheets For Internet Scraping & Marketing campaign Constructing

Editor’s observe: As 2021 winds down, we’re celebrating with a 12 Days of Christmas Countdown of the most well-liked, useful skilled articles on Search Engine Journal this yr.

This assortment was curated by our editorial staff primarily based on every article’s efficiency, utility, high quality, and the worth created for you, our readers.

Every day till December twenty fourth, we’ll repost probably the greatest columns of the yr, beginning at No. 12 and counting all the way down to No. 1. Our countdown begins right this moment with our No. 5 column, which was initially printed on August 4, 2021.

This how-to information from Andrea Atzori teaches readers methods to make the most of Google Sheets for internet scraping and marketing campaign constructing, with none coding expertise required. 

Get pleasure from!

We’ve all been in a scenario the place we needed to extract information from an internet site in some unspecified time in the future.

When engaged on a brand new account or marketing campaign, you may not have the information or the data out there for the creation of the advertisements, for instance.

In a really perfect world, we’d have been supplied with the entire content material, touchdown pages, and related info we’d like, in an easy-to-import format resembling a CSV, Excel spreadsheet, or Google Sheet. (Or on the very least, offered what we’d like as tabbed information that may be imported into one of many aforementioned codecs.)

However that’s not all the time the best way it goes.

These missing the instruments for internet scraping – or the coding information to make use of one thing like Python to assist with the duty – might have needed to resort to the tedious job of manually copying and pasting probably tons of or hundreds of entries.

In a current job, my staff was requested to:

• Go to the consumer’s web site.
• Obtain greater than 150 new merchandise unfold throughout 15 completely different pages.
• Copy and paste the product title and touchdown web page URL for every product right into a spreadsheet.

Now, you possibly can think about how prolonged the duty would have been if we’d achieved simply that and manually executed the duty.

Not solely is it time-consuming, however with somebody manually going by that many gadgets and pages and bodily having to repeat and paste the information product by product, the possibilities of making a mistake or two are fairly excessive.

It could then require much more time to evaluate the doc and ensure it was error-free.

There must be a greater means.

Excellent news: There’s! Let me present you ways we did it.

What Is IMPORTXML?

Enter Google Sheets. I’d such as you to fulfill the IMPORTXML operate.

In line with Google’s help web page, IMPORTXML “imports information from any of assorted structured information sorts together with XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.”

Primarily, IMPORTXML is a operate lets you scrape structured information from webpages — no coding information required.

For instance, it’s fast and simple to extract information resembling web page titles, descriptions, or hyperlinks, but in addition extra complicated info.

How Can IMPORTXML Assist Scrape Parts Of A Webpage?

The operate itself is fairly easy and solely requires two values:

• The URL of the webpage we intend to extract or scrape the data from.
• And the XPath of the component during which the information is contained.

XPath stands for XML Path Language and can be utilized to navigate by components and attributes in an XML doc.

For instance, to extract the web page title from https://en.wikipedia.org/wiki/Moon_landing, we’d use:

=IMPORTXML(“https://en.wikipedia.org/wiki/Moon_landing”, “//title”)

This can return the worth: Moon touchdown – Wikipedia.

Or, if we’re searching for the web page description, do this:

=IMPORTXML(“https://www.searchenginejournal.com/”,”//meta[@name=’description’]/@content material”)

Here’s a shortlist of a few of the most typical and helpful XPath queries:

• Web page title: //title
• Web page meta description: //meta[@name=’description’]/@content material
• Web page H1: //h1
• Web page hyperlinks: //@href

See IMPORTXML In Motion

Since discovering IMPORTXML in Google Sheets, it has actually turn out to be one in every of our secret weapons within the automation of lots of our each day duties, from marketing campaign and advertisements creation to content material analysis, and extra.

Furthermore, the operate mixed with different formulation and add-ons can be utilized for extra superior duties that in any other case would require subtle options and improvement, resembling instruments in-built Python.

However on this occasion, we’ll take a look at IMPORTXML in its most elementary type: scraping information from an internet web page.

Let’s take a look at a sensible instance.

Think about that we’ve been requested to create a marketing campaign for Search Engine Journal.

They want us to promote the final 30 articles which were printed below the PPC part of the web site.

A fairly easy job, you may say.

Sadly, the editors usually are not capable of ship us the information and have kindly requested us to check with the web site to supply the data required to arrange the marketing campaign.

As talked about at first of our article, a technique to do that can be to open two browser home windows — one with the web site, and the opposite with Google Sheets or Excel. We’d then begin copying and pasting the data over, article by article, and hyperlink by hyperlink.

However utilizing IMPORTXML in Google Sheets, we will obtain the identical output with little to no threat of constructing errors, in a fraction of the time.

Right here’s how.

Step 1: Begin With A Contemporary Google Sheet

First, we open a brand new, clean Google Sheets doc:

Step 2: Add The Content material You Want To Scrape

Add the URL of the web page (or pages) we need to scrape the data from.

In our case, we begin with https://www.searchenginejournal.com/class/pay-per-click/:

Screenshot taken from Google Sheets, July 2021

Step 3: Discover The XPath

We discover the XPath of the component we need to import the content material of into our information spreadsheet.

In our instance, let’s begin with the titles of the most recent 30 articles.

Head to Chrome. As soon as hovering over the title of one of many articles, right-click and choose Examine.

Screenshot from SearchEngineJournal.com, July 2021

This can open the Chrome Dev Instruments window:

Screenshot from SearchEngineJournal.com, July 2021

Be sure that the article title continues to be chosen and highlighted, then right-click once more and select Copy > Copy XPath.

Step 4: Extract The Knowledge Into Google Sheets

Again in your Google Sheets doc, introduce the IMPORTXML operate as follows:

=IMPORTXML(B1,”//*[starts-with(@id, ‘title’)]”)

A few issues to notice:

First, in our system, we’ve got changed the URL of the web page with the reference to the cell the place the URL is saved (B1).

Second, when copying the XPath from Chrome, this can all the time be enclosed in double-quotes.

(//*[@id=”title_1″])

Nevertheless, so as to be sure that it doesn’t break the system, the double quotes signal will should be modified to the only quote signal.

(//*[@id=’title_1’])

Be aware that on this occasion, as a result of the web page ID title adjustments for every article (title_1, title_2, and many others), we should barely modify the question and use “starts-with” so as to seize all components on the web page with an ID that incorporates ‘title.’

Here’s what that appears on the Google Sheets doc:

Screenshot taken from Google Sheets, July 2021

And in only a few moments, that is what the outcomes appear like after the question has been loaded the information onto the spreadsheet:

Screenshot taken from Google Sheets, July 2021

As you possibly can see, the record returns all articles which are featured on the web page that we’ve got simply scraped (together with my earlier piece about automation and methods to use Advert Customizers to Enhance Google Adverts marketing campaign efficiency).

You’ll be able to apply this to scraping every other piece of data must arrange your advert marketing campaign, as properly.

Let’s add the touchdown web page URLs, the featured snippet of every article, and the title of the creator into our Sheets doc.

For the touchdown web page URLs, we have to tweak the question to specify that we’re after the HREF component connected to the article title.

Subsequently, our question will appear like this:

=IMPORTXML(B1,”//*[starts-with(@id, ‘title’)]/@href”)

Now, append ‘/@href’ to the top of the Xpath.

Screenshot taken from Google Sheets, July 2021

Voila! Immediately, we’ve got the URLs of the touchdown pages:

Screenshot taken from Google Sheets, July 2021

You are able to do the identical for the featured snippets and creator names:

Screenshot taken from Google Sheets, July 2021

Troubleshooting

One factor to watch out for is that so as to have the ability to totally increase and fill within the spreadsheet with all information returned by the question, the column during which the information is populated will need to have sufficient cells free and no different information in the best way.

This works in an identical technique to after we use an ARRAYFORMULA, for the system to increase there have to be no different information in the identical column.

Conclusion

And there you’ve a totally automated, error-free, technique to scrape information from (doubtlessly) any webpage, whether or not you want the content material and product descriptions, or ecommerce information resembling product value or transport prices.

In a time when info and information may be the benefit required to ship higher than common outcomes, the power to scrape internet pages and structured content material in a straightforward and fast means may be priceless. In addition to, as we’ve got seen above, IMPORTXML may also help to chop execution occasions and scale back the possibilities of making errors.

Moreover, the operate is not only an awesome instrument that may be completely used for PPC duties, however as an alternative may be actually helpful throughout many various tasks that require internet scraping, together with search engine optimisation and content material duties.

2021 SEJ Christmas Countdown:

Featured picture: Aleutie/Shutterstock