'How to count by unique values a user ID has been exposed to by date?

Hi I have a table of sessions and registered table. What i want to do is, first drop all rows in sessions table if sessionstable[dateKey] > registrationDate[dateKey]. Next is make a list of unique campaign and their combinations. Count number of user_id / total count that matches the combinations. The main idea here is to get an idea of the touchpoints and their contribution towards registration. All 5 accounts were touched by one or more of the channels throughout their buyer journeys. In other words, the channels worked together by forming coalitions to increase the likelihood of opportunity conversion

sessions table

dateKey user_id campaign
2022-04-01 20 facebook
2022-04-02 20 google
2022-04-03 20 facebook
2022-04-03 20 google
2022-04-04 20 tiktok
2022-04-01 19 google
2022-04-02 18 google
2022-04-02 17 google
2022-04-03 17 facebook
2022-04-02 16 google
2022-04-03 16 facebook

Registration date

dateKey user_id
2022-04-03 20
2022-04-03 19
2022-04-03 18
2022-04-02 17
2022-04-03 16

output is

coalition ratio
facebook 0
google 0.6
facebook+google 0.2
google+facebook 0.2

so here's what i've got

#join tables
df = sessionstable.merge(registrationDate, on=user_id, how="left")
#return rows that is not bigger than datekey from registrationdate (datekey2)
df= df[df[dateKey]<df[datekey2]]

def subsets(S):
    '''Returns all possible subsets of the given set'''
    s = []
    for i in range(1, len(S)+1):
        s.extend(map(list, combinations(S, i)))
    return list(map('+'.join, s))

N = sorted({'Facebook', 'Google', 'LinkedIn'})
    
coalitions = subsets(N)
coalitions_lbl = ['S{}'.format(i) for i in range(1, len(coalitions)+1)]

how do I do a count on the coalition of items? And also how do I sort by datekey per user_id so that it matches?

Where ratio is registered users/ population(len(user_id.unique()). The coalition 'Facebook+Google' resulted in 1 conversions from 5 opportunities,so has a conversion ratio of 1/5 = 0.2 (20%)



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source