competitive is super important on Google but if you're too competitive you're actually losing out on margin as well so really start to do these types of analysis on your business uh on your e-commerce store because you'll start to figure out where the gaps are for your business per products hi I'm Michael natalin from Market lead and today's video is the first in a series of showing how I use Google Sheets for everyday businesses especially in e-commerce to do deep data analysis so today's video is about how to actually start doing some price competitive analysis so I was doing this this morning with clients and what I also realized is I actually do a lot of things like this similar to this that I haven't been releasing so I'm actually going to be doing more Raw videos like this to show how you can actually do deep analysis on your products and your business with Google ads Google merchant center and Google Sheets there's no apis that can do a lot of this stuff I've built custom some tools at Market lead that do this automatically but some of these things I can't because there's no API for merchant center so I'm going to actually show you how today with products that you sell or even resell you can do price competitive analysis on it to see what your price is what your competitor's price is what the difference in that Benchmark is and then what you need to do the next steps to get it going but the first step is actually getting the data so that's what we're going to do today going through this now so I'm going to go through a client account I'm going to download all their product information based on some certain metrics and then once I do have those metrics I'm going to pull them into the Google sheet that you see here I'm going to then I'm going to be pulling in some information from the merchin center in here as well so firstly we need the product information so with this client here uh that I'm going to go through uh we're wanting to see products that have had more than three clicks cuz this client has thousands of products and then also uh the conversion rate is less than 4% because their conversion rate is actually quite good uh we can actually see over here I've already got some custom labels that play through the account but the main thing here is getting the data that you want uh to export so with this client they have about 70,000 products in their skew so I'm not going to do it for every product but in this case I will be doing a filter so I've just put in three clicks and conversion rate if you've only got a few hundred maybe a couple of thousand products that's fine you don't actually need any of this this is only for this case here so what I'm going to do is let me just also make this a bit smaller so I can download this I'm going to download this to a Google sheet right now and I'm just going to download here now and what I'm going to do is once I've got all this information I've got the item id which is the main thing I've got the product title here and then we've got the average price here as well these are the main things I need to know so if I go to open sheet I've got all the information here so item id which is the main thing I need Title and price so I'm just going to firstly change the settings to Dollars which is really annoying that Google doesn't automatically do this for me and I'm just going to start cleaning through this data here so also you got to make sure your data is based on a certain date range this is the last 30 days uh but you can use any date range depending on what your filter is so if you're using filters you probably want a bigger date range as well so let's just go through here clean up the data delete column um go that's fine delete this title that's fine price that's all I need there and that's all looking good obviously there's the conversion rate which we were looking at before which I don't really need in this case here because this is for a different type of thing that we're doing so I'm going to copy this to my spreadsheet over over here and I'm just going to paste this in here now so we can see that we've got all these item IDs and products here and press delete uh so these are the main things we need which are change this setting again that United States so then we've got the item id the title and the price now the next thing we're going to be doing is we're going to jump into the uh Google merchant center so so next thing is we're jumping into the merchant center here and we're going to go down to growth and then go to Price competitiveness so this is where we're actually going to be able to compare the prices on products so we look here and this product or this client actually uh 89% of their products are below Benchmark uh 1.5% are above Benchmark and 8.5% are above the Benchmark price so what we want to do here is uh we go into what was it again we go down to uh view all products and then we here will have all the products here with their price competitiveness on the side over here so as we can see here there are products uh and then we've got the clicks we've also got your price current Benchmark price and then the price competitiveness over here so this is based on a certain date range but we can definitely do like a longer date range if we want more data based on more clicks and there we go so we know this here so depending on each business it's going to be different but for this client they only have Google's only showing 5, uh 783 products even though the client has way more so what we'll do is we're going to download this and this is going to download as a uh CSV file and what I'm going to need to do next is going to need to open that CSV file here we go so we've got the open CSV file uh we've got the title product IDs which is the main thing that's going to be the key we use uh your price uh your current Benchmark price and percentage difference so what we can do here is I don't need the product title and I'll do this just to save the client's name from being seen we going remove all these so what we want to do is copy this data into this uh sheet and then we're just going going to call it bench mark data and then we're just going to paste this in here so then we will have all the product IDs uh with all the current Benchmark prices so your price Benchmark price and then the current Benchmark price difference which will be a percentage so then we can go back here and uh let's just do let's do bench mark your price bench mark price and percentage difference now the reason why I'm going to put your price in here being at the client price is because uh some of these may be different so what we're going to do is we're going to do a vook up of A2 to a um then we need to look in here and then it's going to be three false we do here here error so then we know here we go show start showing some prices ideally I know what needs to be fixed this needs to be changed to plain text this need to be changed to text that should fix it there we go so your price and that's actually matching up now and then we also need to be doing The Benchmark price as well so we'll do the same again just going to copy this formula over D so that's four now like I said the Google only was allowing to export uh the what was available in there so that's based on the gtin so we know for these types of products uh the price in Google is 407 even in merchant center just making sure that the prices do match up we just make this a bit bigger so it's easier on your eyes maybe even a bit bigger there uh I like to do this as well so then we also can have the price difference now I could literally just go Um this / this minus one which we know that's 39% cheaper or I could just once again do the V lookup as well so even if I got that product ID here f contrl v that is 39% so you can either do that or you could do a v lookup again but I might actually do this as a formula up here so I don't need to explain how these are done this is just me being a data nerd so that way we can actually start to see down here what the percentage difference is so we can start to see per product ID what products are actually like underpriced heavily and then we can start to do some uh conditional formatting to actually start to see that if anything is uh let's go less than z uh it'll be there and then we add another rule anything greater than greater or equal to zero or really just greater than zero we put in Red so then we can start to actually see products that are have their price differences are significantly higher now when we were going back to the uh product report before uh we may even want to incorporate some of the conversion rates in here as well because what this can do is this can probably give us some more insight towards uh if the conversion rate's low or high what the issue might be so let's just do um probably want to yeah conversion value we want to take conversion value uh conversions I might take that and then even cost I'll copy that across over to here so then we've got this so we can start to do even so this is how you just do this on a high level uh here now but I can even start to do some deeper analysis here uh even based on things that have uh conversion rate issues so because we haven't fixed um made any issues here grab this cost conversion rate conversion value we can start to sure there is conversion value yep there's $5,000 $6,000 in there uh just based on these products so we can start to do a Rass analysis as well so if anything has a like an issue with a Rass um we can start to do an analysis there so for example R equals if error is G2 to G / by D2 to D so we can start to see products that have a rise and what their rise percentage is as well and then what we can actually do here is we can maybe do a filter based on conversion value um so we can see products that based on if they have a high Rass we can start to see what their price percentage difference is now there won't be all products as I was saying earlier on won't have this but when it is there we can start to look at products that either have a high cost uh where we can start to see how much they've spent in this like 30-day period and you could do it based on costs or clicks or conversions and we can start to see where the differences is so for example well this top product doesn't but this product that has had uh five conversions the last 30 days 350% rise not good for this client this client needs a Higher One uh needs about an 8X Rous we can see that the price here is actually 5% more expensive uh than the Benchmark price as well so by doing these types of things this is like I've kind of gone all over the shop uh I could have even included clicks uh but this you can do this in so many ways and this is just one thing to do which is really important to really understand how your products or top performing products or how any products is comparing to The Benchmark of the industry so if you are 40% lower you're actually leaving a lot of Revenue on the table so whilst your R might be good it could be higher if you move this from 969 to maybe $13 and really understanding that price competitive is super important on Google but if you're too competitive you're actually losing out on margin as well so really start to do these types of analysis on your business uh on your e-commerce St because you'll start to figure out where the gaps are for your business per products I'll leave it there if you any questions please let me know I'm going to start to do more of these videos on how to use Google Sheets to analyze your data for your e-commerce and lead generation for Google ads and Facebook ads and just do it in a really practical way where you can see me actually doing it rather than providing a template to see that the expertise behind this is very clear you can do this yourself or I could do it for you if you do want to work with us so I'll leave it there any questions please let me know but remember like comment subscribe but most importantly share