In jlewis91/datascience: Set of useful functions and sometime reports. :)

knitr::opts_chunk$set(echo = FALSE, results = FALSE,
                      message = FALSE, warning = FALSE,
                      fig.width = 12, fig.height = 8)

library(data.table)
library(datascience)
library(magrittr)
library(taucharts)
library(ggplot2)
library(lubridate)

google_cred_path <- params$google_cred_path
konkurrenz <- datascience::pullClean(c("comdirect", "number26", "consorsbank", "fidor bank", "1822direkt")
                        , cred_path = google_cred_path) 
konkurrenz$trend[, variable := factor(variable, levels = c("comdirect", "number26", "consorsbank", "fidor bank", "1822direkt")
                               , labels = c("comdirect", "NUMBER26", "Consorsbank", "Fidor Bank", "1822direkt"))]
setnames(konkurrenz$trend, "variable", "Bank")

number26 <- pullClean("number26", cred_path = google_cred_path)
number26$trend[, Bank := "NUMBER26"]

competitordata <- datascience::getCompetitorData()
appReleases <- appReleaseData()

n26_colors <- c("#7c260b", "#76c0c1", "#6794a7", "#014d64", "#01a2d9")

monthly_medians <- konkurrenz$trend[, .(value = value %>% median %>% as.numeric, placeholder = "")
                                   , by = .(Bank, month = floor_date(start, "month"))]

# Plot Last Full Month's Results
last_full_month <- floor_date(Sys.Date(), "month") %m-% months(1)

last_month <- monthly_medians[month == last_full_month]
last_month[, Date := month %>% format("%B %Y")]

# Add in hand-collected datapoints...
cd_complete <- rbind(last_month[, .(Bank, value, variable = "Google Trends")]
                     , competitordata[, .(Bank, value, variable)])

cd_complete[, Date := last_full_month %>% format("%B %Y")]

# Normalize against N26
cd_complete[, value_norm := value / value[Bank == "NUMBER26"], by = variable]
cd_complete[, Bank := factor(Bank, levels = c("comdirect", "NUMBER26", "Consorsbank", "Fidor Bank", "1822direkt")
                         , ordered = TRUE)]
cd_complete[, variable := factor(variable, levels = c("Number of Accounts", "Google Trends"
                                          , "Facebook Likes", "Twitter Followers", "Deposited Funds", "Facebook Rating")
                         , ordered = TRUE)]
cd_complete[, value_per_1kaccounts := 1000 * value / value[variable == "Number of Accounts"], by = Bank]
cd_complete[, value_per_account_norm := value_per_1kaccounts / value_per_1kaccounts[Bank == "NUMBER26"], by = variable]

cd_complete <- merge(cd_complete, dcast(cd_complete, Bank ~ variable, value.var = "value"
                                        , fun.aggregate = format, scientific = FALSE, big.mark = ","
                                        , small.mark = ".", fill = " "), by = "Bank")
per_account_cast <- dcast(cd_complete, Bank ~ variable, value.var = "value_per_1kaccounts", fill = " "
            , fun.aggregate = function(x){x %>% round(1) %>% format(scientific = FALSE, big.mark = ",", small.mark = ".")})

setnames(per_account_cast, per_account_cast %>% names %>% paste0(".per.1k.Accounts")) # Replace spaces
setnames(per_account_cast, "Bank.per.1k.Accounts", "Bank")
cd_complete <- merge(cd_complete, per_account_cast, by = "Bank")

setnames(cd_complete, cd_complete %>% names %>% gsub(" ", ".", .)) # Replace spaces

cd_complete[, Deposited.Funds.EUR := Deposited.Funds]

Sometimes it takes a little exploring to convince yourself it's all right to trust every last groschen to a scrappy Berliner startup.

Room to Grow

tauchart(cd_complete[variable %in% c("Number of Accounts", "Google Trends")
                     , .(Bank, Date, Number.of.Accounts, Google.Trends
                         , Facebook.Likes, Twitter.Followers, variable, value_norm)]
         , width = "800px", height = "300px") %>%
  tau_bar("variable", "value_norm", color = "Bank") %>%
  tau_color_manual(values = n26_colors) %>%
  tau_legend() %>%
  tau_guide_y(label = "Percent of N26", tick_format = "%") %>% tau_guide_x(label = " ") %>%
  tau_tooltip(c("Bank", "Date", "Number.of.Accounts", "Google.Trends", "Facebook.Likes", "Twitter.Followers"))

mkt_share <- cd_complete[variable == "Number of Accounts"
                         , (100*value[Bank == "NUMBER26"] / value[Bank == "comdirect"]) %>% round(0)]

Notes:

Google Trends search data. Normalized to NUMBER26 as 100%. Median weekly performance during r last_month$month[1] %>% format("%B %Y").

Opportunity:

NUMBER26 currently has r mkt_share% of the directbank leader comdirect's marketshare. Google Trends appears to be a decent proxy for overall marketshare and suggests that the existing German market offers lots of opportunity for growth.

---

Growth Spurt

yearly_growth <- monthly_medians[, .(yoy_growth = value[month == last_full_month] /
                                       value[month == last_full_month %m-% years(1)] - 1
                                     , placeholder = "")
                , by = Bank]
yearly_growth[, Dates := paste0(last_full_month %m-% years(1) %>% format("%B %Y")
                                , " to ", last_full_month %>% format("%B %Y"))]

tauchart(yearly_growth, width = "800px", height = "300px") %>%
  tau_bar("placeholder", "yoy_growth", color = "Bank") %>%
  tau_color_manual(values = n26_colors) %>%
  tau_legend() %>%
  tau_guide_y(label = "Year over Year Growth", tick_format = "%") %>% tau_guide_x(label = " ") %>%
  tau_tooltip(c("Dates", "Bank", "yoy_growth"))

Notes:

Google Trends search data. Median weekly performance during r last_month$month[1] %>% format("%B %Y") vs. 12 months before.

Opportunity:

In terms of search mindshare, NUMBER26 has experienced phenomenal growth of over r yearly_growth[Bank == "NUMBER26", round(100*yoy_growth)]% over the past year, while leader comdirect experienced a slight decline in interest of r yearly_growth[Bank == "comdirect", round(100*yoy_growth)]%.

appReleases[, date := val %>% as.Date]
number26$trend[, value := value / 100]
number26$trend[, Major.App.Version.Released := appReleases[Feature.Release == TRUE][order(date)][start <= date &
                                                   end >= date, Release.Number] %>% tail(1), by = .(start, end)]
number26$trend[, Current.App.Version := appReleases[order(date)][end >= date, Release.Number] %>% tail(1), by = end]
number26$trend[, week_over_week := c(NA, value[2:length(value)] / value[1:length(value)-1]) - 1]

number26$trend[, Date := start %>% format("%B %e, %Y")]

account_growth <- monthly_medians[Bank == "NUMBER26"
                , 100000*(value[month == last_full_month] /
                            value[month == last_full_month %m-% months(6)] - 1)] %>% format(big.mark = ",")

---

Foot on the Gas

appReleaseTrend <- copy(number26$trend)
appReleaseTrend[, end := NULL]

tauchart(appReleaseTrend, width = "800px", height = "300px") %>%
  tau_line("start", "value", color = "Bank") %>%
  tau_guide_x(tick_format="%b-%y", label = "Date", tick_period = "week") %>%
  tau_guide_y(label = "Google Trend Rank", tick_format = "%", min = 0, max = 1, auto_scale = FALSE) %>%
  tau_color_manual(values = rep(n26_colors[2], 10)) %>%
  tau_legend() %>%
  tau_annotations(appReleases[, .(dim, val, text, color)]) %>%
  tau_trendline(models = "exponential") %>%
  tau_guide_gridlines(show_x = FALSE) %>% tau_tooltip(fields = c("Date", "value", "Current.App.Version"
                                                                 , "Major.App.Version.Released"))

