Track Keyword Rankings in Postgres with BigQuery Data

### Automation Description This automation serves two main purposes: 1. It monitors keyword rankings for all keywords in your rank tracking table 2. It displays keywords where you already have favorable Google rankings in a separate table The ability clearly shows whether you rank in the top 3, top 10, or top 20 for specific keywords and URLs. Additionally, it displays rankings for both mobile and desktop devices. ### Required Tables To use this automation, you need to create the following tables: #### 1. Top Ranking Keywords This table is used to identify keyword opportunities. It helps you quickly find keywords where you already rank in the top 3, 10, or 20 positions. This information is particularly valuable for creating content that matches these search queries. sql CREATE TABLE IF NOT EXISTS public.top_ranking_keywords ( data_date date NOT NULL, query text COLLATE pg_catalog."default" NOT NULL, url text COLLATE pg_catalog."default" NOT NULL, device text COLLATE pg_catalog."default", result_type text COLLATE pg_catalog."default", total_impressions integer, total_clicks integer, avg_position double precision, ctr_percentage double precision, position_bucket text COLLATE pg_catalog."default", domain text COLLATE pg_catalog."default", CONSTRAINT ranking_primary_key PRIMARY KEY (data_date, query, url) ) #### 2. Tracked Keywords Add the keywords you want to monitor rankings for in this table. sql CREATE TABLE IF NOT EXISTS public.tracked_keywords ( domain text COLLATE pg_catalog."default" NOT NULL, keyword text COLLATE pg_catalog."default" NOT NULL, created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP, CONSTRAINT tracked_keywords_primary_key PRIMARY KEY (domain, keyword) ) #### 3. Rank Tracking by Keywords This table exclusively tracks the rankings of keywords listed in the tracked_keywords table. sql CREATE TABLE IF NOT EXISTS public.rank_tracking_by_keywords ( data_date date NOT NULL, url text COLLATE pg_catalog."default" NOT NULL, device text COLLATE pg_catalog."default", keyword text COLLATE pg_catalog."default" NOT NULL, clicks integer, impressions integer, "position" double precision, ctr double precision, domain text COLLATE pg_catalog."default", CONSTRAINT tracked_keyword_primary_key PRIMARY KEY (data_date, url, keyword) )