WITH

period AS
(SELECT "20170710" as start, "20170716" as finish),

ses AS (
SELECT clientId, geoNetwork.country
FROM `dokdbroker.OWOXBI_Streaming.session_streaming_*`
WHERE _TABLE_SUFFIX BETWEEN (SELECT start FROM period) AND (SELECT finish FROM period)
  AND clientId IS NOT null
)

SELECT
  week, funnelPhase, first_action,
  datasource, country,
  COUNT (DISTINCT session) numSession
FROM (
  SELECT
    week,
    session,
    funnelPhase,
    IF (funnelPhase NOT IN ("Visit", "NoBounce", "Value"),
      FIRST_VALUE (action_type) OVER (PARTITION BY session ORDER BY IF (action_type="0", 1, 0), time, hitCounter ASC),
      "до действия") first_action,
    datasource,
    country
  FROM (
    SELECT
      DIV(EXTRACT(DAYOFYEAR from PARSE_DATE("%E4Y%m%e", date)) + 5, 7) week,
      (SELECT value FROM UNNEST(customDimensions) WHERE index = 2) AS session,
      (SELECT value FROM UNNEST(customDimensions) WHERE index = 3) AS funnelPhase,
      (SELECT value FROM UNNEST(customDimensions) WHERE index = 5) AS hitCounter,
      (SELECT value FROM UNNEST(customGroups) WHERE index = 1) AS side,
      time,
      CASE
        WHEN (eventInfo.eventCategory="Form-1click" AND eventInfo.eventAction="new-order") THEN "ЗОК"
        WHEN eventInfo.eventAction="successfully-callback" THEN "Коллбэк"
        WHEN (eventInfo.eventAction="add-to-cart" AND eventInfo.eventLabel="catalog-buy") THEN "Купить с плитки"
        WHEN (eventInfo.eventAction="add-to-cart" AND eventInfo.eventLabel="article") THEN "Купить с карточки"
        ELSE "0" END AS action_type,
      datasource,
      IF (clientId IN (SELECT clientId FROM ses WHERE country = "Ukraine")
        OR clientId NOT IN (SELECT clientId FROM ses), "Ukraine or NA", "abroad") country
    FROM `dokdbroker.OWOXBI_Streaming.streaming_*`
    WHERE _TABLE_SUFFIX BETWEEN (SELECT start FROM period) AND (SELECT finish FROM period)
  )
  WHERE
    funnelPhase IS NOT NULL AND funnelPhase <> "NA" AND session <> "0"
  GROUP BY
    week, funnelPhase, session, action_type, hitCounter, time, datasource, country
)
GROUP BY
    week, funnelPhase, first_action, datasource, country

ORDER BY
  week, funnelPhase, datasource