5. Transactions

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

#Transactions object

  #Set the input data for the calculation

  WITH input AS
    (
      SELECT
          PARSE_DATE('%Y%m%d','20220901') AS date_start,
          PARSE_DATE('%Y%m%d','20220930') AS date_end,
          7 AS tdw, #Transaction deduplication window          
          'Europe/Kiev' AS timezone
    ),   
    
  #Selecting transactions from streaming
    transactions_web AS 
      (
        SELECT
          * EXCEPT(RN)
        FROM
          (
            SELECT
              date,    
              pseudo_session_id,
              events.timestamp as time,  
              events.transaction_id AS transactionId,
              events.transactionRevenue AS transactionRevenue,
              CAST(null AS STRING) AS status,
              "WEB" AS touchpoint,            
              ROW_NUMBER () OVER (PARTITION BY events.transaction_id ORDER BY events.timestamp ASC) AS RN
            FROM input AS i, `{{projectId}}.{{datasetName}}._temp_sessions`,
            UNNEST (events) as events 
            WHERE 
              date BETWEEN DATE_SUB(i.date_start, INTERVAL tdw DAY) AND i.date_end
          
          )
        WHERE RN=1  
      )

    SELECT 
      date,
      pseudo_session_id,
      transactionId,
      transactionRevenue,
      status,
      touchpoint,
    FROM transactions_web

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