Accessing Mode data in R

Inspiration for this post

In the past, I’ve been used to a pretty straightforward R workflow: download my data as a csv from somewhere, save it on my computer, and go to work. Now that I work in healthcare, downloading data and saving it on my computer is no longer such a good idea (since that would be illegal) so I’ve needed other ways to get the data I need into R. My company uses the data analysis platform Mode Analytics to pull data from our database using SQL. I wanted a way to get data directly from Mode into R without needing to download the results of a report as a CSV, move them onto Google Drive, and then access them in R.

Get API access token

The first step to accessing Mode via API is to generate a token. First, log in to Mode, click on your name in the top left corner, and select “My Account”. Then click on “API Tokens” on the left side. You can generate a token by specifying a name for the token and clicking “Create token”. The resulting credentials consist of your token (a.k.a. username or access key) and your password (a.k.a. access secret). This token is associated with your Mode user account, not an organization, so you’ll be able to access reports from any organization you have access to.

Export report CSV to dataframe

The following instructions essentially follow the Python code instructions on the Mode website to export a report to a csv. This code doesn’t actually download the csv file, however, just imports it into R as a dataframe (so no datafiles on your computer!).

The following functions rely on the httr and jsonlite packages:

library(httr)
library(jsonlite)

You also need your username and password, preferably assigned to variables in R. For this example, my username is assigned to username and my password is assigned to password.

# Replace with your username and password
username <- "YOUR_USERNAME"
password <- "YOUR_PASSWORD"

You also need to find the token of the report you’re interested in. When you go to your report in Mode, the token is everything after “reports/”. So, for example, if you’re interested in Legos (which you should be), you can get the most recent run of the Lego set summary report. The URL is https://app.mode.com/modeanalytics/reports/eb4fdefe37d8, so the report token is “eb4fdefe37d8”.

# Replace with the report token of interest
report_token <- "eb4fdefe37d8"

Finally, you need the name of your organization on Mode. For this example, we’ll be looking at a community available Mode report, so the organization is “modeanalytics”. You can get this from the URL as well, it will be the part directly before “/reports”:

# Replace with the organization of interest
organization <- "modeanalytics"

get_report_latest_run <- function(report_token, organization, username, password){
  # Generate the URL for runs of the report you're interested in
  url = paste0("https://modeanalytics.com/api/", organization, "/reports/", report_token, "/runs")
  r = GET(url, authenticate(username, password, type = "basic"))
  result = fromJSON(content(r, "text"))
  # Find the most recent run token
  most_recent_report_run_token <- result$`_embedded`$report_runs[1,]$token
  # Find the status of the most recent run
  status = result$`_embedded`$report_runs[1,]$state
  if(status == "succeeded"){ # if the most recent report ran,
    return(most_recent_report_run_token) # return that run token
  } else{
    stop("Report latest run did not succeed.") # if the most recent run failed, error
  }
}

get_report_latest_run(report_token, organization, username, password)

## No encoding supplied: defaulting to UTF-8.

## [1] "079bc1243d11"

export_report_to_csv <- function(report_token, organization, username, password) {
  # Get the latest run token
  latest_report_run_token <- get_report_latest_run(report_token, organization, username, password)
  # Generate the URL for the latest run of the report as a csv
  url <- paste0("https://modeanalytics.com/api/", organization, "/reports/", report_token, "/runs/", latest_report_run_token, "/results/content.csv")
  r <- GET(url, authenticate(username, password, type = "basic"))
  # Read the data as a csv
  r_csv <- content(r, type = "text/csv")
  return(r_csv)
}

Example

To get the data from the most recent run of the Lego set summary report, you want to save a script file containing the above two functions (I call this script mode_api_script.R. Then, run the following:

library(httr)
library(jsonlite)
source("mode_api_script.R")

# Set username, password, and report token
username <- "YOUR_USERNAME"
password <- "YOUR_PASSWORD"
report_token <- "eb4fdefe37d8"

lego_data <- export_report_to_csv(report_token, organization, username, password)

## No encoding supplied: defaulting to UTF-8.
## No encoding supplied: defaulting to UTF-8.

## Parsed with column specification:
## cols(
##   year = col_integer(),
##   white = col_double(),
##   red = col_double(),
##   blue = col_double(),
##   black = col_double(),
##   light_gray = col_double(),
##   total = col_double(),
##   main = col_character()
## )

knitr::kable(head(lego_data))