GorillaROI - Live Amazon data into Google Sheets

In this week's webinar, I am going to show you how to use Gorilla ROI, which we are now using all the time and it saves us hours of time.

Gorilla ROI is a connector between Google Sheets and Amazon Seller Central which enables you to get live data from Amazon into your spreadsheets - no more manually inputting data.

Last week, I showed you how we are using SKUdrop.com to dramatically improve our supply chain and halve our inventory levels. That would not be possible without GrorillaROI to tell us how much inventory to send each week.

In this webinar, I will show you and provide sample sheets of how we use GorillaROI including:

• Managing our inventory levels in Amazon
• Sales forecasting
• Session and Conversion Rate data
• Analysing Reviews

Simple spreadsheets that you can build once and then they are always up to date.

If you have more that a few SKU's, I consider GorillaROI as an essential tool for your software stack.

Transcription

In this week, we are talking about GorillaROI.

Now, last week I talked about SKU drop and how we use that and how we send products weekly to Amazon. It would have been impossible really to do it properly without GorillaROI.

So what is GorillaROI, it's a connector between Seller Central Data, and Google Sheets.

Most the time I'm working in Excel, now I'm starting to use Google sheets instead, which is really good be able to share, especially with VAs .

You don't have to input manual data. It means that rather than going through your business reports, or going through data and pulling out information per SKU, you can just get it dumped into Google Sheets automatically.

And a lot of the things that we use it for. It's got live data, how much inventory we've got in Amazon, how much has been shipped, how much is in reserved, etc.

It's really useful for our goal to tell just exactly how much we've got, what we're going to do with it.

If you've got one product, I probably wouldn't worry about it at this stage, you could probably get that type of information. And you don't really want to be spending that money on a subscription, or something you're not really going to use, it's very easy to get into subscription overload in this in this business. And there's a lot you don't need. So this is if you've got more than a few products, and you are all you've got a VA doing something really useful. It just saves so much time.

Let's get into it. Okay, so what you need for setup is Google account. Gmail account will be fine. But you need to have authenticated. Gmail, or Google account makes it much easier to do a lot of things give they give you better limits, if you've got a proper Google account, we found that when my VA created a report, he very quickly ran out of space. It's in terms of the number of calls that you can make through Google to the Amazon API.

You need a subscription to Google ROI, $97 a month, up to 1,000 orders per month, and then $147 up to 6000 orders per month.

Then follow the installation checklist. It's pretty simple.

We connect up Google ROI to your Amazon account, install some things on in Google Chrome, and you're pretty much right to go. In each spreadsheet, when you create them, you need to enable Google ROI and add the subscription ID.

They've got some templates for ideas and they charge you for the Pro versions. Yyou could use them I found them a bit difficult to follow, prefer building them simply from scratch

Here is the link to the spreadsheet that we're kind of created along the way. It's just an empty version this stage. But just save that to where you need to.

Okay, so it's there, it's got all kind of all the calculations you need into it and all the formulas you just need to basically fill in your SKUs and your ASINs but let's go through that now.

Okay, so one of the things I recommend that you have is a master inventory file I'll show you in a sec. This basically contains all the information that you need in relation to your SKUs all the time so it's got the SKU ASIN EIN number the short product name, product costs, freight costs, landed unit costs, units per Carton, carton size, cubic meters cubic feet there's some calculators in there that converted all from metric to Imperial freight cost calculation. cubic meters to unit so there's that little one and then I always have all my skews for all my files in all this in this Same order just makes it a bit easier. So I kind of referenced them. So I'll just show you our one for that.

So this is a master inventory file. So it's got all our skews. It's got our asin, ein numbers, it's got a short name, it's got our product costs, freight costs, lead ID unit costs, we can, I can sometimes group them together.

The carton size in metric weighed in metric cubic meters as a calculation for cubic meters in there. The weight per Carton, volume weight, if you've got products that are, okay, lots of air in them, or very heavy cetera, it will give you a volume weight there, there's a base, you can change that base for how it's calculated, I don't have very many things on volume weight. If you do, you probably know what I'm talking about. Some calculations in relation to cartons in Imperial size, weight and pounds, etc. It's got some calculations that if we change this figure here, let's say it's $284 per cubic meter, it will give you the rate the cost per unit, which will feed back into this one, if you connect it up. It's also got some rates if it's per kilo, right? If it's in if you're sending about airfreight as well. And I've also added a couple of other formulas in here.

