Keywords branded vs non-branded

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

-- Comparativa branded vs non-branded
-- Reemplazar los términos de marca en la lista LIKE
WITH clasificacion AS (
  SELECT
    query,
    clicks,
    impressions,
    sum_top_position,
    CASE
      -- Reemplazar 'mimarca' y 'mi marca' por los términos de la marca real
      WHEN LOWER(query) LIKE '%mimarca%'
        OR LOWER(query) LIKE '%mi marca%'
        THEN 'Branded'
      ELSE 'Non-branded'
    END AS tipo
  FROM
    `your-project.searchconsole.searchdata_site_impression`
  WHERE
    data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
    AND search_type = 'WEB'
    AND is_anonymized_query = FALSE
)
SELECT
  tipo,
  SUM(clicks) AS clics,
  SUM(impressions) AS impresiones,
  ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr,
  ROUND(SUM(sum_top_position + impressions) / SUM(impressions), 2) AS posicion_media,
  COUNT(DISTINCT query) AS keywords_distintas,
  ROUND(SUM(clicks) * 100.0 / SUM(SUM(clicks)) OVER(), 2) AS porcentaje_clics
FROM
  clasificacion
GROUP BY
  tipo
ORDER BY
  clics DESC