By using queries, you can build the audience, i.e., the recipients for your campaign. Most of the standard topics you can select when using the Add Campaign Wizard come with a query. So you can run the campaign without having to build your own query, or, you simply adjust the query to your needs, once you have created the campaign. The Ad-Hoc Newsletter and Custom Audience topics require for you to build your own query.

Note: the functions described are also valid for the Sales Campaign app. The only difference between Marketing Campaign and Sales Campaign is the choice of recipients: in Sales Campaign, you can only write to contacts that exist in B2B.

In this article:

• Overview
• Marketing Campaign Queries Page
• Adding Queries
  • Adding a Standard Query for a Guest Campaign
  • Typical Guest Campaign Queries
  • Adding a Standard Query for a Sales Campaign
  • Typical Sales Campaign Queries
• Adding/Editing Query Parameters
• Scheduled Queries

Overview

For running a campaign, you need to define at least one query. However, it is also possible to have several queries based on various criteria, for instance location, date range or profile details, such as VIP status, stays, revenue criteria, etc.

Additional queries can  be useful if you want to split up the group of recipients by some regional or other criteria to receive more than one result list. For instance, all guests from Germany, Austria, and Switzerland. Or all profiles with VIP status 5 and all profiles with VIP status 4, etc.

By selecting and running the query, you determine the list of recipients, which you can view by selecting the Result Overview tab for a selected campaign.

Note: For most of your campaigns, you can simply use the existing query set available with the chosen campaign topic, such as, for instance, for birthday campaigns. Or you can select the query from the Predefined Queries and use them to generate the audience for your campaign. In some cases, you may require a specialist to access Parameters to formulate exactly the type of query you need for a specific campaign.

Marketing Campaign Queries Page

By using queries, you can locate a group of recipients for your campaign from the Database. You can have as many queries for one and the same campaign as you require.

Note: The system helps you to avoid overmailing recipients. If two queries apply to one and the same profile, the profile will receive the campaign only once. The campaign will not be sent a second time to the same recipient, even if another query applies to the guest. Just remember to keep the checkbox Send Once per Profile selected in the Campaign Details.

Accessing the query page for a selected campaign:

1. In the campaign Search page, locate and select the required campaign.
   The system displays the campaign and the Campaign Menu.

2. Select the Recipient Selection (Queries) tab.
   The system displays the list of queries for the selected campaign.

3. Click into the query to display the query details.

In most cases, there is just one query for the campaign. You can, however, add as many queries as required.

The Query List – Description of Column Information

Field	Description
Query Name	Name of query as determined when adding the query. Once the query is in production the name can no longer be changed.
Schedule Type	Schedule Types are used to determined a dedicated date and time the query is run and the marketing campaign emails are sent. Schedule Type can be once, daily, daily scheduled, and on a particular date and day in the month.
Next Run time	Shows the next time the query will run.
# of Results	Here you can see the number of profiles resulting from the campaign query. This column is filled when you select the Generate Results option. To view the receiving audience, select the Result Overview tab in the Campaign Menu.
# of Processed Results	How many of the overall # of Results have been processed/sent.
% Processed	How many of the overall # of Results have been processed/sent in %.
Statistics	Indicates if results are included in statistics or excluded. Exclusion is usually used for test groups.
Total	Shows the total audience based on all queries, i.e. the total number of profiles matching the queries.
Number of email addresses with multiple results	Shows the number of email addresses with multiple results. The number displays in the # of Results column.
Total number of removable results	Shows the total number of results that can be removed, i.e. that should not receive the campaign. The number displays in the # of Results column.

Available options:

• View and edit an existing query by clicking into the query. See below.
• Copy an existing query to reuse it for your purposes. See Copying Queries.
• Add a new query by selecting the Add Query option. See Adding Queries.

View and edit an existing query

Click into a query to access the query details. Each query is made up of a Query Definition and query Parameters. They are available by selecting the respective tab options.

The Query Definition shows the Query Name and the Remarks describing the target of the query further. You can activate further query-specific options by marking the respective checkboxes in the Query Definition. Use Direct SQL and Use Scheduler. They open up additional query tab options.

Note that these are usually reserved to the NextGuest specialist and required specific permissions.

The Query Parameters option is where you can add queries and/or modify them. See Adding/Editing Query Parameters , see Adding Queries.

Available options from the Query Details page:

• Generate results to create a list of recipients. The system searches the database and locates all profiles matching the given query options. This can take a moment. You will see the number of profiles corresponding to the query in the # of Profiles column.
• View the recipients of the campaign resulting from the query or queries you have defined and exclude or include recipients as required - Select the Result Overview The tab is only displayed if you have run a query and have resulting recipients.
• Add a new query. Return to the list of queries with Close or Esc and then select the Add Query option, see "Adding Queries" on page 64.
• Copy an existing query - Select a query in the query list and select the Copy Query option, see related article.
• As long as you do not change the status of the campaign to In Process, you cannot send the campaign and you can keep adjusting the query results till you have found the desired results. Simply throw away the results by selecting the Remove Results

Note: Icons are grayed out or not displayed when they are not active, i.e. when the respective action is not available at the moment. For instance, the Remove Results option is only active when you have selected Generate Results.

Table Query Details Page – Available query options

Icon	Description
Add Query	Select the Add Query option from the query list to add a new query for the campaign. See Adding Queries Note: This option is not displayed in the Edit Query page. It is available, if you have saved and closed the query page. Note: In some cases, it makes more sense to use the Copy Query action to copy an existing query and change it as required.
	When you have completed the campaign query, select the Save option to store the entry before proceeding further.
Dedupe Multiple Recipients	Select the Dedupe Multiple Recipients option from the query list to check for duplicates in the list of recipients and to remove them. Note: This option is displayed in the Query list page. It becomes available once you have selected Generate results for a query.
Copy Query	Select the Copy Query option to copy an existing query and either leave it as is or adapt it to your requirements. This is especially helpful, when you have a complex query with many parameters and want to change a few details only.
Generate Results/Remove Results (Toggle options)	Select the Generate Results option, if you want to see how many recipients your query will yield and to generate the recipients of your query. The system displays a message with the number of matching results (without filling the Results table). Confirm that you want to generate the results to fill the table.
	The Generate Results option is replaced by the Remove Results option. If you close the query to return to the recipient list, you see the number of recipients generated by the query in the # of Results column. Note: As soon as you have generated results and received profiles in the recipients list,you can no longer edit the query. If you want to modify the query, you need to Remove the Results.
	Select the Remove Results option if you have a list of recipients but want to make changes to the query and not use the existing list of recipients.
Send Mail Results	Select the Send Mail Results option if you want to send the campaign to the selected recipients. Note: this option is only available if the campaign is in status In Process in the tab Campaign Details and if the Results of the query have been generated
Result Report	Select the Result Report option in order to create a report with all the recipients matching the query. The big advantage of this report is that you can check the results and at the same time view the field list that you are producing for the campaign.
Delete	Select the Delete option to delete a query from the list. You can only delete queries that are not in production, i.e. queries with the status In Preparation. Once queries have generated results, the system will not let you delete the query. You can Remove the Results and then Delete it.
Close	Select Close anytime, if you want to close a query without saving your changes and return to the list of queries.
ADDITIONAL QUERY OPTIONS
Parameter tab option	View, add, and modify individual query options. This function should only be used by the NextGuest campaign specialist. See Adding/Editing Query Parameters
Use Direct SQL checkbox	Usually grayed out and reserved to the NextGuest campaign specialist. Filter the group of recipients by using a direct SQL statement. This tab is activated by selecting the Use Direct SQL checkbox in the Query Definition. This option is reserved to the NextGuest specialist.
Use Scheduler checkbox	The Scheduler enables the definition of a date and a time when a query should be run. The Scheduler tab is activated by marking the Use Scheduler checkbox in the Query Definition. See Scheduled Queries

 

Adding Queries

Most of the standard topics you can select when using the Add Campaign Wizard come with a ready-made query. After adding the campaign, you simply run it with this query, or you can adjust it by editing the the query.

The Ad-Hoc Newsletter and the Custom Audience topics requires for you to build your own query. The following describes how to add a campaign query and lists some typical query parameters available with Marketing Campaign.

For an overview of available campaign query options, please ask for our Excel worksheet “Marketing Campaign: Overview of Available Campaign Parameters.”

Adding a new query

1. In the list of queries, select the Add Query option to add a new query for the campaign.
   The system displays the New Query page.
   

Query Name: Enter the name of the query. Use an intuitive name explaining what the query is about. If there will be more than one query for the campaign, ensure that you can tell the differences in the query from the name. (For instance Birthdays July 2021 August 2021, etc. or profiles from Germany, Switzerland, Austria, etc.)

Pre-defined Queries: Select your query from one of the pre-defined standard queries in the drop-down menu. This provides you with typical standard queries that you will use again and again for different campaigns. In most cases, you can select one of the existing query sets here and use them to determine your campaign recipients. If you need to define a query from scratch, select Custom audience or adhoc newsletter from the drop-down.

