How Can I Calculate Impression Share and Click Share Metrics Over a Custom Date Range?

Question

How can I calculate impression share and click share metrics over a custom date range? Specifically, I want to get data for the following Google Ads metrics:

• Search impression share
• Search lost IS (Rank)
• Search lost IS (Budget)
• Search absolute top impression share
• Click share

Environment

Connector: Google Ads

Answer

You can use the following SQL queries to calculate impression share and click share metrics for a custom date range. Replace YYYY-MM-DD with the applicable date range.

• Search Impression Share:

  SELECT SUM(impressions) / SUM(eligible_impressions) * 100 AS search_impr_share  
  FROM google_ads_data  
  WHERE date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD';  
  

• Search Lost IS (Rank):

  SELECT SUM(lost_impressions_rank) / SUM(eligible_impressions) * 100 AS search_lost_is_rank  
  FROM google_ads_data  
  WHERE date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD';  
  

• Search Lost IS (Budget):

  SELECT SUM(lost_impressions_budget) / SUM(eligible_impressions) * 100 AS search_lost_is_budget  
  FROM google_ads_data  
  WHERE date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD';  
  

• Click Share:

  SELECT SUM(clicks) / SUM(eligible_clicks) * 100 AS click_share  
  FROM google_ads_data  
  WHERE date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD';  
  

• Search Absolute Top IS:

  SELECT SUM(top_impressions) / SUM(eligible_top_impressions) * 100 AS search_abs_top_is  
  FROM google_ads_data  
  WHERE date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD';  
  

NOTE: To ensure your queries return valid data, you must add all referenced fields, such as impressions and eligible_impressions, to a custom report and include them in your connection schema.