Create an Email Campaign Stats Calculator

<div> Hi. So I'm in the middle of creating a bunch of templates and I thought I would make this video because in this particular template we're gonna create a calculator. Uh, it's very important for businesses and small businesses, freelancers, anyone who is working in Google Sheets a lot to know how to. Calculate things, how to use formulas to do math. Um, but also it's super useful as a promotional tool to create a calculator for your customers, uh, either as. The thing you sell them, you can sell a calculator, um, or to promote your work.

To promote, you know, he healthier, wealthier people. Um, calculators help people do more with less because they don't have to figure out the calculation . You can do the calculation for them, set it up, and all they they have to do is. Put in the inputs and they get the outputs. So, um, I'm making this template . This template is going to end up being promoted by AppSumo for free. Um, but obviously not for free is how to make it.

And I'm gonna give you in this video, One thing that won't go into the final template . So first I'm gonna share with you how to create the calculations with formulas. We're going to have inputs and outputs. We already know what we're doing. We're doing an email campaign, metrics calculator. We have our inputs here, we have our outputs here, and we know our formulas. That's what I already set up. Um, we might have some nuances here. Generally speaking, we know what we're doing or we think we know what we're doing. , I'm gonna, um, share with you how to design the inputs.

I might not get it all done in this video. Uh, it might not look exactly like the output, the the final template in this particular video. I want to try to keep it, uh, I want to keep this video comprehensive, but I will also, uh, feel like if I just show you a couple of ways to do. . Um, you're gonna understand how you can take over and do what you want with what you've got. Uh, but the last thing is I'm gonna show you how to write a script.

Um, I have some other videos about like intro to scripting, and they go into this a little bit. There's one part where I show you how to do math in a and you're writing in a script, but. This is it actually in, in action. I'm gonna show you how to do that. It's really fun. Uh, you create functions yourself with scripts and then you can use them in your, um, Calculators, but that won't end up in the final template . Okay. So, um, so that's a little bonus you get for watching this video. Uh, stick around for that. So, how to create the calculations .

Well, let's, let's go through what we have. We have our, uh, what we need and what we have. We have number of subscribers sent. We're doing what is this, uh, email campaign. So we're sending emails and we wanna know, You know, some of these calculations might end up being in a crm. We might not pay for a crm, or we have the individual's numbers, but we don't have the statistics that we want.

Um, or maybe there are some statistics that we wanna grab, but what's gonna happen is if we put them in this calculator and we figured out that statistic, and then there's some simple statistics like open rate, click the rate clicks, click percent. that are in say, a, a platform like MailChimp or even, um, Convert kit or if you have a platform and, and you personally use this, but you need to, uh, broadcast out, you know, you need to send to, uh, a report to either people working on email, art, email copy, bosses.

Maybe you need a, you have a all hands meeting or you have clients, you're like, oh yeah, they don't, you don't wanna have them check MailChimp. You really just wanna send them a little concise report and you do this calculation time and time again. You have some customers that maybe you are a consultant and you're helping them build up their, their email campaign metric, uh, library in their head. And so you really wanna just show them, um, these calculations and, and what output they get, uh, or track it. Maybe there's like a tracking.

I'm not gonna, this is not a tracker, so we're only gonna need, we only need to do a calculation . . If you watch some of the other Better Sheets videos, I'm sure you can create a tracker and even get some more interesting insights like average over the last six months or 6 cents or 10 cents, um, set up dashboards and stuff for you. But we're not doing that. We're doing a calculator. Um, so all we need is these inputs. We have number subscribers that are sent the. We have the number of opens, number of clicks total cuz we're imagining.

This is also probably email marketing, not necessarily like a newsletter that's gonna have a lot of, uh, links. We really might have, you know, one or two call to actions, calls to action, uh, number of sales. We wanna know we're making money on this, right? Um, and then we'll probably be able to get the revenue from that, from the number of sales. What we're missing here, as I look through this is.  amount per sale.

So we might have to hard code that in, or we'll just add that here. Um, revenue per sale and depending on, so it wasn't in the original needs, so maybe we don't know what the unit is. We just know how much revenue, because perhaps there's a discount code or perhaps I'm just making up a story. Um, really, we probably do know exactly how much revenue we are making per sale. And then we get, so then the result is the revenue from the campaign. So actually I'm gonna move that down. Um, if we know what, what are we selling, how many of it we sold?

And then campaigns spend is interesting. Did we spend some money in addition to, you know, the time it took to do this, but did we spend some money? Freelance copywriters did we spend some money? So we might have some, you know, just metric, some, some round number we might want to put in there. Metric round number , like a thousand bucks we spent on, you know, getting it written, whatever sent. So we might have something from there. So that'll come into play when we do campaign roi. Okay, that's cool. Um, number of subscribers are gonna get us the open rate.

It's gonna get us the click rate. Um, there's gonna be two metrics actually. Here we go. We, this is it. So we have open rate where we're gonna do opens divided by subs. We have click through rate, which is gonna be clicks divided by subs. But then a very important metric is to know how many clicks per open. So we're gonna look at that as well. Um,  and then clicks to conversions. So we'll know the number of sales, uh, and we'll know how many of those clicks actually converted to sales.

Then we will get a campaign roi, which is going to be the total revenue, which is, uh, number of sales times the revenue per sale or per item, I guess, revenue. We're gonna subtract the campaign.  and then we're gonna divide by the campaign spend. So what's interesting is, I think when I originally said it should be total revenue divided by campaign spend or it's, it's not ROI because re revenue, um, for it would be profit, right? You wanna know how much did you profit, how much did you spend, but that wouldn't be profit. Okay? One second.

Typically ROI is not shown as a percentage. It's shown as a ratio of like two x, three x, four x, or three to one, four to one 12 to one. Um, okay, so this is gonna be interesting cuz what we're gonna have to do is we're gonna get a number , but then we have to convert that to a ratio cuz that's typically a marketing world. At least I've seen it's not a percent.  or a like 200 x or 1.5 x or 100% or 300%? It is three to one. Two to one. Okay. So let's put a note here. This should be a ratio and we're gonna have to figure out how to do that, right?

That's gonna be interesting to show that number . Okay. Let's start with a blank sheet. First thing I like to do is I like to just go ahead and delete all this junk. , um, let's just start with as few cells as possible. Okay? Now we need inputs and outputs generally on a calculator. Different from just making a really cool sheet that we're going to use. A calculator might be embedded. A calculator will have to have, um, we'll have to show the result right where your inputs are. So we're only gonna have one page. So calculator, calculate later. .

Um, if we need to do work, which we don't need to do, I don't think this time, but if we do need to do any work, sometimes I like to add another tab and call it work or scratch and hide it eventually. But we have calculator. That's what we will, um, embed maybe in a card. C A R r d dot CO's a really cool, uh, landing page builder. You can embed anything in, um, or just embedded on a plain HTML page. Um, it will work. . So the calculator will work. Um, okay, so let's start putting in these, we want all of these things. Now, typically I'm gonna do paste transposed .

There we go. Typically, you're gonna see, whenever you see like these kinds of, uh, what is it, like a metric, and then under.  or you might have like, uh, or under it, you'll have the input and perhaps, um, uh, to the right, like maybe these are all stacked here on the left and they, and you insert them as you go into a form. We wanna make it a little more, Hmm. How do you say visually? Interesting. Let me close that. Um, so let's do this First I want to upper everything. , this is gonna be really, cause I wanna make them labels. So let's do this. How many do we have?

1, 2, 3, 4, 5, 6, 6 things. We wanna make it all in one view port and a little bit less. Hmm. So let's make this six actually super small. Oh, it's way too small. , let's center everything. Let's center. And I'm gonna do something here don't normally do, but, uh, super quick. I can double click here and everything goes to its, um, to like the least amount, but I want to make it uppercase upper, so let's just do. Do that. Ooh, uppercase makes it really big. That's fine. We're just gonna uppercase everything. These are the main things and the one big visual thing we're gonna do.

This might not end up working out super well. Okay. Do that again here. . Okay. What I wanna do is I'm gonna make this double size. I'm going to actually make these all the same width, uh, and then right here, let's see if this looks good. Just do that. Nice. And we're gonna get rid of this view, these grid lines. That looks a little nicer. I want to actually give it a little more white space so if we, if we make them wider, right, they're gonna, oh, they'll look. Great. Number of subscribers sent. So we actually probably can just change this.

