Transforming Apple Watch health data to provide richer insights with using the Google Ecoysystem - Google Sheets + Big Query + Data studio
Apple Health provides some good data but it's also quite limiting as well, I wanted to dig into the data more and surfass richer insights to understand the impact of my gym sessions which is very much linked to my strectching I do at least once a month. The common view is to do 10K steps a day which is a great benchmark to have but it was not giving me the insights I wanted. The goal was to move away from step count which I was using as the success metric.
The data collection prcoess is the biggest step and getting it right is critical. The process post the data collection should be smoother.
-
To get the data exported out of Apple Watch it requires using Health Auto Export (or similar) - https://apps.apple.com/us/app/health-auto-export-json-csv/id1115567069 it provides the ability to get the data into CSV.
The core metircs that need to be tracked at a daily level:
- Step Count
- Heart Rate
- Distance (KM)
-
Get the data into Google Sheets using this template (do not change headers) - https://docs.google.com/spreadsheets/d/1rJIg44mvjqyTCQPpO0dZ13MtRhzehohWx9Nis5gkFgs/edit?usp=sharing
The key addtional requirement in the Google Sheet is the tracking if you have visited the Gym, which is tracked as Y(Yes) and N(No).
-
It also requires having Big Query setup. Creating a dataset & table (using the Google Sheet)
From the data available in the Google Sheet, I have developed three custom KPI's to better measures cardiovascular from 'movement' to 'performance'
- Formula:
Total Steps / Average Heart Rate - Concept: This measures the body’s "fuel economy."
- Goal: Move more while keeping the heart rate lower. A higher number indicates a more "economical" and athletic cardiovascular system.
- Formula:
Efficiency MPG x 30(Standardized Multiplier) - Concept: This translates the "Economy" of the body into a "Performance Index."
- The Multiplier: A factor of x30 creates a visual scale that clearly distinguishes "Peak" performance days from "Maintenance" days. It provides a standardized daily "Work Volume" score.
Notes - Tested out the multiplier first of x 20 then x 30. Using a multiplier of x 30 for Capacity Score provided a metric to visualise total output on a scale that allows to see good v average days turning into a performance index.
- The "Turbocharger" Metric: While Capacity Score measures total volume, Avg Steps per Session measures Intensity.
- Insight: Capacity Score alone lacks context. High-value insights come from pairing volume with intensity—proving the engine isn't just running longer, but harder during training sessions.
Using Big Query utilizing a 90-day rolling look-back period to look into the future.
90-day lookback was selected for 2 reasons:
- Relevancy - 90 day windows captures the most recent trends and ensures forecasts are releastic and attainable. Looking back at 6 months of data is a poorer predictor in fitness. THe most recent is the most accurate which is why 90-days is the sweet spot
- Volatility - Using the 90-day average the forecast is not massively impacted by a massive spike when travelling or a netflix and chill day
- Step Baseline (Forecasted Step Count) - The predicted volume required to maintain current fitness based on the last 3 months of behavior.
- Step Count (Actual Step Count) - The step count that was inputted into the Google Sheet
- Step Stretch Goal (5% Growth) - A +5% "Progressive Overload" target. Consistently hitting this goal "pulls" the baseline upward over time, expanding the engine's total capacity
How the data gets updated into Google Sheets can impact the forecasting: There are 2 routes - Real Time v Fixed Benchmarking
- Fixed Benchmarking - To understand April data, only upload April's data on 1st May meaning the forecasted data for April which is based on January to March is frozen
- Real Time - If April's data is updated on 16th April for the first 15 days that will change the forecast for April
The 90-day had a high predictive accuracy with a 2% difference between actual step count v forecasted step count
I compared 90 day v 14 day v 30 day rolling forecast
- 30-day has a 1% difference v actual step count. This helps as my routine is set and it’s quite predictable
- 14-day has a 75% difference v actual step count. It’s too volatile with sample size too small to predict performance
This project utilizes two primary BigQuery views to separating real-time efficiency metrics from forecasting. Below is the breakdown of every dimension and metric used in the model.
| Field | Definition |
|---|---|
| Date | The primary calendar key for each activity entry. |
| year_number | Numeric year for long-term annual trend analysis. |
| month_number | Crucial Sorting Field: Prevents alphabetical sorting (April before August). |
| month_name | The display label for charts (e.g., "April", "May"). |
| month_year | Unique string (YYYY-MM) used to keep a continuous timeline across multiple years. |
| week_number | Groups data into 7-day cycles to identify weekly habit patterns. |
| Gym_YN | A Boolean flag identifying days with intentional workout sessions. |
| activity_segment | Behavioral classification (e.g., "Gym Routine", "Travelling") based on step volume. |
| data_segment | Distinguishes between "Actuals" (past data) and "Planned/Future" (forecasted dates). |
| lifestyle_era | Qualitative label to track major life phases (e.g., "Post-Pandemic") for historical context. |
| Field | Definition |
|---|---|
| gym_count | A running tally of total gym sessions within a specific period. |
| is_high_travel_day | A binary flag for days exceeding 20k steps; used to isolate outliers from training data. |
| Efficiency_MPG | Cardiovascular Economy: Calculates physical output per heartbeat (Steps / Heart Rate). |
| Capacity_Score | The Performance Index: Standardizes daily efficiency into a 30-point scale for visual indexing. |
| Field | Definition |
|---|---|
| actual_distance | Real-world kilometers covered based on Apple Watch GPS/pedometer data. |
| capacity_actual | The calculated capacity score for days where active data exists. |
| step_baseline | Maintenance Forecast: The 90-day rolling average of step volume. |
| step_stretch_goal | Growth Forecast: Baseline + 5% target for progressive overload. |
| distance_baseline | The 90-day rolling average of total distance (km) covered. |
| distance_stretch | Distance Baseline + 5% target for endurance growth. |
| capacity_baseline | The 90-day rolling average of your Efficiency MPG x 30. |
| capacity_stretch | Capacity Baseline + 5% target for cardiovascular system growth. |
| capacity_variance | The Performance Gap: The delta between current output and the forecasted baseline. |
This dashboard covers my apple watch health data from January 2024 onwards
https://datastudio.google.com/reporting/803e5c48-fb6d-4721-8dad-9ee0ab49e25f
A copy of the dashboard can be made, linked to the Big Query views to build your own health view
https://datastudio.google.com/reporting/29fdaf9a-2cf2-4616-9640-92cf41a6dcda
The dashboard is structured into three layers:
- Focus: Total absolute step volume per month.
- Insight: Provides a macro view of physical activity across years to identify seasonal trends and long-term volume growth.
- Focus: Tracks the number of intentional workout sessions completed each month.
- Insight: Acts as a lead indicator for performance; higher gym frequency typically precedes surges in cardiovascular efficiency.
- Focus: A historical heat map of Step Count, Gym Count, Capacity Score, and Efficiency MPG.
- Insight: Provides an at-a-glance comparison of annual performance, highlighting 2025 as a peak efficiency year with a record 201 gym sessions.
- Focus: Maps real-time daily output against the 90-day rolling baseline.
- Insight: Extends the timeline through December 2026, visualizing the "Maintenance Runway" to show required volume for future months.
- Focus: A targeted look at the variance between current monthly output and the baseline/stretch goals.
- Insight: Immediately identifies if the "Engine" is currently in a state of growth (hitting targets) or recovery (falling below baseline).
- Focus: Tracks the relationship between Efficiency MPG (Economy) and Capacity Score (Volume).
- Insight: Visualizes how improvements in cardiovascular health allow for higher work volumes without increased physiological strain.
- Focus: Correlates training intensity with aerobic ROI.
- Insight: Demonstrates that the 19% efficiency surge in 2025 was directly driven by maintaining a high intensity of ~9k steps per session.
- Focus: A bubble chart identifying the intersection of high mechanical output and low heart rate.
- Insight: Pinpoints the "Efficiency Ceiling"—the exact intensity level where the body operates at its most economical state.
- Focus: Comparative scatter plot mapping heart rate against step count.
- Insight: Visualizes the "shift" in performance, proving that newer training strategies have moved the cluster toward higher volume at lower relative heart rates.
- Focus: Stacked bar chart categorizing all activity into tiers (Gym, Exploring, Travelling, etc.).
- Insight: Shows the "Activity Mix" change over time, highlighting how intentional gym sessions have become the dominant driver of total volume.
Developed a strategic planning and forecasting tool in Google Sheets to help plan. It goes from insights into daily action.
https://docs.google.com/spreadsheets/d/1Q2cBNVyj1n206IFyTJT0eU4nLHXotpTktqlHwN0YWs0/edit?usp=sharing
The key features is the run-rate analysis providing what the monthly and daily steps need to be acheive the yearly goal.
How the Google Sheet works:
- B2 - Input last years step count
- B3 - Input the target for the upcoming year
- Column F input the actual step count
The key insights come from:
- B7 - The expected run rate to date
- B8 - The progress to date
- B9 - The variance between expected and progress to date
- Column H provides an updated step count for the upcoming momnth v target
- Column I provides an update avg daily step count v target