Mark Rittman markrittman

Co-founder and technical lead at Rittman Analytics

markrittman / wordpress_posts.view.lkml

Created December 1, 2022 23:19

Example LookML view to create Bitnami Wordpress pages/posts dimension including type and category metadata

markrittman / scorecard.view.lkml

Created July 18, 2022 21:25

Example balanced scorecard calculation for a Looker dashboard

	view: kpi_scorecard {
	derived_table: {
	sql: with delivery_kpi_1 as (
	(
	SELECT
	timestamp(date_trunc(contact_utilization_fact.forecast_week,MONTH)) as kpi_month,
	'Utilization' as kpi_name,
	max(0.5) as category_weighting_pct,
	COALESCE(SUM(contact_utilization_fact.actual_billable_hours ),
	0) / COALESCE(SUM(contact_utilization_fact.target_billable_capacity ),

markrittman / user_journey_with_inferred_identity.sql

Created May 29, 2022 23:41

User journey stitching with inferred identify for off-platform activity

	with events as (
	select
	anonymous_id,
	user_id,
	cast(null as string) as name,
	cast(null as string) as email,
	timestamp,
	'page_view' as event_type,
	concat(split(context_ip,'.')[safe_offset(0)],'.*.*.',split(context_ip,'.')[safe_offset(3)]) as context_ip,
	context_page_path,

markrittman / ten_next_pageviews_after_landing_page.sql

Created May 29, 2022 22:43

Return first ten page views after landing on a particular page using Segment

	with events as (
	select
	anonymous_id,
	user_id,
	cast(null as string) as name,
	cast(null as string) as email,
	timestamp,
	'page_view' as event_type,
	concat(split(context_ip,'.')[safe_offset(0)],'.*.*.',split(context_ip,'.')[safe_offset(3)]) as context_ip,
	context_page_path,

markrittman / landing_page_user_paths.sql

Last active October 4, 2024 02:01

Most common user paths from a given landing page

	with events as (
	select
	anonymous_id,
	user_id,
	cast(null as string) as name,
	cast(null as string) as email,
	timestamp,
	'page_view' as event_type,
	concat(split(context_ip,'.')[safe_offset(0)],'.*.*.',split(context_ip,'.')[safe_offset(3)]) as context_ip,
	context_page_path,

markrittman / segment_consideration_stage_journey.sql

Created May 29, 2022 13:28

Segment Events unioned with Identify Event, ID Stitched, User Details Backfilled and Events Grouped into User Consideration-Stage Journey

	with events as (
	select
	anonymous_id,
	user_id,
	cast(null as string) as name,
	cast(null as string) as email,
	timestamp,
	'page_view' as event_type,
	concat(split(context_ip,'.')[safe_offset(0)],'.*.*.',split(context_ip,'.')[safe_offset(3)]) as context_ip,
	context_page_path,

markrittman / segment_inferred_identity_stitching.sql

Created May 23, 2022 20:50

Example BigQuery SQL statement to stitch user identities together across multiple Segment and other sources based on inferred connections

	with
	meeting_bookings as # meetings have names and email addresses, but no anonymous_id
	(
	select
	# union of three events that are logically the same but were recorded as three different events historically
	id,
	timestamp,
	email as anonymous_id,
	cast(null as string) as context_ip,
	cast(null as string) as context_page_path,

markrittman / snowflake_dbt_setup.sql

Created March 2, 2022 17:24

Setup Script for Snowflake DW for use with dbt

	use role accountadmin;

	create or replace role loader;
	create or replace role transformer;
	create or replace role reporter;
	create or replace role looker_role;
	grant role loader to role sysadmin;
	grant role transformer to role sysadmin;
	grant role reporter to role sysadmin;
	grant role looker_role to role sysadmin;

markrittman / bigquery_monthy_query_costs.sql

Created February 21, 2022 20:14

BigQuery Listing of Current Months' Queries Ordered by Cost, with Running Totals for Cost and % of All Cost

	with
	query_cost as (
	select
	date_trunc(date(creation_time),month) as billing_month,
	user_email,
	query,
	job_type,
	project_id,
	priority,
	concat(destination_table.dataset_id,'.',destination_table.table_id) as destination_table,

markrittman / snowplow_multi_touch_attribution.sql

Last active November 5, 2021 00:41

First, Last, Even-Click and Time-Decay Multi-Touch Attribution Model for Snowplow (BigQuery Standard SQL)

	WITH
	events AS (
	SELECT
	events_true_tstamp_time as event_ts,
	events_user_fingerprint AS user_pseudo_id,
	events_user_id as user_id,
	mkt_network as channel,
	events_mkt_medium as medium,
	events_mkt_source as source,
	events_mkt_campaign as campaign,