Two subscribers, subscribers. Total subscribers. Email opens, clicks. We know this is email, so we can actually do something like that. What I'm gonna do actually as well is command C, shift command B, just get past values , number of sales. Let's just change that to total. Let me type that. Total sales.  campaign spend. Let's change this to total spend. Um, revenue per sale is great. So now we have a little bit more white space. Let me see if I can even make it a little more. 16. It's good. Oh yeah, this is good. So we're gonna have these inputs.

You know what I know later, I'm gonna want these blocks. , I'm gonna make this a little bit bigger. I'm going to make the inputs this bright blue. And now what we can't see is that we want each of these to be on their own, right? We want you to see, um, these as inputs. So I'm gonna intercom to the left. We're going to just give it a little bit of white space. We're gonna go insert, right? We're going to command Y a few times. And just put these, insert these around and they're all gonna be the same. Right?

So we're gonna have some visual, some nice visual looks on this, but each of these is a different input. Perfect. Uh, I want to do two more insert rate, one on the end here and one on the far end here. . Okay, cool. So let's, the only issue is this total subscribers. Okay, let's do 15. Oof. Okay. What we're gonna do is I'm gonna hold down command, and this makes it super easy to design real quickly. Just make it a little bit bigger. Get that Rs oof. Total subscribers, man. Why do subscribers have to be such a large number or, um, . Cool. Revenue per sale. I'm gonna add just for visual.

Add a hard enter there command. And also one more thing I'm gonna do to visually make this a little more appealing, vertically aligned to the top there. So now you enter all of these. Cool. Um, I could, what I do want to do, okay, this is gonna be fun. I'm gonna have a border of. We're gonna do border black, but we're gonna do, um, the bottom and the right as this thick color. So I want to do the same on all of those. It's gonna look really funny, uh, but Interesting. Not funny, bad, but funny. Good. So I have that thick color there, but on the top and the.

, I'm gonna do the thin one and you'll see very quickly that it looks as if it is a drop shadow. Uh, I have another video where we go into actually how to do a drop shadow of like 20, but I think this is a cute little thing that may adds a little bit of an interesting touch to this calculator. Fill in the blue ones and we're gonna get the green ones. So what are we gonna get? Let's design this a little more. We got revenue from campaign open rate. Um, let's just put these down here. So what do we got? Should we just copy this here?

And instead of green, oh, instead of blue, let's do this shade of yellow. Right? And that's gonna be our. Outputs. So total subscribers. We want under here. Open rate. Oops. I'm gonna do a hard enter Open rate. So that looks cool. Click. This does not work. There we go. Right, we got click to open rate. Oh, I know why. Total. So I want to take these, put them over here. And then we want click to open rate. Click open rate. And now where we have total sales, we wanna know what do we wanna know?

Revenue. Okay, so we have over here on the left our sort of inside email, um, statistics, and then we have our revenue and our click to sales conversion sales. It's called sales conversion. Whoops, not conversation. Total revenue, sales conversion and campaign. Okay, so we are missing one thing. All right, we have open rate, click rate, click to open rate. Oh, total revenue is here. Revenue from campaign is here. So now we have six things. Six inputs that we're gonna get, six pain roi. Let's shift that down. All right, just a little fun thing here.

We're going to delete all of. Cool. We can absolutely hold down command, hold these and make these a little bit smaller. Um, I want to make the gaps a little smaller. One other thing we can do is I might make these smaller two just to make sure we fit in that. Um, . Let's just do 100. Boom. Perfect. Not perfect. These are all, these all look terrible. Let's just make these down to 12. That fits better. Nope, it doesn't. We can go down to 10. Oh my God. We need one more. Okay, we're going to do this again. We're gonna just make it a little wider. One 10. . Okay, that looks great.

So now we have our inputs up here. We have our exports, exports outputs. Down here, we know our calculations , so let's just put some numbers in here cause it's gonna look weird too. We're gonna say, uh, let's say we have 12,004. You know what? I didn't even format any of these. We'll format them now. Probably want to make it centered center.  and we don't want, we do want it to wrap , but we want it to be like 12, right? Okay. Let's say we had, I don't think we went around probably a 30% open rate. So what's 12,000? Four times 0.3 3,600. So let's say 34, 5 6.

