Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jannnn04
Frequent Visitor

Matching Users across Platforms

Hi Community and PowerBi Masters,

 

I am currently working on an analysis for which I try to understand the consumer behavior in detail. 

The corresponding data that I have is (name of table: All_Platforms):

 

-Order Number

-Order Time

-User Name

-BGR_ALL.Product Name

-BGR_ALL.Category

-Phone Number

-Platform

 

The ones marked in bold are the ones, which seem to matter for this case.

 

Jannnn04_0-1632882382616.png

 

 

My goal: understand which consumer has changed between platforms. 

An example: Username Oscaaarrr with Phone number 1925526 has purchased something on Facebook on January first and on Amazon ( 9th February, different user name: YO_Oscar) and a month later on Google (9th March, different user name: Oscar32). 

 

Now I want to display that visually in a sankey chart for which I need two variables: Source and Destination. This would allow me to show the flow of users between the platforms.

 

Jannnn04_1-1632882383154.png

 

Jannnn04_2-1632882382954.png

 

 

My question: how do I create new columns that match the phone numbers across platforms and state their platform used for the current transaction and the platform used of the previous transaction?

 

I hope that is clear. Let me know if you need any more informatoin to solve my issue!

Highly appreciate your help.

 

All the best and many thanks!!

10 REPLIES 10
lbendlin
Super User
Super User

how are you planning to identify the users if you don't have anything but your eyeballs to match them?

Hello, 

 

sorry for being unclear: I wanted to match them with their phone numbers. 

Assuming that they kept their numbers constant across platforms.

 

Thanks

Jannnn04

that's a bold assumption 🙂

Please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good) .

Hello @lbendlin ,

 

was the data that I provided in the table form of any help?

I would really appreciate help here, dont manage to find a starting point.

 

Thanks a lot!

Do you want to record only true platform switches, or do you also want to see the events where they purchased from the same platform multiple times in a row (like Emil and Instagram)?

 

See attached for a sample implementation

Thanks a lot! 

Seeing the events where thez purchased from the same platform multiple times in a row would also be nice! Thanks for asking. 

I unfortunately can not open the report because I can not download the latest version of PBI currently. Would you be able to share it in another format? That would be great!

Thanks a lot!

Here's the result:

lbendlin_0-1634558426416.png

and here is the DAX :

 

Came from = 
var i = 'Table'[Index]
var p = CALCULATE(max('Table'[Platform]),ALLEXCEPT('Table','Table'[Phone Number]),'Table'[Index]=i-1)
return if('Table'[Platform]=p,BLANK(),p)

Thank you! 

That looks very promising. When I fit that into my table however, it does not show my anything for the p variable. It just returns blank values.

Jannnn04_0-1634628514127.png

As a measure it does not work because it does not recognize the index column.

What am I doing wrong?

 

Thanks !

 

Here the data

 

Order NumberOrder TimeUser NameBGR_ALL.Product NameBGR_ALL.CategoryValuePlatformPhone Number
1111101.01.2021OscaaarrriphonePhones155Facebook1925526
2222202.01.2021LunalenovoLaptops111Amazon1164070
3333303.01.2021EmilappleTablets69Google1618350
4444404.01.2021Lisa-MariaandroidPhones53Instagram1702213
5555505.01.2021LisamacbookLaptops90Ebay1571808
6666606.01.2021JansamsungTablets102Alibaba1701882
7777707.01.2021markusiphonePhones48Saturn1213889
8888808.01.2021RosalenovoLaptops16Facebook1892353
9999909.01.2021YO_OscarappleTablets168Amazon1925526
11111002.03.2021JoseandroidPhones161Google1164070
12222103.03.2021EmilmacbookLaptops112Instagram1618350
12222103.03.2021EmilmacbookLaptops112Saturn1618350
12222103.03.2021EmilmacbookLaptops112Facebook1618350
12222103.03.2021EmilmacbookLaptops112Amazon1618350
12222103.03.2021EmilmacbookLaptops112Google1618350
12222103.03.2021EmilmacbookLaptops112Instagram1618350
12222103.03.2021EmilmacbookLaptops112Instagram1618350
12222103.03.2021EmilmacbookLaptops112Instagram1618350
12222103.03.2021EmilmacbookLaptops112Instagram1618350
12222103.03.2021EmilmacbookLaptops112Instagram1618350
13333204.03.2021Lisa-MariasamsungTablets192Ebay1702213
14444305.03.2021LisaiphonePhones110Alibaba1571808
15555406.03.2021FeblenovoLaptops175Saturn1492420
16666507.03.2021MarkusappleTablets125Facebook1213889
17777608.03.2021RosaandroidPhones175Amazon1892353
18888709.03.2021Oscar32macbookLaptops100Google1925526

I still did not manage to solve that issue and would be super grateful if someone can assist! 🙂 

Thanks, community!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors