Calculate % of Repeat Customersvia Beast Mode

swagner

Trying to help co-worker with a beast mode... and first not sure if this is possible at the card level (maybe better handled with a dataset view).

Here's the business question: How many of our eCommerce customers are repeat customers? Displayed at a % of total eCommerce customer count. Example: 25 of 100 customers are repeat customers, or 25%

• The card is filtered on the eCommerce transaction type
• The order data is at the line item level, and we are today counting "Customers" via COUNT(DISTINCT `Customer Name`)

Logically this would be something like...

HAVING(COUNT(DISTINCT `Customer Name`) >1) / COUNT(DISTINCT `Customer Name`)

GrantSmith

This will be much simpler to do within an ETL than attempting to do it within a beast mode.

I'd recommend using a rand and window tile in Magic partitioned on the customer, sorted by the order time and calculate a Dense Rank to determine the order number / count for each specific user. You can also partition on the transaction type to determine this within each transaction type. Then in your ETL, calculate the max value of this count for each customer and if it's greater than one then mark them as a repeat customer. Finally in your beast mode you can then calculate the number of distinct customers with:

COUNT(DISTINCT CASE WHEN `Is Repeat` = 1 THEN `Email` END)
/
COUNT(DISTINCT `Email`)

GrantSmith

Do you want this to be affected by filtering the transaction type or is this to be across all transaction types?

swagner

There will be filtering on the card (in analyzer) for the eCommerce transaction type. That would narrow the field down to customers who have ordered (all in the data) and customers who have ordered more than once.

GrantSmith

This will be much simpler to do within an ETL than attempting to do it within a beast mode.

I'd recommend using a rand and window tile in Magic partitioned on the customer, sorted by the order time and calculate a Dense Rank to determine the order number / count for each specific user. You can also partition on the transaction type to determine this within each transaction type. Then in your ETL, calculate the max value of this count for each customer and if it's greater than one then mark them as a repeat customer. Finally in your beast mode you can then calculate the number of distinct customers with:

COUNT(DISTINCT CASE WHEN `Is Repeat` = 1 THEN `Email` END)
/
COUNT(DISTINCT `Email`)

swagner

@GrantSmith thanks!

DavidChurchman

To do aggregate (as opposed to row-level) calculations in a BeastMode, you'll need to use FIXED functions:

https://domo-support.domo.com/s/article/4408174643607?language=en_US

Yours would be something like:

(case when sum(count(Distinct `Customer Name`) fixed())>=2 then 1 else 0 END)/sum(count(distinct `Customer Name`) fixed())