How to count items in a metric without removing dimensionality?
Howdy y’all -
Our team is working on doing a simple customer count, but is struggling to do so without removing all dimensionality from our metric.
We want to count unique instances of a customer where there is spend - but have dimensionalities flagging different types of spend attributes (ex: a flag for customers who have spend in the US vs those who have spend outside of the US). So - if a customer has spend in both the US and outside of the US - they will have a customer count of over 1, when we want each customer to only be counted once while retaining dimensionality. None of the count/countunique formulas are adequately solving this issue.
The page aggregators are correctly counting the number of customers - but we are unable to use the page aggregators in tables to do calculations. Does anyone know of a way to use the “count” from the aggregator for table calculations - or of any other ways to get customer count without double counting across different dimensions?
Page 1 / 1
Hi,
I’m not so sure about your use case but let’s assume you have a Source Metric dimensioned by Customer, Month and Spend.
Source metric
Then if you use a COUNTOF() or COUNTUNIQUEOF() function for example, it removes dimensionality:
But, if instead you use an aggregation method: or BY COUNTUNIQUE: ] you would keep the dimensionality.
But still, each cell is counted because my numbers are not unique.
So for the Blue Customer, you’d still end up with a 2 for each month.
To avoid that you can either have a complex formula to transform the 2s into 1s and keep your 3 dimensions, or the best way is to remove the dimension that is not desired, in this case Spend. And use an appropriate aggregation method, like MIN or COUNTUNIQUE:
You lose the Spend dimension but you keep the other dimensions with the correct count.
Hope this helps.
Best,
Hey Benoit - thank you for sharing! I do think a key part of this is that we are not trying to lose or remove any dimensionality to make this work.
Hi,
Have you tried the last option to see if it works for you?
This option removes Spend but keeps the other dimensions.
Because when you say you need “to count customers for 1 either if they spent or not in the US”, unless I’m mistaken, it basically removes the need for the Spend dimension, you no longer need to be able to pivot your data by Spend because it makes no difference, you need your customers to count for 1 regardless of it.
But I will ask for a second pair of eyes on this one.
Please let me know as well.
Best,
Howdy Benoit -
I made a dummy transaction list/metrics to highlight the goal/issue better. Theoretically - we want a customer count that we can still filter on. IE - if want to count customers with the product/US-nonUS/vertical metrics. Sandra’s Shirts in Feb 25 shows a count of two because the company is using two different software types. Bobby’s Big Trucks has the same issue because the business has US and non-US Spend. The page aggregator gets the count right, but the by countunique only returns a 1 if we strip out all but month and one of the three metrics we want to filter on.
Hi,
I reproduced your example.
But first, I don’t think there is a formula to return only 1 when there is an intersection.
Best solution I could find is having only 1 at the aggregated level everytime using proportions.
This means that if you use the dimensionality to filter the Customer by Country, Product or other, you’d not have a one but a % of 1.
Source list:
(same data as your example, only the names and amount changed)
Customer count with all dimensions
Same metric as Test Benoit, but highlighted in red the conflicts.
Proportion metric
Proportion metric aggreated at the customer level
Here, at the Customer level, each customers counts for 1.
Proportion metric filtered on Non-U.S.
In this case there is the right amount of data by dimension, and the count is correct.
But if you filter on a dimension, you have only the splitted share of that Customer.
Bottom line, maybe it’s best to use the aggregators? .
Hope it helps.
Benoit - do you know if there is a way to reference aggregators as values on table calculations? If there is a way to - then the multi-dimensionality in count is not really needed anymore.
Hi,
Unfortunately, aggregators are just visual view components, they’re not stored objects that you can reference.
.