如何使用Bigquery查找上一页 [英] How can I find the previous page with Bigquery

问题描述

我想找到上一页，当前页是产品页.例如，我有此页面'

I want to find out the previous page where the current page is a product page. For example I have this page 'https://www.emag.ro/telefon-mobil-apple-iphone-x-64gb-4g-space-grey-mqac2rm-a/pd/DN094NBBM'and my previous page is this page 'https://www.emag.ro/search/telefoane-mobile/IPHONE/c?ref=srcql' How in terms of hitnumber I can return how many users had this behavior. I tried with this 2 query and I want to do a JOIN but I don't know how is better. Also, I tried with LAG function but I don't know for sure if I catch all the users.

Thank you in advance.

with
view_product as (

SELECT 
            ga.fullVisitorId AS GA_USER_ID,
            date as date,
            h.hitnumber as hitnumber,
            CONCAT(ga.fullVisitorId, cast(ga.visitId AS string)) AS SessionID,
            (SELECT VALUE FROM h.customDimensions WHERE INDEX = 10) AS PAGETYPE,
            (SELECT VALUE FROM h.customDimensions WHERE index =8) as ref_parameter,
            visitid as visitid,
            h.page.pagePath as page_path

            FROM 
            `emagbigquery.0` ga, 
            UNNEST(hits) AS h

             WHERE h.type='PAGE'
             AND  _TABLE_SUFFIX = '20190115'
             AND  (SELECT VALUE FROM h.customDimensions WHERE INDEX = 10) = 'viewproduct'

    )         

     ,

     SEARCH_page_WITH_REF_SRCQL as (

    select 
            date as date,
            ga.fullVisitorId AS GA_USER_ID,
            h.hitnumber as hitnumber,
            CONCAT(ga.fullVisitorId, cast(ga.visitId AS string)) AS SessionID,
            (SELECT VALUE FROM h.customDimensions WHERE INDEX = 10) AS PAGETYPE,
            (SELECT VALUE FROM h.customDimensions WHERE index =8) as ref_parameter,
            visitid as visitid,
            h.page.pagePath as page_path

            FROM 
            `emagbigquery.0` ga, 
            UNNEST(hits) AS h

             WHERE h.type='PAGE'
             AND  _TABLE_SUFFIX = '20190115'
             AND  (SELECT VALUE FROM h.customDimensions WHERE INDEX = 10) = 'search'
             AND (SELECT VALUE FROM h.customDimensions WHERE index =8) LIKE 'srcql'



     )

           select 

               COUNT(DISTINCT GA_USER_ID) AS USERS,
               COUNT(DISTINCT SessionID) AS SESSIONS,
              previous_page_from_srcql

               from ( 

             select 
             t1.ga_user_id,
             t1.sessionid,
             t2.hitnumber > t1.hitnumber  as previous_page_from_srcql
              from SEARCH_page_WITH_REF_SRCQL as t1
              inner join view_product as t2
              on t1.ga_user_id = t2.ga_user_id

              group by 
              previous_page_from_srcql

解决方案

Try UNNEST WITH OFFSET. It can give you an easy way to later determine that one row came after the other:

WITH path_and_prev AS (
  SELECT ARRAY(
    SELECT AS STRUCT session.page.pagePath
      , LAG(session.page.pagePath) OVER(ORDER BY i) prevPagePath
    FROM UNNEST(hits) session WITH OFFSET i
  ) x
  FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`
)

SELECT COUNT(*) c, pagePath, prevPagePath
FROM path_and_prev, UNNEST(x) 
WHERE pagePath='/vests/yellow.html' 
AND prevPagePath='/vests/'
GROUP BY 2,3

这篇关于如何使用Bigquery查找上一页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！