17.11. Calculating % Share of Total using SQL

Scenario: Multiple queries sending traffic to a single landing page

In this lesson, we have a scenario where multiple queries send traffic to a single landing page. Let's see if it's the case in our dataset and figure out how we can calculate the share of each keyword. To do this, we need to sort by landing page and order the results accordingly.

Adding a new column for sum of clicks

We want to calculate the sum of clicks for each cluster of landing pages. To do this, we can use the SUM function in SQL and partition our table by landing page:

SUM(clicks) OVER (PARTITION BY GSC_landing_page) AS sum_LP_clicks

This will create a new column with the sum of clicks for each landing page group.

Calculating query share of total clicks

Next, we want to calculate the share of total clicks for each query. We can do this by dividing the number of clicks by the sum_LP_clicks calculation:

clicks / sum_LP_clicks AS query_share_of_total

Now that we have calculated the query share of total clicks, let's check if there are any duplicate search queries or queries sending data to multiple landing pages.

Checking for duplicate search queries

To check for duplicate search queries, we can use SELECT DISTINCT:

SELECT DISTINCT search_query FROM shares;

If there are duplicate search queries, their count will be less than 266 (the number of rows in our dataset).

Aggregating results for unique search queries

Now that we have identified duplicate search queries, let's aggregate their results together so that they're combined into one row:

SELECT
    search_query,
    SUM(impressions) AS impressions,
    SUM(clicks) AS clicks,
    SUM(sessions * query_share_of_total) AS sessions,
    SUM(goal_completions * query_share_of_total) AS goal_completions,
    SUM(goal_value * query_share_of_total) AS goal_value
FROM shares
GROUP BY search_query;

This will sum the number of impressions, clicks, sessions, goal completions, and goal value for each unique search query.

Final thoughts

We have successfully calculated the share of each keyword in sending traffic to a landing page and aggregated results for unique search queries. This information can be used to calculate our final rank for a keyword based on its value per thousand impressions.