Difference between revisions of "Report: Client Query Wizard"

From Agency Systems Wiki
Jump to navigation Jump to search
(→‎Mailing Labels: added mailing label link)
 
(16 intermediate revisions by 2 users not shown)
Line 1: Line 1:
[[Category:Reports]][[Category:Client Query Wizard]][[Category:Memo Writer]]
+
[[Category:Reports]][[Category:Client Query Wizard]][[Category:Memowriter]][[Category:Mailing Labels]]
 
<table class="infobox" cellspacing="5" style="background-color: #FFFFFF; width:27em; text-align:left; border-spacing: 2px; line-height:1.5em;">
 
<table class="infobox" cellspacing="5" style="background-color: #FFFFFF; width:27em; text-align:left; border-spacing: 2px; line-height:1.5em;">
 
<tr style="background-color: #bdbdbd; text-align: center; font-weight:bold;">
 
<tr style="background-color: #bdbdbd; text-align: center; font-weight:bold;">
Line 22: Line 22:
 
<li>[[:Category:Reports|Reports]]</li>
 
<li>[[:Category:Reports|Reports]]</li>
 
<li>[[:Category:Client Query Wizard|Client Query Wizard]]</li>
 
<li>[[:Category:Client Query Wizard|Client Query Wizard]]</li>
<li>[[:Category:Memo Writer|Memo Writer]]</li>
+
<li>[[:Category:Memowriter|Memowriter]]</li>
 +
<li>[[:Category:Mailing Labels|Mailing Labels]]</li>
 
</ul>
 
</ul>
 
</td>
 
</td>
Line 30: Line 31:
  
 
Any of the five report options can output to a number of printable reports including mailing labels, a spreadsheet file for use in Microsoft Excel, or output directly to a [[Memowriter]] document for mass mailing.  
 
Any of the five report options can output to a number of printable reports including mailing labels, a spreadsheet file for use in Microsoft Excel, or output directly to a [[Memowriter]] document for mass mailing.  
 +
== Report Examples ==
 +
[[Report: Client Query Wizard: Birthday List|Birthday List]]
 +
 +
[[Report: Client Query Wizard: Clients Age 65 Or Older By December 2015|Clients Age 65 or Older by December 2015]]
 +
 +
[[Report: Client Query Wizard: Export All Active Client Email Addresses Excluding Prospects|Export All Active Client Email Addresses Excluding Prospects]]
 +
 +
[[Report: Client Query Wizard: Inactive Clients|Inactive Clients]]
 +
 +
[[Report: Client Query Wizard: Print Mailing Labels For All Active Clients Excluding Prospects|Print Mailing Labels For All Active Clients Excluding Prospects]]
  
 
== Access and Window Options ==
 
== Access and Window Options ==
The Client Query Wizard can be opened by selecting '''Marketing -> Query Wizard'''.  
+
The Client Query Wizard can be opened by selecting '''Client -> Client Query'''.  
  
 
Access to the Client Query Wizard is controlled through the [[Security System| security system]] '''Marketing -> Client Query -> View''' setting.
 
Access to the Client Query Wizard is controlled through the [[Security System| security system]] '''Marketing -> Client Query -> View''' setting.
  
 
[[File:clntqrywiz-default.png]]
 
[[File:clntqrywiz-default.png]]
 +
 +
== Report Types ==
 +
