require(tidyverse)
require(DT)This example shows the general process to follow to fill in the LURCS templates, based on the granularity of the available data.
For the purpose of this illustrative example, a simplified template with two segmentation variables, age_bucket_lapse and distribution_channel, and two quantitative variables, TP_without_RM and lambda_BE, is considered.
The former quantitative variable is a monetary amount, while the latter is a probability. The simplified LURCS template identifies 9 segments, given by the possible combination of the two segmentation variables.
In most of the current LURCS templates the value “all” was introduced for several segmentation variables, to give more flexibility and accommodate different data availability. For simplicity, this value is not included in the simplified LURCS templates within scenarios A and B, while it is addressed in scenario C.
## possible values of the segmentation variables in the LURCS template
LURCS_age_bucket_lapse = c("[0-39)", "[40-69)", "70+")
LURCS_distribution_channel = c("direct", "banking", "other")
simplified.LURCS = expand.grid("LURCS_age_bucket_lapse" = LURCS_age_bucket_lapse,
"LURCS_distribution_channel" = LURCS_distribution_channel) %>%
mutate("LURCS_segment" = 1:n()) %>%
relocate(LURCS_segment, 1)simplified.LURCS %>% mutate("lambda_BE" = "",
"TP_without_RM" = "") %>%
DT::datatable(extensions = 'Buttons', rownames= F,
filter = list(position = 'top'),
options = list(columnDefs = list(list(className = 'dt-center', targets = "_all")),
dom = 'Blfrtip', buttons = c('copy', 'csv', 'excel')))Depending on the granularity of the data available to the undertaking, there are three possible situations.
The database of policies covered by the relevant internal model, available to the undertaking, includes all segmentation and quantitative variables at policy level, or at HRG/ModelPoint/Cluster that is equally or more granular than the one identified by the combination of segmentation variables. For each of these policies/HRG/ModelPoints/clusters, a relevant weight driver (the suggested weight driver is the sum insured) is available.
In this case, the policy database would have the following form:
set.seed(12)
db.pol = tibble(
"policy_id" = 1:10,
"age" = round(runif(10, 20, 60)),
"distribution_channel" = sample(LURCS_distribution_channel, size=10, replace=T),
"relevant_weight_driver" = sample(c(10, 20, 50), size=10, replace=T),
"lambda_BE"= round(runif(n=10, min=0.01, 0.1), 2),
"TP_without_RM" = round(runif(10, 5, 20), 1)
)It is then possible to map each policy to the segments identified by the LURCS template. In this case, only the age needs to be mapped from the granularity of the undertaking to the granularity of the template, while the distribution_channel variable has the same granularity.
mapped.db.pol = db.pol %>%
mutate(LURCS_age_bucket_lapse = cut(age,breaks = c(0,39,69,100),
labels=LURCS_age_bucket_lapse)) %>%
left_join(simplified.LURCS, by = c("LURCS_age_bucket_lapse",
"distribution_channel" = "LURCS_distribution_channel")) %>%
relocate(LURCS_segment,.before = 2)The next step is to compute the quantitative variables for each segment required by the LURCS template. THe monetary amount variable is calculated as the sum across the policies within each segment, while the non monetary amount variable is calculated as the weighted average across the policies within each segment, using a relevant weight driver (the suggested weight driver is the sum insured).
calculated.db = mapped.db.pol %>%
group_by(LURCS_segment,
LURCS_age_bucket_lapse,
distribution_channel) %>%
summarise(lambda_BE = round(weighted.mean(x = lambda_BE, w = relevant_weight_driver),3),
TP_without_RM = sum(TP_without_RM))Finally, the simplified LURCS template would appear in the following way, where the empty spots in the quantitative variables correspond to LURCS segments that do not identify any policies of the undertaking:
final = simplified.LURCS %>%
left_join(calculated.db %>% select(LURCS_segment, lambda_BE, TP_without_RM)) %>%
mutate(across(everything(), ~ replace(.x, is.na(.x), ""))) In the database of policies covered by the relevant internal model, available to the undertaking, only segmentation variables are available at policy level. The quantitative variables are available only at HRG/ModelPoint/Cluster level, which has a different granularity than the data request.
To explain how to proceed in this case, the previous illustrative example is modified by complicating two aspects:
Nevertheless, provided that the undertaking knows which policies belong to which HRG/ModelPoint/Cluster and that a relevant weight driver is available at policy level, the templates can still be filled in with limited approximations.
In this situation, the database of the undertaking would comprise the segmentation variables and no quantitative variables, as illustrated below:
db.pol = db.pol %>% select(1:4)
# the db.pol of the situation A has been kept, without the quantitative variablesThe quantitative variables are available only at HRG/ModelPoint/Cluster level, with a different granularity. This “cluster-level” database would have the following form:
# Different granularity for the quantitative variables
UT_age_bucket_lapse = c("[0-20)", "[21-35)", "[36-45)", "45+")
UT_distribution_channel = c("direct", "non_direct")
db.cluster = expand.grid("UT_age_bucket_lapse" = UT_age_bucket_lapse,
"UT_distribution_channel" = UT_distribution_channel)%>%
mutate("lambda_BE" = round(runif(n(),0,0.1),3),
"TP_without_RM"= round(runif(n(), 10,50),1),
"UT_cluster" = paste0("cluster_", 1:n())) %>%
relocate(UT_cluster, 1)The next step is to map the policy level database to the cluster level granularity and to the LURCS template granularity.
This is performed to select the clusters that contain the relevant policies identified by each segment of the LURCS template. As shown in the table below, under situation B there may be no 1-to-1 correspondence between the undertaking specific cluster and the LURCS segment.
mapped.db = db.pol %>%
mutate(
UT_age_bucket_lapse = cut(age, breaks=c(0,20,35,45,100),
labels = UT_age_bucket_lapse),
UT_distribution_channel = case_when(distribution_channel == "direct" ~"direct",
TRUE ~ "non_direct"),
LURCS_age_bucket_lapse = cut(age, breaks = c(0,39,69,100),
labels = LURCS_age_bucket_lapse),
LURCS_distribution_channel = distribution_channel #LURCS and the policy DB have the same granularity for distribution_channel.
) %>%
### map to the cluster-level DB
left_join(db.cluster,by = c("UT_age_bucket_lapse", "UT_distribution_channel")) %>%
## Calculate the sum of the relevant_weight_driver by cluster, this may be already available for a real undertaking
group_by(UT_cluster) %>%
mutate(tot_relevant_driver_in_cluster = sum(relevant_weight_driver)) %>%
ungroup() %>%
#### map to the LURCS template
left_join(simplified.LURCS, by=c("LURCS_age_bucket_lapse", "LURCS_distribution_channel")) %>%
relocate(c(UT_cluster, LURCS_segment),.before=2) The quantitative variables can be calculated using the relevant weight driver to aggregate the lambda_BE and to allocate the TP_without_RM of the HRG/ModelPoint/Cluster to the identified policies.
For example, lambda_BE for LURCS_segment_1 = {“LURCS_distribution_channel” = “direct”, “LURCS_age_bucket_lapse” = “[0-39)”}, the lambda_BE is the weighted average of the lambda_BEs of the two UT clusters, Cluster_1 = {“UT_distribution_channel” = “direct”, “UT_age_bucket_lapse” = “[0-20)”} and Cluster_2 ={“UT_distribution_channel” = “direct”, “UT_age_bucket_lapse” = “[21-35)”}, which contain policies_id_5 {distribution_channel = “direct”, age = 27} and policies_id_10 = {distribution_channel = “direct”, age = 20}. The weights are given by the relevant_weight_driver of the two policies.
For what concerns the monetary amount quantitative variable, since the clusters may contain other policies belonging to other LURCS_segments or in any case not identified by the LURCS_segment being considered, the sum of the quantitative variables should be weighted for the percentage of the relevant_weight_driver of each cluster that is related to the policies identified by each segment.
For example, the TP_without_RM of LURCS_segment_7 = {“LURCS_distribution_channel” = “other”, “LURCS_age_bucket_lapse” = “[0-39)”} is 5.9, being equal to the TP_without_RM of Cluster_6 (38.6), multiplied by the percentage of its relevant_weight_driver that is related to the policies that belong the LURCS_segment_7, namely policy_id_1 (10/130) and policy_id_9 (10/130).
calc.db = mapped.db %>%
group_by(LURCS_segment,LURCS_age_bucket_lapse,LURCS_distribution_channel) %>%
summarise(
lambda_BE = round(weighted.mean(x = lambda_BE, w = relevant_weight_driver), 3),
TP_without_RM = round(sum(TP_without_RM * relevant_weight_driver/tot_relevant_driver_in_cluster), 1))Finally, the simplified LURCS template would appear in the following way, where the empty spots in the quantitative variables correspond to LURCS segments that do not identify any policies of the undertaking
final = calc.db %>%
right_join(simplified.LURCS, by=c("LURCS_age_bucket_lapse","LURCS_distribution_channel","LURCS_segment")) %>%
mutate(across(everything(), ~ replace(.x, is.na(.x), ""))) In the database available to the undertaking, neither the quantitative variables nor some of the segmentation variables are available in a granularity that is equal or greater than the LURCS template. For example, only the following information is available to the undertaking, without the possibility to map individual policies to the clusters nor to the LURCS_segments.
In this case, given the unavailability of the segmentation variables in the necessary granularity, the undertaking could use the “all” value introduced in the LURCS templates or a materiality based approximation can be performed (and reported in template G1) in case for example an undertaking cluster has a granularity that is very close but not exactly identical to the granularity required by the LURCS segment
The following example outlines how the value “all” can be used when the undertaking’s granularity matches (or exceeds) the LURCS’ granularity only for a subset of the segmentation variables.
Let’s consider the following simplified LURCS_template:
simplified.LURCS = expand.grid("LURCS_age_bucket_lapse" = c("all", LURCS_age_bucket_lapse),
"LURCS_distribution_channel" = c("all", LURCS_distribution_channel)) %>%
mutate(LURCS_segment = 1:n()) %>%
relocate(LURCS_segment, 1)In this example the undertaking can achieve the same level of granularity of the LURCS template for the LURCS_distribution_channel segmentation variable, but not for the segmentation variable LURCS_age_bucket_lapse.
The same process as in situation A can be performed considering clusters identified by the value “all” for the segmentation variable of which the granularity is not achievable by the undertaking, namely LURCS_age_bucket_variable.
db.cluster.mod = db.cluster %>%
bind_rows(group_by(., UT_age_bucket_lapse) %>%
summarise(lambda_BE = round(weighted.mean(x=lambda_BE, w = tot_relevant_driver_in_cluster),3),
TP_without_RM = round(sum(TP_without_RM),1),
tot_relevant_driver_in_cluster = sum(tot_relevant_driver_in_cluster)) %>%
mutate(UT_distribution_channel="all",
UT_cluster = paste0("cluster_" , 9:12) )
) %>% bind_rows(
group_by(., UT_distribution_channel) %>%
summarise(lambda_BE = round(weighted.mean(x=lambda_BE, w = tot_relevant_driver_in_cluster),3),
TP_without_RM = round(sum(TP_without_RM),1),
tot_relevant_driver_in_cluster = sum(tot_relevant_driver_in_cluster)) %>%
mutate(UT_age_bucket_lapse="all",
UT_cluster = paste0("cluster_" , 13:15) )
)mapped.db = db.cluster.mod %>%
mutate(LURCS_distribution_channel = UT_distribution_channel,
# the two granularities match
LURCS_age_bucket_lapse = "all") %>%
left_join(simplified.LURCS) %>%
relocate(LURCS_segment, 1)And the same process as in situation A can be performed to calculate the quantitative variables, using the value “all” for the segmentation variables
calc.db = mapped.db %>% filter(UT_distribution_channel =="all" | UT_age_bucket_lapse =="all") %>%
group_by(LURCS_segment, LURCS_distribution_channel, LURCS_age_bucket_lapse) %>%
filter( UT_distribution_channel !="all" | UT_age_bucket_lapse !="all" ) %>%
summarise(lambda_BE = round(weighted.mean(x = lambda_BE, w = tot_relevant_driver_in_cluster),3),
TP_without_RM = sum(TP_without_RM))The final template appears as follows, where “” signals that the LURCS segment does not select any policies/clusters within the undertaking’s portfolio, while “NP” signals that it was “Not Possible” for the undertaking to provide the quantitative variable, although the LURCS segment identifies some policies/clusters within the undertaking’s portfolio.
final = simplified.LURCS %>%
left_join(calc.db) %>%
ungroup() %>%
mutate(across(c(lambda_BE, TP_without_RM), as.character),
across(c(lambda_BE, TP_without_RM),
~ case_when(
is.na(.x) & (
LURCS_age_bucket_lapse %in% mapped.db$LURCS_age_bucket_lapse |
LURCS_distribution_channel %in% mapped.db$LURCS_distribution_channel
) ~ "NP",
T ~ (.x)
)),
across(c(lambda_BE, TP_without_RM),
~ if_else((is.na(.x)|.x=="NP") & ( (LURCS_age_bucket_lapse %in% mapped.db$LURCS_age_bucket_lapse) &
!(LURCS_distribution_channel %in% mapped.db$LURCS_distribution_channel))
, "" , .x)))