As a complete beginner, I know this might be an ambitious use case to follow, but it is nonetheless the one we need to get to :-).
We would like to measure and compare retention rates based on referral codes. I have found some examples online on how to make cohort analyses. Still, I’m unsure how to model our data, as our business is not a traditional subscription or eCommerce business.
The best way to illustrate our situation is by comparing us to an auction site.
Our “fact” table data from our production system is structured by transactions where each line item looks like this.
orderID | sellerUserID | buyerUserID | amount | date |
1 | 10 | 11 | 20 | 2022-12-24 |
We have another table with user info like this.
userID | creationDate | referralCode |
10 | 2022-12-01 | ref01 |
From looking at examples online, I gather it would be better for us to have a fact table that is a little bit different. Something like:
transactionID | transactionType | orderID | userID | amount | date |
1 | sell | 1 | 10 | 20 | 2022-12-24 |
2 | buy | 1 | 11 | 20 | 2022-12-24 |
The next thing I would start working on is learning how to convert our existing fact table in Power BI to the one proposed.
Am I on the right track, or should I think differently about this?
Solved! Go to Solution.
IMO you should no to this direction, but the answer will be like this.
From this:
To this:
Power Query M:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABEglpEBmDAy0jU00jUyUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"orderID " = _t, #"sellerUserID " = _t, #"buyerUserID " = _t, #"amount " = _t, date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"orderID ", Int64.Type}, {"sellerUserID ", Int64.Type}, {"buyerUserID ", Int64.Type}, {"amount ", Int64.Type}, {"date", type date}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"orderID ", "amount ", "date"}, "transactionType", "UserID"),
#"Detect Sell" = Table.ReplaceValue(#"Unpivoted Columns","sellerUserID","sell",Replacer.ReplaceText,{"transactionType"}),
#"Detect Buy" = Table.ReplaceValue(#"Detect Sell","buyerUserID","buy",Replacer.ReplaceText,{"transactionType"})
in
#"Detect Buy"
If you will go with this scenario you will end up with 2n rows at the end, which may be a problem in the future due to fact that you can expect a lot of transactions, right? 🙂
Split information from your oryginal data info fact_transaction and fact_user_to_transaction tables.
fact_transaction
let
#"Removed Other Columns" = Table.SelectColumns(Sample,{"orderID ", "date", "amount "})
in
#"Removed Other Columns"
fact_user_to_transaction
let
#"Removed Other Columns" = Table.SelectColumns(Sample,{"orderID ", "sellerUserID ", "buyerUserID "}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"orderID "}, "transactionType", "userID"),
#"Detect sell transactions" = Table.ReplaceValue(#"Unpivoted Columns","sellerUserID","sell",Replacer.ReplaceText,{"transactionType"}),
#"Detect buy transactions" = Table.ReplaceValue(#"Detect sell transactions","buyerUserID","buy",Replacer.ReplaceText,{"transactionType"})
in
#"Detect buy transactions"
dim_user
Note that I've added a customer with ID 11.
PBIX file: https://we.tl/t-t98gEAuuDF
transactionID | transactionType | orderID | userID | amount | date |
1 | sell | 1 | 10 | 20 | 2022-12-24 |
2 | buy | 11 | 11 | 20 | 2022-12-24 |
Can you explain why row 2 has transactionID with number 2 and why the orderID is 11? User ID is clear for me, but I dont understand this 2 fields?
What I mean here is that you are thying to get unpivoted information about transaction so it orderID suppose to be 1 not 11, right? But still - what is transactionID?
Sorry. You are right. This is a mistake. It should of course be orderID = 1.
I will correct it in the original post.
Thanks for pointing it out.
And for the transactionID? What it is comming from? 🙂 I can help you with transformation but this is the missing part.
Thanks for your interest. Actually the transactionID is not coming form anywhere as it is not something we have today. The example with the transactionID, is the end result I'm thinking we should end up with.
The two first examples is how our data looks today. The third example, you are are refering to, is what I "think" should be the end result :-).
Hope it makes sense.
IMO you should no to this direction, but the answer will be like this.
From this:
To this:
Power Query M:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABEglpEBmDAy0jU00jUyUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"orderID " = _t, #"sellerUserID " = _t, #"buyerUserID " = _t, #"amount " = _t, date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"orderID ", Int64.Type}, {"sellerUserID ", Int64.Type}, {"buyerUserID ", Int64.Type}, {"amount ", Int64.Type}, {"date", type date}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"orderID ", "amount ", "date"}, "transactionType", "UserID"),
#"Detect Sell" = Table.ReplaceValue(#"Unpivoted Columns","sellerUserID","sell",Replacer.ReplaceText,{"transactionType"}),
#"Detect Buy" = Table.ReplaceValue(#"Detect Sell","buyerUserID","buy",Replacer.ReplaceText,{"transactionType"})
in
#"Detect Buy"
If you will go with this scenario you will end up with 2n rows at the end, which may be a problem in the future due to fact that you can expect a lot of transactions, right? 🙂
Split information from your oryginal data info fact_transaction and fact_user_to_transaction tables.
fact_transaction
let
#"Removed Other Columns" = Table.SelectColumns(Sample,{"orderID ", "date", "amount "})
in
#"Removed Other Columns"
fact_user_to_transaction
let
#"Removed Other Columns" = Table.SelectColumns(Sample,{"orderID ", "sellerUserID ", "buyerUserID "}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"orderID "}, "transactionType", "userID"),
#"Detect sell transactions" = Table.ReplaceValue(#"Unpivoted Columns","sellerUserID","sell",Replacer.ReplaceText,{"transactionType"}),
#"Detect buy transactions" = Table.ReplaceValue(#"Detect sell transactions","buyerUserID","buy",Replacer.ReplaceText,{"transactionType"})
in
#"Detect buy transactions"
dim_user
Note that I've added a customer with ID 11.
PBIX file: https://we.tl/t-t98gEAuuDF
Woow thanks a lot. This is much more than I expected. A solution and advice to do improve. Thanks a lot. I will study your examples with great care.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
119 | |
76 | |
65 | |
53 | |
51 |
User | Count |
---|---|
183 | |
101 | |
80 | |
79 | |
77 |