Skip to content

Instantly share code, notes, and snippets.

View markrittman's full-sized avatar

Mark Rittman markrittman

View GitHub Profile
@markrittman
markrittman / blog_marketer_classifier.sql
Created August 2, 2024 23:56
Use VertexAI to turn blog posts into tweets, outbound marketing emails and classify each blog post by topic
WITH
social_media_posts AS (
SELECT
page_title,
page_url,
page_content,
CONCAT(REPLACE(ml_generate_text_llm_result,' [link to blog post] ',''),' ',page_url) AS social_post
FROM
ML.GENERATE_TEXT( MODEL `analytics_ai.gemini_1_5_flash`,
(
@markrittman
markrittman / scrape_squarespace_blogs.py
Created August 2, 2024 23:48
Scrape Squarespace blog posts and save to CSV file
!pip install requests beautifulsoup4
import sys
import subprocess
import time
import random
# Install required packages
def install(package):
subprocess.check_call([sys.executable, "-m", "pip", "install", package])
@markrittman
markrittman / compare_segment_rudderstack_page_views_by_day.sql
Created May 21, 2023 18:13
Script to compare page view counts for Segment, Rudderstack and GA4 tracking, grouped by day
with segment_pageviews as (
select
timestamp_trunc(p.received_at,day) as day_day,
context_page_title as page_title,
count(distinct id) as page_views
from
`ra-development.company_website.pages_view` p
where
date(timestamp_trunc(p.received_at,DAY)) > '2023-05-01'
and context_page_url not like '%info.rittmananalytics.com%'
@markrittman
markrittman / compare_segment_rudderstack_ga4_page_views_by_page_title.sql
Last active May 21, 2023 18:10
Script to show total page views for all pages on our site as recorded by Segment, Rudderstack and GA4
with segment_pageviews as (
select
timestamp_trunc(p.received_at,day) as day_day,
context_page_title as page_title,
count(distinct id) as page_views
from
`ra-development.company_website.pages_view` p
where
date(timestamp_trunc(p.received_at,DAY)) > '2023-05-01'
and context_page_url not like '%info.rittmananalytics.com%'
@markrittman
markrittman / compare_rudderstack_segment_page_events_for_given_page_title.sql
Created May 21, 2023 15:12
Script to compare the page views logged by Segment and Rudderstack for the same page, used when the page view counts for the two sources differ
with segment_pageviews as (
select
'segment' as source, id, timestamp_trunc(timestamp,HOUR) as timestamp_minute, path, context_page_title, anonymous_id, user_id, context_ip
from
`ra-development.company_website.pages_view` p
where
date(timestamp_trunc(p.received_at,DAY)) > '2023-05-10'
and context_page_title = 'Adding Looker Regression Tests to the dbtCloud CI/CD Pipeline using Spectacles — Rittman Analytics'
@markrittman
markrittman / ga4_attribution.sql
Created May 9, 2023 10:37
GA4 Multi-Step, Multi-Cycle Marketing Attribution example using the BigQuery GA4 Sample Dataset at https://developers.google.com/analytics/bigquery/web-ecommerce-demo-dataset
WITH
events AS (
SELECT
TIMESTAMP_MICROS(event_timestamp) AS event_ts,
CONCAT(user_pseudo_id,'-',event_name,'-',CAST(event_timestamp AS STRING)) AS event_id,
user_pseudo_id AS user_pseudo_id,
user_id,
traffic_source.name AS utm_channel,
traffic_source.medium AS utm_medium,
traffic_source.source AS utm_source,
@markrittman
markrittman / wordpress_search_keyword_stats.sql
Created December 3, 2022 20:37
Return search keyword stats over time for blog, podcast and marketing pages in our Wordpress site
WITH wordpress_posts AS (SELECT * FROM
(
SELECT
p.post_title AS Post_Title,
'Content' AS post_type,
t.name AS Category,
p.post_date AS Post_Date,
p.post_name AS Post_Name,
p.comment_count AS comment_count,
p.post_excerpt
@markrittman
markrittman / google_search_console_weekly_stats.view.lkml
Created December 2, 2022 20:31
LookML view containing Google Search Console keyword page search stats data, formatted to join to Wordpress wp_posts.post_name column
view: google_search_console_weekly_stats {
derived_table: {
sql: WITH
page_keyword_performance AS (
SELECT
CASE
WHEN page = 'https://rittmananalytics.com/' THEN 'home'
ELSE
COALESCE(LOWER(ARRAY_REVERSE( SPLIT(RTRIM(page,'/'),'/'))[SAFE_OFFSET(0)]),'home')
END
@markrittman
markrittman / rudderstack_weekly_page_stats.view.lkml
Created December 2, 2022 20:24
LookML view that aggregates Rudderstack page traffic data and formats the page URL for joining to Wordpress wp_posts.post_name column
view: rudderstack_weekly_page_stats {
derived_table: {
sql: SELECT
title,
CASE
WHEN path = '/' THEN 'home'
ELSE
COALESCE(LOWER(ARRAY_REVERSE( SPLIT(RTRIM(path,'/'),'/'))[SAFE_OFFSET(0)]),'home')
END
AS page_name,
@markrittman
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
view: wordpress_posts {
derived_table: {
sql: SELECT * FROM
(
SELECT
p.post_title AS Post_Title,
'Content' AS post_type,
t.name AS Category,
p.post_date AS Post_Date,
p.post_name AS Post_Name,