User Segmentation in Google Analytics Data via SQL

1

Hi everyone and infront thanks for your time and help... I have a questions regarding Google Analytics Big Query Data. I want to analyse Google Analytics(4) Data based on different user Segments. I have a raw data table with user_id, event, timestamp/date. Now I want to build three different user segments:

  • Users who triggered event1 then event2 (based on the timestamp)
  • Users who triggered event1 <10 minutes after event2
  • Users who triggered event1 directly after event2
    The raw data is event scoped, so every event is one row and its a normal relational database.

I can query for users who had event 1 and 2, but I don't know how to query for the different order-szenarios shown above.

The basic query for users with both events would be:
Select
user_pseudo_id
from >table name<
where event in (event1,event2)

Many thanks for your support, Rapid_SQL:)

2

SQLTeam.com is a Microsoft SQL Server site. We're not experts in Google Big Query.

However, what you describe sounds like something that can be done using LAG or LEAD window functions:

If you scroll that page down to LAG you'll find an example. There's similar functionality in SQL Server, so we may be able to translate it for you, but we would need actual sample data from you, plus the results you're expecting, so we can ensure the SQL is correct.