Class: SiteMapDataPoint

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

Overview

== Schema Information

Table name: site_map_data_points
Database name: primary

id :bigint not null, primary key
metric_type :enum not null
period :daterange not null
recorded_at :datetime not null
reference :string
value :decimal(15, 4) not null
created_at :datetime not null
updated_at :datetime not null
site_map_id :bigint not null
source_batch_id :uuid

Indexes

idx_data_points_batch (source_batch_id) WHERE (source_batch_id IS NOT NULL)
idx_data_points_lookup (site_map_id,metric_type,period) USING gist
idx_data_points_metric_period (metric_type,period) USING gist
idx_data_points_reference (site_map_id,metric_type,reference) WHERE (reference IS NOT NULL)
idx_data_points_unique_metric_period (site_map_id, metric_type, period, COALESCE(reference, ''::character varying)) UNIQUE

Foreign Keys

fk_rails_... (site_map_id => site_maps.id) ON DELETE => cascade

Constant Summary collapse

GSC_METRICS =

Metric type categories for grouping

%w[gsc_clicks gsc_impressions gsc_ctr gsc_avg_position].freeze
GA4_METRICS =

Ga4 metrics.

%w[ga4_page_views ga4_sessions ga4_users ga4_bounce_rate
ga4_engagement_rate ga4_avg_session_duration].freeze
AHREFS_METRICS =

Ahrefs metrics.

%w[ahrefs_traffic ahrefs_keywords_count ahrefs_traffic_value
ahrefs_domain_rating].freeze
ADS_METRICS =

Ads metrics.

%w[ads_clicks ads_impressions ads_cost ads_conversions
ads_conversion_value].freeze
KEYWORD_METRICS =

Keyword metrics.

%w[keyword_position keyword_search_volume keyword_traffic_share].freeze
CLOUDFLARE_METRICS =

Cloudflare edge-analytics metrics (every request at the edge — bots, cache hits, JS-disabled).

%w[cloudflare_requests].freeze
INVERTED_METRICS =

Metrics where lower is better (for trend interpretation)

%w[ga4_bounce_rate gsc_avg_position keyword_position].freeze

Constants included from Schedulable

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_attributes, ransackable_scopes, ransortable_attributes, #to_relation

Methods included from Schedulable

config

Methods included from Models::AfterCommittable

#after_commit

Methods included from Models::EventPublishable

#publish_event

Instance Attribute Details

#metric_type ⇒ Object (readonly)

Validations

Validations:



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

validates :metric_type, presence: true

#period ⇒ Object (readonly) 



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

validates :period, presence: true

#reference ⇒ Object (readonly) 



57
 
# File 'app/models/site_map_data_point.rb', line 57

validates :reference, presence: true, if: :keyword_metric?

#value ⇒ Object (readonly) 



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

validates :value, presence: true, numericality: true

Class Method Details

.bulk_record!(site_map:, metrics:, period_start:, period_end:, batch_id: nil, reference: nil) ⇒ Object

Bulk record multiple metrics from a sync (upserts to prevent duplicates)

Parameters:

  • site_map (SiteMap)
  • metrics (Hash) —

    { metric_type => value }

  • period_start (Date)
  • period_end (Date)
  • batch_id (String) (defaults to: nil)
  • reference (String, nil) (defaults to: nil) —

    Context for keyword metrics



194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
 
# File 'app/models/site_map_data_point.rb', line 194

def bulk_record!(site_map:, metrics:, period_start:, period_end:, batch_id: nil, reference: nil)
  batch_id ||= SecureRandom.uuid
  now = Time.current
  period_str = "[#{period_start},#{period_end}]"

  records = metrics.filter_map do |metric_type, value|
    next if value.nil?

    {
      site_map_id: site_map.id,
      metric_type: metric_type.to_s,
      value: value,
      period: period_str,
      reference: reference,
      source_batch_id: batch_id,
      recorded_at: now
    }
  end

  return if records.empty?

  # Use raw SQL for upsert since we have a functional index with COALESCE
  values = records.map do |r|
    sanitize_sql_array([
                         "(?, ?, ?, ?::daterange, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)",
                         r[:site_map_id], r[:metric_type], r[:value], r[:period],
                         r[:reference], r[:source_batch_id], r[:recorded_at]
                       ])
  end.join(', ')

  lease_connection.execute(<<~SQL.squish)
    INSERT INTO site_map_data_points
      (site_map_id, metric_type, value, period, reference, source_batch_id, recorded_at, created_at, updated_at)
    VALUES #{values}
    ON CONFLICT (site_map_id, metric_type, period, COALESCE(reference, ''))
    DO UPDATE SET
      value = EXCLUDED.value,
      source_batch_id = EXCLUDED.source_batch_id,
      recorded_at = EXCLUDED.recorded_at,
      updated_at = CURRENT_TIMESTAMP
  SQL
