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 = 
%w[ga4_page_views ga4_sessions ga4_users ga4_bounce_rate
ga4_engagement_rate ga4_avg_session_duration].freeze
AHREFS_METRICS = 
%w[ahrefs_traffic ahrefs_keywords_count ahrefs_traffic_value
ahrefs_domain_rating].freeze
ADS_METRICS = 
%w[ads_clicks ads_impressions ads_cost ads_conversions
ads_conversion_value].freeze
KEYWORD_METRICS = 
%w[keyword_position keyword_search_volume keyword_traffic_share].freeze
INVERTED_METRICS =

Metrics where lower is better (for trend interpretation)

%w[ga4_bounce_rate gsc_avg_position keyword_position].freeze

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

#publish_event

Instance Attribute Details

#metric_type ⇒ Object (readonly)

Validations

Validations:



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

validates :metric_type, presence: true

#period ⇒ Object (readonly) 



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

validates :period, presence: true

#reference ⇒ Object (readonly) 



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

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

#value ⇒ Object (readonly) 



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

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



186
187
188
189
190
191
192
193
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
 
# File 'app/models/site_map_data_point.rb', line 186

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(', ')

  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:



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

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:



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

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:



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

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:



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

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:



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

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

.latest ⇒ ActiveRecord::Relation<SiteMapDataPoint>

A relation of SiteMapDataPoints that are latest. Active Record Scope

Returns:

See Also:



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

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

.latest_values ⇒ Hash

Get the latest value for each metric type

Returns:

  • (Hash) —

    { metric_type => value }



169
170
171
172
173
174
175
176
 
# File 'app/models/site_map_data_point.rb', line 169

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)
    .each_with_object({}) do |point, hash|
      hash[point.metric_type] = point.value
    end
end

.overlapping ⇒ ActiveRecord::Relation<SiteMapDataPoint>

A relation of SiteMapDataPoints that are overlapping. Active Record Scope

Returns:

See Also:



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

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



149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
 
# File 'app/models/site_map_data_point.rb', line 149

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:



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

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:



81
82
83
84
85
86
87
88
89
90
91
 
# File 'app/models/site_map_data_point.rb', line 81

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



98
99
100
101
102
103
104
105
106
107
108
109
110
111
 
# File 'app/models/site_map_data_point.rb', line 98

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



118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
 
# File 'app/models/site_map_data_point.rb', line 118

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) 


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

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

#ahrefs_metric? ⇒ Boolean

Returns:

  • (Boolean) 


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

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

#ga4_metric? ⇒ Boolean

Returns:

  • (Boolean) 


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

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

#gsc_metric? ⇒ Boolean

Returns:

  • (Boolean) 


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

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

#inverted_metric? ⇒ Boolean

Returns:

  • (Boolean) 


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

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

#keyword_metric? ⇒ Boolean

Returns:

  • (Boolean) 


246
247
248
 
# File 'app/models/site_map_data_point.rb', line 246

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

#period_days ⇒ Object 



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

def period_days
  return nil unless period

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

#period_end ⇒ Object 



236
237
238
 
# File 'app/models/site_map_data_point.rb', line 236

def period_end
  period&.end
end

#period_start ⇒ Object

Instance methods



232
233
234
 
# File 'app/models/site_map_data_point.rb', line 232

def period_start
  period&.begin
end

#site_map ⇒ SiteMap

Returns:

See Also:



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

belongs_to :site_map