Displaying Segment Groups


#1

Hi There

I can’t work this out!
I have a classic Sales Table. Customer sales go up and down, so I’ve segmented my customers in Groups - High, Med, Low - and this changes every month. A customer can move from High to Med to Low and back again over a series of months.

I hold this in a table, Cust ID, Segment Class (Hi, Med, Low) and Month (the various Customers in each group that month). These are calculated outside of BI.

What I want to show is the various sales by Month for the total class so that I can see (eg) Total Customer Sales by Hi for each of the previous months. That will allow me to see if Hi customers are increasing / decreasing etc.

I can’t think how I would link this on a data model. I can link Class to Customer to Sales table, but this will only return Total Sales, as I can’t work the Date dimension into the model. I (think I) want All Customers in a Class in this month; total sales for that month for this customers. That creates too many date relationships in the model.

Amy Clues?


#2

This can all be done in Power BI using segmentation techniques and formulas.

Check out the below example

Some other examples of similar techniques below


#3

Hi,

Thanks for those examples. The challenge is slightly different.
The segmentation is done on a different platform so it can be published across many systems. In effect it creates a table with Customer Classification - (say) Good, Med, Bad; a customer ID, which customers sit in each group; and a month, which is the month the customer sat in that group. So importantly a customer can move across a group month on month. This granularity allows Customer behaviour to be tracked on a granular basis.

So if I choose Good Customers, then the cohort will change month on month.

I also have:
A customer reference table - Customer ID, Location, Contact details etc. All static information.
A Sales table which has invoices by Date(ddmmyy); Value; Customer ID
A Date Table with ddmmyy and of course Month (to connect with Month in the Customer Segment table)
What I want to show is how the make up of Good, Med, Bad changes month on month. This will change by make up of the Customers on a monthly basis as Customers move across each of those groups. It is not dynamic, it is set by the Segmentation Platform.
I want to create something akin to Cash Flow, but called Customer Flow. So month on month how does the make up of Good change by Value, Volume, Margin etc.

But I can’t work out how to do that? Make sense?


#4

I think I would have to see an example of the scenario in a Power BI model to offer any assistance here.

Sometimes these calculations can be complex, depending on the data structure and how you want to ultimately show it within a report page.

If you could set something up and send it through that would be helpful.

Thanks


#5

Hi,

I annonomised all this so its good to browse.
The basic output I am looking is like this:

Segment Q1 2017 Q2 2017 Q3 2017 Q4 2017
Platinum £1,234 £2,455 £3,456 £3,456
Gold £1,234 £2,455 £3,456 £3,456
Silver £1,234 £2,455 £3,456 £3,456
Bronze £1,234 £2,455 £3,456 £3,456
Transactional £1,234 £2,455 £3,456 £3,456

The numbers are mis-leading, but you can see I want to see how the Segment Groups vary over time. So are good customers spending more or less. Then to drill down and see what customers make up Gold and why are they spending more or less - more product, different prices etc. Once I have the basic construct then I can drill down for hours on the make up.

I set up this pbix to show the basics.
Segment Example.pbix (1.5 MB)

The data model isn’t correct but I was playing with connections.
So the key tables are
Invoice Table - all the raw data
Date Table
Cust Ref Table - reference data on a Customer
CustSegmentbyQ - So in each quarter customers are one of P,G,S,B,T and then they possibly move to segment in the next Q.
I put a key measure of Total Sales just to try and work it out.

I almost want to create a virtual table each time, of all the customers in a segment in a quarter, and total the sales for the selection, but I can’t.

I think it is a combination of date logic and the relationship between a Customer ID and the Invoicetable.

I’m not sure whether I should be amending the Raw Data (one way might be to do all the totalling outside of BI and just have another fact table - add Total Sales to Cust Segment by Q);
OR creating logic in the data model - the QQ-YYYY link table has a role but doesn’t play fair, and when I connect CustSegment to CustRef I get circular many to many relationships

Or if there is some commands I don’t know yet.

Or, probably all of the above!


#6

To me this looks nothing different to the standard patterns I’ve reference above for grouping and banding information.

I’ve had a look at the demo model and it’s seems the same. There’s no difference to the scenarios really.

Not to sure what this is doing below, doesn’t seem necessary

This table here isn’t sufficient because it doesn’t contain the information required to group any of the results

image

After looking at this even more I just can’t see any difference to what you are trying to do here to the example I placed above. It’s exactly the same.

You only need one virtual table here laying out the bounds for each group.

By doing this then you will see how the spending within these groups change over time and also who is doing the spending within those groups.

Really review the below example. This shows you exactly how to do this.

This is the formula pattern you need to use (with you’re own inputs)

This is the type of table you require as a virtual table with no relationship to the core model

This same formula can then be used in the same context you require it as per below…you just need to set up a matrix with the correct inputs

image

This also here is all you need for your core model (nothing else is required from what I can see, other than your virtual table)

image

Have a go really working through this example and try to replicate everything.

Thanks
Sam


#7

Hi,

I think my example is different in a couple of material ways:
Segmentation Business Rules - are more complex than a min, max sales figure. That is why they are developed in a different application which has access to more data. That core system then shares that segmentation output across many applications, not just BI. It wouldn’t make sense to try to replicate that logic in BI.
The segmentation values are dynamic. In the your example, the ‘business rules’ are hard coded and don’t change in response to customer behaviour. If for example, there is a period of poor weather and sales drop because no-one purchases anything, then all customers become bad in that period. The segmentation I am working with looks at all customer behaviour in one month and then classifies all customers in that month, so a good customer will still be good relative to all customers even if there total spend is lower - providing all customers are spending less that month.

The segmentation reporting needs to show Period on Period movements and the underlying metrics of those customers who have moved. In the example total profits are calculated across all period included in the filter. I need to show which customers have moved across segments Period on Period and then provide Insight as to why. Similarly I need to show insight across the entire segment as well.

Apologies for my model not making sense to you. I did say it was an n th iteration and would not be correct.

I going to look at the problem differently. The use of the Segmentation word kicks off a different train of thought. It is essentially a reporting challenge with different groupings across different periods.