1

Alteryx Integrations: Connecting to Your Personal Fitbit Data with Tableau

Using an API integration can be a frustrating experience due to complex or non-existent documentation. In this exercise, we go through the exchange, transform, load (ETL) process of extracting data from a Fitbit API (in the JSON format), transforming it (e.g., splitting text to columns and filtering out the data we don’t want), and loading it into Tableau.

We use the powerful and intuitive Alteryx ETL tool to navigate this complex request with relative ease. During the transformation stage, Alteryx has a “browse anywhere” functionality that connects you with what your data looks like in real time as it progresses through the workflow.

You can download Alteryx here before we get started (free two-week trial): http://www.alteryx.com/.

EXTRACT: CONNECTING TO YOUR FITBIT API

Issue to solve No. 1 – You do not currently have robust analytical abilities on the Fitbit data that is tracked on your wrist. We need to pull our Fitbit information into Alteryx (aka Extract).

Let’s start by setting up your app here: https://dev.fitbit.com/.

1. Log in using your www.fitbit.com credentials (when setting up the device). We will tie your app to your personal data set.

2. Click the Register an App button: 3. Fill in the form as follows:

App name: Type “Test”

Description: Type “Alteryx and Tableau Fitbit demo”

Application website: Any website will do (type “http://www.eksh.com” if you wish)

Organization: Any organization will do (type “EKSH” if you wish)

Organization website: Use the same website as above (e.g., http://www.eksh.com)

OAuth 2.0 Application Type: Click Personal

Callback URL: Type “https://www.google.com” o We use Google to mine out tokens for access to the Fitbit API. The Fitbit app will not

communicate with Google.

Default access type: Select Read-Only

Check I have read and agree to the terms of service

2

Click Register

3

4. Copy/paste both the OAuth 2.0 Client ID and Client Secret (blacked out below) to a Notepad file:

Next, Fitbit walks you through the API setup. Our purpose is to “mine” the items we will need in Alteryx.

5. Right click on the OAuth 2.0 Tutorial page hyperlink and click Open link in new tab: By filling in the three fields below, a URL will be generated that will take us to the next step.

6. In the OAuth 2.0 tutorial page, do the following: 1) Select Implicit Grant Flow. 2) Copy and paste OAuth 2.0 Client ID and Client Secret from Notepad. 3) Type “https://www.google.com” in the Redirect URI field.

4

4) Click on the URL that Fitbit has generated for you.

The next window gives us the option to pull in fields other than the one we care about at this moment (heart rate). We recommend that you bring all of these fields (e.g., food and water logs and activity and exercise) into Alteryx in case you want to analyze it someday.

5

7. Click Allow:

You will be redirected back to Google but with a much different URL. From this URL we will copy the token and user ID and paste them into our Notepad file.

8. After #access_token=, copy and paste the value (up to, but not including &user_id) into your Notepad file. This will be your token the Alteryx setup:

9. After &user_id=, copy and paste the 6 digit value into Notepad: Now we will switch gears to Alteryx. The goal of this data flow is to get heart rate at the minute level. If your Fitbit does not have heart rate functionality, do not despair. There are multiple GET requests you can find by reading through the API docs here: https://dev.fitbit.com/docs/activity/.

NOTE

This URL is another sample GET statement: https://api.fitbit.com/1/user/[user-id]/sleep/date/2016-11-30.json. While some GET statements have different transformations, the JSON processing techniques can cross over.

6

10. Open Alteryx.

11. Click the In/Out category and then drag the Text Input tool to the canvas:

NOTE

After logging into https://dev.fitbit.com, click on Manage Your Apps. From here you can find the desired API. To do so, click on Read API Docs, click on Heart Rate and copy the desired URL. To save space in these instructions, we list the URL below.

12. Copy this URL (in the Heart Rate API docs): https://api.fitbit.com/1/user/-/activities/heart/date/today/1d/1min.json.

13. In the Text Input – Configuration pane, paste the URL in the Field1 box:

14. Click the Field1 header and rename it, typing “URL”:

NOTE

Feel free to add URLs here for the other GET statements (e.g., for Food Logging or Sleep).

7

15. Click on the Connectors category and drag the Download tool to the canvas, to the right of the Text Input tool:

16. In the Download – Configuration pane, under the Basic tab, make sure Field is set to URL:

17. Click on the Headers tab in the Download – Configuration pane. 18. Type or copy/paste the following Names and Values (note: Click the Add button to create each

new row): 1) Type “Authorization” for Name and type “Bearer” before coping and pasting your Google

access token for Value (refer to your Notepad). 2) Type “user_id” for Name and copy and paste your Google user_id for Value (refer to your

Notepad). 3) Type “scope” for Name and copy and paste your Fitbit OAuth 2.0 Client ID for Value (refer to

your Notepad). 4) Type “scope” for Name and copy and paste the following text to Value:

sleep+settings+nutrition+activity+social+heartrate+profile+weight+location (note: this retrieves all the data in your Fitbit account).

5) Type “token_type” for Name and type “Bearer” for Value.

8

19. Click on the Payload tab in the Download – Configuration pane. 20. Change HTTP Action to GET (or FTP):

Your Alteryx workflow should look as follows:

Issue No. 1 solved – It might not seem like it just yet, but at this point you have a working data connection (aka Extract) to the cloud where your Fitbit data is stored.

NOTE

A star next to a tool (e.g., ) signifies that the tool is in the Favorites category. In this exercise, we opted to show you the specific category each ETL tool is in. If you find yourself using the same tools that are not in Favorites, simply right-click on the tool and click Add To Favorites:

9

TRANSFORM: MAKING SENSE OF THE JSON EXTRACT

Issue to solve No. 2 – The data being pulled from the cloud (Fitbit) is in JSON format and unreportable. We need to perform some cleaning and reorganization (aka transform the data).

1. Click the Developer category and drag the JSON Parse tool onto your canvas, to the right of the Download tool:

The JSON Parse tool enables Alteryx to process the JSON into a table view (rows and columns).

NOTE

Each Alteryx tool, such as Text Input and Download has data being fed into and/or out of it. Data flowing in and out of the tool are represented by the green icons. Click on either the incoming our outgoing icons to see your data (up to approximately 200 records):

2. Click the Run Workflow icon to process the new column named DownloadData (note: you will receive errors (e.g., 1 field version error); ignore for now):

10

3. Do the following: 1) Click the JSON Parse tool on the canvas. 2) Click the JSON Field drop-down arrow in the JSON Parse – Configuration pane. 3) Click DownloadData.

4. In the JSON Parse – Configuration pane, click the Output values into data type specific fields radio button:

Optional: To examine our data at this point in the workflow, we use the Browse tool.

5. Click on the In/Out category and drag the Browse tool to the upper right of the JSON Parse tool.

6. Click the Run Workflow button in the Toolbar. 7. Click on the Browse tool in your canvas:

11

8. See the data available at this stage in the workflow:

NOTE

The Browse tool does not alter your data — it’s simply a reading tool to see if you are on the right track at different stages in your workflow-building process. While

the green output icon to the right of a tool in your canvas displays up to 2,000 records, the Browse tool shows all records.

Next, we add the Text to Columns tool to start narrowing down the data pulled into Alteryx. This tool breaks apart a string of values into separate fields (in this exercise: heart rate, for each minute, for each day).

9. Click the Parse category and drag Text to Columns to the canvas, to the right of the JSON Parse tool:

10. In the Text to Column – Configuration pane, do the following: 1) In the Field to Split drop-down list, select JSON-Name. 2) For Delimiters, change to a period “.”.

12

3) Increase # of Columns to 10.

Next, we filter the data for only what we want to process (heart rate, for each minute, by day).

11. Click the Preparation category and drag the Filter tool to the canvas, to the right of the Text to Columns tool:

12. In the Filter – Configuration pane, do the following: 1) In the Basic Filter drop-down list, select JSON_Name1. 2) Type “activities-heart-intraday” in the associated field.

We now use the Select tool to change the data types as well as data names.

13

13. Click the Preparation category and drag the Select tool to the canvas, to right of the

Filter tool, connecting it to the True filter arrow.

14. In the Select – Configuration pane, make the following changes:

1) For JSON_ValueString, change the Type to Time. 2) For JSON_ValueString, type “Time_of_Day” in the Description field. 3) For JSON_ValueInt, type “Heart_Rate” in the Description field. 4) For JSON_Name3, change the Type to Int64. 5) For JSON_Name3, type “Time_ID” in the Description field. 6) Uncheck all boxes except for the three values we are interested in:

Next, let’s use the Summarize tool to help us group our data by Time_ID.

14

15. Click the Transform category and drag the Summarize tool to the canvas, to the right of the Select tool:

16. In the Summarize – Configuration pane, do the following:

1) Click the JSON_Name3 field. 2) Click the Add button. 3) Click Group By.

17. In the Output Field Name for JSON_Name3, type “Time_ID”:

15

18. In the Summarize – Configuration pane, do the following: 1) Click the JSON_ValueString field. 2) Click the Add button. 3) Hover over String. 4) Click on Concatenate.

19. In the Output Field Name for JSON_ValueString, type “Time_of_Day”:

16

20. In the Summarize – Configuration pane, do the following: 1) Click the JSON_ValueInt field. 2) Click the Add button. 3) Click on Sum.

21. In the Output Field Name for JSON_ValueString, type “Heart_Rate.” When finished, the Actions portion of the Summarize – Configuration pane should look as follows:

To sort the data by Time_ID, use the Sort tool.

17

22. Click the Preparation category and drag the Sort tool to the canvas, to right of the Summarize tool:

23. In the Sort – Configuration pane, click the Name drop-down list and select Time_ID (to sort by Time_ID):

Issue No. 2 solved – You now have your heart rate data neatly organized by time of day for each row.

18

LOAD: DATA READY FOR REPORTING

Issue to solve No. 3 – What format do I want Alteryx to output to? In this tutorial, we are loading our Fitbit data into a Tableau workbook in order to create a visualization. If you have access to Tableau (you can get a two-week free trial from Tableau), export Alteryx to a .tde Tableau file and create your vizzes. Alternatively, you can output/load to a database or Excel workbook.

1. Click the In/Out category and drag the Output Data tool to the canvas, to right of the Sort tool:

2. In the Output Data – Configuration pane, click the Write to File or Database drop-down list and

click File Browse:

19

3. Navigate to your desired location, name the file and click Save.

4. Change the Output Options to Overwrite Existing Extract File (Create if does not Exist):

When finished, your entire Alteryx workflow should look as follows:

Click the Run Workflow icon in Alteryx:

20

5. Click on the green Output Data icon to see the data exported to your Tableau file in the Results pane (under the Canvas):

6. Minimize Alteryx and double-click on your new Tableau Extract file:

7. Create a visualization using your Fitbit data:

21

Issue solved No. 3 – We successfully output our Fitbit heart rate data to Tableau for further analysis. Whatever output makes the most sense for your architecture is the right answer!

NOTES

Alteryx supports many different output options. See a complete list here: https://help.alteryx.com/9.5/DbFileOutput.htm.

Explore Fitbit’s API docs for many more GET commands at this location: https://dev.fitbit.com/docs/activity/.

Extra Credit: Try another GET command and apply some of the JSON parsing techniques to make that data reportable.

Text to columns – to parse out data separated by periods.

Filter – to exclude header data.

Select – to change data types and rename for easier visibility.

Summarize – to group by the item you want to report on.

About the author:

JUSTIN LOWE

Senior in the consulting service area

Three years of consulting and business intelligence experience

Serves companies in the finance and banking industries

Extensive experience in reporting, data analysis, data warehousing,

and data architecture

BS Accounting University of Colorado at Boulder

For additional information about EKS&H's business analytics services, please contact Mark Richards

at mrichards@eksh.com or at (303) 740-9400.