Hi
I am trying to calculate a percentage of customers who repeated, out of all customers.
Basically count(distinct repeat customers) / count(distinct all customers). This has to be done in beastmode so users can select a date and the repeat customer % will be dynamic.
This is the raw data, Cumulative sessions is used to determine if a customer was a repeat customer.
Cumulative sessions beastmode: count(PhoneNo
) over (partition by Location.ID
, PhoneNo
order by TransactionDateUTC
).
Based on the above screenshot the Repeat Customer Percentage should = 33.33%
My goal to calculate the repeat customer percent is to count the distinct phone numbers whose cumulative sessions > 1 divided by the count of all distinct phone numbers:
count(distinct case when count(PhoneNo
) over (partition by Location.ID
, PhoneNo order by TransactionDateUTC
) > 1 then PhoneNo
end)
/
count(distinct PhoneNo
) * 100
But this is throwing a calculation error.
Is there a way around this? I don't have access to referencing one beastmode inside another beastmode.