5. Transactions

NEW documentation: This article covers setup for OWOX BI. For the latest guides on setting up FREE connectors and Data Marts, visit the new OWOX Data Marts documentation.

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: