00:01 Hello Better Sheet members! This video is exclusively for members alone tutorial walking you through how I just created an SEO meta description writer using AI and a couple of interesting things in Google Sheets.
00:14 So first off some of this you probably can do like copy and pasting into chat GPT or even using some GUI on top of chat GPT, right?
00:25 There's like neuron writer, there is there's some other writers, especially on AppSumo, there's available. I wanted to do this all in Google sheets because there's this new function in AI and chat GPT that you can browse with Bing, but in Google sheets, you can import XML.
00:43 And so I wanted to show you how to do that. And I also wanted to get this done, I had like about 50.
00:50 Articles that I needed to write a meta description for, and I wanted to see just like how good chat GPT could be, or how good AI could be, but I wanted to give it all of the information that I know in my head, right?
01:02 So that means, one, I wanted to give it all the keywords. So these are keywords that I'm already ranking for in some respect, but also that they appear in the article itself.
01:15 I didn't want to introduce new keywords in the description only. I wanted to show you all of this because I think it's pretty interesting and I think you can do this yourself.
01:32 Also, you're going to get this exact sheet that I'm using. You'll be able to get it and use it yourself and I'll show you how to use it.
01:40 But first, let me show you how I set this up. So first off, I have all the links here on a page called Links.
01:44 I have an API key here. I'll delete this API key once I get them finished with the tutorial. But it works now.
01:50 And you'll see it's not, it's like in the middle of working because AI right now is a little slow because I'm sending it a lot of information.
01:59 You'll see why. Alright. So first off, I listed all of the links. These are just blog posts that I've written.
02:06 And in the next call I have import XML and all I'm doing is grabbing the body of the post, meaning slash slash body.
02:16 So import XML can be pretty convoluted if you're looking for one specific thing. But in this case, I'm looking at A2, the link, and I'm looking for the xpath here is just slash slash body, the xpath query, that's all.
02:30 I did have to do this locale, which I've never seen before, and I think it might be a little new, but just I did en us.
02:39 I don't know why that's new to me, but import XML got the URL, and the xpath in this case is pretty simple, slash slash body.
02:47 A couple of times it did get, it was too large, even for import XML, because there is a character limit to Google Sheets there also is an input limit to check GPD or API, OpenAI's API.
03:01 I wrapped this, and then I also noticed that when I do import XML and I get the body, I notice that there's lots of other stuff with it, like there's multiple columns.
03:14 So what I did is I only wanted the first column, so I put index around the import XML, and I did 1,1.
03:21 This might, however be detrimental to you. You might want more text. I only wanted the first part of this text.
03:29 The other parts were like, like footer stuff. Like, literally, my footer. I wrote a prompt here, and actually, quote unquote, I wrote a prompt.
03:38 I actually got a prompt from Alec, SEO guy on Twitter, who I actually have been working with a little bit with SEO recently.
03:47 He's the one actually who told me these specific blog posts don't have meta descriptions. So he's been very helpful, and I wanted to use his own thoughts here, which is a tweet that he has, which he wrote a prompt, and he does this all in- chat GPT.
04:04 But again, I wanted to do this programmatically, automatically, inside a Google Sheet. So I grabbed the the original prompt that he says, write a compelling meta description, summarize its main points, the characters.
04:17 I've edited this a little bit, because- Because this next part, I think, is even cooler than just sending this text to chat GPT and saying, hey, write something.
04:26 What I did at the end is I said, at least you- Also, in addition to writing this meta description, use at least a couple of these keywords.
04:34 Again, these are keywords that I am already ranking for in SEO, and I want to optimize these posts for those specific keywords.
04:44 I want to rank higher. And so what I did is, this is going to look really convoluted, but I'll explain it.
04:50 In the row one, I have a thousand keywords, so clearly, better sheets is one of my top keywords, but I also have account if, extract URL, add checkbox, learn checkbox, google forms, all this stuff.
05:07 What I did here is I did regex match and I said go look in this text field B2, all of the import XML, all the text that I'm getting from the website, go look there and is there a match for this word or phrase.
05:23 How I did that is using regex, regex match and quite literally with regex you just have to look for the text.
05:30 If you're just looking for text alone and this one string of text, it's not a complicated regular expression you have to use.
05:38 You just need to use the text and it says, does this text exist in this cell? Yes or no? True or false?
05:47 If it's false, no. If it's true, you'll see some trues here. True and somewhere in here it's true. But I didn't want to sift through all of it.
05:55 These thousand key words, right? And so what I did is I just filtered all of this row here that's looking for the Rejects matches.
06:06 Just tell me which ones are true. Which ones are true? Grab that from row one. So now I've grabbed all of them from row one, so in this case it's sell sheets, sell Google Sheets Google Sheet, Google Sheets and then I said, you know, there was a lot that just didn't find any, right?
06:24 These are blog posts that probably don't have any key terms, yet, that I wrote. And so all I did, I said, you know, if there's nothing, just put Google Sheets.
06:32 That's all. We want Google Sheets. And so now for each row on links, I have created, I have gotten all of the keywords that exist.
06:43 In there, using Regex match. So, so far all the formulas we've used, we've used import XML, we've used index, we've used Regex match and also what we're gonna do here is we're gonna count the characters of the actual meta description that's written, and so we're gonna use L-E-N, and I wrote here a little
07:02 if is blank and if it, if the meta description is blank, don't do anything, but if it's not blank, count the letters.
07:10 L-E-N is just counting the characters, or the letters are length. That's actually, L-E-N means length. But it's counting the characters, so here we have 230, and some of these are working.
07:21 Some of these are working very well. Learn how to export your Google Sheets to PDF. This is great. That's a great, and 166 characters, so I can probably edit about 10 length.
07:32 11 characters out of that. 153. I got 334, so I gotta do a little bit of editing here, but this is great.
07:38 It's getting me really compelling meta descriptions, game confidence, Google Sheets with free tutorials. That's probably good. I'm gonna go through and, double check, right?
07:49 How can I count true or false? And we can see here with the SEO detailed extension, there is no description, and here I already have now, I have written a description for me.
08:03 Learn how to count true or false. Count if formula, great. This looks good from reading this right now, looks great.
08:10 And this is a lot of work that has been sort of programmatically done for me. I can use import XML, grab the text, I can use Regex match to see are there any, words that I want to optimize for, and then bam, write that meta description through AI.
08:25 So AI, the AI part, if you're watching this on better sheets, obviously you are, because I'm not putting this out on anywhere else you'll be able to go over to Adventures in AI.
08:34 The course there has this script already written, and specifically in the video upgrade to GPT-4. I have not upgraded. This is November 7th.
08:43 They just announced GPT-4 Turbo. I have not upgraded that yet. I will be adding to that course soon something, but I'm still using this same AI prompt here.
08:55 Oh, actually I need to do this. Let's do increase the max tokens here to 4,000. I just realized that. But this text, or this app script A is in this sheet so you can get that sheet down below.
09:09 You're just gonna need to put in API key, your API key for OpenAI. Okay, it's gonna rerun all of these.
09:14 We have errors. Yeah, there's a maximum length. We're gonna rerun these again. It's okay. I'm probably gonna delete these. If you get this error again maybe this needs to be 3000 instead.
09:30 Let's see. Copy and paste this. I've sort of combined everything together into one AI prompt which is asking it to write a compelling prompt.
09:40 It's taking the prompt. Adding the keywords and then saying this is the blog post and adding this text from B2 here.
09:51 You can actually see it. See. But yeah, it will write them. Again, I hope, and we'll get some new meta descriptions.
10:01 So yeah, let me know if you have any questions. Hopefully, this is helpful to you. I thought this was super helpful to me writing now, 50 metagectricians with keywords optimized.
10:10 This is pure SEO, right? Optimizing for actual search. All right, thanks. Bye. Bye.