CODEWITHSUNDEEP
google-bigquery
maggieto
maggieto

Reputation: 613

How to get Days since last session in BigQuery

I'm trying to get Days since last session out of my raw GA data in BigQuery. I see visitStartTime for the current session, but how can I get the previous session time?

Upvotes: 0

Views: 1250

Answers (1)

Willian Fuks
Willian Fuks

Reputation: 11797

Not sure if this is what you need but hopefully this gives you some insights on getting previous days:

SELECT
  fv,
  IF(dts IS NULL, NULL, DATE_DIFF(CURRENT_DATE(), PARSE_DATE("%Y%m%d", dts), DAY) ) count_days
FROM(
  SELECT 
    fullvisitorid fv,
    ARRAY_AGG(DISTINCT date ORDER BY date DESC)[SAFE_OFFSET(1)] dts
  FROM `dataset.ga_sessions_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AND  FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
  GROUP BY 1
)

As you said in your comment, you'll be running this analyzes daily so I used the CURRENT_DATE() variable as reference.

It gives for each customer how many days passed since last session. If there's no session before, then it returns NULL (so you can keep absolute values for reference in your analyzes).

You can play around with this query and adapt as you see fit, such as taking averages of past days since last session and so on.

Upvotes: 2

Related Questions