Benchmark System Architecture

Architecture Overview

The benchmark system implements a simplified architecture using only 2 Materialized Views for comparing restaurant performance against competitors, tags, and city averages.

TIER 1: DATA SOURCE
├─ MySQL Production Database (booking_production)
├─ Airbyte Continuous Sync to ClickHouse
└─ Real-time data capture

    ↓

TIER 2: ANALYTICS ENGINE (ClickHouse)
├─ Database: syn
├─ 2 Materialized Views (auto-refresh daily)
│   ├─ mv_restaurant_monthly (includes city_id, country_id, tag_ids)
│   └─ mv_restaurant_weekly (includes city_id, country_id, tag_ids)
└─ Aggregation queries for city/tag benchmarks

    ↓

TIER 3: APPLICATION LAYER (Rails)
├─ MetricService (PartnerService::Benchmark::MetricService)
├─ ClickHouse Models (HhClickhouse::RestaurantMonthly/Weekly)
├─ REST API Endpoint
└─ JSON Response

Tier 1: Data Source (MySQL)

Source Tables

booking_production database:
├─ reservations         : Booking records (date, party_size, restaurant_id)
├─ reservation_properties: Pricing data (price_cents, revenue)
├─ restaurants          : Restaurant information (city_id, country_id)
├─ restaurant_tags      : Tag definitions (Cuisine, DiningStyle)
├─ restaurant_tags_restaurants: Restaurant-tag relationships
└─ cities               : City information

Data Filters Applied

All benchmark calculations use these standard filters:

• reservations.active = 1 (active bookings only)
• reservations.ack = 1 (acknowledged bookings)
• reservation_properties.revenue > 0 (revenue-generating only)
• reservations.date >= addMonths(today(), -12) (last 12 months)

Tier 2: Analytics Engine (ClickHouse)

Database: syn

The benchmark system uses the syn database with 2 Materialized Views.

Materialized Views

Single MV Architecture

Instead of 6 separate MVs (restaurant, city, tag × monthly, weekly), we now use:

• 2 MVs that store restaurant-level data with embedded city_id, country_id, and tag_ids
• Aggregation queries calculate city and tag benchmarks on-the-fly

This approach:

1. Reduces storage and maintenance overhead
2. Ensures consistency across all benchmark types
3. Simplifies data refresh (MV auto-refreshes daily)

Common Columns (Both Views)

View-Specific Columns

Monthly View (mv_restaurant_monthly):

• month_number (UInt8): Month 1-12
• month_label (String): Format “YYYY-MM”

Weekly View (mv_restaurant_weekly):

• week_number (UInt8): ISO week number 1-53
• week_label (String): Format “Jan 06 - Jan 12”
• date_range_start / date_range_end (Date)

City Aggregation Query

SELECT
  city_id,
  month_label AS period_label,
  any(year) AS agg_year,
  any(month_number) AS agg_period_number,
  ROUND(SUM(gmv), 0) AS sum_gmv,
  COUNT(DISTINCT restaurant_id) AS restaurants_count,
  ROUND(SUM(gmv) / COUNT(DISTINCT restaurant_id), 2) AS avg_gmv_per_restaurant,
  SUM(reservations_count) AS total_reservations,
  SUM(total_covers) AS sum_total_covers,
  ROUND(SUM(gmv) / SUM(total_covers), 2) AS avg_spend_per_person
FROM syn.mv_restaurant_monthly
WHERE city_id = ?
  AND (year = ? AND month_number = ?)
GROUP BY city_id, month_label
ORDER BY month_label ASC

Tag Aggregation Query

Uses ARRAY JOIN for efficient tag filtering:

SELECT
  tag_id,
  month_label AS period_label,
  any(year) AS agg_year,
  any(month_number) AS agg_period_number,
  ROUND(SUM(gmv), 0) AS sum_gmv,
  COUNT(DISTINCT restaurant_id) AS restaurants_count,
  ROUND(SUM(gmv) / COUNT(DISTINCT restaurant_id), 2) AS avg_gmv_per_restaurant,
  SUM(reservations_count) AS total_reservations,
  SUM(total_covers) AS sum_total_covers,
  ROUND(SUM(gmv) / SUM(total_covers), 2) AS avg_spend_per_person
FROM syn.mv_restaurant_monthly
ARRAY JOIN tag_ids AS tag_id
WHERE tag_id = ?
  AND country_id = ?
  AND (year = ? AND month_number = ?)
GROUP BY tag_id, month_label
ORDER BY month_label ASC

Tier 3: Application Layer (Rails)

Service Layer

app/services/partner_service/benchmark/
└─ metric_service.rb      # Main calculation service

app/models/hh_clickhouse/
├─ benchmark_base.rb      # Base class with aggregation methods
├─ restaurant_monthly.rb  # mv_restaurant_monthly model
└─ restaurant_weekly.rb   # mv_restaurant_weekly model

Controller

app/controllers/api/partner/v1/
└─ benchmark_controller.rb

Data Flow

Request
  │
  ▼
BenchmarkController#index
  │
  ├─ Validates params (data_view_type, data_type, dates)
  ├─ Gets current_staff.restaurants.first
  │
  ▼
MetricService.new(...)
  │
  ├─ Generates periods (monthly or weekly)
  ├─ Queries ClickHouse via HhClickhouse models
  │   ├─ Vertigo data (restaurant's own metrics)
  │   ├─ City benchmark (aggregation on restaurant MV)
  │   └─ Tag benchmark (aggregation with ARRAY JOIN)
  │
  ▼
JSON Response

Key Metrics

GMV (Gross Merchandise Value)

ROUND(SUM(reservation_properties.price_cents / 100), 0) AS gmv

Average Spend Per Person

IF(
  SUM(party_size) > 0,
  ROUND(SUM(price_cents / 100) / SUM(party_size), 2),
  0
) AS avg_spend_per_person

Average GMV Per Restaurant

ROUND(
  IF(COUNT(DISTINCT restaurant_id) > 0,
     SUM(gmv) / COUNT(DISTINCT restaurant_id),
     0),
  2
) AS avg_gmv_per_restaurant

Growth Percentage

def calculate_growth_percentage(current, base)
  return 0 if base.zero?
  ((current - base) / base.to_f * 100).round(2)
end

MV Refresh Strategy

The Materialized Views use REFRESH EVERY 1 DAY which means:

• ClickHouse automatically refreshes the MVs daily
• No external worker/scheduler needed
• Data is always fresh within 24 hours

CREATE MATERIALIZED VIEW syn.mv_restaurant_monthly
REFRESH EVERY 1 DAY
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (restaurant_id, period_date)
...