6. 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
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: