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.
3 Likes
I would like to see something like this as well.
@Patrick.McBriarty
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:
Advanced Search:
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.
2 Likes
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!
1 Like
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
1 Like
Hi Patrick,
To identify Core products, it will be best to do it at Source Data that you sync to Phocas (can be excel) and then add it as a subdimension of your Product. It will then gives you the flexibliy of maintaining the Core products list.
Then you can focus on Core to get product list and continue to use standard Phocas measures.
For the measure, you can then use the Case When (similar to If logic in Excel) in the following post :
Eg: If sales >0, then ! else - to give you an indicator of those customer that have or have not purchase the core products.
There is a easier way to do this using market mode.
Choose Market mode
From the product dimension select and focus on your core products
Change to Customer Dimension
Change Market button to Products
Returns how many products the customer buys and how many of the selected core they buy.
2 Likes
Hi Neil, does Market mode need to be set up and enabled? I can’t see the option in our system.
Are you focused in on a “market” first? Choose a dimension, select a few and click focus. See if you have a “Market” mode available now. (It doesn’t show up until you have focused in on some dimension.)
I stand corrected on my previous post. You do not have to be focused anyway. You just have to have a dimension selected.
Most likely then the Market Mode isn’t enabled on your user. If you’re an admin user, you can change this yourself. Otherwise you may need to get an admin to check it for you.
Thanks, I’ll check tomorrow
Ok, got it working but struggling to follow what the numbers actually mean.
Maybe this example will help out a bit.
I want to take a particular customer type, and see how many of those customers are buying some particular product categories. So, first, I go to my customer type dimension, and focus on the customer type I want to take a look at. Then I switch to Market mode. This customer type becomes the “Market” (“A” in screen shot). Next I switch to my Product Category dimension, and I select the product categories I’m analyzing, and Focus on them. These categories now become the “Segment” (B). Then I select the dimension I want to count (from the dropdown), in this case I chose Customer (“C”). Lastly, I want to look at these numbers by location, so I switch to my location dimension. So, in this screenshot, I can see that I have a total of 144 customers of this customer type. (D). (Customer Type is my “Market”). 71 of those 144 customers have purchased items in my selected product categories (E). (My product categories are my “Segment”). (F) just shows this as a percentage… 49.31% of the Market (Customers) have purchased this Segment (Product Category). Since I’m looking at this by location, I can see that my Asheville location has 7 customers of this customer type, of which 4 customers, or 57.14%, have purchased products in these categories.
Since I also have my “Sales” measure selected, I can see these sales figures as well. I can see total sales by customers in this customer type (G) and I can see what their sales are for products in these product categories are (H).
Does this help?
2 Likes