Help with Customer Behavior Tracking

[missdev]

Hey all!

I am trying to build a Power BI where we can show the aggregate behavior of our customers across different stages in our process. We have three steps to our process:

Primary and Secondary, which can each have 4 choices per customer

Leftover, which can only have 1 choice, but a customer can purchase multiples of

What I am trying to show is when a customer applies for a certain product at a certain step and choice, what their behavior looks like over the remaining steps. For example, I want to be able to build a visual to show the number of applicants for EP99999P for step 1, choice 1 and how many people applied for each subsequent step and choice (step 1, choice 2; step 1, choice 3; step 1, choice 4; step 2, choice 1... etc). I don't need to know WHAT they applied for or purchased in those subsequent actions, just how many participated. 

The question we are ultimately trying to answer is how many people who apply for X product as a 1st choice in step 1 go on to apply or purchase other products later on and which ones.

Here is a sample of my data with PII scrubbed.

Customer Key	Residency Status	Age Category	Product	Success	Step	Step #	Choice
1234A	Resident	Adult	EP99999P	Unsuccessful	Primary	1	1
1234A	Resident	Adult	EF049O1M	Unsuccessful	Primary	1	2
1234A	Resident	Adult	EF049O3R	Unsuccessful	Primary	1	3
1234A	Resident	Adult	EM011O1R	Unsuccessful	Secondary	2	1
1234A	Resident	Adult	EF011O1R	Purchased	Leftover	3	1
5434A	Resident	Adult	EP99999P	Unsuccessful	Primary	1	1
5434A	Resident	Adult	EM069O2R	Unsuccessful	Primary	1	2
5434A	Resident	Adult	EF012P5R	Purchased	Primary	1	3
5434A	Resident	Adult	EF011O1R	Purchased	Leftover	3	1
5124B	Resident	Adult	EP99999P	Unsuccessful	Primary	1	1
5674Q	Nonresident	Youth	EF011O1R	Unsuccessful	Primary	1	1
7457Y	Nonresident	Adult	EF011O1R	Purchased	Secondary	2	1

Any help would be greatly appreciated! Thank you!

[missdev]

Thanks for your reply - unfortunately it's not what I am looking for. What I need is to show is (obviously fictious numbers):

10,000 people applied for EP99999P for Step 1, Choice 1

of those 10,000, 5,000 applied for anything at Step 1, Choice 2

of those 10,000, 3,000 applied for anything at Step 1, Choice 3

of those 10,000, 1,000 applied for anything at Step 1, Choice 4

of those 10,000, 6,000 applied for anything at Step 2, Choice 1

of those 10,000, 4,500 applied for anything at Step 2, Choice 2

of those 10,000, 3,400 applied for anything at Step 2, Choice 3

of those 10,000, 100 applied for anything at Step 2, Choice 4

of those 10,000, 9,000 purchased something at Step 3

Does that make more sense?

I would like to change the initial selection of what they applied for so that I can show this information for other products as well.

Thank you!

[PaulDBrown]

I think @v-yadongf-msft suggestion is what you need, albeit with a different visual layout (a Matrix visual with Product, Step and Choice as rows; Success as columns - or a measure filtering out the "unsuccessful")

 I've attached a sample PBIX file

[missdev]

Thanks for the help, but it's still not working as I expect. The issue is that when I'm doing the distinct count, it's only counting when someone has applied for that specific product for any of the steps and choices, not when someone has applied for that specific product for step 1, choice 1 and ANYTHING ELSE for the remaining steps. 

This is when I select EP99999P from the splicer

Since EP99999P is only available at Step 1, Choice 1, it's only showing the customers who applied for that product. I need it to show everyone who applied for that product then how many of those initial applicants took an action and successive steps...

[PaulDBrown]

In that case try the following...
I've set up the model with dimension tables for the main fields and another disconnected table for products to use as the slicer:

 Create a measure to calculate the number of products:

Number of products = 
DISTINCTCOUNT(fTable[Product])

Create a measure to filter the customers who have purchased the selected product to use as a filter for the customer dimension in the filter pane:

Products from selection =
VAR _CustList =
    CALCULATETABLE (
        VALUES ( 'fTable'[Customer Key] ),
        fTable[Product] IN VALUES ( 'Product Sel'[SelProduct] )
    )
RETURN
    COUNTROWS ( _CustList )

Set up the matrix with fields from the dimension tables: Customer, Step, Choice, Product and success. In the filter pane, select the filter for the customer dimension, select TopN from the dropdown, input 1 and add the [Products from selection] measure:

I've attached the sample PBIX file

[missdev]

Thank you again for the response. I tried what you laid out and I am still not figuring out how to get the aggregate I'm looking for. I am needing to go beyond a matrix of individual customer behavior and see the aggregate. I need to turn this into a visual for presentation, and I still can't get that to come together with the solutions presented.

[PaulDBrown]

Can you post a depiction of the expected outcome?

[missdev]

What I am looking for is customer retention based on what the customer applied for as their first choice. So, if they applied for EP99999P, I need to see how many of those people participated in the other steps of the process. The chart might look something like this:

When "EP99999P" is people who applied for that at A.1 and "Other" is people who applied for anything else at A.1. It does not matter WHAT they applied for at A.2 and further, the ONLY thing that matters is what they applied for at A.1.

Everything I've been able to create will not filter A.2-C.1 based on the choice for A.1, which is what I need.

Thanks!

[PaulDBrown]

If you want to use measures, here is one way (with the independent product selection table - same model as I posted previously)

Customer retention =
VAR _List =
    CALCULATETABLE (
        VALUES ( 'fTable'[Customer Key] ),
        FILTER (
            ALL ( fTable ),
            'fTable'[Product] = SELECTEDVALUE ( 'Product Sel'[SelProduct] )
                && ( 'fTable'[Step #] ) = 1
                && fTable[Choice] = 1
        )
    ) // Creates a table of all customer keys which have bought the selected product at step 1 and choice 1
VAR _Cust =
    VALUES ( 'Dim Customer'[dCustomer Key] ) //lists customer keys
VAR _FiltCust =
    INTERSECT ( _Cust, _List ) //Creates a table where the customer key is present in the _list table
VAR _Result =
    CALCULATE ( DISTINCTCOUNT ( fTable[Customer Key] ), _FiltCust ) // Calculates the distinctcount of customers present in _FiltCust table
RETURN
    _Result

Other =
VAR _List =
    CALCULATETABLE (
        VALUES ( 'fTable'[Customer Key] ),
        FILTER (
            ALL ( fTable ),
            'fTable'[Product] <> SELECTEDVALUE ( 'Product Sel'[SelProduct] )
                && ( 'fTable'[Step #] ) = 1
                && fTable[Choice] = 1
        )
    )
VAR _Cust =
    VALUES ( 'Dim Customer'[dCustomer Key] )
VAR _FiltCust =
    INTERSECT ( _Cust, _List )
VAR _Result =
    CALCULATE ( DISTINCTCOUNT ( fTable[Customer Key] ), _FiltCust )
RETURN
    _Result

 I've attached the new file

[missdev · Accepted solution]

I FINALLY figured it out. For folks looking, here's where I found the solution:

https://community.powerbi.com/t5/Desktop/Group-Customers-by-Products-Sold/m-p/618373/highlight/true#...

Basically, all I needed to do was group customers by their first choice and then build my visuals from there. Thanks for all the help!

[v-yadongf-msft]

Hi @missdev ,

Please try following DAX to create a new measure:

Number of people = DISTINCTCOUNT('Table'[Customer Key])

Then create a table visual like below:

If I misunderstand your demands, please feel free to contact us in time.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.