For step-by-step instructions running specific queries, [[#Report Examples|see the report examples section above]].
  
 
[[#General client query|General client query]]
 
[[#General client query|General client query]]
Line 47: Line 61:
  
 
[[#Clients with policies of one class but not another|Clients with policies of one class but not another]]
 
[[#Clients with policies of one class but not another|Clients with policies of one class but not another]]
== Report Types ==
 
For step-by-step instructions running specific queries, [[#Report Examples|see the report examples section below]].
 
  
=== Filter by Client Details ===
+
== Filter by Client Details ==
 
Each of the client query wizard report options, [[#Filter by Policy Details|including the queries which include policy filters]], allow the operator to add the additional client filters below.   
 
Each of the client query wizard report options, [[#Filter by Policy Details|including the queries which include policy filters]], allow the operator to add the additional client filters below.   
  
==== General client query ====
+
=== General client query ===
 
The general client query will output a list of clients who meet the selected criteria.  To include policy criteria in the query, use [[#Filter by Policy Details|one of the other report options]].
 
The general client query will output a list of clients who meet the selected criteria.  To include policy criteria in the query, use [[#Filter by Policy Details|one of the other report options]].
  
#Select '''Marketing -> Client Query Wizards -> General Client Query'''.  
+
#Select '''Client -> Client Query'''.  
 
#To limit the list of client by number, enter a starting and ending client number.  
 
#To limit the list of client by number, enter a starting and ending client number.  
 +
#Select '''Additional Filters'''.
 
#:[[File:clntqrywiz-gen-1.png]]
 
#:[[File:clntqrywiz-gen-1.png]]
#Select '''Additional Filters'''.
 
 
#Add any desired filters for client alpha, client producer, client sub-producer, client CSR, client type or client/prospect status.  
 
#Add any desired filters for client alpha, client producer, client sub-producer, client CSR, client type or client/prospect status.  
 +
#:''(Each of the client query wizard report options, [[#Filter by Policy Details|including the queries which include policy filters]], allow the operator to add the additional client filters below.)''
 
#:[[File:clntqrywiz-gen-2-addl-gen.png]]
 
#:[[File:clntqrywiz-gen-2-addl-gen.png]]
 
#Select the '''Contact Age/Birthday''' tab to add filters for clients who have contacts with a certain birthday, or that are a certain age.  
 
#Select the '''Contact Age/Birthday''' tab to add filters for clients who have contacts with a certain birthday, or that are a certain age.  
Line 72: Line 85:
 
[[#Output Format|Select the sort method and output format]].
 
[[#Output Format|Select the sort method and output format]].
  
=== Filter by Policy Details ===
+
== Filter by Policy Details ==
 
There are four client queries will which include policy detail filters.  Each of the queries will output a list of clients who meet the selected criteria.  
 
There are four client queries will which include policy detail filters.  Each of the queries will output a list of clients who meet the selected criteria.  
  
 
After selecting the policy detail filters, the option to [[#Filter by Client Details|add additional client-level filters]] will be available.   
 
After selecting the policy detail filters, the option to [[#Filter by Client Details|add additional client-level filters]] will be available.   
====Clients with a policy====
+
===Clients with a policy===
#Select '''Marketing -> Client Query Wizard'''.
+
#Select '''Client -> Client Query'''.
 
#Select '''Clients With A Policy -> Next'''.
 
#Select '''Clients With A Policy -> Next'''.
 
#Enter the desired starting and ending active policy date range.  The date range does not look at the effective date only, it finds any policy active/in effect during the period specified.  Select '''Next'''.
 
#Enter the desired starting and ending active policy date range.  The date range does not look at the effective date only, it finds any policy active/in effect during the period specified.  Select '''Next'''.
Line 87: Line 100:
 
#The option to [[#Filter by Client Details|add additional client-level filters]] will now be available.
 
#The option to [[#Filter by Client Details|add additional client-level filters]] will now be available.
  
====Policy effective/expiration date range====
+
===Policy effective/expiration date range===
#Select '''Marketing -> Client Query Wizard'''.
+
#Select '''Client -> Client Query'''.
 
#Select '''Policy effective/expiration date range'''.
 
#Select '''Policy effective/expiration date range'''.
 
#Select either the policy transaction effective dates, policy transaction expiration dates, or both. Select '''Next'''
 
#Select either the policy transaction effective dates, policy transaction expiration dates, or both. Select '''Next'''
Line 98: Line 111:
 
#The option to [[#Filter by Client Details|add additional client-level filters]] will now be available.
 
#The option to [[#Filter by Client Details|add additional client-level filters]] will now be available.
  
====Clients with one line but not another====
+
===Clients with one line but not another===
 +
#Select '''Client -> Client Query'''.
 +
#Select '''Client with one line but not another'''.  Select '''Next'''.
 +
#Select the risk category the client has, and a risk category the client does not have. ''(The '''[[Class Profile]]''' specifies the risk category for a policy)'' Select '''Next'''.
 +
#:[[File:Clntqrywiz-oneline-riskcat.png]]
 +
#Enter the policy search criteria for the '''risk category the client has''', by effective or expiration date. Select '''Next'''.
 +
#:[[File:Clntqrywiz-oneline-effexpdates.png]]
 +
#Optionally, enter the policy search criteria for the '''risk category the client does not have'''', by effective or expiration date.  Select '''Next'''.
 +
#:[[File:Clntqrywiz-oneline-missing-effexpdates.png]]
 +
#The option to [[#Filter by Client Details|add additional client-level filters]] will now be available.
  
====Clients with policies of one class but not another====
+
===Clients with policies of one class but not another===
 +
Clients with policies of one class is very similar to [[#Clients with one line but not another|clients with one line but not another]], but is limited to a single class code, instead of an entire risk category. ''(The '''[[Class Profile]]''' specifies the risk category for a policy)''
 +
#Select '''Client -> Client Query'''.
 +
#Select '''Clients with policies of one class but not another'''.  Select '''Next'''.
 +
#Select the '''Class Code''' the client has, and a '''Class Code''' the client does not have. Select '''Next'''.
 +
#:[[File:Clntqrywiz-oneclass-enterclass.png]]
 +
#Enter the policy search criteria for the '''class code the client has''', by effective or expiration date. Select '''Next'''.
 +
#:[[File:Clntqrywiz-oneline-effexpdates.png]]
 +
#Optionally, enter the policy search criteria for the '''class code the client does not have'''', by effective or expiration date.  Select '''Next'''.
 +
#:[[File:Clntqrywiz-oneline-missing-effexpdates.png]]
 +
#The option to [[#Filter by Client Details|add additional client-level filters]] will now be available.
  
=== Output Format ===
+
== Output Format ==
The sort order and output format options are the same for each of the client query wizards.
+
The sort order and output format options '''are the same''' for each of the client query wizards.
  
 
After [[#Report Types|selecting the options for the desired query]], the sort options are displayed.  (''If the query result output will be a spreadsheet, the sort method does not apply.'')
 
After [[#Report Types|selecting the options for the desired query]], the sort options are displayed.  (''If the query result output will be a spreadsheet, the sort method does not apply.'')
Line 110: Line 142:
 
Select '''Next''' to select the desired output method.  
 
Select '''Next''' to select the desired output method.  
 
:[[File:clntqrywiz-gen-4-outputmethod.png]]
 
:[[File:clntqrywiz-gen-4-outputmethod.png]]
==== Summary ====
+
=== Summary ===
 
The summary output format provides a printable report that can be exported as a .PDF file  The report includes a single result per client sorted in the selected order.   
 
The summary output format provides a printable report that can be exported as a .PDF file  The report includes a single result per client sorted in the selected order.   
  
 
For each client, the following values are included: Client Number, Client Name, Phone Number, Secondary Phone Number, Producer code, and CSR code.
 
For each client, the following values are included: Client Number, Client Name, Phone Number, Secondary Phone Number, Producer code, and CSR code.
  
==== Detail ====
+
=== Detail ===
 
The detail output format provides a printable report that can be exported as a .PDF file.  The report includes a single result per client sorted in the selected order.
 
The detail output format provides a printable report that can be exported as a .PDF file.  The report includes a single result per client sorted in the selected order.
  
Line 126: Line 158:
 
The contact name will either be the initial contact, or the first contact/driver when the '''Birthday''' filter is used.
 
The contact name will either be the initial contact, or the first contact/driver when the '''Birthday''' filter is used.
  
==== Mailing Labels ====
+
=== Mailing Labels ===
The [[:Category:Mailing Labels|mailing label]] output format provides a printable report that can be exported as a .PDF file.  The report includes a single result per client sorted in the selected order.
+
The [[:Category:Mailing Labels|mailing label]] output format provides a printable report which includes a single result per client sorted in the selected order. ''(The mailing labels display in the '''Avery Standard 5160''' format with 10 rows and 3 columns.)''
  
 
For each client, the following values are included: Client Name and Street Address.  (''The name and address will be replaced with the first contact/driver when the '''Birthday''' filter is used'').
 
For each client, the following values are included: Client Name and Street Address.  (''The name and address will be replaced with the first contact/driver when the '''Birthday''' filter is used'').
Line 135: Line 167:
 
[[File:Clntqrywiz-gen-5-mailinglabels.png]]
 
[[File:Clntqrywiz-gen-5-mailinglabels.png]]
  
==== Form Letter ====
+
=== Form Letter ===
 
The Form Letter output provides a mechanism to output the list of clients directly to a form letter template created with [[Memowriter]] for mass mailing.   
 
The Form Letter output provides a mechanism to output the list of clients directly to a form letter template created with [[Memowriter]] for mass mailing.   
  
During the process, the client information is merged into the letter based on the user-defined merge codes.  If the form letter includes policy detail merge codes, one of the [[#Filter By Policy Details|four policy detail client queries]] should be used.  
+
During the process, the client information is merged into the letter based on the user-defined merge codes.  If using one of the policy queries, [[#Filter By Policy Details|four policy detail client queries]], note that only '''client information''' will merge to the letter.  
  
 
The Client Query Wizard will prompt the user to select the form letter.
 
The Client Query Wizard will prompt the user to select the form letter.
Line 146: Line 178:
 
After the letter is selected, the system will automatically merge and save a copy of the form letter to each matching client, and output the form letter to the printer.
 
After the letter is selected, the system will automatically merge and save a copy of the form letter to each matching client, and output the form letter to the printer.
  
==== AL3 Setup ====
+
=== AL3 Setup ===
 
The AL3 setup output format exports a custom file for legacy rating vendors.
 
The AL3 setup output format exports a custom file for legacy rating vendors.
  
==== Folder Labels ====
+
=== Folder Labels ===
 
The folder labels output format provides a printable report that can be exported as a .PDF file.  The report includes a single result per client sorted in the selected order. (''The folder labels will display in the '''Avery Standard 5160''' format with 10 rows and 3 columns.'')
 
The folder labels output format provides a printable report that can be exported as a .PDF file.  The report includes a single result per client sorted in the selected order. (''The folder labels will display in the '''Avery Standard 5160''' format with 10 rows and 3 columns.'')
  
Line 158: Line 190:
 
[[File:Clntqrywiz-gen-5-mailinglabels.png]]
 
[[File:Clntqrywiz-gen-5-mailinglabels.png]]
  
==== Comma Separated File (CSV) Spreadsheet ====
+
=== Comma Separated File (CSV) Spreadsheet ===
 
The CSV output format will export a file which can be opened with Microsoft Excel, or other third-party spreadsheet applications.  The output contains client name, client address, contact name, contact address, email addresses and much more.  
 
The CSV output format will export a file which can be opened with Microsoft Excel, or other third-party spreadsheet applications.  The output contains client name, client address, contact name, contact address, email addresses and much more.  
  
 
;Example output file converted to Microsoft Excel Spreadsheet.
 
;Example output file converted to Microsoft Excel Spreadsheet.
:[[File:Clientquery-export-withpolicy.xls]]
+
:[[Media:Clientquery-export-withpolicy.xls|Client Query Export With Policy.xls]]
  
 
The CSV output normally includes every contact saved to the clients who match the query filters.  If a policy detail filter is used, the matching policy number will also be included.  
 
The CSV output normally includes every contact saved to the clients who match the query filters.  If a policy detail filter is used, the matching policy number will also be included.  
Line 169: Line 201:
  
 
If a contact or driver birthday filter is used, the CSV output will '''only include the clients/drivers with a matching date''', not all contacts for the client.
 
If a contact or driver birthday filter is used, the CSV output will '''only include the clients/drivers with a matching date''', not all contacts for the client.
 
== Report Examples ==
 
[[Report: Client Query Wizard: Export All Active Client Email Addresses Excluding Prospects|Export All Active Client Email Addresses Excluding Prospects]]
 

Latest revision as of 14:45, 2 February 2021

Related Pages
Related Categories

The Client Query Wizard offers a host of user-defined filters to find a list of clients which meet either a broad or narrow set of criteria. The list of clients can be based solely on client profile details such as address, client type, or birthday, or can also include powerful policy filters such as type of business, or policy effective/expiration dates.

Any of the five report options can output to a number of printable reports including mailing labels, a spreadsheet file for use in Microsoft Excel, or output directly to a Memowriter document for mass mailing.

Report Examples

Birthday List

Clients Age 65 or Older by December 2015

Export All Active Client Email Addresses Excluding Prospects

Inactive Clients

Print Mailing Labels For All Active Clients Excluding Prospects

Access and Window Options

The Client Query Wizard can be opened by selecting Client -> Client Query.

Access to the Client Query Wizard is controlled through the security system Marketing -> Client Query -> View setting.

Clntqrywiz-default.png

Report Types

For step-by-step instructions running specific queries, see the report examples section above.

General client query

Clients with a policy

Policy effective/expiration date range

Clients with one line but not another

Clients with policies of one class but not another

Filter by Client Details

Each of the client query wizard report options, including the queries which include policy filters, allow the operator to add the additional client filters below.

General client query

The general client query will output a list of clients who meet the selected criteria. To include policy criteria in the query, use one of the other report options.

  1. Select Client -> Client Query.
  2. To limit the list of client by number, enter a starting and ending client number.
  3. Select Additional Filters.
    Clntqrywiz-gen-1.png
  4. Add any desired filters for client alpha, client producer, client sub-producer, client CSR, client type or client/prospect status.
    (Each of the client query wizard report options, including the queries which include policy filters, allow the operator to add the additional client filters below.)
    Clntqrywiz-gen-2-addl-gen.png
  5. Select the Contact Age/Birthday tab to add filters for clients who have contacts with a certain birthday, or that are a certain age.
    Clntqrywiz-gen-2-addl-age.png
  6. Select the User Codes tab to include or exclude clients with specific user code tags.
    Clntqrywiz-gen-2-addl-usercodes.png
  7. Select the Address tab to add a filter by mailing or physical client address.
    Clntqrywiz-gen-2-addl-address.png
  8. Select Ok then Next.

Select the sort method and output format.

Filter by Policy Details

There are four client queries will which include policy detail filters. Each of the queries will output a list of clients who meet the selected criteria.

After selecting the policy detail filters, the option to add additional client-level filters will be available.

Clients with a policy

  1. Select Client -> Client Query.
  2. Select Clients With A Policy -> Next.
  3. Enter the desired starting and ending active policy date range. The date range does not look at the effective date only, it finds any policy active/in effect during the period specified. Select Next.
    Clntqrywiz-wpol-dates.png
  4. The policy matches can be limited to a risk type, or a specific Company, Transaction, Department, Class or Producer. Select Next.
    Clntqrywiz-wpol-risktypes.png
  5. By default, all Policy Company Status types are included, in addition to quote transactions. To remove an item, deselect the check box. Select Next.
    Clntqrywiz-wpol-polstattype.png
  6. The option to add additional client-level filters will now be available.

Policy effective/expiration date range

  1. Select Client -> Client Query.
  2. Select Policy effective/expiration date range.
  3. Select either the policy transaction effective dates, policy transaction expiration dates, or both. Select Next
    Clntqrywiz-wpol-effexpdates.png
  4. The policy matches can be limited to a risk type, or a specific Company, Transaction, Department, Class or Producer. Select Next.
    Clntqrywiz-wpol-risktypes.png
  5. By default, all Policy Company Status types are included, in addition to quote transactions. To remove an item, deselect the check box. Select Next.
    Clntqrywiz-wpol-polstattype.png
  6. The option to add additional client-level filters will now be available.

Clients with one line but not another

  1. Select Client -> Client Query.
  2. Select Client with one line but not another. Select Next.
  3. Select the risk category the client has, and a risk category the client does not have. (The Class Profile specifies the risk category for a policy) Select Next.
    Clntqrywiz-oneline-riskcat.png
  4. Enter the policy search criteria for the risk category the client has, by effective or expiration date. Select Next.
    Clntqrywiz-oneline-effexpdates.png
  5. Optionally, enter the policy search criteria for the risk category the client does not have', by effective or expiration date. Select Next.
    Clntqrywiz-oneline-missing-effexpdates.png
  6. The option to add additional client-level filters will now be available.

Clients with policies of one class but not another

Clients with policies of one class is very similar to clients with one line but not another, but is limited to a single class code, instead of an entire risk category. (The Class Profile specifies the risk category for a policy)

  1. Select Client -> Client Query.
  2. Select Clients with policies of one class but not another. Select Next.
  3. Select the Class Code the client has, and a Class Code the client does not have. Select Next.
    Clntqrywiz-oneclass-enterclass.png
  4. Enter the policy search criteria for the class code the client has, by effective or expiration date. Select Next.
    Clntqrywiz-oneline-effexpdates.png
  5. Optionally, enter the policy search criteria for the class code the client does not have', by effective or expiration date. Select Next.
    Clntqrywiz-oneline-missing-effexpdates.png
  6. The option to add additional client-level filters will now be available.

Output Format

The sort order and output format options are the same for each of the client query wizards.

After selecting the options for the desired query, the sort options are displayed. (If the query result output will be a spreadsheet, the sort method does not apply.)

Clntqrywiz-gen-3-sort.png

Select Next to select the desired output method.

Clntqrywiz-gen-4-outputmethod.png

Summary

The summary output format provides a printable report that can be exported as a .PDF file The report includes a single result per client sorted in the selected order.

For each client, the following values are included: Client Number, Client Name, Phone Number, Secondary Phone Number, Producer code, and CSR code.

Detail

The detail output format provides a printable report that can be exported as a .PDF file. The report includes a single result per client sorted in the selected order.

For each client, the following values are included: Client Number, Client Name, Phone Number, Secondary Phone Number, Producer code, CSR code, Street Address, User Codes, and a Contact Name.

Clntqrywiz-output-detailwpol.png

If a policy detail filter is used, the policy number and policy company code are also included.

The contact name will either be the initial contact, or the first contact/driver when the Birthday filter is used.

Mailing Labels

The mailing label output format provides a printable report which includes a single result per client sorted in the selected order. (The mailing labels display in the Avery Standard 5160 format with 10 rows and 3 columns.)

For each client, the following values are included: Client Name and Street Address. (The name and address will be replaced with the first contact/driver when the Birthday filter is used).

Multiple labels can be created per result if the option is selected.

Clntqrywiz-gen-5-mailinglabels.png

Form Letter

The Form Letter output provides a mechanism to output the list of clients directly to a form letter template created with Memowriter for mass mailing.

During the process, the client information is merged into the letter based on the user-defined merge codes. If using one of the policy queries, four policy detail client queries, note that only client information will merge to the letter.

The Client Query Wizard will prompt the user to select the form letter.

Clntqrywiz-gen-5-formletter.png

After the letter is selected, the system will automatically merge and save a copy of the form letter to each matching client, and output the form letter to the printer.

AL3 Setup

The AL3 setup output format exports a custom file for legacy rating vendors.

Folder Labels

The folder labels output format provides a printable report that can be exported as a .PDF file. The report includes a single result per client sorted in the selected order. (The folder labels will display in the Avery Standard 5160 format with 10 rows and 3 columns.)

For each client, the following values are included: Client Number, Producer Code, Phone Number, Client Name and Street Address.

Multiple labels can be created per result if the option is selected.

Clntqrywiz-gen-5-mailinglabels.png

Comma Separated File (CSV) Spreadsheet

The CSV output format will export a file which can be opened with Microsoft Excel, or other third-party spreadsheet applications. The output contains client name, client address, contact name, contact address, email addresses and much more.

Example output file converted to Microsoft Excel Spreadsheet.
Client Query Export With Policy.xls

The CSV output normally includes every contact saved to the clients who match the query filters. If a policy detail filter is used, the matching policy number will also be included.

Click here for instructions removing multiple contacts from the CSV export.

If a contact or driver birthday filter is used, the CSV output will only include the clients/drivers with a matching date, not all contacts for the client.