We are attempting to recreate a metric in Snowflake that is out-of-the box if folks use BigQuery with Google Search Console API. However, we are using Airbyte and we do not have sum_top_position in our fields from GSC. How can we calculate this metric (sum_top_position)?
We aren't sure what to try. I'm not sure how this metric is achieved and we need help with a formula based on position (a value we have).
On
From documentation here: https://support.google.com/webmasters/answer/12917991?hl=en
sum_top_position: The sum of the topmost position of the site in the search results for each impression in that table row, where zero is the top position in the results. To calculate average position (which is 1-based), calculate SUM(sum_top_position)/SUM(impressions) + 1.
Therefore to answer your direct question: (AVG(position) - 1) * SUM(impressions) = SUM(sum_top_position)
To have an approximation of the sum_top_position, you need to do result[position] * result[impressions] when querying data from GSC API.
=> you will then get an approximate result (not 1:1 as far as my tests went) but I don't know if Google apply thresholds with positions for this metric (aka only position above a certain position - maybe 100 ? Thus explaining differences I have)
Tell me if it helps or if you had a better results using something else