Use VertexAI to turn blog posts into tweets, outbound marketing emails and classify each blog post by topic

blog_marketer_classifier.sql

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`,
      (
      SELECT
        page_title,
        page_url,
        page_content,
        CONCAT('Transform this blog post into a compelling Tweet that conveys the main highlights of the brand. Blog post is: ',web_content.page_content) AS prompt
      FROM (
        SELECT
          page_title,
          page_url,
          page_content
        FROM
          analytics_seed.website_page_content ) web_content),
      STRUCT( 0.5 AS temperature,
        1024 AS max_output_tokens,
        TRUE AS flatten_json_output))),
  marketing_emails AS (
  SELECT
    page_title,
    page_url,
    page_content,
    CONCAT(REPLACE(ml_generate_text_llm_result,' [link to blog post] ',''),'. You can read more at <a href="',page_url,'">',page_title,'</a>.') AS marketing_email
  FROM
    ML.GENERATE_TEXT( MODEL `analytics_ai.gemini_1_5_flash`,
      (
      SELECT
        page_title,
        page_url,
        page_content,
        CONCAT('Transform this blog post into a professional-sounding outbound marketing email to promote this service, case study or blog post: ',web_content.page_content) AS prompt
      FROM (
        SELECT
          page_title,
          page_url,
          page_content
        FROM
          analytics_seed.website_page_content  ) web_content),
      STRUCT( 0.5 AS temperature,
        1024 AS max_output_tokens,
        TRUE AS flatten_json_output)) ),
  topic AS (
  SELECT
    page_title,
    page_url,
    page_content,
    ml_generate_text_llm_result AS topic
  FROM
    ML.GENERATE_TEXT( MODEL `analytics_ai.gemini_1_5_flash`,
      (
      SELECT
        page_title,
        page_url,
        page_content,
        CONCAT('Classify the content and topics of this blog post as JUST ONE one of either "Data Centralization","Looker BI & Data Analytics","Marketing Analytics","Customer Data Platforms","Marketing Attribution","Product Analytics","Generative AI","Data Strategy","Data Modeling","Rittman Analytics Company Update" and reply with just the categorization and no commentary, markdown or other decoration: ',web_content.page_content) AS prompt
      FROM (
        SELECT
          page_title,
          page_url,
          page_content
        FROM
          analytics_seed.website_page_content  ) web_content),
      STRUCT( 0.5 AS temperature,
        1024 AS max_output_tokens,
        TRUE AS flatten_json_output)) ),
  landing_page_value as (
WITH web_events_fact AS (select *,              
              count(distinct case when event_type = 'Page View' then web_events_pk end) over (partition by replace(page_title,'—','-')) as total_page_views,
              count(distinct blended_user_id) over (partition by replace(page_title,'—','-')) as total_unique_viewers
       from `analytics.web_events_fact`
       where date(event_ts) > date_sub(current_date(), interval 90 day)),
      metrics as (
SELECT
    web_sessions_fact.first_page_url  AS page_url,
    
    COUNT(DISTINCT web_sessions_fact.web_sessions_pk ) AS total_sessions,
    SAFE_DIVIDE(COUNT(DISTINCT(case when  web_sessions_fact.is_bounced_session   = TRUE then  web_sessions_fact.web_sessions_pk   end)),( COUNT(DISTINCT web_sessions_fact.web_sessions_pk ) ))   AS web_sessions_fact_bounced_session_rate,
    COALESCE(SUM(( case when (web_events_fact.event_type = 'Meeting Booked') then 16 else safe_cast(split(web_events_fact.page_category,":")[SAFE_OFFSET(0)] as int64) end)), 0) as total_session_value,
    COALESCE(SUM(( case when (web_events_fact.event_type = 'Meeting Booked') then 16 else safe_cast(split(web_events_fact.page_category,":")[SAFE_OFFSET(0)] as int64) end)), 0)/COUNT(DISTINCT web_sessions_fact.web_sessions_pk ) AS avg_session_value,
    COUNT(DISTINCT CASE WHEN (( web_events_fact.page_category ) = '04: Marketing') THEN case when web_events_fact.event_type = 'Page View' then web_events_fact.web_events_pk end  ELSE NULL END)/COUNT(DISTINCT web_sessions_fact.web_sessions_pk ) AS pct_sessions_with_marketing_page_views,
    COUNT(DISTINCT CASE WHEN (( web_events_fact.page_category ) = '08: Services') THEN case when web_events_fact.event_type = 'Page View' then web_events_fact.web_events_pk end  ELSE NULL END)/COUNT(DISTINCT web_sessions_fact.web_sessions_pk ) AS pct_sessions_with_services_page_views,
    COUNT(DISTINCT CASE WHEN (( web_events_fact.page_category ) = '06: Case Study') THEN case when web_events_fact.event_type = 'Page View' then web_events_fact.web_events_pk end  ELSE NULL END)/COUNT(DISTINCT web_sessions_fact.web_sessions_pk )  AS pct_sessions_with_case_study_page_views,
    
FROM `analytics.web_sessions_fact`
     AS web_sessions_fact
LEFT JOIN web_events_fact ON web_sessions_fact.session_id = web_events_fact.session_id
where date(web_sessions_fact.session_start_ts) > date_sub(current_date(), interval 90 day)
GROUP BY
    1)
SELECT 
  *
from metrics  

  )
SELECT
  s.page_title,
  s.page_url,
  s.page_content,
  s.social_post,
  m.marketing_email,
  t.topic,
  concat("drives ",case when (pct_sessions_with_marketing_page_views > pct_sessions_with_services_page_views) and (pct_sessions_with_marketing_page_views > pct_sessions_with_case_study_page_views) then 'marketing'
       when (pct_sessions_with_services_page_views > pct_sessions_with_marketing_page_views) and (pct_sessions_with_services_page_views > pct_sessions_with_case_study_page_views) then 'services'
       when (pct_sessions_with_case_study_page_views > pct_sessions_with_services_page_views) and (pct_sessions_with_case_study_page_views > pct_sessions_with_marketing_page_views) then 'case studies'
       else 'social' end," page views") as purpose,
  l.avg_session_value
FROM
  social_media_posts s
JOIN
  marketing_emails m
ON
  s.page_url = m.page_url
JOIN
  topic t
ON
  s.page_url = t.page_url
JOIN
  landing_page_value l
ON
  s.page_url = l.page_url