leighajarett · July 20, 2021 16:21
diff --git a/Google Trends Sample Queries b/Google Trends Sample Queries
 #### Google Trends Sample Queries ####

 # Look at trends relevant to your stores
 SELECT stores.id as store_id, array_agg(term ORDER BY score DESC LIMIT 3) as rising_terms
 FROM `looker-private-demo.retail.us_stores` as stores
 JOIN  `bigquery-public-data.geo_us_boundaries.designated_market_area`  as dma
 ON ST_WITHIN(ST_GEOGPOINT(stores.Longitude, stores.Latitude),dma.dma_geom)
 JOIN `bigquery-public-data.google_trends.top_terms` as trends
 ON dma.dma_id = CAST(trends.dma_id as STRING)
 WHERE trends.refresh_date =  DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) 
 AND stores.id IS NOT NULL 
 AND trends.week = DATE_TRUNC(CURRENT_DATE(), WEEK)
 GROUP BY 1;

 #Pre-process text data and join with trends
 CREATE TEMP FUNCTION pre_process(text STRING) as (
    (SELECT array_agg(word)
    FROM UNNEST(SPLIT(LOWER(REGEXP_REPLACE(text, r'[\.\",*:()\[\]/|\n]', ' ')), ' ')) as word
    WHERE word NOT IN UNNEST(["a", "an", "the", "and", "but", "if", "or",  "as",  "of", "at", "by", "for", "with",
 "to", "from", "nice","yummy","authentic","tasty","gooey","delicious","fresh"]) 
    AND NOT REGEXP_CONTAINS(word, "[0-9]")) 
 );

 WITH product as (SELECT "10oz Authentic Ham and Sausages from Spain" as product_name)
 SELECT DISTINCT
 product_name, 
 terms.term
 FROM 
 product
 ,UNNEST(pre_process(product_name)) as processed_name
 ,(SELECT DISTINCT term FROM `bigquery-public-data.google_trends.top_rising_terms`) as terms
 WHERE processed_name in UNNEST(SPLIT(terms.term,' '))
	#### Google Trends Sample Queries ####

	# Look at trends relevant to your stores
	SELECT stores.id as store_id, array_agg(term ORDER BY score DESC LIMIT 3) as rising_terms
	FROM `looker-private-demo.retail.us_stores` as stores
	JOIN `bigquery-public-data.geo_us_boundaries.designated_market_area` as dma
	ON ST_WITHIN(ST_GEOGPOINT(stores.Longitude, stores.Latitude),dma.dma_geom)
	JOIN `bigquery-public-data.google_trends.top_terms` as trends
	ON dma.dma_id = CAST(trends.dma_id as STRING)
	WHERE trends.refresh_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
	AND stores.id IS NOT NULL
	AND trends.week = DATE_TRUNC(CURRENT_DATE(), WEEK)
	GROUP BY 1;

	#Pre-process text data and join with trends
	CREATE TEMP FUNCTION pre_process(text STRING) as (
	(SELECT array_agg(word)
	FROM UNNEST(SPLIT(LOWER(REGEXP_REPLACE(text, r'[\.\",*:()\[\]/\|\n]', ' ')), ' ')) as word
	WHERE word NOT IN UNNEST(["a", "an", "the", "and", "but", "if", "or", "as", "of", "at", "by", "for", "with",
	"to", "from", "nice","yummy","authentic","tasty","gooey","delicious","fresh"])
	AND NOT REGEXP_CONTAINS(word, "[0-9]"))
	);

	WITH product as (SELECT "10oz Authentic Ham and Sausages from Spain" as product_name)
	SELECT DISTINCT
	product_name,
	terms.term
	FROM
	product
	,UNNEST(pre_process(product_name)) as processed_name
	,(SELECT DISTINCT term FROM `bigquery-public-data.google_trends.top_rising_terms`) as terms
	WHERE processed_name in UNNEST(SPLIT(terms.term,' '))