0

Determine Net New customers via Select Statement for Dashboard

I would like a formula field that will represent the number of clients we have in a month that are first time customers. We are an event company. This will be via a field on a dashboard I'm creating.

I have a Customer table and Event Detail table. The Event Detail table holds the date, time, and all details for each event. The 2 tables are tied together via a 1toMany relationship ie 1 client can have many events.

Essentially if a customer only has 1 record in the Event Detail table then they are not a returning customer. 

How would I write a query/script to look at clients for the current month and determine if they are a first time client?  
 

3 replies

null
    • Fred
    • 1 yr agoTue, March 21, 2023 at 3:05 AM UTC
    • Reported - view

    1) why do you only want to look at the current month? Don’t you want to check your whole clients table?

    2) where are you looking up the client? On a dashboard? Or in the clients table after you have entered the data?

    seems like a new client would not exist in your clients table at all? So can’t you search on name?

    • Fred
    • 1 yr agoTue, March 21, 2023 at 3:55 PM UTC
    • Reported - view

    Sorry, I need to stop responding so early in the morning. Forget my last post.

    You can try:

    count(select Customers where 'Event Detial' = "")
    

    Interestingly, I first used = null and that didn't return any values, but when I switched to "" then it worked.

    • Firefly Team Events
    • James.3
    • 1 yr agoTue, March 21, 2023 at 10:55 PM UTC
    • Reported - view

    Fred, thanks for the input - I'm know enough but not enough so what is simple or obvious to you may play differently in my head. I appreciate any insights. 

    Dashboard: I have a dashboard that displays monthly stats for each sales person. It includes revenue, # of events, etc. The dashboard is it's own table comprised mostly of formulas. 

    My intention is to wire a formula field to look at total events for the current month, determine how many of those are from new clients vs returning clients, and display that number. 

    Does that add clarity and answer your questions? 

Content aside

  • 1 yr agoTue, March 21, 2023 at 10:55 PM UTCLast active
  • 3Replies
  • 65Views
  • 2 Following