VizWiz

Data Viz Done Right

June 19, 2020

How to Get Your Data from Strava to Tableau

No comments
UPDATE: If you prefer video tutorials, there is now one on my YouTube channel here. The steps to get the links into the Simple Mass Downloader are done more efficiently in the video. There are several steps removed from the Excel and link uploads that are listed in the blog.




For a while now, I've been using this process for getting my data from Strava into Tableau like this:
  1. Download the data from Strava
  2. Prep it with Alteryx
  3. Visualize it in Tableau

In this post, I will outline the steps for getting your data from Strava into Tableau. I have also created a template with some sample vizzes for you to use. Download it from Tableau Public here.

I started revamping the process after wanting to create a small multiple view of all of my activities like this:


When I first started creating these, I took a process based on R and converted it into a workflow in Alteryx. This worked great, but there were two main drawbacks:

  1. My Alteryx workflow was a mess (I tinkered with it every month).
  2. Each of the routes would be adjusted to fit perfectly in a square.

This second problem meant that the routes were not scaled correct according to the curvature of the Earth. I then saw that Andy Cotgreave reached out to Ken Flerlage for help. They've written about their process here. Great! However, the calculations on this process make rendering the maps in Tableau very, very slow. Like REALLY slow.

That's where Alteryx comes in. I pushed all of the data processing into Alteryx, export each route as a single spatial object and every renders super fast. So if you're interested in getting all of your data out of Strava and visualizing it in Tableau, this process is for you. It does require Alteryx, but you could replicate the process in R.


TOOLS REQUIRED

  • A Strava account - NOTE: Make sure you are logged in to Strava throughout these steps.
  • Alteryx Designer
  • Tableau Desktop
  • Simple Mass Downloader Chrome extension

GET THE DATA FROM STRAVA

Step 1 - Login to Strava, click on your profile icon on the upper right, choose Settings.


Step 2 - On the My Profile page, click on My Account.


Step 3 - Scroll to the bottom and click on the Get Started button in the Download or Delete Your Account section.


Step 4 - In step 2, choose Request Your Archive. Don't do anything else on this page.




Step 5 - Check your email in a few minutes. When it arrives, click on the Download Archive button in the email.



Step 6 - Unzip the file that downloads. Open the folder that's created and you should see something like this.

The activities folder contains all of the raw files, but these won't all be in the same format, which means they're useless. The ONLY file we need is activities.csv. Open the CSV in Excel.

Step 7 - The only column we need in the file is the Activity ID column.


Delete all columns except Activity ID.


Step 8 - Insert a column to the left of Activity ID. I name it URL, but call it whatever you prefer. Then in cell A2 enter this formula: 

=CONCAT("https://www.strava.com/activities/",B2,"/export_gpx")

Then copy it down for all rows. This create a link to each activity in GPX format. If some of the activities don't have location data, don't worry about it. Those will simply not download in the process.

Choose the URL column header (the "A" above URL), choose Copy, then Paste Special => Values. Then delete column B (the column with the activity ids).



DOWNLOAD EACH ACTIVITY

Step 1 - Add the Simple Mass Downloader Chrome extension.


A new tab will open with a tutorial if you're interested to learn how it works.

Step 2 - Click on the extension button and you should see this screen.



Step 3 - Click on the hamburger on the right and choose Import URLs from local file.



Step 4 - Import the activities.csv file and the URLs will upload. This loads all of the files into the queue on the Download List tab. Choose the Select All box and they should all be highlighted.



Step 5 - Click on Start Selected and you'll see a bunch of files start downloading very quickly. Again, some of them will error out if the activity doesn't have location data. The files that download will have a green check next to them.



Phew! That might seem like a lot, but once you do it a couple times, the process is really quick.

Fantastic...all of the files are now downloaded. Onto Alteryx we go. 

ALTERYX PROCESS

Step 1 - Download the Strava Route Maps workflow from the Alteryx Gallery and open it in Alteryx Designer. If you can't download it from the Gallery, I have it on Google Drive here.


The Strava icon is a simple macro that will import all of the GPX files in the Directory you specify when you run the app. 

The workflow is split into four parts:

  1. Import the data, strip out the parts we need, then create points and lines.
  2. Normalize the data based on the calcs from Ken Flerlage, turn them into lines (one mark for each route rather than hundreds of point for each route) and export as a Tableau extract.
  3. For each point, calculate the distance, climb, etc. and extract all of the points.
  4. Take each of the points and turn them into lines.

Neither part 3 nor part 4 above normalize the data into squares. These are simply all of the data for each route.

Step 2 - Run the workflow as an App by clicking on the magic wand next to the Run button.


Step 3 - Choose the folder that contains all of the GPX files from the Simple Mass Downloader output. And click Finish.


This will generate three Tableau extracts that will load into the same directory as the GPX files. The workflow extracts them as TDE because Hyper files don't support polygons. Even though these aren't polygons, it makes me more comfortable that Tableau will read the files correctly.

Success!


Click the Clear button and then the OK button. Otherwise all three files will open in Tableau, which we don't want.

We're all done with Alteryx. Onto Tableau.


TABLEAU PROCESS

I've created a template for you to get started with. Download it from my Tableau Public profile here. Once you have done that, follow these steps.

