How to prepare and model an unusual fact table for cohort analysis?

[twowaysyellow]

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?

[bolfri]

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?

[twowaysyellow]

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.

[bolfri]

And for the transactionID? What it is comming from? 🙂 I can help you with transformation but this is the missing part.

[twowaysyellow]

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.

[bolfri · Accepted solution]

IMO you should no to this direction, but the answer will be like this.

Direct answer

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? 🙂

Recommended solution

Split information from your oryginal data info fact_transaction and fact_user_to_transaction tables.

Data preparation

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.

Relationship

It will be easier for you to track the logic and write down measures like number of transactions, numer for transactions for each user, numer of transactions where user was a buyer or maybe KPI: share buyer / user role. In this model it will be just easy and it saves a lot of unnessesary rows.

PBIX file: https://we.tl/t-t98gEAuuDF

[twowaysyellow]

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.