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.
Data sourced from Ahrefs organic keywords endpoint.

== 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
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 = 
2

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::EventPublishable

#publish_event

Instance Attribute Details

#keyword ⇒ Object (readonly) 



49
50
 
# File 'app/models/seo_page_keyword.rb', line 49

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

Class Method Details

.at_risk ⇒ ActiveRecord::Relation<SeoPageKeyword>

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

Returns:

See Also:



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

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:



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

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

.by_traffic ⇒ ActiveRecord::Relation<SeoPageKeyword>

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

Returns:

See Also:



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

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:



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

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

.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



118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
 
# File 'app/models/seo_page_keyword.rb', line 118

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

.noise?(keyword) ⇒ Boolean

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

Returns:

  • (Boolean) 


58
59
60
61
62
63
64
 
# File 'app/models/seo_page_keyword.rb', line 58

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

.ranking ⇒ ActiveRecord::Relation<SeoPageKeyword>

A relation of SeoPageKeywords that are ranking. Active Record Scope

Returns:

See Also:



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

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

.ransackable_attributes(_auth_object = nil) ⇒ Object 



103
104
105
 
# File 'app/models/seo_page_keyword.rb', line 103

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) ⇒ Object

Includes virtual aggregate columns exposed by the keywords overview query.



108
109
110
 
# File 'app/models/seo_page_keyword.rb', line 108

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:



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

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

.with_ai_overview ⇒ ActiveRecord::Relation<SeoPageKeyword>

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

Returns:

See Also:



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

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:



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

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

Instance Method Details

#ai_overview_present? ⇒ Boolean

Returns:

  • (Boolean) 


89
90
91
 
# File 'app/models/seo_page_keyword.rb', line 89

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

#cannibalization_risk? ⇒ Boolean

Check if this keyword has cannibalization risk

Returns:

  • (Boolean) 


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

def cannibalization_risk?
  competing_pages.at_risk.exists?
end

#cited_in_ai_overview? ⇒ Boolean

Returns:

  • (Boolean) 


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

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:



74
75
76
77
78
79
80
81
 
# File 'app/models/seo_page_keyword.rb', line 74

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:

See Also:



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

belongs_to :site_map