Random sample of N unique customer IDs while ensuring the more one transacts the higher chances of being selected

user_9653491 0
Viewed 155

I have a table called "transaction_history", containing millions of transactions with the following columns: column 1: customer_id column 2: transaction date

In this table one customer may have x amount of transactions, where X >= 1

What I am looking to do is get a random sample of n (n is the number of prizes to allocate to n winners)unique customer IDs BUT ensure that the more frequent the transactions for a given customer the higher their chances of being selected a winner.

I have tried the following: 1- the straight forward dplyr::sample_n(transaction_history, size = ...) which leads to sample with duplicate customer_ids

2- Transactions %>% dplyr::distinct(customer_id) %>% dplyr::sample_n(transaction_history, size = ...) which does not give frequent customers a higher chance

3- Sampling from per customer_id groups before sampling again which also defeats this goal.

Any help will be greatly appreciated.

Thanks

dplyr r random
2 Answers

How about this:

# create some random toy data to use for example:
testdata <- 
  tibble( person_id = sample(1:5, size=20, replace=TRUE) ) %>%
  mutate( transaction_id = row_number() ) %>% 
  arrange( person_id )

The toy data looks like this:

   person_id transaction_id
 1         1              9
 2         1             11
 3         2              4
 4         2              5
 5         2              6
 6         2             10
 7         2             19
 8         3              7
 9         3             17
10         3             18
11         3             20
12         4              1
13         4              2
14         4              3
15         4              8
16         4             12
17         4             13
18         4             14
19         4             16
20         5             15

Now, count number of transactions per person, and use this transaction count as the weight in the sample_n() function:

testdata %>% 
  # count number of transactions per person:
  group_by(person_id) %>% 
  summarise( n_transactions = n() ) %>%
  ungroup() %>% 
  # select a random 2 people, where chance of being selected is based on number of transactions:
  sample_n( size = 2,
            weight = n_transactions
          )

If you run the above code-block multiple times with the same toy data, you will see that people with more transactions get selected more often.

The actual probability of selection used by the sample_n() function is calculated like this: (read about it in the help documentation of the sample_n() function)

testdata %>% 
  # count number of transactions per person:
  group_by(person_id) %>% 
  summarise( n_transactions = n() ) %>%
  ungroup() %>% 
  # calculate selection probability:
  mutate( probability_of_being_selected = n_transactions/sum(n_transactions) )

  person_id n_transactions probability_of_being_selected
1         1              2                          0.1 
2         2              5                          0.25
3         3              4                          0.2 
4         4              8                          0.4 
5         5              1                          0.05
user_138429440 
library(dplyr)

db <- read.csv("XXXXX.csv", as.is = TRUE) #data
N <- nrow(db) #population
n <- ceiling(nrow(db)*0.1) #ceiling((1.96^2*0.5*0.5*nrow(db))/(0.02^2*(nrow(db)-1)+1.96^2*0.5*0.5)) #sample size exemple

# simple sample:
simple <- sample_n(db, n) 

# proportional stratified sample
proportional <- db %>% group_by(customer_id) %>% sample_frac(n/N)
user_138101770
Related