Non buying customers 1

[jethrothul]

Hello,

I wonder if anybody has an idea how to get a report showing customers who have not purchased any item ie. non buying customers. There is a Technical Note in the MSI Knowledge base but this only gives the number of non buying customers ie. number of customers - number of buying customers.

Thanks !

 

[nlim]

fairly straightforward. one way is as follows

- create a metric M1 as count(orders) at the customer dimensional level. This metric counts the number of orders each customer has.
- create a filter F1 as M1 = 0. ie. customers with zero orders.

you should be able to add this filter into a report with customer and M1 on it. You can probably add other filters on it as well, time product etc.

 

[jethrothul]

Hello nlim,

many thanks for your help! I'm afraid I must be missing something here as I can't get it to work. I don't know how to set up the metric 'count the number of orders' which will give me values for all values in the lookup table customer. I only get values for the customers in the fact table ie. number of orders > 0

Thanks again.

 

[entaroadun]

You may have to play with the join settings to get that solution to work.

The pass that calculates count metric only returns customers in the table with orders. A second pass is executed on top of the first one to get all customers with zero orders. This second pass will come up empty since there should be no customers from the first pass that have zero orders.

Try it out. If it doesn't work, then try creating a report with Customer and the count metric on it. Create a report filter on top of that with a Not In condition. That should give you what you need.

You may get poor performance, so come back if you need the solution tuned.

 

[FLB]

Check out with parameter you set for your count function, you might not hit the right fact table....
You can try to post your SQL maybe that will help us understand what you are doing wrong.

2 cents,
Flb.

 

[nlim]

Select the text "count" in the metric editor, and RMC. pick count parameters.

you need to set the distinct to true, and the fact table to your order table.

Also add customer to the dimensionality.

To see if this works, create a report R1 with customer on it and M1. this report should show all customers and their number of orders. In theory you should get all customers, some with 0 orders, others with more. If you don't get 0 orders, than you probably need to change the metric to be an outer join metric. And for the report, set the vldb lookup to pick all from the lookup table.

 

[nlim]

jethrothul, did you get this to work?

 

[Golan]

I keep hitting the same problem many times. I can’t find non buying customers unless I put a month attribute in columns, so that, together with the outer join setting of the count order metric, it show nulls (or zeroes if configured to) where the customer had no order in a specific month. But when I use only one month (in a filter), for example, it does not show a customer who had not bought that month. Counting begins with ‘1’. The filter does not work and the report returns no data. End.

I couldn’t find the VLDB setting of lookup table, is this the problem? And I don’t know if the factid parameter of count I set up to the fact ‘order’, the same as the metric (Count([order]), is correct. Am I missing something? Please help!

 

[nlim]

golan = jethrothul?

Don't panic. Try these steps from scratch.

1) for your metric count(order), make sure the factID parameter is set to the order FACT table. Make sure Distinct parameter is set to true.

2) create a report R1 with customer and count(order) on template. Add filter for one month.

3) create a empty filter, save and call it F1.

4) create a new report R2, add customer to the template. Add F1 to the filter, and then
drag and drop R1 onto the filter section. Doubleclick on the AND operator to change it to AND NOT. Your filter should read something like this "F1 AND NOT R1"

5) Run R2 now and see if any customers show up. These are the customers with no orders in the specific month.

let us know how this works and we'll proceed from there.

 

[jonhoward]

The logic is something like "complete list of customers" minus "list of customers who have a purchase value in the fact table for that month" equals "customers who didn't make any purchases this month"

Customer and Month on your template.
Filter is metric qualification that says "Purchase Value (or whatever your metric is that indicates 'Purchase') is Null"
VLDB Setting for the report is Joins -> Preserve All Lookup table elements -> .... without filter.

That's it. Isn't it?

 

[Golan]

I think the logic is ok, but when I use SUM instead of COUNT (that is, when I check the balance of the ‘account’ of each customer instead of counting the number of records for the sales fact for each customer), I can’t get the filter “Is null” or “Exactly 0” to work. It returns no data at all. Do you know how to solve this issue?

 

[nlim]

jonhoward, that won't work because the isnull or exactly 0 will not outer join to the customer lookup in the intermediate pass. So, non buying customers are automatically excluded.

Golan, try my solution from the Oct 22 post, and let us know if that works.

 

[Golan]

nlim,

It worked: for a specific month I now know the customers that didn’t buy anything. For R1 I set “Metric Join Type” to “Outer” and for “Attribute Join Type” I used “Preserve common elements of lookup and final pass result table”. What’s next? Thank you!

 

[nlim]

now R1 can be used as a filter for your final report. Just drag and drop it into the filter section of your report.

For your other post of "non-active" customers, I will post a solution there.