How To Calculate ROAS in Google Sheets

Showing you two ways to use sheets to calculate Return on ad Spend. ROAS. One way with formulas and one way with Apps Script. Learn more at Better Sheets: https://Bettersheets.co

Transcript:

How To Calculate ROAS in Google Sheets - YouTube
https://www.youtube.com/watch?v=yaj0vHIbsUQ

Transcript:
(00:00) Hey, in this video we're gonna calculate ROAS return on ad spend. We're gonna do it in two ways. I think this is gonna be a really fun video because you're gonna see how I do it in a how I create ROAS or. Return on ad spend in a formula, which is gonna be concatenate around and then a divide.

(00:21) Then we're gonna concatenate that with the ratio. And I'm also going to show you how to create a custom function, which gets into some really interesting parts of app script that I think absolutely blows away. Anything you might have already done in App Script if you've already been working with App Script.

(00:35) And it's very simple. So if you've never worked with App Script before, follow along in this step by step tutorial and we'll get there. What is return on ad spend? It's when you're spending money on advertising direct advertising, pay per click advertising, and you can have some ar not arbitration, some attribution as to what gets purchased Based on those ads, you can calculate the amount of ad you add spending you do versus how much revenue you make total.

(00:59) And usually what happens is this comes out to a ratio. These are ratios like two to one. Five to one, six to one, 12 to one. In the heyday of Facebook marketing, I think people were getting 12 to one or 24 to one and absolutely blowing away the competition and being able to run the add. Profitably. So we're not running ads in this video.

(01:23) We're actually just trying to calculate this and displayed in a ratio. This is the hard part, right? We can do calculations, meaning like percentage very easily. We can do equals we're just gonna do, here we have some revenue in column B. Let me label this revenue. And we have some spend in column C.

(01:44) Simply, we might be able to do equals B2 divided by c2, and this gives us a 0.79. Great. We can use this in a Google sheet, and you might already be using this as saying, okay, I understand this ratio. This is gonna be 2.8 to one. And then go and display that somewhere else. But we want to actually display this ratio because that's how we think our that's how we think, that's how we want to compare, right? We don't want to have to do the work, even if it's just a quick cognitive work

(02:13) of saying, okay, 2.79 is equal to 2.8. Colon one, maybe we wanna copy paste that text somewhere. Or maybe we wanna create a nice dashboard that shows, this month's or ROAS versus last month's ROAS. And we want to do that in a sheet and we don't want to type that out. We wanna actually just get the answer again.

(02:31) The first way we can do this is taking that re that percentage B2 divided by, let's do that B2 divided by c2. Here we go, and we're gonna wrap that in around, I'm gonna round that to two places. So if it might be a little too much if it's three places, two places, it's up to you what you want to show.

(02:54) But that's not it. We need to add the colon one. So how do we. E Super easy. Again, we're gonna use a combination here of round and concatenate. Concatenate. Make sure we spell that correctly. And what are we gonna concatenate? The thing we want to concatenate is colon one. We're gonna put that in quotes, colon one, and then put an n parenthesis here.

(03:16) And there we go. We have now displayed, we have formatted this text from percentage, right? 2.7, nine, blah, blah, blah. And now we can. Copy paste this down. We might, let's see if we can do this. I don't know if we can do this with array formula. I always wonder, some things we can do array formula in some things you can't.

(03:35) Let's test that out. Now. We want array formula. We just have to change B2 to colon B and C2 colon C we have a divide by cro. Oh, probably because, Of this maybe we can do. I wonder if this is gonna work, if is blank c2. So let's move that inside the array formula. Oh, if is blank. Let's just take a little moment and see if this works.

(04:02) Blank. C2, the C. Oh, we get a bunch of divide by zeros. Okay. So it doesn't seem like maybe it's this round that we can't do or this concatenate, but it seems like a ray formula can't work here. That's good to know that we can't do that. We have our formula that we can copy paste throughout the whole thing.

(04:20) But now what if we want to do that with a more elegant solution? If we want to say, display this text with more elegant solution, not in display, but in usage. Let's say we're creating a sheet for clients. Maybe you are already a pay-per-click agency, or you're doing some freelance work and you're like, I would love to do the math for people and I would love to get this solution, but seeing this formula concatenate, round colon one, this is not elegant.