2. Enter the query name.
3. Pick one of the pre-defined queries.
   After selecting one of the pre-defined queries, a short description of this query displays in the Remarks field.
4. If necessary, add Remarks to further explain the query.
5. Select Save when you are done.

The system displays the new query in the list of existing queries.

Click into the query name to display the Query Details page for the new query.

6. Check the query (to see if you are getting the desired results) with Generate Results.
7. If you want to add more selection criteria to your query set, you can use the Parameters tab. 

Typical Guest Campaign Queries

Here is an overview of typical details you can query for in Guest Campaigns. Queries can be based on details regarding the hotel(s), stay(s), reservation(s), the campaign, the guest, loyalty issues and geo data (if you work with POI)

• Address and profile details
• Address type (business, home, any)
• Country
• ZIP code
• City
• Language
• Gender
• Birthday
• Nationality
• Company
• Campaign details (class, group, type, received, read, link opened, sent date, etc.)
• Reservation details
• Arrival and departure dates
• Number of nights
• Number of adults and children
• Room type
• Rate code
• Reservation status
• Stay and revenue details
• Min/Max number of stays at the hotel
• Achieved room, F&B, other and total revenue
• ARR per stay/total room revenue per stay
• Supplementary information, such as guest preferences, loyalty and other guest cards, etc.
• Guest preferences
• Features
• Specials
• Additional customized guest preferences
• Preferred business/leisure travel
• Recorded mailing codes
• Guest cards, loyalty cards, etc.
• Achieved loyalty points

Building a customer audience for a one-time campaign

1. In the Campaign Details, select option Recipient Selection (Queries).
2. Select the Add Query option to add a new query.
3. Enter the Query Name.
4. Pick Custom Audience or Ad-hoc newsletter from the drop-down with pre-defined queries.
   After selecting one of the pre-defined queries, a short description of this query displays in the Remarks field.
5. If necessary, add Remarks to further explain the aim of the query.
6. Select Save when you are done.
   The system returns to the query list and displays the new query in the list.
7. Click into the new query to start adding the query details, i.e. the query Parameters.

The system displays the Edit Query page.

Since you are starting from scratch, you need to add parameters to the query.

1. Select the Parameter tab.
   
2. Select Add New Parameter.

Adding/Editing Query Parameters

The Parameters tab is available for queries, as long as the query has not been run. Using the Parameters page, you can access, view and edit the individual filters used to build the query. For most of your campaigns, you will simply select one of the existing query sets from the Standard Queries and use them to find your list of recipients.

In some cases, though, you may need to formulate a special type of query for a specific campaign, or you may want to add some extra filters to an existing standard query. The Parameter option is reserved for these cases.

This option is basically identical to the functionality you receive for setting up standard queries in the campaign Setup -> Predefined Queries). For editing and expanding the standard queries, we recommend to use the Setup option (independently from individual campaigns).

Select the Parameters tab in the Query Details page to reveal the Parameter Overview, i.e. an exact listing of the parameters, labels and conditions used to build each single selection criterion that is making up the selected standard query.

From here, you can

• View the parameters that are being used in the query.
• Change the sequence by moving parameters up or down (select the respective Up and Down arrows in the Action column).
• Open the Parameter Definition by clicking into the respective data row (make sure you do not click into the Action column, since it is used to move the parameter up or down.)
• Delete the selected parameter by selecting the Delete

Adding/Editing a Parameter Definition

Select Add to add a new query. Click into the respective data row to edit an existing query.

The system displays the Parameter Definition for the query.

1. First, select the Parameter Group.
2. Next, select the Parameter Type.
3. Specify the table and/or fields which you want to include in the campaign query, the sequence of the parameters, as well as any specific default values or conditions that need to apply for the query.
4. Select the Save option when you are done.
5. Select the Check SQL Syntax option to ensure that the specified SQL conditions are ok.
6. Add as many parameters as you require.

Depending on the selection in the Group field, the system will offer different parameter Types. In the case of Generic types, such as string, date, number, etc. the Condition fields will remain empty.

Most other selections usually target a particular field. In these cases, the system will automatically add and fill in the SQL condition required for the query.

For a description of the fields used to build a query parameter, please refer to table below.

Caution: For building queries manually by creating and adding parameters using SQL statements and conditions, you require some knowledge of database structure, table and field names and some knowledge of SQL statements. This functionality is therefore reserved for system specialists.

Note: In order to test your parameters, go back to the Query level and test the set of parameters you have created by Generating Results.

Table Query Parameter Definition – Description of Fields

