Create a Branded Lead Magnet from Qualitative Data

<div>00:00 We found another Google Sheet in the wild. So Josh Nusbaum, who I have met and is a wonderful open-source developer. 00:10 He's the maintainer of Svelte Stripe. That's a Svelte. He also tweets about Svelte and open- open-source software. He has created a qualitative not quantitative, but qualitative dataset of H1 subheads and call to actions from many websites. 00:29 And when I saw this, I was like it's one, a Google Sheet. Two, it's free. When did you learn the secrets of Google Sheets? | Sheet Talking Episode 8 Esa Advanced Coding in Google Sheets for Programmers How To Sell Google Sheets For Fun And Profit 5 Google Sheet Templates You’ll Get as a Better Sheets Member

But three, it's missing a lot of cool stuff that I think we can add to it. 00:37 So if you are watching this video on better sheets, I'm not gonna give you the link to the sheet. I will give you a link to this tweet so you can get it yourself. 00:45 If you're watching this video on YouTube. Then down below is a link to this tweet and also we'll check out Josh's Podia site. 00:54 I'll also put a link down there for you to see that. But yeah, go grab this sheet if you want to see what he's done, and maybe I will give him what we do here. 01:05 And so, hi Josh. Better Sheets Videos Checklist Are you an A1 or B2 Kind of Sheet Guy? Download Google Sheets

And we'll then, hopefully we'll see it in action if we do see it eventually. But I want to make this sheet better. 01:12 Here is the sheet itself. Let's take a file, make a copy, and close the first one. And what we're gonna do here is, first off, I want to make this as Joshy as possible. 01:30 We're gonna get into a little bit of analysis . Sheet Improvement: Electric Vehicle Stock Tracker by evuniverse.io 80 Years In 1 Spreadsheet 2 Forms 1 Sheet

I want to do some analysis of the H1 subheads, primary call to actions. 01:34 We want to find like, the m- most common ones, how long they are, how many characters they are, all that kind of stuff. 01:40 We'll do a few- a bit of that, but first, the very first thing I want to do is I want this to scream, hey, I'm Josh Neusbaum, this is what I do. 01:49 So what I typically do, and and which is very weird, is I will add a row under the frozen one. 01:58 Why is that? Sheet Makeover - Google Ad Writer BEFORE YOUR SHEET GOES TO AI

It's because normally if you look at sort of freebies in sheets, they will add above the freeze line this big banner and they'll say, hey, I'm Josh and- here's my podiaceae here, you can buy stuff from me. 02:10 But I'm gonna put it right here because when we're using this, I don't wanna have to delete that right away. 02:14 I wanna be able to scroll down and see some cool stuff and it scrolls away, but in some uhm I wanna be able to see it. 02:21 So let's see what we are working with. Let's look at Josh's site. His podiaceae site's here, j**********************. Great Sheets! Seedtable Created an amazing Community Board

He's building SASS kits with Svelte. 02:32 He's building dashboards , database apps, uhm and I think he also, and again he maintains Svelte Stripe integration. So. Loves purple so far, that's what I see here, just purple, uhm but two shades of purple. 02:46 I want to get this shade of purple and this shade of purple and we use HTML, uhm actually get color from image . 02:56 I'll just google that, it's always going to be I think the first one. 150 VCs Actively Investing by Lemon.io

We're just going to upload the image and get the colors. 03:02 Okay, we got that color, we're going to add it in custom right here that's a nice purple and then we also have this hex code for the button we want to use or any buttons or any call to actions, we'll use that. 03:18 I'm actually, this is going to be strange, I'm going to add three rows and you'll see why soon, but we're going to create a little button here that looks like this. 03:29 And we're going to say copy. 150 VCs Actively Investing by Lemon.io Button Styles Count Color by Row

So that if somebody accesses this sheet, we want them to be able to copy this URL. 03:36 Uhm, I'm going to call this one better, but what I would suggest to Josh is use the URL of your own, uhm, website. 03:44 Google Sheet, right? And what we're going to do is create equals hyperlink , and we're going to use the URL, but we're going to add slash copy at the end. 03:57 And actually, we're going to use template . Actually I'm not going to use copy for this one. I'm going to use template preview for this one. And I'm going to say copy to copy sheet . Creating a Custom URL Link for Your Spreadsheet Roasting Google Sheets Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet How to Copy an Entire Spreadsheet

I want to add a little emoji here. 04:11 Maybe uhm, don't know if there's a copy maybe this arrow does well. We're going to have to center it. It's going to be super boring for a moment. 04:23 Add some white text. We're going to default to quicksand for this one. We'll do 15 font. Copy sheet . Why did I put two more rows here? 04:32 I'm going to select both those rows. I'm going to resize them to about 20. And then I'm going to do something magical. 04:39 I'm going to move this actually over to let's go over here. Copy sheet . Paste that. Better Google Ad Writing Tool Sheet Makeover Sheet Makeover - Google Ad Writer YOU CAN DO BETTER

And I'm going to one more column there. 04:53 G and I. I'm going to resize as well to 20. Same as over here. And I'm going to make this a really cool button. 05:02 I'm going to merge these four cells . And then I'm going to add another shadow. A darker purple. I'm going to select . 05:08 Actually I'm going to select this purple but make it a shade darker. Customize it. Shade darker. There we go. And now if we view. 05:20 Show grid lines. We have this big ol' button there. Nice. Don't have to have it so big. But hey uhm. 05:29 Created by Josh. Check out my podia. Pabbly Connect Countdown - "Sheet Improvement!" 150 VCs Actively Investing by Lemon.io Button Styles YOU CAN DO BETTER

And I would add a link up here. But here's the thing too. This is probably not enough. 05:47 And we might want to add a little image here of Josh. We're gonna use his uhm. We're gonna just say copy link address I think. 05:57 I think it's gonna be just that. But we need copy image address. That's what we need, that profile image . We're gonna do equals image . 06:08 URL. And then now there's been a change. We have to actually allow access . So we allow access in there. There's Josh. 06:17 Hi. Link In Image in Google Sheets

And if this was his actual photo we would, I don't know, not nicer, but it would just be different. 06:24 Where 's the hand waving? There it is. Hi, I'm, I'm Josh. I run, so we can just use his I'm the maintainer of Svelte Stripe. 06:43 There we go. Let's make this really nice though. Let's use quicksand. Let's use white for now. Bold. Make it a little bit bigger. 06:57 I'm gonna actually move that primary CTA. Check out my putty here and we're going to merge these cells . So hi, I'm Josh Maintainer of Svelte Stripe. 07:09 Check out the data we have. 150 VCs Actively Investing by Lemon.io

I provide it rounded up from top sites. There you go. And Josh, you can write anything you want here. 07:23 I think a nice little blurb here would be really nice saying hey, you know, this is why I made this. 07:29 I would also put data and I would take this all, just copy it, let's add a new one here, new page, and what I would do is I would add more information on this page, let's just add all this stuff. 07:46 This can be just a few rows, just a few columns, it can be pretty big if we want to.

Uhm, we can also view without the gridlines as well. 07:58 Uhm, and here I think Josh, you can put a lot of information and you say about. Say, you know, I would put instructions here if you're like, hey, this is what, how you can use this yourself. 08:11 Here's the data I found. I think Josh, you mentioned in another tweet, you found some actual. Tips to Navigating Thousands of Lines of Code In Apps Script

Lessons learned from this, beyond what we're going to do here, but I think having an about page is perfect. 08:23 And here's something that is super foreign to a lot of people who are creating sheets and giving them away for free, is uhm, just expect it to be shared. 08:31 Like, we want the sheets that we make to be so useful that they, people want to share them. Sheet Makeover - Google Ad Writer How To Sell Google Sheets For Fun And Profit 17 Minutes of Google Sheet Business Ideas

So don't think about like, there is protections you can do. 08:39 You can always get only sheets, which allows only the people who buy a sheet, access to the sheet, which I made, only sheets. 08:48 Uhm, there are protections, but when we're giving away a sheet for free, just expect that it will be, uhm, shared. 08:55 So, the person getting it is not going to know who made it, and so adding this kind of information is super useful to make sure that when it is shared, because it is so- goddamn useful, right? 09:05 So amazingly useful, that uhm, people are sharing it, and sharing it, friends of friends of friends, that now you can still get that information back, right? 09:13 They're- they still know it's you, you put your email address, or not- but how to contact you, how to- how to sign up for your Podia, how to- what you're building, I would add way more about what you're building, how you're building it, your open source software, why it's useful, all that stuff on the 09:28 about page, uhm, and a way- this is- this is something that is super unique to people who are- making Google Sheets for the first time too, some way to keep getting information from you. 09:41 What I do is I use- I set up a Gumroad page, so something like Big Year which I set up for free, uhm, but it's Gumroad , Big Year , there it is. 09:52 It's on my own Gumroad , and you have to put in your email address and a price here, but you can do $0, it's a freebie. 09:59 But what I do is I will keep people informed, so when they buy the 2024 one, they'll probably get the 2025 one for free or, uhm, or notified of it. 10:08 I'll probably create a brand new one in November or December of 2024 for 2025, and so I want to be able to get the people who got it before, get it again. 10:20 So setting up a Gumroad page is super useful for this. Protect Your Spreadsheet IP YouTuber Talks About Sheets | Sheet Talking Episode 3 Prolific Oaktree OnlySheets Password Protect Google Sheets

