Replicating Google Analytics Internal Search Statistics

Looker Q&A Looker
1

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;;
  }
2

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.