We wanna make sure these formats, this is gonna all be a number , but not this. . This is the, so it's this pound sign. This will always have a comma here. It'll always add a comma to those numbers, but this one needs to be, um, up the same. That's total sales. Okay? Total spend is going to be a dollar. So let's say we spent, we're gonna come back and fix these numbers later. We had, you know, 234. , how many clicks we had? Mm, 657 revenue per sale. Let's say we're selling something like, um, I don't know, something. $99. Okay, so now these are all formatted correctly.

Why we do these first is because when we start doing the calculations , Google Sheets will try to help us and, and it'll figure out the format . Sometimes you'll see when this open rate, it doesn't do it. Exactly. So we're gonna do equals What's open rate? It's just D four divided by B four.  and we get this crazy decimal number . Well, we need to format it to a percentage. Google Sheets makes it super easy to format as percent done Click, great.

This is clicks divided by, or the uh slash here, total number of subscribers. And same thing, we're gonna format it like a percentage click to open. Now this is going to be equals the number of clicks per opens. And here, so of those people that.  of the ones that opened, how many clicked? 19%. These are good numbers. Total revenue. What is that gonna be? That's gonna be, uh, your total sales times or the, um, asterisks times L four, which is your revenue per sale. Okay.

And see now we have, we don't have to, uh, change the format of this because Google Sheets is like, oh, you're taking a number and multiplying it by an already formatted.  dollar amount or euros or pesos. Whatever you're doing there, you don't need to format it. Sales conversions. Alright, what this is, let me just go back and double check before I do it. Measure twice, cut once. Um, click to sales conversion. Number of sales from the email divided by email. Clicks. Okay, so total sale.

So this is equal to total sales divided by total clicks.  and then hit enter, and we're going to change that to a percentage as well. Great. Third of people. Now campaign roi. This is gonna be the tough one. So this is equals, um, total spent, total revenue divided by this total spent. So here, so now this is five point. . Okay, so if we do a percentage that's gonna be 587%, which is totally fine if you want to showcase this campaign, ROI as a percentage, do that. But right now I'm gonna problem solve and try to figure out how to show this as a ratio. Okay?

So what we would do is, let me, um, command Z. So we have five point. . So we had 5.8 times the amount of revenue then spent. Um, if you want to, here's what I would do. I would say concatenate , concatenate . And what are we gonna concatenate ? We're gonna concatenate this number , but we're gonna round.  to two places and then we're gonna concatenate with a text. So in in quotes, colon one. And so now this 5.87 to one campaign ratio is our revenue to spend. We've made, you know, if this was one to one, that would mean we have made as much as we.  if you want to.

So if your calculation is not that, it's the total spend, or sorry, the total revenue divided by the total spend, what you can do is right here is H seven, put H seven minus the spend. And that's 4.8, right? It's just gonna take that one off. But what that might be is if you take that off, you might not show it as a ratio, because now.  4.87 to one is not correct. Um, but so we're gonna just take that off sometimes if, if you wanna show the percentage, I would say take this off and say, okay, we've made this amount of revenue, percent out percentage over the spend. Um, cool.

So now we have built calculator, right? So we have our calculator and we have done it with the formulas. We've designed our. What we've done is we made, uh, just a little tiny fun thing here where we used the, um, border, the big , the border, big, uh, big border and the small border to make it look a little like it's a, uh, drop shadow again. I have another video. Oh my hair's a mess. Um, I have another video about how to do different borders than this, but I think this is like a really lovely little, really cool thing. Um, . Okay.

Now, as a bonus for making it this far into the video, you might not want to share these metrics or, or these calculations , right? Um, and if you embed this in a, um, web app or a website, someone can still see this calculation . Maybe there's some proprietary.  thing we wanna do, uh, you just want to sh showcase this in some other way. So what we're gonna do now is we're gonna do go to script editor , and I'm gonna create a script, but, uh, sorry. Function a a, a custom function . Uh, that is I'm, what I need to do is I need to, uh, name this.

So this is email campaign calculator. Let that. it might not say. So lemme delete all of this. There we go. Okay, we're gonna write function . Um, I'm gonna have, what are, which one are we gonna do first? Let's do the open rate, um, which is gonna be D four divided by B four. So we have two input. , we have opens and total subs, opens, subs, we'll call it. So we are just creating these four, um, variables. We need, um, these curly brackets now on either side of this. And what is our output gonna be? It's going to be opens divided by subs.

I'm gonna end that with.  there, but we want to then, oh, and then it's a percentage, so it will be, let's see what's there. Second. Okay, so we want to turn opens divided by, so we can put a space here. I. We wanna name this, right? We want to name it, let's call it open rate. Now, what will name it is important because we will, uh, use this name here, open rate to do this, and we're going to set this up. We, our opens is going to be right, same as that D four and our subs is going to be, So let's see what happens now. 2, 2, 2.

So if we go here and we go equals open rate, and we use the same thing. D four comma D four loading, and we get 0.2 8 76, right? So we got that. Same thing we can. Do this, we can do divide by or times 100. Let's save that. Show what that, that's now 28.7. We can do some other rounding, but I'm not gonna do any more rounding. It's actually, I'm gonna let the, the format do this as well, just to make it simpler. Just to keep it simple. We can then just apply that format and now we have the exact same answer. Right. What this allows you to do.

So now if we look at this, if we, if we don't have access to the script and we're some outside person and we say, oh, here's open rate D four B four, we can say, okay, opens clicks. But we literally do not know from just looking. Obviously we can tell if we understand the word open rate, um, we can understand, oh, this is gonna be D four divided by B four. But if it's some proprietary calculation , , perhaps a rating.

Perhaps you're doing some other statistics calculations , uh, beyond just a normal like divide. I cannot see from, from this data alone right here, this open rate D four four, I cannot tell that this is a divide. Obviously, I can see the result. I can be like, oh. , well, what's 34 53 divided by 1204. That looks oh, 28.70. That's similar, right? It, it will still take me a little while to figure out what this calculation is doing if I'm using a script and what the script or, or a, a custom function . Let's do one more now that you've seen the whole thing. Uh, let's do click rate.

Um, so let's do something more interesting. . Yeah, actually let's just do click right . So it's not, it's so interesting and you'll probably be able to figure this out on your own, but, um, what I need, I don't need to create any more, I just do function . I can do it right here. Click rate, got clicks, comma, subs. Now this, these words can be anything. I could do subs, I could do subscribers. I'm gonna put a curly brackets here, and inside the curly brackets, I'm gonna say return. And what are we gonna return?

We're gonna return whatever clicks is divided by subscribers. There we go. Can take that off space and then at the end, put a semicolon, click uh, save. So now see what we have here is I'm gonna, This equals click rate, uh, F four t4, it's gonna give us that error , right? That that underlying red market says unknown function , but we really know what it is. And it's exact same. It is 19.03%. Great. We have done a custom function . Isn't that fun? Um, You can also do this times, right? So let's do one more and then we'll be done.

Um, cuz I just wanna see, I wanna see just something other than, uh, the divide. So let's do, what are we doing? Total revenue. We're doing revenue per sale. Total sales. So let's just call it function . Total rev. And we're gonna get sales and price. You can name, name it, anything you want really. , we're gonna return sales times price and semi-colon at the end. We're going to do that. So total rev. Let's go here equals total rev. And in parentheses we have this com. This cool. Now we've lost our formatting , so let's go. Add that formatting back. Done and done. Isn't that cool?

And look at this, this function here is grabbing this as well. It's totally fine. Hasn't changed. Hasn't been affected. So we know this is all good. Um, if we wanted to, um, what I will be doing is I will be, um, making a copy of this. So down below of this video is a link to this exact sheet as. Um, I'm releasing this template as a different sheet, but it'll look exactly the same, but it won't have this custom function in it.

Um, so this custom function is only for you Better sheets members, thank you so much for watching this entire video. Please feel free to respond, ask me any questions you want about this template . Um, we. Done all of this. We've created the calculation with formulas. We've designed the inputs, and I showed you how to write scripts. Hopefully you, uh, had a good time watching this video.</div>