This is the third of the posts in the series of my “programmatic SEO without programming” experiment – after the “[competitor1] vs [competitor2]” and “best tools for [use case]” post templates, I decided to tackle another (perhaps the most popular) BoFu pattern: “[tool (typically your direct competitor)] alternatives”] Here are the instructions for how to create one yourself – using the same database I discussed last week in the post about creating “best tools for use case”.
Let’s see how to build this template step-by-step!
No time for writing formulas? Upgrade and download this template here 😉
STEP 1: Start with the post outline structure
As usual, you first need to decide on the structure of your post. Use tools like SurferSEO for this – or you can try to compare all the relevant posts on the 1st page in SERPs for a few KWs following the same pattern.
We settled on the following pattern:
H2 What is [tool1] used for?
H3 [JTBD1]
H3 [JTBD2]
H3 [JTBD3]- optional
H2 [tool1] pricing
- [competitor-priceplan1]
- [competitor-priceplan2]
- [competitor-proceplan3]
H2 [tool1] reviews
H3 Pros of [tool1]
- [PROs-toolname-bulletlist]
[G2-review-positive-tool2-text-quotes]
H3 Cons of [tool1]
- [CONs-toolname-bulletlist]
[G2-review-negative-tool2-text-quotes]
H2 People also ask question
H2 3 reasons why you might need a [tool1] alternative
H3 [CON1tool1]
H3 [CON2tool1]
H3 [CON3tool1]
H2 Top [tool1] alternatives and competitors
→ list in a numbered list (great for ranking for featured snippets)
- [tool2]
- [tool3]
- [tool4]
- [tool5]
- [tool6]
- [tool7]
- [tool8]
H2 [tool2] vs [tool1]
H3 [PRO1tool2]
H3 [PRO2tool2]
H3 [PRO3tool2]
H3 [tool2] reviews
[G2-review-positive-tool2-text-quotes]
[G2-review-negative-tool2-text-quotes]
H3 [tool2] pricing
H2 [tool3] vs [tool1]
–> repeat the same pattern for however many competitors you want to compare.
H2 Conclusion
STEP 2: Create the spreadsheet TEMPLATE with columns for each heading + paragraph
The columns need to reflect all the elements on the outline (above).
Watch this video to understand it better. 📹
For each heading, you will need 2 columns:
1. Heading-formula (white)
The ‘formula’ columns include filler text with variables (e.g. <tool>, <alternative1>, <alternative> that can be used universally in different blogs for different tools you’ve listed in your spreadsheet (that you want to generate all the posts for).
E.g. <H2>What is [tool]?</H2>
Note all the heading formulas are formatted in HTML – the whole content output is in HTML, so it can be directly imported into your WP code editor in the right formal.
2. Heading-content (green)
This is where we’re swapping the variables with the right content using substitutions formulas, e.g. for the H2 above:
=SUBSTITUTE(AU2,”[tool]”,$C2)
Essentially, it substitutes the variables in cell AU2 (where we had the formula) for the content of cell $C2.
Output:
<H2>What is SuperTool?</H2>
With more than one variable, you’ll need to use nested substitution formulas, e.g. =SUBSTITUTE(SUBSTITUTE(BH2,”[Tool]”,$C2),”[usecase]”,$O2)
3. Paragraph-content (orange)
For each paragraph, you will need to fetch the paragraph content relevant to the tool you’re writing about from the right cell in the “toolsdatabase”. You do it using VLOOKUP formulas, e.g.: =VLOOKUP(C2,tooldatabase!A2:AI99,10,FALSE)
I’m explaining how to use it below.
STEP 3: Using VLOOKUP function to fetch content from database
What I did differently this time is that I created 3 databases in separate sheets and fetched information from each of them into the “database” columns and content columns rather than filling them in by hand for each KW pattern.
How to use the VLOOKUP formula for this?
Let me give you an example.
Let’s say I want to fill in the “What is [use case]?” column for all the use cases.
I will fetch the description of the use case for each use case from the “usecasedatabase” sheet using the following formula:
=VLOOKUP(E2,usecasedatabase!A2:Z10,2,FALSE)
Where:
“E2” is the column containing the variable [use case] in your TEMPLATE sheet
“usecasedatabase” is the sheet containing information about each use cases
“A2:Z10” is the range where the VLOOKUP formula can find
“2” is the exact column where the “What is [use case]” information is located
“FALSE” means we are looking for the exact match
The VLOOKUPs are also used for fetching information about the tools (pargapraph content) from the toolsdatabase, such as:
– Tool description
– dashboard images
– lists of tool’s benefits
– lists of tool’s disadvantages
– tool’s pricing (+ pricing screenshots)
– tool positive reviews
– tool negative reviews
– review screenshot
Again – if you don’t have enough time to create your own spreadsheet, you can download the template here.
STEP 4: Add images (blue) in relevant places & fetch their HTML code using VLOOKUP formulas.
I’ve added images for the tool and every alternative for: dashboard, pricing, positive review and negative review + the main use case for the main tool only.
E.g.: =VLOOKUP(C2,tooldatabase!A2:AZ101,9,FALSE)
This formula is fetching the content of the 9th column from the toolsdatabase from data range “A2:AZ101” for the exact tool that sits in cell C2 for this template.
STEP 5: Add final CONTENT
In the last cell of the spreadsheet, we concatenate content from all the content output cells (headings – in green, the content of the paragraphs – in orange, and the code for the images – in blue) using the following formula:
=if(len(AL2),)&if( len(AL2)>1,CHAR(10)&CHAR(10)&AL2,)&if( len(AT2)>1,CHAR(10)&CHAR(10)&AT2,)&if( len(AV2)>1,CHAR(10)&CHAR(10)&AV2,) etc.
…adding &if( len(X2)>1,CHAR(10)&CHAR(10)&X2,) for every cell that contains data.
If any cells are empty, they will be skipped.
Now, you can export this sheet as a CSV file and import it to your WordPress instance using the “Imports and Exports” plugin, or simply copy and paste the HTML code.
English