Bellabeat - Google Data Analytics Capstone

• Reliability & Originality: Third-party survey data from Amazon Mechanical Turk.
• Comprehensive & Current: Limited sample size (30 users) over a short timeframe (1 month) and outdated date (2016), no demographic data and missing seasonal trends.
• Cited: Dataset is publicly available and cited via Kaggle.

-- Before : 4/12/2016 3:00:00 AM
PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', ActivityHour) AS ActivityHour
-- After  : 2016-04-12 03:00:00

The FORMAT DATETIME() replaced minute and second portion (MM:SS) of activity_datetime with 00:00. I used GROUP BY id and Hour to prevent everyone's heart rate at specific hour from being merged together. Lastly, AVG() was used to aggregate the available data of a user within the hour.

SELECT 
  Id,
 FORMAT_DATETIME('%Y-%m-%d %H:00:00',
   PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', Time)
   ) AS Hour,
 AVG(Value) AS avg_hr
FROM 
 `bellabeat-capstone-26.capstone.heartrate_sec`
GROUP BY 
 Id, Hour

Since I already imported the master table on Tableau, I decided to go back on BigQuery created a lightweight categorical mask from daily_activity table. Because Bellabeat's users were mainly women, I decided to use the average daily steps and daily calorie burned (for women) to categorize users' activity levels. Using two metrics helps in preventing possible categorization errors due to sensor miscalculations.

activity_mask as
(
  SELECT DAILY.Id,
  CASE
    WHEN AVG(DAILY.TotalSteps) < 5000 OR AVG(DAILY.Calories) < 1800 THEN 'Sedentary'
    WHEN AVG(DAILY.TotalSteps) < 7500 OR AVG(DAILY.Calories) < 2000 THEN 'LightlyActive'
    ELSE 'Active'
  END AS Activity_Level
FROM `bellabeat-capstone-26.capstone.daily_activity` AS DAILY
GROUP BY DAILY.Id
)

Since hourly calorie burned is a key part for the analysis, I used it as the base of the joins. Grouping the results by Id and Hour makes it so that calories, heart rate, and other measures are properly synced with time.

WITH all_hourly_clean AS (
  SELECT
    CAL.Id,
    CAL.ActivityHour,
    CAL.Calories,
    HR.HrValue,
    MET.MetVal,
    STEPS.StepTotal
  FROM calories_hour_clean AS CAL
  LEFT JOIN heartrate_hour_clean AS HR
    USING(Id, ActivityHour)
  LEFT OUTER JOIN met_hour_clean AS MET
    USING(Id, ActivityHour)
  LEFT OUTER JOIN steps_hour_clean as STEPS
    USING(Id, ActivityHour)
)

The logical join matches tables using both Id and Activity Date (DD/MM/YYYY). Because Activity Hour has DATETIME (DD/MM/YYYY H:MM:SS XM), I created a relationship calculation that converts Activity Hour to DATE with DATE([Activity Hour]). Similar step was performed for the SleepDay_merged table.

WITH calories_hour_clean AS
(
  SELECT DISTINCT SAFE_CAST(TRIM(Id) as int64) as Id,
  PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', ActivityHour) AS ActivityHour,
  SAFE_CAST(Calories as int64) as Calories
  FROM `bellabeat-capstone-26.capstone.calories_hour`
  WHERE Id IS NOT NULL
    AND ActivityHour IS NOT NULL

)

Users heavily favor doing workouts immediately after standard working hours.

Lower daytime activity can affect sleep quality.

To compensate the limited time, active users prioritized short but high intensity workouts.