Keyword volume estimation for the keywords your website already ranks - Formula included

1. Step 1

Open Google Search Console's GSC performance section, select 28 days interval, and click on the Export button

1. Step 2

Create a new column on the Google, Excel, Numbers, or any other Spreadsheet program's sheet and name it as

"Keyword volume"

1. Step 3

Copy this formula into the "Keyword volume" cell.
=E2*1/ifs(EQ(QUOTIENT(D2,10),0), 1,
EQ(QUOTIENT(D2,10),1), 0.085,
EQ(QUOTIENT(D2,10),2), 0.037,
EQ(QUOTIENT(D2,10),3), 0.026,
EQ(QUOTIENT(D2,10),4), 0.022,
EQ(QUOTIENT(D2,10),5), 0.02,
EQ(QUOTIENT(D2,10),6), 0.018,
EQ(QUOTIENT(D2,10),7), 0.017,
EQ(QUOTIENT(D2,10),8), 0.016,
EQ(QUOTIENT(D2,10),9), 0.015)

1. Step 4

Based on keyword volume. You can prioritize which pages to optimize first.

If the keyword volume is big enough for the queries where our page is on the second page of Google and the competition on the first page is not super high, then you can prioritize that query.

Keywords with high keyword volume and average position > 30 should be easier to rank higher and get more impressions and clicks.

1. Step 5

Let's see how and if this method is accurate.

I compared the numbers with the values from the Google Ads Keyword Planner, and the keyword volume was within the Keyword planner's estimated range.

However, it gives more insights.

For example, when Keyword Planner gives an estimation of 10k-100k, the formula gives a concrete number. Something like 86.500

As a result, you can see is the number closer to 100K rather than 10k. Huge difference!