Top 10 Creative "Saved Queries"

With all the available filters in Level 1, 2 and 3 queries, ClientBase Plus 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 listed the "Top 10" below. This newsletter's focus is on Profile Manager and we'll continue with sample saved queries for the Activity and Res Card Managers in future newsletters. Enjoy!

"Top 10" Profile Manager Saved Queries

#1. "New Customers Last 7 Days"
Level 2 Query
*** General Info ____________________________________________
Profile Type Is Equal to [Leisure/Corporate]
Profile Create Date Between "=-7,0,0" And "="
Profile Status Is Equal to Active
*** Marketing ______________________________________________
Client Status
New Customer

#2. "Passports Are Expiring Within the Quarter"
Level 2 Query
*** General Info ____________________________________________
Profile Status Is Equal to Active
*** Family/Employee _________________________________________
Passport Expiration Between "=" And "=0,+3,0"

#3. "Clients we Can Market to Via E-mail"
*** Profile Level 3 Filter Number 1 _____________________________
(Profile Type [Leisure,Corporate])
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)

#4. "Clients with Completed Address Info"
*** 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)

#5. "Past Cruisers"
Level 2 Query
*** General Info ___________________________________________
Profile Type Is Equal to [Leisure/Corporate]
Profile Status Is Equal to Active
*** Travel History _________________________________________
Travel Categories Is Equal to [Cruise]

6. "Clients that have Booked This Week"
Level 2 Query
*** General Info ______________________________________________
Profile Type Is Equal to [Leisure]
Profile Status Is Equal to Active
*** ResCards ______________________________________________
... ResCard Filters . . . . . . . . . . . . . . . . . . . . . . . .
ResCard Status Is Equal to Active
... Reservation Filters . . . . . . . . . . . . . . . . . . . . . . . .
Booking Status Is Equal to Confirmed
Date Reserved Between "=-7,0,0" [4/12/2005] And "=" [4/19/2005]

#7. "Customers with No Bookings 1 Year
*** Profile Level 3 Filter Number 1 ___________________________
(Profile Type [Leisure,Corporate])
AND (Profile Status Is Equal to Active)
AND (Travel History Type: Does NOT Have Any Travel History Records with the following criteria)
(Invoice Issue Date Is From "=0,0,-1" [4/19/2004] to "=" [4/19/2005])

#8. "Top 200 Clients Based on Past Purchases"
Level 2 Query
*** General Info ______________________________________________
Profile Type Is Equal to [Leisure/Corporate]
Profile Status Is Equal to Active
*** Travel History ______________________________________________
Top X Client Sales (X=200)

#9. "Bought > $10,000 in Travel Last Year"
Level 2 Query
*** General Info ______________________________________________
Profile Type Is Equal to [Leisure/Corporate]
Profile Status Is Equal to Active
*** Travel History ______________________________________________
Client Sales:Sales Total Between 10,000.00 And 999,999.00 FOR ONLY (Invoice Issue Dates Between "=0,0,-1" And "=" )

#10. "Booked in Past but no Trips yet for Next 6 mos."
*** Profile Level 3 Filter Number 1 _____________________
(Profile Type [Leisure])
AND (Profile Status Is Equal to Active)
AND (ResCards Filter Type: Does NOT Have Any ResCard Records with the following criteria)
(ResCard Trip Start Date Is From "=" to "=0,+6,0")
AND (Travel History Filter Type: Does Have Travel History Records)
Bonus: "Clients Sent no Marketing in Last 3 Weeks"
*** 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" [3/30/2005] to "=" [4/20/2005])

Steps to Saving a Query as a Folder

Just to refresh your memory, here are steps for saving a query as a folder:

1) Using Level 1, 2, or 3 query, enter your query filters and run your query. (Use date formulas as described below if applicable.)

2) To customize the columns used in the query, click the Columns button at the top right corner next to the Count button.
To change a column, move the highlighted column over to the other side by using the > or < arrow. 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 U and D 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.

3) When satisfied with the query results, go to File | Save Query As. This screen appears:

4) 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).

5) Name the query.

6) Click OK. The new query folder is located 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 and year in the From and To date fields. Instead, the query is processed with a formula for the month, day, day of the week, 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 run queries and/or save them for future use without the need to modify dates.

Example: The following tables contain sample formulas for specifying dates. Mix and match the absolute and relative parameters in the function. The first number specifies the day of the month, the second number specifies the month, and the third number specifies 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.

DATE FORMULAS:

If You Want This Enter This:	Enter This
1st day of this month:	=1,0,0
Last day of this month:	=31,0,0
1st day of this year:	=1,1,0
Last day of this year:	=31,12,0
Seven days from now:	=+7,0,0
Last Monday (will show today if today is Monday)	=Mon
A week from last Monday	=Mon-1
Next Monday	=Mon+1
Three months from now	=0,+3,0
Six months ago:	=0,-6,0
One year from now	=0,0,+1