Keywords nuevas que aparecieron en los últimos 7 días

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 37 38 39 40

-- Keywords nuevas: aparecieron en los últimos 7 días pero no en los 21 anteriores
WITH recientes AS (
  SELECT
    query AS keyword,
    SUM(clicks) AS clics,
    SUM(impressions) AS impresiones,
    ROUND(SUM(sum_top_position + impressions) / SUM(impressions), 2) AS posicion_media
  FROM
    `your-project.searchconsole.searchdata_site_impression`
  WHERE
    data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND CURRENT_DATE()
    AND search_type = 'WEB'
    AND is_anonymized_query = FALSE
  GROUP BY
    keyword
),
anteriores AS (
  SELECT DISTINCT query AS keyword
  FROM
    `your-project.searchconsole.searchdata_site_impression`
  WHERE
    data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
      AND DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY)
    AND search_type = 'WEB'
    AND is_anonymized_query = FALSE
)
SELECT
  r.keyword,
  r.clics,
  r.impresiones,
  r.posicion_media
FROM
  recientes r
  LEFT JOIN anteriores a ON r.keyword = a.keyword
WHERE
  -- Solo keywords que NO existían en el periodo anterior
  a.keyword IS NULL
ORDER BY
  r.impresiones DESC
LIMIT 50