I have been stuck on this for a couple of days now, so I would appreciate any help. There several posts about distinct count but my problem seems unique.
I am analyzing sales for a two day sales event. I have 4 customer cohorts. All, Deal customers, new, and new + deal customers. And I want to use controls to cut the data in multiple ways. So I created the following parameters.
Left colum show the cust count adn the rigth colum shows the the unique count. If I run a query to check the numbers I should be 653,050 new customers.
I was trying different aggregation levels, so if I do this:
distinctCountOver({customer_id},[pg],PRE_AGG) I get 1.2M which is closer but still is twice as much as it should be. This tells me I might be missing something at the aggreation level.
Hello @jjhernandezg - The calculation you are suing is going to give you the distinct count at the pg i.e. Business Unit level. Is this what you are looking for?
I am doing an analysis on customer count looking at four cohorts all, deal customer, new and new deal customer. The query has customer ids by region, product group, category, sub_category, and so on.
I created parameters for each of the columns mentioned above to make the analysis dynamic. The filters are working because I see the counts go down, but they distinct count calculation is not accurate.
I know there are 653,050 new customers, so I don’t know why the distinct count is not working. If I use this formula distinct_count({customer_id}) and a new customer filter, I get 4.1M. This formula distinctCountOver({customer_id},[pg],PRE_AGG) returns 1.08M.
It seems that distinct count is double counting. It is not accurate even in a sheet with no parameters.
Hi,
so in your raw data, you get the correct unique count?
Because for me it looks like, you have 1.6 million customer in your dataset.
Can you test another thing? create a new tab in your report, so we have no active filters. Create a visual ( KPI type) and insert {customer_id} as a value. As aggregation choose unique count.
without filters, another dimensions, etc. this shows us the complete unique count of the customers in your dataset.