joseramoncajide
10/12/2017 - 9:29 PM

page_paths.sql

page_paths.sql
content_copyfile_download
SELECT
    fullVisitorId,
    visitID,
    hits.page.pagePath  AS LandingPage,
    IF(REGEXP_MATCH(hits.page.pagePath, r'^(.*?)(?:VER_TODO|Pagina_[\d]+|$)' ),REGEXP_EXTRACT(hits.page.pagePath,r'^(.*?)(?:VER_TODO|Pagina_[\d]+|$)'), hits.page.pagePath) AS pagePath,
      NTH(1, SPLIT(IF(REGEXP_MATCH(hits.page.pagePath, r'^(.*?)(?:VER_TODO|Pagina_[\d]+|$)' ),REGEXP_EXTRACT(hits.page.pagePath,r'^(.*?)(?:VER_TODO|Pagina_[\d]+|$)'), hits.page.pagePath), '/')) AS pagePathLevel1,
      NTH(2, SPLIT(IF(REGEXP_MATCH(hits.page.pagePath, r'^(.*?)(?:VER_TODO|Pagina_[\d]+|$)' ),REGEXP_EXTRACT(hits.page.pagePath,r'^(.*?)(?:VER_TODO|Pagina_[\d]+|$)'), hits.page.pagePath), '/')) AS pagePathLevel2,
      NTH(3, SPLIT(IF(REGEXP_MATCH(hits.page.pagePath, r'^(.*?)(?:VER_TODO|Pagina_[\d]+|$)' ),REGEXP_EXTRACT(hits.page.pagePath,r'^(.*?)(?:VER_TODO|Pagina_[\d]+|$)'), hits.page.pagePath), '/')) AS pagePathLevel3,
      NTH(4, SPLIT(IF(REGEXP_MATCH(hits.page.pagePath, r'^(.*?)(?:VER_TODO|Pagina_[\d]+|$)' ),REGEXP_EXTRACT(hits.page.pagePath,r'^(.*?)(?:VER_TODO|Pagina_[\d]+|$)'), hits.page.pagePath), '/')) AS pagePathLevel4,
      NTH(5, SPLIT(IF(REGEXP_MATCH(hits.page.pagePath, r'^(.*?)(?:VER_TODO|Pagina_[\d]+|$)' ),REGEXP_EXTRACT(hits.page.pagePath,r'^(.*?)(?:VER_TODO|Pagina_[\d]+|$)'), hits.page.pagePath), '/')) AS pagePathLevel5,
      NTH(6, SPLIT(IF(REGEXP_MATCH(hits.page.pagePath, r'^(.*?)(?:VER_TODO|Pagina_[\d]+|$)' ),REGEXP_EXTRACT(hits.page.pagePath,r'^(.*?)(?:VER_TODO|Pagina_[\d]+|$)'), hits.page.pagePath), '/')) AS pagePathLevel6
 FROM
    [zara-bq-2:97466444.ga_sessions_20171010]
  WHERE visitNumber = 1 AND hits.page.pagePath like '%/Catalogo/I2017/MUJER/ABRIGOS/TRENCH%'
  LIMIT 500