{"id":19303,"date":"2015-04-21T13:01:47","date_gmt":"2015-04-21T13:01:47","guid":{"rendered":"https:\/\/blog.kaywa.com\/?p=19303"},"modified":"2015-04-21T14:57:02","modified_gmt":"2015-04-21T14:57:02","slug":"working-with-stripe-data-and-r","status":"publish","type":"post","link":"https:\/\/blog.kaywa.com\/?p=19303","title":{"rendered":"Working with Stripe Data and R"},"content":{"rendered":"

I recently started working with R<\/a>. As we use Stripe<\/a> as one of our payment providers, I thought it might be interesting to use Stripe's CSV files and R to answer some of the questions every SaaS startup needs to know.<\/p>\n

Question: How many new, recurring and lost customers do we have per week?<\/h2>\n

As we have a weekly meeting where we discuss our numbers, it would be best to get the numbers per week. So I decided to start with the question of how many new and recurring customer we have, as well as how many customers we lose per week.<\/p>\n

To start I only wanted to have a table that shows the week and below the numbers for lost, new and recurring customers [1].<\/p>\n

In future posts, I plan to show different aspects like financial data or add visual features like plots. <\/p>\n

The CSV file from Stripe<\/h2>\n

If you are a Stripe user you can download the payments.csv file directly from https:\/\/dashboard.stripe.com\/payments<\/a>.
\nBelow are the columns, or variables in R speak, available in the CSV file:<\/p>\n

> names(raw_data)\r\n [1] "id"                         "Description"               \r\n [3] "Created..UTC."              "Amount"                    \r\n [5] "Amount.Refunded"            "Currency"                  \r\n [7] "Converted.Amount"           "Converted.Amount.Refunded" \r\n [9] "Fee"                        "Tax"                       \r\n[11] "Converted.Currency"         "Mode"                      \r\n[13] "Status"                     "Statement.Descriptor"      \r\n[15] "Customer.ID"                "Customer.Description"      \r\n[17] "Customer.Email"             "Captured"                  \r\n[19] "Card.ID"                    "Card.Last4"                \r\n[21] "Card.Brand"                 "Card.Funding"              \r\n[23] "Card.Exp.Month"             "Card.Exp.Year"             \r\n[25] "Card.Name"                  "Card.Address.Line1"        \r\n[27] "Card.Address.Line2"         "Card.Address.City"         \r\n[29] "Card.Address.State"         "Card.Address.Country"      \r\n[31] "Card.Address.Zip"           "Card.Issue.Country"        \r\n[33] "Card.Fingerprint"           "Card.CVC.Status"           \r\n[35] "Card.AVS.Zip.Status"        "Card.AVS.Line1.Status"     \r\n[37] "Disputed.Amount"            "Dispute.Status"            \r\n[39] "Dispute.Reason"             "Dispute.Date..UTC."        \r\n[41] "Dispute.Evidence.Due..UTC." "Invoice.ID"                \r\n[43] "Payment.Source.Type"  \r\n<\/code><\/pre>\n
We will only use a few of them for our purpose, but it's good to see what is available.
\nBesides the payments.csv, there are also CSV files available for customers and for transfers. We may discuss their respective merit in future posts.<\/p>\n
Installing R and R Studio<\/h2>\n
To play with the R script<\/a> and your CSV file, you need, however, first to download<\/a> and install R.
\nI also highly recommend to use R Studio<\/a>.<\/p>\n
Setting your Working Directory<\/h2>\n
Once you have R Studio (or R on your Terminal\/Shell) up and running, choose your working directory and set it in R Studio like this:<\/p>\n
## "your_working_directory" is a path like "\/Users\/rogerfischer\/R\/kaywa\/stripe_with_R"\r\nsetwd("your_working_directory")\r\n<\/code><\/pre>\n
If you want to check that you are where you want to be, you can use<\/p>\n
getwd()\r\n<\/code><\/pre>\n
The R script<\/a> and your payments.csv file should be in the same working directory if you want to run this “as is”. <\/p>\n
If you don't use Github yet, you can always copy the script's<\/a> content and import it in a file named “stripe_weekly_new_recurring_lost.R”. Any text editor (Sublime, R Studio) will be fine for this task. <\/p>\n
Running the whole R script or run by code chunk<\/h2>\n
You can run the script entirely and you will then find a cvs file in your working directory called “new_recurring_lost.csv” (which you can import into Excel). This CSV file is bare bones. It starts with the first week of your payments.csv data and ends with the last.
\nHere is an example how it will look:<\/p>\n
14-52 | 15-00 | 15-01 | Year and Week, 14-52 = Week 52, 2014 (Weeks start Sunday, US notation)      \r\n1     | 4     | 4     | Lost Customers      \r\n5     | 11    | 23    | New Customers       \r\n25    | 53    | 88    | Recurring Customers \r\n<\/code><\/pre>\n
In the CVS file itself the names “year and week”, “lost”, “new”, “recurring\u201c are missing. This is by convention, as further manipulation of your data could screw up because you named the rows.<\/p>\n
So don't write row.names = TRUE as in the documentation about write.table<\/a>. This will only create a blank column.
\nIf you absolutely want names, you better transform the table into a wide format data frame (see "wide_df” at the end of the article) and then export this to csv. In the R script, I added the option to create a CSV file with column names, based on “wide_df” too.<\/p>\n
I recommend that you run the code file by code chunks as this is more interesting. And will also give you a better understanding of the data. In R Studio you will see in the top right corner above your R script an icon named Run. If you select your code and click on Run, it will only execute the selected part. So let's start!<\/p>\n
Loading the Data with read.cvs<\/h2>\n
At first we load the data into an R object, called a data frame, with this command:<\/p>\n
raw_data <- read.csv("payments.csv", stringsAsFactors=FALSE)\r\n<\/code><\/pre>\n
You can now explore your data. I recommend the following:<\/p>\n
dim(raw_data)     # Dimension of the data frame, first the rows, then the columns\r\nstr(raw_data)     # More information about the structure of your data frame\r\nsummary(raw_data) # Gives you a summary by column \r\nhead(raw_data)    # Gives you the first 6 rows\r\ntail(raw_data)    # Gives you the last 6 rows\r\n\r\nView(raw_data)    # See all your data in one big table\r\n<\/code><\/pre>\n
Every function gives you some information about your data frame respectively about your payments.csv data. With View(raw_data), you see all your data in tabular form. As there is too much data, we first need to do some cleanup. <\/p>\n
In our R script<\/a> the cleaning, subsetting, renaming, changing the type of variables, so we can count, the sorting continues till line 72. If you are interested to see what kind of value is behind a variable name you can always run it.
\nHere is an example:<\/p>\n
data$period <- format(data$time,"%y-%U")\r\n## Now you want to know what the value of data$period is. Just use:\r\n> head(data$period, 10)\r\n [1] "15-10" "15-10" "15-10" "15-10" "15-10" "15-10" "15-10" "15-10" "15-10" "15-10"\r\n\r\n## or if you know you don't have tons of data, try to run it directly\r\ndata$period\r\n<\/code><\/pre>\n
This is a good way to check what the code does. The comments in the R script should give you enough guidance.<\/p>\n
Creating the weekly table of lost, new and recurring customers<\/h2>\n
If you have selected and run all the code till line 72, all your objects are now loaded in memory.
\nNow we will build our table which you can directly check up in R Studio's console<\/p>\n
cust_types_by_period <- table(data_f$cust_type, data_f$period)\r\n\r\n## Show table in the console\r\ncust_types_by_period \r\n<\/code><\/pre>\n
Create a new CVS file, called new_recurring_lost.csv<\/h2>\n
Create now the CSV file called “new_recurring_lost.csv” with the function write.table. The CSV file  will be available in your working directory. It contains the table with the weekly data we discussed earlier.<\/p>\n
If you don't want to create this CSV file, you can just outcomment the following:<\/p>\n
write.table(user_types_by_period, file = "new_recurring_lost.csv", row.names = FALSE, sep = "\\t")\r\n<\/code><\/pre>\n
Addendum: Months instead of weeks as data period<\/h2>\n
If you want to look at months rather than weeks, you can just change the format of the data$period.
\nInstead of<\/p>\n
## By weeks\r\n## data$period <- format(data$time,"%y-%U") \r\n<\/code><\/pre>\n
use<\/p>\n
## Month as decimal number, otherwise use %b or %B\r\ndata$period <- format(data$time,"%y-%m") \r\n<\/code><\/pre>\n
Addendum: Creating data frames in the long and wide format<\/h2>\n
If you want to store your table data in a new data frame, you can do this by running <\/p>\n
long_df <- data.frame(cust_types_by_period)\r\ncolnames(long_df) <- c("customer_type", "period", "count")\r\n<\/code><\/pre>\n
You can see the long format below:<\/p>\n
> head(long_df)\r\n  customer_type period count\r\n1          lost  12-43     1\r\n2           new  12-43     4\r\n3     recurring  12-43     1\r\n4          lost  12-44     6\r\n5           new  12-44    17\r\n6     recurring  12-44     1\r\n<\/code><\/pre>\n
If you need the wide format looking like this:<\/p>\n
> head(wide_df)\r\n  period lost new recurring\r\n1  12-43    1   4         1\r\n2  12-44    6  17         1\r\n3  12-45    0  24         1\r\n4  12-46    2  23         1\r\n5  12-47    0  13         0\r\n6  12-48    0  12        14\r\n<\/code><\/pre>\n
you can install the reshape2 package<\/a> and then load it or just run this code chunk:<\/p>\n
install.packages("reshape2") # needs only to be installed once\r\nlibrary(reshape2)\r\nwide_df <- dcast(long_df, period ~ customer_type, value.var = "count")\r\n<\/code><\/pre>\n
If you want the column names in your CSV file, as mentioned above, you could now run the following:<\/p>\n
write.table(wide_df, file = "wide_df.csv", row.names = FALSE , col.names = TRUE, sep = "\\t")\r\n<\/code><\/pre>\n
Now the first line uses your columns names: period, lost, new, recurring.<\/p>\n
Addendum: Mean and Media for all weeks<\/h2>\n
With the wide format, you are now able to calculate the mean (or average) and median (or middle value) over all your weeks for lost, new and recurring customers. If you have a lot of outliers in your data, I recommend using the median over the mean.<\/p>\n
> weekly_medians <- apply(wide_df[, 2:4], 2 , median)\r\n> weekly_medians\r\n     lost       new   recurring \r\n      3.0      76.0        91.0 \r\n<\/code><\/pre>\n
Addendum: Looking at one week only<\/h2>\n
If you want to look at a single week, use:<\/p>\n
## Subset Week 9, 2015\r\np1509 <- subset(data_f, data_f$period == "15-09")\r\nView(p1509)\r\n## Subset all lost users in Week 9, 2015\r\np1509_lost <- subset(data_f, data_f$period == "15-09" & data_f$cust_type == "lost")\r\nView(p1509_lost)\r\n<\/code><\/pre>\n
Conclusion<\/h2>\n
Now you can always download your current payments.cvs and rerun the R script and you have your updated numbers. You will always know how many new, lost or recurring customers you have. And you can compare that to your average or median week. <\/p>\n
Obviously this is just a first attempt to work with the Stripe data and I plan to improve in any direction. Next up will be the week, seen from its financial side. And then the first visualization of this data.<\/p>\n
Many thanks also to Vitomir Kovanovic<\/a> who helped me with the R script.<\/p>\n
If you have any questions, ideas, improvements, let me know in the comments.<\/p>\n<\/p>\n\n
[1]
\nDefinitions<\/strong>   <\/p>\n