Grouping customers based on their actions

[#1]

Hi All,

I would like to be able to make arbitrary sets of customers who have performed some action, and be able to put them into a chart. Let me illustrate.

E.g. I want to be able to group my customers into those who have bought product A, product B, product C or None of the Above. Then I'd like to show my sales for each. Result should look something like:

Group Product A     1M

Group Product B     1.4M

Group Product C     2M

None of the Above     10M

The sets are not necessarily mutually exclusive, so a customer can be in Group Product A as well as Group Product B, if she happened to have bought both products.

The groupings should also take into account any selections, e.g. time period selected. So if a user chose July 2014, then the groupings should reflect those who bought a product in July 2014. The final aim is to be able to compare Lifetime Values based on customer behavior.

Thanks all.

[Anonymous]

Does your full data model include invoice ID or something that designates a total invoice?

If so, please repost with that included. I may have a solution.

[mark_casselman · Accepted solution]

Hi,

A solution for 'row level formulas' is automatically generating all possible formulas and put them in a 'pick, match' formula.

Suppose you have 3 products: A, B and C, you should use straight table with the product as dimension and the following formula as expression:

pick(match(IslandProduct,'A','B','C'),sum({<Customer=P({<Product={'A'}>})>}Sales),sum({<Customer=P({<Product={'B'}>})>}Sales),sum({<Customer=P({<Product={'C'}>})>}Sales))

This should work for 3 products.

But, as you mentioned there are a lot more products. In this case you can generate the necessary formula by creating a variable vFormula with the following expression (including the single quotes when entering the formula) :

'pick(match(IslandProduct,'&concat(chr(39)&IslandProduct&chr(39),',')&'),'&concat('sum({<Customer=P({<Product={'&chr(39)&IslandProduct&chr(39)&'}>})>}Sales)',',')&')'

Then make the straight table with Product as dimension and =$(vFormula) as expression.

You get as result a table with the sum of sales of the customers that have the product of the row.

This solution generates a long formula, so thousands of products might not work or work slow, but a hundred products should work fine.

In attachement you will find a test dashboard with 3 products (just add more customers and products to test more products, I tested it with over 20 products).

Mark

[jyothish8807]

Hi Robert,

Why dont you use pivot chart?You can acheive it pretty easily.

Regards

KC

[#5]

No it doesn't. The actual data model is aggregated so transaction level information is thrown away.

[#6]

A pivot chart will not give me the results I need.

The results are sort of generated in 2 steps:

1) Get those customers who bought product A (or took promotion 1 or whatever).

2) What was their revenue?

A customer can have bought product A as well as product B. In that case, he has to appear in both sets. A pivot chart will only tell me what the total sales on product A are no?

[#7]

I'm having a look at your solution right now. It seems like this is the right one.

[#8]

This worked! Thanks.

Performance suffers when no selections on product are made. But when the user selects a few products, it's great.