Create and Populate a Simple App Sales and Revenue Database using SQLite, App Annie's API, and R

04 Jun 2015

Eric Fram ##Introduction##

I’ve found that I frequently want to compare sales and revenue data across territories, dates, and applications. We use App Annie to keep track of all of this information, but constantly going to their website to download .csv files gets pretty tedious. They offer and API that let’s me get all the data I want much more efficiently. Instead of making frequent API calls, I prefer to store the data in my own database.

The database management system I’ve chosen for this purpose is SQLite. It’s lightweight, easy to set up, and perfect for storing a relatively small amount of data. It would not be good if I had a large amount of data that I needed to store on a server or if lots of other people were also accessing the database, but it is perfect for my uses here.

From within R, I can get the data from the API and create and set up the sales and revenue database with only about 100 lines of code. Once everything is set up, it’s easy to query data and run a wide range of analyses.

This guide will show you how to set this system up for yourself.

##Get Sales and Revenue Data from App Annie ##

We can get all the data we need with just two API calls after a little bit of preparation work. First, you’ll need to get your API key from App Annie. You can do so here. Next, you’ll need to make sure that you have the “httr” and “jsonlite” packages installed in R.

You’ll also need to know your account number. If you don’t know your App Annie account number, you can retrieve it through the API as so:

# Save your authorization header as a variable to easier use
auth <- "bearer {your API key}"

# Load the "httr" and "jsonlite" libraries
library(httr) # for GET() function
library(jsonlite) # for fromJSON() function

# Call the API, get account information, and save account number
acctQuery <- "https://api.appannie.com/v1.2/accounts"
acctQuery <- GET(url=acctQuery, add_headers(Authorization=auth))
acctNo <- fromJSON(content(acctQuery,type="text"))
acctNo <- acctNo$accounts$account_id

Let’s go ahead and get all of our sales and revenue data for each date by country and by territory.

#Get sales and revenue breakdown by product, country, and date
query <- "https://api.appannie.com/v1.2/accounts/{your account number}/sales
                                            ?break_down=product+country+date"
getdata <- GET(url=query, add_headers(Authorization=auth))

#Convert data into data frame
data <- fromJSON(content(getdata,type="text"))
df <- data$sales_list
df <- flatten(df) # as df originally contains nested data frames

#See how the data is structured
colnames(df)
## [1] "date"                       "country"                    "product_id"                 "units.product.promotions"
## [5] "units.product.downloads"    "units.product.updates"      "units.product.refunds"      "units.iap.promotions"
## [9] "units.iap.sales"            "units.iap.refunds"          "revenue.ad"                 "revenue.product.promotions"
## [13] "revenue.product.downloads" "revenue.product.updates"    "revenue.product.refunds"    "revenue.iap.promotions"
## [17] "revenue.iap.sales"         "revenue.iap.refunds"

Now we have all of our revenue and downloads data in one place broken down by date, territory, and application. Each row in this new data represents a unique combination of date, country, and product id. In this data frame though, we can’t really tell which product_id is associated with which app. This is easy to fix.

Get App Name Reference Data from App Annie

We can make another call to the API to check which product_id is associated with which app.

#Get product information and convert to data frame
prodIDQuery <- "https://api.appannie.com/v1.2/accounts/{your account number}/products?page_index=0"
prodIDQuery <- GET(url=prodIDQuery, add_headers(Authorization=auth))
prodIDData <- fromJSON(content(prodIDQuery,type="text"))
app.list <- prodIDData$products

#Select only product_ID and _product_name
app.list <- app.list[,c(2,6)]

#Check to make sure we have the correct information
colnames(app.list)
## [1] "product_id"   "product_name"

Perfect. Now we have a data frame where each product ID is associated with the actual application name. We’re ready to insert this new data into our SQLite database.

Conceptual Data Model

At this stage, the database we are creating is really simple. It’s just two tables. One for looking up app names based on their product ID’s, and one that contains revenue and downloads data. The data model will look like this:

SQLite automatically creates Row ID’s for use as primary keys for each table, so we’ll just use those. Also, since SQLite doesn’t support Foreign Keys by default and since App Annie is already enforcing Foreign Key relationships in this data, I’m not going to explicitly specify the one here.

