17.12. Calculating Keyword Ranking in BigQuery SQL

In this advanced Looker Studio lesson, you'll learn how to calculate the value each keyword brings to your website per thousand impressions, using BigQuery SQL.

Creating the Calculation

First, let's create a new calculation by dividing the value by the number of impressions. To get the value per thousand impressions, multiply the entire calculation by 1000:

(value / impressions) * 1000 AS value

This calculation gives you the value each query brings for a thousand impressions from sales.

Sorting by Value

To make your analysis more interesting and relevant, sort your data by value in descending order. This way, the highest values will appear at the top of your results.

For example:

ORDER BY value DESC

You might notice some outliers with high values per thousand impressions. These could be due to implementation issues, session timeouts, or even bots.

Saving and Analyzing Your Query

Once you're satisfied with your query and calculations, save query for future reference. Keep in mind that this example is designed to demonstrate an end-to-end data pipeline rather than provide specific optimization advice for SEO or website improvements.

Remember that results may be temporary and subject to change as you refine your calculations or update your data sources over time.

Enroll in Looker Studio Masterclass →

📩 Receive my weekly Looker Studio tips

🖇 Connect with me on LinkedIn