Case Study: Bellabeat

Purpose: Ensure that the ActivityDate follows a valid date format 

Purpose: To ensure the Id column exists in every relevant table for later joins. 

3.2 Data Cleaning

After ensuring that the data is valid, I performed cleaning operations such as removing duplicates, handling missing values, and aligning datasets for merging.

• Duplicate Removal: I removed duplicates in sleep logs based on Id and SleepDay.

• Missing Values: I used COALESCE to handle null values where necessary during joins.

3.3 Data Transformation for Time-of-Day Analysis

Next, I transformed the data to analyze user activity and intensity levels based on the time of day and day of the week. By grouping data at the person level, I smoothed over anomalous days and calculated aggregate metrics such as total intensity, average intensity, and intensity deciles.

The following query categorizes each activity into specific time intervals (morning, afternoon, evening, and night), and segments data by the day of the week (weekend vs. weekday). It calculates both total and average intensity per time period.

 3.4 Merging Data

Merging multiple tables (e.g., daily activity, sleep, and nap data) was a critical step for creating a unified dataset that could be analyzed holistically. I used LEFT JOIN to ensure that no data points were missed during the merge.

Purpose: Combine activity, sleep, and nap data to create a comprehensive view of users' daily patterns. 

 4.0 Analyze Phase

In the Analyze phase, I focused on identifying trends in activity and sleep patterns, the impact of naps on total activity, and sleep efficiency analysis.

4.1 Activity Trends

4.1.1- General Activity and Sleep Trends

First, I calculated average metrics across several categories (e.g., steps, activity levels) to understand general trends in user behavior.

Purpose: Identify average activity levels to understand user engagement with fitness tracking

The average step count of 7652 steps per day is close to the recommended 10,000 steps per day for a healthy lifestyle. However, most users are quite sedentary, spending a significant portion of their day in sedentary activities.

4.1.2 METs vs. Calories

What is METs?
The metabolic equivalent of task (MET) is a measure of the energy expenditure rate for a given physical activity relative to resting energy expenditure. One MET represents the energy cost of sitting quietly, which is roughly 3.5 mL of oxygen per kilogram of body weight per minute (ml/kg/min), equivalent to 1 kcal/kg/hour.

In simpler terms, METs quantify how much energy an activity requires compared to resting. For example, an activity with 4 METs uses four times the energy of resting. If someone performs a 4 MET activity for 30 minutes, it equates to 120 MET-minutes (4 x 30) or 2.0 MET-hours.

Calorie Burn Formula

To calculate the number of calories burned per minute during an activity, use the following formula

This formula estimates how many calories you burn based on the activity's MET value and your body weight.

SQL Query

The query calculated the total METs and calories burned per day for each user by joining two tables—minute_met_narrow and minuteCaloriesNarrow_merged. The result was then used to visualize the relationship between these two variables. 

The scatter plot of Total METs (X-axis) vs. Total Calories (Y-axis) revealed a positive linear relationship:

The linear regression formula obtained was:

• Total Calories = 0.113516 * Total METs + (-1784.56)

• R-squared: 0.61032

• P-value: < 0.0001

The R-squared value of 0.61032 indicates a moderate to strong linear relationship between METs and calories burned, suggesting that approximately 61% of the variation in calories burned can be explained by the total METs (activity intensity). The P-value being less than 0.0001 confirms the statistical significance of this relationship.

From this, it’s evident that the more METs (activity intensity) a user accumulates, the higher their total calorie expenditure, aligning with general fitness principles. Users who log more high-intensity activity will likely burn more calories, supporting the efficacy of tracking METs as an indicator of energy output.

Insights:

1. Calorie Burn Prediction: The strong correlation suggests that Bellabeat users who engage in more intense activities (as measured by METs) consistently burn more calories. This insight can guide recommendations for increasing activity intensity to users aiming to enhance weight management or fitness levels.

2. Personalized Activity Tracking: The relationship between METs and calorie burn can be used to provide more accurate, personalized feedback to users in the app, motivating them to increase their METs to meet daily calorie burn goals.

3. Consistency Across Users: The strong statistical significance (P-value < 0.0001) shows that this relationship holds consistently across the dataset, making it a reliable indicator for activity-based calorie burn.

4.2 Feature Usage

4.2.1 User Grouping Based on Activity Frequency

This section groups users based on how often they recorded steps, sleep, and weight data. The queries identify users with "Low", "Moderate", or "High" logging frequency for each metric. By understanding how often users interact with the app, we gain insights into user engagement and their tracking behaviors.

Query

I) Recording Frequency, Bar chart

This will visualize the change in group sizes between step counts, sleep, and weight recording.

Recording Frequency (n):

• Low: n ≤ 10

• Moderate: 10 < n ≤ 20

• High: 20 < n ≤ 31

Chart

A bar chart was generated to show the relative sizes of each user group (Low, Moderate, High) across steps, sleep, and weight tracking. The clear disparity between step logging and sleep/weight logging indicates where user engagement is strong and where it is lacking.

Interpretations:

• Steps Frequency: Users were classified based on their step-recording frequency, revealing that most participants recorded their steps for at least 20 days.

• Sleep Frequency: Sleep tracking was much less frequent than steps, with fewer users reaching the "Moderate" and "High" categories.

• Weight Frequency: Weight tracking was the least common, with a very small percentage of users engaging in regular weight logging..

This data provides key insights into which features of the app are most utilized by users. The high frequency of step-tracking suggests that users prioritize physical activity, but there is less emphasis on sleep and weight tracking.

II) Login Frequency and Activity Levels

Objective:

The goal here was to explore whether there is a correlation between how often users log their data and their physical activity levels, measured by average daily steps. 

Insights

There is a moderate positive correlation between login frequency and the number of daily steps. Users who log more frequently tend to have higher daily activity levels, but the correlation is not strong enough to conclude that frequent logging causes higher activity levels. Instead, it suggests that users who are already motivated to be active may be more likely to log their steps regularly.

III) Trends in Time

Objective:

The aim of this analysis is to investigate patterns in the times users log their activity, hypothesizing that exercise times will align with the typical 9-5 job schedule. By examining both average steps per hour and quartile-based analysis, we can explore how activity levels differ throughout the day among more or less active users.

Step 1: Average Steps per Hour

SQL Query 1: Extract hour and get step totals per hour

The data shows that users are most active in the afternoon and evening, consistent with breaks in a typical 9-5 workday. There is a peak in activity around these times, which indicates that many users may log activity around work breaks or post-work exercise routines. 

There could still be more to data as some factors that may affect when someone decides to log their steps can vary on many things. In our data, we can identify and account for at least one of those factors – activity level.

Let’s take a look at the distribution of the total steps each participant took. I decided to plot the distribution of total steps each participant took over the course of the recorded 31 days to estimate their fitness lifestyle. I chose to work with the sum of monthly steps because I would like to consider people who are more consistent with their fitness schedule as “more active”. In other words, people who logged more steps are considered to be more active than those who logged less steps over the 31 days.

Step 2: Distribution of Total Steps and Activity Quartiles

SQL Query: Calculate quartiles based on monthly steps 

-- Calculate quartiles based on monthly steps using PERCENT_RANK

WITH ranked_data AS (

    SELECT

        Id,

        PERCENT_RANK() OVER (ORDER BY SUM(StepTotal)) AS quartile_rank,

        SUM(StepTotal) AS sum_steps_monthly

    FROM

        `data-analytics-cert-435119.fitbit.hourlySteps_merged`

    GROUP BY

        Id

)

SELECT

    Id,

    CASE

        WHEN quartile_rank <= 0.25 THEN 'Quartile 1'

        WHEN quartile_rank <= 0.5 THEN 'Quartile 2'

        WHEN quartile_rank <= 0.75 THEN 'Quartile 3'

        ELSE 'Quartile 4'

    END AS sum_quartile,

    sum_steps_monthly

FROM

    ranked_data;

The most active of this sample are on the right side of this histogram, whereas the least active people of this sample are on the left side.

To check if there are differences in time-related habits between people who are more or less active, we can divide the histogram above into four parts, otherwise known as quartiles. Usually, we would not want to convert continuous variables, such as activity, into categorical variables because we loose nuance and statistical power. However, because I am looking at fitness as a quality shared by groups of people in the sample, I decided it will be the clearest to show as groups of fitness level.

This histogram shows the distribution of total steps taken by participants in 31 days but is now color coded into the quartiles. Again, the most active people of the sample are quartile 4 (purple) and the least active people of the sample are quartile 1 (red).

Insights:

• Quartile Grouping: By plotting the distribution of total steps taken over the course of 31 days, users are categorized into four quartiles based on their activity levels:

  • Quartile 4 (most active) shows users who log more steps, representing highly consistent fitness behaviors.

  • Quartile 1 (least active) represents users who log fewer steps and demonstrate irregular or lower activity.

• Visual Analysis: In the quartile-based histogram, Quartile 4 users, who are the most active, dominate the right side of the graph, while Quartile 1 users, the least active, fall on the left side. The grouping allows us to easily observe the difference in fitness levels across the sample.

By dividing users into quartiles, the analysis revealed that the most active users show clear activity peaks throughout the day, while less active users are more sporadic in their activity logging.

Now, using these same groups, we can recreate the line graph of average steps per house.

We see that the top two most active groups of this sample peaks three times: around morning, afternoon and evening. These times coincide around breaks of a typical 9-5 job. The second to least active group (quartile 2) has a small peak in the morning time, but I see the afternoon and evening peaks as stronger and clearer. Then lastly, we have quartile 1 (or the least active of the sample) seems to have one small peak in the late morning.

in summary, dividing the sample into groups based on activity revealed more information that looking at the group as a whole. This can serve as encouragement for Bellabeat’s marketing and development team to tailor user experience based on consumer habits and fitness skill. As an example, perhaps if the app identifies hours of the day in which they are active, it can remind them to hydrate as they prepare to go on their daily workout, further encouraging our consumer’s to continue to engage with the product.

Insights:

• Activity Peaks by Group: The line graph representing average steps by hour, broken down by quartile, reveals that:

  • Quartile 4 (most active) users exhibit peaks in the morning, afternoon, and evening, coinciding with breaks in a typical workday. This suggests that these users are highly consistent in engaging in physical activity during regular intervals.

  • Quartile 2 shows peaks primarily in the afternoon and evening, indicating moderate engagement.

  • Quartile 1 (least active) users show only minor peaks, mostly in the late morning, reflecting less consistency in their activity.

Behavioral Insight: This quartile-based segmentation reveals that the most active users are disciplined with their activity, whereas the least active users exhibit erratic or minimal engagement. 

4.3 Sleep Trends 

4.3.1 Sleep Efficiency Analysis 

1. Correlation Between Sleep and Activity Levels

This analysis investigates whether there is a correlation between users' daytime activity (measured by steps) and their sleep quality (measured by total sleep duration).

Key Findings:

• R² = 0.036, indicating a weak correlation between total steps and sleep duration.

• P-value = 0.0001054, which shows statistical significance, meaning the weak correlation is unlikely to be due to random chance.

While the relationship between steps and sleep duration, though weak, is statistically significant. This implies that although steps have some influence on sleep, they are not a major determining factor, and other variables likely contribute more to variations in sleep duration.

• The significance of the p-value suggests this weak trend could still be used to inform product recommendations (e.g., suggesting sleep tracking for highly active users), but other factors, such as stress, bedtime routines, or diet, likely play a larger role in determining sleep quality.

1. Impact of Time Spent in Bed vs Time Asleep (Sleep Delay)

Understanding the difference between time in bed and actual sleep duration is critical for assessing sleep quality.

Calculating sleep delay for each user:

The chart depicts the distribution of sleep delays for 24 Bellabeat users. Each user is color-coded (based on ID), showing the variation in how long it takes them to fall asleep across multiple days. The data provides insights into users' sleep habits and patterns related to sleep delays, with a focus on individual behaviors.

Analysis

• User-specific patterns: The color-coded IDs reveal that some users consistently exhibit longer sleep delays, while others fall asleep more quickly. For example, certain users appear to have a more significant delay during specific time periods, indicating possible stress factors, evening routines, or other behaviors that delay sleep onset.

• Clustering behavior: Users with shorter sleep delays appear grouped closely, potentially representing healthy sleep routines or better management of pre-sleep habits. Conversely, those with longer delays are distributed more widely, hinting at inconsistent sleep behaviors.

