Calculate % of Repeat Customersvia Beast Mode

swagner
swagner Contributor
in Beast Mode

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`)

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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`)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • swagner
    swagner Contributor

    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
    GrantSmith Coach
    Answer ✓

    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`)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • swagner
    swagner Contributor

    @GrantSmith thanks!

  • DavidChurchman
    DavidChurchman Coach

    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())

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Categories