Google analytics
Event Performance
This SQL template aggregates Google Analytics event data by month, summarizing event counts, user counts, average events per user, and total revenue for each event. It groups the data by period and event name, then orders the results by event count in descending order.
1select
2 date_trunc(date, month) as period -- Change to 'DAY' for daily overview
3 , event_name
4 , sum(event_count) as event_count
5 , sum(total_users) as total_users
6 , avg(event_count_per_user) as event_count_per_user
7 , sum(total_revenue) as total_revenue
8from
9 {{raw.google_analytics_4.events_overview}}
10group by
11 period
12 , event_name
13order by
14 event_count desc;Example of output from model:
period | event_name | event_count | total_users | event_count_per_user | total_revenue
-----------|---------------|-------------|-------------|----------------------|--------------
2023-01-01 | page_view | 5000 | 3000 | 1.67 | 15000
2023-01-01 | purchase | 1200 | 800 | 1.50 | 24000
2023-01-01 | sign_up | 800 | 700 | 1.14 | 0
2023-01-01 | add_to_cart | 1500 | 1000 | 1.50 | 5000
2023-01-01 | checkout | 900 | 600 | 1.50 | 18000The "Event Performance" SQL model for Google Analytics integration provides a comprehensive overview of event metrics over specified periods, such as months or days. By aggregating data from the Google Analytics 4 events overview, this model calculates total event counts, user counts, average events per user, and total revenue generated by events. This SQL model is particularly useful for understanding user engagement and behavior, identifying high-performing events, and assessing the revenue impact of different events. It enables businesses to make data-driven decisions to optimize their event strategies and improve overall performance.
