6. Transactions

Use the SQL code below and change {{projectId}} and {{datasetName}} to your own.

#Transactions object
WITH 
  #Set the date range for the calculation
    dates AS
      ( 
        SELECT
          '20220901' AS date_start,
          '20220930' AS date_end,  
          7 AS tdw,
      ),
  #Selecting transactions from streaming
    transactions_web AS 
      (
        SELECT
          * EXCEPT(RN)
        FROM
          (
            SELECT
              date,      
              sessionId,
              transactionId,
              transactionRevenue,
              CAST(null AS STRING) AS status,
              "WEB" AS touchpoint,            
              ROW_NUMBER () OVER (PARTITION BY transactionId ORDER BY time ASC) AS RN
            FROM dates AS d, `{{projectId}}.{{datasetName}}._temp_transactions` 
            WHERE date BETWEEN DATE_SUB(PARSE_DATE('%Y%m%d',d.date_start), INTERVAL tdw DAY) AND PARSE_DATE('%Y%m%d',d.date_end)
          )
        WHERE RN=1  
      )

    SELECT
      date,
      sessionId,
      transactionId,
      transactionRevenue,
      status,
      touchpoint
    FROM
      transactions_web as web_data,
      dates as d
   WHERE date BETWEEN PARSE_DATE('%Y%m%d',d.date_start) AND PARSE_DATE('%Y%m%d',d.date_end)

You will get the data in the table 'transactions' with the schema below: