Creating a dashboard for my Strava runs

My running schedule this year had its ups and downs. Started quite well in January and February, then during the March-April lockdown it went really really well, but summer was a bit more stagnant. My goal for the year was to run 1000km and I wanted to have a cool way to visualize my progress.

Strava provides some data, but I kept finding myself checking how much more I’ve got to run and calculating how many weeks we’ve got to see the required weekly average.

On top of that, I wanted to have full visibility on my progress and be able to see it in a chart format. Strava has tons of data, and the opportunities to visualize are endless.

So I decided to create a running dashboard that gets updated automatically on every new run. I decided to keep it simple with Google Sheets and Google data studio for visualization.

Initially I would need to get my old runs from Strava to a Google Sheet. Then, get a trigger for every new run to get added in the sheet and use Google Data Studio to visualize.

Getting data from Strava

Luckily for me, there was a great post to help me get my old runs in a Google Sheet:

https://www.benlcollins.com/spreadsheets/strava-api-with-google-sheets/

This was really great because it helped with the hard part of creating a new app and authorizing it for Strava and providing the core app.

However I did want a few more things so I edited a bit the code to to map all available Strava fields (I was particularly interested in Location coordinates, gear, and other cool stats such as elevation).

The initial mapping was easy, I just checked Strava API reference and added the rest of them. This is how the array.push looks like now:

arr.push(
      activity.id,
      activity.name,
      activity.type,
      activity.distance,
      activity.moving_time,
      activity.elapsed_time,
      activity.total_elevation_gain,
      activity.start_date,
      activity.start_date_local,
      activity.timezone,
      activity.utc_offset,
      startlat+", "+startlon,
      endlat+", "+endlon,
      activity.location_city,
      activity.location_state,
      activity.location_country,
      activity.achievement_count,
      activity.kudos_count,
      activity.comment_count,
      activity.athlete_count,
      activity.photo_count,
      activity.trainer,
      activity.commute,
      activity.manual,
      activity.private,
      activity.flagged,
      activity.gear_id,
      activity.from_accepted_tag,
      activity.average_speed,
      activity.max_speed,
      activity.average_cadence,
      activity.average_watts,
      activity.weighted_average_watts,
      activity.kilojoules,
      activity.device_watts,
      activity.has_heartrate,
      activity.average_heartrate,
      activity.max_heartrate,
      activity.max_watts,
      activity.pr_count,
      activity.total_photo_count,
      activity.has_kudoed,
      activity.suffer_score,
      activity.workout_type,
      activity.external_id,
      activity.upload_id
    );

The only tricky thing was the location which was an array and not always available, so I added a bit of very simple code just before the array.push:

//check if GPS location is empty
    if(activity.start_latlng == null){
    // array is empty 
      var startlat = 0;
      var startlon = 0;
      var endlat = 0;
      var endlon = 0;

    } else {
    //array not empty
      var startlat = activity.start_latlng[0];
      var startlon = activity.start_latlng[1];
      var endlat = activity.end_latlng[0];
      var endlon = activity.end_latlng[1];
    }

The result was something like this:

Update spreadsheet on every new run

We’ve now added all the past runs, but we want to update automatically on each new run. We need a service that adds a new row of data for each new run, and get the gear information.

I wanted to use a no-code solution for that. This could have easily been done with Zapier, but I decided to use Pipedream , which a great tool that can do a lot of such workflows and you can edit code in JavaScript.

The Pipedream workflow is the following:

• New run gets triggered
• Add a single row to Google Sheet
• End workflow

The mapping looks like this:

Pipedream also allows workflows to be public and shareable, so here is the link to the complete workflow:

https://pipedream.com/@tanastaselos/new-strava-activity-to-gsheet-p_RRCMqQ

I also used Pipedream to get the gear data for my old runs. I used a new sheet in my spreadsheet which has all my gear, and I’m using index-match in Google Sheets to match it.

In my runs sheet, I add a column with this function:

=iferror(INDEX(Gear!F:F, match(AA2, Gear!A:A, 0),""))

I also added the Click to update link. This links to a webhook in Pipedream to update gear once I get new shoes.

The complete Pipedream workflow to update my gear is here:

https://pipedream.com/@tanastaselos/get-strava-gear-p_wOC1qy

Finally I also added a few more columns with data transformation in Google Sheets to help me with my reports, such as Weekday, Day of Month, Week number, a sequential number etc. I heavily used the arrayformula function to make sure that I don’t need to copy paste functions for all my rows. The columns I added are:

Date - Strava provides dates in datetime format, I added a simple date in case needed:

=arrayformula(iferror(if(I2:I="", "", datevalue(left(I2:I, 10))), ""))

Time - as above, I added a separate time field to be able to use it in order to classify activities per time of day

=arrayformula(iferror(if(I2:I="", "", timevalue(left(RIGHT(I2:I, 9), 8))), ""))

Weekday - Which day of the week. To be used in the Bubble chart below

=arrayformula(iferror(if(AU2:AU="", "", WEEKDAY(AU2:AU, 2)), ""))

Day - Day of the month

=arrayformula(iferror(if(AU2:AU="", "", day(AU2:AU)), ""))

Month

=arrayformula(iferror(if(AU2:AU="", "", MONTH(AU2:AU)), ""))

Week number - to be used in the bubble chart

=ARRAYFORMULA(if(AU2:AU="","", WEEKNUM(AU2:AU)))

Year - this also serves as an individual filter

=arrayformula(iferror(if(AU2:AU="", "", year(AU2:AU)), ""))

Date Sequence - finally a sequential number of all my runs

=arrayformula(if(AU2:AU="", "", row(AU2:AU)-1))

After all the calculation, my sheet looks like this:

Creating the reports in Google Data Studio

Now that we’ve got all our data, time to create the dashboard. I used Google Data Studio as it is a free tool with a lot of functionality with great Google Sheets integration.

After I create a new project and connect my Google Sheet, I start playing with the data. At first, I created 4 reports:

• Overall activity summary report
• Running board
• Runs bubble chart (inspired by the Strava-to-Sheets post )
• Map on where I have run

Summary report

The summary report has an overview of the activities split by Run/Swim/Ride.

The first stats are simply a filter on the activity type:

And the filter is set like this:

I also added similar filters per time of day and then split by Morning/Afternoon/Evening activities.

Finally, I’ve added a table with the gear and number of activities & distance for each gear type.

The result is the below:

Runs report

This report is focused on running, so the run filter applies to each of the metrics.

Apart from the stats, I’ve added a target parameter to define my target goal for the period. This parameter is then passed to other fields to calculate the remaining distance to reach my goal for the year, what is the average distance per week etc. There is also an input field to change this target depending on reporting period, if plans change etc. The default is set to 1000km per year.

The remaining distance is calculated as

Target-sum(Distance (km))

while the weekly run needed as

(Target-sum(Distance (km)))/((DATE_DIFF(DATE(YEAR(TODAY()), 12, 31), TODAY()))/7)

The end result was this:

Weekly / day of the week heatmap

In this section I recreated the bubble/heatmap report. Not much more to add from the post linked above, and the result was this:

Map my runs

Finally I wanted to have a visual of where have I run. A simple map with location from the coordinates resulted to this:

This wraps up the first iteration of my Strava dash-boarding. Up next - perhaps some more fancy charts, maybe enriching the data even further – who knows 🙂.

I hope you enjoyed - ping me for any questions on how to achieve the same dashboards, or if you have any improvement ideas!