Hello PUG community,
I am looking for way to show a list of customers that have purchased a complete number/list of products (10-30 products that would be considered “core”) for a defined period.
I was hoping someone may have had a similar request/need for this type of information.
I was thinking a traffic light or a tick/cross next to those customers that have or have not purchased the whole list from the core range.
Hello PUG community,
I would like to see something like this as well.
This is a bit…well…clunky. But I have a solution to make a report that would run this for you. It takes quite a few steps, but if you do this once and those “core” products don’t change often, then this should work. So here we go:
Step #1: Run a new Favorite by the Product dimension and define your Period, search your “core” products and then “Focus” on them. You could also “Matrix”, if you want to see the product-by-product breakdown later.
Step #2: Click into your Customer dimension (however you have it named). Go into Advanced Search.
Step #3: Here’s where it gets clunky. Specify each product having sales over 0 for your specified time period. I chose 3 of my products YTD. Here are my steps:
Make sure it is by your Customer dimension, and that “And” is selected. Then add as many Measure slots as you are going to search for products - in other words, you will be specifying ONE PRODUCT per MEASURE.
Click into the first measure, specify your stream and measure, period, and first product selection. After making all selections, click “Apply”.
For that newly created Measure, select “Greater Than” and enter 0.
Do the entirety of step #3 for as many products as you need. Again, I chose 3 products. So my final Advanced Search screen looks like this:
Click “Apply”, and your customer list should ONLY show accounts that have specified that entire list of chosen products. Good luck, and please let me know how it works! I verified data by running it this way, and in Matrix mode pulled into Excel.
Thanks @tim.janes I have given that a go and you are right it works but like you said, it is quite clunky.
The result from the Advanced search shows the customers that have bought each of the products, but one of my wishlists from the search would also be who has not bought all of the list of products and the count of how many from the list they have actually purchased
I thought of trying to get a count of the number using Custom Mode, but there is no COUNT function there.
Is there any other suggestions form anyone?
Thanks again for the suggestion @tim.janes
You’re welcome, Patrick.
For the list of customers who have not purchased all of the products, you could probably just run a report by the Product dimension, search the list of products, “Matrix” them, then hit your Customer dimension. This would show both customers who have and have not bought the entire list. From there, it would probably be easiest to pull into Excel and do a COUNTIF formula or others from there to get your information.
That might be the less-clunky and quicker way to get your info for the customers who have bought each product, as well. Specifying a formula in Excel such as =COUNTIF(range,“0”) in a column after all Matrixed items would give you the count of how many items they have NOT purchased. From there, you could separate those with a “0” value returned, meaning they bought ALL items, from all the other customers. Here’s an example of what it might look like:
Again, I hope this is helpful. Let me know how it goes!
Thanks again for your suggestion @tim.janes it is helpful. If it was just me wanting this info I would do it that way or similar, but the idea was to have this on a dashboard for easy use and viewing for users that would not be able to use Excel to get the required info.
Again thanks for your time in suggesting this solution