Sample_Queries.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#### 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,' ')) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment