markrittman’s gists

markrittman / ga4_session_event_counts.sql

Last active June 7, 2022 03:34

Count various event types for a GA4 session, whether user is returning and whether it's a bounced session and/or converting session

	WITH events as (SELECT
	user_pseudo_id,
	(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
	(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS session_number,
	replace(lower(event_name),' ','_') event_name,
	count(*) events
	FROM `ra-development.analytics_277223877.events_*`
	GROUP BY
	1, 2, 3, 4),
	session_events_pivoted as (

markrittman / ga4_list_events_params.sql

Last active November 5, 2021 16:43

List out all GA4 events and parameters

	SELECT
	event_name,
	event_params.key AS event_params_key
	FROM
	`ra-development.analytics_277223877.events_*` AS t
	CROSS JOIN UNNEST (t.event_params) AS event_params
	group by 1,2
	order by 1,2

markrittman / ga4_sessions.sql

Created July 18, 2021 16:14

SQL Query to list out all sessions in GA4

	SELECT
	user_pseudo_id,
	TIMESTAMP_MICROS(event_timestamp) AS session_start_ts,
	CAST(LEAD(TIMESTAMP_MICROS(event_timestamp),1) OVER (PARTITION BY CONCAT(user_pseudo_id)
	ORDER BY
	event_timestamp) AS timestamp) AS session_end_ts,
	(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
	(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS session_number,
	(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS referrer,
	(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS landing_page_path,

markrittman / ga4_page_views.sql

Created July 18, 2021 14:07

GA4 Individual page views with device and geography information SQL query

	SELECT
	event_timestamp,
	(
	SELECT
	value.string_value
	FROM
	UNNEST(event_params)
	WHERE
	event_name = 'page_view'
	AND key = 'page_location') AS page_path,

markrittman / ga4_visitor_journey.sql

Last active July 11, 2023 10:01

User Page View Journey for GA4

	SELECT
	user_pseudo_id,
	geo.city,
	TIMESTAMP_MICROS(MIN(event_timestamp)) AS session_start_ts,
	traffic_source.name AS channel,
	traffic_source.medium AS medium,
	traffic_source.source AS source,
	(
	SELECT
	value.int_value

markrittman / ga4_attribution_model.sql

Last active August 23, 2025 13:10

First-Click, Last-Click, Even-Click and Time Decay Attribution for GA4

	/*
	First-Click, Last-Click, Even-Click and Time Decay Attribution for GA4
	Replace "event_name in ('Contact Us','Contact Us Clicked','CTA Pressed','Form Submitted')" with the name(s) of your conversion event in lines 54, 57, 61 and 76
	*/
	WITH
	events AS (
	SELECT
	event_timestamp as event_ts,
	user_pseudo_id AS user_pseudo_id,
	user_id,

markrittman / rs_segment_analytics.js

Created July 15, 2021 19:41

JS Snippet for loading segment/RS Javascript tracking scripts together (the SEGMENT_WRITE_KEY RUDDERSTACK_WRITE_KEY DATA_PLANE_URL need to be replaced, and you can remove comments and minify as needed)

	<script type="text/javascript">
	(function(){
	// Create a queue, but don't obliterate an existing one!
	var analytics = window.analytics = window.analytics \|\| [];
	// If the real analytics.js is already on the page return.
	if (analytics.initialize) return;
	// If the snippet was invoked already show an error.
	if (analytics.invoked) {
	if (window.console && console.error) {
	console.error('Segment snippet included twice.');

markrittman / query_cost_by_query_by_month.sql

Created June 25, 2021 13:06

	SELECT
	date_trunc(date(creation_time),MONTH) as billing_month,
	user_email,
	query,
	count(distinct job_id) as query_runs,
	sum(total_bytes_billed) as total_bytes_billed,
	SUM(total_bytes_billed)/1e12*5 costInDollars
	FROM
	`region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
	WHERE

markrittman / query_cost_by_user_by_month.sql

Created June 25, 2021 13:04

	```SELECT
	date_trunc(date(creation_time),MONTH) as billing_month,
	count(distinct job_id) as query_runs,
	sum(total_bytes_billed) as total_bytes_billed,
	SUM(total_bytes_billed)/1e12*5 costInDollars
	FROM
	`region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
	WHERE
	date_trunc(date(creation_time),MONTH) = '2021-04-01'
	AND job_type = "QUERY"

markrittman / dashboard_impact_heatmap.url

Created June 24, 2021 20:50

https://LOOKER_DOMAIN.looker.com/explore/system__activity/history?fields=history.dashboard_run_count,history.average_runtime,history.completed_week,dashboard.title&pivots=history.completed_week&fill_fields=history.completed_week&f[history.dashboard_session]=-NULL&f[history.completed_week]=4+weeks&f[dashboard.title]=-NULL&sorts=history.completed_week+desc,impact+desc+1&limit=500&vis=%7B%22show_view_names%22%3Afalse%2C%22show_row_numbers%22%3Atrue%2C%22transpose%22%3Afalse%2C%22truncate_text%22%3Atrue%2C%22hide_totals%22%3Afalse%2C%22hide_row_totals%22%3Afalse%2C%22size_to_fit%22%3Atrue%2C%22table_theme%22%3A%22white%22%2C%22limit_displayed_rows%22%3Afalse%2C%22enable_conditional_formatting%22%3Atrue%2C%22header_text_alignment%22%3A%22left%22%2C%22header_font_size%22%3A%2212%22%2C%22rows_font_size%22%3A%2212%22%2C%22conditional_formatting_include_totals%22%3Afalse%2C%22conditional_formatting_include_nulls%22%3Afalse%2C%22color_application%22%3A%7B%22collection_id%22%3A%22legacy%22%2C%22palette_id%22%3A%22looker

Mark Rittman markrittman