Capstone: Bellabeat Case Study SQL

On this Case Study I used the same dataset on my previous work Capstone: Bellabeat Case Study with R but this time Im using soley on SQL and Excel to showcase my skill.

Summary

Bellabeat is a high-tech company that manufactures health-focused smart products.They offer different smart devices that collect data on activity, sleep, stress, and reproductive health to empower women with knowledge about their own health and habits.

The main focus of this case is to analyze smart devices fitness data and determine how it could help unlock new growth opportunities for Bellabeat. We will focus on one of Bellabeat’s products: Bellabeat app and Leaf.

The Bellabeat app provides users with health data related to their activity, sleep, stress, menstrual cycle, and mindfulness habits. This data can help users better understand their current

The Leaf is Bellabeat’s classic wellness tracker can be worn as a bracelet, necklace, or clip. The Leaf tracker connects to the Bellabeat app to track activity, sleep, and stress habits and make healthy decisions. The Bellabeat app connects to their line of smart wellness products

Ask Phase

Business Task

Identify trends in how consumers use non-Bellabeat smart devices to apply insights into Bellabeat’s marketing strategy.

Stakeholders

• Urška Sršen - Bellabeat cofounder and Chief Creative Officer
• Sando Mur - Bellabeat cofounder and key member of Bellabeat executive team
• Bellabeat Marketing Analytics team

Prepare Phase

Dataset used:

The data source used for our case study is FitBit Fitness Tracker Data. This data set is stored in Kaggle and was made available through Mobius.

Accessibility and privacy of data:

Verifying the metadata of our data set we can confirm it is open-source. The owner has dedicated the work to the public domain by waiving all of his or her rights to the work worldwide under copyright law, including all related and neighboring rights, to the extent allowed by law. You can copy, modify, distribute and perform the work, even for commercial purposes, all without asking permission.

Information about our dataset:

These data sets were generated by respondents to a distributed survey via Amazon Mechanical Turk between 03.12.2016-05.12.2016. Thirty eligible Fitbit users consented to the submission of personal tracker data, including minute-level output for physical activity, heart rate, and sleep monitoring. Variation between output represents use of different types of Fitbit trackers and individual tracking behaviors / preferences.

Cleaning the data using Excel

The following steps were taken within each dataset:

• Sorted and filtered data by Id to obtain how many unique users there were within the dataset.
• Checked for duplicate data using the ‘duplicate data’ tool in Excel
• Formatted date data into MM/DD/YY date format
• Formatted all numerical data into Number format with either no decimils or up to 2 decimials.
• Sorted by date to find the first and last date of the dataset (this is what first indicated only a 31-day period of activity was captured).
• Separated Date and Hour into two columns when needed for later analysis. Utilized the ‘Text to Columns’ tool to do so.
• Formatted any time data into 00:00:00 format for consistency.
• Checked Id entries and other columns for LEN to make sure the data was correct and uniform in length

After the cleaning process was finished, only 3 rows of duplicate information was found within the Daily_Sleep_Merged file. These were removed before analysis.

Process phase

In this analysis I will focus on Bigquery SQL and MS excel and to be able to create data viz for the stakeholders.

Importing dataset

I opened Bigquery Console, then select “Create Project”. Typed down the name of the project you are going to explore, in this case I used first-analyst. I created a new dataset for Bellabeat and named it bellabeat_data. Inside bellabeat dataset, I imported the .csv datasets I previously downloaded from FitBit Fitness Tracker Data.

• Daily_Activity_Merged
• Daily_Sleep_Merged
• Hourly_Steps_Merged

After that, I started my work by finding the total number of users’ id

Number of users

SELECT 
  COUNT( DISTINCT Id)
FROM
  `first-analyst.bellabeat_data.hourly_steps` -- 33


SELECT 
  COUNT(DISTINCT Id)
FROM 
  `first-analyst.bellabeat_data.daily_activity` --33
  
  
SELECT 
  COUNT(DISTINCT Id)
FROM 
  `first-analyst.bellabeat_data.daily_sleep` -- 24

