how do I Identify Duplicates in last 90 days and publish first touch attribution fields

[vanik85]

I have a table with below fileds

Month	Mobile	UTM
10-Apr	11111	Organic
12-Apr	11111	Paid
15-Apr	22222	Paid
20-Apr	33333	Paid
02-May	55555	Paid
12-May	33333	Organic
12-May	22222	Organic
12-Jun	11111	Web
12-Jun	44444	Paid
28-Jul	22222	Paid

I wish to check duplicates in mobile numbers by checking past 90 days and accordinlgy revert with Unique or Repeat user. Additonally need to mention first touch points

Month	Mobile	UTM	Unique/Repeat	First Touch Month	First Touch UTM
10-Apr	11111	Organic	Repeat	Apr	Organic
12-Apr	11111	Paid	Repeat	Apr	Organic
15-Apr	22222	Paid	Repeat	Apr	Paid
20-Apr	33333	Paid	Repeat	Apr	Paid
02-May	55555	Paid	Unique	May	Paid
12-May	33333	Organic	Repeat	Apr	Paid
12-May	22222	Organic	Repeat	Apr	Paid
12-Jun	11111	Web	Repeat	Apr	Organic
12-Jun	44444	Paid	Unique	June	Paid
28-Jul	22222	Paid	Unique	July	Paid

Can above be done using custom table using certain commands ?

[Ashish_Mathur]

Hi,

On what basis have you got the Repeat and unique in the column?  Why should 11111 pn April 12 be repeat? - there is no data for this ID in the past 90 days.

Also, do you want a measure or a calculated column formula solution?

[vanik85]

Why should 11111 pn April 12 be repeat? - there is no data for this ID in the past 90 days.

there is a data entry for mobile 1111 on 10th Apr hence..

[Ashish_Mathur]

Sorry but i meant to ask why on April 10 should the word repeat appear for 11111.  For this ID, there is no data prior to April 10.

[vanik85]

It's fine incase no prior value it will be unique

[Ashish_Mathur · Accepted solution]

Hi,

These calculated column formulas work

Unique/Repeat = if(Data[Month]-CALCULATE(MAX(Data[Month]),FILTER(Data,Data[Mobile]=EARLIER(Data[Mobile])&&Data[Month]<EARLIER(Data[Month])))<=90,"Repeat","Unique")

First touch month = FORMAT(CALCULATE(min(Data[Month]),FILTER(Data,Data[Mobile]=EARLIER(Data[Mobile])&&Data[Month]<=EARLIER(Data[Month]))),"mmmm")

First touch UTM = LOOKUPVALUE(Data[UTM],Data[Month],CALCULATE(min(Data[Month]),FILTER(Data,Data[Mobile]=EARLIER(Data[Mobile])&&Data[Month]<=EARLIER(Data[Month]))),Data[Mobile],Data[Mobile])

Hope this helps.

[vanik85]

Hi 

getting error a table of multiple values was supplied where a single value was expected on first touch UTM query

[Ashish_Mathur]

It means that for one or more mobile numbers, there are multiple min dates.

[vanik85]

That would a scenario when a Mobile number was punched within hours on same day correct ?

do o need to alter the formulae then ?

[Ashish_Mathur]

First and foremost, post a representative sample and clearly show such nuances.  On that representative sample, show the expected result.

[vanik85]

Thanks a lot !

[Ashish_Mathur]

You are welcome.