Parámetros dinámicos en Looker Studio para filtrar por fecha

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 44 45 46 47 48 49 50

-- Parámetros dinámicos en Looker Studio con periodo de comparación
-- Los parámetros @DS_ se vinculan al selector de fechas del dashboard
-- El periodo de comparación se calcula automáticamente con la misma duración
WITH periodo_actual AS (
  SELECT
    'Actual' AS periodo,
    url,
    SUM(clicks) AS clics,
    SUM(impressions) AS impresiones,
    ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr
  FROM
    `your-project.searchconsole.searchdata_url_impression`
  WHERE
    data_date BETWEEN @DS_START_DATE AND @DS_END_DATE
    AND search_type = 'WEB'
  GROUP BY
    url
),
periodo_anterior AS (
  SELECT
    'Anterior' AS periodo,
    url,
    SUM(clicks) AS clics,
    SUM(impressions) AS impresiones,
    ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr
  FROM
    `your-project.searchconsole.searchdata_url_impression`
  WHERE
    -- Calcula el periodo anterior con la misma duración
    data_date BETWEEN
      DATE_SUB(@DS_START_DATE, INTERVAL DATE_DIFF(@DS_END_DATE, @DS_START_DATE, DAY) + 1 DAY)
      AND DATE_SUB(@DS_START_DATE, INTERVAL 1 DAY)
    AND search_type = 'WEB'
  GROUP BY
    url
)
SELECT
  COALESCE(a.url, p.url) AS url,
  IFNULL(p.clics, 0) AS clics_anterior,
  IFNULL(a.clics, 0) AS clics_actual,
  IFNULL(a.clics, 0) - IFNULL(p.clics, 0) AS diferencia,
  ROUND(SAFE_DIVIDE(
    IFNULL(a.clics, 0) - IFNULL(p.clics, 0),
    IFNULL(p.clics, 0)
  ) * 100, 2) AS variacion_pct
FROM
  periodo_actual a
  FULL OUTER JOIN periodo_anterior p ON a.url = p.url
ORDER BY
  diferencia DESC