Class: SeoPageKeyword

Inherits:
ApplicationRecord show all
Defined in:
app/models/seo_page_keyword.rb

Overview

Keywords that a page ranks for, used for cannibalization detection.
Organic data sourced from Google Search Console (primary) with Ahrefs as
fallback; Google Ads search terms enrich the paid_* columns, and staff can
add keywords manually.

Organic lifecycle: every organic sync stamps organic_source and
organic_last_seen_at on the keywords it returns. Keywords that stop
appearing are NOT deleted — once they have been unobserved for STALE_AFTER
they are marked position: nil (see .mark_stale_for_site_map), which drops
them from the ranking scope while preserving paid metrics and manual
keyword_target curation. The 100-keyword API cap means a single absence
proves nothing, hence the grace window rather than immediate expiry.

== Schema Information

Table name: seo_page_keywords
Database name: primary

id :bigint not null, primary key
best_position_kind :string
keyword :string not null
keyword_target :enum
organic_last_seen_at :timestamptz
organic_source :string
paid_clicks :integer
paid_conversions :decimal(10, 2)
paid_cost_micros :bigint
paid_cpc_micros :bigint
paid_impressions :integer
position :integer
search_volume :integer
serp_features :text is an Array
snapshot_date :date
traffic_share :integer
created_at :datetime not null
updated_at :datetime not null
site_map_id :bigint not null

Indexes

idx_seo_keywords_site_map_keyword (site_map_id,keyword) UNIQUE
index_seo_page_keywords_on_keyword (keyword)

Foreign Keys

fk_rails_... (site_map_id => site_maps.id)

Examples:

Find pages cannibalizing for a keyword

SeoPageKeyword.where(keyword: 'heated floors')
              .where(position: 1..30)
              .includes(:site_map)

Constant Summary collapse

MIN_KEYWORD_LENGTH =

Minimum keyword length.

2
ORGANIC_SOURCES =

Organic sync sources that stamp organic_last_seen_at.

%w[gsc ahrefs].freeze
STALE_AFTER =

Grace period before an unobserved organic ranking is marked stale
(position -> nil). Must comfortably exceed the slowest sync cadence —
low-traffic pages re-sync every 30 days (SeoBatchCollectorWorker tiers) —
because the APIs cap responses at 100 keywords, so one absence proves
nothing. 60 days ≈ 2 low-tier cycles / 8 high-tier cycles.

60.days

Constants included from Models::Schedulable

Models::Schedulable::SIMPLE_FORM_OPTIONS

Instance Attribute Summary collapse

Belongs to collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from ApplicationRecord

ransackable_associations, ransackable_scopes, #to_relation

Methods included from Models::Schedulable

config

Methods included from Models::AfterCommittable

#after_commit

Methods included from Models::EventPublishable

#publish_event

Instance Attribute Details

#keyword ⇒ String (readonly)

Returns:

  • (String) 


74
75
 
# File 'app/models/seo_page_keyword.rb', line 74

validates :keyword, presence: true,
uniqueness: { scope: :site_map_id }

#organic_source ⇒ String (readonly)

Returns:

  • (String) 


77
 
# File 'app/models/seo_page_keyword.rb', line 77

validates :organic_source, inclusion: { in: ORGANIC_SOURCES }, allow_nil: true

Class Method Details

.at_risk ⇒ ActiveRecord::Relation<SeoPageKeyword>

A relation of SeoPageKeywords that are at risk. Active Record Scope

Returns:

See Also:



151
 
# File 'app/models/seo_page_keyword.rb', line 151

scope :at_risk, -> { where(position: 5..20) }

.by_position ⇒ ActiveRecord::Relation<SeoPageKeyword>

A relation of SeoPageKeywords that are by position. Active Record Scope

Returns:

See Also:



153
 
# File 'app/models/seo_page_keyword.rb', line 153

scope :by_position, -> { order(:position) }

.by_traffic ⇒ ActiveRecord::Relation<SeoPageKeyword>

A relation of SeoPageKeywords that are by traffic. Active Record Scope

Returns:

See Also:



152
 
# File 'app/models/seo_page_keyword.rb', line 152

scope :by_traffic, -> { order(traffic_share: :desc) }

.cited_in_ai_overview ⇒ ActiveRecord::Relation<SeoPageKeyword>

A relation of SeoPageKeywords that are cited in ai overview. Active Record Scope

Returns:

See Also:



82
 
# File 'app/models/seo_page_keyword.rb', line 82

