SevenRooms model
Guest feedback
Guest feedback scores per venue and visit date: overall, food, drinks, service and ambience.
- Grain
- reservation date x venue x feedback type
- Built from
- 1Raw tables
- 1Staging
- 1Core
- 1Analytics
- Reads from SevenRooms
- reservation_feedback
- Ships as
- Weld and dbt SQL, open source on GitHub
core.seven_rooms.guest_feedback
1-- core.seven_rooms.guest_feedback
2-- Guest feedback scores per venue and visit date: overall, food, drinks, service
3-- and ambience.
4-- Grain: reservation date x venue x feedback type.
5--
6-- Each score is carried as a sum and a count of answers, because guests skip
7-- questions: average over any period as SUM(<x>_total) / SUM(<x>_responses).
8-- The avg_ columns are for this row only.
9
10SELECT
11 reservation_date,
12 venue_id,
13 feedback_type,
14 COUNT(*) AS feedback_received,
15 SUM(overall_rating) AS overall_total,
16 COUNT(overall_rating) AS overall_responses,
17 SAFE_DIVIDE(SUM(overall_rating), COUNT(overall_rating)) AS avg_overall,
18 SUM(food_rating) AS food_total,
19 COUNT(food_rating) AS food_responses,
20 SAFE_DIVIDE(SUM(food_rating), COUNT(food_rating)) AS avg_food,
21 SUM(drinks_rating) AS drinks_total,
22 COUNT(drinks_rating) AS drinks_responses,
23 SAFE_DIVIDE(SUM(drinks_rating), COUNT(drinks_rating)) AS avg_drinks,
24 SUM(service_rating) AS service_total,
25 COUNT(service_rating) AS service_responses,
26 SAFE_DIVIDE(SUM(service_rating), COUNT(service_rating)) AS avg_service,
27 SUM(ambience_rating) AS ambience_total,
28 COUNT(ambience_rating) AS ambience_responses,
29 SAFE_DIVIDE(SUM(ambience_rating), COUNT(ambience_rating)) AS avg_ambience,
30 COUNTIF(LOWER(recommend_to_friend) IN ('true', 'yes', '1')) AS would_recommend,
31 COUNT(recommend_to_friend) AS recommend_responses
32FROM {{staging.seven_rooms.reservation_feedback}}
33GROUP BY 1, 2, 3This model and the 2 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().
Notes
How it counts. And where it stops.
Each score is carried as a sum and a count of answers, because guests skip questions: average over any period as SUM(<x>_total) / SUM(<x>_responses). The avg_ columns are for this row only.
Raw tables it reads
- raw.seven_rooms.reservation_feedback
More SevenRooms models
Put SevenRooms in front
of your agents.
Connect SevenRooms, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.