Find simple frequency pattern in dates

Question

I have a list of customer payment dates and I'm looking to see if there is a 7/14 day or monthly pattern to the payments, often there is!. The problem is that there can also be intermediate payments of similar value, so just looking at the time between payments doesn't always work. Is there any simple approach (using SQL or R) that can help me classify customers as weekly or monthly payers?

Seems like a very simple signal processing problem but perhaps I don't know the right words to google as I can't find anything. Any pointing me in the right direction would be appreciated!

Example data:

    CustomerID  Payment Date
    Customer1   2017-01-05
    Customer1   2017-01-06
    Customer1   2017-01-12
    Customer1   2017-01-17
    Customer1   2017-01-19
    Customer1   2017-01-19
    Customer1   2017-01-26
    Customer1   2017-02-02
    Customer1   2017-02-03
    Customer2   2017-06-04
    Customer2   2017-06-06
    Customer2   2017-07-04
    Customer2   2017-07-06
    Customer2   2017-07-22
    Customer2   2017-07-28
    Customer2   2017-08-06

Example Output

    CustomerID   Classification   
    Customer1    Weekly
    Customer2    Monthly

Edit: Just to be clear, the data is generally much larger and can be noisier than above. I was just looking for general ideas for algorithms that find patterns, not to try solve the problem for the small dataset I've posted.

user_3052148 · Answer

payment_date <-
  as.Date(
    c(
      "2017-01-05",
      "2017-01-06",
      "2017-01-12",
      "2017-01-17",
      "2017-01-19",
      "2017-01-19",
      "2017-01-26",
      "2017-02-02",
      "2017-02-03",
      "2017-06-04",
      "2017-06-06",
      "2017-07-04",
      "2017-07-06",
      "2017-07-22",
      "2017-07-28",
      "2017-08-06"
    )
  )

df <- data.frame(payment_date,
                 customer_id = 0)

df$customer_id[1:9] <- 1
df$customer_id[10:16] <- 2

customer_information <- data.frame(customer_id = numeric(),
                                   payment = character())

for (i in 1:length(unique(df$customer_id))) {
  delta_t <-
    abs(as.numeric(df$payment_date[(df$customer_id == i) &
                                     (!duplicated(df$customer_id))] - df$payment_date[(df$customer_id == i) &
                                                                                        (!duplicated(df$customer_id, fromLast = TRUE))]))
  nr_of_payments <- NROW(df[df$customer_id == i,])
  days_to_pay <- delta_t / nr_of_payments

  if (days_to_pay > 7) {
    to_add <- data.frame(customer_id = i,
                         payment = "monthly")
    customer_information <- rbind(customer_information, to_add)
  } else{
    to_add <- data.frame(customer_id = i,
                         payment = "weekly")
    customer_information <- rbind(customer_information, to_add)
  }
}

The code is using the average time it takes a customer to make a payment. If the average time exceeds 7 he's a monthly payer, else he's a weekly payer.

It works, but I guess it's not a satisfying solution. It seems like there are two payments per month/week. If that's the case that is something you could consider to get more accurate results.

Find simple frequency pattern in dates

1 Answers