How a wellness tech company can play it smart.

Bellabeat, founded by Urška Sršen and Sando Mur in 2013, is a high-tech company that manufactures wellness products dedicated to empower women about their activity, sleep, stress, and reproductive health. Bellabeat is an international company and more about them and their products can be found on their website.

Stakeholders:

  1. CCO of Bellabeat: Urska Srsen
  2. Co-founder of Bellabeat: Sando Mur
  3. Bellabeat marketing analytics team

Business Task:

Trends, insights, and patterns can be identified in smart device data to find correlations to Bellabeat products & users, and to help dictate appropriate marketing strategies to increase market share.

Preparation:

Summary: The datasets contain wellness information among 33 anonymous FitBit users such calories, distance, steps, & sleep. The users consented to submission of tracked wellness information which are contained in .csv for the purpose of data exploration.

Licensing and accessibility: The data has been published under the CCO 1.0 Public Domain Dedication license. The publisher dedicated the data to public domain and waived all rights of the work worldwide under copyright law. Users of this data can copy, modify, distribute, and perform work even for commercial purposes without asking permission. The security and accessibility of this data is free to the public, however my personal copy and work of the data has been secured on a local device and backed up in an online drive.

Organization & Integrity:
The .csv files used were:

Files were categorized into folders such as Daily, Hourly, and Sleep. The datasets were sorted in long format for processing.The integrity of this data is outdated being from 2016 however the user information could not be changed once it is electronically logged and stored.

Limitations: The sample size was just above the minimum with 33 users and activity was tracked only for 31 days. There are possible issues with bias in this data because not every user logged the same amount of days for activity or sleep records which can affect the results of the study if not properly accounted for.

Process

Tools:

Cleaning: Below is a summary of techniques used and embedded in the Bellabeat Datasets: Cleaning Log are the specific changes for cleaning made in the datasets.

Renaming columns

Transformation & Aggregation:

Days of the Week Activity: To get a better understanding of the averages by the day of the week, I uploaded the cleaned datasets to BigQuery. I created a new table in SQL titled Days of the Week Activity by aggregating the columns from the Daily_Activity dataset and extracting the Day of the week from the ActivityDate column.

Then I transformed the newly created table Days of the Week Activity table by creating a CASE statement to convert and sort minutes to hour when they exceeded 60. Then I created one final CASE statement to sort and assign the Day of the Week Column from a number to string text all in order to make the table more comprehensible.

User Type: In order to discern the number of times each unique user Id logged in over the course of data collection period, I created a new table and added a CASE statement that counted logins and assigned users to groups based on activity.

Sleep The final table I created for processing in BigQuery was Sleep_Avg so I created a new table from the uploaded Sleep_Data table and created columns for Id, extracted DAYOFWEEK from the Date column as done in the previous table. I also wanted to get the averages for Time_In_Bed & Time_Asleep.

Analysis

Group Averages

SQL: To start my analysis I wanted to determine the average activity level for each day of the week for both the group and individual users. My metrics were going to be average calories per day and average steps per day. I created a User_Averages table using SQL in BigQuery from the Daily_Activity table and aggregated the average total of steps, distance, and calories. I then queried the newly created table to round the averages and download the results to sort in Excel before uploading to Tableau.

Excel: I downloaded the queried results to an excel notebook and created a pivot table for Calories & Steps. For the pivot table the rows were sorted as days of the week and values were the averages of Calories & Steps. The last step was creating a chart to have visualization accompany the table.

User Averages

Tableau: I downloaded the results of the previously queried table Days_of_Week_Activity to an excel notebook to be uploaded to Tableau. The table was grouped by Id, Calories, Steps, Distance, and Day. I wanted to create a Visualization in Tableau to sort each individual daily calorie and step summary by day of the week, to make it easier to analyze the data. Below are the results.

Results We can already see from both the data and visualizations that the most active days of the week based on user and group averages of calories and steps are Tuesday & Saturday. The least active days appear to be Thursday & Sunday. Let’s continue our analysis and see what other trends we can find.

Active Time of Day

Steps & Calories by Hour: SQL To find the most active time of the day for all users over the course of the data collection period, I queried the results of the Calories_Hourly & Steps_Hourly tables by selecting DISTINCT Hour and rounding the average steps and caloires.

Hourly Activity: Tableau I uploaded the query results ,that were in long format in an excel notebook, to Tableau. I wanted to compare both the average calories burned and average steps of all users against the time of day to see the most active hours of the day. I created a dual-axis line chart in Tableau, here are the results.

Results According to the aggregated data and chart it appears the most active time of day is between about 5 & 6 pm. We get a sharp increase in activity at the beginning of the day around 6 am and it keeps rising until we get a dip between 2 - 4pm, before our active peak of the day between 5 & 6 pm. After about 7pm there is a sharp decrease in activity.

Sleep

Average Sleep Data: SQL & Excel Sleep is extremely important and has a major impact on our day. I wanted to find the average amount of sleep by day of the week as well as a summary of sleep over the collection period so I could compare the results to see if it had any affect on activity. I created a pivot table and chart of sleep averages with day of the week in Excel from the Sleep_Avg table which I already processed and transformed from the Sleep_Data dataset in BigQuery.(As shown above in the Process section of this Case Study.)

Sleep vs. Highly Active Minutes: Tableau To get a comparison of average sleep and activity, I needed to create a dual axis chart in Tableau. I made a connection with the Sleep_Data table and Daily_Activity in the data soucre section in Tableau. I created a dual axis line chart comparing average time asleep to very active minutes over the dates of the data collection period.

Results: Recommended sleep for adults is between 7 - 9 hours a day. The Sleep averages chart shows that the weekend (Saturday & Sunday) is when users get the most sleep. Tuesday and Thursday users get the least amount of sleep, less than 7 hours which is the minimum amount recommended for adults. We also saw a decline in activity after Tuesday in the Average calories and steps data. This shows a trend in sleep and physical activity. The dual axis chart shows a direct correlation between sleep and highly active minutes over time. Each day the users get more sleep the level in highly active minutes increases. Every day that has a dip in sleep, there is a dip in highly active minutes. The more sleep the users get the more highly active minutes they will partake in the next day. The less sleep the user gets, the more likely of a decrease in highly active minutes the following day.

User Tracking: SQL & Excel

Since sleep is so important and not only does the Bellabeat Time product keep track of your activity, but sleep as well. I wanted to compare how active users were, when tracking activity versus tracking sleep. I used the same standard for both tables of data by categorizing how often a user was tracking for that specific activity.

  • 25 or more records: Highly Active
  • Between 15 and 25 records: Moderately Active
  • Between 6 and 14: Fairly Active
  • Between 0 and 5: Lightly Active

I created a pivot chart from the aggregated data in the User_Type table which is filtered and processed from the Daily_Activity table.

To categorize the users for sleep tracking I queried the Sleep_Data table in SQL by selecting DISTINCT user Id counting each user login date. Then I categorized each user based on number of logins.

User Tracking: Activity vs. Sleep: Tableau To compare how users track their sleep and how they track their activity I wanted to create a dashboard in tableau to compare the charts side by side. I uploaded the User_Type table and made a connection by user type with the already uploaded Sleep_Data table. Within Tableau I created a pie chart from both tables and changed the total login data to percentage of total. I created a dashboard to compare both pie charts side by side.

Results: The amount of Highly Active users for physical activity reached almost 88%, while Highly Active users only accounted for roughly 46% of sleep tracking. The amount of Highly Active users for tracking sleep was almost cut in half compared to Highly Active users for physical activity. Lightly Active users (which is between 0 and 5 logins) accounted for one third of all sleep tracking, compared to about 3% of tracking physical activity.

Conclusion & Recomendations

Summary

Days of the Week

  • The most active days of the week are Tuesday & Saturday
  • The least active days of the week are Thursday & Sunday

Hourly

  • Activity for users start between 5 - 6 am
  • Activity for users dips between 2 - 4 pm
  • Activity Peaks between 5 - 6 pm
  • Activity declines for the rest of the day after 7 pm

Sleep

  • Users tend to get the most sleep on Sunday & Saturday
  • Users get the least amount of sleep on Tuesday & Thursday
  • There is a positive correlation between sleep and highly active minutes
  • The more sleep a user gets the more highly active minutes the user will partake in the following day

User Tracking

  • Highly Active users (25 or more logins) accounted for almost 88% of activity tracking
  • Highly Active users accounted for only about 46% of sleep tracking
  • Lightly Active users (between 0 and 5 logins) accounted for about 3% of activity tracking
  • Lightly Active users accounted for one third (33.3%) of all sleep tracking

Recommendations:

  • To increase usage for sleep tracking, feature health & wellness articles about the importance of sleep through mobile app and email
  • Send daily notifications about sleep and evening reminders for bedtime and tracking
  • If user is reporting low activity for the day, send an alert by 10 am to keep user goals on track
  • Create reminders for peak activity at 4pm if user has not reached daily fitness goals
  • Send users weekly reports on physical activity
  • Send notifications of their lowest activity day from the previous week on the same day next week
  • Send meditation reminders for high stress detection and days below recommended sleep
  • Create daily, weekly, and monthly challenges for users, or have them select their own challenges and goals
  • Additional data from Bellabeat users and products could be used for comparison to other smart devices to expand on user usage comparison

Marketing Strategy Since the Covid-19 pandemic and a shift in working from home, it is recommended that the marketing department continue and increase their investment opportunities in digital ads on:

  • Women’s health and wellness websites
  • YouTube ads for at home exercises & fitness
  • Podcasts on wellness, fitness, and entertainment (ads & sponsorship)

Advertisement time would be beneficial between 4 - 7 pm when people are getting off work and starting their peak time of physical activity.

Thank you for your interest in my Case Study!
Regards,
Jonathan Hill