Find Count Distinct w. Multiple Factors

user17258

Hello!

 

I need to find ‘new initial customers’ by sales agent at the campaign level.

 

For the purpose of this report, we define a ‘new initial customer’ as a customer (Customer_ID) who entered into a campaign (Campaign_ID) within a specified time range (by calendar month). 

I need to only count Customer_IDs the first time they opted in (as there can be multiple of the same Customer_ID under the same Campaign_ID) over the given calendar month, but then capture that and calculate again for the following month, regardless of the data the month prior and so on. So looking to capture the data for each month and the first occurrence that month under each campaign. 

 

I would like to do this within an ETL. However, beast mode would be okay too. 

 

When doing this at the card level in beast mode, I used case when statements but ran into an issue.

 

For example:

 

(Case

when Campaign_ID = 51 then count(distinct Customer_ID)  

when Campaign_ID = 52 then count(distinct Customer_ID)

when Campign_ID = 53 then count(distinct Customer_ID)

End)

 

That seemed to work, but when I went to multiply that by order_total to get revenue, it gave me a larger number than when I sum(Order Total) for the same campaigns, unfiltered.

 

(Case

when Campaign_ID = 51 then count(distinct Customer_ID)  

when Campaign_ID = 52 then count(distinct Customer_ID)

when Campign_ID = 53 then count(distinct Customer_ID)

End)*'Order Total'

 

Any insights as to why I’m getting incorrect numbers and what I could do in the ETL?

 

Sample data attached. 

MarkSnodgrass

I think ETL is definitely the way to go on this. I think a combination of MAX and SUM would get you where you want to go. Could you provide a screenshot of what the end result of your sample data would look like? I'm having a bit of a hard time visually what you are looking for based on your description.