Custom Excel Algorithms for Better SEO
- Adwords Competition Data
- Local Search Volume
- Traffic Data (~1 year of visitor data will do)
- User Engagement Metric (I use Average Page/Visit)
- Ranking Data (we use Mkubed to pull ranking data automatically)
Algorithm #1: Interest Index
The Interest Index is a simple formula to determine what are the best opportunities based on user engagement, weighted by the proportion of visitors the keyword is responsible for. For example, a keyword that has extremely high levels of user engagement, yet only had one visit YTD will still get a relatively low score compared to a keyword that that has more moderate levels of user engagement yet brings in significantly more traffic. Think of the Interest Index as a way to balance user behaviour with traffic volume.
Interest Index Excel Formula
“=User Behaviour Metric*(Traffic/Aggregate Traffic)”
Algorithm #2: TP Index
No, this next algorithm does not help you determine how much toilet paper you’ll need, it is another simple formula to determine which keywords possess the biggest traffic potential (TP). The TP Index looks at traffic potential versus your actual rank; for example, a keyword that has an approximate monthly search volume of 1 million queries will yield a minimal score if your rank is position 100,000.
TP Index Excel Formula
“=Rank*(Local Traffic Volume/Aggregate Local Traffic Volume)”
Algorithm #3: Home-Run Index
The Home Run Index is my attempt at marrying a users’ level of interest/intention with competition, traffic potential, and current position. I should preface this formula by noting that Adwords competition data is derived from paid search results, not organic; that being said, keywords with higher levels of paid competition tend to be more competitive from an organic side as well. For this formula to work correctly, a value of 0 must be assigned to all keywords that don’t rank. This formula also requires the IFERROR condition; otherwise keywords that don’t rank will bring back a “#DIV/0!” error message.
Home-Run Index Formula
“=IFERROR((User Behaviour Metric*(Traffic/Aggregate Traffic))*(Local Search Volume*(Competition/Rank)),0)”
I hope these formulas prove useful in helping you perform higher level SEO services more efficiently. If anyone has any of their own in-house algorithms that help with SEO, please feel free to share them. Thanks for reading!