konosp

-- This query is not valid as there is no realtime data available in the sample data set.
SELECT
  date AS Date,
  channelGrouping AS Default_Channel_Grouping,
  Sessions
FROM (
  SELECT
    date,
    CASE
      WHEN trafficSource.source = '(direct)' AND (trafficSource.medium = '(not set)' OR trafficSource.medium = '(none)') THEN 'Direct'

konosp

-- This query is not valid as there is no realtime data available in the sample data set.
SELECT
  date,
  COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))) AS Sessions
FROM
  `project.dataset.ga_realtime_view`
WHERE
  suffix = CURRENT_DATE()
  AND totals.visits = 1
GROUP BY

konosp

-- This query is not valid as there is no realtime data available in the sample data set.
CREATE VIEW
  `project.dataset.ga_realtime_view` AS
SELECT
  PARSE_DATE('%y%m%d',
    _TABLE_SUFFIX) AS suffix,
  visitKey AS visitKey2,
  ARRAY_AGG(t
  ORDER BY
    exportTimeUsec DESC

konosp

-- This query will return no intraday data as there is no intraday data available in the sample data set.
SELECT
  _table_suffix AS Table_Suffix,
  COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))) AS Sessions
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  REGEXP_EXTRACT(_table_suffix, r'[0-9]+') BETWEEN '20160801'
  AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  AND totals.visits = 1

konosp

SELECT
  -- Default Channel Grouping (dimension)
  CASE
    WHEN trafficSource.source = '(direct)' AND (trafficSource.medium = '(not set)' OR trafficSource.medium = '(none)') THEN 'Direct'
    WHEN trafficSource.medium = 'organic' THEN 'Organic Search'
    WHEN hits.social.hasSocialSourceReferral = 'Yes' OR REGEXP_CONTAINS(trafficSource.medium, r'^(social|social-network|social-media|sm|social network|social media)$') THEN 'Social'
    WHEN trafficSource.medium = 'email' THEN 'Email'
    WHEN trafficSource.medium = 'affiliate' THEN 'Affiliates'
    WHEN trafficSource.medium = 'referral' THEN 'Referral'
    WHEN REGEXP_CONTAINS(trafficSource.medium, r'^(cpc|ppc|paidsearch)$') AND trafficSource.adwordsClickInfo.adNetworkType != 'Content' THEN 'Paid Search'

konosp

  -- Some sample set custom dimensions return null values
SELECT
  -- Custom Dimension XX (User)
  (
  SELECT
    value
  FROM
    UNNEST(session.customDimensions)
  WHERE
    index = 3

konosp

SELECT
  -- Product SKU (dimension)
  productSKU AS Product_SKU,
  -- Unique Purchases (metric)
  COUNT(CASE
      WHEN hits.eCommerceAction.action_type = '6' THEN hits.transaction.transactionId
    ELSE
    NULL
  END
    ) AS Unique_Purchases,

konosp

SELECT
  -- Product SKU (dimension)
  productSKU AS Product_SKU,
  -- Product (dimension)
  v2ProductName AS Product,
  -- Product Variant (dimension)
  productVariant AS Product_Variant,
  -- Product Brand (dimension)
  productBrand AS Product_Brand,
  -- Product Category (Enhanced Ecommerce) (dimension)

konosp

  -- This query will return no data as there is no standard ecommerce product data available in the sample data set.
SELECT
  -- Product SKU (dimension)
  hits.item.productSku AS Product_SKU,
  -- Product (dimension)
  hits.item.productName AS Product,
  -- Product Category (dimension)
  hits.item.productCategory AS Product_Category,
  -- Quantity
  SUM(hits.item.itemQuantity) AS Quantity,

konosp

SELECT
  -- Transaction ID (dimension)
  hits.transaction.transactionId AS Transaction_ID,
  -- Transactions (metric)
  COUNT(DISTINCT hits.transaction.transactionId) AS Transactions,
  -- Revenue (metric)
  SUM(hits.transaction.transactionRevenue)/1000000 AS Revenue,
  -- Ecommerce Conversion Rate
  COUNT(DISTINCT hits.transaction.transactionId) / COUNT(DISTINCT CONCAT(CAST(fullVisitorId AS STRING), CAST(visitStartTime AS STRING))) AS Ecommerce_Conversion_Rate,
  -- Avg. Order Value