• Outliers: Certain individuals (highlighted by distinct color patterns) show erratic or extreme sleep delays that might warrant personalized coaching or interventions to improve sleep hygiene.

4.4 Nap Patterns

Assessing the role of naps on overall sleep quality.

4.4.1 Effects of Nap on NIght Sleep

The scatter plot above visualizes the relationship between Average Total Nap Time (in hours) and Average Total Night Sleep (in hours) across various participants. A trendline is also shown, indicating a negative correlation between these two variables.

Key Observations:

1. Negative Correlation:

   • As the total nap time increases, the total night sleep duration tends to decrease. This is depicted by the downward slope of the trendline, suggesting that individuals who nap more during the day tend to sleep less at night.

2. Sleep Distribution:

   • Most participants who average more than 6 hours of naps show very little night sleep (close to 0–1 hours).

   • Participants who take shorter naps (less than 2 hours on average) exhibit a wide range of night sleep durations, with many achieving 6-8 hours of sleep.

3. Outliers:

   • A few participants with average nap times around 6 hours also show 1–3 hours of night sleep. These could represent individuals who nap extensively but still manage some night sleep, although significantly less than those with fewer naps.

4. Total Sleep Displacement:

   • It appears that daytime naps compensate for nighttime sleep for many individuals in this dataset. Those with higher nap times seem to substitute naps for what could be considered a typical full night’s sleep (7–9 hours).

2. Nap Effect on Activity

Analysis of Nap Patterns:

The data shows that naps play a crucial role in user sleep routines and physical activity, as illustrated by the relationship between nap duration, nighttime sleep, and total steps:

1. Key Insights:

   1. Moderate naps are associated with higher physical activity, indicating that short naps can recharge energy levels.

   2. Users with longer naps tend to be less active, suggesting that excessive naps may lead to lethargy.

The relationship between naps, sleep quality, and physical activity highlights that users' nap behaviors significantly impact their overall wellness. For Bellabeat, this data offers an opportunity to refine its product offerings by providing personalized insights and recommendations based on nap and sleep behaviors.

4.5 Analysis Summary

Activity Trends

• General Activity: Users average 7,652 steps/day, close to the 10,000-step guideline, yet most remain sedentary.

• METs and Calories: A strong positive correlation between METs and calories suggests users who engage in higher-intensity activities consistently burn more calories.

Feature Usage

• Tracking Frequency: Step tracking was the most-used feature, followed by sleep logging, while weight tracking saw minimal use. More frequent loggers recorded slightly higher daily steps.

Time-Based Patterns

• Activity Peaks: The most active users log steps consistently around typical workday breaks (morning, midday, evening). Less active users show fewer peaks.

Sleep Trends

• Sleep and Steps: A weak but significant correlation between steps and sleep duration suggests other factors impact sleep more.

• Naps and Night Sleep: Increased nap time correlates with reduced night sleep, while moderate nappers tend to be more active.

5.0  Recommendations

1. Drive Engagement with Gamification and Rewards

   • Implement a point system and daily/weekly streaks to encourage regular activity and logging (e.g., similar to Duolingo’s goals).

   • Offer rewards for meeting fitness, sleep, and hydration milestones, boosting motivation and retention.

2. Simplify Tracking with Automated Entry

   • Introduce Bluetooth-compatible devices (e.g., scales) for seamless weight and sleep logging.

   • Provide reminders for daily device charging and prompt tracking at optimal times.

   • Offer interchangeable, comfortable and  fashionable straps/cases for devices

3. Personalized Activity and Sleep Coaching

   • Use MET and calorie data to tailor activity prompts based on individual fitness trends.

   • Offer in-app guidance on sleep hygiene, linking credible resources, and reminders to wind down before bed.

   • Develop a "Total Sleep Efficiency" metric that combines nap data with night sleep.

4. Timely, Adaptive Notifications

   • Send personalized, behavior-driven notifications during peak activity hours (e.g., hydration reminders, gentle nudges to stay active).

   • Adapt workout recommendations to match user engagement levels—high-intensity suggestions for active users and gradual routines for beginners.