Benchmark e-commerce prices with Google Sheets
Adrien Velter
Co-founder @ NoDataNoBusiness - ?? We help you make better decisions by unlocking Web data
When you sell products online,?whether on your?own?e-commerce or on any marketplaces, pricing is key and something you you grapple every single day.??
Among other decision factors, there's no doubt that competitors' prices are frequently?observed and benchmarked.?
But do you have an efficient process for benchmarking and monitoring competitors' prices?
If not, this article is for you.?
The old metholds to benchmark prices
Pricing benchmarks and analysis are usually handled by Marketing, or Pricing teams for bigger retailers. And most of the time, they consider one of the 3 following options:
Fortunately, there's an alternative that is inexpensive, easy to adopt, and doesn't require any technical skills: Google Sheets ;)
Introducing ImportFromWeb to benchmark prices with Google Sheets
ImportFromWeb is a Google Sheets add-on that enables to extract any data from any website using a simple function,?=IMPORTFROMWEB().
To proceed and extract a data point from a webpage, it is as simple as writing in any cell:
=IMPORTFROMWEB("url","path_to_data")?
And like any other spreadsheet functions, the arguments can be either written in quotation marks, or by referring to a cell that contains them:
=IMPORTFROMWEB(A2,B1)
For most websites, path to data is described using XPath or CSS selectors.
XPath (XML Path Language) is a query language for selecting nodes from an XML document. They can be used to describe the location of any element on a webpage. With the help of your Web browser, it can be very easy to find the XPath for the piece of data you are looking for.
You can refer to this article to find an xPath with limited HTML knowledge.
CSS selectors are less powerful than XPaths to find elements in HTML pages, but they usually do the job and are also easier to build. This article will tell you more on how to find CSS selectors.
Luckily, for some major marketplaces, such as Amazon or Walmart, price extraction is even easier as it does not require looking for Xpath or CSS selectors.?
For those platforms, path to data can be described using a built-in selector. Extracting a price from an Amazon listing becomes then as easy as typing:?
=IMPORTFROMWEB("amazon item url", "sale_price")
In the next 2 sections, we'll review both cases (Xpath and built-in selectors) with a clear step by step guide.
Extracting prices using Xpath (illustrated with eBay)
To illustrate how to extract a price using an Xpath, let's work with ebay.com on this specific item:
Just make sure as a prior requirement to install ImportFromWeb from Google Workspace Marketplace as it will allow the =IMPORTFROMWEB() formula to your Google Sheets environment.?From there, you can enable the formula within any given spreadsheet by activating it through your menu: Extensions >> ImportFromWeb >> Activate.
STEP 1:?Discovering the Xpath to scrape data
The first step of our process consists in opening the product page on our browser and right clicking anywhere on the page to open the Inspector tool.?
领英推荐
Once opened, the important step is to click on the arrow symbol in the top left-hand corner that says: “Select an element in the page to inspect it.” With this enabled, you’ll be able to view the information related to the element that you’d like to extract.
When you click on the price, you’ll see that it highlights the related code within the Developer view.
Right click on the highlighted code, then choose Copy, and finally Copy Xpath (in our case: //*[@id='mainContent']/div[1]/div[3]/div/div/div[1]/span).
Now you’re ready to feed this information to the =IMPORTFROMWEB() function so that it can easily extract your data.
STEP 2?Creating your first function with ImportFromWeb
Turning to your Google Sheets, it’s time to write our first function with the copied Xpath selector. Simply input:
=IMPORTFROMWEB("https://www.ebay.com/itm/175422713877","the copied Xpath selector")
Then watch as the tool scrapes the price from the webpage (you'll see a "loading" message for a few seconds), copying it directly to your spreadsheet.?
STEP 3: scaling the process on multiple products
Now that you have understood how it works for 1 single item, just repeat the process to extract prices on multiple items.
Since it works with a function, we'll input our eBay URLs in column A and enter the following formula in B1:
=IMPORTFROMWEB("A1","the copied Xpath selector")
Then, all you have to do is to drag down or copy the formula to your last row!
Step 4: Regularly update the data
Oneshot analysis is of course not enough, since prices are not be set in stone, and you will need to review them regularly.
The benefit of using ImportFromWeb is that your spreadsheets are somehow synced with the source pages real-time data. So every time you execute the =IMPORTFROMWEB() formulas (when you open the spreadsheet or using the add-on sidebar), your table is updated with live prices.
Extracting prices using a built-in selector (illustrated with Amazon)
As previously mentionned, on some marketplaces, prices extraction with Google Sheets is even easier as the price is described with a simple keyword:
=IMPORTFROMWEB("amazon item url", "sale_price")
So when it comes to extract prices from a list of hundreds or even thousands products sold on Amazon, the process is very easy:
As you have seen, ImportFromWeb is extremely easy to use. When it comes to benchmark prices on multiple products, it saves you from dealing with the usual methods (from the copy/paste to the in-house solutions)
Did you find this article useful?