end

.by_period_start ⇒ ActiveRecord::Relation<SiteMapDataPoint>

A relation of SiteMapDataPoints that are by period start. Active Record Scope

Returns:

See Also:



64
 
# File 'app/models/site_map_data_point.rb', line 64

scope :by_period_start, -> { order(Arel.sql("lower(period) ASC")) }

.by_recorded ⇒ ActiveRecord::Relation<SiteMapDataPoint>

A relation of SiteMapDataPoints that are by recorded. Active Record Scope

Returns:

See Also:



63
 
# File 'app/models/site_map_data_point.rb', line 63

scope :by_recorded, -> { order(recorded_at: :desc) }

.containing_date ⇒ ActiveRecord::Relation<SiteMapDataPoint>

A relation of SiteMapDataPoints that are containing date. Active Record Scope

Returns:

See Also:



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

scope :containing_date, ->(date) { where("period @> ?::date", date) }

.for_metric ⇒ ActiveRecord::Relation<SiteMapDataPoint>

A relation of SiteMapDataPoints that are for metric. Active Record Scope

Returns:

See Also:



60
 
# File 'app/models/site_map_data_point.rb', line 60

scope :for_metric, ->(type) { where(metric_type: type) }

.for_reference ⇒ ActiveRecord::Relation<SiteMapDataPoint>

A relation of SiteMapDataPoints that are for reference. Active Record Scope

Returns:

See Also:



61
 
# File 'app/models/site_map_data_point.rb', line 61

scope :for_reference, ->(ref) { where(reference: ref) }

.latest ⇒ ActiveRecord::Relation<SiteMapDataPoint>

A relation of SiteMapDataPoints that are latest. Active Record Scope

Returns:

See Also:



76
 
# File 'app/models/site_map_data_point.rb', line 76

scope :latest, -> { by_recorded.limit(1) }

.latest_values ⇒ Hash

Get the latest value for each metric type

Returns:

  • (Hash) —

    { metric_type => value }



177
178
179
180
181
182
183
184
 
# File 'app/models/site_map_data_point.rb', line 177

def latest_values
  # Use DISTINCT ON to get most recent per metric_type
  select("DISTINCT ON (metric_type) metric_type, value, recorded_at")
    .order(:metric_type, recorded_at: :desc)
    .to_h do |point|
    [point.metric_type, point.value]
  end
end

.overlapping ⇒ ActiveRecord::Relation<SiteMapDataPoint>

A relation of SiteMapDataPoints that are overlapping. Active Record Scope

Returns:

See Also:



73
 
# File 'app/models/site_map_data_point.rb', line 73

scope :overlapping, ->(start_date, end_date) { where("period && daterange(?, ?, '[]')", start_date, end_date) }

.period_comparison(metric_type) ⇒ Hash?

Compare current period to previous period

Parameters:

  • metric_type (Symbol, String) —

    The metric to compare

Returns:

  • (Hash, nil) —

    { current:, previous:, change_percent: } or nil if insufficient data



157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
 
# File 'app/models/site_map_data_point.rb', line 157

def period_comparison(metric_type)
  # Get the two most recent data points
  recent_points = for_metric(metric_type).by_recorded.limit(2).pluck(:value)
  return nil if recent_points.size < 2

  current, previous = recent_points
  return nil if previous.zero?

  change_pct = ((current - previous) / previous * 100).round(1)

  {
    current: current,
    previous: previous,
    change_percent: change_pct
  }
end

.recent ⇒ ActiveRecord::Relation<SiteMapDataPoint>

A relation of SiteMapDataPoints that are recent. Active Record Scope

Returns:

See Also:



62
 
# File 'app/models/site_map_data_point.rb', line 62

scope :recent, ->(months = 6) { where("period && daterange(?, ?)", months.months.ago.to_date, Date.current) }

.record!(site_map:, metric_type:, value:, period_start:, period_end:, reference: nil, batch_id: nil) ⇒ SiteMapDataPoint

Convenience method to record a data point

Parameters:

  • site_map (SiteMap) —

    The site map this metric belongs to

  • metric_type (Symbol, String) —

    The type of metric

  • value (Numeric) —

    The metric value

  • period_start (Date) —

    Start of the period this metric covers

  • period_end (Date) —

    End of the period this metric covers

  • reference (String, nil) (defaults to: nil) —

    Optional context (e.g., keyword text)

  • batch_id (String, nil) (defaults to: nil) —

    Optional batch ID for grouping

Returns:



89
90
91
92
93
94
95
96
97
98
99
 