Notes:

Google Trends search data.

Opportunity:

In terms of search mindshare, NUMBER26 fits an exponential growth curve quite nicely. Very conservatively estimating overall growth using the average search growth rate over the past six months and last year's average of 100k accounts per year, NUMBER26 might be expected to add r account_growth accounts in the next year, over doubling in size. And that's only looking at German growth rates (and assuming that the 100k accounts in 2015 were predominantly German). A recent drop-off in growth calls into question the sustainability of the trend.

---

Revolution Every Release

tauchart(appReleaseTrend[!is.na(Major.App.Version.Released) & !Current.App.Version %in% c("1.0", "1.1")], width = "625px", height = "300px") %>%
  tau_bar("Major.App.Version.Released", "week_over_week", color = "Major.App.Version.Released_bool") %>%
  tau_color_manual(values = rep("#76c0c1", 15)) %>%
  tau_guide_y(tick_format = "%", label = "Percent Growth Week over Week") %>% tau_guide_x(label = "Release Number")

Notes:

Google Trends search data. App Release week Google Trend as percent of previous week. Major increases in search interest tied to app feature releases. Data plotted at beginning of each 7 day trend observation period. Versions 1.0 & 1.1 missing due to lack of trend data.

Opportunity:

Search mindshare appears highly associated with app releases, which is a good thing seeing as NUMBER26 sees its mobile-first platform as the main product and its key differentiator. Since 2.0, all app feature releases have been associated with spikes in search activity. The most recent new feature, access to the international money transfer service Transferwise (2.3), was associated with a spike equivalent to nearly all of the Google Trends gain since the 2.0 app launch.

geoTrends <- copy(konkurrenz$Top.Unterregionen.für..consorsbank)
geoTrends <- geoTrends[geoTrends$Unterregion != "Schaffhausen", ]
geoTrends <- as.data.table(geoTrends) #patch
# Add in Region
geoTrends[, Region := "Altes Bundesgebiet"]
geoTrends[Unterregion %in% c("Brandenburg", "Mecklenburg-Vorpommern", "Sachsen", "Sachsen-Anhalt", "Thüringen")
          , Region := "Neue Länder"]
geoTrends[Unterregion %in% c("Berlin", "Bremen","Hamburg"), Region := "Stadtstaaten"]
geoTrends[, Region := factor(Region, c("Altes Bundesgebiet", "Stadtstaaten", "Neue Länder"), ordered = TRUE)]

# Reformat Data
geoTrends %<>% reshape2::melt(id.vars = c("Unterregion", "Region"))
geoTrends[, variable := factor(variable, levels = c("comdirect", "number26", "consorsbank", "fidor.bank", "X1822direkt")
                             , labels = c("comdirect", "NUMBER26", "Consorsbank", "Fidor Bank", "1822direkt")
                             , ordered = TRUE)]

# Fix Bundeslaender
geoTrends[Unterregion == "Mecklenburg-Vorpommern", Unterregion := "Mecklenburg-Vor."]
geoTrends[Unterregion == "Nordrhein-Westfalen", Unterregion := "NRW"]
geoTrends[, Bundesland := Unterregion]

# Calculate Trend Data & Normalize
geoTrends <- geoTrends[, .(value = median(value) %>% as.numeric), by = .(Region, variable)]
geoTrends[, value := value / max(value[variable == "NUMBER26"])]
geoTrends[, Bank := variable]

---

Go East

tauchart(geoTrends, width = "800px", height = "300px") %>%
  tau_bar("Region", "value", color = "Bank") %>%
  tau_color_manual(values = n26_colors) %>%
  tau_legend() %>% tau_guide_y(label = "Google Trends Rank") %>%
  tau_guide_y(label = "Google Trends", tick_format = "%") %>%
  tau_tooltip(c("Region", "Bank", "value"))

Notes:

Google Trends search data. Regional values are the median across all Bundesländer within a given region. All values are scaled so that NUMBER26 in Stadtstaaten equals 100%.

Opportunity:

