This is a follow-up post on Strava: Analyse your fitness using Power BI. Go read that post to understand the basics of the PowerBI connector for Strava and how to use it.
Having a group of people to run together or to track your running activities together is a great motivator to get outside and exercise. Strava knows this and supports this by offering clubs. You can create a club for your running squad and get a view on your friends’ activities. It gamifies the running experience, with a leaderboard of who ran the most in the last week. You can even use it to plan runs together.
The dashboard on Strava for clubs is limited. It only shows you the graph of everyone’s current week progress, and a leaderboard for the previous week. You can see an example of this below.
This post digs deeper into the Strava PowerBI data connector to see what data we can fetch from the Strava API to get more insight into our club actvitities.
Strava PowerBI data connector
The PowerBI connector by Kasper on BI is limited to 2 Strava API endpoint: Athlete and Athlete Activities. However, the Strava API allows us to connect to our club as well.
I’ve modified the connector code to include the following 2 endpoints:
- /clubs/{id}: Returns a given club using its identifier.
- /clubs/{id}/activities: Retrieve recent activities from members of a specific club. The emphasis is on recent. To respect the privacy of your club members, the information that you can get is limited.
The modified connector can be found on my GitHub. See my previous article for instructions on how to build and use the connector.
https://github.com/gerryhendrickx/PowerBIStravaConnector
Finding and setting the club ID
To fetch the information of your club, you’ll need to set the club ID in the same way as we’ve set the client_id and client_secret. Currently the connector only works with one club at a time.
To find the club ID, browse to the club page on Strava and look at the URL. On the landing page of the club, the URL will probably still contain the club name instead of the ID, but if you click on, for instance, recent activities, the URL will change to include the club number as shown below:
Add a file to the connector project called club_id and paste in the club id. Make sure to not include any spaces.
The club_id will be fetched by the connector using the code below:
club_id = Text.FromBinary(Extension.Contents("club_id"));I won’t go into the other code changes. They are basically a copy/paste with some modifications.
Data in PowerBI
When you build the code and update the connector in PowerBI, you’ll be able to get more data:
A view on the data in the tables:
- Club: For the club itself, we retrieve a lot of metadata, ranging from the type, cover photo, number of members, name, profile picture, type of activities,… You can use this to provide some background on the club in your dashboards
- ClubActivities: The activities of your club members are limited to the last month. In addition, the API operation returns an object SummaryActivity which doesn’t contain the full details of the activity. We’re limited to the name of the athlete and the activity, along with the distance, moving time, elapsed time, total elevation gain and type of activity. We don’t even have the start date and time of the activity.
Some insights
Given it’s limitations, there’s not a whole lot of insights we can gather from this data. I did build a small dashboard showing:
- The club profile picture
- The club name
- Number of members
- Over the last month, the sum of all distances per member: Who ran the furthest?
- Over the last month, the average speed per member: Who ran the fastest?
You can play around with the elevation and moving time, but without a start date and time, that’s about it.
Conclusion
It was a fun exercise to extend the Strava PowerBI connector, but unfortunately the API to get information about your club members is fairly limited. This is understandable because you’re fetching other people’s data.
To get a more in depth insight into your club performance, we’d have to build an app that allows all club members to authenticate and grant permission to use their activities. That way, we’d have the full data set of all members and we can go a lot deeper into the analysis.
But that’s a topic for another post!