9. TrafficSources

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.

#TrafficSources object
WITH
  #Set the date range for the calculation
    dates as 
      ( 
        SELECT
          '20220901' AS date_start,
          '20220930' AS date_end,
      )

    SELECT
      CAST(FARM_FINGERPRINT(CONCAT(
        IFNULL(trafficSource.source,""),
        IFNULL(trafficSource.medium,""),
        IFNULL(trafficSource.campaign,""),
        IFNULL(trafficSource.keyword,""),
        IFNULL(trafficSource.adContent,""))) as STRING) as trafficSourceHash,
      trafficSource.source,
      trafficSource.medium,
      trafficSource.campaign,
      trafficSource.keyword,
      trafficSource.adContent,
    FROM
      dates as d, `{{projectId}}.{{datasetName}}.ga_sessions_*`
    WHERE 
      _TABLE_SUFFIX BETWEEN d.date_start and d.date_end
    GROUP BY 1,2,3,4,5,6

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