Skip to content

Instantly share code, notes, and snippets.

@markrittman

markrittman/blog_marketer_classifier.sql

Created August 2, 2024 23:56
Show Gist options
  • Save markrittman/7d2a906cba5d8fcbb652a76323dcb4fc to your computer and use it in GitHub Desktop.
Save markrittman/7d2a906cba5d8fcbb652a76323dcb4fc to your computer and use it in GitHub Desktop.
Download ZIP
Use VertexAI to turn blog posts into tweets, outbound marketing emails and classify each blog post by topic
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment