Project Overview
As part of Cornell University’s Master of Science in Business Analytics program, our team conducted an end-to-end analysis of the Yelp Academic Dataset to investigate the relationship between business popularity and customer satisfaction. Working with over 281,000 records spanning businesses, users, reviews, tips, photos, and customer check-ins, we transformed raw relational data into meaningful business insights using SQL.
Rather than simply querying existing tables, this project required extensive data validation, feature engineering, and preprocessing before reliable analysis could begin. One of the largest technical challenges involved restructuring Yelp’s unusually formatted check-in data into an analytical model capable of measuring customer traffic at both the business and market level.
Ultimately, our analysis demonstrated that customer traffic and online visibility do not necessarily translate into higher customer satisfaction, highlighting the importance of balancing operational growth with customer experience.
Technologies & Key Skills
- SQL (SQLite) & Relational Database Analysis
- Exploratory Data Analysis (EDA) & Data Validation
- Feature Engineering & Data Preparation
- Statistical Reasoning & Hypothesis Testing
- Customer Behavior & Business Intelligence Analytics
- Business Strategy Translation & Decision Support
- Data Storytelling & Executive Presentation
- Cross-Functional Team Collaboration
Business Problem
Businesses frequently assume that popularity naturally leads to customer satisfaction. Restaurants, for example, often measure success through customer traffic, online engagement, or review volume.
Our objective was to determine whether this assumption was supported by data.
Specifically, we sought to answer one research question:
Do highly popular business categories consistently receive higher customer ratings?
Answering that question required much more than descriptive statistics. Before analysis could begin, the underlying dataset had to be validated, cleaned, transformed, and normalized into a format suitable for analytical modeling.
My Role
As a member of Team 9, I contributed throughout the complete analytics lifecycle—from data preparation and SQL development to visualization design and executive storytelling.
My responsibilities included:
- Validating relational data across multiple tables
- Cleaning and transforming complex datasets into analysis-ready structures
- Developing SQL queries for aggregation and trend analysis
- Creating executive-ready visualizations to communicate findings
- Translating analytical results into practical business recommendations
Most importantly, I helped ensure our conclusions reflected meaningful business insights rather than surface-level observations.
Methodology
Data Validation
The project began by auditing the relational database to understand its overall quality and structure.
We evaluated:
- Record counts across six core tables
- Missing values
- Duplicate records
- Category consistency
- Geographic distribution
- Business operating status
This validation phase established confidence that subsequent analyses were grounded in reliable data rather than incomplete or inconsistent records.
Feature Engineering & Data Preparation
One of the most technically challenging components involved Yelp’s check-in table.
Instead of storing customer visits in a traditional transactional format, each business contained approximately 80 separate day-hour columns, with values recorded as either numeric counts or the text value "N/A".
To make this information analytically useful, we:
- Converted textual “N/A” values into numeric zeroes
- Cast each field into integer data types
- Summed all hourly columns into total customer check-ins per business
- Joined the transformed data back to the business table
- Aggregated results by business, city, and category for comparative analysis
This preprocessing transformed more than 847,000 time-based observations into meaningful traffic metrics representing over 6.2 million recorded customer check-ins, creating a behavioral dataset that could be compared directly against ratings and review activity.
Rather than analyzing opinions alone, we were now able to compare actual customer behavior with perceived customer satisfaction.
Exploratory Analysis
After engineering the dataset, we investigated multiple dimensions of customer behavior.
Examples included:
- Reviewer concentration
- Geographic market distribution
- Business category performance
- Review growth over time
- Customer traffic patterns
- Review frequency
- Photo engagement
- Appointment-only versus walk-in businesses
We developed more than 42 SQL queries supporting validation, aggregation, feature engineering, correlation analysis, and reporting throughout the project.
Hypothesis Testing
Using the transformed dataset, we tested whether highly popular categories consistently achieved higher customer ratings.
Our findings showed the opposite.
Restaurants generated:
- 51% of all businesses
- 73% of all reviews
yet averaged only 3.60 stars, making them one of the lowest-rated categories despite receiving the greatest customer attention.
Further analysis demonstrated:
- Customer traffic strongly correlated with review volume.
- Customer traffic showed only a weak relationship with customer ratings.
- Review activity was highly concentrated among a small percentage of users, introducing potential reviewer bias.
These findings reinforced that visibility and satisfaction represent fundamentally different business metrics.
Key Insights
- Popularity does not predict customer satisfaction.
- High customer traffic increases exposure but also increases opportunities for inconsistent experiences to affect ratings.
- Approximately 1% of reviewers generated nearly 20% of all reviews, illustrating the importance of considering reviewer bias during analysis.
- Niche restaurant categories consistently outperformed high-volume cuisines in average customer ratings.
- Behavioral data (customer check-ins) provided a more reliable measure of popularity than review counts alone.
Business Recommendations
Although the project focused on exploratory analytics, our findings naturally suggested several strategic opportunities for restaurant operators.
Rather than pursuing more reviews indiscriminately, businesses should prioritize creating consistently exceptional customer experiences that encourage repeat visitation.
Potential strategies include:
- Implement reservation windows to create predictable demand and increase perceived exclusivity.
- Develop loyalty programs that reward repeat customers instead of focusing solely on attracting first-time visitors.
- Measure customer retention alongside review volume when evaluating business performance.
- Monitor both popularity and customer satisfaction independently rather than assuming one predicts the other.
These recommendations shift attention from maximizing visibility toward building sustainable customer relationships that improve long-term reputation.
Reflection
This project reinforced one of the most valuable lessons I’ve learned in business analytics: the majority of analytical work happens long before visualization or statistical testing begins.
Cleaning data, validating assumptions, engineering meaningful features, and understanding the structure of relational databases proved to be just as important as writing SQL queries or interpreting charts. Transforming Yelp’s complex check-in table into a usable behavioral dataset illustrated how thoughtful data preparation can unlock insights that would otherwise remain hidden.
More importantly, this experience strengthened my appreciation for analytics as a decision-making discipline. Rather than simply describing what happened, effective analysis should challenge assumptions, uncover root causes, and guide actionable business strategy. That mindset is one I continue to apply throughout my studies and in my professional work.
Leave a Reply