Count Distinct Customer Visits Without Showing Customers in Card

I am needing some help with a project I am working on where I need to report on how many customers visited in a given time frame.

 

Attached is sample/dummy data of the dataset I am trying to analyze.

 

Requirements:

1) Use Visit_Date as a filter (date range)

2) Report how many distinct Customer_ID visited during the filtered Visit_Date range

 

Expected output:

For 1/1/2020-1/10/2020

Visit_Count_Bucket            Count_of_Customer_ID

1                                         2

2                                         1

3                                         1

5                                         1

 

Current Visit_Count_Bucket code that isn't working UNLESS I bring in Customer_ID into the card

Case when COUNT(DISTINCT `Customer_ID`, `Visit_Date`)  = 1 THEN '1'
     when COUNT(DISTINCT `Customer_ID`, `Visit_Date`) = 2 THEN '2'
     when COUNT(DISTINCT `Customer_ID`, `Visit_Date`) = 3 THEN '3'
     when COUNT(DISTINCT `Customer_ID`, `Visit_Date`) = 4 THEN '4'
     when COUNT(DISTINCT `Customer_ID`, `Visit_Date`) = 5 THEN '5'
     when COUNT(DISTINCT `Customer_ID`, `Visit_Date`) >= 6 THEN '6+'
     when COUNT(DISTINCT `Customer_ID`, `Visit_Date`) <=0 THEN 'No Visits'
     end

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Accepted Answer

    @JeffDaySJP  i hate to say it, but I don't think you're going to get this one across the line without pre-aggregating your data.

     

    with the new Data Views beta, you'll be able to generate the aggregation in a VIEW, a la.

    CREATE VIEW new_domo_dataset as 

    SELECT

    count( distinct customer, date) as visit_count,

    customer,

    date

    from table

    GROUP BY

    customer, date

     

    AND then use new_domo_dataset as the input for a card.

     

    ask your CSM about access to the data views beta.

This discussion has been closed.