Latte Ledger: Café Transactions

New Yorkers are known for their deep affection for coffee, which serves as a vital fuel for the fast-paced, always-on-the-go atmosphere of New York City. To thrive among many coffee shops, owners must discern how to best use their resources. How should they stock, when should they schedule, to keep the customers coming back for more? This project encompasses data from a coffee shop chain with three locations (Astoria, Hell's Kitchen, and Lower Manhattan) in New York City. The data spanned 12 columns and almost 150,000 rows covering transactions from January to June 2023 and identifying peak business hours, popular products, and golden opportunities.

Peak Hours:

Note: Astoria's hours are different from the other two. Lower Manhattan and Hell's Kitchen operate from 6:00 a.m. to 9:00 p.m., while Astoria operates from 7:00 a.m. to 8:00 p.m.

The busiest time for the coffee shops is early morning, specifically 8 - 10. 7 a.m. is very busy as well at Astoria and Lower Manhattan but not as much at Hell's Kitchen.

After the morning rush, there is a stable flow of customers coming until 6 p.m. and hardly any transactions happen after 8:00 p.m. Astoria is closed by then but has a consistent volume of customers until they close every day. Weekends, especially Saturdays, are slower than weekdays, likely due to most office work positions being closed on those days.

Popular Products:

Using the "Location" slicer, the product type table can display the top 15 product types for each store or in total. The top three types are the same for each store:

• Astoria

  1. Brewed Chai Tea . . . . . . . . . . . . . transactions = 6,293 . . . . . revenue = $27,428
  2. Gourmet Brewed Coffee . . . . . transactions = 6,053 . . . . . revenue = $23,823
  3. Barista Espresso . . . . . . . . . . . . . . transactions = 4,930 . . . . . revenue = $27,935

• Hell's Kitchen

  1. Barista Espresso . . . . . . . . . . . . . transactions = 6,153 . . . . . revenue = $32,420
  2. Brewed Chai Tea . . . . . . . . . . . . . transactions = 5,824 . . . . . revenue = $25,645
  3. Gourmet Brewed Coffee . . . . . transactions = 5,642 . . . . . revenue = $23,010

• Lower Manhattan

  1. Barista Espresso . . . . . . . . . . . . . . transactions = 5,320 . . . . . revenue = $31,051
  2. Gourmet Brewed Coffee . . . . . transactions = 5,217 . . . . . revenue = $23,201
  3. Brewed Chai Tea . . . . . . . . . . . . . . transactions = 5,066 . . . . . revenue = $24,009

These three types brought in about $10K more than those not in the top three, with one exception. Hot chocolate earns almost as much revenue, if not more.

• Hot Chocolate
  • Astoria . . . . . . . . . . . . . . . . transactions = 4,300 . . . . . revenue = $26,335
  • Hell's Kitchen . . . . . . . . . transactions = 3,763 . . . . . revenue = $23,586
  • Lower Manhattan . . . . . transactions = 3,405 . . . . . revenue = $22,495

Let's go granular!

Examining transactions rather than revenue since order size may skew the results when judging popularity, given that larger sizes cost more. Brewed Chai Tea was fairly split between Morning Sunrise Chai, Spicy Eye Opener Chai, and Traditional Blend Chai, however, Morning Sunrise Chai was the favorite at all three locations.

• Morning Sunrise Chai . . . . . Astoria: 2,129 . . . . . Hell's Kitchen: 1,992 . . . . . Lower Manhattan: 1,735
• Spicy Eye Opener Chai . . . . . Astoria: 2,128 . . . . . Hell's Kitchen: 1,888 . . . . . Lower Manhattan: 1,640
• Traditional Blend Chai . . . . . Astoria: 2,036 . . . . . Hell's Kitchen: 1,944 . . . . . Lower Manhattan: 1,691

Although Chai tea has the highest transactions and revenue overall, going granular reveals that there are three products under the Brewed Chai Tea type but only two under Gourmet Brewed coffee. Each of the gourmet coffees has more transactions and higher revenue than each of the individual chai teas. Ethiopia is the favorite Gourmet coffee at Hell's Kitchen and Lower Manhattan while Columbian is the star at Astoria.

• Columbian Medium Roast . . . . . Astoria: 3,117 . . . . . Hell's Kitchen: 2,762 . . . . . Lower Manhattan: 2,593
• Ethiopia . . . . . . . . . . . . . . . . . . . . . . . . Astoria: 2,936 . . . . . Hell's Kitchen: 2,880 . . . . . Lower Manhattan: 2,624

The Barista Espresso type has four products and the widest range so far. The products in this type bracket are split between shots and full drinks. This is the category that consistently brings in the highest revenue even when it does not have the highest transactions. It may have had the highest transactions at Astoria if it sold the Ouro Brasileiro Shot as well.

• Cappuccino . . . . . . . . . . . . . . Astoria: 1,929 . . . . . Hell's Kitchen: 1,887 . . . . . Lower Manhattan: 1,749
• Espresso Shot . . . . . . . . . . . Astoria: 953 . . . . . . . Hell's Kitchen: 943 . . . . . . . . Lower Manhattan: 834
• Latte . . . . . . . . . . . . . . . . . . . . . Astoria: 2,048 . . . . . Hell's Kitchen: 1,960 . . . . . Lower Manhattan: 1,878
• Ouro Brasileiro Shot . . . . . Astoria: N/A . . . . . . . Hell's Kitchen: 1,363 . . . . . Lower Manhattan: 859

There are two hot chocolates as well:

• Dark Chocolate . . . . . . . . . . . . . . . . . . Astoria: 2,197 . . . . . Hell's Kitchen: 1,793 . . . . . Lower Manhattan: 1,675
• Sustainably Grown Organic . . . . . Astoria: 2,103 . . . . . Hell's Kitchen: 1,970 . . . . . Lower Manhattan: 1,730

Golden Opportunities:

Optimize inventory

The most profitable products are varieties of coffee. Now that the most in-demand of those products has been identified, ensure that there is enough stock to meet demand.

Consider branching out. Add more coffee products to the menu. In the case of Astoria, add the Ouro Brasileiro Shot given its proven success from other locations.

Staffing Schedule Adjustment

Now that peak hours and low hours have been identified, alter the schedule for adequate manpower during the morning rush and don't overstaff in the evening when customer traffic gets slow.

Operating hours change

Given that hardly any customers come in during the last hour, consider whether it is worth staying open that late. Astoria opens an hour later and closes an hour earlier, yet it is not falling behind the other two businesses and maintains steady foot traffic until it closes for the night.

Optimize Sales Strategy for Coffee

The highest revenue comes from coffee sales. To maximize earnings in this lucrative category, implement more promotional strategies. Consider promotions to get more customers during the afternoon when business is slower.

Thanks for joining me to stir up insights with Data!