How to build programmatic SEO databases?
After my last (successful) attempt at building a Programmatic SEO spreadsheet for the “[Tool1] vs. [Tool2]” pattern, I decided to build a “Best Tools for [use case] in 2022” sheet. This one proved a lot more challenging, but also – my colleague Adina pushed me to uplevel this spreadsheet by building an external database for all the tools that we could reuse and simply “fetch” data from it with the VLOOKUP function. This again, seems to be working, so in this post I’m sharing with you what I learned from this – and how to build more scalable SHEETs with Databases using the Vlookup function.
STEP 1: Start with the post outline structure
Just like before, 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 – “Best tools for [use case]” in this case.
We settled on the following pattern:
Slug: best-[use case]-tool
H1: Best [use case] tool in 2022
H2: Intro
H2: TL;DR
H2: What is [use case]?
H2 The importance of [use case] tool
- JTBD1
- JTBD2
- JTBD3
- JTBD4
H2 Must have features of good [use case] tools
- feature1
- feature2
- feature3
- feature4
H2 Best [use case] tools
H3 [Tool1]- Best [use case] tool for [JTBD1]
H4 PROs of [Tool1]
- [PROs-toolname-bulletlist]
H4 CONs of [Tool1]
- [CONs-toolname-bulletlist]
H4 What users say about [Tool1]
[G2-review-positive-tool1-text-quotes]
[G2-review-negative-tool1-text-quotes]
H4 [Tool1] pricing
- [Tool1-priceplan1]
- [Tool1-priceplan2]
- [Tool1-proceplan3]
H3 [Tool2] Best [use case] tool/software/platform for [JTBD2]
H4 PROs of [Tool2]
- [PROs-toolname-bulletlist]
H4 CONs of [Tool2]
- [CONs-toolname-bulletlist]
H4 What users say about [Tool2]
[G2-review-positive-tool2-text-quotes]
[G2-review-negative-tool2-text-quotes]
H4 [Tool2] pricing
- [Tool2-priceplan1]
- [Tool2-priceplan2]
- [Tool2-proceplan3]
repeat for all tools
H2 Conclusion
STEP 2: Create the spreadsheet TEMPLATE with columns for each heading + paragraph
The columns need to include, again – as with the “Tool1 vs Tool2” template – reflecting all the elements on the outline:
Watch this video to understand it better. 📹
Keyword (combination)
PAGE id
Title Formula
Title Content
Use case
KW
What is [use case]?
Why do you need [use case] tools?
Must have features of [use case] tools
Best tools for [use case
]JTBD1
JTBD2
JTBD3
JTBD4
JTBD1-tool1
JTBD1-tool2
JTBD1-tool3
JTBD1-tool4
JTBD2-tool1
JTBD2-tool2
JTBD2-tool3
JTBD2-tool4
JTBD3-tool1
JTBD3-tool2
JTBD3-tool3
JTBD3-tool4
Metadescription Formula
Metadescription content
Slug
Excerpt
Category
Tags
Author
Featured image
Shortcode
TLDR – H2
TL;DR – formula
TL;DR – content
H1-formula
H1-content
Intro-formula
Intro-content
H2-1 What is [use case]? – formula
H2-1 What is [use case]? – content
H2-1-paragraph formula
H2-1-paragraph content
H2-2 Why you may need a [use case] tool?-formula
H2-2 Why you may need a [use case] tool?-content
H2-2 – The importance of [use case] tool – paragraph-formula
H2-2 The importance of [use case] tool – paragraph-content
H2-3 Must have features of good [use case] tools-formula
H2-3 Must have features of good [use case] tools-content
H2-3 paragraph: Must have features of good [use case] tools-formula
H2-3 paragraph: Must have features of good [use case] tools-content
H2-4: Best [use case] tools-formula
H2-4: Best [use case] tools-content
H2-4: Best [use case] tools -paragraph-content
H3-1-formula- [Yourtool]- Best [use case] platform for [JTBD1] (repeat this column sequence as many times as many tools you have)
H3-1-content- [Tool] – Best [use case] tool for [JTBD1]
H3-1-paragraph-formula Image for H3-1
H4-formula PROs of [Tool1]
H4-content PROs of [Tool1]
PROs of [Tool1]
H4-formula CONs of [Tool1]
H4-content CONs of [Tool1]
CONs of [Tool1]
H4-formula What users say about [Tool1]
H4-content What users say about [Tool1]
Image for positive review [Tool1]
[G2-review-positive-tool1-text-quotes]
H4-formula [Tool1] pricing H4-content
[Tool1] pricing
H3-2-formula- [Tool2]- Best [use case] platform for [JTBD-2] {repeat the column sequence from above for TOOL 2 etc.}
H2 Conclusion – formula
H2 Conclusion – content
H2 Conclusion – paragraph formula
H2 Conclusion – paragraph content
Content Output
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 tools database, 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 screenshots
Again – if you don’t have enough time to create your own spreadsheet, you can download the template here.
Enjoy and let me know if you have any questions in the comments!
English