Calculate ROAS in Google Sheets

Intermediate

About this Tutorial

Calculate Return on Ad Spend with a formula combination. Bonus: create a more elegant solution in Apps Script using JsDoc syntax to create an autocomplete native function. We use the formula combination of Concatenate / Round to create the ratio, but in Apps Script we do it a whole lot better.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

CONCATENATE() ROUND()

Video Transcript

<div>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. 00:20 And then a divide, 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 Apps Script that I think absolutely blows away anything you might have already done in Apps Script if you've 00:35 already been working with Apps Script . And it's very simple, so if you've never worked with Apps Script before, follow along in this step-by-step tutorial and we'll get there. 00:43 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 arb- not arbitration, some attribution as to what gets purchased based on those ads, you can calculate the amount of ad you, ad  01:02 spending you do versus how much revenue you make total.

And usually what happens is this comes out to a ratio. 01:09 These are ratios like two to one, five to one, six to one, twelve to one in the heyday of Facebook marketing, I think people were getting twelve to one or twenty-four to one and absol- blowing away the competition and being able to run ad profitably. 01:27 So we're not running ads in this video, we're actually just trying to calculate this and display it in a ratio. 01:34 This is the hard part, right? We can do calculations meaning like percentage very- easily.

We can do equals what we're just gonna do here, we have some revenue in column B. 01:48 Let me label this revenue and we have some spend in column C. Simply we might be able to do equals B2 divided by C2 and this gives us a 0.79. 02:01 Great! We can use this in a Google Sheet and you might already be using this and saying, okay, I understand this ratio. 02:08 This is gonna be 2.8 to 1 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. 02:17 That's how we want to like compare, right?

We don't want to have to do the work. Even if it's just a quick cognitive work of saying, okay, 2.79 is equal to 2.8 colon 1. 02:29 Maybe we want to copy paste that text somewhere or maybe we want to create a nice dashboard that shows, you know, this month's ROAS versus last month's ROAS. 02:38 And we want to do that in a sheet and we don't want to type that out. We want to actually just get the, answer. 02:43 Again, the first way we can do this is taking that percentage, B2 divided by, let's do that, B2 divided by C2. 02:54 There we go. And we're going to wrap that in a round.

I'm going to round that to, B2 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. 03:11 But that's not it. We need to add the colon one. So how do we do that? Super easy. Again, we're going to use a combination here. 03:22 Of round and concatenate . Concatenate , make sure we spell that correctly. And what are we going to concatenate ? The thing we want to concatenate is colon one. 03:31 We're going to put that in quotes, colon one, and then put an end parentheses here. And there we go.

We have now displayed, we have formatted this text from percentage, right? 03:41 2.7. 9, blah blah blah. And now we can copy paste this down. We might. Let's see if we can do this. 03:50 I don't know if we can do this with array formula . I always wonder, some things we can do array formula and some things you can't. 03:57 Let's test that out now. We want array formula . We just have changed B2 to colon. B and C2 colon C. 04:07 We have a divide by zero. Oh, probably because of this. Maybe we can do, I wonder if this is gonna work. 04:19 If is blank. See. C2.

So let's move that inside the array formula . If is blank. Let's just take a little moment and see if this works blank. 04:41 C2. C2. 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 it's concatenate . 04:52 But it seems like a array formula can't work here. Well, that's good to know that we can't do that. So we have our formula that we can copy paste throughout the whole thing. 05:02 But now, What if we want to do that with a more elegant solution?

Like, you know, if we want to say display this text with a more elegant solution, not in display, but in usage. 05:15 Let's say we're creating a sheet for clients. Maybe you're already a pay-per-click agency or you're doing some freelance work. And you're like you know, I would love to do the math for people and I would love to get this solution. 05:29 But seeing this formula concatenate round colon one, this is not elegant.

Someone can look at this, see it and be like, oh, okay, I can do that math myself. 05:40 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. 05:54 How do you do that, right? I will show you step by step.

So we'll 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 do equals, let's say we created another function called 06:22 CPM, CPM and we.

Use that right now it can it doesn't look like it's actually a function but if we use ROAS which has that JS doc there ROAS it has autocomplete so now other people using your sheet are going to be able to use this function very easy you'll be able to give them a note it'll say what  06:41 it actually does 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 ROAS 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 . 06:58 I'm gonna go over here and rename this to few r's and we're gonna rewrite this as I did before function ROAS that's all you need to do. 07:12 We're gonna do revenue we need two inputs revenue comma spend that's all we need to do.

Go over to the end of the parentheses add a curly brackets. 07:24 Hit a couple new lines and now we are gonna create our function . What do we want to return? We want to return our result whatever the resulting ratio is. 07:37 Okay how do we get the resulting ratio? Well first off we needed the first thing that we did earlier which is variable percent percentage could be percent. 07:47 It doesn't matter what we write here. Percent or percentage equals revenue divided by just the slash.

That's all we have to do here. 07:54 The math in, in JavaScript and Apps Script it's just math divided by spin. Okay great. But now how do we make sure that that's rounded to two. 08:04 Two places. Well we're going to create another variable, rounded. We're going to get equals. We're going to take that variable that we just created, percent, and we're going to, we're going to do the function too fixed. 08:18 Too fixed. And we're going to round it to two places. All we have to do is put in parentheses the number two. 08:24 That's it.

