leighajarett

//// Using BigQuery query results to populate a template slide deck ////

// This object defines the queries associate with each of the params used in the template
// Note that you may want to use functions defined within BigQuery instead of parameterized queries 
var queryObj = {
  "new_cust": "SELECT COUNT(*) FROM `looker-private-demo.retail.customers` as custs " +
              "JOIN `looker-private-demo.retail.transaction_detail` as trans ON custs.id = trans.customer_id " +
              ", UNNEST(trans.line_items) as line_items " +
              "WHERE TIMESTAMP_TRUNC(custs.created_at,WEEK) = @week " + 
              "AND line_items.product_id in (select id from `looker-private-demo.retail.products` where category = @team);",

leighajarett

//// Using BigQuery query results to populate a template slide deck ////

// This object defines the queries associate with each of the params used in the template
var queryObj = {
  "new_cust": "SELECT COUNT(*) FROM `looker-private-demo.retail.customers` as custs " +
              "JOIN `looker-private-demo.retail.transaction_detail` as trans ON custs.id = trans.customer_id " +
              ", UNNEST(trans.line_items) as line_items " +
              "WHERE TIMESTAMP_TRUNC(custs.created_at,WEEK) = @week " + 
              "AND line_items.product_id in (select id from `looker-private-demo.retail.products` where category = @team);",
  "rep_rate": "WITH repeat_orders as " +

leighajarett

#### 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) 

leighajarett

leighajarett

leighajarett

-- Declare a variable to hold the timeframe (month, quarter or year)
DECLARE timeframe STRING DEFAULT "month";

leighajarett