Google Sheets ImportXML – Automatically Scrape Web and Collect Product Price Info

If you’re running eCommerce web stores or a pro-seller on multi-marketplaces, you must agree with me on one thing. Collecting data is essential for you to find the next hot-selling product and keep your product pricing in an absolutely attractive format. 

I am always on the lookout for a unique angle to use freely available or potentially scrapable data sources. Also, It’s indeed frustrating that you have, admittedly, spent hours upon hours trying to learn Python. It’s only for writing simple web scraper applications and implementing web scraping automatically. However, in the end, you can only discover the data isn’t accessible, interesting, or differentiated enough from what’s already out there.

If you just want to automate updating the profit calculator of your eCommerce business, thankfully we have a method. There is an easier way to collect data from the web without spending that many hours:

Google Sheets ImportXML Function

In this article, I’m going to talk about how to implement web scraping with google sheets importXML. By the end of this article, you can learn this function applied to your profit calculator. And then you can check if your pricing is attractive enough in your target market, and current advertising acquisition cost makes sense or not.

Why Web Scraping for Product Price Is Important

First thing first, you might not be the only one who is selling a product on the target market. 

In a way, pricing is critical to catch new customers’ attention, converting them into sales, and retaining old customers. When I assume your competitors’ other marketing elements that can impact conversion rates are similar to yours. (Such as shipping, customer review, product variation, etc)

Thus, you can see from the table, your product pricing is lower than the competitor’s. Then the market value percentage is a key metric to see if your product is competitive in the target market or not. (Here is 9.4%, higher is better, formula = 1- your price/your competitor price). You don’t want to manually check hundreds of SKUs every day. Instead, the competitor data can be updated automatically in the profit calculator, for the purpose to adjust marketing strategies in a more agile way.

Secondly, Web Scraping for Repricing and Optimising Cost per Sale of Paid Media

You are not surprised to see your SKU conversion rate is going down when your competitor is buying the same advertising keyword offering lower pricing than yours.

15% – 30% product profit margin is a common range in the eCommerce sector, lowering the pricing is basically equal to lowering the profit margin, if you don’t want to lose new customer acquisition opportunities, and prefer to compete with the rivals. In parallel, your paid media bidding strategy also needs adjusting to working with the new pricing, for the purpose to earn profit rather than losing money in this battle game.

As you can see from the table, the import XML function is plus a conditional formatting feature. And it also plus IF function to visually show you if the SKU pricing is attractive and the SKU is earning profit. 

What’s Google Sheets ImportXML Function

In Google Sheets, the ImportXML formula will be formatted with two arguments:

=ImportXML (URL, XPath_query)

1. URL: A string of page paths where the data can be retrieved
2. XPath_query: XPath Expressions can be used to navigate XML documents.

Case Study: Take Ring Stick Up Security Camera as An Example:

1. Target Web Page

Go to the webpage you’re going to scrape the price data. For example, here we go to Amazon, search, and find the Ring stick-up camera bundle with one solar panel.

Copy the URL: https://www.amazon.com/All-new-Ring-Stick-Up-Cam-Solar-HD-security-camera-with-two-way-talk-Works-with-Alexa-/dp/B07W7G23ZG/, and paste it to the SKU your store is selling

2. Find and Copy the Element XPath_query

Right-click the price > “inspect” to open Chrome’s dev tools. In dev tools, right-click the highlighted element (<ul class=”rows” should be highlighted in this example) > Copy > Copy Full XPath.

(Note: The naming of XML Path might be different among different browsers. For example, Safari only shows XPath)

3. Apply the same XPath_query to other product pages from the same website

The price element XPath_query, such as the Ring stick-up camera bundled with one solar panel on Amazon //*[@id="priceblock_ourprice"], is applicable to other product pages you want to scrape data. It’s unnecessary to find the path again because it’s the same thing.

So you can scale up to create competitor price trackers just by adding more target web pages into the table.

Additional Functions to Visualise the Signal of Market Value and Profit Margin

Setting up the competitor price scraping in the profit calculator is just halfway done. It’s because the primary purpose of web scraping is to easily notice the SKU market value and profit margin figures. It’s rather than being lost in front of hundreds of SKU data. Here basically we need to use additional two functions.

Conditional Formatting

Your SKU price looks great when it’s in the green light. It’s because its pricing is lower than your target competitor and you can understand the percentage from the market value metric (9.4%). 

However, your competitor might launch promotions and adjust the pricing much lower than yours. Conditional formatting is set up as showing red color when the pricing is lower than you. So it’s easier for you to spot the update and adjust the strategy.

IFS function

Lowering the SKU price, on the other hand, is narrowing down the audience reach scope. It’s because the cost per sale or cost per acquisition needs lowering as well, for the purpose to be still profitable.

For example, when the SKU price is US$134.99, the profit margin is 22% (After having minus landed, fulfillment, and payment processing fees). So Basically ACoS must be less than 22% (Normally it should be less than 10%, and it depends on the marketing strategy).

So we could use the IFS function in the profitable or not column. And then we create a formulate between ACoS (Cost per sale/SKU Price) and profit margin listed as below. It’s for showing the strings, either of losing money or earning money.

=IFS(P8>L8,"Losing Money",P8<L8,"Earning Profit")

More recommendations:

9 Google Sheet Formulas Plus 1 Add-on for the eCommerce Marketing

Conclusion

Repricer and dynamic pricing software can also be an option, such as channel advisor. But you might be tight on the marketing budget and want to have a FREE way to get the same thing. Rather than spending hours upon hours learning Python, Google Sheets ImportXML can be an alternative option.

Also, this function can be flexibly applied to scrape target pages’ content, email addresses, social media, etc. It’s absolutely a very cost-efficient method to automate data collection and update.

Being said that if you’re interested in recommended Google Sheets functions and scraping the pricing data by using Python, please check out this article:

Chapter 4: Create a Website Bot to Scrape Specific Website Data Using BeautifulSoup

Chapter 8: Build a Shopify Scraper to Fetch Competitor Webshop Product Data Using Easy2Digital APIs

9 Google Sheet Formulas Plus 1 Add-on for the eCommerce Marketing

I hope you enjoy reading Web Scraping with Google Sheets ImportXML for Real-Time Product Price and find it helpful. If you did, please support us by doing one of the things listed below, because it always helps out our channel.

• Support my channel through PayPal (paypal.me/Easy2digital)
• Subscribe to my channel and turn on the notification bell Easy2Digital Youtube channel.
• Follow and like my page Easy2Digital Facebook page
• Share the article to your social network with the hashtag #easy2digital
• Buy products with Easy2Digital 10% OFF Discount code (Easy2DigitalNewBuyers2020)
• You sign up for our weekly newsletter to receive Easy2Digital latest articles, videos, and discount code on Buyfromlo products and digital software
• Subscribe to our monthly membership through Patreon to enjoy exclusive benefits (www.patreon.com/louisludigital)