You do have to give I think 10% of any money you earn. 10:36 Something in order to capture that email address.

Josh, you're a programmer as well, you probably know that you can also just implement this kind of some JavaScript action where you can take in a form field , take in an email address and then email them the sheet link possibly, right? 10:56 If you do want to give it away for free and you don't want to take email addresses , uhm, it's okay. 11:01 I still do con- I would still, no matter what, consider adding all of your information about what you do, how you do it, uhm, into the sheet itself. 11:10 There's one more place where it's really unique . Are you an A1 or B2 Kind of Sheet Guy? Email When Cell Changes

If you're, uhm, have some scripts or something, put in extensions, Apps Script , especially if you're a programmer and you're trying to sell to other programmers, add in here, yes, in Apps Script , a note , and how you can do that is just go up to the top 11:25 , it is, uhm, slash, actually I think wrong slash, there, and two, uhm, what are these, uhm, stars, oh my god this creates a multiline, uhm, comment that you can add, hi, I'm Josh, I made this. 11:44 Sheet, again, I'm Andrew, I'm from BetterSheets, I didn't make this sheet, Josh made, if you're catching this late, uhm, if you're ten minutes into this video and you're like, who is this Josh guy, I'm not Josh, Josh made the sheet, I'm Andrew, I run BetterSheets.co, uhm, where I give tips, tools, I, 12:00 I, I give tips and tricks to make your Google Sheets better, I also build tools and templates myself, uhm, and so yeah, I think this has been really a long winded way to say like add your information inside your sheet, uhm, put it on about page, put it in the data , put it in an untitled. 12:16 A project in the uhm, app script , yes. Sheet Makeover - Google Ad Writer Advanced Coding in Google Sheets for Programmers How To Sell Google Sheets For Fun And Profit Quick Follow Up Email

If you're selling to developers, this is a great way to communicate with them. 12:22 If you're like hey you, you discovered this little thing, hi. This is what I'm doing. Alright, let's get to some analysis . 12:29 Cause I think we can do some really cool stuff here. Your and we can even add it, we can actually I think add a row above here and add this data here. 12:38 So, what do we need? Let's add some more data here. I'm gonna add it far to the right. I'm gonna do cta length. 12:48 . Second cta length and all I'm doing here is equals len and putting in the length here. 150 VCs Actively Investing by Lemon.io Upgrade Your Outreach LIVE COWORK SESSION

But, we're gonna get zero for when there's none. 12:59 Doesn't mean that it's actually zero. Characters means that there's none. So, I'm gonna wrap this with if. . Is blank. 13:05 Umm. E6. And now, if it is not blank it'll have a number and if it is blank it won't have a number . 13:17 It won't be zero. I mean it's still technically zero. Thank watching. But what the cool thing here is is that it's gonna just be blank instead of as an actual number zero. 13:28 Which is important. Okay. Let's do that as well for the secondary. Wallace Asks: How To Find New Names Remove Non Numbers

So now we have length of call to action. 13:35 We have some pretty big ones. We have some pretty small ones. Three letters by. We have ten letters, ten, sixteen. 13:44 What's the average of this? Let's figure out how do we get the average of this length. So if we just do average. 13:51 You This. That is going to be cool. Fourteen. Not bad. Actually that's a lot higher than I expected. But I do want to round this down to two places. 14:06 . Yeah, two places is fine. Maybe one place. This place is better. Fourteen point six. Probably better there. Sheet Makeover - Google Ad Writer

And now I'm going to do it for the exact same thing. 14:15 The secondary. So the secondary call to action is a little bit longer than the first one. Cool. But hey, there's also this sub-head. 14:23 Subs www.zeoranger.co And h1. So let's get those as well. And let's just get the call to action. The umm the length. 14:31 So h1 length, sub-head length. And again we can just copy paste this here. Here. And now we have the average. 14:48 So 35 characters for the h1 length, 111 for the sub-head length. Cool. That's pretty good data , right? Sheet Makeover - Google Ad Writer Get YouTube Subscriber Count

