Connect with us

SEO

How To Use Google Sheets For Web Scraping & Campaign Building

Published

on

How To Use Google Sheets For Web Scraping & Campaign Building


Editor’s note: As 2021 winds down, we’re celebrating with a 12 Days of Christmas Countdown of the most popular, helpful expert articles on Search Engine Journal this year.

This collection was curated by our editorial team based on each article’s performance, utility, quality, and the value created for you, our readers.

Each day until December 24th, we’ll repost one of the best columns of the year, starting at No. 12 and counting down to No. 1. Our countdown starts today with our No. 5 column, which was originally published on August 4, 2021.

This how-to guide from Andrea Atzori teaches readers how to utilize Google Sheets for web scraping and campaign building, without any coding experience required. 

Enjoy!


We’ve all been in a situation where we had to extract data from a website at some point.

When working on a new account or campaign, you might not have the data or the information available for the creation of the ads, for example.

Advertisement

Continue Reading Below

In an ideal world, we would have been provided with all of the content, landing pages, and relevant information we need, in an easy-to-import format such as a CSV, Excel spreadsheet, or Google Sheet. (Or at the very least, provided what we need as tabbed data that can be imported into one of the aforementioned formats.)

But that’s not always the way it goes.

Those lacking the tools for web scraping – or the coding knowledge to use something like Python to help with the task – may have had to resort to the tedious job of manually copying and pasting possibly hundreds or thousands of entries.

In a recent job, my team was asked to:

  • Go to the client’s website.
  • Download more than 150 new products spread across 15 different pages.
  • Copy and paste the product name and landing page URL for each product into a spreadsheet.

Now, you can imagine how lengthy the task would have been if we’d done just that and manually executed the task.

Advertisement

Continue Reading Below

Not only is it time-consuming, but with someone manually going through that many items and pages and physically having to copy and paste the data product by product, the chances of making a mistake or two are quite high.

It would then require even more time to review the document and make sure it was error-free.

There has to be a better way.

Good news: There is! Let me show you how we did it.

What Is IMPORTXML?

Enter Google Sheets. I’d like you to meet the IMPORTXML function.

According to Google’s support page, IMPORTXML “imports data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.”

Essentially, IMPORTXML is a function allows you to scrape structured data from webpages — no coding knowledge required.

For example, it’s quick and easy to extract data such as page titles, descriptions, or links, but also more complex information.

How Can IMPORTXML Help Scrape Elements Of A Webpage?

The function itself is pretty simple and only requires two values:

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

XPath stands for XML Path Language and can be used to navigate through elements and attributes in an XML document.

For example, to extract the page title from https://en.wikipedia.org/wiki/Moon_landing, we would use:

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

This will return the value: Moon landing – Wikipedia.

Or, if we are looking for the page description, try this:

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

Here is a shortlist of some of the most common and useful XPath queries:

Advertisement

Continue Reading Below

  • Page title: //title
  • Page meta description: //meta[@name=’description’]/@content
  • Page H1: //h1
  • Page links: //@href

See IMPORTXML In Action

Since discovering IMPORTXML in Google Sheets, it has truly become one of our secret weapons in the automation of many of our daily tasks, from campaign and ads creation to content research, and more.

Moreover, the function combined with other formulas and add-ons can be used for more advanced tasks that otherwise would require sophisticated solutions and development, such as tools built in Python.

But in this instance, we will look at IMPORTXML in its most basic form: scraping data from a web page.

Let’s have a look at a practical example.

Imagine that we’ve been asked to create a campaign for Search Engine Journal.

They would like us to advertise the last 30 articles that have been published under the PPC section of the website.

Advertisement

Continue Reading Below

A pretty simple task, you might say.

Unfortunately, the editors are not able to send us the data and have kindly asked us to refer to the website to source the information required to set up the campaign.

As mentioned at the beginning of our article, one way to do this would be to open two browser windows — one with the website, and the other with Google Sheets or Excel. We would then start copying and pasting the information over, article by article, and link by link.

But using IMPORTXML in Google Sheets, we can achieve the same output with little to no risk of making mistakes, in a fraction of the time.

Here’s how.

Step 1: Start With A Fresh Google Sheet

First, we open a new, blank Google Sheets document:

Start with a Blank Google Sheets Document.

Step 2: Add The Content You Need To Scrape

Add the URL of the page (or pages) we want to scrape the information from.

Advertisement

Continue Reading Below

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

Add the URL of the Page You Want to Scrape.Screenshot taken from Google Sheets, July 2021

Step 3: Find The XPath

We find the XPath of the element we want to import the content of into our data spreadsheet.

In our example, let’s start with the titles of the latest 30 articles.

Head to Chrome. Once hovering over the title of one of the articles, right-click and select Inspect.

Open the Chrome WebDev Tool.Screenshot from SearchEngineJournal.com, July 2021

This will open the Chrome Dev Tools window:

Find and Copy the XPath Element You Want to Extract.Screenshot from SearchEngineJournal.com, July 2021

Make sure that the article title is still selected and highlighted, then right-click again and choose Copy > Copy XPath.

Advertisement

Continue Reading Below

Step 4: Extract The Data Into Google Sheets

Back in your Google Sheets document, introduce the IMPORTXML function as follows:

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

A couple of things to note:

First, in our formula, we have replaced the URL of the page with the reference to the cell where the URL is stored (B1).

Second, when copying the XPath from Chrome, this will always be enclosed in double-quotes.

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

However, in order to make sure it doesn’t break the formula, the double quotes sign will need to be changed to the single quote sign.

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

Note that in this instance, because the page ID title changes for each article (title_1, title_2, etc), we must slightly modify the query and use “starts-with” in order to capture all elements on the page with an ID that contains ‘title.’

Here is what that looks on the Google Sheets document:

An example of IMPORTXML.Screenshot taken from Google Sheets, July 2021

And in just a few moments, this is what the results look like after the query has been loaded the data onto the spreadsheet:

Titles Imported in Google Sheets.Screenshot taken from Google Sheets, July 2021

As you can see, the list returns all articles that are featured on the page that we have just scraped (including my previous piece about automation and how to use Ad Customizers to Improve Google Ads campaign performance).

Advertisement

Continue Reading Below

You can apply this to scraping any other piece of information need to set up your ad campaign, as well.

Let’s add the landing page URLs, the featured snippet of each article, and the name of the author into our Sheets document.

For the landing page URLs, we need to tweak the query to specify that we are after the HREF element attached to the article title.

Therefore, our query will look like this:

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

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

Import the Article Links.Screenshot taken from Google Sheets, July 2021

Voila! Straight away, we have the URLs of the landing pages:

Articles and URLs Imported in Google Sheets.Screenshot taken from Google Sheets, July 2021

You can do the same for the featured snippets and author names:

All the Data is Scraped and Imported in Google Sheets.Screenshot taken from Google Sheets, July 2021

Troubleshooting

One thing to beware of is that in order to be able to fully expand and fill in the spreadsheet with all data returned by the query, the column in which the data is populated must have enough cells free and no other data in the way.

Advertisement

Continue Reading Below

This works in a similar way to when we use an ARRAYFORMULA, for the formula to expand there must be no other data in the same column.

Conclusion

And there you have a fully automated, error-free, way to scrape data from (potentially) any webpage, whether you need the content and product descriptions, or ecommerce data such as product price or shipping costs.

In a time when information and data can be the advantage required to deliver better than average results, the ability to scrape web pages and structured content in an easy and quick way can be priceless. Besides, as we have seen above, IMPORTXML can help to cut execution times and reduce the chances of making mistakes.

Additionally, the function is not just a great tool that can be exclusively used for PPC tasks, but instead can be really useful across many different projects that require web scraping, including SEO and content tasks.

2021 SEJ Christmas Countdown:

Advertisement

Continue Reading Below

Featured image: Aleutie/Shutterstock





Source link

SEO

Google Be Colorful Sign from Peter The Greeter

Published

on

Google Be Colorful Backdrop


Here is a photo from Peter the Greeter at the GooglePlex in front of a “Be Colorful” sign that is super colorful with a lot of fun scenery around it. I assume this was for the Pride celebrations at the Google office.

He shared this on Instagram.

This post is part of our daily Search Photo of the Day column, where we find fun and interesting photos related to the search industry and share them with our readers.





Source link

Continue Reading

SEO

Google Automatically Selecting Background Colors For Search Result Snippet Images

Published

on

Google Automatically Selecting Background Colors For Search Result Snippet Images


Over the past few months Google has been auto-selecting background colors not just for image search results and not just for shading Google Shopping Ads but also for images added to the search result snippets in Google Search.

Punit spotted this a few months ago and he shared a couple examples on Twitter that shows how Google is taking an image with maybe a light gray background and then replacing it with a green background. He has more examples but here is the bigger one.

On the left is an image of a heel on a dark green background in the Google Search result image snippet:

But if you looked at the web page, that image as on a light gray background:

Here are more examples:

Pretty cool for Google to do this but I do wonder if the publishers won’t like Google changing the image backgrounds like this?

Forum discussion at Twitter.





Source link

Continue Reading

SEO

Top 6 Free Survey Maker Tools For Marketers

Published

on

Top 6 Free Survey Maker Tools For Marketers


The number of online surveys has risen dramatically in the past decade, according to the Pew Research Center.

From short social media polls to lengthy feedback forms, it’s never been easier to survey your target audience and find out what exactly they’re thinking.

When it comes to free survey makers, you have plenty of options to choose from.

That’s the good news.

The bad news is you have to wade through your options to figure out the best survey tool for you.

In this article, I’ve done that dirty work for you.

Below I outline the top six free survey makers, with a simple bulleted list of their pros and cons, so you can quickly select the best one for your needs.

But first up, the caveats.

What You’re Missing With Free Survey Makers

When something’s free, there’s usually a catch. The same goes for free survey makers.

Free survey tools, or the free plan offered by a paid survey tool, often come with the following limitations:

  • Limited export options. You may not be able to export your survey data for review in Excel or Google Sheets. There may be a PDF-only export option or no export ability at all.
  • Limited analytics. Free survey tools often skimp on the analytics. You may be left to your own pivot tables and Excel expertise if you want to create anything fancy from your survey data.
  • Limited survey functionality. This runs the gamut, from a limit on how many respondents or questions you can have per survey, to only allowing so many question types (e.g., multiple-choice, long-form, etc.).
  • Limited extra perks. By perks, I mean those other features that make software from good to great. With survey makers, that might mean easy-to-access support, the ability to embed surveys in email or webpages, multiple user accounts, or integration with other email marketing or CRM software.
  • No branding. Free survey makers give you their tools for free. In return, you provide them with free brand awareness. Don’t expect to be able to swap out their logo for your own. You’ll probably be stuck with their branding, along with a prominent link to their site throughout the survey or on the thank you page (or both).

If any of the above is a dealbreaker for you, you should plan to drop a little dough on a paid survey tool. That’s why I’ve also included the starting price for all six of the tools featured below.

In case you end up having to upgrade later, it’s easier to do so from a tool you’re already familiar with.

Top 6 Free Survey Tools

Without further ado, I present the best free survey makers you’ll find today. These are listed in no particular order.

1. Google Forms

Screenshot by author, June 2022

Do you live and die by your Google Drive?

Great news: Google also offers free survey software via Google Forms.

Alright, I know I just said these were presented in no particular order, but I’ll openly admit Google Forms is my personal favorite. Just look at all of the features they include in their free plan!

All you need is a free Google account to get started.

Here’s what’s included in the free plan:

  • Unlimited surveys.
  • Unlimited questions.
  • Unlimited responses.
  • Export to Google Sheets.
  • Survey logic (ability to skip or trigger questions).
  • Ability to embed images and YouTube videos.
  • Ability to embed the survey on your website and share to social media.
  • Survey analytics, updated in real-time.
  • Integration with Google Docs, Sheets, Slides.
  • Unlimited collaborators.
  • Customizable survey templates.
  • Free branding.

What’s missing from the free plan:

  • Enhanced security and collaboration options.
  • Integration with your existing Google Workplace account.

Price: Completely free. Google Workplace pricing starts at $6 per user per month.

Best for: Anyone and everyone, for business or casual use.

2. SurveyMonkey

surveymonkeyScreenshot by author, June 2022

SurveyMonkey is the online survey tool. Established in 1999, it’s still the most well-known online survey software.

Despite the limitations of its free plans, SurveyMonkey continues to be popular thanks to its intuitive interface and brand recognition. Notable clients include Allbirds, Tweezerman, and Adobe.

One nice perk is that you can test out any of the paid features with your free plan. (You just won’t be able to actually use it in your live survey until you pay up.)

Here’s what’s included in the free plan:

  • Unlimited surveys.
  • 10 questions.
  • 15 question types.
  • 100 responses per survey.
  • Over 250 customizable survey templates.
  • Ability to embed the survey on your website.
  • Mobile app.
  • One user.

What’s missing from the free plan:

  • Unlimited questions, question types, and responses.
  • Data exports – this is a biggie!
  • Custom branding.
  • Survey logic (ability to skip or trigger questions).
  • Team collaboration.
  • Advanced security (single sign-on, HIPAA compliance).
  • A/B testing.

Price: Freemium. Paid plans start at $16 per month for individuals, $25 for teams.

Best for: Those who want a tried-and-true survey maker with all the features you could ask for.

3. Typeform

