Sunday, April 14, 2019

All Customers and their Total Sales even if Customer has no Sale for certain time period Saved Search

Overview of Customers and their Sales Activity to know how active they are and total amount spent for products or services within a certain time period. 

Note: 
It is necessary to build two separate Saved Searches due to period restriction. If Date Range is not needed, then Saved Search 2 will suffice (just remove criteria i).

1. Customers with NO transactions within the time period (mm/yyyy) = Customers with Status "Closed-Won" only
a. List > Search > Saved Searches > New > Customers
b. On Criteria subtab > Standard sublist:
i. Status = Closed-Won
c. On Results subtab > Columns sublist: Add columns as needed, for example:
i. Name
ii. Email
d.Save & Run
 
2. Customers with transactions within the Date Range (mm/yyyy)
a. List > Search > Saved Searches > New > Customers
b. On Criteria subtab > Standard sublist:
i. Transaction Fields…> Date Created is within custom (From: mm/dd/yyyy and To: mm/dd/yyyy)
ii. Transaction Fields…> Main Line = True
iii. Status = Closed-Won
c. On Results subtab > Column sublist: Add columns as needed, for example: i. Name | Summary Type = Group
ii. Formula(Currency)  
Summary Type = Sum 
Formula = case when count(distinct({transaction.number})) is NULL then 0 Else sum(Distinct({transaction.totalamount})) end 
Custom Label = Total Transaction Amount
d. Save & Run

Saved Search 1 returned results: All customer in status "Closed-Won".
Saved Search 2 returned results: All customer in status "Closed-Won" with transaction total amount realized within certain date range.
User can use both search results and manually compare to view differences.

No comments:

Post a Comment