markrittman

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

!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

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

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

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

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

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

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

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

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,