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)
- URL: A string of page paths where the data can be retrieved
- 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.
- URL: A string of page paths where the data can be retrieved
- XPath_query: XPath Expressions can be used to navigate XML documents.
- URL: A string of page paths where the data can be retrieved
- XPath_query: XPath Expressions can be used to navigate XML documents.
- 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
- GOOGLE Latest Trending API Endpoint Recommendation
FAQ:
Q1: What is Google Sheets ImportXML?
A: Google Sheets ImportXML is a function within Google Sheets that allows users to import data from XML and HTML sources.
Q2: How does Google Sheets ImportXML work?
A: Google Sheets ImportXML works by using an XPath query to extract data from XML or HTML sources. The ImportXML function retrieves data based on the provided XPath query and imports it into a Google Sheets cell.
Q3: What can I use Google Sheets ImportXML for?
A: You can use Google Sheets ImportXML to import data from various online sources, such as web pages, RSS feeds, APIs, and more. This can be helpful for data analysis, tracking, and automation purposes.
Q4: Can I import data from any website using Google Sheets ImportXML?
A: In most cases, you can import data from any website using Google Sheets ImportXML. However, some websites may have restrictions or require authentication, which may limit the data that can be imported.
Q5: Are there any limitations to using Google Sheets ImportXML?
A: Yes, there are some limitations to using Google Sheets ImportXML. The ImportXML function may not work correctly with websites that heavily rely on JavaScript or have dynamic content loaded through AJAX. Additionally, the function has a maximum limit on the number of requests that can be made per day.
Q6: How do I use Google Sheets ImportXML?
A: To use Google Sheets ImportXML, you need to open a Google Sheets document and enter the ImportXML function in a cell. The function requires two parameters: the URL of the webpage or XML source and the XPath query to extract the desired data.
Q7: Can I schedule automatic data updates using Google Sheets ImportXML?
A: Yes, you can schedule automatic data updates using Google Sheets ImportXML. You can use the built-in Google Sheets features like time-driven triggers or third-party tools like Zapier to automate the data import process at regular intervals.
Q8: Is there a limit to the amount of data I can import with Google Sheets ImportXML?
A: There is a limit to the amount of data you can import with Google Sheets ImportXML. The function has a maximum limit on the number of cells it can import data into. If you exceed this limit, you may need to split your data into multiple import functions or consider alternative methods.
Q9: Are there any alternatives to Google Sheets ImportXML?
A: Yes, there are alternative methods and tools available for importing data into Google Sheets. Some alternatives include using Google Apps Script, third-party add-ons, or custom scripts to fetch and import data.
Q10: Where can I find more information about Google Sheets ImportXML?
A: You can find more information about Google Sheets ImportXML in the official Google Sheets documentation. The documentation provides detailed instructions, examples, and tips for using the ImportXML function effectively.
Google API Endpoint Recommendation
Google Shopping Product Scraper API
Price: US$18
Google Shopping SERP scraper crawls the product information from Google Shopping channel. API allows to filter by platform country domain, user location, language. Users can scrape the product information using a keyword query. The scraped dataset include product name, pricing, shipping fees, brand name, product page URL etc.
More API options from the Google collection.