typeformScreenshot by author, June 2022

Many online survey tools are designed for the general public.

Readers of Search Engine Journal will be happy to hear that there’s a survey tool created just for us. Typeform was built specifically with marketers, UX researchers, and business owners like us in mind.

Here’s what’s included in the free plan:

  • Unlimited surveys.
  • 10 questions per survey.
  • 10 responses per month.
  • Basic question types.
  • Basic reporting and analytics
  • Ability to embed the survey on your website.
  • Integrations with MailChimp, HubSpot, Trello, Google Sheets, Zapier, and more.

What’s missing from the free plan:

  • Unlimited questions and responses.
  • Custom thank you screen.
  • Custom branding.
  • Survey logic (ability to skip or trigger questions).
  • Team collaboration.
  • Ability to accept payment.
  • Ability for survey respondents to upload files.
  • Integration with Facebook pixel and Google Tag Manager.

Price: Freemium. Paid plans start at $29 per month.

Best for: Enterprise users, UX researchers, and marketers hoping to track customer behavior.

4. Zoho Survey

zoho surveyScreenshot by author, June 2022

Zoho Survey is part of the same Zoho suite of apps that caters to sales, HR, IT, finance, and virtually any kind of business user you can think of.

Given their tenure creating SaaS software for business, their survey tool is just as robust as you might expect. Customers include big names like Netflix, Amazon, Facebook, and Change.org.

Here’s what’s included in the free plan:

  • Unlimited surveys.
  • 10 questions per survey.
  • 100 responses per survey.
  • Ability to embed surveys in email or website, or share to social media.
  • Export to PDF.
  • 250 survey templates.
  • Password protection and HTTPS encryption.
  • One user.

What’s missing from the free plan:

  • Unlimited questions and responses.
  • Ability to export to XLS or CSV.
  • Survey logic (ability to skip or trigger questions).
  • Custom branding.
  • Team collaboration.
  • Real-time responses.
  • Multilingual surveys.
  • Integration with Google Sheets, Tableau, Shopify, Zendesk, Eventbrite, and others.

Price: Freemium. Paid plans start at $25 per month.

Best for: Zoho users, or anyone who needs an extra level of security for their surveys.

5. Alchemer

alchemer survey makerScreenshot by author, June 2022

Alchemer is an advanced survey maker developed for the enterprise client.

Paid features include custom coding so you can customize every single element of your survey, from the survey URL to the form logic.

They stand out among free survey makers for being one of the few (besides Google Forms) to offer unlimited questions and Excel exports in their free plan. Clients include Disney, Salesforce, Verizon, and The Home Depot.

Here’s what’s included in the free plan:

  • Three surveys at a time.
  • Unlimited questions.
  • 100 responses.
  • 10 question types.
  • Export to Excel.
  • Customizable templates.

What’s missing from the free plan:

  • Unlimited surveys.
  • Unlimited responses.
  • Unlimited question types.
  • Survey logic (ability to skip or trigger questions).
  • Custom branding.
  • Ability to embed surveys in websites.
  • Export to PDF, PowerPoint, or Word.
  • Ability for survey respondents to upload files.
  • Survey analytics and reporting.
  • Ability to accept payment.

Price: Freemium. Paid plans start at $49 per month.

Best for: Enterprise users needing to create long surveys with advanced logic and question types.

6. Jotform

jotform survey makerScreenshot by author, June 2022

With over 10,000 templates, Jotform takes the cake as the survey maker with the most form templates on our list.

Jotform also stands out for letting you accept payments with the free plan (although you’re limited to 10).

This popular survey maker includes clients as wide-ranging as AMC and Nickelodeon to Redfin and the American Medical Association.

Here’s what’s included in the free plan:

  • Five surveys.
  • 100 questions per survey.
  • 100 responses per survey.
  • Ability to embed surveys in email or website.
  • Export to PDF or Excel.
  • 10,000 survey templates.

What’s missing from the free plan:

  • Unlimited surveys.
  • Unlimited questions and responses.
  • Survey logic (ability to skip or trigger questions).
  • Custom branding.
  • HIPAA compliance.

Price: Freemium. Paid plans start at $29 per month.

Best for: Users who want a template for every kind of survey possible.

Which Survey Tool Will You Use?

There truly is a survey maker for everybody.

The above options are all solid choices. Which one works for you may depend on your organization’s needs and your personal preferences.

Take advantage of the free trials and see which one you like best.

More Resources:


Featured Image: Prostock-studio/Shutterstock





Source link

Continue Reading

Trending

Copyright © 2021 Liveseo.com