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

Continue Reading
Comments

SEO

DuckDuckGo, Ecosia, Qwant Pen Open Letter On Fair Choice

Published

on

DuckDuckGo, Ecosia, Qwant Pen Open Letter On Fair Choice


In an open letter released on July 5, 2022, the three search engine chief executives outlined 10 principles to enable internet users to change their default search engine effectively.

The release, entitled “10 Principles for Fair Choice Screens and Effective Switching Mechanisms,” comes on the same day the European Parliament adopts the Digital Services Act (DSA).

This groundbreaking political agreement aims to protect consumers by establishing a new standard to hold dominant online platforms, or gatekeepers, liable for illegal and harmful content. Companies designated as gatekeepers include Google, Meta, and Twitter.

Fair Choice Seeks to Break Away from Gatekeeper Defaults

The letter, signed by Gabriel Weinberg, CEO of DuckDuckGo, Christian Kroll, CEO of Ecosia, and Corinne Lejbowicz, president of Qwant, calls for a set of 10 “common-sense” principles that will improve online user experience.

The executives state in the letter:

“Choice screens and effective switching mechanisms are crucial tools that empower users and enable competition in the search engine and browser markets. Without strict adherence to both clear rules and principles for fair choice screens and effective switching mechanisms, gatekeeping firms could choose to circumvent their legal obligations.”

The principles outlined suggest that users should have a choice screen for search engines, web browsers, and virtual assistants upon initial platform use. They also call for these screens to be offered periodically, at any time when “users are in the mindset to change core services, and major software updates can reset or affect gatekeeper-controlled search and browser default settings.”

It also stressed switching services should be applied across access points, be prominently displayed as top-level settings, and be free of charge.

Adoption Could Cut into Google’s Market Share

Should these principles be adopted, it could lead to the signatory companies and other search engines claiming a more significant piece of the search engine market. Google currently accounts for more than 90% of all internet searches worldwide.

This could have rippling effects through the search engine optimization and digital marketing industries, as optimization tactics would have to be adjusted for these other algorithms.

However, one of the conditions of the DSA is to enforce increased transparency measures on online platforms, including revealing how algorithms work for recommendations. The goal is to create a more level playing field between gatekeepers and smaller companies.

Additionally, as the three signatories, along with other smaller search engines, do not collect personal data about users, digital marketers would be required to find other means of targeting display ads, PPC, and other campaigns that rely on Google data.


Source: DuckDuckGo

Featured Image: VectorMine/Shutterstock





Source link

Continue Reading

SEO

Google Ads Diagnostic Insights On Overview Page

Published

on

Google Ads Diagnostic Insights On Overview Page


Google Ads has added the diagnostic insights on the Insights and Overview page of the ad console. Diagnostic insights help you identify common reasons why your campaign may not be serving or getting conversions.

Google announced this saying the reason they added this was because “it can be difficult to identify and troubleshoot these issues in a timely fashion.”

The diagnostic insights will highlight when a given campaign’s ads are not running due to all ad groups being paused, account suspensions, or low Ad Strength, among other diagnostic checks.

After you submit a campaign, diagnostic insights are an easy way to see where you’re at in the campaign serving process. In addition, they can automatically identify issues that range from ad policy, billing, budget, bids, and more. Clicking on “Show all campaign diagnostics” gives you a more detailed breakdown for the following:

  • Account status
  • Billing status
  • Policy review
  • Conversion tracking
  • Campaign budget
  • Bid strategy target
  • Campaign status
  • Ad strength

This tool was available in April 2022 but not on the overview or insights page.

Forum discussion at Twitter.





Source link

Continue Reading

SEO

6 Best WordPress Review Plugins For 2022

Published

on

6 Best WordPress Review Plugins For 2022


If you want to increase traffic to your WordPress site, boost your SEO, or bring in more sales from your WooCommerce products, you will benefit from installing a WordPress review plugin on your website.

By having reviews for your services or products and increasing social proof, you’re more likely to draw new customers to your business and retain existing ones.

Reviews are one of the most powerful sources of marketing and the best part is, they’re also free!

Collecting reviews from your customers and clients is also a great source of feedback.

This will allow you to make changes and improvements to your products and customer experience as well as form a relationship with your customers.

Not only does this increase the chance that they will become a repeat customer but they will also be more willing to recommend you and your services or products to others.

Choosing A WordPress Review Plugin

As with choosing any plugin, you want to make sure the review plugin you install on your WordPress site is going to be reliable and safe.

Before installing a review plugin, first, make sure that it has good reviews and ratings.

When browsing plugins, you will see a star rating and in brackets will be the number of reviews.

The higher the star rating and the number of reviews, the better.

You also want to check how many active installations the plugin has.

Again, the higher the number of active installations, the better, as this means that more people are using the plugin on their website.

And lastly, make sure the plugin has been tested with the latest version of WordPress.

Screenshot from WordPress, June 2022
ratingsScreenshot from WordPress, June 2022