# File 'app/models/site_map_data_point.rb', line 89

def record!(site_map:, metric_type:, value:, period_start:, period_end:, reference: nil, batch_id: nil)
  create!(
    site_map: site_map,
    metric_type: metric_type,
    value: value,
    period: period_start..period_end,
    reference: reference,
    source_batch_id: batch_id,
    recorded_at: Time.current
  )
end

.trend(metric_type, months: 6) ⇒ Array<Hash>

Get trend data for a metric over time

Parameters:

  • metric_type (Symbol, String) —

    The metric to analyze

  • months (Integer) (defaults to: 6) —

    How many months of data to include

Returns:

  • (Array<Hash>) —

    Array of { recorded_at:, period_start:, period_end:, value: }



106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'app/models/site_map_data_point.rb', line 106

def trend(metric_type, months: 6)
  for_metric(metric_type)
    .recent(months)
    .by_period_start
    .pluck(:recorded_at, :period, :value)
    .map do |recorded_at, period, value|
      {
        recorded_at: recorded_at,
        period_start: period.begin,
        period_end: period.end,
        value: value
      }
    end
end

.trend_direction(metric_type, months: 3) ⇒ Symbol

Calculate trend direction for a metric

Parameters:

  • metric_type (Symbol, String) —

    The metric to analyze

  • months (Integer) (defaults to: 3) —

    Comparison window

Returns:

  • (Symbol) —

    :growing, :declining, or :stable



126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
 
# File 'app/models/site_map_data_point.rb', line 126

def trend_direction(metric_type, months: 3)
  data = for_metric(metric_type).recent(months).by_period_start.pluck(:value)
  return :unknown if data.size < 2

  midpoint = data.size / 2
  first_half = data.first(midpoint)
  second_half = data.last(midpoint)

  first_avg = first_half.sum.to_f / first_half.size
  second_avg = second_half.sum.to_f / second_half.size

  return :stable if first_avg.zero?

  change_pct = ((second_avg - first_avg) / first_avg * 100)

  # Invert for metrics where lower is better
  change_pct = -change_pct if INVERTED_METRICS.include?(metric_type.to_s)

  if change_pct > 10
    :growing
  elsif change_pct < -10
    :declining
  else
    :stable
  end
end

Instance Method Details

#ads_metric? ⇒ Boolean

Returns:

  • (Boolean) 


270
271
272
 
# File 'app/models/site_map_data_point.rb', line 270

def ads_metric?
  ADS_METRICS.include?(metric_type.to_s)
end

#ahrefs_metric? ⇒ Boolean

Returns:

  • (Boolean) 


266
267
268
 
# File 'app/models/site_map_data_point.rb', line 266

def ahrefs_metric?
  AHREFS_METRICS.include?(metric_type.to_s)
end

#cloudflare_metric? ⇒ Boolean

Returns:

  • (Boolean) 


274
275
276
 
# File 'app/models/site_map_data_point.rb', line 274

def cloudflare_metric?
  CLOUDFLARE_METRICS.include?(metric_type.to_s)
end

#ga4_metric? ⇒ Boolean

Returns:

  • (Boolean) 


262
263
264
 
# File 'app/models/site_map_data_point.rb', line 262

def ga4_metric?
  GA4_METRICS.include?(metric_type.to_s)
end

#gsc_metric? ⇒ Boolean

Returns:

  • (Boolean) 


258
259
260
 
# File 'app/models/site_map_data_point.rb', line 258

def gsc_metric?
  GSC_METRICS.include?(metric_type.to_s)
end

#inverted_metric? ⇒ Boolean

Returns:

  • (Boolean) 


278
279
280
 
# File 'app/models/site_map_data_point.rb', line 278

def inverted_metric?
  INVERTED_METRICS.include?(metric_type.to_s)
end

#keyword_metric? ⇒ Boolean

Returns:

  • (Boolean) 


254
255
256
 
# File 'app/models/site_map_data_point.rb', line 254

def keyword_metric?
  KEYWORD_METRICS.include?(metric_type.to_s)
end

#period_days ⇒ Object 



248
249
250
251
252
 
# File 'app/models/site_map_data_point.rb', line 248

def period_days
  return nil unless period

  (period.end - period.begin).to_i + 1
end

#period_end ⇒ Object 



244
245
246
 
# File 'app/models/site_map_data_point.rb', line 244

def period_end
  period&.end
end

#period_start ⇒ Object

Instance methods



240
241
242
 
# File 'app/models/site_map_data_point.rb', line 240

def period_start
  period&.begin
end

#site_map ⇒ SiteMap

Returns:

See Also:



32
 
# File 'app/models/site_map_data_point.rb', line 32

belongs_to :site_map