Capstone Project - Claim Insurance
Iman Subarkah
February 11, 2020
1 Intro
We are an insurance company that join with another financial technology company as business to business, we covered life and health our partner customer. Our partner challenged us to decrease our SLA of claim process and we have to got this challenged.
2 Libraries and Setup
Preparing Rmarkdown Document
# clear-up the environment
rm(list = ls())
# chunk options
knitr::opts_chunk$set(
message = FALSE,
warning = FALSE,
fig.align = "center",
comment = "#>"
)
# scientific notation
options(scipen = 9999)3 Datasets
Data already stored in our SQL Server Read data from sql server (local) using RODBC library
#local_conn = odbcConnect("local")
#claim_database <- sqlQuery(local_conn,"SELECT * from lbb_algoritma.dbo.view_claim")
#write.csv(claim_database, "claim_dashboard/data/claim.csv", sep = "|", row.names = FALSE, quote = FALSE)
claim <- read_csv("claim_dashboard/data/claim.csv", quote = "|")Check if data loaded successfuly and assigned to object
head(claim)Discover Data Structure
glimpse(claim)#> Observations: 11,186
#> Variables: 53
#> $ id <dbl> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ...
#> $ id_klaim <dbl> 2017, 2017, 2017, 2017, 2017, 2...
#> $ id_peserta <dbl> 2016.008, 2016.009, 2016.007, 2...
#> $ start_date <date> 2016-12-07, 2016-12-19, 2016-1...
#> $ end_date <date> 2017-10-07, 2017-10-19, 2017-0...
#> $ date_of_birth <date> 1995-11-30, 1993-12-20, 1984-0...
#> $ currency <chr> "IDR", "IDR", "IDR", "IDR", "ID...
#> $ sum_insured <dbl> 3600000, 1799000, 1798000, 2698...
#> $ participant_status <chr> "Maturity", "Maturity", "Maturi...
#> $ sales_room_city_external <chr> NA, NA, NA, NA, "Surabaya", NA,...
#> $ region <chr> "Unmapped", "Unmapped", "Unmapp...
#> $ category <chr> "Unmapped", "Unmapped", "Unmapp...
#> $ claim_type <chr> "Hospitalized", "Hospitalized",...
#> $ event_date <date> 2016-12-24, 2016-12-22, 2016-1...
#> $ register_date <date> 2017-01-10, 2017-01-25, 2017-0...
#> $ complete_doc_date <dttm> 2017-01-10 13:40:28, 2017-01-2...
#> $ verify_date <dttm> 2017-02-08 11:47:56, 2017-01-2...
#> $ decision_date <dttm> 2017-02-08 11:48:04, 2017-02-0...
#> $ payment_date <dttm> 2017-01-23, NA, 2017-02-27, NA...
#> $ claim_status <chr> "Paid", "Claim Rejected", "Paid...
#> $ claim_amount <dbl> 454200, 233400, 1899497, 320300...
#> $ paid_amount <dbl> 454200, 0, 1899497, 0, 0, 10271...
#> $ reject_reason <chr> "Non", "Out of policy coverage"...
#> $ aging_register_to_complete_doc <dbl> 0, 0, 0, 1, 0, 0, 1, 2, 1, 5, 4...
#> $ aging_complete_doc_to_verify <dbl> 21, 0, 5, 0, 0, 14, 2, 1, 10, 1...
#> $ aging_verify_to_decision <dbl> 0, 10, 1, 1, 1, 0, 1, 3, 0, 1, ...
#> $ aging_complete_doc_to_decision <dbl> 21, 10, 6, 1, 1, 14, 3, 4, 10, ...
#> $ aging_decision_to_payment <dbl> 0, 0, 2, 0, 0, 8, 0, 0, 5, 3, 2...
#> $ aging_register_to_payment <dbl> 21, 10, 8, 2, 1, 22, 4, 6, 16, ...
#> $ aging_complete_doc_to_payment <dbl> 21, 0, 8, 0, 0, 22, 0, 0, 15, 1...
#> $ sla_register_to_complete_doc <dbl> 90, 90, 90, 90, 90, 90, 90, 90,...
#> $ sla_complete_doc_to_verify <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1...
#> $ sla_verify_to_decision <dbl> 11, 11, 11, 11, 11, 11, 11, 11,...
#> $ sla_complete_doc_to_decision <dbl> 12, 12, 12, 12, 12, 12, 12, 12,...
#> $ sla_decision_to_payment <dbl> 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2...
#> $ sla_register_to_payment <dbl> 104, 104, 104, 104, 104, 104, 1...
#> $ sla_complete_doc_to_payment <dbl> 14, 14, 14, 14, 14, 14, 14, 14,...
#> $ status_sla_register_to_complete_doc <chr> "Meet SLA", "Meet SLA", "Meet S...
#> $ status_sla_complete_doc_to_verify <chr> "Below Expectation", "Meet SLA"...
#> $ status_sla_verify_to_decision <chr> "Meet SLA", "Meet SLA", "Meet S...
#> $ status_sla_complete_doc_to_decision <chr> "Below Expectation", "Meet SLA"...
#> $ status_sla_decision_to_payment <chr> "Meet SLA", "Meet SLA", "Meet S...
#> $ status_sla_register_to_payment <chr> "Meet SLA", "Meet SLA", "Meet S...
#> $ status_sla_complete_doc_to_payment <chr> "Below Expectation", "Meet SLA"...
#> $ meet_sla_register_to_complete_doc <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1...
#> $ below_sla_register_to_complete_doc <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0...
#> $ meet_sla_complete_doc_to_payment <dbl> 0, 1, 1, 1, 1, 0, 1, 1, 0, 1, 0...
#> $ below_sla_complete_doc_to_payment <dbl> 1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 1...
#> $ meet_sla_complete_doc_to_decision <dbl> 0, 1, 1, 1, 1, 0, 1, 1, 1, 1, 0...
#> $ below_sla_complete_doc_to_decision <dbl> 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1...
#> $ meet_sla_decision_to_payment <dbl> 1, 1, 1, 1, 1, 0, 1, 1, 0, 0, 1...
#> $ below_sla_decision_to_payment <dbl> 0, 0, 0, 0, 0, 1, 0, 0, 1, 1, 0...
#> $ diagnosa <chr> "Injuries to the abdomen", "OTH...Variable descriptions:
id: Running number atau nomor urutid_klaim: Id transaksi klaimid_peserta: Id unique pesertastart_date: Tanggal mulai peserta dalam asuransiend_date: Tanggal selesai peserta dalam asuransidate_of_birth: Tanggal lahir pesertacurrency: Mata uangsum_insured: Uang pertanggunganparticipant_status: Status kepesertaan asuransisales_room_city_external: Kota pada saat peserta membeli asuransiregion: Klasifikasi berdasarkan kota domisili pesertacategory: Klasifikasi berdasarkan media penjualan asuransiclaim_type: Tipe klaimevent_date: Tanggal Kejadianregister_date: Tanggal pengajuan klaimcomplete_doc_date: Tanggal kelengkapan dokumen klaimverify_date: Tanggal klaim diverifikasidecision_date: Tanggal klaim diputuskan untuk disetujui atau ditolakpayment_date: Tanggal klaim dibayarclaim_status: Status klaimclaim_amount: Jumlah klaim yang diajukanpaid_amount: Jumlah klaim yang dibayarreject_reason: Alasan klaim ditolakaging_register_to_complete_doc: Selisih hari dari tanggal pengajuan klaim ke tanggal dokumen lengkapaging_complete_doc_to_verify: Selisih hari dari tanggal dokumen lengkap ke tanggal verifikasi klaimaging_verify_to_decision: Selisih hari dari tanggal verifikasi klaim ke tanggal decisionaging_complete_doc_to_decision: Selisih hari dari tanggal dokumen lengkap ke tanggal decisionaging_decision_to_payment: Selisih hari dari tanggal decision ke tanggal klaim dibayaraging_register_to_payment: Selisih hari dari tanggal pengajuan klaim ke tanggal klaim dibayaraging_complete_doc_to_payment: Selisih hari dari tanggal dokumen lengkap ke tanggal klaim dibayarsla_register_to_complete_doc: Sla dari tanggal pengajuan klaim ke tanggal dokumen lengkapsla_complete_doc_to_verify: Sla dari tanggal dokumen lengkap ke tanggal verifikasi klaimsla_verify_to_decision: Sla dari tanggal verifikasi klaim ke tanggal decisionsla_complete_doc_to_decision: Sla dari tanggal dokumen lengkap ke tanggal decisionsla_decision_to_payment: Sla dari tanggal decision ke tanggal klaim dibayarsla_register_to_payment: Sla dari tanggal pengajuan klaim ke tanggal klaim dibayarsla_complete_doc_to_payment: Sla dari tanggal dokumen lengkap ke tanggal klaim dibayarstatus_sla_register_to_complete_doc: Meet SLA/Below Expectation untuk tanggal pengajuan klaim ke tanggal dokumen lengkapstatus_sla_complete_doc_to_verify: Meet SLA/Below Expectation untuk tanggal dokumen lengkap ke tanggal verifikasi klaimstatus_sla_verify_to_decision: Meet SLA/Below Expectation untuk tanggal verifikasi klaim ke tanggal decisionstatus_sla_complete_doc_to_decision: Meet SLA/Below Expectation untuk tanggal dokumen lengkap ke tanggal decisionstatus_sla_decision_to_payment: Meet SLA/Below Expectation untuk tanggal decision ke tanggal klaim dibayarstatus_sla_register_to_payment: Meet SLA/Below Expectation untuk tanggal pengajuan klaim ke tanggal klaim dibayarstatus_sla_complete_doc_to_payment: Meet SLA/Below Expectation untuk tanggal dokumen lengkap ke tanggal klaim dibayarmeet_sla_register_to_complete_doc: Jika Meet SLA = 1, Below Expectation = 0 untuk selisih tanggal pengajuan klaim ke tanggal dokumen lengkapbelow_sla_register_to_complete_doc: Jika Below Expectation = 1, Meet SLA = 0 untuk selisih tanggal pengajuan klaim ke tanggal dokumen lengkapmeet_sla_complete_doc_to_payment: Jika Meet SLA = 1, Below Expectation = 0 untuk selisih tanggal dokumen lengkap ke tanggal klaim dibayarbelow_sla_complete_doc_to_payment: Jika Below Expectation = 1, Meet SLA = 0 untuk selisih tanggal dokumen lengkap ke tanggal klaim dibayarmeet_sla_complete_doc_to_decision: Jika Meet SLA = 1, Below Expectation = 0 untuk selisih tanggal dokumen lengkap ke tanggal decisionbelow_sla_complete_doc_to_decision: Jika Below Expectation = 1, Meet SLA = 0 untuk selisih tanggal dokumen lengkap ke tanggal decisionmeet_sla_decision_to_payment: Jika Meet SLA = 1, Below Expectation = 0 untuk selisih tanggal decision ke tanggal klaim dibayarbelow_sla_decision_to_payment: Jika Below Expectation = 1, Meet SLA = 0 untuk selisih tanggal decision ke tanggal klaim dibayar
Checking some missing value
claim_data <- claim %>%
is.na() %>%
colSums()
claim_data#> id id_klaim
#> 0 0
#> id_peserta start_date
#> 0 0
#> end_date date_of_birth
#> 0 0
#> currency sum_insured
#> 0 0
#> participant_status sales_room_city_external
#> 0 21
#> region category
#> 0 0
#> claim_type event_date
#> 0 0
#> register_date complete_doc_date
#> 0 0
#> verify_date decision_date
#> 0 66
#> payment_date claim_status
#> 607 0
#> claim_amount paid_amount
#> 0 0
#> reject_reason aging_register_to_complete_doc
#> 67 0
#> aging_complete_doc_to_verify aging_verify_to_decision
#> 0 0
#> aging_complete_doc_to_decision aging_decision_to_payment
#> 0 0
#> aging_register_to_payment aging_complete_doc_to_payment
#> 0 0
#> sla_register_to_complete_doc sla_complete_doc_to_verify
#> 0 0
#> sla_verify_to_decision sla_complete_doc_to_decision
#> 0 0
#> sla_decision_to_payment sla_register_to_payment
#> 0 0
#> sla_complete_doc_to_payment status_sla_register_to_complete_doc
#> 0 0
#> status_sla_complete_doc_to_verify status_sla_verify_to_decision
#> 0 0
#> status_sla_complete_doc_to_decision status_sla_decision_to_payment
#> 0 0
#> status_sla_register_to_payment status_sla_complete_doc_to_payment
#> 0 0
#> meet_sla_register_to_complete_doc below_sla_register_to_complete_doc
#> 0 0
#> meet_sla_complete_doc_to_payment below_sla_complete_doc_to_payment
#> 0 0
#> meet_sla_complete_doc_to_decision below_sla_complete_doc_to_decision
#> 0 0
#> meet_sla_decision_to_payment below_sla_decision_to_payment
#> 0 0
#> diagnosa
#> 04 Set the theme
theme_iman <- theme(legend.key = element_rect(fill="black"),
legend.background = element_rect(color="white", fill="#263238"),
plot.subtitle = element_text(size=6, color="white"),
panel.background = element_rect(fill="#dddddd"),
panel.border = element_rect(fill=NA),
panel.grid.minor.x = element_blank(),
panel.grid.major.x = element_blank(),
panel.grid.major.y = element_line(color="darkgrey", linetype=2),
panel.grid.minor.y = element_blank(),
plot.background = element_rect(fill="#263238"),
text = element_text(color="white"),
axis.text = element_text(color="white"),
legend.position='none')5 Subsetting and Plotting
5.1 Preparing additional value
claim$Y_register_date <- lubridate::year(claim$register_date)
claim$M_register_date <- lubridate::month(claim$register_date, label = T, abbr = T)
claim$m_register_date <- lubridate::month(claim$register_date)
claim$YYYY_mm_register_date <- format(as.Date(claim$register_date, "%Y-%m-%d"), "%Y-%m")
claim$MM_YYYY_register_date <- paste(claim$M_register_date, claim$Y_register_date, sep="-")
claim$YQ_register_date <- lubridate::quarter(claim$register_date, with_year = TRUE)6 Capstone Project
6.1 Claim Number by Claim Type
6.1.1 Subsetting
claim_number_by_claim_type <- claim %>%
group_by(claim_type) %>%
summarise(total_claim_number = n()) %>%
ungroup() %>%
mutate(text = glue(
"Claim Typw: {claim_type}
Total Claim Number: {total_claim_number}"
))
# Compute percentages
claim_number_by_claim_type$fraction = claim_number_by_claim_type$total_claim_number /
sum(claim_number_by_claim_type$total_claim_number)
# Compute the cumulative percentages (top of each rectangle)
claim_number_by_claim_type$ymax = cumsum(claim_number_by_claim_type$fraction)
# Compute the bottom of each rectangle
claim_number_by_claim_type$ymin = c(0, head(claim_number_by_claim_type$ymax, n=-1))
# Compute label position
claim_number_by_claim_type$labelPosition <- (claim_number_by_claim_type$ymax + claim_number_by_claim_type$ymin) / 2
# Compute a good label
claim_number_by_claim_type$label <- paste0(claim_number_by_claim_type$claim_type, "\n value: ",
comma(claim_number_by_claim_type$total_claim_number, accuracy = NULL))
claim_number_by_claim_type6.1.2 Ploting
plot_claim_number_by_claim_type <- ggplot(data = claim_number_by_claim_type, aes(ymax=ymax, ymin=ymin, xmax=4, xmin=3, fill=claim_type)) +
geom_rect() +
coord_polar(theta="y") +
xlim(c(2, 4)) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 0, hjust = 1)) +
geom_rect() +
geom_label( x=3.5, aes(y=labelPosition, label=label), size=4) +
scale_fill_brewer(palette=3) +
coord_polar(theta="y") +
xlim(c(2, 4)) +
theme_void() +
theme(legend.position = "none") +
labs(y = "Total Claim Number",
x = "Claim Type")
plot_claim_number_by_claim_type
## Claim Amount by Claim Type ### Subsetting
claim_amount_by_claim_type <- claim %>%
group_by(claim_type) %>%
summarise(total_claim_amount = sum(claim_amount)) %>%
ungroup() %>%
mutate(text = glue(
"Claim Typw: {claim_type}
Total Claim Amount: {total_claim_amount}"
))
# Compute percentages
claim_amount_by_claim_type$fraction = claim_amount_by_claim_type$total_claim_amount /
sum(claim_amount_by_claim_type$total_claim_amount)
# Compute the cumulative percentages (top of each rectangle)
claim_amount_by_claim_type$ymax = cumsum(claim_amount_by_claim_type$fraction)
# Compute the bottom of each rectangle
claim_amount_by_claim_type$ymin = c(0, head(claim_amount_by_claim_type$ymax, n=-1))
# Compute label position
claim_amount_by_claim_type$labelPosition <- (claim_amount_by_claim_type$ymax + claim_amount_by_claim_type$ymin) / 2
# Compute a good label
claim_amount_by_claim_type$label <- paste0(claim_amount_by_claim_type$claim_type, "\n value: ",
comma(claim_amount_by_claim_type$total_claim_amount, accuracy = NULL))
claim_amount_by_claim_type6.1.3 Ploting
plot_claim_amount_by_claim_type <- ggplot(data = claim_amount_by_claim_type, aes(ymax=ymax, ymin=ymin, xmax=4, xmin=3, fill=claim_type)) +
geom_rect() +
coord_polar(theta="y") +
xlim(c(2, 4)) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 0, hjust = 1)) +
geom_rect() +
geom_label( x=3.5, aes(y=labelPosition, label=label), size=4) +
scale_fill_brewer(palette=3) +
coord_polar(theta="y") +
xlim(c(2, 4)) +
theme_void() +
theme(legend.position = "none") +
labs(y = "Total Claim Amount",
x = "Claim Type")
plot_claim_amount_by_claim_type
## Claim Number by Claim Status ### Subsetting
claim_number_by_claim_status <- claim %>%
group_by(claim_status) %>%
summarise(total_claim_number = n()) %>%
ungroup() %>%
mutate(text = glue(
"Claim Type: {claim_status}
Total Claim Number: {comma(total_claim_number, accuracy = NULL)}"
))
claim_number_by_claim_status6.1.4 Ploting
plot_claim_number_by_claim_status <-
ggplot(data = claim_number_by_claim_status, aes(x = claim_status,
y = total_claim_number,
text = text)) +
geom_col(aes(fill = claim_status), position = "dodge", show.legend = F)+
labs(y = "Total Claim Number",
x = "Claim Status") +
theme_minimal()
ggplotly(plot_claim_number_by_claim_status, tooltip = "text")claim_amount_by_claim_status <- claim %>%
group_by(claim_status) %>%
summarise(total_claim_amount = sum(claim_amount)) %>%
ungroup() %>%
mutate(text = glue(
"Claim Type: {claim_status}
Total Claim Amount: {comma(total_claim_amount, accuracy = NULL)}"
))
claim_amount_by_claim_status6.1.5 Ploting
plot_claim_amount_by_claim_status <-
ggplot(data = claim_amount_by_claim_status, aes(x = claim_status,
y = total_claim_amount,
text = text)) +
geom_col(aes(fill = claim_status), position = "dodge", show.legend = F)+
labs(y = "Total Claim Amount",
x = "Claim Status") +
scale_y_continuous(labels = unit_format(unit = "M", scale = 1e-6))+
theme_minimal()
ggplotly(plot_claim_amount_by_claim_status, tooltip = "text")6.2 Year On Year Claim Number
6.2.1 Subsetting
yoy_by_claim_number <- claim %>%
group_by(Y_register_date, M_register_date) %>%
summarise(total_claim_number = n()) %>%
ungroup() %>%
mutate(text = glue(
"Register Date: {M_register_date} - {Y_register_date}
Total Claim Number: {total_claim_number}"
))
yoy_by_claim_number6.2.2 Ploting
plot_yoy_by_claim_number <- ggplot(data = yoy_by_claim_number,
aes(fill=M_register_date,
y=total_claim_number,
x=interaction(Y_register_date, M_register_date))) +
geom_segment(aes(x=interaction(Y_register_date, M_register_date),
xend=interaction(Y_register_date, M_register_date),
y=0,
yend=total_claim_number),
color="grey")+
geom_point(aes(col = M_register_date, size = 4, text = text)) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
ggtitle("Year on Year by Claim Number") +
labs(y = "Total Claim Number",
x = "Year & Month of Register Date")
ggplotly(plot_yoy_by_claim_number, tooltip = "text") %>%
config(displaylogo = F) %>%
layout(showlegend = FALSE)6.3 Year On Year Claim Amount
6.3.1 Subsetting
yoy_by_claim_amount <- claim %>%
group_by(Y_register_date, M_register_date) %>%
summarise(total_claim_amount = sum(claim_amount)) %>%
ungroup() %>%
mutate(text = glue(
"Register Date: {M_register_date} - {Y_register_date}
Total Claim Amount: {total_claim_amount}"
))
yoy_by_claim_amount6.3.2 Ploting
plot_yoy_by_claim_amount <- ggplot(data = yoy_by_claim_amount,
aes(fill=M_register_date,
y=total_claim_amount,
x=interaction(Y_register_date, M_register_date))) +
geom_segment(aes(x=interaction(Y_register_date, M_register_date),
xend=interaction(Y_register_date, M_register_date),
y=0,
yend=total_claim_amount),
color="grey")+
geom_point(aes(col = M_register_date, size = 4, text = text)) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
scale_y_continuous(labels = unit_format(unit = "M", scale = 1e-6)) +
ggtitle("Year on Year by Claim Amount") +
labs(y = "Total Claim Amount",
x = "Year & Month of Register Date")
ggplotly(plot_yoy_by_claim_amount, tooltip = "text") %>%
config(displaylogo = F) %>%
layout(showlegend = FALSE)6.4 SLA Performance
6.4.1 Subsetting
claim_sla_complete_doc_to_payment <-
claim %>%
filter(claim_status == "Paid") %>%
group_by(
YYYY_mm_register_date,
status_sla_complete_doc_to_payment) %>%
summarise(
count_status_sla_complete_doc_to_payment =
sum(meet_sla_complete_doc_to_payment)+
sum(below_sla_complete_doc_to_payment)) %>%
ungroup() %>%
mutate(count_status = ifelse(status_sla_complete_doc_to_payment == "Meet SLA",
count_status_sla_complete_doc_to_payment,
-1*count_status_sla_complete_doc_to_payment)) %>%
arrange(desc(YYYY_mm_register_date)) %>%
mutate(text = glue(
"SLA Status: {status_sla_complete_doc_to_payment}
Register Date: {YYYY_mm_register_date}
Claim Number: {count_status_sla_complete_doc_to_payment}"
))6.4.2 Ploting
breaks_values <- pretty(claim_sla_complete_doc_to_payment$count_status)
plot_claim_sla_complete_doc_to_payment <- ggplot(data = claim_sla_complete_doc_to_payment,
aes(x = YYYY_mm_register_date,
y = count_status,
fill = status_sla_complete_doc_to_payment))+
geom_hline(yintercept = 0)+
geom_bar(stat = "identity")+
geom_point(aes(text = text)) +
coord_flip()+
scale_y_continuous(breaks = breaks_values,
labels = abs(breaks_values))+
theme_minimal() +
ggtitle("SLA Performance") +
labs(y = "SLA Status",
x = "Year & Month of Register Date")
ggplotly(plot_claim_sla_complete_doc_to_payment, tooltip = "text") %>%
config(displaylogo = F) %>%
layout(showlegend = F)6.5 What If Analysis
6.5.1 Subseting
#karena data di bawah Juli 2018 menggunakan SLA 14 hari maka data difilter hanya untuk yang menggunakan SLA 7 hari yaitu klaim dengan tanggal pengajuan di atas Juni 2018
SLA_x <- 2 #parameter input SLA ini silahkan diganti-ganti, jika ingin tahu bagaimana SLA performance jika SLA diturunkan
claim_sla_x <-
claim %>%
filter(claim_status == "Paid", register_date >= "2018-07-01") %>%
mutate(
status_sla_complete_doc_to_payment_new =
case_when(aging_complete_doc_to_payment > SLA_x ~ "Below Expectation",
TRUE ~ "Meet SLA")) %>%
select(
YYYY_mm_register_date,
status_sla_complete_doc_to_payment_new)
claim_sla_x_ready <-
claim_sla_x %>%
mutate(
meet_sla_complete_doc_to_payment_new =
case_when(status_sla_complete_doc_to_payment_new != 'Meet SLA' ~ 0, TRUE ~ 1),
below_sla_complete_doc_to_payment_new =
case_when(status_sla_complete_doc_to_payment_new != 'Below Expectation' ~ 0, TRUE ~ 1)
)
claim_sla_complete_doc_to_payment_x <-
claim_sla_x_ready %>%
group_by(
YYYY_mm_register_date,
status_sla_complete_doc_to_payment_new) %>%
summarise(
count_status_sla_complete_doc_to_payment_new =
sum(meet_sla_complete_doc_to_payment_new)+
sum(below_sla_complete_doc_to_payment_new)) %>%
ungroup() %>%
mutate(count_status = ifelse(status_sla_complete_doc_to_payment_new == "Meet SLA",
count_status_sla_complete_doc_to_payment_new,
-1*count_status_sla_complete_doc_to_payment_new)) %>%
arrange(desc(YYYY_mm_register_date)) %>%
mutate(text = glue(
"SLA Status: {status_sla_complete_doc_to_payment_new}
Register Date: {YYYY_mm_register_date}
Claim Number: {count_status_sla_complete_doc_to_payment_new}"
))
claim_sla_complete_doc_to_payment_x6.5.2 Ploting
breaks_values <- pretty(claim_sla_complete_doc_to_payment_x$count_status)
plot_claim_sla_complete_doc_to_payment_x <- ggplot(data = claim_sla_complete_doc_to_payment_x,
aes(x = YYYY_mm_register_date,
y = count_status,
fill = status_sla_complete_doc_to_payment_new))+
geom_hline(yintercept = 0)+
geom_bar(stat = "identity")+
geom_point(aes(text = text)) +
coord_flip()+
scale_y_continuous(breaks = breaks_values,
labels = abs(breaks_values))+
theme_minimal() +
ggtitle("What If Analysis") +
labs(y = "SLA Status",
x = "Year & Month of Register Date")
ggplotly(plot_claim_sla_complete_doc_to_payment_x, tooltip = "text") %>%
config(displaylogo = F) %>%
layout(showlegend = F)6.6 Claim Number by Claim Type
Our partner want to see how many claim number based on claim type ### Subseting
claim_number_by_type <-
claim %>%
group_by(claim_type, YQ_register_date) %>%
summarise(total_claim_number = n()) %>%
ungroup() %>%
mutate(text = glue(
"Claim Type: {claim_type}
Year & Quarter: {YQ_register_date}
Total Claim Number: {total_claim_number}"
))6.6.1 Plotting
plot_claim_number_by_claim_type <- ggplot(data = claim_number_by_type, aes(x = YQ_register_date,
y = total_claim_number)) +
geom_point(aes(color = claim_type, text = text), show.legend = F) +
geom_line(aes(color = claim_type), show.legend = F)+
labs(title = "Trend Total Claim Number Based on Claim Type",
y = "Total Claim Number",
x = "Quarter & Year Register Date") +
theme_iman
ggplotly(plot_claim_number_by_claim_type, tooltip = "text") %>%
config(displaylogo = F)6.7 Claim Amount by Claim Type
Our partner want to see how many claim amount based on claim type ### Subseting
claim_amount_by_type <-
claim %>%
group_by(claim_type, YQ_register_date) %>%
summarise(total_claim_amount = sum(claim_amount)) %>%
ungroup() %>%
mutate(text = glue(
"Claim Type: {claim_type}
Year & Quarter: {YQ_register_date}
Total Claim Amount: {total_claim_amount}"
)) 6.7.1 Plotting
plot_claim_amount_by_claim_type <- ggplot(data = claim_amount_by_type, aes(x = YQ_register_date,
y = total_claim_amount)) +
geom_point(aes(color = claim_type, text = text), show.legend = F) +
geom_line(aes(color = claim_type), show.legend = F)+
labs(title = "Trend Total Claim Amount Based on Claim Type",
y = "Total Claim Amount",
x = "Quarter & Year Register Date") +
scale_y_continuous(labels = unit_format(unit = "M", scale = 1e-6))+
theme_iman
ggplotly(plot_claim_amount_by_claim_type, tooltip = "text") %>%
config(displaylogo = F)6.8 Claim Number by Claim Status
Our partner want to see how many claim number based on claim status ### Subseting
claim_number_by_status <-
claim %>%
group_by(claim_status, Y_register_date) %>%
summarise(total_claim_number = n()) %>%
ungroup() %>%
mutate(text = glue(
"Claim Status: {claim_status}
Year: {Y_register_date}
Total Claim Number: {total_claim_number}"
))6.8.1 Plotting
plot_claim_number_by_claim_status <-
ggplot(data = claim_number_by_status, aes(x = Y_register_date,
y = total_claim_number,
text = text)) +
geom_col(aes(fill = claim_status), position = "dodge", show.legend = F)+
labs(title = "Trend Total Claim Number Based on Claim Status",
y = "Total Claim Number",
x = "Year Register Date") +
theme_iman
ggplotly(plot_claim_number_by_claim_status, tooltip = "text")6.9 Claim Amount by Claim Status
Our partner want to see how many claim amount based on claim status ### Subseting
claim_amount_by_status <-
claim %>%
group_by(claim_status, Y_register_date) %>%
summarise(total_claim_amount = sum(claim_amount)) %>%
ungroup() %>%
mutate(text = glue(
"Claim Status: {claim_status}
Year: {Y_register_date}
Total Claim Amount: {total_claim_amount}"
))6.9.1 Plotting
plot_claim_amount_by_claim_status <-
ggplot(data = claim_amount_by_status, aes(x = Y_register_date,
y = total_claim_amount,
text = text)) +
geom_col(aes(fill = claim_status), position = "dodge", show.legend = F)+
labs(title = "Trend Total Claim Amount Based on Claim Status",
y = "Total Claim Amount",
x = "Year Register Date") +
scale_y_continuous(labels = unit_format(unit = "M", scale = 1e-6))+
theme_iman
ggplotly(plot_claim_amount_by_claim_status, tooltip = "text")6.10 Claim Number by Region
Our partner want to see how many claim number based on region ### Subseting
claim_number_by_region <-
claim %>%
group_by(region, YQ_register_date) %>%
summarise(total_claim_number = n()) %>%
ungroup() %>%
mutate(text = glue(
"Region: {region}
Year & Quarter: {YQ_register_date}
Total Claim Number: {total_claim_number}"
))6.10.1 Plotting
plot_claim_number_by_region <- ggplot(data = claim_number_by_region, aes(x = YQ_register_date,
y = total_claim_number)) +
geom_point(aes(color = region, text = text), show.legend = F) +
geom_line(aes(color = region), show.legend = F)+
labs(title = "Trend Total Claim Number Based on Region",
y = "Total Claim Number",
x = "Quarter & Year Register Date") +
theme_iman
ggplotly(plot_claim_number_by_region, tooltip = "text") %>%
config(displaylogo = F)6.11 Claim Amount by Region
Our partner want to see how many claim amount based on region ### Subseting
claim_amount_by_region <-
claim %>%
group_by(region, YQ_register_date) %>%
summarise(total_claim_amount = sum(claim_amount)) %>%
ungroup() %>%
mutate(text = glue(
"Region: {region}
Year & Quarter: {YQ_register_date}
Total Claim Amount: {total_claim_amount}"
))6.11.1 Plotting
plot_claim_amount_by_region <- ggplot(data = claim_amount_by_region, aes(x = YQ_register_date,
y = total_claim_amount)) +
geom_point(aes(color = region, text = text), show.legend = F) +
geom_line(aes(color = region), show.legend = F)+
labs(title = "Trend Total Claim Amount Based on Region",
y = "Total Claim Amount",
x = "Quarter & Year Register Date") +
scale_y_continuous(labels = unit_format(unit = "M", scale = 1e-6))+
theme_iman
ggplotly(plot_claim_amount_by_region, tooltip = "text") %>%
config(displaylogo = F)6.12 Claim Number by Category
Our partner want to see how many claim number based on category ### Subseting
claim_number_by_category <-
claim %>%
group_by(category, YQ_register_date) %>%
summarise(total_claim_number = n()) %>%
ungroup() %>%
mutate(text = glue(
"Category: {category}
Year & Quarter: {YQ_register_date}
Total Claim Number: {total_claim_number}"
))6.12.1 Plotting
plot_claim_number_by_category <- ggplot(data = claim_number_by_category, aes(x = YQ_register_date,
y = total_claim_number)) +
geom_point(aes(color = category, text = text), show.legend = F) +
geom_line(aes(color = category), show.legend = F)+
labs(title = "Trend Total Claim Number Based on Category",
y = "Total Claim Number",
x = "Quarter & Year Register Date") +
theme_iman
ggplotly(plot_claim_number_by_category, tooltip = "text") %>%
config(displaylogo = F)6.13 Claim Amount by Category
Our partner want to see how many claim amount based on category ### Subseting
claim_amount_by_category <-
claim %>%
group_by(category, YQ_register_date) %>%
summarise(total_claim_amount = sum(claim_amount)) %>%
ungroup() %>%
mutate(text = glue(
"Category: {category}
Year & Quarter: {YQ_register_date}
Total Claim Amount: {total_claim_amount}"
))6.13.1 Plotting
plot_claim_amount_by_category <- ggplot(data = claim_amount_by_category, aes(x = YQ_register_date,
y = total_claim_amount)) +
geom_point(aes(color = category, text = text), show.legend = F) +
geom_line(aes(color = category), show.legend = F)+
labs(title = "Trend Total Claim Amount Based on Category",
y = "Total Claim Amount",
x = "Quarter & Year Register Date") +
scale_y_continuous(labels = unit_format(unit = "M", scale = 1e-6))+
theme_iman
ggplotly(plot_claim_amount_by_category, tooltip = "text") %>%
config(displaylogo = F)6.14 Claim SLA Performance
6.14.1 Subseting
claim_sla_complete_doc_to_payment <-
claim %>%
filter(claim_status == "Paid") %>%
group_by(
YYYY_mm_register_date,
status_sla_complete_doc_to_payment) %>%
summarise(
count_status_sla_complete_doc_to_payment =
sum(meet_sla_complete_doc_to_payment)+
sum(below_sla_complete_doc_to_payment)) %>%
ungroup() %>%
arrange(desc(YYYY_mm_register_date)) %>%
mutate(text = glue(
"SLA Status: {status_sla_complete_doc_to_payment}
Register Date: {YYYY_mm_register_date}
Total Claim Number: {count_status_sla_complete_doc_to_payment}"
)) 6.14.2 Plotting
plot_claim_sla_complete_doc_to_payment <-
ggplot(data = claim_sla_complete_doc_to_payment,
aes( x = YYYY_mm_register_date,
y = count_status_sla_complete_doc_to_payment,
text = text)) +
geom_col(aes(fill = status_sla_complete_doc_to_payment), position = "fill", show.legend = F)+
labs(title = "SLA Performance - Complete Doc to Payment",
y = "Claim Number",
x = "Register Date")+
coord_flip()+
theme_iman
ggplotly(plot_claim_sla_complete_doc_to_payment, tooltip = "text")6.15 Claim SLA Performance “What If Analysis”
6.15.1 Subseting
#karena data di bawah Juli 2018 menggunakan SLA 14 hari maka data difilter hanya untuk yang menggunakan SLA 7 hari yaitu klaim dengan tanggal pengajuan di atas Juni 2018
SLA_x <- 2 #parameter input SLA ini silahkan diganti-ganti, jika ingin tahu bagaimana SLA performance jika SLA diturunkan
claim_sla_x <-
claim %>%
filter(claim_status == "Paid", register_date >= "2018-07-01") %>%
mutate(
status_sla_complete_doc_to_payment_new =
case_when(aging_complete_doc_to_payment > SLA_x ~ "Below Expectation",
TRUE ~ "Meet SLA")) %>%
select(
YYYY_mm_register_date,
status_sla_complete_doc_to_payment_new)
claim_sla_x_ready <-
claim_sla_x %>%
mutate(
meet_sla_complete_doc_to_payment_new =
case_when(status_sla_complete_doc_to_payment_new != 'Meet SLA' ~ 0, TRUE ~ 1),
below_sla_complete_doc_to_payment_new =
case_when(status_sla_complete_doc_to_payment_new != 'Below Expectation' ~ 0, TRUE ~ 1)
)
claim_sla_complete_doc_to_payment_x <-
claim_sla_x_ready %>%
group_by(
YYYY_mm_register_date,
status_sla_complete_doc_to_payment_new) %>%
summarise(
count_status_sla_complete_doc_to_payment_new =
sum(meet_sla_complete_doc_to_payment_new)+
sum(below_sla_complete_doc_to_payment_new)) %>%
ungroup() %>%
arrange(desc(YYYY_mm_register_date)) %>%
mutate(text = glue(
"SLA Status: {status_sla_complete_doc_to_payment_new}
Register Date: {YYYY_mm_register_date}
Total Claim Number: {count_status_sla_complete_doc_to_payment_new}"
)) 6.15.2 Plotting
plot_claim_sla_complete_doc_to_payment_new <-
ggplot(data = claim_sla_complete_doc_to_payment_x,
aes( x = YYYY_mm_register_date,
y = count_status_sla_complete_doc_to_payment_new,
text = text)) +
geom_col(aes(fill = status_sla_complete_doc_to_payment_new), position = "fill", show.legend = F)+
labs(title = "SLA Performance - Complete Doc to Payment",
y = "Claim Number",
x = "Register Date")+
coord_flip()+
theme_iman
ggplotly(plot_claim_sla_complete_doc_to_payment_new, tooltip = "text")