I mean, I have no analysis over this. 14:58 I don't understand what this might mean. But it is a little bit bigger than I thought this call to action would be shorter. 15:05 But 14, there's a lot of 13 lengths, so this one is surface pronine. What's just around the 16. Start free trial. 15:15 That's 16. 20. Sign up. It's free. Every time there's free. There's a lot of freeze here. And there's a lot of get started. 15:22 So that was, I think, something that Josh noticed is, oops. Cheers. That get started is sort of the most common here. 15:31 But let's look at unique data .

I like the unique function and I want to create a equals unique . Why I'm doing this is. 15:43 Thank for watching. E6 actually. I just want E6. So these are all the unique ones and now we're going to use count if we're going to use the range . 15:58 This range criteria and it's going to be this unique . Mm hmm. Only there's a blank one but it's okay. So now here's the most the number of each one. 16:16 . . But what's interesting about this is that some include others. 3 More Ways to Count in Google Sheets Figure Out Frequency of Numbers Highlight Unique Values

Like start free trial includes free trial get started free, has the word started, get started, get started. 16:27 It's free. It's unique . Something we can do to fix this. You sort of add another line here of like does it include the word free? 16:37 Does it include get started? We could do deeper analysis if we take our time to sort of add more context to each one of these. 16:46 So we can do something like this. Free and equals fine search for free. Text to search is going to be this primary one. 17:04 . It's not going to find it, it's going to have a value, could not find it.

So if, actually let's see if what happens if there is free, this one, it's going to give us a number . 17:14 Okay so if error , if value error put a zero and if this is greater than zero, value if it's true, let's put a one or yes. 17:34 Thanks for watching! And if it's false, nothing. And so we have a bunch of yeses here. But now we have a way to count how many are free. 17:47 So if it doesn't include the word free. So count. . . Actually yes, count if n6 to n, criterion yes. 18:05 And now we have 12. 12 of these. And so how many are Thanks watching! How to Count Numbers in a Row

So what we're gonna do is we're gonna get a count over here. 18:11 Count all a6 to a. So we have 45. And 12 of them have free. So let's actually change this number . 18:22 I don't think 12 is a really good number . Thanks for I think if we divide it by count all a6 to a. 18:36 And change this to a percentage. I think that's better. 26% record of them Thanks watching! Have the word free in their primary call to action. 18:43 Pretty cool, right? Now we have some interesting information. Let's do that as well. For sign. For like sign up. Start. 18:53 Let's see if. Sheet Makeover - Google Ad Writer Count First Digit of Cell

All we need to do is take the same one and copy it over here and instead of free do sign. 19:11 Let's center this. What I do realize is actually we need a lower e6. Ah that's because the capital numbers gave us. 19:23 So it's actually 31% have the word free. And now we did sign. And then let's do this with start as well. 19:29 We added the lower around e6. So now any start will get us that number . And what we're gonna do Thank you. All we have to do here is hold a with this dollar sign and now we can copy paste this. 19:51 So now 8% almost 9% have the word sign. Free is umm 31%. Count First Digit of Cell

Thanks watching! But start is definitely 40% definitely the most. 20:03 Definitely. Or get. Let's do this. And now actually I just realized another thing we can do if we want to look for more singular words. 20:11 Instead of using free here we can just use this and to. Thank you. But what we'll need to do is hold the 2. 20:23 Okay. And hold e6. Or not the 6 but the e there. Okay. Now we can. Mm. Take this. Copy it. 20:34 And paste it. And we'll still get the same numbers. Perfect. So it's the same. But now. We can take a whole column , move it over. 20:42 And use. Any word here. 2 New Ways To Add Emoji Reactions Run Every Prompt in Every Cell All At Once How To Get Last Word in Cell

Get. And now we get. The information so quickly, right? Take this whole column . Copy it over. 20:53 Instead of get. Let's use. Buy. Four percent used by. Wow. Start. Start free trial. Thank you. That free would be a lot more, actually. 21:06 Funny enough. Thirty one percent is a lot. Hmm. Now I'm looking at start- forty percent is more. Start free, start for trial. 21:14 Get started. Not much buys. Eyes. Yeah. Try. Maybe try as well. Now we can copy this. Add try. Four point four percent. 21:30 Sweet. That's some cool information, right? Run 100 ChatGPTs at One Time

That we can choose anywhere there and get analysis of it. You Let's see also. 21:37 I want to see in this subhead how many times is there a period? So what we're going to do is we'll put a dot here. 21:48 Actually let's copy this whole thing. Put a dot here and instead We'll see you next of looking at T e6. 21:57 I think we want to look at the subhead d6. Does it have at least one period? 77 points. Wow. Most of them have a period. 22:10 Hmm. Now let's see how many periods, right?

Okay so we wanted to count the number of periods in this subhead. 22:22 But what ended up happening is I actually used my google sheets formula generator here to count the number of periods in a cell and it told me count all split and it worked sort of but it has a huge problem where if there is none it'll always have one. 22:38 So we have this one here. There is nothing, there is one here here there is none and it is number one. 22:49 So even if there's one or zero it'll have one. How to Iterate Counting Blank Cells in Google Sheets Figure Out Frequency of Numbers Google Sheets Count Cells with Text

So what we need to do is if find we're gonna search for a period, text to search for is going to be the sub-head. 23:00 Umm. And if. So this will either be an error , right? Let's see. Thank you for watching! Actually, what happens? We just do find. 23:18 Find it in number 9. And yeah, we're gonna get some value of error , we're just gonna get 46. Is that actually true? 23:26 Let's double check. In the sub head. Oh, it did. Very long, yeah. Mmm, that is true. Find a Value in a Range SUM IF Fuzzy Search

And that is true that there is no value there. 23:36 So if it's an error , so if error We're not going to We're going to put zero. Umm, actually we're going to put No. 23:52 If it is, if we do find it. So. Shh shh negative one. Actually we'll do negative one. Because if. This is greater than zero. 24:08 Value if it's true is going to be the count all. Same one we had here. Count all split . Let's it. Need to take away that. 24:17 That. Don't need A1 we need D6. And if it's false. We'll put nothing. Alright so now. Now. Count Domains In a List of Emails - Part 2 How can I count true and false? IF Negative Then Zero

Where there is no period at all we have actually a blank or zero. 24:38 So we have a count. Period count. And we can take an average of that. That's it. Thank you. U6 to U. 24:52 Let's round this as well. To one place. 1.7 period. So on average there is more than one thing. So there's . 25:04 . . Either one or more. Periods. Nice. So that's some cool information that we can add context to this data of here's a bunch of sites. 25:14 And if we say, okay these are the top 50 sites on planet earth, right?

We'll see And we keep adding maybe we do the next 100 or the next 200 or the next 500. 25:25 We can lock in this information and then create a new data set of the top 500. And now we can sort of look at like okay here's top 50 versus top 500 numbers. 25:36 And I think there's some interesting numbers here of you know what categories can you look at this information? Can you look at indie hacker sites? 25:45 So go to indie hackers scrape sort of the top 50 sites their H1 subhead primary CTA secondary CTA. 150 VCs Actively Investing by Lemon.io Scrape H1 in Google Sheets

And compare it to these top 50 sites in the world, right? 25:56 What are the differences? Are they shorter primary call to actions? Are they longer? Do they even have any secondary call to actions? 26:04 Do they have what kind of subhead do they have? What kind of H1 is it? Is it a vid? Or is long H1? 26:09 Is it a much longer H1 than 35 character average here? Or is it shorter? Are people saying more here than they are in in the hacker world, right? 26:20 I think that would be very interesting to see. So yeah, thanks You Josh for putting this information together. 150 VCs Actively Investing by Lemon.io Scrape H1 in Google Sheets

I hope this has been helpful. 26:27 I might add some more like instead of allowing sort of the grid lines here. I would probably one last thing I would do is add the shade of purple as an alternating rose and maybe a very very light purple. 26:46 Umm. Let's go make a custom one. Just a shade lighter. There. Take all this and now we're gonna copy paste the format only . 26:59 That might look a lot nicer. Same here. Paste format only. Maybe flip this around to some extent. Ah it can't move it on. 27:11 But it's okay. Thank much. Turn Bland Sheets Into Glam Sheets! 80 Years In 1 Spreadsheet How to Add Alternating Row Colors

Umm we might not want to freeze that at all but yeah there's 45 products. We got their lengths. 27:18 We got the percentage when they have some call to action. Like we found out that 40% of the words start. 27:24 That's pretty cool. We found out that there's at least one or more. Very on average to more like two periods in their sub head or sorry h1 or sub head. 27:39 I think sub head. We added this copy sheet link. We have made it sort of nice. We added</div> Sheet Makeover - Google Ad Writer