Business analytics · COMM 414 · SQL (BigQuery) · October 2026

Opening an independent café: what Yelp data says

This was a five-person team project in COMM 414 using the Yelp open dataset in Google BigQuery. We took the role of concept and operations consultants for someone planning to open an independent café, and used review text and check-in activity to guide two decisions: what customer experience to prioritize, and how to staff the café through the day.

387,402Independent café reviews analyzed
4.45 vs 2.14Average stars, friendly vs rude staff mentions
11 a.m.Weekend check-in peak
12 p.m.Weekday check-in peak

Setting up the sample

The first query compared four large chains (Starbucks, Dunkin', Tim Hortons and McDonald's) with all other coffee-related businesses. The chains averaged 2.47 stars against 4.05 for the others, and only 8.48% of chain locations were closed at the time of the snapshot, compared with 36.47% of the other cafés. Because chains operate under a different business model, we excluded them from the rest of the analysis, which left 4,286 independent cafés.

Which review themes go with higher ratings

We matched keywords in the lowercased review text against ten themes and compared each theme's average rating and share of one- and two-star reviews with the baseline of all café reviews (4.11 stars, 13.11% rated one or two stars). Reviews that mention friendly staff average 4.45 stars, while reviews that mention rude or unfriendly staff average 2.14, and 67% of them are rated one or two stars. Slow service and cleanliness are the other clear risks: only 1.04% of reviews mention dirty conditions, but 45% of those reviews are rated one or two stars.

Average star rating of reviews that mention each theme, against the baseline of all independent café reviews. Hover for the number of reviews and the share rated one or two stars.

These results make customer interaction the first priority before opening: clear standards for greeting customers, explaining delays and resolving complaints, supported by order-to-pickup times, queue length and cleaning checks. Keyword matches are a signal to investigate rather than a verified measure of sentiment, and none of these comparisons shows that changing one feature would raise ratings.

When cafés are busiest

Yelp stores check-ins as one long text field per business, so the query split each record into individual timestamps, converted them from UTC to each café's local time zone, and divided weekday and weekend totals by their number of calendar dates so the two can be compared fairly. Weekday activity builds through the morning and peaks at noon, at 17.65 check-ins per date. Weekend activity rises quickly after 8 a.m., peaks at 11 a.m. at 36.22, and stays well above weekdays until mid-afternoon.

Average check-ins per calendar date across the independent café sample, by local hour. Hover to compare weekdays and weekends.

For the owner, this means weekdays and weekends need different staffing plans: more staff from late morning to early afternoon on weekends, and weekday coverage focused around lunch, with part-time shifts covering the peaks rather than extra staff all day.

Limitations: check-ins are voluntary, so they measure Yelp activity rather than total customer traffic. Keyword themes do not capture context, and one review can match several themes. The time zone of Yelp timestamps is not documented; we tested the UTC assumption by comparing the busiest and quietest hours in Nevada and Pennsylvania before applying it.

Tools

Google BigQuery SQL: common table expressions, joins, regular-expression keyword matching with REGEXP_CONTAINS, CROSS JOIN with UNNEST and SPLIT to expand check-in strings, time-zone conversion, window functions (RANK with PARTITION BY) and conditional aggregation. The first part of the assignment covered five standard summaries, each submitted as an initial query, a refined final query and a short explanation of the changes.