Optimizing Restaurant Revenue
A Ticket Segmentation Strategy
Executive Summary
At The Goose Pub, revenue isn’t constrained by the number of visitors, but by how service is prioritized. By shifting staff attention toward customers’ strategic elasticity, my analysis projects a 4% revenue increase. This requires strategic upselling for Group 1,2,4 and a focused service surge in Weeks 29–33 to make the most of Group 3’s seasonal peak.
Tool used : SQL, Spreadsheet, Python
PHASE 1 : ASK
This case study is based on The Goose Pub, a small-town Canadian restaurant seeking to optimize summer revenue per table through data analysis and visualization.
Primary stakeholder: Joseph, Front-of-House Manager.
Goal: Deliver insights and recommendations that are directly actionable by service staff.
Business question
How can we leverage upselling to increase revenue per table using existing traffic and staffing?
This analysis identifies which customer behaviors respond to upselling, when they occur, and how service strategy should adapt.
PHASE 2 : PREPARE
Due to limitations of the legacy Veloce POS system, I engineered a Python-based ETL pipeline to parse exported PDF reports, validate totals against control figures, and load structured data into PostgreSQL..
All data used in this case study is proprietary first-party data sourced directly from the POS system. This ensures the dataset meets high quality standards: it is reliable, original, comprehensive, current, and cited.
After filtering, the final dataset consisted of 2,400 qualified transactions.
All data in this analysis has been anonymized to protect client confidentiality.
PHASE 3 : PROCESS
Methodology: Behavioral Transaction Clustering
I developed a heuristic classification model to infer dining intent based on ticket composition.
Technical Definitions
Spend Tiers: Party size / total bill value in $, calculated using dataset quartiles
Low: Bottom 25% (< Q1)
Medium: Interquartile Range (Q1–Q3)
High: Top 25% (> Q3)
Dining Intent: Classified based on revenue split (>50% Food vs. >50% Drink).
Party Size (Est.): Inferred via item weighting (Appetizer=1, Main=2).
Small Group: ≤ 6 points
Big Group: > points
Result
This process reduced 12 initial clusters into 4 core dining profiles, covering 73% of total bills and 71% of revenue.
Final dining profiles
Group 1 (G1): Small group / Low spend / Food heavy
Group 2 (G2): Small group / Medium spend / Food heavy
Group 3 (G3): Big group / Medium spend / Food heavy
Group 4 (G4): Small group / High spend / Drink heavy
The dataset covers the full summer season of 2025 (mid-June to mid-September) and forms the basis of this analysis.
Data was filtered to cover:
Positive value checks only: Excluding staff discounts and empty checks.
Regular service nights: Excluding special event parties (outside of regular operations).
Dining customers: Only bills containing food items, excluding "after-kitchen-hours" bar customers or non-restaurant related checks.
To get access to the full data cleaning process and data wharehouse structure, please visit the GitHub page.
PHASE 4 : ANALYSIS
1.We are currently spending 49% of our labor on customers who only generate 33% of our revenue.
The first step is to analyze market share based on seasonal revenue for each group.
G1 and G2 show low revenue relative to their market share. Combined, they represent 49% of total restaurant traffic but only 33% of total revenue, making them priority groups with strong growth potential.
G3 naturally accounts for a higher share of revenue due to larger average party sizes. However, because these customers already represent significantly larger groups—approximately twice the size of other segments—upsell opportunities are more limited.
G4 can be considered a benchmark group, with a near 1:1 ratio between traffic share and revenue share, indicating balanced and efficient monetization.
2. Group 3 is not a steady segment; they are a 4-week revenue explosion that we are currently unprepared for.
Here we look at the distribution of sales by group over the summer.
G3 : This group sees a “service surge” between weeks 29 and 33 that represents 15% of the summer revenue. We need to be ready for this window and switch our operational strategy to catch maximum profit.
The other groups see an increase between weeks 29 to 35 but are much more stable. Their strategy needs to be equally applied all summer.
3. Upsell Potential Varies By Individual Elasticity Across Customer Groups
Our benchmark goal is to shift more customers toward their group’s median spend, minimizing behavioral friction while delivering significant profit gains.
G1: The wide gap between Q1 and the median indicates a highly elastic low-spending segment with strong theoretical upsell potential.
G2 & G3: The relatively narrow range between Q1 and median indicates lower individual spending elasticity.
• For G2, this suggests focusing on premiumization by transforming individual add-ons into premium drink options.
• For G3, upsell opportunities may come from aggregating individual add-ons within large parties into shared items, such as an additional meal to share or extra bottles of wine.
G4: These customers appear highly receptive to upsell, allowing for a more straightforward strategy to encourage higher spending with minimal friction.
4. Despite rigid elasticity Group 2 and 3 have high drink upsell potential.
We will start by defining our upsell strategy for alcohol and premium drinks by customer group.
G1: Acknowledging their high elasticity but low response to alcohol and premium drinks, we will focus on offering a wider range of drink options. The objective is to attract these customers toward new items through variety and discovery.
G2 and G3: These groups show lower individual elasticity and a medium response to alcohol and premium drinks. To leverage this, we will focus on upgrading existing drink choices rather than introducing entirely new products. Upsell strategies will emphasize shared options, such as wine bottles instead of glasses or pitchers instead of individual pints. For more curious customers, smaller-format drinks can also be offered to encourage trial of multiple items.
G4 : This group is already likely ordering premium drinks. Upsell efforts should therefore focus on increasing the number of rounds through more proactive and attentive service.
5. Main Course Conversion: Uncovering the "Grazers" Who Only Order Appetizers
G1 and G4 have low conversion rates from appetizers to full meals but show high elasticity, indicating a strong opportunity to convert appetizer orders into main courses. If this group sticks to appetizers, we can increase the number of rounds to sell more appetizers and potentially more drinks.
G2 shows a medium conversion rate and low elasticity. To minimize friction, appetizer-to-main upgrades should be offered through bundled options, such as combining two small, shareable appetizers into a single main-course equivalent.
G3 already has a strong conversion rate. Given the larger party size and lower elasticity, revenue growth should focus on additional shareable items such as extra appetizers, desserts, and side items rather than pushing full meal upgrades.
PHASE 5 : ACT & RECOMMENDATIONS
GROUP 1
Profile: High Individual Elasticity
Strategy:
Encourage drink diversification to upsell premium beverages.
Work to convert appetizers into full meals.
We can increase the number of rounds to sell more appetizers and potentially more drinks.
GROUP 3
Profile : Low Individual Elasticity
Strategy:
Prioritize sharing behavior. Between drinks and food, we can leverage their low elasticity by focusing on small increases in individual shares, which will accumulate over time.
Insight: Focused staffing and upselling during week 29 to 33 unlock disproportionate revenue.
Group 2
Profile: Low Individual Elasticity
Strategy:
Prioritize Premiumization: Convert "appetizer-only" tables into full main courses trough sharing.
Upgrade single drink orders into large-format shareables (wine bottles, pitchers)
Even small upgrades can have a major impact due to their large market size.
GROUP 4
Profile: High Individual Elasticity
Strategy:
Multiply the round to sell more drink & appetizer
Work to convert appetizers into full meals.
This group has high spending potential; staff can upsell confidently with minimal resistance.
Projected Revenue Impact
Simply by increasing the spend of the lowest one-third of customers in each group to the median, we could raise revenue by 4%—without changing anything else except how we engage with customers.
This projection requires no pricing changes and no additional traffic.
Revenue growth at The Goose Pub is not only constrained by demand.
It is constrained by where staff attention is spent.