Checking Start-End Date and Id

SELECT 
  MIN(Date) as start_date,
  MAX(Date) as end_date
FROM 
  `first-analyst.bellabeat_data.daily_activity`

SELECT 
  MIN(Date) as start_date,
  MAX(Date) as end_date
FROM 
  `first-analyst.bellabeat_data.daily_sleep`

SELECT 
  MIN(Date) as start_date,
  MAX(Date) as end_date
FROM 
  `first-analyst.bellabeat_data.hourly_steps`

  -- daily activity, daily sleep, hourlysteps are same startdate: 2016-04-12, enddate: 2016-05-12. 31 days in total

Check all ids have the same length

SELECT 
  Id
FROM 
  `first-analyst.bellabeat_data.daily_activity`
WHERE
  LENGTH(CAST(Id as STRING)) > 10 OR LENGTH(CAST(Id as String)) < 10

  -- No data display meaning there are no Id more than or less than to 10
  
  SELECT 
  Id
FROM 
  `first-analyst.bellabeat_data.daily_sleep`
WHERE
  LENGTH(CAST(Id as STRING)) > 10 OR LENGTH(CAST(Id as String)) < 10

  -- No data display meaning there are no Id more than or less than to 10


SELECT 
  Id
FROM 
  `first-analyst.bellabeat_data.hourly_steps`
WHERE
  LENGTH(CAST(Id as STRING)) > 10 OR LENGTH(CAST(Id as String)) < 10

  -- No data display meaning there are no Id more than or less than to 10

It showed that all datasets have the same start and end date: start 2016-04-12 and end 2016-05-12. In term of id’s length, all datasets also showed the same length: 10 characters.

Cleaning the data

Finding Duplicates

Note: I just repating this below code to check for daily sleep and hourly steps

SELECT 
  Id,
  Date,
  COUNT(*) as num_of_id
FROM 
  `first-analyst.bellabeat_data.daily_activity`
GROUP BY
  Id, Date
HAVING 
  num_of_id > 1

-- no data to display / no duplicates in daily_activity
  
SELECT 
  Id,
  Date,
  COUNT(*) as num_of_id
FROM 
  `first-analyst.bellabeat_data.hourly_steps`
GROUP BY
  Id, Date
HAVING 
  num_of_id > 24
-- I put 24 because it is 24 hours in a day / no display no duplicates
  
  
SELECT 
  Id,
  Date,
  COUNT(*) as num_of_id
FROM 
  `first-analyst.bellabeat_data.daily_sleep`
GROUP BY
  Id, Date
HAVING 
  num_of_id > 1

-- displays 3 duplicates

According to the result of finding duplicates, it showed that there are 3 duplicate rows in sleep_day dataset. We need to create a new sleep_day table, and remove the duplicates in the new table. In this case, I named the new table: daily_sleep_new.

Duplicate rows in daily_step table need to be removed

Creating and replacing new sleep_day table with all distinct values

CREATE or REPLACE TABLE `first-analyst.bellabeat_data.daily_sleep_new`
AS SELECT *
FROM
(
  SELECT *, 
  ROW_NUMBER() 
  OVER (PARTITION BY Id, Date)
  row_number
  FROM `first-analyst.bellabeat_data.daily_sleep`
)
WHERE row_number = 1


-- Check it again if it the new table had no duplicates

SELECT
  Id,
  Date,
  COUNT(*) as num_of_id
FROM `first-analyst.bellabeat_data.daily_sleep_new`
GROUP BY
  Id, Date
HAVING 
  num_of_id > 1

  -- no data display/ no duplicates

Removing the unwanted Data

During the checking and cleaning process, I found that there were some zero data in TotalSteps column inside the daily_activity dataset. Therefore, I decided to check and remove those zero value. I created new table and named it daily_activity_new, so that the previous dataset still remained.

--Check if total steps = 0 in daily_activity table
SELECT 
  Id, 
  Count(*) as num_of_zero_steps
FROM `first-analyst.bellabeat_data.daily_activity`
WHERE 
  Total_Steps = 0
GROUP BY Id
ORDER BY num_of_zero_steps

  -- 15 ids with 0 total steps

-- Create new daily activity table
CREATE TABLE `first-analyst.bellabeat_data.daily_activity_new`
AS SELECT *
FROM `first-analyst.bellabeat_data.daily_activity`


-- Delete all rows that contain zero total steps
DELETE FROM `first-analyst.bellabeat_data.daily_activity_new`
WHERE Total_Steps = 0
--77 data deleted


-- Removing the zero value on Hourly steps
SELECT 
  Id, 
  Count(*) as num_of_zero_steps
FROM `first-analyst.bellabeat_data.hourly_steps`
WHERE 
  Step_Total = 0
GROUP BY Id
ORDER BY num_of_zero_steps
-- 33 ids with 0 total steps

DELETE FROM `first-analyst.bellabeat_data.hourly_steps`
WHERE Step_Total = 0
-- 7535 deleted

Find the null data

--Check for null data
SELECT *
FROM `first-analyst.bellabeat_data.daily_activity_new`
WHERE Id IS NULL
-- no data display

SELECT *
FROM `first-analyst.bellabeat_data.daily_sleep_new`
WHERE Id IS NULL
-- no data display

SELECT *
FROM `first-analyst.bellabeat_data.hourly_steps`
WHERE Id IS NULL
-- no data display


--Delete rows of null data
DELETE FROM `first-analyst.bellabeat_data.daily_activity_new`
WHERE Id IS NULL

Analyze Phase and Share Phase

We were going analyze the trends of FitBit user and to determine if can help us to make a decision for marketing strategy

User Level

We want to determine the type of users with the data we have because we don’t have any demographic variables from our sample. We can categorize users based on their daily number of steps. Users can be classified as follows:

• Sedentary - Less than 5000 steps a day.
• Lightly active - Between 5000 and 7499 steps a day.
• Fairly active - Between 7500 and 9999 steps a day.
• Very active - More than 10000 steps a day. Classification has been made per the following article https://www.10000steps.org.au/articles/counting-steps/

--Creating temp table for the mean of daily steps
WITH
  daily_average AS (
  SELECT
    Id,
    AVG(Total_Steps) AS totalsteps_mean,
  FROM
    `first-analyst.bellabeat_data.daily_activity_new`
  GROUP BY
    Id
  ORDER BY
    totalsteps_mean  
  
),
--After getting all the total mean, we will now categorize each user base on User Level 
 users AS (
SELECT 
  Id, 
  AVG(totalsteps_mean) as avg_total_steps,
  CASE
  WHEN AVG(totalsteps_mean) < 5000 THEN 'Sedentary'
  WHEN AVG(totalsteps_mean) BETWEEN 5001 AND 7500 THEN 'Lightly Active'
  WHEN AVG(totalsteps_mean) BETWEEN 7501 AND 10000 THEN 'Fairly Active'
  WHEN AVG(totalsteps_mean) > 10000 THEN 'Very Active'
  END AS user_level
FROM daily_average
GROUP BY
  Id
ORDER BY avg_total_steps

),
 user_level_counts AS (
    SELECT user_level, COUNT(*) AS total
    FROM users
    GROUP BY user_level
  ),
  total_user_level_counts AS (
    SELECT SUM(total) AS total_user_level
    FROM user_level_counts
  ),
  user_level_percentages AS (
    SELECT user_level, CAST(total AS FLOAT64) / total_user_level_counts.total_user_level AS total_percent
    FROM user_level_counts, total_user_level_counts
    WHERE 1 = 1
  )
SELECT user_level,
total_percent,
FROM user_level_percentages

User Level

Spreedsheet data

Steps and minutes asleep per weekday

We want to know now what days of the week are the users more active and also what days of the week users sleep more. We will also verify if the users walk the recommended amount of steps and have the recommended amount of sleep.

Below we are calculating the weekdays based on our column date. We are also calculating the average steps walked and minutes asleep by weekday.

