CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
Omar Gonzales
Omar Gonzales

Reputation: 4008

How to get sessions by day, by userid (as custom dimension)?

I can get sessions by user ID, but I'm having problems to select also date. So my end result would have 3 cols: date, userid, sessions (where sessions is the total number of sessions for that day).

Sessions by user id, missing the date column:

SELECT
    (SELECT value FROM UNNEST(customDimensions) WHERE index=2) AS userId,
    sum(totals.visits) AS sessions,
FROM
  `ga-360-tvgo.76246634.ga_sessions_*`
WHERE _table_suffix BETWEEN "20200701" AND "20200723"
GROUP BY
  1

Expected result:

-an

Upvotes: 0

Views: 41

Answers (1)

Martin Weitzmann
Martin Weitzmann

Reputation: 4746

I think you can simply add it to your groups

SELECT
    date,
    (SELECT value FROM UNNEST(customDimensions) WHERE index=2) AS userId,
    sum(totals.visits) AS sessions,
FROM
  `ga-360-tvgo.76246634.ga_sessions_*`
WHERE _table_suffix BETWEEN "20200701" AND "20200723"
GROUP BY
  1,2

Upvotes: 1

Related Questions