Search mindshare appears highly geographically concentrated, primarily in large cities and secondarily in former West German states. Getting a foothold in German cities in the East, like Leipzig and Dresden will be key to proving out the model within Germany.

---

Heard it On the Grapevine

tauchart(cd_complete[!variable %in% c("Number of Accounts", "Deposited Funds") & Number.of.Accounts != " "
                     , .(Bank, Date, Number.of.Accounts, Google.Trends
                         , Facebook.Likes, Twitter.Followers, variable, value_per_account_norm
                         , Likes.per.1k.Accounts = Facebook.Likes.per.1k.Accounts
                         , Followers.per.1k.Accts = Twitter.Followers.per.1k.Accounts)]
         , width = "800px", height = "300px") %>%
  tau_bar("variable", "value_per_account_norm", color = "Bank") %>%
  tau_color_economist() %>% tau_legend() %>% tau_guide_y(label = "Social Interest per Account") %>%
  tau_color_manual(values = c("#7c260b", "#76c0c1", "#6794a7", "#014d64", "#01a2d9")) %>%
  tau_guide_x(label = " ") %>% tau_guide_y(label = "Percent of N26", tick_format = "%") %>%
  tau_tooltip(c("Bank", "Date", "Number.of.Accounts", "Facebook.Likes",
              "Likes.per.1k.Accounts", "Twitter.Followers", "Followers.per.1k.Accts"))

pct_with_facebook <- cd_complete[Bank == "NUMBER26" & variable == "Facebook Likes", value_per_1kaccounts / 10]
proj_accounts <- 100000 * (pct_with_facebook / cd_complete[Bank == "comdirect" & variable == "Facebook Likes", value_per_1kaccounts / 10])

Notes:

All data on a per account basis and normalized against NUMBER26. Google Trends search data. Facebook and Twitter account data for NUMBER26 and competitors pulled in mid-March.

Opportunity:

Unlike its competitors, NUMBER26 is incredibly strong in terms of social engagement. Relative to the number of accounts, NUMBER26 has an unparalleled density of Twitter & Facebook engagement. Twitter is used as a frequent & informal help tool and the Facebook community appears to have generated high interest, even outside of the territories where service is currently offered. Leveraging this social network for further growth seems a likely path for expansion.

Already for every four account holders, NUMBER26 has a Facebook 'Like'. This unusually engaged community of fans can likely be deployed to expand the reach of the bank. If NUMBER26 had the same ratio of account holders to Facebook Likes as comdirect, it would have r format(proj_accounts, scientific = FALSE, big.mark = ",") accounts open already.

---

Bonus: Growing Pains?

uberweisung <- fread(system.file("uberweisungdauer.csv", package = "datascience"))
uberweisung[, timestamp := as.Date(timestamp)]
uberweisung[, Date.Completed := format(as.Date(timestamp), "%b %e, %Y")]
tauchart(uberweisung, width = "480px", height = "300px") %>%
  tau_line("timestamp", "time_diff_rel_min") %>%
  tau_color_manual(values = rep("#76c0c1", 15)) %>%
  tau_guide_y(label = "Hours til Completed Wire") %>%
  tau_guide_x(tick_format="%b-%y", label = "Date") %>%
  tau_tooltip(c("Date", "time_diff_rel_min"))

Notes:

Data pulled from NUMBER26 account and based on a weekly (Wedneday) dauerauftrag of 200 EUR from a comdirect account. All values are normalized against the fastest completed Uberweisung over the past 10+ months.

Opportunity:

The many expansion opportunities come with risks associated with maintaining the present high standard of service for existing account holders. After getting used to near instantaneous domestic money wires and push notifications confirming successful receipt of funds, increasing transaction delays in the past months (as seen above) appear to be mobilizing critique on social media.

---

Methodology

Report built in R as an R package and all source code is available on Github.com

Sources

• Google Trends
• gtrendsR library
• Source code for report document
• Build your own version

jlewis91/datascience documentation built on May 19, 2019, 12:46 p.m.