Replicating Google Analytics Internal Search Statistics

Hi all, new Looker developer here asking for help.

I’m working on recreating a set of measures to replicate GA metrics. As an example, I have the need for the “number of search term refinements” made. I constructed SQL in BQ like the following for a single day as an example. Can I replicate “GROUP BY” functionality?

SELECT 
COUNT(visitorSessionGroup) AS count_of_search_refinements
FROM
(
#Count of unique search terms searched per session by a visitor
SELECT
DISTINCT CONCAT(fullVisitorId,visitStartTime) AS visitorSessionGroup,
COUNT(DISTINCT hits.page.searchKeyword) AS count_of_distinct_searches
FROM
`{table}.ga_sessions_*`, UNNEST(hits) as hits
WHERE
_TABLE_SUFFIX = "20220321"
and (hits.page.searchKeyword is not NULL)
GROUP BY 1
)
WHERE count_of_distinct_searches > 1

I’m using the GA360 “hits.view.lkml” and have access to hits through ${TABLE} and sessions thru ${ga_sessions.id}, but I’m really not sure how to build my measure SQL block to use a GROUP BY function. Is it even possible? This is as far as I got in an experiment.
 

#count_of_search_refinements
measure: Search_Refinements{
view_label: "Internal Site Search"
type: number
sql: CASE WHEN
COUNT(DISTINCT CONCAT(${ga_sessions.id},${TABLE}.page.searchKeyword)) > 1
THEN ${ga_sessions.id} END;;
}


 

0 1 89
1 REPLY 1

Update: Instead of hacking the SQL in the hits view I created a NEW view for the needed SQL and joined it, adding it to my model. All good so far.

Top Labels in this Space
Top Solution Authors