Comparativa periodo actual vs anterior por URL

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 41 42 43

-- Comparativa de URLs: últimos 14 días vs 14 días anteriores
WITH datos AS (
  SELECT
    url,
    CASE
      WHEN data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) THEN 'actual'
      ELSE 'anterior'
    END AS periodo,
    clicks,
    impressions,
    sum_top_position
  FROM
    `your-project.searchconsole.searchdata_url_impression`
  WHERE
    data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
    AND search_type = 'WEB'
)
SELECT
  url,
  SUM(IF(periodo = 'anterior', clicks, 0)) AS clics_anterior,
  SUM(IF(periodo = 'actual', clicks, 0)) AS clics_actual,
  SUM(IF(periodo = 'actual', clicks, 0)) - SUM(IF(periodo = 'anterior', clicks, 0)) AS dif_clics,
  ROUND(SAFE_DIVIDE(
    SUM(IF(periodo = 'actual', clicks, 0)) - SUM(IF(periodo = 'anterior', clicks, 0)),
    SUM(IF(periodo = 'anterior', clicks, 0))
  ) * 100, 2) AS variacion_clics_pct,
  ROUND(SAFE_DIVIDE(
    SUM(IF(periodo = 'anterior', sum_top_position + impressions, 0)),
    SUM(IF(periodo = 'anterior', impressions, 0))
  ), 2) AS pos_anterior,
  ROUND(SAFE_DIVIDE(
    SUM(IF(periodo = 'actual', sum_top_position + impressions, 0)),
    SUM(IF(periodo = 'actual', impressions, 0))
  ), 2) AS pos_actual
FROM
  datos
GROUP BY
  url
HAVING
  clics_anterior >= 10
ORDER BY
  dif_clics ASC
LIMIT 50