1.1

Charge the libraries

library(dplyr)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

library(tidyverse)

## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ forcats   1.0.0     ✔ readr     2.1.4
## ✔ ggplot2   3.4.4     ✔ stringr   1.5.1
## ✔ lubridate 1.9.3     ✔ tibble    3.2.1
## ✔ purrr     1.0.2     ✔ tidyr     1.3.0

## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(lubridate)
library(conflicted)

Get all the data

ds_deposit <- read.csv("D:/Bitso Challenge/databases_csv/ds_deposit.csv", header = FALSE, as.is = TRUE)
ds_withdrawal <- read.csv("D:/Bitso Challenge/databases_csv/ds_withdrawal.csv", header = FALSE, as.is = TRUE)
ds_trade <- read.csv("D:/Bitso Challenge/databases_csv/ds_trade.csv", header = FALSE, as.is = TRUE)
ds_price_data <- read.csv("D:/Bitso Challenge/databases_csv/ds_price_data.csv", as.is = TRUE)
options(scipen = 999)

Deposit cleaning

colnames(ds_deposit) <- ds_deposit[1, ]
ds_deposit = rename(ds_deposit,c("NA","id" = "NA"))
ds_deposit <- ds_deposit[-c(1),]
rownames(ds_deposit) <- NULL

Find users that deposit per week

ds_deposit$created <-
as.Date(ds_deposit$created,format = "%Y-%m-%j")
ds_deposit$Week <-
strftime(c(ds_deposit$created), format = "%V")

Create a table to show User deposits x week

Deposits_Week <-
ds_deposit %>%
  group_by(Week) %>%
    summarize(Users_That_Deposit = n_distinct(user_id))

Clean ds_withdrawal

colnames(ds_withdrawal) <- ds_withdrawal[1, ]
ds_withdrawal = rename(ds_withdrawal,c("NA","id" ="NA"))
ds_withdrawal <- ds_withdrawal[-c(1), ]
rownames(ds_withdrawal) <- NULL

Find users that withdrawal per week

ds_withdrawal$created <-
  as.Date(ds_withdrawal$created,format = "%Y-%m-%j")
ds_withdrawal$Week <-
  strftime(c(ds_withdrawal$created), format = "%V")

Create a table to show User Withdrawal x week

Withdrawal_Week <-
ds_withdrawal %>%
  group_by(Week) %>%
    summarize(Users_That_withdrawal = n_distinct(user_id))

Merge tables to show both

This way you can have the info. separately and also merged.

Deposits_Withdrawal_Week <-
  merge(Withdrawal_Week,Deposits_Week,by = "Week")
print(Deposits_Withdrawal_Week)

##   Week Users_That_withdrawal Users_That_Deposit
## 1   49                    16                 18
## 2   50                    14                 11
## 3   51                    11                 11
## 4   52                     3                  7
## 5   53                     4                  5

1.2

Declare a new column in both tables to classify the transaction

ds_deposit$type <- "deposit"
ds_withdrawal$type <- "withdrawal"

Change the datatype

ds_deposit$amount <- as.numeric(as.character(ds_deposit$amount))

Rename the date to merge both tables

ds_deposit = rename(ds_deposit,c("created","date" = "created"))

Get the deposit amount in usd

Deposit_in_Usd<- merge(ds_deposit, ds_price_data, by = "date", all.x = TRUE)
convert_to_usd <- function(currency, amount, usd) {
  if (currency == "usd") {
    return(amount)
  } else {
    return(amount * usd)
  }
}

Apply the function

Deposit_in_Usd$amount_usd <- mapply(convert_to_usd,
                                    Deposit_in_Usd$currency,
                                    Deposit_in_Usd$amount,
                                    Deposit_in_Usd$usd)

Change the amount datatype

ds_withdrawal$amount <- as.numeric(as.character(ds_withdrawal$amount))

Rename the date to merge both tables

ds_withdrawal = rename(ds_withdrawal,c("created","date" = "created"))

Get the withdrawal amount in usd

Withdrawal_in_Usd<- merge(ds_withdrawal, ds_price_data, by = "date", all.x = TRUE)
convert_to_usd <- function(currency, amount, usd) {
  if (currency == "usd") {
    return(amount)
  } else {
    return(amount * usd)
  }
}

Apply the function

Withdrawal_in_Usd$amount_usd <- mapply(convert_to_usd,
                                       Withdrawal_in_Usd$currency,
                                       Withdrawal_in_Usd$amount,
                                       Withdrawal_in_Usd$usd)

Get a new table to get the net deposit amount per week