Here are my top six WordPress plugin recommendations. These will provide an attractive and easy-to-use review process for your website.

All of the listed review plugins have been tested with WordPress 6.0 and have excellent ratings among over 20,000 users.

1. WP Review Pro

WP Review Pro pluginScreenshot from mythemeshop.com, June 2022

If you are looking for a truly comprehensive review plugin, you cannot go wrong with WP Review Pro.

WP Review Free includes all of the basic features, but it is well worth the $67 to upgrade to the pro version for unlimited sites to unlock the full feature set.

With WP Review Pro, you get unlimited color selections, and a variety of rating options, such as stars, percentages, thumbs up/down, points, or a circle rating.

The plugin supports 19 rich snippets and works seamlessly with Facebook, Google, and Yelp reviews in order to extend your reach.

Highly customizable pre-defined designs make it easy to match your branding or other popular review platforms.

2. Site Reviews

Site Reviews pluginScreenshot from WordPress, June 2022

Site Reviews is a simple review plugin that allows users to leave a review through a customized form.

Reviews use a 1 to 5-star system and are filterable.

You can also pin favorable reviews to the top so that they are the first reviews that customers see.

Site Reviews can also be used with WooCommerce via a free add-on to replace the WooCommerce Reviews with Site Reviews for the products in your WooCommerce store.

The plugin can be used as a shortcode, custom Gutenberg block, or widget and it supports native Elementor widgets to accommodate various theme and builder types and is free.

3. Plugin For Google Reviews

Plugin for Google Reviews pluginScreenshot from WordPress, June 2022

Plugin for Google Reviews specifically displays ratings and reviews from Google, using a public Google API.

The free version will limit you to displaying five Google reviews while the Business version, which runs $85/year for a single site, uses an API for your Google Business account, allowing unlimited Google Reviews.

The upgrade will also give you unlimited Yelp and Facebook reviews and will sync your accounts for automatic updates.

You can also mix and match reviews and customers can leave a review through your website for the other platforms.

Want to put your best reviews at the top?

Use the filter feature to display reviews in the order you want.

Five themes are available and reviews can be displayed using a widget, shortcode, or Universal HTML/JavaScript.

4. Customer Reviews For WooCommerce

Customer Reviews for WooCommerce pluginScreenshot from WordPress, June 2022

The Customer Reviews for WooCommerce plugin allows customers who have shopped in your store to leave written reviews and photos to increase your social proof and bring in more customers.

You can have an email automatically sent when a customer makes a purchase asking them to leave a review. You can even send coupons to those that do leave a review.

The plugin also integrates with a service to verify the authenticity of reviews.

Customer Reviews for WooCommerce uses ratings and voting review types, as well as customer-submitted photos. Reviews can also be filtered.

There is a free Basic version but for more customization and professional features, you will want to consider the Professional version for $49.99/year.

5. Starfish Reviews

Starfish Reviews pluginScreenshot from Starfish Reviews, June 2022

The Starfish Reviews plugin uses a funnel system to sort positive and negative reviews.

The plugin is set up in the WordPress dashboard and can link to Google, Yelp, Facebook, and a number of other third-party review platforms.

In the first step of the review, a positive or negative review choice is shown.

If the positive review is chosen, you give the customer single or multiple options of where they can leave their review.

If a negative review is chosen, the feedback will be sent back to you and they can be prompted for more information.

Depending on the restrictions of the review platform(s) you are using, you can choose not to allow them to post a negative review or allow it.

A very limited version of the plugin is available for free on wordpress.org and Starfish Reviews also has multiple paid tiers starting at $37 per month.

6. Taqyeem

Taqyeem pluginScreenshot from codecanyon.net, June 2022

The Taqyeem WordPress Review Plugin creates beautiful custom reviews that can be added to pages, posts, and custom post types.

You have unlimited color and review criteria customizations with a choice of over 500 Google Fonts to match a variety of branding.

Rating styles are points, percentages, and stars and multiple options are available for the rating image. Reviews can be displayed by Best, Recent, and Random.

The Taqyeem plugin uses Google Rich Snippets for optimal viewing through search engines.

The plugin has a one-time purchase price of $29 through CodeCanyon, which includes six months of support. Additional support can be purchased.

Choosing The Best WordPress Review Plugin In 2022

With over 1,000 WordPress review plugins available to choose from, it can be hard to know where to begin.

When choosing any plugin for your WordPress site, be mindful of the star rating, reviews, active installations, and compatibility with the latest version of WordPress.

There are plenty of free review plugins for WordPress that are reputable and reliable, but paid review plugins may offer the additional features that you require.

Displaying reviews from customers and clients on your website is great for marketing and establishing trust with your audience so it’s worth taking the time to choose a review plugin that is going to work seamlessly on your WordPress website.

More resources: 


Featured Image: Jirsak/Shutterstock





Source link

Continue Reading

Trending

Copyright © 2021 Liveseo.com