And now the rounded is going to take that percentage, which is revenue divided by spend, and fix it to two places. 08:32 Cool. But now the next thing is we need a concatenate . But we don't use the function concatenate in JavaScript or in Apps Script here. 08:39 We want result equals. We're just going to get the rounded number . Whatever that number is. We're going to use the plus sign here. 08:48 And we're going to do in quotes, colon one.

So just like before, in our calculate row as here, let's delete that. 08:58 We had concatenate with this colon one because that percentage is just based on one. We've normalized. We've normalized it down to one. 09:07 Not one thousand one, not two thousand to one. So we just need to add that colon one. And here in Apps Script , instead of using a function called concatenate , we literally use the plus sign. 09:19 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 . 09:28 Alright, let's save this. And now let's check if our ROAS works. Our new function . We always want to check just in case we miss something. 09:38 Our revenue is going to be B2, comma, C2 is our spend. Now it's value here is, we can keep going. 09:45 It is a B2. It shows us unknown function . If we hit enter, it will say loading. And it still works. 09:50 Even though it says unknown function , it still works. This is great. We can keep using, we can keep trying this out. 09:57 Okay, it all works. It's correct.

But we want to make it much more elegant than unknown function in this red line. 10:04 We want to have auto complete here. So how do we do that? All right. Let's look at our JS doc. 10:12 We're going to do slash star star. It's going to give us this, it'll auto complete this so far. We're going to write returns the ROAS. 10:23 We're going to do something different than we do down here just so that we know that it's completely different. It turns the ROAS of your numbers. 10:30 This is very poor help here. An example is ROAS 3000 comma 1000. It turns three colon one.

Right, hit enter. 10:51 We're still in this comment , which is slash star star and then at the end slash star slash, or asterisks. Now we have a parameter . 11:01 Which we have two parameters here actually and we can do curly brackets. We're gonna do. Let's do put our example here. 11:10 Revenue. We don't want to want two spaces. I think revenue. The. Need one more space. At peram. We have a second one here. 11:24 This is not as imp. Important, I think. But it will help. It'll help see people to see.

How this function works when you put it in there. 11:40 Alright, we're going to hit enter again and the most important thing with JS doc and app script and these custom. 11:47 Functions is at custom function . I'll show you what happens if we don't have it in there one second. But let's just make sure this works as as prescribed as we there it is. 12:00 Now it is auto-completing R O A S. It is on the list we can even I think. Let's see if this works if we rename this R O A S in all caps. 12:11 I'm wondering will that. Then actually makes it. There it is. R O H S.

Now it looks literally as another function in this list. 12:25 It looks no different. What's. So ever it has the helper here. Row as if we click on it. It has an example. 12:32 It has the about section that we wrote at the top without having to say about it has this revenue amount of revenue. 12:37 It has the spend as we type it in 4000. Actually we can reference be three. It will autumn. It automatically go over to the next one spend. 12:49 It's highlighting here the spending amount. Okay. See three. Double check that got it correct. Perfect.

So now what happens if we don't have this custom function again? 13:00 This is the most important part of this text up here. We have some text up here. Actually I think we can get rid of all this program stuff too. 13:07 We'd. It's not unnecessary but it's super helpful sometimes when you have different parameters and you want to explain what those are. 13:14 But what about this custom function ? What happens if we don't have one? If we just put the text in and we're like, oh we forgot custom function and you didn't remember custom function . 13:22 What would that look like?

Go back to our. See now. It looks exactly like it did before. It does not have autocomplete but it still works. 13:33 It will still work. B2. C2. There we go. It still works but it doesn't have the autocomplete. So if you want autocomplete just add at custom function right here. 13:48 There you go.

This, that's two ways to write row as into, or create row as or calculate row as in Google sheets. 13:55 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 is. 14:08 I think is just one of the most magical things inside of Google sheets. You know, we can absolutely create these kinds of functions and calculations in a in a function using formulas and it's totally okay. 14:22 But we can do better, right?

We can create an App Script that does it more elegantly, especially if we are. 14:28 Are an agency or we have client, any kind of clients freelancers, or we're creating this for someone else to use. 14:35 Including, if you work at a company and you're trying to make some sheet for your boss or your co-workers or even your employees to use consistently and you don't want them to have to remember or formulate and go back to old sheets, they can just. 14:48 Take this app script and just start doing the calculations themselves with your, with your maps, right?

And but even with that app script , it can be better, which is adding this JS doc makes it so much more elegant. 15:03 It makes it off. This custom function that you write yourself gets embedded into Google sheets. Already. Now, one last thing we'll say before going anywhere is that there's one extra thing you can do, which is you can push this out as an editor add on. 15:21 And then anyone who installs that into your into their sheet will get this app script and they can't see the app script . 15:28 So.

If you're ever writing this app script and you're like, oh, I wonder if people want this, but I don't really want to expose this script. 15:35 You can do that by deploying as an add on and then sharing it to the add on marketplace. But I won't go through that in this video. 15:47 If you're looking for more of that. Actually take my live class spreadsheet automation 101 will go through that kind of stuff easily if you if you want. 15:55 Thanks for watching. Bye.</div>