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 = mean(rvxclient, na.rm = TRUE),
    Median_Revenue = median(rvxclient, na.rm = TRUE),
    Std_Dev = sd(rvxclient, na.rm = TRUE),
    Freq = n(),
    .groups = "drop"
  )

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

Practice Revenue Per Client by Expertise

print(knitr::kable(summary_table, digits = 0, format = "markdown"))
Experience Tier Mean_Revenue Median_Revenue Std_Dev Freq
No Major Designations 7245 4319 9521 82
No CFP But Other Majors 16084 4240 54355 60
CFP, No Post-CFP 7959 5891 8804 259
CFP, Post-CFP 9591 6034 19824 188 
### Graph for Median Revenue per Client 
ggplot(summary_table, aes(x = `Experience Tier`, y = Median_Revenue, fill = `Experience Tier`)) +
  geom_col(width = 0.5, color = "black", show.legend = FALSE) +
  geom_text(aes(label = scales::dollar(Median_Revenue, 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.