Introduction
I recently put myself in the position of a customer analysing a new data source for their business, with the goal to understand the best ways to ingest data to answer customer queries effectively. To do this, I took on the role of a data consultant for a fictional restaurant called Dragonfly Cafe.
Dragonfly Cafe is an Asian restaurant with about three years of experience under their belt. Although Dragonfly Cafe don’t have too much experience, they have lofty ambitions of opening up multiple chains throughout London. However, they face a challenge - their delivery business isn't keeping up with their in-house success, despite offering delicious Asian cuisine through both dine-in and delivery apps. In order to understand why this is happening, Dragonfly Cafe purchased Fivetran to get to the bottom of this mystery using data.
In addition, Dragonfly Cafe relies on Square for payment processing, point-of-sale (POS) operations, and overall business management. Armed with the knowledge that Square serves as their primary data source, I delved into their business performance.
In this article, we'll dive into Dragonfly Cafe's journey towards becoming more data-driven and explore the tools and strategies I used to help them reach their ambitious goals.
Data Stack and Tools
Dragonfly Cafe’s existing one-person data team has built a robust data stack to empower their data-driven journey. They rely on Fivetran for seamless data extraction and Snowflake, a popular data warehousing solution, to store and analyse their data. Whilst they have expressed curiosity about dbt to me, they admit to having limited experience in this domain. Nonetheless, they are ready to harness the potential of their data to drive their business forward.
Exploring Square: A Versatile Business Ally
Square, their chosen platform, has proven to be a versatile and small-business-friendly companion. It offers a user-friendly interface, quick setup, and minimal fees, making it an ideal choice for businesses like Dragonfly Cafe. Beyond payment processing, Square provides a plethora of tools for business operations, including employee management, inventory tracking, and loyalty programs.
Using Fivetran
At no surprise to anybody, I used Fivetran to push Dragonfly Cafe’s Square data to their Snowflake instance. Fivetran’s Square connector is super easy to set up and comes with a predefined entity relationship diagram. This made it a lot easier to figure out what data I needed and where I would need to go to get it. As well as this, Fivetran’s dbt integration was ideal for me, as it meant that I could integrate my models with Fivetran and get Fivetran to carry out integrated scheduling for me, as well as display the associated data lineage graph.
Square’s Schema: A Treasure Trove of Data
Within Square's ecosystem lies a rich pool of data, if you can access and understand it. Using Fivetran’s Square schema, I have tried to break this schema down into several key categories, although the following categories are not all encompassing:
Orders: This category includes data on order amounts, products purchased, quantities, and product prices.
Customers: This comprises customer information such as names, emails, birthdays, and email subscription preferences.
Refunds: Information on refund reasons, amounts, and statuses.
Employees: Details about employee names, shifts, wages, and locations.
Location: Data on addresses, business names, and currency.
Inventory: Insights into products, quantities, and locations.
Price Modifiers: Information about taxes and discounts.
Payments: Details about payment types, card information, status, and processing fees.
Disputes: Information regarding dispute reasons, amounts, evidence, and dates.
Cash: Data on cash register transactions and locations.
Loyalty: Insights into loyalty program tiers, points, and rewards.
Key Questions and Insights
In order to improve Dragonfly Cafe's delivery service, I drafted the following questions to answer by the end of my analysis.
Sales & Revenue Analysis:
- What is the total sales and revenue for a specific time period, and how does it compare to previous periods (this month/quarter/year vs. last)?
- Which products are most frequently purchased together?
- What products or services are generating the most revenue?
- What are the trends in sales and revenue over time?
Customer Analysis:
- How many new customers have been acquired in a certain time frame?
- How many customers are at risk of churning, and what is the average spending per customer?
- What products are the most valuable customers purchasing?
Payment Method & Operational Efficiency Analysis:
- How often are different payment methods used?
- Are there specific times when payment processing faces bottlenecks?
- How efficient is the payment processing system?
- How much do customers spend on processing fees?
To address these questions, I developed sample queries and data analysis pipelines, aiming to answer these questions.
Sales and Revenue Analysis
Firstly, analysing Dragonfly Cafe’s revenue and profit data provided the foundation in my continued data interrogation. When analysing this data, I created models for the daily, monthly, and yearly sales summaries, as well as developing models to understand the products and quantities purchased. This helps to provide an overview for the business, see which products are most popular and profitable, and be the backbone for other models.
Operational Efficiency Analysis
To enhance their operational efficiency, I created dbt models to analyse transaction processing times, peak processing times, and total transactions per hour. These future insights should help them streamline Dragonfly Cafe’s operations for maximum efficiency.
Customer Analysis
My analysis of customer data focused on identifying valuable insights about Dragonfly Cafe’s customer base. I developed models on customer average purchase value, customer churn rates, customer lifetime value, and other characteristics. By understanding customer demographics, preferences, and behaviours, Dragonfly Cafe can tailor their marketing efforts and improve customer retention.
Payment Method Analysis
Analysing payment methods allows Dragonfly Cafe to gain insights into how customers prefer to pay, how long it takes for payments to be processed, and how much must be paid in processing fees. This information can help Dragonfly Cafe optimise their payment processing systems and potentially reduce processing fees.
Data Lineage: Mapping the Journey
Understanding data lineage is crucial for maintaining data quality and ensuring the accuracy of insights. After creating these models, I produced a sample data lineage graph to document the flow of data from the source through various transformation stages. This can be seen below:
Outcomes
Machine Learning & AI: Predicting Customer Churn
In order to predict customer churn, I used XGBoost, a gradient boosted decision tree model, because of its speed, performance, flexibility, and open-source nature make it an ideal tool for this particular use case. After defining a churn criteria and selecting the relevant data columns, I aimed to predict future churn using XGBoost. This model meant that the at-risk customers could be highlighted, at an accuracy ranging from 0.72 to 0.76. Considering my fake dataset had only 1000 rows, this displayed the model's potential in identifying customers at risk of churning accurately. Knowing this means that targeted marketing efforts and customer retention strategies can be pushed to the highlighted at-risk customers.
The Power of Data-Driven Insights
In order to visualise the transformed data and the churn model results in a suitable way, using a BI tool (Tableau) was vital. Visualising data not only enhances understanding of the data for non-technical users, but also helps such users make data-backed decisions. It also provides certainty in assumptions and empowers the team to act on valuable insights rapidly. I created three dashboards which can be seen here.
Conclusion
In conclusion, Dragonfly Cafe's journey to becoming more data-driven is not just about numbers and algorithms; it's about transforming their business, understanding their customers better, and ultimately achieving their ambitious goals in the competitive world of Asian cuisine. Armed with the insights above, Dragonfly Cafe can take proactive measures to retain customers.
As part of this journey, I also learnt a lot about how difficult it is to develop meaningful business insights. Just understanding a business’ data is time-consuming in and of itself, let alone analysing that data appropriately and continually iterating the insights delivered. After doing this project, I developed a newfound respect for analysts across the globe!
