Stitching user IDs for click tracking

user_17129771 0
Viewed 62

My company uses a tag management platform that attempts to perform user stitching in its click tracking tables. I've found that it's not even close to being watertight, and I'm exploring ways to perform that stitching on my own. What I have to work with is a series of IDs (why so many? Beats me) that pair to each other somewhat haphazardly. For one user, the data would look something like this:

ID_1 ID_2 ID_3
A C
A D
A E
A F
B C
B D
B E
B F

My goal is to create a new column that has stores one constant ID. I don't care which one, A/B/C/D/E/F, as long as it's constant and exhaustive across all records for a given user. Something like:

ID_1 ID_2 ID_3 ID_final
A C A
A D A
A E A
A F A
B C A
B D A
B E A
B F A

I would love a SQL-based way of doing this, but I'd be open to an R- or Python-based solution as well. For R/Python, we'd probably spin up a dockerized job that performs the stitching and writes a lookup table to our warehouse. Thank you!!

A few edits based on initial feedback:

  1. Added second table with visualization of the end result I'm trying to achieve.
  2. FYI, Google Tag Manager is not the actual tag management platform. It doesn't actually matter what the actual tag manager is, this is more of a data wrangling task than a tag management task. I only mentioned tag management to provide context for where this question came up.
data-cleaning r sql python
1 Answers

I checked back in on this out of curiosity. This is by no means an answer, just an alternative way to pose the question. The way I'm understanding it, it's a graph problem. So if you phrase it like that you might get some looks from people with more chops with that.

dataset = [
    {'A': ['B','C']},
    {'A': ['D','E']},
    {'E': ['F','G']},
    {'S': ['T','U']},
    {'U': ['V','X']}]

Desired output - grouping of graphs by all sequences of shared nodes.

grouped_by_commonality = [
    {'common_id_1_grouping': ['A','B','C','D','E','F','G']},
    {'common_id_2_grouping': ['S','T','U','V','X']}]

Then you'd assign your common id based on membership of id_1,id_2,id_3 in your dataset.

user_56620050
Related