Return search keyword stats over time for blog, podcast and marketing pages in our Wordpress site

wordpress_search_keyword_stats.sql

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