Architecture & Data Pipeline

Goal of this chapter: Understand where our data lives in ClickHouse, how tables connect, the mandatory booking filter rules, and how data safely updates without downtime.

1. System Architecture Overview

The Restaurant Prioritization pipeline is fully automated and runs inside a containerized environment connected directly to Hungry Hub’s analytics database (ClickHouse):

flowchart TD
    subgraph DataSources["ClickHouse Source Tables"]
        T1["raw_data_staging.master_bookings<br/>(Reservations & Revenue)"]
        T2["hh_power_bi.facebook_ads_daily<br/>(Ad Spend & Impressions)"]
        T3["hh_power_bi.kol_expenses_flattened<br/>& Posts_TH (Influencer Spend)"]
        T4["raw_data_staging.restaurant_reviews<br/>(Ratings & Sentiment)"]
    end

    subgraph Pipeline["Processing & Machine Learning Engine"]
        F1["Data Normalization & Cleaning"]
        F2["PR #14 Net Revenue & Date Alignment"]
        F3["Weekly Feature Aggregation (80 Features)"]
        F4["LightGBM core_v2 Scoring"]
    end

    subgraph Serving["ClickHouse Output & Dashboard"]
        O1["hh_power_bi.combined_rankings_new<br/>(Staging Table)"]
        O2["hh_power_bi.combined_rankings<br/>(Live Serving Table)"]
        D1["Streamlit Dashboard<br/>(Marketing & Account Teams)"]
    end

    T1 --> F1
    T2 --> F1
    T3 --> F1
    T4 --> F1

    F1 --> F2 --> F3 --> F4
    F4 --> O1
    O1 -- "Atomic Table Exchange" --> O2
    O2 --> D1

2. ClickHouse Source Tables

The pipeline aggregates data across 8 distinct source tables:

3. The Mandatory 7-Condition Booking Filter

Caution

Strict Company Standard for All Booking & GMV Calculations:
When calculating GMV, booking volume, or covers from master_bookings, you MUST ALWAYS apply the following 7 validation conditions. Failing to include any of these will result in inaccurate revenue reporting.

SELECT
    restaurant_id,
    toStartOfWeek(created_at) AS booking_week,
    count(DISTINCT id) AS total_valid_bookings,
    sum(adult_covers + kids_covers) AS total_covers,
    sum(total_price_v2_cents) / 100.0 AS net_gmv_thb
FROM raw_data_staging.master_bookings
WHERE (active = 1)
  AND (no_show = 0)
  AND (revenue > 0)
  AND (is_temporary = 0)
  AND (for_locking_system = 0)
  AND (channel != 5)
  AND (ack = 1)
GROUP BY restaurant_id, booking_week;

Why Each Filter Condition Exists:

1. active = 1: Excludes canceled or voided reservations.
2. no_show = 0: Excludes customers who booked but never showed up at the restaurant.
3. revenue > 0: Filters out test reservations or non-billable zero-baht entries.
4. is_temporary = 0: Discards draft, incomplete, or abandoned booking attempts.
5. for_locking_system = 0: Excludes temporary table locks used during reservation checkout.
6. channel != 5: Excludes internal staff testing or invalid integration channels.
7. ack = 1: Ensures the booking was acknowledged and accepted by the restaurant partner.

4. PR #14 Financial & Operational Alignments

In recent updates (specifically Pull Request #14 on the main repository), several critical accounting alignments were implemented to ensure complete consistency with Hungry Hub’s executive Power BI reports:

A. Aggregating by Booking Date (created_at) vs Dining Date

• Old Method: Bookings were aggregated by dining date (start_time), causing past weeks to constantly shift whenever advance bookings were created for future months.
• Current Production Standard: Aggregated strictly by Reservation Creation Date (created_at). This reflects immediate marketing response: when a campaign runs this week, did it generate reservations this week?

B. Clean Net GMV (total_price_v2_cents / 100.0)

• In earlier raw exports, revenue was occasionally recorded in cents without division or using unverified customer total fields, inflating GMV numbers by up to 10x.
• The pipeline now strictly calculates clean Thai Baht using: $$\text{Net GMV} = \frac{\text{total_price_v2_cents}}{100.0}$$

C. Multi-Restaurant Facebook Ad Splitting ($1/N$ Unpivot)

When a Facebook ad campaign features multiple restaurants (e.g., a single ad promoting “Top 5 Rooftop Bars” with ฿5,000 spend):

• The pipeline parses all mentioned restaurant IDs ($N$).
• The total ad spend is equally divided: $\text{Spend per restaurant} = \frac{\text{Total Spend}}{N}$.
• This ensures individual restaurants are not unfairly recorded with the entire multi-brand budget.

D. Influencer Marketing Union (KOL Expenses + Posts_TH)

• Influencer fees come from accounting records (kol_expenses_flattened).
• Influencer engagement metrics come from marketing logs (Posts_TH).
• The pipeline merges these two tables using an outer union, guaranteeing that both costs and social reach are accounted for per restaurant.

5. Zero-Downtime Atomic Table Exchange

To ensure dashboard users never experience slow queries or partial data while the pipeline is updating:

1. The ML pipeline writes fresh results into a temporary staging table:
   hh_power_bi.combined_rankings_new
2. Once validation checks pass (verifying row count, no null IDs, valid score ranges), ClickHouse executes an instant atomic swap:

   EXCHANGE TABLES hh_power_bi.combined_rankings AND hh_power_bi.combined_rankings_new;
   

3. The swap takes less than 1 millisecond. The previous table is preserved as a backup, and users see updated rankings immediately.

6. Next Steps

To understand the mathematical model and features powering the predictions, proceed to 04. Machine Learning Pipeline.