It's not in the sample, but there's a fee is estimate just puts in the FBA fees and referral fees.

So the Master Inventory File is really handy thing, I use this all the time. Because when were updating our 52 week cash flow, these are the products always in the same order, when using LinkMyBooks, all these products, we've got the freight costs in here, we can easily make a little spreadsheet up to upload on that. We use it you know, when he was setting up new products in Amazon, then we've got all the details of what the weights are, etc. So we use this all the time.

So that's the first one master inventory file. Okay, so let's go to the next one.

Which is some tips. Okay, there's a couple of and you'll see this later on, I'll show you in Google ROI it needs you need to use ranges.

But basically, you make one formula, and then Google Sheets will populate the rest. So just what it means is you're making one call to the API rather than 20 calls. And it just means that if you've got a lot of products, you can very quickly use up your limits in Google, because you're making too many calls.

So that's just a simple way to make. I'll show you how that works. It's pretty simple. When you're using historical data, grab the make the call, calculate all the figures, and then copy and paste it over as values.

So you're not making that call, again. Use that master inventory file.

Strings. Basically text in formulas is always in quotes. Okay. And when you're copying the formulas, we what we could use absolute references. So it's $1 sign. That means that when you copy that formula across, and it's got $A$1, it means that that's always going to refer to that particular cell as you copy them across.

Okay, so this is pretty useful - Amazon sales history. So sales for any period of days, weeks, months, years, quarters per SKU, manually, this would take hours.

If you're even updating it once a month. It's a pain, it takes a while or once a week, whatever. Using this formula takes minutes, and then it's done. So there's a whole bunch of formulas in there. I'll go over those in a bit. But this is one that I use just some of these ones that I use a lot. One of them is called =gorilla_salescount. And then it's got the seller ID the period the marketplace SKU range shipped when I'm using This for inventory forecasting I'm using on the Shipped basis. There's a no in there, start date end date. And that's kind of how the formula looks.

Let's just go to this one. Okay, so, GorillaROI, have a list of all their formulas, there's a little video on how it works. They've got different ways that you can use it syntax along the way, and description, and I'll tell you all the different code status, etc. So you could have all sales. You could have shipped, you could just put, okay, how many sales are canceled in there, if you wanted. So there's a lot of different things in there. Whether includes merchant fulfilled, start date, etc. There's different full list of periods. There's a set of periods here. Yeah, so you can do it for different periods. Yeah, today, this month lasts three months, blah, blah, blah, there's heaps of options. When you putting those ones in, you will just put it in as a quote. Let's get back to this. Okay. So let's have a look at how this one works.

So this is a sales history. We've got all our SKU, our ASINs. And in here, it asked for the seller ID I usually put the seller ID at the top you can, rather than a big long SKU, you just put it in as a text. And just here it says notice, it's a bit hard to see when I separate it make this bigger.

In this formula, we've got the formula, which refers to this range. So you can see here, it's refers to the whole range. And so we only have to put it in once, and then it will populate to the next one doesn't, it just has the value. So for example, if we wanted to just copy that all across, after March here at loading data, And basically, we have loaded all our sales for all our products for the last year and a bit. If you think about how long that would take you, whatsoever, then when I do, I will grab all these lead match there. And I will copy that, and then paste it over as values. So it doesn't need to call that information again, because February sales are done. So really quick way of just grabbing all your sales data. Okay, you put a sum in here. Okay, then you drag that across. Okay, easily done. So that's, yeah, you can do have weeks in there, we've just put a couple of formulas in here. So we put in a date in there, and then just, it has to be formatted a certain way. But when we get round to doing April's, we can just grab this formula and drag it across. And it's all done. So that's a process. Yeah, that would have taken someone a long time. But you can grab for any period you like in there.

We'll use our 52 week cash flow model. Okay, you can use it for just populating that you just have the weeks in there. So that's, that's the first one easy one most common one that most people are going to use.

Okay, this one's the next one we use all the time. It's live data on how much Amazon how much inventory you have in Amazon. By product. So in Amazon warehouses will fillable how much is sellable at each shipping stage working shipping shipped receiving, how much have reserved our orders, FC transfer processing. There's a formula here you can have a look through those formulas how they work, I will quickly show you what it does. Your spreadsheet may not have the pretty colors in this one. So we've got our SKU and our asin this is all the stock we've got.

This just also each one of these is a term in GorillaROI that you kind of refer to. This one's also this is all shipped and in the warehouse says how much is in the warehouse? How much is fulfilable? How much is sellable? unsellable.

Then we've got How much does shipping, how much is shipped. So this is the working orders.

In FC transfer, there's another seven processing. To be honest, I don't know what that is. That could be just processing the orders type thing. And then there's another amount for researching, I don't know what researching is. Where this is really handy is if you're thinking people are kind of using up your stock. Now and by having these, you can have a look at this and just see where this is positioning, I think this is when you when people save it to the cart are but not yet processed in that stage. This is when it's actually paid, all done. And they're processing it FC. And this is FC transfer. So this guy in between variety of things in between the F and the fulfillment centers. Okay, so really useful.

Okay, inventory planning. So this is where we're going to bring those two sets of formulas together. So we want to aim to have 42 days of inventory in Amazon, and 42 days of inventory shipping into Amazon, assuming six weeks shipping time.

days of inventories is units of inventory divided by the sales per day.

The first thing we need to do is find out how many sales we make a day.

we might take seven days, 14 days and 30 days and then take an average to see how it kind of sits for this purpose. I don't know I think Amazon takes 30 days is when they calculate their days in there could even be more. Not quite sure. But we're just going to use take an average there.

Then we need to know how many units are on their way to Amazon, we need to know how many units we have in Amazon, then we can calculate the number of days and then calculate the how much we have to top up based on the inventory days per SKU.

This is all live data. So this will this is this will change every time you go in. So we usually have a process where we do our inventory shipments. Once a week, on a Monday, and on a once a day at the beginning of each month, we calculate how much inventory we need to get manufactured. I haven't built the manufacturing into this formula, but it's easy enough to do.

We have got all our SKU’s and ASINs. We've got a formula here that just says this is the sales count for the last seven days. This is the sales count for the last 14 days. This is the sales count the last 30 days. We've just calculated an average based on those particular each one, we kind of did a summary down here we are seeing a bit of a decline. So the daily average was 104. It's dropped down to 92 down to 88. We've got some out of stock issues that we need to fix up. So we've got a daily average and then we want to calculate well this is what we've got an hour is on so inbound working, inbound shipped somebody that's just using the gorilla inventory formula, how much was receiving total inbound? So there we can see how much stock we've got inbound, how many days we've got inbound, which is the total inbound, divided by the daily. We can see here we've got 36 on average days inbound, so it's pretty close to ourtarget. Then we've got this is what we've got an Amazon.

So we've got four fillable, unsellable reserved. In this formula, we probably should take out the unsellable in terms of the number of days. So this will just create our this is how much is in there, but we probably should. Yeah, it should be actually minus what's unsellable because that could be significant at times. So then we've got the number of days, we've got an Amazon, which is the stock than that not the number of days in warehouse divided by our daily sales. We can see here we're up to 77. So we're a bit over where we should be. And that's because we've got this a bit of a declining trend here. And so it'll say what we put out here is our target days. So we can see here on all the almost all of these were above our target. So we've got more than we need, we've got a couple in red. So I just put what's called conditional formatting on those. If you right click, I think you can do conditional formatting. So I basically said if value is greater than this figure, then color green if it's less than this one color red.

And we can see that we got a couple of reds in there, then we got a green. So okay, that's pretty much covered. We're not continuing that product. And then this one is total days in Amazon, inbound, and inbound and in Amazon, so divided by the number of days. So we've got 103 ad for pretty much on target. But 70 here. And there's a few less here. So what we've said is, well, this is our total inventory we've got. And we've done a little formula in here that basically says, if this is less than 84, then our target days, then we need to send in some stock. And it will tell so that we catch up to the Daily Sales average. So in some of these, okay, we don't need to send any more of this one, we do need to send in this one, I put in quantity per cartons, and basically it's saying I need to send in one carton or this one carton or there's two cartons of that one of those and 12 of those. And that's what I need to send to Amazon.

And that will of course update each week. So tomorrow that could be different. But if say we did a fulfillment order through SKU drop, then it would start showing up here. And here as the as the inventory was sent. And so these figures will be different all the time. If we process that today, by tomorrow, that wouldn't be showing on a dissenter on 39 units. You can also take that a little bit further. And you could say okay, we're going to look at the next. Yeah, target days could be plus 30. Over here, so we could have a manufacturing. And then we could say well, you'd have to then say how much you've got in in 3PL’s and SKU drop etc. But you could just keep on that same basis. It might be 30 days so your target days for manufacturing is going to be 114 and then calculate how much you have to manufacture each maths, what it doesn't take this formula doesn't take into account, what happens if you've got Christmas coming up or other things. And that requires a little more planning a couple of different ways that could be done, you might get to, okay, let's say you need to get your product in by November, take three, four months back from them. I guess it's July, July, you need to have a different inventory planning situation to take account of Christmas. Okay, so that's the way we do that. All those formulas are based in that sample sheet if you haven't a using Google ROI. Okay.

The next one we're doing. Okay, just having a look at some of the other formulas. I haven't used this one a lot. But refund rates, well, indicator of Amazon investigations and voice of the customer suspensions as painful as they are. So we can record and track refund rates by month and SKU. So there is a formula in there when it says, which I've written all those ones over. Let's see, we're here.

Let's make this nice and big. So it's as refund percentage. First, a couple of things, the formulas built in there, but it basically will give you a refund rate, sales divided by refunds. So you may end up with a situation where, yeah, December always looks a bit worse. Sorry, January, as people give their unwanted Christmas presents back. It also points to Oh, we've got a problem here. With these two products. People really don't like those ones. Say, Oh, my God, oh, we don't have Yeah, others are looking pretty good. 3.2%. Bit high there. So you can have a look and say, Oh, we've got a problem there with some of the products, we need to fix them.

So simple thing you can do, you can put some Yeah, just have a look very quickly over all your products as to what it is, this information, the refund rates is really hard to find in Amazon. I mean, you can look around a whole bunch of reports, and you won't find that there. So getting it out this way really useful.

Conversion rates, always harp on about conversion rates indicator of future organic ranking. So basically, if our conversion rates are increasing, we would expect our organic ranking to be increasing. If our conversion rates and decreasing. It's indicated that we're probably going to fall, the rankings are going to start falling. And it's things we need to fix. So we can we can get this data from business reports. It's time consuming to get and if you Yeah, if you're getting a VA to do it, it's time consuming for them, they could be doing other things. It's kind of a dark manual data entry is always prone to having mistakes in it. So GorillaROI now has business reports data, so you can also get sessions in there, you can also get a bunch of things, I just want to give you an example of conversion rates. So got conversion rates in here. I've colored them in this case, put some conditional formatting on them. So basically, if it's green, it's going up. If it's red, it's going down. So we can see here, okay, we're going up. We've got some problems along the way with some of our products, others are sitting okay, we can see all our products in terms of the conversion rate. So we've got some products that have a really good conversion rate. Others

are not so good. Always a good sign if your products good or not.

As to looking at the conversion rates. Some of these ones, it also matters which one you send your your advertising to as well. So we're going okay, well, these have declined a bit, which is a problem. We need to work with those to get them increasing. And if you're saying some of these products don't sell that many So that's what we get this pie variations on things. So when it comes to the next month, you can just drag that across, it will show you the the conversion rates per month.

Okay, other data you can get sessions, data sessions per business reports, finance data, haven't used this one, but you can get all the data respective your payments.

Out of stocks, kind of a useful one. So you can look, if you're doing budgeting and looking at those things, you can say, okay, what are the number of days you add a stock in a period? So you could say, Okay, let's look at our sales per period. And then say, if you're doing forecasting, we're out of stock for that period. What was the effect of that, or if we were in stock for those periods, what difference would have made our fees, you can see Amazon, Amazon fees per product, and referral, you might create a little spreadsheet that has your expected Amazon fees versus what Amazon are actually charging you. And those times when Amazon wrongly measures your product could throw up an alert type thing or red link.

Reviews, you can get all the one star reviews for product. Okay, I'll show you the reviews one, they did have a different and it's slightly different to that now. So what you can do is per product, you can put in the product SKU, the number of reviews, you can have five to get 100 in there. And it will tell you all the reviews. So we've got who was by their title, link to it, and what their further description was. So if you go through and go, Oh, okay, I've got a one star in there. That's a problem. You could change this around. Let's choose this ones

and we'll tell you, Okay, we've got five reviews. That's kind of all all the five reviews were added on that. Pretty good. Yeah, if you wanted, you could create another one for each product. You can bring in the photos of the product in there. There's a whole bunch of things you can do with it. Very useful bit of thing. This inventory planning one. We couldn't do SKU drop without this inventory planning.

Okay, questions