Select any of the links below to move directly to the query.
Overview
Maintenance Queries
Profile with missing email addresses:
Invalid emails (missing @ or . ):
Incomplete Address Information:
Profiles with no Marketing Codes:
Res Card / Reservation Queries
Past Clients Who Have Not Purchased In The Last 2 Years:
Top XXX Clients Based on Past Purchases:
Clients Who Bought > $10,000 in Travel Last Year:
Marketing Queries
Clients We Can Market To Via Email:
Clients With Complete Address Information:
Client With No Marketing In The Last 3 Weeks:
Profile Queries
Passports Expiring Within the Quarter:
Saving Queries
Using Date Formulas in Queries
Overview
With all the available filters in Level 1, 2 and 3 queries, ClientBase Windows allows you to view your database inside out, upside down or in just about any subset you desire. Some people call this “mining your data”. We call it “querying” your database. In the end the objective is to better understand your customer base and maximize your marketing and service efforts with your clients. So in our opinion, the more you query your database the more successful you will be.
To help you with those queries that you want to run often, we suggest you save the query filters after setting them up. This is called saving a query to a folder. One click on a saved query folder, and the query filters are completed automatically. What a time saver!
With this in mind, we thought you might be interested in seeing how other agencies have gotten creative with their saved queries and have listed the most creative below.
Contact support for additional help with setting up, running and saving queries. Support@TresTechnologies.com.
Look for BEST PRACTICE tips along the way!
Maintenance Queries
Profile with missing email addresses:
Level 3 Query Summarize:
*** Profile Level 3 Filter Number 1
(Profile Type [Leisure])
AND (Profile Status Is Equal to Active)
AND (Communication Filter Type: Does Have Communication Records with the following criteria)
(Communication Type Is Equal to E-mail)
AND (Communication Entry Is BLANK)
BEST PRACTICE: For clients that do not have an email address, create a Marketing Code called ‘No Email’. When you create the query for missing email addresses, add another filter. Select the Marketing Codes tab and double click the ‘No Email’ code. This will exclude clients that do not have an email address.
Level 3 Query Summarize:
*** General Info Tab
Profile Type is Equal to Leisure
Profile Status Is Equal to Active
*** Communication Tab
Communication Filter Type: Does Have Communication Records with the following criteria
Communication Type Is Equal to E-mail
Communication Entry Is BLANK
*** Marketing Tab
Marketing: Promotions Is NOT Checked [No Email Address]
Invalid emails (missing @ or . ):
This query requires 2 filters.
Level 3 Query Summarize:
Requires 2 filters
Level 3 Query Summarize:
*** Profile Level 3 Filter Number 1
(Profile Type [Leisure])
AND (Profile Status Is Equal to Active)
AND (Communication Filter Type: Does Have Communication Records with the following criteria)
(Communication Type Is Equal to E-mail)
AND (Communication Entry NOT Contains "@")
AND (Marketing Permission Is Equal to Y)
*** Profile Level 3 Filter Number 2______________
(Profile Type [Leisure])
AND (Profile Status Is Equal to Active)
AND (Communication Filter Type: Does Have Communication Records with the following criteria) (Communication Type Is Equal to E-mail)
AND (Communication Entry NOT Contains ".")
AND (Marketing Permission Is Equal to Y)
BEST PRACTICE: Create the first filter and use ‘Duplicate Filter’ to create the second filter replacing the @ sign with a period ( . ).
Incomplete Address Information:
This query requires 4 filters.
- Once you create the first filter above where the Address Line 1 ‘Is BLANK’, Select Add Filter.
- Select Profile type as Leisure and City ‘Is Blank’. Click OK.
- Create two more filters selecting State ‘Is Blank’ and then Zip Code ‘Is Blank’.
- Your Summarize will look like the following:
Level 3 Query Summarize:
*** Profile Level 3 Filter Number 1 _____________________
(Profile Type [Leisure])
AND (Address Line 1 Is BLANK)
AND (Profile Status Is Equal to Active)
*** Profile Level 3 Filter Number 2 _____________________
(Profile Type [Leisure])
AND (City Is BLANK)
AND (Profile Status Is Equal to Active)
*** Profile Level 3 Filter Number 3 _____________________
(Profile Type [Leisure])
AND (State Is BLANK)
AND (Profile Status Is Equal to Active)
*** Profile Level 3 Filter Number 4 _____________________
(Profile Type [Leisure])
AND (Zip Code Is BLANK)
AND (Profile Status Is Equal to Active
BEST PRACTICE: For Level 3 queries, select the Summarize button to view the details of your query. For Level 2 queries, the details are displayed below the tab.
Profiles with no Marketing Codes:
Right Click on every Marketing Category in order to exclude it. By excluding all, the result will be profiles that do not have any marketing codes selected.
BEST PRACTICE: Depending on the size of your database, you may want to run this Query at night.
Res Card / Reservation Queries
Profiles with no Res Cards:
Level 3 Query Summarize:
*** Profile Level 3 Filter Number 1
(Profile Type [Leisure])
AND (Profile Status Is Equal to Active)
AND (Res Cards Filter Type: Does NOT Have Any Res Card Records)
BEST PRACTICE: Run Queries by Primary Agent and create a Reminder for the Agent to follow-up.
Past Clients Who Have Not Purchased In The Last 2 Years:
Level 3 Query Summarize:
*** Profile Level 3 Filter Number 1
(Profile Type [Leisure])
AND (Profile Status Is Equal to Active)
AND (Res Cards Filter Type: Does NOT Have Any Res Card Records with the following criteria)
(Res Card Create Date Is From "=0,0,-2" [1/21/2014] to "=" [1/21/2016])
AND (Travel History Filter Type: Does Have Travel History Records with the following criteria)
(Invoice Issue Date Is From "=0,0,-5" [1/21/2011] to "=0,0,-3" [1/21/2013])
Top XXX Clients Based on Past Purchases:
Level 2 Query Summarize:
*** General Info
Profile Type Is Equal to [Leisure])
Profile Status Is Equal to Active
*** Travel History
Top X Client Sales (X=100)
Clients Who Bought > $10,000 in Travel Last Year:
Level 2 Query Summarize:
*** General Info
Profile Type Is Equal to [Leisure])
Profile Status Is Equal to Active
*** Travel History ____________
Client Totals:Total Sales Between 10,000.00 And 999,999.00 FOR ONLY (Invoice Issue Dates Between "=0,0,-1" [1/7/2015] And "=" [1/7/2016] )
Bookings This Week:
Level 2 Query Summarize:
*** General Info
Profile Type Is Equal to [Leisure])
Profile Status Is Equal to Active
*** Res Cards ____________
... Res Card Filters . . . . . . . . . . . . . . . . . . . . . . . .
Res Card Status Is Equal to Active
... Reservation Filters . . . . . . . . . . . . . . . . . . . . . . . .
Booking Status Is Equal to Confirmed
Date Reserved Between "=-7,0,0" [1/14/2016] And "=" [1/21/2016]
Past Cruisers:
Level 2 Query Summarize:
*** General Info
Profile Type Is Equal to [Leisure])
Profile Status Is Equal to Active
*** Travel History ____________
Travel Categories Is Equal to [Cruise]
Travel Categories Is Equal to [Cruise]
Res Cards with No Insurance:
Level 3 Query Summarize:
*** Profile Level 3 Filter Number 1
(Profile Type [Leisure])
AND (Profile Status Is Equal to Active)
AND (Res Cards Filter Type: Does Have Any Res Card Records with the following criteria)
(Res Card Trip Start Date Is From "XX/XX/XXXX" to)
AND (Res Card Status [Active])
AND (Travel History Type: Does NOT Have Any Travel History Records with the following criteria) (Booking Travel Category [Insurance])
Marketing Queries
Promo Open, Sent and Clicked:
Level 2 Query Summarize:
*** General Info
Profile Type Is Equal to [Leisure])
Profile Status Is Equal to Active
*** Activities _________________
Mailers
Activity Date Between "=-7,0,0" [1/19/2016] And "=" [1/26/2016]
Subject Starts With "Promo Open"
Replace the Subject field to Promo Sent to find email promotions that have been sent.
Replace the Subject field to Promo Click to find email promotions that have been clicked through within the email.
BEST PRACTICE: Save Queries and run them weekly or monthly.
Clients We Can Market To Via Email:
Level 3 Query Summarize:
*** Profile Level 3 Filter Number 1
(Profile Type [Leisure])
AND (Profile Status Is Equal to Active)
AND (Communication Filter Type: Does Have
Communication Records with the following criteria)
(Communication Type Is Equal to E-mail)
AND (Marketing Permission Is Equal to Y)
Clients With Complete Address Information:
Level 3 Query Summarize:
*** Profile Level 3 Filter Number 1
(Profile Type [Leisure])
AND (Address Line 1 Is NOT BLANK)
AND (Zip Code Is NOT BLANK)
AND (City Is NOT BLANK)
AND (State Is NOT BLANK)
AND (Profile Status Is Equal to Active)
Client With No Marketing In The Last 3 Weeks:
Level 3 Query Summarize:
*** Profile Level 3 Filter Number 1
(Profile Type [Leisure])
AND (Profile Status Is Equal to Active)
AND (Activity Filter Type: Does NOT Have Any Activity Records with the following criteria)
(Mailers (All))
AND (Activity Date Is From "=-21,0,0" [1/5/2016] to "=" [1/26/2016])
Profile Queries
New Customers Last 7 Days:
Level 2 Query Summarize:
*** General Info _____________
Profile Type Is Equal to [Leisure/Corporate]
Profile Create Date Between "=-7,0,0" [1/19/2016] And "=" [1/26/2016] Profile Status Is Equal to Active
Passports Expiring Within the Quarter:
Level 2 Query Summarize:
*** General Info
Profile Type Is Equal to [Leisure])
Profile Status Is Equal to Active
*** Traveler/Contact ___________
Expiration Between "=" [1/26/16]
And "=0,-3,0" [4/26/2016]
Saving Queries
- Using Level 1, 2, or 3 query, enter your query filters and run your query. Use date formulas as described below if applicable.
- Customize the columns used in the query by selecting the Columns button on the right.
- To change a column, move the highlighted column over to the other side by using the > or < By using the shift key and mouse, select as many consecutive entries as you want to move and use the > or < arrows. By using the control key and your mouse, you can select non-consecutive entries to move. Use the up and down arrows to order the selections.
- To sort up to 3 columns in ascending or descending order, click Sort by Columns. You can also arrange, size, and sort the columns in the Query on the result screen by inserting the mouse between the column headers (see a bi-directional arrow), holding down the left-mouse button, and pulling the columns to the new size. Pull any column to a new location by using the mouse. To sort, hold down Shift and click a column header one time for ascending order, and once again for descending order.
- When satisfied with the query results, go to File > Save Query As.
- From the drop-down Level menu, choose whether you want the query to be Personal (for your own use only), or Global (for the use of everyone in the agency).
- Name the Query
- Click OK. The new query folder is lcated on the left hand side of the Manager screen.
NOTE: ClientBase saves the filters and results layout, not the query results. The next time you use the saved query, any new data that has been added since it was created is included in the new query.
Using Date Formulas in Queries
Since queries can be saved and retrieved for frequent use, the date fields in all queries allow for date formulas. This means any field that has a date, can also contain a formula for that date. The date formula function allows you to create a query without identifying a specific month, day, day of the wee, and year based upon the date the report is processed.
Remember: Make sure system date and time are correct.
Benefit: When entering a date formula, the system converts it to the intended time period to fun queries and/or save them for future use without the need to modify dates.
Example: The following table contain sample formulas for specifying dates. Mix and match the absolute and relative parameters in the function. The first number specified the day of the month, the second number specified the month and the third number specified the year. So if you only have the first number filled in, this means you intend this report to reoccur on this day every month, in every year.