If you’re running eCommerce web stores or a pro-seller on multi marketplaces, you must agree with me that 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 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, 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 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, convert into sales, and retain old customers. In particular, when I assume your competitors’ other marketing elements which can impact conversion rate, (Such as shipping, customer review, product variation, etc), are similar to yours.

Thus, you can see from the table, when your product pricing is lower than the competitor, market value percentage (here is 9.4%, higher is better, formula = 1- your price/your competitor price), is a key metric to see if your product is competitive on the target market or not. You don’t want to manually check hundreds of SKUs every day, rather 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

Unless you are not aware of, you will not be 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 work with the new pricing, for the purpose to earn profit rather than losing money in this battle game.

Thus, as you can see from the table, importXML function plus conditional formatting feature and if function can 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 path 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, because the primary purpose of web scraping is to easily notice the SKU market value and profit margin figures, rather than being lost 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 green light, because its pricing is lower than your target competitor and you can understand the percentage from market value metric (9.4%). 

However, your competitor might launch promotion and adjust the pricing much lower than yours. Conditional formatting which 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, because cost per sale or cost per acquisition needs lowering down as well, for the purpose to be still profitable.

For example, when the SKU price is US$134.99, profit margin is 22% (After having minus landed, fulfillment, 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 IFS function in the profitable or not column, and create a formulate between ACoS (Cost per sale/SKU Price) and profit margin listed as below, to show the strings, either of losing money or earning money.

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

Conclusion

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

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

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.

By Louis Lu

Growth Hacker & Digital Marketer, with a proven record of over 11 years experience in 20+ Asian markets, and 24,000+ connections in Linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *