So, I’m working on this project where I’m optimizing descriptions for a client using Google Sheets. I need these descriptions to follow a very specific format, and to be sure that they do… I’m using Google Sheets. I’m also using it because I’m going to be populating them through an import with a CSV file. It just makes sense to do it all in one place.
Here are my format restrictions:
- Description #1 must be over 200 words (with other content being about 120 words, so 80)
- Description #1 must contain a specific keyword 1%-3% times
- and the meta description must be less than 160 characters
Pretty simple stuff, just the start of a description and a meta description to accompany it. Here is the information I have been provided:
- Custom URL fragment
- Unedited Description
Find the Keyword
So, first to figure out my keyword. To do this I’m going to take my URL fragment:
/tools/power-tools-and-accessories/drills-and-drivers/impact-drivers/
I know my keyword is going to be “Impact Driver”, but I need Google sheets to know that. So I’m going to use the following code to isolate this phrase. My URL fragment is in position A2, because A1 is the title I will need for my CSV import.
I’ll figure out a cleaner way to do this in the future, but for now I need 2 cells. In B2 I’ll add this formula to convert the dashes to spaces, and remove the final slash on the right…
=LEFT(SUBSTITUTE(A2,"-"," "),LEN(A2)-1)
It’s super important that we remove the final slash with LEFT, otherwise the formula in cell C2 would return a value of nothing. Because it is returning whatever information is provided after the final slash.
=trim(RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))),1)))
Size Up the Description
Now we should have a cell (C2) containing our keyword, impact drivers, and it’s time to check our unedited description to see if it’s formatting complies with the restrictions I mentioned earlier. So, to test this, I’m going to past the current description in position D2.
“DEWALT offers a full line of cordless impact drivers and wrenches for the professional user. Ranging in voltage from 9.6 volts to 20 volts, including the 12V MAX* and 20V MAX* impacts, DEWALT has the right cordless impact driver or impact wrench for completing a wide range of fastening applications. When looking for an impact driver or wrench with innovation, performance and durability to withstand the rigors of the jobsite, DEWALT cordless has you covered.”
This will ultimately become our description (D2) & meta description(H2) for this page in our website, but first we need to see how they stack up. In position E2, we’re going to see how many words are in our description, and to do this we need a pretty simple tag that will essentially just count up the spaces.
=COUNTA(SPLIT(D2, " "))
The next formula for position F2 is going to tell us how many times our specific keyword is used. I’m using SUBSTITUTE because I have 2 words combined as a phrase to use as my keyword, and I’m using LEFT to remove the plural, “s” so that I can get the singular version of the word(s) as well.
=COUNTA(SPLIT(SUBSTITUTE(D2,left(C2,len(C2)-1)," &&&&& "),"&&&&"))-1
Figure Out the Percentage
Then, G2 is where we will simply calculate the percentage. This column will need to be formatted as a percentage by either clicking the “%” button, or going through the Google menu to Format > Number > Percent.
=F2/E2
Meta Description Optimization
We know know all the information we need to modify this paragraph and make our description. So, the meta description should be pretty easy from here. Once you’ve got the description how you like it, copy and paste it (D2) into the meta description cell (H2). Then, in cell I2 place this formula:
=len(H2)
…which will tell you how long it is. Now all I have to do is chop up the copy until it’s just under 160 characters.
I know it’s a pretty roundabout way of explaining this, but this is how I’m going about it for this project, and like I said… I’ll likely figure out a better way of doing this in the future, and I’ll make sure to redirect people to that page when I do. Thanks for reading along, feel free to comment below with any hints or criticisms.