Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
FROM (
SELECT
id,
post_title,
Post_Date,
Post_Name,
post_type,
post_status,
post_excerpt,
comment_count
FROM (
SELECT
*,
post_modified = MAX(post_modified) OVER (PARTITION BY CAST(p.id AS int64) ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS is_last_modified_ts
FROM
bitnami_wordpress.wp_posts p )
WHERE
is_last_modified_ts
GROUP BY
1,2,3,4,5,6,7,8) p
JOIN
bitnami_wordpress.wp_term_relationships tr
ON
p.ID = tr.object_id
JOIN
bitnami_wordpress.wp_terms t
ON
tr.term_taxonomy_id = t.term_id
JOIN
bitnami_wordpress.wp_term_taxonomy tx
ON
tx.term_id = t.term_id
WHERE
p.post_type = 'post'
AND p.post_status = 'publish'
AND tx.taxonomy = 'category' )
GROUP BY
1,2,3,4,5,6,7
UNION ALL
select p.post_title,
'Marketing' as post_type,
initcap(post_type) as category,
p.post_date,
p.post_name,
p.comment_count,
p.post_excerpt
from bitnami_wordpress.wp_posts p
where p.post_type in ('page','technology','industry','offer','service') and p.post_status = 'publish'
group by 1,2,3,4,5,6,7
)
, google_search_console_weekly_stats AS (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
AS page_name,
query AS search_keywords,
DATE_TRUNC(date, WEEK) AS search_week,
SUM(clicks) AS total_clicks,
SUM(impressions) AS total_impressions,
AVG(ctr) AS avg_ctr,
AVG(position) AS avg_position
FROM
`ra-development.fivetran_google_search_console.keyword_page_report`
GROUP BY
1,2,3)
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY search_week ORDER BY total_clicks DESC) AS weekly_query_clicks_rank
FROM
page_keyword_performance)
SELECT
wordpress_posts.Post_Name AS wordpress_posts_page_name,
google_search_console_weekly_stats.search_keywords AS google_search_console_weekly_stats_search_keywords,
wordpress_posts.post_type AS wordpress_posts_page_type,
wordpress_posts.Category AS wordpress_posts_page_category,
COALESCE(SUM(google_search_console_weekly_stats.total_clicks ), 0) AS google_search_console_weekly_stats_total_clicks,
COALESCE(SUM(google_search_console_weekly_stats.total_impressions ), 0) AS google_search_console_weekly_stats_total_impressions,
AVG(google_search_console_weekly_stats.avg_position ) AS google_search_console_weekly_stats_avg_position,
AVG(google_search_console_weekly_stats.avg_ctr ) AS google_search_console_weekly_stats_avg_ctr
FROM wordpress_posts
LEFT JOIN google_search_console_weekly_stats ON wordpress_posts.Post_Name = google_search_console_weekly_stats.page_name
GROUP BY
1,2,3,4
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment