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
missdev
Regular Visitor

Help with Customer Behavior Tracking

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 KeyResidency StatusAge CategoryProductSuccessStepStep #Choice
1234AResidentAdultEP99999PUnsuccessfulPrimary  11
1234AResidentAdultEF049O1MUnsuccessfulPrimary12
1234AResidentAdultEF049O3RUnsuccessfulPrimary13
1234AResidentAdultEM011O1RUnsuccessfulSecondary21
1234AResidentAdultEF011O1RPurchasedLeftover31
5434AResidentAdultEP99999PUnsuccessfulPrimary  11
5434AResidentAdultEM069O2RUnsuccessfulPrimary12
5434AResidentAdultEF012P5RPurchasedPrimary13
5434AResidentAdultEF011O1RPurchasedLeftover31
5124BResidentAdultEP99999PUnsuccessfulPrimary11
5674QNonresidentYouthEF011O1RUnsuccessfulPrimary11
7457YNonresidentAdultEF011O1RPurchasedSecondary21

 

Any help would be greatly appreciated! Thank you!

1 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!

View solution in original post

10 REPLIES 10
missdev
Regular Visitor

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!

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")
Result.png

 I've attached a sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

 

missdev_0-1662067626915.png

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...

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:

model.png

 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:

matrix.png

 

filter.png

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

Can you post a depiction of the expected outcome?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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:

 

missdev_0-1662566859323.png

 

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!

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

model.png

 

 

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

 

Retention.png

 I've attached the new file

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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
Community Support
Community Support

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:

vyadongfmsft_0-1662025944798.png

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.

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.