0

I am attempting to calculate the number of members(customers) who have met threshold criteria to be bumped up to the next tier of membership and the date on which they met the criteria.

The criteria are:

  • Visit 5 or more locations their membership
  • Spend at least $1500

Using my code below, I'm able to create a new dataframe containing members who meet the above criteria, however, I'm stuck when it comes to recording the date on which they were met.

The code I am using is:

criteria_met <- member_bookings %>% 
      filter(visit_date >= member_join_date, visit_date <= expiry_date) %>% 
      group_by(member_number) %>% 
      summarise(sum_nett = sum(new_total_nett),
                locs_visited = n_distinct(location_id),
                crit_met = as.logical(if_else(sum_nett >= 1500 & parks_visited >= 5, 1, 0))) %>% 
      filter(crit_met == TRUE)

I attempted to use mutate(date_met = max(visit_date)) however, as I should have known, this only returned their most recent visit and not the visit date on which the criteria were met.

J.Gayler
  • 1
  • You need to `mutate` to add columns with cumulative sums (see `?cumsum`) for both `new_total_nett` and the number of distinct locations. Then you want the first row where both criteria are met. If you [make your example reproducible](https://stackoverflow.com/q/5963269/903061) we can help you, but without shared data it is difficult. – Gregor Thomas Jul 17 '17 at 01:32
  • Agree with @Gregor, need data. I would suggest that you do this as a two step analysis: (1) filter out the eligible members - as you have done and then, (2) extract all the sales data for the filtered members and then cumulatively sum the revenue for each member and identify when the $500 threshold was reached. Your helpers for this analysis are `goup_by` and `nest`. Look-up https://stackoverflow.com/questions/44363535/how-to-create-a-list-of-list-and-then-perform-a-vectorised-function-over-it on how 'nest' is used – cephalopod Jul 17 '17 at 02:58

0 Answers0