Project Summary:

As a Data Consultant, one of my specialties is analyzing client/ customer data, specifically for fitness studios. This Looker dashboard is a sample of a client and revenue analysis, using synthetic data generated by AI. This imaginary fitness studio needs to know how each location is performing in overall revenue, client foot traffic, membership churn, and outstanding negative account balances.

Businesses often have thousands or millions of data points, but downloading large CSV files each time a new insight is needed is time consuming and does not result in a quick analysis. This dashboard is equipped to be connected to a database via API for live data updates as often as every 15 minutes. Now that’s some real At-A-Glance insights.

key insights:

• Overall: All locations experienced a dip in revenue until August, and then stagnation until the end of the year. Overall revenue seemed to be unaffected by the large loss of 73 memberships in December, meaning other revenue categories offset the loss. Overall, all locations had an 11% increase in memberships from January ‘24 to December ‘24. March was the best performing month financially and August saw the most membership growth, while July was the lowest performing month financially and December held the largest membership loss, which will be important to note when creating seasonal predictions.

  • Recommendations: Review individual location data to determine the best strategies for increasing overall revenue and identifying reasons for membership loss.

• South City is the highest revenue earner, 25% gradual membership increase for 2024, yet has the lowest client visit count.

  • Recommendations: Review marketing strategies implemented for South City to increase client foot traffic.

• North City has had an 11% membership growth for 2024 despite large cancellation months in April and December, but holds a large negative account balance.

  • Recommendations: Gather data on reasons why customers cancelled their memberships in the months of April and December. Review each client carefully and ensure all steps were taken to retain clients before cancelling them. Address large negative account balances by contacting clients and collecting billing information.

• East City is the lowest performing location in membership generation. Membership rates are slowly declining as more members are cancelling than signing on, despite being the location with the highest foot traffic.

  • Recommendations: Identify areas of staff weakness and client dissatisfaction to pinpoint why clients will attend classes, but not commit to a membership.

• West City is the lowest performing location in revenue, retaining only 40% January’s revenue in July.

  • Recommendations: Create an additional dashboard to analyze West City’s revenue and identify the major loss of income from January through July. Membership and client foot traffic numbers did not plummet the same amount as revenue, so another factor such as retail sales, refunds, or lack of class pack purchases may have had an impact.

process

Objective:

Create a user-friendly dashboard for technical and non-technical stakeholders to quickly access, understand, and interact with client and revenue data needed for weekly reporting. Provide insights for overall trends to help management identify areas of opportunity for additional revenue.

Step-by-Step Process:

1. CSV files with synthetic data generated by AI were imported into spreadsheets in Google Sheets.

   This is a great solution for one-time report generation, but for live data updates I would use BigQuery instead of Sheets. I then would have connected directly to the business database using an API key, eliminating the need to pull reports manually.

2. The Looker dashboard was created, with each chart connecting to the corresponding Sheets.

3. I created 5 pages to cover the typical data needs of a fitness studio who is focused on revenue and client foot traffic.

   1. Revenue and client traffic at a glance, to quickly monitor progress for the current month, quarter, or year.

   2. Revenue for all locations and singular locations, to monitor yearly and year-over-year trends.

   3. Client traffic, to quickly identify attendance trends by class and payment type.

   4. Memberships, to monitor client churn and identify popular demographics per location.

   5. Negative account balances, to keep track of which locations have large negative balances on client accounts (due to overdrafts, expired payment methods, etc.) that require immediate attention to resolve.

4. Data was filtered, sorted, and categorized by color to ensure integrity and consistency across all charts.

5. Date and Location filters were added to allow stakeholders to filter their data and see the precise numbers that they need.

6. Analyze overall trends and identify patterns in client/ revenue activity to suggest data-driven business decisions (see in Key Insights).