How to sum email opt-in per customer per month

Question

For my master thesis I try to sum the total opt-ins a customer has for all possible newsletters over-time (monthly). I fail in doing so.

Dput() data to Replicate the set:

structure(list(GroupID = c(404712L, 404722L, 404722L, 404722L, 
404722L, 404731L, 404731L, 404731L, 404731L, 404776L, 404776L, 
404776L, 404776L, 404845L, 404845L), MailingListName = c("Ticketing", 
"Merchandise", "Nieuwsbrief", "Partners", "Ticketing", "Merchandise", 
"Nieuwsbrief", "Partners", "Ticketing", "Merchandise", "Nieuwsbrief", 
"Partners", "Ticketing", "Merchandise", "Nieuwsbrief"), OptIn = c("1", 
"0", "0", "0", "0", "1", "1", "0", "1", "1", "1", "0", "1", "1", 
"1"), modifieddate = structure(c(17454, 17957, 17957, 17957, 
17957, 17455, 17455, 17455, 17455, 17901, 17901, 17901, 17901, 
18665, 18665), class = "Date"), modifieddate_Yr_Month = c("2017-10", 
"2019-03", "2019-03", "2019-03", "2019-03", "2017-10", "2017-10", 
"2017-10", "2017-10", "2019-01", "2019-01", "2019-01", "2019-01", 
"2021-02", "2021-02")), row.names = c(NA, -15L), class = c("data.table", 
"data.frame"))

I tried dplyr:

   library(dplyr)
    dfnewsletters_total <- dfnewsletters %>%
      group_by(OptIn, GroupID, modifieddate_Yr_Month) %>%
      mutate(OptIn_Total = n()) %>%
      arrange(desc(OptIn_Total))

Without any good results.

Anyone has an idea how to fix this?

Answer 1

Nieuwsbrief = Newsletter?

library(dplyr)
dfnewsletters %>%
  filter(MailingListName == "Nieuwsbrief") %>% 
  group_by(GroupID, modifieddate_Yr_Month) %>%
  summarise(OptIn_Total = sum(as.numeric(OptIn))) %>% 
  arrange(desc(OptIn_Total))

Output:

GroupID modifieddate_Yr_Month OptIn_Total
    <int> <chr>                       <dbl>
1  404731 2017-10                         1
2  404776 2019-01                         1
3  404845 2021-02                         1
4  404722 2019-03                         0