Return search keyword stats over time for blog, podcast and marketing pages in our Wordpress site
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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