Sample_Queries.sql

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,' '))