Cacher is the code snippet organizer for pro developers

#StandardSQL
WITH landingpages AS (
  SELECT
    fullVisitorId,
    visitID,
    h.page.pagePath AS LandingPage
  FROM
    `project_id.dataset.table`, UNNEST(hits) AS h
  WHERE hitNumber = 1
), 
sales AS (
   SELECT
      fullVisitorId, visitID, SUM(totals.transactions) AS Transactions , (SUM(totals.transactionRevenue)/1000000) AS Revenue
    FROM
      `project_id.dataset.table`
    WHERE
      totals.visits > 0
      AND totals.transactions >= 1
      AND totals.transactionRevenue IS NOT NULL
    GROUP BY fullVisitorId, visitID
)
SELECT 
  LandingPage, 
  SUM(Transactions) AS Transactions, 
  SUM(Revenue) AS Revenue
FROM landingpages 
JOIN sales
ON landingpages.VisitID = sales.VisitID 
AND landingpages.fullVisitorId = sales.fullVisitorId
GROUP BY LandingPage