(04:50) Someone can look at this, see it and be like, oh, okay, I can do that math myself. What if we just created a function that's built into Google Sheets called ROAS, and we got the first thing, which is revenue, and we got the spend and we figured out the ratio there. How do you do that? I will show you step by step.

(05:09) So we'll go to extensions. Do not worry. This is not a horrible thing. It is literally four lines of code here and we're gonna write it all out. And this crazy thing in front of it is what's called JS Doc. And I will go over that as well because what happens is, If we G do equals, let's say we created another function called cpm.

(05:34) CPM and we use that right now, you, it can, it doesn't look like it's actually a function, but if we use ROAS, which has that JS stock there, R O A S, it has auto complete. So now other people using your sheet are gonna be able to use this. Very easy. You'll be able to give them a note. It'll say what it actually does.

(05:53) Return the return on ad spend ratio of two inputs. You can, they can click on it, they can see an example. They can see right here, example ro and all of that is in this formatted text here. And again, I will show you that piece by piece. But first, let's create the function. I'm gonna go over here and rename this to.

(06:14) A few Rs r ROAS, and we're gonna rewrite this as I did before function ROAS. That's all you need to do. We're gonna. Revenue. We need two inputs, revenue spend. That's all we need to do. Go over to the end of the parentheses, add a curly brackets, hit a couple new lines, and now we are gonna create our.

(06:37) Function. What do we wanna return? We want to return our result, whatever the resulting ratio is. Okay? How do we get the resulting ratio? First off, we need to, the first thing that we did earlier, which is variable percentage, could be percent. It doesn't matter what we write here, percent or percentage equals revenue divided by just the slash.

(06:56) That's all we have to do here. The math in math. JavaScript and app script. It's just math divided by spin. Okay, great. But now how do we make sure that's rounded to two places? We're gonna create another variable rounded, and we're gonna get equals, we're going to take that variable that we just created, percent, and we're gonna, we're gonna do the function, two fixed, two fixed, and we're going to round it to two places.

(07:23) All we have to do is put in parentheses the number two that. And now the rounded is gonna take that percentage, which is revenue divided by spend and fix it to two places. Cool. But now the next thing is we need a CanCan, but we don't use the function concatenate in JavaScript or in app script here. We want result equals, we're just gonna get the round date number, whatever that number is.

(07:47) We're gonna use the plus sign here, and we're going to do in quotes colon one. So just like before, In our calculate ROAS. Here, let's delete that. We had concatenate with this colon one because  percentage is just based on one. We've normalized it down to one. Not 1001, not 2000 to one.

(08:09) So we just need to add that colon one. And here in Cript, instead of using it for a function called concatenate, we literally use the plus sign. This adds these things together. But we'll do it in a text. We will be like combining these two texts. The plus sign does that not concatenate. Alright, let's save this.

(08:29) And now let's check if our ROAS works. Our new function. We always wanna check just in case we miss something. Our revenue is going to be b2. C2 is our spend. Now it shows us unknown function. If we hit enter, it'll say loading, and it still works. Even though it says unknown function, it still works.

(08:49) This is great. We can keep using, we can keep trying this out. Okay. It all works. It's correct, but we want to make it much more. Then unknown function and this red line we wanna have auto complete here. So how do we do that? All right, let's look at our Js doc and we're gonna do slash star. Star.

(09:09) It's gonna give us this, it'll auto complete this. So far. We're gonna write returns, turns the ROAS. We're gonna do something different than we do down here, just so that we know that it's completely different and turns the ROAS of your numbers. This is very poor help here. An example is ro 3,001,000 returns three one.

(09:38) All right. Hit enter. We're still in this comment, which is slash star. Star, and then at the end, S star slash or asterisks. Now we have a parameter, which we have two parameters here actually, and we can do curly brackets. We're gonna do, let's do, put our example here. Revenue. We don't want, actually want two spaces, I think.

(09:58) Revenue. The, no. Give me one more space. At Peram. We have a second one here. This is not as important, I think, but it will help. It'll help see PE people to see. How this function works when you put it in there. All right, we're gonna hit enter again. And the most important thing with JS Doc and App Script and these custom functions is at custom function.

