'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 | |
| 2022-04-02 | 20 | |
| 2022-04-03 | 20 | |
| 2022-04-03 | 20 | |
| 2022-04-04 | 20 | tiktok |
| 2022-04-01 | 19 | |
| 2022-04-02 | 18 | |
| 2022-04-02 | 17 | |
| 2022-04-03 | 17 | |
| 2022-04-02 | 16 | |
| 2022-04-03 | 16 |
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 |
|---|---|
| 0 | |
| 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 |
|---|