WITH
-- Merging  two tables
  daily_activity_sleep  AS (
    SELECT
    Total_Steps,
    TotalMinutesAsleep,
    daily_activity_new.Id AS id,
    daily_activity_new.Date AS date
  FROM `first-analyst.bellabeat_data.daily_activity_new` AS daily_activity_new
  INNER JOIN 
    `first-analyst.bellabeat_data.daily_sleep_new` AS daily_sleep_new
  ON
  daily_activity_new.Id = daily_sleep_new.Id  AND
   daily_activity_new.Date = daily_sleep_new.Date
   )


--Find the average of Total steps and Total minute asleep per week
SELECT 
  day_of_week, 
  ROUND(AVG(Total_Steps),2) as ave_totalsteps_perday,
  ROUND(AVG(TotalMinutesAsleep),2) AS ave_minutesasleep_perday
FROM
  (
  SELECT *,
  CASE
  WHEN (EXTRACT(DAYOFWEEK FROM date)= 1) THEN 'Mon'
  WHEN (EXTRACT(DAYOFWEEK FROM date)= 2) THEN 'Tue'
  WHEN (EXTRACT(DAYOFWEEK FROM date)= 3) THEN 'Wed'
  WHEN (EXTRACT(DAYOFWEEK FROM date)= 4) THEN 'Thu'
  WHEN (EXTRACT(DAYOFWEEK FROM date)= 5) THEN 'Fri'
  WHEN (EXTRACT(DAYOFWEEK FROM date)= 6) THEN 'Sat'
  WHEN (EXTRACT(DAYOFWEEK FROM date)= 7) THEN 'Sun'
  END AS day_of_week
  FROM daily_activity_sleep
  )

GROUP BY day_of_week

Steps and minutes asleep per weekday

Spreedsheet data

In the graphs above we can determine the following:

Users walk daily the recommended amount of steps of 7500 besides Sunday’s. based on the article above Users don’t sleep the recommended amount of minutes/ hours - 8 hours.

Hourly steps within a day

We were going to find out when the users more active throughout the day.

Hourly steps within a day

Spreedsheet data

As we can see the graph above user are more active 7:00AM to 9:00PM. We also user more likely walk more at 11:00AM to 2:00PM, rest at 3:00PM, and 5:00PM to 7:00PM in the evening.

Correlation

We will now determine if there is any correlation between different variables: - Daily steps and daily sleep - Daily steps and calories

Daily steps and daily sleep

Spreedsheet data

Based on our plots:

There’s is no correlation between the daily steps and minutes asleep. Basically walking daily does not affect the minutes of their sleep But, there is a correlation between the daily steps and calories. Basically the more user steps the more they burn calories.

Total days used by users

Now that we have seen some trends in activity, sleep and calories burned, we want to see how often do the users in our sample use their device. That way we can plan our marketing strategy and see what features would benefit the use of smart devices.

We will calculate the number of users that use their smart device on a daily basis, classifying our sample into three categories knowing that the date interval is 31 days:

• high use - users who use their device between 21 and 31 days.
• moderate use - users who use their device between 11 and 20 days.
• low use - users who use their device between 1 and 10 days.

First I will make a temp table.

WITH
  --Merging two tables with two primary key
  daily_activity_and_sleep AS (
    SELECT
    daily_activity.Id as Id,
    COUNT(*) as num_of_use
    FROM `first-analyst.bellabeat_data.daily_activity_new` as daily_activity
    INNER JOIN `first-analyst.bellabeat_data.daily_sleep_new` as daily_sleep
    ON daily_activity.Id = daily_sleep.Id AND daily_activity.Date = daily_sleep.Date
    GROUP BY Id
  ),
  #Filtering user usage based on daily sleep and activity of a users
  usages AS (
    SELECT 
  Id, 
  SUM(num_of_use) AS day_used, 
  CASE 
    WHEN SUM(num_of_use) BETWEEN 1 AND 10 THEN 'low use'
    WHEN SUM(num_of_use) BETWEEN 11 AND 20 THEN 'moderate use'
    WHEN SUM(num_of_use) BETWEEN 21 AND 31 THEN 'high use'
  END AS usage
FROM daily_activity_and_sleep
GROUP BY Id
  ),
  -- Counting the number of usage
  usage_summary AS (
    SELECT 
      usage, 
      COUNT(*) AS total
    FROM usages
    GROUP BY usage
  ),
  -- Getting the average of number of usage and total usage
  usage_percentage AS (
    SELECT 
      usage, 
      total, 
      total_usage, 
      CAST(total AS FLOAT64) / total_usage AS total_percentage
   -- Selecting it FROM usage summary, and finding the total usage 
    FROM (
      SELECT 
        usage, 
        total, 
        SUM(total) OVER () AS total_usage
      FROM usage_summary
    ) 
  )
