Class: HhClickHouseConversionRate

Inherits:
HhClickHouseSynBase show all
Defined in:
app/models/hh_click_house_conversion_rate.rb

Overview

HhClickHouseConversionRate provides read-only access to visitor and conversion analytics data stored in ClickHouse for analytical and reporting purposes.

This model maps to the 'mv_conversion_rates' materialized view in ClickHouse, which tracks daily visitor metrics and conversion rates by restaurant and date.

Materialized View Details:

  • Name: mv_conversion_rates

  • Database: syn

  • Host: 5.223.67.134:8123

Columns:

  • restaurant_id: ID of the restaurant

  • event_date: Date of the event (YYYY-MM-DD format)

  • itemsViewed: Total number of page views/visitors

  • itemsPurchased: Total number of conversions/purchases

  • purchaseToViewRate: Conversion rate (itemsPurchased / itemsViewed)

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from HhClickHouseSynBase

find

Class Method Details

.aggregate_by_date(restaurant_ids, start_date, end_date) ⇒ Array<Hash>

Aggregate conversion rate data by date using SQL GROUP BY Calculates weighted average conversion rate in ClickHouse for better performance

This method performs aggregation in ClickHouse instead of Ruby, which:

  • Reduces data transfer (returns aggregated rows instead of all rows)

  • Eliminates Ruby iteration overhead

  • Leverages ClickHouse's optimized aggregation engine

Note: The service layer formats dates as day numbers only (e.g., “14”, “21”) for chart display, but this method returns full ISO dates from ClickHouse.

Examples:

HhClickHouseConversionRate.aggregate_by_date([1, 2, 3], Date.today - 6.days, Date.today)
# => [
#   {"event_date"=>"2025-11-14", "total_views"=>150, "total_purchases"=>15, "purchase_to_view_rate"=>0.1, "conversion_rate"=>10.0},
#   {"event_date"=>"2025-11-21", "total_views"=>200, "total_purchases"=>20, "purchase_to_view_rate"=>0.1, "conversion_rate"=>10.0}
# ]
# Service layer formats as: dates: ["14", "15", "16", "17", "18", "19", "20", "21"]

Parameters:

  • restaurant_ids (Array<Integer>) —

    List of restaurant IDs to filter

  • start_date (Date) —

    Start date for the date range

  • end_date (Date) —

    End date for the date range

Returns:

  • (Array<Hash>) —

    Aggregated results with event_date, total_views, total_purchases, purchase_to_view_rate, conversion_rate



59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
 
# File 'app/models/hh_click_house_conversion_rate.rb', line 59

def self.aggregate_by_date(restaurant_ids, start_date, end_date)
  return [] if restaurant_ids.empty?

  # Sanitize inputs to prevent SQL injection
  sanitized_ids = restaurant_ids.map(&:to_i).join(',')
  sanitized_start = connection.quote(start_date.to_s(:db))
  sanitized_end = connection.quote(end_date.to_s(:db))

  sql = <<-SQL.squish
    SELECT
      event_date,
      sum(itemsViewed) as total_views,
      sum(itemsPurchased) as total_purchases,
      round(
        sum(itemsPurchased) / nullIf(sum(itemsViewed), 0),
        4
      ) as purchase_to_view_rate,
      round(
        (sum(itemsPurchased) / nullIf(sum(itemsViewed), 0)) * 100,
        2
      ) as conversion_rate
    FROM #{table_name}
    WHERE restaurant_id IN (#{sanitized_ids})
      AND event_date BETWEEN #{sanitized_start} AND #{sanitized_end}
    GROUP BY event_date
    ORDER BY event_date ASC
  SQL

  connection.select_all(sql).to_a
end

.aggregate_by_month(restaurant_ids, start_date, end_date) ⇒ Array<Hash>

Aggregate conversion rate data by month using SQL GROUP BY Groups by month and calculates weighted average conversion rate

This method performs monthly aggregation in ClickHouse for better performance. Uses formatDateTime to format month keys consistently with Ruby's strftime.

Note: The service layer formats month_key as lowercase 3-letter abbreviations (e.g., “sep”, “oct”, “nov”) for chart display, but this method returns “Mon YYYY” format from ClickHouse.

Examples:

For last 3 months including current month (Sep, Oct, Nov when today is Nov 21, 2025):

HhClickHouseConversionRate.aggregate_by_month([1, 2, 3], Date.new(2025, 9, 1), Date.new(2025, 11, 30))
# => [
#   {"month_key"=>"Sep 2025", "total_views"=>1200, "total_purchases"=>120, "purchase_to_view_rate"=>0.1, "conversion_rate"=>10.0, "first_date"=>"2025-09-01"},
#   {"month_key"=>"Oct 2025", "total_views"=>1400, "total_purchases"=>140, "purchase_to_view_rate"=>0.1, "conversion_rate"=>10.0, "first_date"=>"2025-10-01"},
#   {"month_key"=>"Nov 2025", "total_views"=>1500, "total_purchases"=>150, "purchase_to_view_rate"=>0.1, "conversion_rate"=>10.0, "first_date"=>"2025-11-01"}
# ]
# Service layer formats as: dates: ["sep", "oct", "nov"]

Parameters:

  • restaurant_ids (Array<Integer>) —

    List of restaurant IDs to filter

  • start_date (Date) —

    Start date for the date range

  • end_date (Date) —

    End date for the date range

Returns:

  • (Array<Hash>) —

    Aggregated results grouped by month, sorted by date ascending (oldest first)



112
113
114
115
116
117
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
 
# File 'app/models/hh_click_house_conversion_rate.rb', line 112

def self.aggregate_by_month(restaurant_ids, start_date, end_date)
  return [] if restaurant_ids.empty?

  # Sanitize inputs to prevent SQL injection
  sanitized_ids = restaurant_ids.map(&:to_i).join(',')
  sanitized_start = connection.quote(start_date.to_s(:db))
  sanitized_end = connection.quote(end_date.to_s(:db))

  sql = <<-SQL.squish
    SELECT
      formatDateTime(event_date, '%b %Y') as month_key,
      sum(itemsViewed) as total_views,
      sum(itemsPurchased) as total_purchases,
      round(
        sum(itemsPurchased) / nullIf(sum(itemsViewed), 0),
        4
      ) as purchase_to_view_rate,
      round(
        (sum(itemsPurchased) / nullIf(sum(itemsViewed), 0)) * 100,
        2
      ) as conversion_rate,
      min(event_date) as first_date
    FROM #{table_name}
    WHERE restaurant_id IN (#{sanitized_ids})
      AND event_date BETWEEN #{sanitized_start} AND #{sanitized_end}
    GROUP BY month_key
    ORDER BY first_date ASC
  SQL

  connection.select_all(sql).to_a
end

Instance Method Details

#readonly? ⇒ Boolean

Returns:

  • (Boolean) 


23
24
25
 
# File 'app/models/hh_click_house_conversion_rate.rb', line 23

def readonly?
  true
end