library(haven)
library(tidyverse)
library(stringr)

# Wellbeing Study 2025
file_path <- "C:\\Users\\JH\\Kitces.com\\DV - Research\\Anonymized Data and Codebooks - All Projects\\Wellbeing Studies\\2025\\Wellbeing 2025.dta"
df <- read_dta(file_path)

# Set variables (grpexp3 and rspclexp) to use
df <- df %>%
  mutate(
    experience_tier = as_factor(grpexp3),
    exp_segment = case_when(
      rspclexp < 10 ~ "Below 10 Years",
      rspclexp >= 10 ~ "10+ Years"
    ),
    exp_segment = factor(exp_segment, levels = c("Below 10 Years", "10+ Years"))
  )

### Summary Table
summary_table <- df %>%
  filter(!is.na(experience_tier) & !is.na(rvxclient)) %>%
  group_by(`Experience Tier` = experience_tier) %>%
  summarise(
    Mean_Revenue = scales::dollar(mean(rvxclient, na.rm = TRUE), accuracy = 1),
    Median_Revenue_Num = median(rvxclient, na.rm = TRUE), # Kept as numeric for the plot below
    Mean_Col = scales::dollar(Median_Revenue_Num, accuracy = 1), # Formatted for the table
    Std_Dev = scales::dollar(sd(rvxclient, na.rm = TRUE), accuracy = 1),
    Freq = n(),
    .groups = "drop"
  )

# Rename columns for the final table display
final_summary_table <- summary_table %>% 
  select(
    `Experience Tier`, 
    `Mean` = Mean_Revenue, 
    `Median` = Mean_Col, 
    `Std Dev` = Std_Dev, 
    Freq
  )

# Heading
cat("### Practice Revenue Per Client by Expertise\n\n")

Practice Revenue Per Client by Expertise

print(knitr::kable(final_summary_table, format = "markdown"))
Experience Tier Mean Median Std Dev Freq
No Major Designations $7,245 $4,319 $9,521 82
No CFP But Other Majors $16,084 $4,240 $54,355 60
CFP, No Post-CFP $7,959 $5,891 $8,804 259
CFP, Post-CFP $9,591 $6,034 $19,824 188 
### Graph for Median Revenue per Client
ggplot(summary_table, aes(x = `Experience Tier`, y = Median_Revenue_Num, fill = `Experience Tier`)) +
  geom_col(width = 0.5, color = "black", show.legend = FALSE) +
  geom_text(aes(label = scales::dollar(Median_Revenue_Num, accuracy = 1)), vjust = -0.5, fontface = "bold", size = 4) +
  theme_minimal(base_size = 12) +
  scale_fill_brewer(palette = "Blues") +
  scale_y_continuous(labels = scales::dollar_format(), expand = expansion(mult = c(0, 0.15))) +
  scale_x_discrete(labels = function(x) str_wrap(x, width = 15)) + # Wraps overlapping text
  labs(
    title = "Practice Revenue Per Client by Expertise",
    x = NULL,
    y = "Median Revenue Per Client"
  ) +
  theme(
    plot.title = element_text(face = "bold", size = 14),
    panel.grid.major.x = element_blank(),
    panel.grid.minor = element_blank(),
    axis.text.x = element_text(size = 10, vjust = 0.5)
  )

### define above/below 10 Years using rspclexp
segmented_table <- df %>%
  filter(!is.na(experience_tier) & !is.na(rvxclient) & !is.na(exp_segment)) %>%
  group_by(experience_tier, exp_segment) %>%
  summarise(
    Median_Revenue = median(rvxclient, na.rm = TRUE),
    Freq = n(),
    .groups = "drop"
  )

### Graph for Median Revenue per Client by Expertise and client facing experience
ggplot(segmented_table, aes(x = experience_tier, y = Median_Revenue, fill = exp_segment)) +
  geom_col(position = position_dodge(width = 0.7), width = 0.6, color = "black") +
  geom_text(
    aes(label = scales::dollar(Median_Revenue, accuracy = 1)),
    position = position_dodge(width = 0.7),
    vjust = -0.5,
    fontface = "bold",
    size = 3.5
  ) +
  theme_minimal(base_size = 12) +
  scale_fill_brewer(palette = "Paired") +
  scale_y_continuous(labels = scales::dollar_format(), expand = expansion(mult = c(0, 0.15))) +
  scale_x_discrete(labels = function(x) str_wrap(x, width = 15)) + # Wraps overlapping text
  labs(
    title = "Practice Revenue Per Client by Expertise and Client Facing Experience",
    x = NULL,
    y = "Median Revenue Per Client",
    fill = NULL
  ) +
  theme(
    plot.title = element_text(face = "bold", size = 14),
    panel.grid.major.x = element_blank(),
    panel.grid.minor = element_blank(),
    legend.position = "bottom",
    axis.text.x = element_text(size = 10, vjust = 0.5)
  )

# space
cat("\n\n### Interpretation\n")

Interpretation

cat("There is a clear upward trend in median revenue per client as advisors secure higher designations, moving from $4,319 for those with no major designations to $6,034 for advisors holding a CFP plus post-CFP credentials. Notably, having other major designations without a CFP ($4,240) yields nearly identical median revenue to holding no major designations at al.\n\n")

There is a clear upward trend in median revenue per client as advisors secure higher designations, moving from $4,319 for those with no major designations to $6,034 for advisors holding a CFP plus post-CFP credentials. Notably, having other major designations without a CFP ($4,240) yields nearly identical median revenue to holding no major designations at al.

cat("When segmenting the sample by years of experience, it is clear that advisors with 10 or more years of client-facing experience systematically generate higher revenue per client than their less-experienced counterparts within the same designation bracket. Interestingly, this experience premium is most pronounced among non-CFP advisors - where those with experience outpace newer advisors - but significantly narrows among those who hold a CFP.\n")

When segmenting the sample by years of experience, it is clear that advisors with 10 or more years of client-facing experience systematically generate higher revenue per client than their less-experienced counterparts within the same designation bracket. Interestingly, this experience premium is most pronounced among non-CFP advisors - where those with experience outpace newer advisors - but significantly narrows among those who hold a CFP.

cat("These findings align with pretty much all the other research we've ever done in showing that post-CFP designations don't yield any material benefit beyond that already obtained with the CFP marks.\n")

These findings align with pretty much all the other research we’ve ever done in showing that post-CFP designations don’t yield any material benefit beyond that already obtained with the CFP marks.