supercooldata
Published Wed, May 26, 2021 by Bayan Hammami
Building funnels with SQL doesn’t need to be hard. It’s often touted as the hardest thing to do in SQL and that you would need hundreds of lines with complex joins and window functions.
This isn’t true, there’s an easier way and it involves some regular expressions and a nice function called string_agg in BigQuery. This function is also known as listagg in Redshift or Snowflake. We will use the BigQuery syntax for all examples here.
Say you have a table of events called course.google_analytics. This table has one row for every event fired off by the user. The first step is to aggregate this table for every session and use the function string_agg to create a comma separated list of events this user performed in the order that they occurred.
We’ve now created the column events_in_session which summarises the events that occurred in each session. Each session is defined by a user id (fullvisitorid) and a session start time (visitstarttime) pair.