Step 1- Open Tableau and right-click on each data source and pick Edit Data Source.


Step 2 - Click on the carrot next to the data source name and choose Edit Connection.


Step 3 - Navigate to the data source that downloaded from the Alteryx workflow. It will have the same name.



Step 4 - Go back to your worksheets and everything should update automatically. If not, right-click on the data source name and choose Refresh.

Step 5 - Customize the views as you see fit. That's it!

Here are the vizzes that I have included in the template.










June 8, 2020

#MakeoverMonday Week 23 - Frequency of Meat-Free Consumption by Brits in 2019

No comments
Week 23 was a pretty straight forward data set. It focuses on the frequency with which Brits of different diet types have consumed meat-free foods. Somehow meat-eaters have NEVER eaten meat-free food. No fruits or veggies? Ever? 

And what about the vegans? This data says they most frequently eat meat-free food weekly. Huh? They're vegan; they eat meat-free food every meal. 

I'm skeptical of this data. Anyway, here's my viz.

June 2, 2020

#TableauTipTuesday: How to Compare to a Selected Value with a Set Action

No comments
Set actions are super useful when you want to see how one thing compares to the rest. In this use case, I show you how you can click on a State and have all other States show as a comparison to the selected State.

May 19, 2020

#TableauTipTuesday - Re-Rank Metrics Based on the Overall Rank

No comments
Here's the scenario:
  1. You want to filter a dimension (e.g., State) by the top 10 overall rank. 
  2. You want to re-rank the remaining dimension members (i.e., top 10 States overall) within other metrics. 

For example, Massachusetts is ranked 10th overall and 21st in sales. Once I filter to the overall top 10, the rank should be recalculated to show where Massachusetts ranks in sale amongst the other States in the top 10 overall.

In this tip, I show you how to re-rank the dimensions filter remaining from the overall rank within other metrics.

May 5, 2020

#TableauTipTuesday - How to Add Labels Below All Bar Charts on a Single Worksheet

No comments
By default in Tableau, you cannot have labels below multiple bar charts in the same worksheet. For example, you want month labels below each set of bars in the same worksheet. In this tip, I show you how to work around that by using a dual-axis chart.

April 21, 2020

#TableauTipTuesday: How to Create a Dynamic Reference Band with Set Actions

No comments
Last week when I was teaching DS19, we were coming up with use cases for set actions. We came up with the idea of allowing a user to create their own reference band by lassoing the dates they are interested in. The reference band, via a set action, will then automagically move to the range of dates selected.

Enjoy!

April 13, 2020

#MakeoverMonday: Messi vs. Ronaldo - Who Took the Fewest Minutes to Score?

No comments
I haven't written a blog about Makeover Monday since the end of last year and I want to get back into the habit of blogging, so I'll give a quick review of what could be improved with this week's viz.


What could be improved?

  • Curvy lines don't help you see the exact points of the data. When curved lines are drawn, they render to smooth out the lines, therefore misleading the location of the data along the axis. 
  • Does the data represent each season or matches within a season? Given that I haven't looked at the data yet, the curved lines make me think the latter.
  • Is there a missing title?
  • What does the y-axis represent?
  • It looks like the data might be goals per match, but that would be misleading since they might have substitute appearances? Would goals per minute normalize the data better?

For my alternative, I decided to see who scored goals more often based on the number of minutes played, in other words, how often do they score? Then I wanted to know, season by season, who was better based on that stat.

April 7, 2020

#TableauTipTuesday: How to Use Set Actions to Maintain the Rank of a Dimension Upon Filtering

No comments
One of the drawback of filtering a dimension is that you will lose the rank of a dimension relative to others. This happens because of Tableau's order of operations which dictate that a dimension filter takes effect before a table calculation (rank in this case).

This video shows you how to use Set Actions to filter a dimension while also maintaining the rank.

March 10, 2020

#TableauTipTuesday - How to Use Level of Detail Expressions to Create a Daily Profit KPI

No comments
In this week's tip, I show you how to create a daily KPI view that counts the number of profitable days per month. This is a video that demonstrates my take on example 3 from the Top 15 LOD Expressions blog post on Tableau's website.

February 26, 2020

Visualizing the Geography of TV Stations

No comments
It seems to have been a while since I worked on a personal data analysis/visualization project. The one I'm going to take you through below was inspired by a piece of work I saw by Erin Davis (no contact info to link to). Check out her amazing portfolio on her website.

The piece I wanted to replicate in Tableau is based on her beautiful work Visualizing the Geography of FM Radio. Since she had already done this for radio, I thought I'd try to replicate her work, but with TV stations, that is, the strength and coverage that the broadcast signals from TV stations transmit.

First, I had to prep the data. Fortunately the raw data was easily accessible on the FCC website as are explanations of the fields and how to use them. The FCC also have information about which States fall into which FCC regions. I manually grouped the States into their regions in Tableau (it would have needed to be manually created data anyway).

From there, it was some data prep to get the signal boundaries for each state, ensure they are in the correct State (e.g., some stations that were listed in California actually plotted in other States), then export as a TDE (Hyper files don't work well with polygons).

Here's the Alteryx workflow:


For Tableau, I created a custom color palette based on the color legend on Erin's vizzes, replicated her maps as close as possible, and that's it!


Enjoy!