scope :cited_in_ai_overview, -> { where(best_position_kind: %w[ai_overview ai_overview_sitelink]) }

.current_organic ⇒ ActiveRecord::Relation<SeoPageKeyword>

A relation of SeoPageKeywords that are current organic. Active Record Scope

Returns:

See Also:



100
 
# File 'app/models/seo_page_keyword.rb', line 100

scope :current_organic, -> { ranking.on_visible_site_maps.well_formed }

.keywords_with_cannibalization_risk(keywords) ⇒ Set<String>

Batch-check cannibalization risks for multiple keywords.
Returns a Set of keyword strings that have cannibalization risk.
Only considers pages in the same locale — geo-targeted variants (en-US vs en-CA)
are not real competitors because Google treats them as separate audiences.

Parameters:

Returns:

  • (Set<String>) —

    Keywords with cannibalization risk



213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
 
# File 'app/models/seo_page_keyword.rb', line 213

def self.keywords_with_cannibalization_risk(keywords)
  return Set.new if keywords.blank?

  site_map_id = keywords.first.site_map_id
  keyword_strings = keywords.map(&:keyword)
  locale = SiteMap.where(id: site_map_id).pick(:locale)

  same_locale_ids = SiteMap.active.where(locale: locale).select(:id)

  competing_keywords = SeoPageKeyword
                       .where(keyword: keyword_strings)
                       .where.not(site_map_id: site_map_id)
                       .where(site_map_id: same_locale_ids)
                       .ranking
                       .at_risk
                       .distinct
                       .pluck(:keyword)

  Set.new(competing_keywords)
end

.mark_stale_for_site_map(site_map, stale_before: STALE_AFTER.ago) ⇒ Integer, void

Marks this page's keywords that no organic sync has observed within
STALE_AFTER as no longer ranking (position -> nil). Rows are never
deleted: paid metrics and manual keyword_target curation survive.
organic_last_seen_at falls back to snapshot_date for rows written before
provenance existed (and a NULL pair — manual/paid-only rows — never
matches the cutoff, so those are never touched). Absence is only trusted
after the grace window; a sync that simply failed leaves rows alone
because callers invoke this only after a successful save.

Parameters:

  • site_map (Object)
  • stale_before (Object) (defaults to: STALE_AFTER.ago)

Returns:

  • (Integer) —

    rows marked stale

  • (void) 


115
116
117
118
119
120
 
# File 'app/models/seo_page_keyword.rb', line 115

def self.mark_stale_for_site_map(site_map, stale_before: STALE_AFTER.ago)
  site_map.seo_page_keywords
          .where.not(position: nil)
          .where('COALESCE(organic_last_seen_at, snapshot_date::timestamptz) < ?', stale_before)
          .update_all(position: nil, updated_at: Time.current)
end

.noise?(keyword) ⇒ Boolean

Returns true if the keyword is likely noise (too short or contains no letters).

Parameters:

  • keyword (Object)

Returns:

  • (Boolean) 


143
144
145
146
147
148
149
 
# File 'app/models/seo_page_keyword.rb', line 143

def self.noise?(keyword)
  return true if keyword.blank?
  return true if keyword.length < MIN_KEYWORD_LENGTH
  return true unless keyword.match?(/\p{L}/)

  false
end

.on_visible_site_maps ⇒ ActiveRecord::Relation<SeoPageKeyword>

A relation of SeoPageKeywords that are on visible site maps. Active Record Scope

Returns:

See Also:



85
 
# File 'app/models/seo_page_keyword.rb', line 85

scope :on_visible_site_maps, -> { joins(:site_map).merge(SiteMap.active.where(hide: false)) }

.ranking ⇒ ActiveRecord::Relation<SeoPageKeyword>

A relation of SeoPageKeywords that are ranking. Active Record Scope

Returns:

See Also:



79
 
# File 'app/models/seo_page_keyword.rb', line 79

scope :ranking, -> { where.not(position: nil).where(position: 1..100) }

.ranking_keyword_suggestions(term) ⇒ void

This method returns an undefined value.

Distinct ranking keywords for the CRM filter. Empty lookups are
alphabetical; typed lookups use pg_trgm distance so exact and closest
matches appear first. Scoped to current organic rankings on live pages.

Parameters:

  • term (Object) 


127
128
129
130
131
132
133
134
135
136
137
138
 
# File 'app/models/seo_page_keyword.rb', line 127

