What else do my customers buy?
We want to find out, for example:
This month, which of our customers have bought a specific product. Let's say Gold.
Then we want to see what else they've bought.
(We'd then try and use this to enhance our product pages on our website, and display a "You may also be interested in this, this, and this, because existing customers for this product are interested in them".)
I'm able to get the customers who bought the product, that part is easy.
I am then unable to find out what else they have bought, because it is already filtered based on product name.
May Kwok You could get the list of Trading Account IDs you want to look at and then do a search using a bulk filter on just these IDs. You can add bulk filters by clicking on 'Add values in bulk' in the normal filter box. Then add "product short name not contains 'gold'" to your search and you'd get a list of all the other products they purchased.
If the list is too large for bulk filters you could also try creating an aggregated worksheet of just the customer ids you want to look at and then create a relationship between that smaller list and the source data so you just get the products those customers bought. Then add the same "product short name not contains 'gold'" to your search to see the other products.
Another option is to save this report as an aggregated worksheet called "Gold Purchasers". Then create another aggregated worksheet of purchases that are not gold and call it "Non-gold purchases". Join the 2 aggregated worksheets on the trading account id (and also the month, if you are only interested in purchases made in the same as the purchase of the gold). Then create a new report from these two aggregated worksheets and pull in fields from both tables to ensure that they are joined. You could possibly pull the account id from "Gold Purchases" and Product Name from "Non-gold purchases"
Thanks Vanessa! I was hoping to have it completely automated so that my end user doesn't need to make any effort...! But yes I think this is certainly something I can implement. Maybe I'll do something like a "top 50" so that the list will be a definitive size.
And thanks Siva for the suggestion. I'll give that a go too. The raw data that this will be based on is quite a big table with a lot of joins, and the users will be querying the Worksheet. I'm concerned about performance but will certainly give it a test.
Thank you both for your help!