(10:26) I'll show you what happens if we don't have that in there. One second, but let's just make sure this works as prescribed as we. There it is. Now it is auto complaint. ROAS. It is on. The list. We can even, I think, let's see if this works. If we rename this ROAS in all caps, I'm wondering will that actually.

(10:50) Makes it. There it is, ez. Now it looks literally as another function in this list. It looks no different whatsoever. It has the helper here, ROAS. If we click on it, it has an example. It has the about section that we wrote at the top, without having to say about it has this revenue, amount of revenue, it has the spend as we type it in 4,000.

(11:12) Actually we can reference b3. It will automatically go over to the next one's spend. It's highlighting here the spending amount. Okay. C3. Let's double check that. Got it. Correct. Perfect. So now what happens if we don't have this custom function? Again, this is the most important part of this text up here.

(11:30) We have some text up here. Actually. I think we can get rid of all this per stuff too. We, it's n unnecessary, but it's super helpful sometimes when you have different parameters and you wanna explain what those are. But what about this custom function? What happens if we don't have 'em? What if we just put the text in and we're like, oh, we forgot custom function and you didn't remember custom? What would that look like? Go back to R.

(11:53) See, now it looks exactly like it did before. It does not have auto complete, but it still works. It will still work. B2, c2. There we go. It still works, but it doesn't have the auto complete. So if you want auto complete, just add at custom function. Right here. There you go. This, that's two ways to write ROAS into or create ROAS or calculate ROAS in Google Sheets.

(12:18) We did a formula here and then I showed you as if you have not been in app script before, I showed you a very simple way to do it in app script and this custom function text, this JS doc text up here, which I think is just one of the most magical things inside of Google Sheets. We can absolutely. Create these kinds of functions and calculations in a in a function using formulas, and it's totally okay.

(12:42) But we can do better, right? We can create an app script that does it more elegantly, especially if we are an agency or we have client, any kind, clients, a freelancers or we're creating this for someone else to use. Including if you work at a company and you're trying to make some sheet for your.

(12:59) Boss or your coworkers or even your employees to use consistently. And you don't want them to have to remember formula and go back to old sheets. They can just take this app script and just start doing the calculations themselves with your math, right? And, but even with that app script, it can be better, which is adding this JS doc makes it so much more elegant.

(13:21) This custom function that you write yourself gets embedded into Google Sheets. Thanks for watching that video. If you're looking for that sheet that you see inside this video become a member of better sheets today, it is one of the only ways to get every single sheet, every single template, every script that I write, you can get a as a member.

(13:42) So become a member today at bettersheets.co if you'd like to see if chat GBT can write script or can even automate spreadsheets. Go check out this video, where I attempt to use chat GPT to automate spreadsheets. And if you're really interested in learning more about programming inside of Google sheets, here's A very quick guide to programming in Google sheets.

(14:04) You can learn a program in Google sheets if you already know how to program, but it also is a very good introduction to Google apps script.

Google Sheet Formula Frustrations Solved

Find every formula you'll ever need in Google Sheets here at Better Sheets. Whether you're a beginner or an advanced user, I’ve got you covered with a comprehensive guide of 504 formulas.

Are you struggling to find a specific value in a column of data? Look no further than the powerful VLOOKUP formula. Or maybe you need to calculate the sum of values that meet specific criteria - try out SUMIF. And when it comes to frequency of values, COUNTIF has you covered.

Have you heard of the mysterious and powerful IF formula? It can turn your spreadsheets into gateways of productivity. And don't forget about the oft-partner ISBLANK().

Find step-by-step tutorials for any formula here on Better Sheets. Every formula page comes with links to written blog posts and Better Sheets tutorials featuring the exact formula.

Looking for a way to segment data based on specific criteria? The FILTER formula is perfect for you.

If you need to replace VLOOKUP, give INDEX/MATCH a try.

At bettersheets.co/formulas, I have everything you need to take your Google Sheets skills to the next level.

From ABS() to ZTEST(), Better Sheets has it all.

Become a Better Sheets Member today