[SOLVED] Calculate visits in BQ using Adobe Analytics data

Calculate visits in BQ using Adobe Analytics data

I am trying to calculate the total number of visits per day by extracting the raw data from Adobe Analytics in Big Query. I have limited skill in writing SQL queries, therefore I would really appreciate if someone could point out what I am doing wrong.

Query:

SELECT EXTRACT(DATE FROM date_time) AS Day, 
CONCAT(visid_high, "-", visid_low, "-", visit_start_time_gmt) AS visits 
FROM `adobe_data.table1` WHERE date_time > '2019-05-01' AND date_time < '2019-05-30' 
AND page_event = '0' 
AND exclude_hit = '0'
GROUP by Day;

The error I get is: "Select list expression column visits_high which is neither grouped not aggregated."

Note: I do not want to group by 'visits', I am trying to get visits by 'Day' (such as on 2019-05-01, visits was 12555).

Solution

You need to an aggregation function for the second expression. Perhaps:

SELECT EXTRACT(DATE FROM date_time) AS Day, 
       ARRAY_AGG(CONCAT(visid_high, '-', visid_low, '-', visit_start_time_gmt)) AS visits 
FROM `adobe_data.table1`
WHERE date_time > '2019-05-01' AND date_time < '2019-05-30' AND
      page_event = '0' AND
      exclude_hit = '0'
GROUP by Day;