all_transactions <- rbind(Deposit_in_Usd, Withdrawal_in_Usd)
net_deposit <- sum(all_transactions$amount * ifelse(all_transactions$type == "deposit", 1, -1))

Summarize the Net Deposit

net_deposit <- all_transactions %>%
  group_by(Week) %>%
  summarise(net_deposit_usd = sum(amount_usd))

net_deposit$net_deposit_usd <- format(net_deposit$net_deposit_usd, big.mark = ".", decimal.mark = ",")

print(net_deposit)

## # A tibble: 5 × 2
##   Week  net_deposit_usd    
##   <chr> <chr>              
## 1 49    "  268.662.822.487"
## 2 50    "   53.405.045.688"
## 3 51    "  345.336.619.382"
## 4 52    "9.747.567.116.848"
## 5 53    "1.912.651.343.516"

1.3

Create the function to categorize the currencies

classify_currency <- function(currency) {
  # List of known fiat currencies
  fiat_currencies <- c("usd", "mxn", "ars", "brl")
  # List of known cryptocurrencies
  crypto_currencies <- c("btc", "eth", "xrp", "ltc", "bch","bat","dai","gnt","mana","tusd","etc","rbtc","bsv")  # Add more if needed

  # Check if the currency is fiat or crypto
  if (currency %in% fiat_currencies) {
    return("Fiat")
  } else if (currency %in% crypto_currencies) {
    return("Crypto")
  } else {
    return("Unknown") 
  }
}

Apply an showing the info.

ds_deposit$currency_type <- sapply(ds_deposit$currency, classify_currency)
head(ds_deposit)

1.4

Merging 2 tables to get all transactions per day per user

ds_deposit <- subset(ds_deposit, select = -currency_type)
all_transactions <- rbind(ds_deposit, ds_withdrawal)
# get active users per week
active_users_per_week <- aggregate(
  user_id ~ Week,
  data = all_transactions,
  FUN = function(x) length(unique(x)))

Change the column names

colnames(active_users_per_week) <- c("week", "active_users")
print(active_users_per_week)

##   week active_users
## 1   49           28
## 2   50           22
## 3   51           18
## 4   52           10
## 5   53            8

2.1

Clean the table

colnames(ds_trade) <- ds_trade[1, ]
ds_trade <- ds_trade[-c(1), ]
rownames(ds_trade) <- NULL
ds_trade$created <- as.Date(ds_trade$created,format = "%Y-%m-%j")
ds_trade = rename(ds_trade,c("created","date" = "created"))

Change the datatype

ds_price_data$usd <- as.numeric(ds_price_data$usd)
ds_trade$major_fee <- as.numeric(ds_trade$major_fee)
ds_trade$minor_fee <- as.numeric(ds_trade$minor_fee)

Merge the tables to get the seller fee in usd

Seller_fee <- merge(ds_trade, ds_price_data, by = c("date", "major_currency"), all.x = TRUE)
Seller_fee <- mutate(Seller_fee, Fee_usd_seller = ifelse(major_currency == "usd", major_fee, major_fee * usd))

Merge the table to get the buyer fee in usd

ds_price_data_minor <- rename(ds_price_data, minor_currency = major_currency)
ds_trade$major_fee <- as.numeric(ds_trade$major_fee)
Buyer_fee <- merge(ds_trade, ds_price_data_minor, by = c("date", "minor_currency"), all.x = TRUE)
Buyer_fee <- mutate(Buyer_fee, Fee_usd_buyer = ifelse(minor_currency == "usd", minor_fee, minor_fee * usd))

Change the date to week and sumarizing the fees

Seller_fee_week <- Seller_fee %>%
  mutate(week = lubridate::week(date)) %>%
  group_by(week) %>%
  summarize(Fee_usd_seller_total = sum(Fee_usd_seller, na.rm = TRUE))
Buyer_fee_week <- Buyer_fee %>%
  mutate(week = lubridate::week(date)) %>%
  group_by(week) %>%
  summarize(Fee_usd_buyer_total = sum(Fee_usd_buyer, na.rm = TRUE))

Merge both in 1 table

Revenue_week <- merge(Seller_fee_week, Buyer_fee_week, by = "week", all = TRUE) %>%
  mutate(Total_Fee_usd = Fee_usd_seller_total + Fee_usd_buyer_total) %>%
  select(-Fee_usd_seller_total, -Fee_usd_buyer_total)

Show the result

Revenue_per_week <- Revenue_week
Revenue_week$Total_Fee_usd <- format(Revenue_week$Total_Fee_usd, big.mark = ".", decimal.mark = ",")
print(Revenue_week)

##   week Total_Fee_usd
## 1   48     33.308,28
## 2   49     58.820,51
## 3   50    124.393,10
## 4   51    275.450,62
## 5   52    112.611,67
## 6   53     54.631,84

2.2

Make a new column to show the date in weeks

ds_trade$Week <- strftime(c(ds_trade$date), format = "%V")

Make a table to show paying users

paying_users <-
  ds_trade %>%
  group_by(Week) %>%
  summarize(buyers = ifelse(amount != 0,major_user_id,0),
            sellers = ifelse(value != 0,minor_user_id,0))

## Warning: Returning more (or less) than 1 row per `summarise()` group was deprecated in
## dplyr 1.1.0.
## ℹ Please use `reframe()` instead.
## ℹ When switching from `summarise()` to `reframe()`, remember that `reframe()`
##   always returns an ungrouped data frame and adjust accordingly.
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
## generated.

## `summarise()` has grouped output by 'Week'. You can override using the
## `.groups` argument.

Make a new table to show the sum of paying users per week

result <-
  paying_users %>%
  group_by(Week) %>%
summarize(paying_users= n_distinct(buyers,sellers))

print(result)

## # A tibble: 5 × 2
##   Week  paying_users
##   <chr>        <int>
## 1 49              53
## 2 50              44
## 3 51              46
## 4 52              54
## 5 53              39

2.3

Weekly ARPU

buyer <- ds_trade %>%
  group_by(Week) %>%
  summarise(user = minor_user_id)

## Warning: Returning more (or less) than 1 row per `summarise()` group was deprecated in
## dplyr 1.1.0.
## ℹ Please use `reframe()` instead.
## ℹ When switching from `summarise()` to `reframe()`, remember that `reframe()`
##   always returns an ungrouped data frame and adjust accordingly.
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
## generated.

## `summarise()` has grouped output by 'Week'. You can override using the
## `.groups` argument.

seller <- ds_trade %>%
  group_by(Week) %>%
  summarise(user = major_user_id)

## Warning: Returning more (or less) than 1 row per `summarise()` group was deprecated in
## dplyr 1.1.0.
## ℹ Please use `reframe()` instead.
## ℹ When switching from `summarise()` to `reframe()`, remember that `reframe()`
##   always returns an ungrouped data frame and adjust accordingly.
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
## generated.

## `summarise()` has grouped output by 'Week'. You can override using the
## `.groups` argument.

users <- rbind(buyer,seller)
users <- users %>%
  group_by(Week) %>%
  summarise(users = n_distinct(user))

Change the dataype

users$Week <- as.factor(users$Week)
names(Revenue_per_week)[names(Revenue_per_week) == "week"] <- "Week"

Revenue_per_week$Week <- as.factor(Revenue_per_week$Week)

# Convertir las columnas a los tipos de datos adecuados en 'Revenue_per_week'
Revenue_week <- as.data.frame(lapply(Revenue_per_week, type.convert))

## Warning in type.convert.default(X[[i]], ...): 'as.is' should be specified by
## the caller; using TRUE

## Warning in type.convert.default(X[[i]], ...): 'as.is' should be specified by
## the caller; using TRUE

# Fusionar 'Revenue_per_week' y 'users' por la columna "Week"
revenue_per_user <- merge(Revenue_week, users, by = "Week")

# Verificar la estructura de 'revenue_per_user' después de la fusión
str(revenue_per_user)

## 'data.frame':    5 obs. of  3 variables:
##  $ Week         : int  49 50 51 52 53
##  $ Total_Fee_usd: num  58821 124393 275451 112612 54632
##  $ users        : int  24 13 17 18 11

# Convertir la columna 'Total_Fee_usd' a numérico
revenue_per_user$Total_Fee_usd <- as.numeric(revenue_per_user$Total_Fee_usd)

# Calcular ARPU (ingreso promedio por usuario activo)
ARPU <- revenue_per_user %>%
  group_by(Week) %>%
  summarise(ARPU = sum(Total_Fee_usd) / sum(users))

# Formatear ARPU para mostrar números con separador de miles y coma decimal
ARPU$ARPU <- format(ARPU$ARPU, big.mark = ".", decimal.mark = ",")

# Imprimir ARPU
print(ARPU)

## # A tibble: 5 × 2
##    Week ARPU        
##   <int> <chr>       
## 1    49 " 2.450,855"
## 2    50 " 9.568,700"
## 3    51 "16.202,978"
## 4    52 " 6.256,204"
## 5    53 " 4.966,531"

weekly ARPPU

Change the datatype

names(Revenue_per_week)[names(Revenue_per_week) == "week"] <- "Week"
Revenue_per_week$Total_Fee_usd <- as.numeric(Revenue_per_week$Total_Fee_usd)
result$paying_users <- as.numeric(result$paying_users)
result$Week <- as.factor(result$Week)
Revenue_per_week$Week <- as.factor(Revenue_per_week$Week)
names(Revenue_per_week)[names(Revenue_per_week) == "week"] <- "Week"

Get the ARPPU

ARPPU <- merge(Revenue_per_week, result, by = "Week") %>%
  group_by(Week) %>%
  summarise(ARPPU = sum(Total_Fee_usd) / sum(paying_users))

ARPPU$ARPPU <- format(ARPPU$ARPPU, big.mark = ".", decimal.mark = ",")
print(ARPPU)

## # A tibble: 5 × 2
##   Week  ARPPU    
##   <fct> <chr>    
## 1 49    1.109,821
## 2 50    2.827,116
## 3 51    5.988,057
## 4 52    2.085,401
## 5 53    1.400,816

Bonus

Import the dataframe

ds_user_balance <- read.csv("D:/Bitso Challenge/databases_csv/ds_user_balance.csv", as.is = TRUE)

Charge the libraries

library(dplyr)
library(ggplot2)

Change the datatype

ds_user_balance$date <- as.Date(ds_user_balance$date)

Make the categories

df_summary <- ds_user_balance %>%
  mutate(month = format(date, "%Y-%m")) %>%
  group_by(month, user_id) %>%
  summarize(
    balance_positive = sum(balance_usd > 0),
    balance_previous_positive = sum(dplyr::lag(balance_usd, default = 0) > 0),
    balance_previous_zero = sum(dplyr::lag(balance_usd, default = 0) == 0),
    balance_zero = sum(balance_usd == 0)
  ) %>%
  mutate(category = case_when(
    balance_positive > 0 & balance_previous_positive == 0  ~ "New user",
    balance_positive > 0 & balance_previous_positive > 0 ~ "Steady user",
    balance_zero > 0 & balance_previous_positive > 0 ~ "Lost user",
    balance_positive > 0 & balance_previous_zero > 0 ~ "Recovered user",
    TRUE ~ "Other"
  )) %>%
  ungroup() %>%
  group_by(month, category) %>%
  summarize(users = n()) %>%
  mutate(percentage = users / sum(users) * 100)

## `summarise()` has grouped output by 'month'. You can override using the
## `.groups` argument.
## `summarise()` has grouped output by 'month'. You can override using the
## `.groups` argument.

Make the plot

ggplot(df_summary, aes(x = month, y = percentage, fill = category)) +
  geom_bar(stat = "identity", position = "stack") +
  scale_fill_manual(values = c("New user" = "blue", "Steady user" = "green", "Lost user" = "red", "Recovered user" = "orange", "Other" = "gray")) +
  labs(
    title = "Porcentaje de usuarios por categoría",
    y = "Porcentaje de usuarios",
    x = "Mes",
    fill = "Categoría"
  ) +
  theme_minimal()

Bitso Challenge

Facundo Nuñez

2024-05-29

1.1

Charge the libraries

Get all the data

Deposit cleaning

Find users that deposit per week

Create a table to show User deposits x week

Clean ds_withdrawal

Find users that withdrawal per week

Create a table to show User Withdrawal x week

Merge tables to show both

This way you can have the info. separately and also merged.

1.2

Declare a new column in both tables to classify the transaction

Change the datatype

Rename the date to merge both tables

Get the deposit amount in usd

Apply the function

Change the amount datatype

Rename the date to merge both tables

Get the withdrawal amount in usd

Apply the function

Get a new table to get the net deposit amount per week

Summarize the Net Deposit

1.3

Create the function to categorize the currencies

Apply an showing the info.

1.4

Merging 2 tables to get all transactions per day per user

Change the column names

2.1

Clean the table

Change the datatype

Merge the tables to get the seller fee in usd

Merge the table to get the buyer fee in usd

Change the date to week and sumarizing the fees

Merge both in 1 table

Show the result

2.2

Make a new column to show the date in weeks

Make a table to show paying users

Make a new table to show the sum of paying users per week

2.3

Weekly ARPU

Change the dataype

weekly ARPPU

Change the datatype

Get the ARPPU

Bonus

Import the dataframe

Charge the libraries

Change the datatype

Make the categories

Make the plot