def self.ranking_keyword_suggestions(term)
  term = term.to_s.strip
  suggestions = current_organic.select(:keyword).distinct
  return suggestions.order(:keyword) if term.blank?

  distance = sanitize_sql_for_order([Arel.sql('seo_page_keywords.keyword <-> ?'), term])

  suggestions
    .where('seo_page_keywords.keyword ILIKE ?', "%#{sanitize_sql_like(term)}%")
    .select(distance.as('distance'))
    .order(distance, :keyword)
end

.ransackable_attributes(_auth_object = nil) ⇒ void

Parameters:

  • _auth_object (Object) (defaults to: nil)
  • _auth_object (Object) (defaults to: nil)

Returns:

  • (void)
  • (void) 


196
197
198
 
# File 'app/models/seo_page_keyword.rb', line 196

def self.ransackable_attributes(_auth_object = nil)
  %w[keyword position search_volume traffic_share snapshot_date best_position_kind]
end

.ransortable_attributes(_auth_object = nil) ⇒ void

This method returns an undefined value.

Includes virtual aggregate columns exposed by the keywords overview query.

Parameters:

  • _auth_object (Object) (defaults to: nil) 


203
204
205
 
# File 'app/models/seo_page_keyword.rb', line 203

def self.ransortable_attributes(_auth_object = nil)
  %w[keyword best_position search_volume page_count locale_count last_updated]
end

.top_positions ⇒ ActiveRecord::Relation<SeoPageKeyword>

A relation of SeoPageKeywords that are top positions. Active Record Scope

Returns:

See Also:



150
 
# File 'app/models/seo_page_keyword.rb', line 150

scope :top_positions, -> { where(position: 1..10) }

.well_formed ⇒ ActiveRecord::Relation<SeoPageKeyword>

A relation of SeoPageKeywords that are well formed. Active Record Scope

Returns:

See Also:



93
94
95
96
97
 
# File 'app/models/seo_page_keyword.rb', line 93

scope :well_formed, -> {
  where('length(btrim(seo_page_keywords.keyword)) >= ?', MIN_KEYWORD_LENGTH)
    .where("seo_page_keywords.keyword ~ '[[:alpha:]]'")
    .where("seo_page_keywords.keyword !~ '[\\x00-\\x1F]'")
}

.with_ai_overview ⇒ ActiveRecord::Relation<SeoPageKeyword>

A relation of SeoPageKeywords that are with ai overview. Active Record Scope

Returns:

See Also:



81
 
# File 'app/models/seo_page_keyword.rb', line 81

scope :with_ai_overview, -> { where("'ai_overview' = ANY(serp_features)") }

.with_volume ⇒ ActiveRecord::Relation<SeoPageKeyword>

A relation of SeoPageKeywords that are with volume. Active Record Scope

Returns:

See Also:



80
 
# File 'app/models/seo_page_keyword.rb', line 80

scope :with_volume, -> { where.not(search_volume: nil).where('search_volume > 0') }

Instance Method Details

#ai_overview_present? ⇒ Boolean

Returns:

  • (Boolean) 


175
176
177
 
# File 'app/models/seo_page_keyword.rb', line 175

def ai_overview_present?
  serp_features&.include?('ai_overview')
end

#cannibalization_risk? ⇒ Boolean

Check if this keyword has cannibalization risk

Returns:

  • (Boolean) 


170
171
172
 
# File 'app/models/seo_page_keyword.rb', line 170

def cannibalization_risk?
  competing_pages.at_risk.exists?
end

#cited_in_ai_overview? ⇒ Boolean

Returns:

  • (Boolean) 


180
181
182
 
# File 'app/models/seo_page_keyword.rb', line 180

def cited_in_ai_overview?
  best_position_kind.in?(%w[ai_overview ai_overview_sitelink])
end

#competing_pages ⇒ ActiveRecord::Relation<SeoPageKeyword>

Find other pages ranking for the same keyword (for cannibalization).
Scoped to the same locale so geo-targeted variants (en-US vs en-CA) are not
flagged as competitors — Google treats them as separate geo audiences.

Returns:



159
160
161
162
163
164
165
166
 
# File 'app/models/seo_page_keyword.rb', line 159

def competing_pages
  same_locale_ids = SiteMap.active.where(locale: site_map.locale).select(:id)
  SeoPageKeyword.where(keyword: keyword)
                .where.not(site_map_id: site_map_id)
                .where(site_map_id: same_locale_ids)
                .ranking
                .includes(:site_map)
end

#site_map ⇒ SiteMap?

Returns:



56
 
# File 'app/models/seo_page_keyword.rb', line 56

belongs_to :site_map