CODEWITHSUNDEEP
mixpaneljql
Michael
Michael

Reputation: 22967

Find users with more than one distinct ids

I have an issue with Mixpanel merge that causes random users to be merged together. I am trying to debug this issue and for that I need a JQL query that shows me which users have more than 1 (or X) distinct IDs. They can be anonymous IDs (unidentfied users) or my app's IDs.

Mixpanel shows these distinct ids in the user dashboard:

Mixpanel user distinct id

Is there any way to write a JQL query that will return the users that have more than X distinct IDs ?

I was trying this:

function main() {
  return Events({
    from_date: "2024-08-01",
    to_date: "2024-08-08"
  })
  .groupBy(["distinct_id"], mixpanel.reducer.count())
  .filter(result => result.value > 1)
}

But I am not sure this is correct.

Upvotes: 2

Views: 123

Answers (1)

Amin
Amin

Reputation: 57

If i understand you correctly, to truly identify if there are multiple 'distinct_id's associated with a single individual, you need to anchor on a static property which is unique to each individual, such as email address, hashed email or ...

function main() {
  return Events({
    from_date: "2024-08-01",
    to_date: "2024-08-25"
  })
  .groupBy(['distinct_id', 'properties.Email'], function() {})
  .reduce(function(acu, events) {
    var results = {};

    // Collect distinct_ids for each email
    events.forEach(function(event) {
      var distinctId = event.key[0];
      var email = event.key[1];

      if (!results[email]) {
        results[email] = new Set();
      }
      results[email].add(distinctId);
    });

    return Object.entries(results).map(function([email, distinctIds]) {
      return {
        'User Email': email,
        'Distinct IDs': Array.from(distinctIds).join(" - ")
      };
    });
  });
}

Also i should add this that for anonymous or undefined users you mentioned, you can use ‘properties.$device_id’ as the anchor.

But this doesn’t correctly define undefined individuals, i don’t know any other built-in property that could serve this purpose.

Upvotes: 1

Related Questions