Count Distinct Customer Visits Without Showing Customers in Card

JeffDaySJP

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

jaeW_at_Onyx

@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.

jaeW_at_Onyx

@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.