boydnorwood

-- Merchant Analysis in Product Listings In SERPs
-- Change the workspace slug and project slug (workspaceslug_projectslug) on lines 9, 22, and 116 to your own slugs

WITH

-- filter keywords early to reduce query execution time 
filtered_keyword_ids AS (
  SELECT keyword_id
  FROM nozzledata.workspaceslug_projectslug.latest_keywords_by_keyword_id
  JOIN UNNEST(keyword_groups) AS kg

boydnorwood

--For Local Pack Dashboard: For generating Pages 1, 2, and 4 on the template
WITH
-- find the latest versioned keyword data
-- this can also be used to pin a query to an older version, good for static reports
latest_keyword_source_versions AS (
  SELECT keyword_source_id, MAX(keyword_source_version_id) AS keyword_source_version_id
  FROM nozzledata.localseodemocompany_localseodemoco.keywords
  GROUP BY keyword_source_id
),

boydnorwood

-- Nozzle data top 20 results csv export

WITH
latest_filtered_rankings AS (
  SELECT AS VALUE
    ARRAY_AGG(t ORDER BY inserted_at DESC LIMIT 1)[OFFSET(0)]
  FROM nozzledata.nozzle_nozzleofficial.rankings t
  WHERE requested >= '2022-01-01 00:00:00' AND requested < '2022-01-25 00:00:00'
  GROUP BY ranking_id
  ),

boydnorwood

--Top Ranking URLs Report for Nozzle.io
--Data Studio Template can be found here: https://datastudio.google.com/u/1/reporting/359d4414-0cd6-4da1-8df5-2c6908e0ddec/page/pyxcB

WITH
-- find the latest versioned keyword data
-- this can also be used to pin a query to an older version, good for static reports
latest_keyword_source_versions AS (
 SELECT keyword_source_id, MAX(keyword_source_version_id) AS keyword_source_version_id
 FROM nozzledata.nozzle_nozzleofficial.keywords
 WHERE keyword_source_id=930701976723823