Use Google Sheets for Net Scraping & Marketing campaign Constructing

Red Mention

We’ve all been in a scenario the place we needed to extract information from a web site sooner or later.

When engaged on a brand new account or marketing campaign, you won’t have the info or the knowledge accessible for the creation of the adverts, for instance.

In a super world, we might have been supplied with the entire content material, touchdown pages, and related data we’d like, in an easy-to-import format reminiscent of 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 way in which it goes.

These missing the instruments for net 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 presumably tons of or 1000’s of entries.

In a current job, my workforce was requested to:


Proceed Studying Under

  • Go to the consumer’s web site.
  • Obtain greater than 150 new merchandise unfold throughout 15 totally 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 completed simply that and manually executed the duty.

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

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

There needs to be a greater method.

Excellent news: There may be! Let me present you the way we did it.


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

In keeping with Google’s support page, IMPORTXML “imports information from any of varied structured information sorts together with XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.”


Proceed Studying Under

Basically, IMPORTXML is a perform permits you to scrape structured information from webpages — no coding information required.

For instance, it’s fast and simple to extract information reminiscent of web page titles, descriptions, or hyperlinks, but additionally extra complicated data.

How Can IMPORTXML Assist Scrape Components of a Webpage?

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

  • The URL of the webpage we intend to extract or scrape the knowledge from.
  • And the XPath of the ingredient during which the info is contained.

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

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

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

It will return the worth: Moon touchdown – Wikipedia.

Or, if we’re in search of the web page description, do this:

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

SEJ Home Page Meta Description Pulled with IMPORTXML.

Here’s a shortlist of a number of the commonest 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 really turn out to be considered one of our secret weapons within the automation of a lot of our day by day duties, from marketing campaign and adverts creation to content material analysis, and extra.

Furthermore, the perform 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, reminiscent of instruments in-built Python.

However on this occasion, we are going to have a look at IMPORTXML in its most elementary kind: scraping information from an online 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 have been revealed beneath the PPC part of the web site.


Proceed Studying Under

A fairly easy job, you would possibly say.

Sadly, the editors aren’t capable of ship us the info and have kindly requested us to check with the web site to supply the knowledge required to arrange the marketing campaign.

As talked about at the start 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 might then begin copying and pasting the knowledge over, article by article, and hyperlink by hyperlink.

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

Right here’s how.

Step 1: Begin with a Recent Google Sheet

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

Start with a Blank Google Sheets Document.

Step 2: Add the Content material You Have to Scrape

Add the URL of the web page (or pages) we wish to scrape the knowledge from.


Proceed Studying Under

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

Add the URL of the Page You Want to Scrape.

Step 3: Discover the XPath

We discover the XPath of the ingredient we wish to import the content material of into our information spreadsheet.

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

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

Open the Chrome WebDev Tool.

It will open the Chrome Dev Instruments window:

Find and Copy the XPath Element You Want to Extract.

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


Proceed Studying Under

Step 4: Extract the Knowledge Into Google Sheets

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

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

A few issues to notice:

First, in our formulation, we now have 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, it will all the time be enclosed in double-quotes.


Nonetheless, to be able to make sure that it doesn’t break the formulation, the double quotes signal will must be modified to the only quote signal.


Word that on this occasion, as a result of the web page ID title modifications for every article (title_1, title_2, and so on), we should barely modify the question and use “starts-with” to be able to seize all parts on the web page with an ID that comprises ‘title.’

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

An example of IMPORTXML.

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

Titles Imported in Google Sheets.

As you possibly can see, the checklist returns all articles which are featured on the web page that we now have simply scraped (together with my earlier piece about automation and find out how to use Ad Customizers to Improve Google Ads campaign performance).


Proceed Studying Under

You possibly can apply this to scraping some other piece of knowledge 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 ingredient hooked up to the article title.

Subsequently, our question will appear like this:

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

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

Import the Article Links.

Voila! Immediately, we now have the URLs of the touchdown pages:

Articles and URLs Imported in Google Sheets.

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

All the Data is Scraped and Imported in Google Sheets.


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


Proceed Studying Under

This works in an identical option to once we use an ARRAYFORMULA, for the formulation to increase there should be no different information in the identical column.


And there you might have a completely automated, error-free, option to scrape information from (doubtlessly) any webpage, whether or not you want the content material and product descriptions, or ecommerce information reminiscent of product worth or transport prices.

In a time when data and information may be the benefit required to ship higher than common outcomes, the power to scrape net pages and structured content material in a straightforward and fast method may be priceless. In addition to, as we now have seen above, IMPORTXML might help to chop execution occasions and cut back the probabilities of making errors.

Moreover, the perform is not only an excellent instrument that may be completely used for PPC tasks, however as an alternative may be actually helpful throughout many alternative tasks that require net scraping, together with web optimization and content material duties.

Extra Assets:


Proceed Studying Under

Picture Credit

All screenshots taken by creator, August 2021

Source link

Leave A Comment



Our purpose is to build solutions that remove barriers preventing people from doing their best work.

Giza – 6Th Of October
(Sunday- Thursday)
(10am - 06 pm)

No products in the cart.

Select the fields to be shown. Others will be hidden. Drag and drop to rearrange the order.
  • Image
  • SKU
  • Rating
  • Price
  • Stock
  • Availability
  • Add to cart
  • Description
  • Content
  • Weight
  • Dimensions
  • Additional information
  • Attributes
  • Custom attributes
  • Custom fields
Click outside to hide the comparison bar