Create and Populate the Database

We can create a new SQLite database from within R using the RSQLite package. RSQLite’s dbConnect function will create a new empty database if the one you specify doesn’t already exist.

#Connect to the app information database
dbFile <- {file path to your database}
library(RSQLite)
con <- dbConnect(SQLite(),dbFile)

Let’s create the app name lookup table.

#Create App Reference table
dbWriteTable(con, "app_reference",
             app.list, row.names = NA, overwrite = TRUE, append = FALSE,
             field.types = NULL)
             #app.list is the data frame containing product_id and product_name informtion

Now, let’s create the account sales and revenue table.

#Create Account Connection Sales table
dbWriteTable(con,"account_sales",
             df, row.names = NA, overwrite = TRUE, append = FALSE,
             field.types = NULL)

Let’s test drive the database. This query will get the total downloads for each app.

#Example query to get total downloads for each app
testQuery <- dbSendQuery(con, "
                        SELECT
                              app_reference.[product_name],
                              SUM(account_sales.[units.product.downloads])
                        FROM app_reference
                        INNER JOIN account_sales
                        ON app_reference.[product_id] = account_sales.[product_id]
                        GROUP BY app_reference.[product_name]")
testData <- fetch(testQuery, n= -1)
testData
product_name                                           SUM(account_sales.[units.product.downloads])
## 1                     Beautiful You the App                                             x,xxx
## 2  Birds vs. Bees: Battle for the Birdhouse                                            xx,xxx
## 3             Birds vs. Bees: Beehive Blast                                            xx,xxx
## 4                                Dot Direct                                             x,xxx
## 5                           Earth Day Carol                                           xxx,xxx
## 6                            Fight the Fire                                            xx,xxx
## 7                  Helitack: Fight the Fire                                            xx,xxx
## 8                                  Puznetic                                             x,xxx
## 9                        Puznetic Anthology                                             x,xxx
## 10                             Puznetic Art                                             x,xxx
## 11                            Puznetic Pets                                             x,xxx
## 12                             Puznetic Sky                                             x,xxx

Looks like everything is working. (Note: I’ve obscured the actual numbers with x’s. R will return actual numbers for you.)

Update the Database as Necessary

You’ll probably want to update your data at some point. The function below will do that. It connects to the database, checks to see if there is likely to be new data available, then gets the new data and stores it. This will work if you’ve already defined “dbFile” as the path to your database, and “auth” as your bearer authorization.

#Function to get new data
update <- function() {

      require(RSQLite)
      con <- dbConnect(SQLite(),dbFile)

      #1 - Check to see what the most recent date in account_sales is
      maxDate <- dbSendQuery(con, "
                                    SELECT
                                          max(date)
                                    FROM account_sales")
      maxDate <- fetch(maxDate, n=-1)
      maxDate <- (maxDate[1,1])

      #2 - Get the date two days before current date
      #(App Annie reports take between 24 and 48 hours)
      newDate <- Sys.Date()-2

      #3 - Check to make sure that newDate is more recent than maxDate
      if (maxDate > newDate) {
            stop("Please wait until newer data is available")
      }
      #4- Get new data from API
      else{
            query <- "https://api.appannie.com/v1.2/accounts/{your account no}/sales
                                                    ?break_down=product+country+date"
            getdata <- GET(url=query, add_headers(Authorization=auth))
            data <- fromJSON(content(getdata,type="text"))
            df <- data$sales_list
            df <- flatten(df)

      #5- Add new data to the account_sales table
            dbWriteTable(con,"account_sales",
                         df, row.names = NA, overwrite = TRUE, append = FALSE,
                         field.types = NULL)
      }
}

#Update the information in the database
update()

#Disconnect from the database
dbDisconnect(con)

##Conclusion##

Thanks for reading. I definitely get that this may be overkill for such a small amount of data, but I’ve found that storing everything in a database like this makes it much easier to access exactly the data I want. If you have any questions or comments, please feel free to contact me directly or tweet at me @ZippyBrain.