6. TrafficSource

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.

#Preparing TrafficSource object

  WITH input AS
    (
      SELECT
          PARSE_DATE('%Y%m%d','20220901') AS date_start,
          PARSE_DATE('%Y%m%d','20220930') AS date_end
    ) 
 
  SELECT    
    CAST(FARM_FINGERPRINT(CONCAT( --deviceHash
          IFNULL(session_source, ''),
          IFNULL(session_medium, ''),
          IFNULL(session_campaign, ''),
          IFNULL(session_keyword, ''),
          IFNULL(session_adContent , '')
        )) AS STRING) AS trafficSourceHash,        
    session_source as source,        
    session_medium as medium,
    session_campaign as campaign,
    session_keyword as keyword,
    session_adContent as adContent
  FROM input as i, `{{projectId}}.{{datasetName}}._temp_sessions`
  WHERE date BETWEEN i.date_start AND i.date_end
  GROUP BY 1,2,3,4,5,6

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