SELECT 
  usage, 
  total_percentage, 
  CONCAT(CAST(ROUND(total_percentage * 100, 1) AS INT64), '% (', CAST(total AS INT64), ')') AS labels
FROM usage_percentage

Total days used by users

Spreedsheet data

The results on out graph are: - Between 21 and 31 days, 50% of users frequently use their phone. - 38% of users balance their phone usage between 11 and 20 days. - 12% of the users are rarely used their phone 1 to 10 days

Time used smart device

Being more precise we want to see how many minutes do users wear their device per day. For that we will merge the created daily_use data frame and daily_activity to be able to filter results by daily use of device as well.

WITH
  --Merging two tables with two primary key
  daily_activity_and_sleep AS (
    SELECT
    daily_activity.Id as Id,
    COUNT(*) as num_of_use
    FROM `first-analyst.bellabeat_data.daily_activity_new` as daily_activity
    INNER JOIN `first-analyst.bellabeat_data.daily_sleep_new` as daily_sleep
    ON daily_activity.Id = daily_sleep.Id AND daily_activity.Date = daily_sleep.Date
    GROUP BY Id
  ),
  --Filtering user usage based on daily sleep and activity of a users
  usages AS (
    SELECT 
  Id, 
  SUM(num_of_use) AS day_used, 
  CASE 
    WHEN SUM(num_of_use) BETWEEN 1 AND 10 THEN 'low use'
    WHEN SUM(num_of_use) BETWEEN 11 AND 20 THEN 'moderate use'
    WHEN SUM(num_of_use) BETWEEN 21 AND 31 THEN 'high use'
  END AS usage
FROM daily_activity_and_sleep
GROUP BY Id
  ),
  -- Counting the number of usage
  usage_summary AS (
    SELECT 
      usage, 
      COUNT(*) AS total
    FROM usages
    GROUP BY usage
  ),
  -- Getting the average of number of usage and total usage
  usage_percentage AS (
    SELECT 
      usage, 
      total, 
      total_usage, 
      CAST(total AS FLOAT64) / total_usage AS total_percentage
   -- Selecting it FROM usage summary, and finding the total usage 
    FROM (
      SELECT 
        usage, 
        total, 
        SUM(total) OVER () AS total_usage
      FROM usage_summary
    ) 
  ), 
  -- Creating new subquery for daily used
  daily_activity_used AS (
    SELECT *
    FROM  `first-analyst.bellabeat_data.daily_activity_new`, usage_summary
  ),

  minutes_worn AS (
   SELECT *,
   CASE 
    WHEN minutes_worn_percentage = 100 THEN 'All day'
    WHEN minutes_worn_percentage >= 50 AND minutes_worn_percentage < 100 THEN 'More than half day'
    WHEN minutes_worn_percentage > 0 AND minutes_worn_percentage < 50 THEN 'Less than half day'
   END as worn

  FROM (
     SELECT *, 
      (Very_Active_Minutes + Fairly_Active_Minutes + Lightly_Active_Minutes +
Sedentary_Minutes) as total_worn_minutes,
  (Very_Active_Minutes + Fairly_Active_Minutes + Lightly_Active_Minutes +
Sedentary_Minutes) / 1440 * 100 as minutes_worn_percentage
     FROM daily_activity_used
   )

  ),

-- As we have done before, to better visualize our results we will create new subqueries. 

worn_summary AS (
  SELECT 
  worn,
  COUNT(*) as total
  FROM minutes_worn
  GROUP BY worn
),
worn_percentage AS (
  SELECT
  worn,
  total,
  total_worn,

  FROM (
    SELECT
    worn,
    total,
    SUM(total) OVER() total_worn
    FROM worn_summary
  )

),



  minutes_worn_highuse AS (
    SELECT
    worn,
    total/totals AS total_percentage,
    CONCAT(ROUND(total / totals * 100, 2), '%') AS labels,
    FROM (
      SELECT 
      worn,
      COUNT(*) AS total,
      SUM(COUNT(*)) OVER () AS totals   -- Calculating the sum of the number of rows (which is total)
      FROM minutes_worn
      WHERE usage = 'high use'
      GROUP BY worn
    )
    GROUP BY worn, total_percentage,labels
  ),
  minutes_worn_moderateuse AS (
    SELECT
    worn,
    total / totals as total_percentage,
    CONCAT(ROUND(total/totals * 100, 2), '%') as labels
    FROM (
      SELECT
      worn,
      COUNT(*) as total,
      SUM(COUNT(*)) OVER() as totals 
      FROM minutes_worn
      WHERE usage = 'moderate use'
      GROUP BY worn

    )
    GROUP BY worn, total_percentage,labels
  ),
   minutes_worn_lowuse AS (
    SELECT
    worn,
    total / totals as total_percentage,
    CONCAT(ROUND(total/totals * 100, 2), '%') as labels
    FROM (
      SELECT
      worn,
      COUNT(*) as total,
      SUM(COUNT(*)) OVER() as totals 
      FROM minutes_worn
      WHERE usage = 'low use'
      GROUP BY worn

    )
    GROUP BY worn, total_percentage,labels
  )

-- SHOWING THE TOTAL USAGE OF ALL USER
-- SELECT 
-- worn,
-- total_percentage,
--  CONCAT(CAST(ROUND(total_percentage * 100, 1) AS INT64), '% (', CAST(total AS INT64), ')') AS labels
-- FROM worn_percentage


SELECT
*
FROM minutes_worn_lowuse

Conclusion and Recommendation

Based on our findings, I would recommend that we use our own tracking data for further analysis in order to respond to our business task and assist Bellabeat with their mission. Because we didn’t have any demographic information about users, the data sets we used had a small sample size and could be biased. Knowing that our primary demographic is young and adult women, I would encourage you to continue looking for trends in order to develop a marketing strategy centered on them.

That being said, after our analysis we have found different trends that may help our online campaign and improve Bellabeat app:

library(knitr)
recommendation <- c("Adding a 'goal' on the app", "Sleeping recommendation for users", "Level or rank badge system")
description <- c("Putting a goal to track daily steps and calories helps users monitor their progress towards their fitness goals.", 
                 "A sleep recommendation feature can remind users to maintain a healthy sleep routine and provide helpful tips for better sleep.",
                 "A level or rank system can motivate users to be more active by allowing them to track their progress and earn badges as rewards.")
tbl <- data.frame(Recommendation = recommendation, Description = description)
kable(tbl)

Recommendation	Description
Adding a ‘goal’ on the app	Putting a goal to track daily steps and calories helps users monitor their progress towards their fitness goals.
Sleeping recommendation for users	A sleep recommendation feature can remind users to maintain a healthy sleep routine and provide helpful tips for better sleep.
Level or rank badge system	A level or rank system can motivate users to be more active by allowing them to track their progress and earn badges as rewards.

NA
NA

On our analysis we dint just check for the trends but also we see that healthy users tend to be use their device and 36% of the users wear the device all time.

• It could be good to market this device who are more active and who want to track their daily activities We can continue promote Bellabeat’s products features:

• Worable device for men

• Water-resistant

• Long-lasting batteries

• Fashion/ elegant products