Field	Description
Group	In this field, you determine which group the query belongs to. The Group selection narrows down the options you receive with the Parameter Type field. For instance, if you select the Hotel Group, the available parameters in the Type field will be reduced to the parameters available with hotels, i.e. brand, region, country, and state.
Type	In this mandatory field, you determine which type of table and field you address by selecting the required choice from the drop-down list. Using the Type field, you determine, which kind of drop-down selection is used for the query, i.e. a date selection, a monthly selection, a TRUE FALSE selection, or a query to a specific field. Using the Condition fields, you tell the system which fields to evaluate with the selection. Note: The system automatically fills the required conditions for all queries referring to specific fields. For instance, if you select Membertype, the system will automatically fill the WHERE Condition with the required SQL query " m.membertype='{0}' Only with Generic Type selections do you actually have to formulate your own SQL conditions. Caution: Be careful not to change the Type, once you have entered conditions, since they are immediately removed, if you change the type.
Label	The name by which the parameter is displayed in the query list.
WHERE/HAVING/ FROM/ORDER Conditions	Using the Condition fields, you tell the system which fields to evaluate with the selection. For instance, use the monthly selection to evaluate the birthday date field of guests or use the membertype selection and check the membertype for the central member code. With most parameter Types, the respective condition is pre-filled by the system. You only require manual insertion of SQL conditions with the Generic types. For instance, if you select Membertype, the system will automatically fill the WHERE Condition with the required SQL query " m.membertype='{0}'   Note: The possibilities and options depend from field to field in the NetHotel database. If in doubt, it is recommend to check the exact field contents that you can query for.
Default Value	If the parameter should have a certain default value, you can select it here. For instance, if you filter by country (Type = Country) and most of your queries will be run for profiles from Germany, you could select Germany from the available drop-down. When you select the query next time, the Country field will automatically default to Germany. Moreover, if you make a query selection and run the query, the system will remember your selection by writing it into the Default field.

Scheduled Queries

Using the Scheduler from the Queries Detail page, you can automate the sending of campaigns. The Scheduler enables the definition of a specific date and time when a query should run.

The Scheduler is linked to the query and accessed from the query page. The Scheduler tab is activated by marking the Use Scheduler checkbox in the Query Definition.

Caution: The Use Scheduler activates the Scheduler tab and the respective scheduled function immediately. Take extra care to set up the campaign carefully, check and preview the query results and the look of the template BEFORE activating the scheduler, since once it has been activated, the automatic sending of campaign emails to the query recipients will be ongoing. This is especially critical with campaigns that are sent every day.

Setting up a scheduled query:

1. Set up the campaign query to produce the required target group for the campaign.
2. Next, in the Query Definition, mark the Use Scheduler checkbox .

The system activates and displays the Scheduler tab.

Using the Scheduler page, you determine the time and the date as well as the frequency with which you want to send the query and with it the linked campaign.

The Scheduler is ideal for all kinds of automated mailings or if you want to send your campaign on a special date and time, i.e. on Mothersday or on New Years Eve.

3. Select the required Schedule Type from the drop-down.

 

According to the picked Schedule Type you can determine the timing for a query once, daily, daily scheduled and on a particular date and day in the month. The resulting display will vary accordingly.

• Daily at Time: Run the query daily at a specific time, for instance a campaign with offer of the day sent daily at 09:30 
• Daily Scheduled: Run the query daily; the time does not play a role; let's say a welcome email, where every day during the day, the system checks for newly created profiles or new loyalty card owners and sends them the welcome This additionally opens a weekday selection, effectively allowing you to schedule weekly mailings.
• Day of Month: Run the query once a month on a specified day. For instance monthly account or loyalty statements to your guests.
• External Application: the sending is scheduled by an external application
• One Time: Run a query once on a certain specified time and send the linked campaign to the resulting recipients. This has the advantage that you can plan and specify a campaign and then send it exactly at the time you need without any further interaction. 

 

4. Select the required Start Date and End Date for the campaign. If you would like to leave these dates open, simply enter today’s date for the start date and select an end date well in the future.
5. Select the Start Time when the scheduler should send the campaign. For instance, for large mailings, you may want to use the early morning hours, say 3:00 to take advantage of the lower email traffic.
6. Select the Allow multiple Results checkbox, if required.

Regarding the Allow multiple Results checkbox: If it is checked, the system will send the campaign to a guest profile more than once, if the query applies again.

Note: This is really important for loyalty account statement emails, for example. You want to make sure that guests receive the automated loyalty account statement every time, not only once.

7. Set up the Scheduler exactly as required for the query and your campaign.
8. When you are sure that everything is correct, select the Save option.