Converting Legacy SQL to Standard SQL - Enhannced Ecommerce

Question

I am in no way a coder so I have tried but falling over on this.

I want to use this query from Googles Google Analytics Big Query Cookbook

Products purchased by customers who purchased product A (Enhanced Ecommerce)

I have pasted the code below

Into Standard SQL.

I have made a few attemps but am falling over and not

Thank you in advance John

SELECT hits.product.productSKU AS other_purchased_products, 
  COUNT(hits.product.productSKU) AS quantity
FROM (
  SELECT fullVisitorId, hits.product.productSKU, hits.eCommerceAction.action_type 
    FROM TABLE_DATE_RANGE([bigquery-public-data:google_analytics_sample.ga_sessions_],
                          TIMESTAMP('2017-04-01'), TIMESTAMP('2017-04-20'))
)
WHERE fullVisitorId IN (
  SELECT fullVisitorId
    FROM TABLE_DATE_RANGE([bigquery-public-data:google_analytics_sample.ga_sessions_],
                          TIMESTAMP('2017-04-01'), TIMESTAMP('2017-04-20'))
    WHERE hits.product.productSKU CONTAINS 'GGOEYOCR077799'
    AND hits.eCommerceAction.action_type = '6'
    GROUP BY fullVisitorId 
)
AND hits.product.productSKU IS NOT NULL
AND hits.product.productSKU !='GGOEYOCR077799'
AND hits.eCommerceAction.action_type = '6'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

What results are you getting? Any errors? What results are you expecting? — David Hempy, Aug 28 '19 at 03:42

score 1 · Accepted Answer · answered Aug 28 '19 at 01:57

Below is pure equivalent in BigQuery Standard SQL (no any optimizations, improvements, etc. - just pure translation from legacy to standard)

SELECT productSKU AS other_purchased_products, COUNT(productSKU) AS quantity
FROM (
  SELECT fullVisitorId, prod.productSKU, hit.eCommerceAction.action_type 
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
  UNNEST(hits) hit, UNNEST(hit.product) prod
  WHERE _TABLE_SUFFIX BETWEEN '20170401' AND '20170420'
)
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
  UNNEST(hits) hit, UNNEST(hit.product) prod
  WHERE _TABLE_SUFFIX BETWEEN '20170401' AND '20170420'
  AND prod.productSKU LIKE '%GGOEYOCR077799%'
  AND hit.eCommerceAction.action_type = '6'
  GROUP BY fullVisitorId 
)
AND productSKU IS NOT NULL
AND productSKU !='GGOEYOCR077799'
AND action_type = '6'
GROUP BY other_purchased_products
ORDER BY quantity DESC

obviously produces exactly same result as legacy version

Thank you so so much, really helpful. Thank you again. – John Mitchell Aug 28 '19 at 02:32 — John Mitchell, Aug 28 '19 at 02:32

Converting Legacy SQL to Standard SQL - Enhannced Ecommerce

1 Answers1