Count How Many Subscribers In a Company by BetterSheets.co

Intermediate

About this Tutorial

I download it from MailChimp and then I try to figure out which companies are subscribed to me most. And I do, I have to figure out the domain and count the domain. And we're gonna do that all in Google Sheets.

Featured Formulas

ARRAYFORMULA() COUNTIF() LOWER() SORT() SPLIT() UNIQUE()

Video Transcript

<div> Hello, newsletter Writers Creators newsletter. It's Andrew. I'm a fellow newsletter writer. I write Influence Weekly, and I also just created Better sheets.co, which is helping everyone learn how to do better Google Sheets in this free tutorial. I'm  doing what I've done on a many times with my newsletter subscriber list. I download it from MailChimp and then I try to figure out which companies are subscribed to me most. And I do, I have to figure out the domain and count the domain. And we're gonna do that all in Google Sheets. We're gonna do that right now.

And let me just first discuss what the reasons are for this. Well, I like to reach out to CEOs or decision makers who already subscribe, and I like to know how many people subscribe to my newsletter to understand what their connection.  with me already.

I wanna know also if I have the employee count, like maybe I go on LinkedIn and I just find out how many employees they have, I can easily, quickly find out what percentage of employees subscribe to me so I can figure out is there room there a lot of room for me to get more in, in, in subscribers from their company. And then I also like to understand, What rewards can I give to refers who is actually talking about my newsletter and their employees are signing up? Who's taking action? Who are the influencers?

And in this case, influencers are gonna be the CEOs, the VPs, bosses who tell their employees like, you should sign up. I have had very early on in my newsletter I had CEOs who made signing up for my newsletter, part of their onboarding experience. So week to week, I saw exactly how many people were. Coming onto their company. And I knew that I wanted to reward them. And so I, I got something like Spark Loop, which is a referral program. Based on that information, I already knew referrals were happening. And I wanted to measure that.

Well, this is one of the ways that I did refer you to figure out, you know, before paying for Spark Loop, I wanted to figure out like, are people referring me? Are there large, are there companies that I never heard about that are subscribed to me that I just haven't seen? So this is how do it. Is we're gonna take our CSE file if cc file of your subscribers. If you don't know, go get it. You can just export email chi, convert it every everywhere. Should be able to get a list, a CSV list of all of your email subscribers in their emails. We're gonna split that list.

We're, we're gonna split it across the app. We're gonna count, we're gonna count which ones are unique . We're gonna use a unique function there, and we're gonna sort it. We're gonna. Who subscribes for our letter? I'm using a dummy list today. Or it's a, it's a real list of real journalist emails, but it can be any list. What we're gonna do is we're gonna file and we're gonna go find our sheet, but we're gonna click the upload function right here. We can click and select . I'm gonna drag from my. Best we download right here.

So again, this is a list four, 500 journalist with email addresses . It acts exactly the same. We'll get domain names and we'll get, we'll understand which companies they work for. What I wanna do is, I wanna insert a new sheet. What's really cool here, let me just take a moment here, is if we create this Google sheet to. In a certain way, we won't have to just insert and do this work every time. We can set this up and then every month come back and re-upload this list and replace the spreadsheet . Replace the sheet, current sheet , sorry.

And we can then get that new information without having to redo this. But I go into that in a different tutorial in, in the paid version. But this, we're just gonna get this information, so we just insert new sheet. We're gonna import data , we're gonna wait for it long to load. If you have.  good wifi, or if it's on your websites web on your top. So here you can see I have names and I have email address. We really just care about the email addresses . One thing I like to do when I'm setting up Google Sheets,  is I, you absolutely can do all the work in one sheet.

But that doesn't let us do this over and over again. So what I'm gonna do is I'm gonna create sheets or tabs here and I'm gonna name them a across what we're actually gonna do. So the first one, we're gonna split the domains. Then what we're gonna do is we are going to count. Along the way, we are gonna have some challenges on, and I'll, I'll share with you sort of some pitfalls so you don't fall into them and her ways to fix that. And then the last thing we're gonna do is sort , we're gonna sort by h how many there are.

So. First off, to get the list here, we only want the column of the email address. We're gonna use Array formula for that. So array formula and then what you can do if you know the name, but we don't, we don't really wanna type that out. So we can literally just click for here, click the C column, and then end parenthesis and And now that's all of our email addresses . So, Split is a really simple function at its base. Without options, it takes two arguments . You can go equal split , and all we need is the text, which is a two here, comma.

And then in quotation marks, we're just gonna put an at sign cuz that's what we're gonna literally split the this email address is into two things, two items, and it'll go across column B and C. And now all we do is command C.  and then we can do a shift command and hit the down button and it, it goes to all of the B column and then hit. And let that pace. And now we have all of the domains here. Okay.

And, and probably what's gonna happen most of the time is you'll have a lot of Gmail, you'll have a lot of Yahoo Outlook. You'll have those we'll look at, we'll see those later, right? And so if you saw, oh, here's the next web, right? Command c I can copy that and I can search for it, right? And we can see, okay, there's. Six of these, right? Cause they're now double and we can do that, but we really just wanna see a list of them. So what we're gonna do then is take now this column C and we're gonna count it.

You we know already there, there's number of ones like the same, right? They're, they're, they're not unique . What we need to do is we need to take a column here, we'll go email or domain. Actually, it's gonna be. And in a two, we're gonna go equals unique . We're gonna use the unique function here, the range and column C on the split . And what unique co formula does is it only gets. One of each. Okay. And then in the B column, all we're gonna do is count if function, count if, and this is a really fun one. We need to just get the range.

We need two arguments , the range and what we need to count. So the range is gonna be this C column here, and what we're gonna count is a three over here. And , and now we know everything's gonna be one, right? So we can copy this also when we, we can copy this and hit shift and paste it, or we can do all the way down here. What I'd like to do, actually, way enough, is I'd like to go to the very last one, command shift, and I like two down. and I like to all the rows. So now it's really easy to command c pay copy, and then shift command down and we get all of 'em and we can paste.

So now what's interesting, we're gonna, I'm gonna, I'm gonna, we're gonna get a challenge here right now. So let's look at this. We have let's look at the count over here. We have, or some, sorry. I wanna see some mice. Okay, we have a sum of 578, and over here we have a list of 568 actually. Okay, so there's some problem with this , and what the problem is, I'll tell you right away, is that it's going to count, count. Count a capital letter and a lowercase letter as the same thing. So what we need to do is actually just lower all.

One way we can fix this is to lowercase everything, and we do that in the array function . So we go back to the slit column, and remember we have this array formula , and inside of this we do lower parentheses lower. Parenthesis and, and then n parentheses here. What that does is everything. Now let's go back and see again. Now we have, let's see, 560, and our sum should be 5 68. Perfect. So that's how we solve that problem. Now we have the problem of, we have, oh, here's Gmail here, 80. Here's CBS Interactive, 10. Here's Mashable at eight. We want to sort you.

So we really wanna see who's the, who's the best or who's the top. So on sort tab, let's call this domain, and then we call 'em Count. We can do that as well. Tab, right? And now we go here, and then here we go. Equal source. What do we wanna sort . We actually wanna sort the count a. And B.

So we wanna actually get both of those columns and then we wanna sort the column, which column we want, wanna sort the second column and then another co. And now we have to say, do we want it to go like A to Z or Z to a US sending or descending one to a hundred or a hundred to one US sending is going one to a hundred. So we actually want to. Descending, which we use a zero here. So is, it's just a bullying is ascending, yes or no? We wanna say no. We want it descending. So we do zero. And now we'll have, we want to actually get this A two. Two.

Now we have sorted every domain.  that is our top subscriber. So now we know exactly who, if this was our subscriber list, we would know that in Gadget, TechCrunch, business Insider and CBS Interactive and Future Net and Mashable. Here we have exactly our top subscribers and the companies they work at. You, you just have to look beyond the Gmails, the there's probably Yahoo as well, an Outlook. But yeah, that's it. So now we know how many Subscribers we have. So what's interesting as well is that we can go further, right?

So because we put our information on these tabs beyond this data , we, we can. Move this data , we can replace this data each month with updated subscriber accounts. And so I go into that more in, in, in the paid version. Feel free to check out better sheets.co. I have some more free tutorials on Better sheets.co, but if you pay, we, we actually go, I do this again, we go deeper. I go into adding if is blank, to find out, you know, to make it more. We wanna be able to see maybe ones is we wanna see which email addresses are actually at each of these companies.

We wanna be able to see who's actually following us, but we also may wanna see the kind of like style of the email address so that we can guess other employee's email addresses . And we want to be able to save this data month to month to be able to see some differences month to month. So if we say, Hey, we're gonna reach out to, you know, the 20 top companies, and ask them, you know, how are they referring us? Can we talk? Can we do something for 'em to talk about it more? Can we create like little summaries for them to share with their employees?

Can we do something to engage them?  and then the next month we wanna see did it work, right? So we wanna save this count month to month and see the difference month to month. It also helps to see the difference if we all.</div>