
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Grouping customers based on their actions
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.
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
.png)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Robert,
Try like this in straight chart:
In Dimension:
1. Take your Product as dimension.
2. aggr(Customer,Product)
exp:
Sum(sales)
Hope it works.
Regards
KC
KC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
You could create these groups in the script itself.
For eg: if you have Product and Customer in a table.
then while loading add a new field as below.
if(Product='A','Group A',if(Product='B','Group B',if(Product='C','Group C'))) as Group

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I don't think I can do that, because the groupings need to be arbitrary based on other selections.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you please share your app? That will be helpful.
Regards
KC
KC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am trying your solution as described above. For some reason, it's not giving me the answers I expect, but it seems to be on the right track.
I'll create a sample application and share it which will illustrate the issue.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This would need to be applied to each expression, here's a sales example (total spend per customer - not just their spend on that product)
Sales For customers who have purchase Product A:
sum({$<Customer=P({$<Product={'Product A'}>})>}Sales)
Sales For customers who have purchase Product B:
sum({$<Customer=P({$<Product={'Product B'}>})>}Sales)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The limitation with set analysis is that I have to explicitly set them up myself. In my example I put 3 groups but the reality is that there's thousands. Products are also added dynamically in the source system.
I was trying to get to a solution using the P() type of expressions but hit a brick wall because of this problem.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Robert,
I guess it can be achieved by using pivot table:
PFA
Regards
KC
KC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here's the model app

- « Previous Replies
-
- 1
- 2
- Next Replies »