Creating a List from a Data Analyzer Query Paige Freeman May 06, 2024 16:17 Updated Marketing lists can be populated with customer records based on a predefined Data Analyzer query created using existing ThreeSixty data. Importing a predefined query uses the MKT510 batch process. The results returned by the Data Analyzer query will create the customers on the marketing list. The marketing process involves several steps: Define where the list is coming from.ThreeSixty can help identify who you wish to communicate to, based on inputs from a variety of sources (e.g., an event attendee list, a log of constituents’ opt in requests, or a query for constituents meeting certain criteria such as location, age, past purchases, etc.). Define who is in the list.The first step indicates where the data comes from, but in order to proceed, a list of names and IDs need to be loaded into a distinct list, which will be used to create emails, mailing labels, faxes, and/or a file. Each of the data sources that were defined in the first step may change over time, so this next step “loads” the list of current names (who is on the opt in list right now, who was on the most recently purchased list, who currently meets a query’s criteria, etc.). Produce the list.This is where the actual communication occurs. Using the list defined in the previous step, ThreeSixty will output emails, label, faxes, and/or a file that staff can use as a call list. An activity, contact tracking record, or appeal can be captured on each constituent’s record when this occurs. You can use BusinessObjects folders that are referenced by public folders and personal folders to search for the query. For public marketing lists (available to all users in your org/org unit), save your Data Analyzer query in the All > Public Folders > Personify[version] > [Org_OrgUnit] > Marketing Lists folder. Example:All > Public Folders > Personify760> NASE_NASE > Marketing Lists. For personal marketing lists, save your Data Analyzer query the All > My Favorites folder. Master_Customer_ID and Sub_Customer_ID must exist as the first two columns in the report in order for the DA query to work. The use of a Data Analyzer query to generate a marketing list gives you the opportunity to create more complex marketing segments as well as retain the query and create additional reports around the data. If you want to use a Data Analyzer query list with MKT510, you need to create a DA query with only 2 columns - Master_Customer_ID and Sub_Customer_Id in Select if the query has a UNION clause. If you want to create a query that excludes certain records, then the best practice is to create two lists and use Creation Method of Merge Lists. Example:If you want to pull all clients from Montana but exclude those with advanced degrees, then create two queries: the first query should include constituents from Montana and the second should list constituents with advanced degrees. Use the Marketing module to specify that all constituents from the first list should be used EXCEPT where those customers are found in the second list. This approach will provide better performance than trying to exclude records within a multi-step Data Analyzer query. To create a list from a Data Analyzer query: From Marketing Central, select Data Analyzer List from the Create List card.- OR -From the Manage Marketing List page, select Data Analyzer Query from the Create List drop-down. The Add Marketing List page displays. From the List Information section, perform the following: Modify the system-generated List Code, if necessary. Select an option from the Regeneration Frequency drop-down to define how often the list will regenerate so that it has the most up-to-date customer information, if necessary.If you select a regeneration frequency of ALWAYS, any time MKT600 is run in the future, it will also regenerate any marketing list with a regeneration frequency of ALWAYS. Enter a Description of the list. The Creation Date defaults to today. Change this if necessary. If necessary, select the list Expiration Date from the drop-down. In the Parameters section, perform the following: Select the Specify Query Folder drop-down to select where the Data Analyzer query exists. If you select "Public Marketing Lists", the Data Analyzer Queries window will populate with Data Analyzer queries that were saved in Public Folders > [Environment Folder] > [OrgID_OrgUnit] > Marketing Lists in Data Analyzer. If you select "Private Marketing Lists", the Data Analyzer Queries window will populate with Data Analyzer queries that were saved in the "My Favorites" folder in Data Analyzer. Select the Select Report "Find" button to select which query to use.The Choose a Query page appears. The list of Data Analyzer queries populates based on the folder selected from the Select where the Data Analyzer query exists drop-down.If you cannot find the DA query you're looking for or want to create a new query, close this window, and open Data Analyzer in a new tab. If you create a new query, you will need to refresh the list when you return. The list of queries is cached when the Creation Method of Data Analyzer is first selected and saved. If a query that was specified in the past is no longer available (i.e., it was renamed, removed, etc.), then the SQL will no longer be available. In that circumstance, you should refresh the query list to see what the current options are.If Master Customer ID and Sub-Customer ID are not defined in the Data Analyzer query setup, the query will NOT be processed. Select an item from the list and click Select. Select the Specify Constituent "Find" button to select a type of identifier. If necessary, select the number of records to receive by selecting the Get First radio button and entering a number, or you can receive all of the records by selecting All. From the List Creation section, perform the following: Select the Include or Exclude Constituents from the selected opt-in interest area drop-down to add or remove customers to the list based on interest area if necessary. Select the Opt-In Category drop-down to select an area of interest to either include or exclude customers by, if necessary.After selecting an opt-in category, the Opt-In Choice drop-down becomes a required field. Select the Opt-In Choice drop-down to either exclude or include customers based on the specified interest area if necessary. Set the Follow “Do Not Solicit” preferences of constituents toggle to adhere to the constituent’s contact preferences. Set the Promotional Communication toggle to YES to indicate that the list is meant to be for a marketing promotion, if necessary. Set the Only include current members on the list toggle to YES to eliminate any constituents from the generated list who are not current members, if necessary. Set the values for the Exclude Constituents from this list values as necessary. Select the Has drop-down and select from either Donation (to exclude a bill-to customer) or Order (to exclude a ship-to customer). Select the For drop-down to determine what the order or gift was for.The title of the link below will change depending on your selection. Select the Within drop-down and select a time frame to limit your exclusion. Select the Select If/How Households Should Appear in the List drop-down to either include or exclude customers who do not belong to a household if necessary.This option is only available if the “Use Householding” app parameter = Y. From the Optional Information section, perform the following: Select the department code or the name of the department that owns the list from the Department drop-down, if necessary. Select a campaign from the Campaign drop-down to select how you want this list to be used to target customers, if necessary. Select the Assigned to field and select a different user to assign the list to, if necessary. Select a subject from the List Subject drop-down to better categorize your list, if necessary. Add any additional information in the Special Notes text box, if necessary. Select Save and Create List.The system runs the MKT500 batch process. Screen Element Description List Information List Code Text field. This unique code identifies the marketing list throughout ThreeSixty. While the system captures the list code when you record it on the Order Entry screen and assigns the list code to a specific group of customers and prospects, you can change the code here. The list code helps determine if a person from a specific list responds to a specific appeal. The system uses the list code to perform an ad hoc analysis of the results from one or more lists. This code cannot exceed 24 characters. Regeneration Frequency Drop-down. Select an option to define how often the list will regenerate so that it has the most up-to-date customer information. If you schedule the job here, you must verify the MKT600 batch job is running using the Job Submission screen. If you select a regeneration frequency of ALWAYS, any time MKT600 is run in the future, it will also regenerate any marketing list with a regeneration frequency of ALWAYS. Values in the drop-down are populated based on the fixed code defined for the MKT "REGENERATION_FREQUENCY" system type. Description Text field. Enter a description for the list. This field is required to generate a list. Creation Date Drop-down. Displays the date the list was created. This field defaults to the current date, but you can change the date by clicking the drop-down to select from that calendar. Once the information is saved, you can no longer edit this field. Expiration Date Drop-down. Displays the date the list will expire. Click the drop-down to specify a date for users to discontinue using the list. This field is optional. Parameters Specify Query Folder Drop-down. If you select "Public Marketing Lists", the Data Analyzer Queries window, launched via the Select which query to use link, will populate with Data Analyzer queries that were saved in the following location in Data Analyzer: Public Folders > [Environment Folder] > [OrgID_OrgUnit] > Marketing Lists. For example: Public Folders > PPRO > NSSWP_NSSWP > Marketing Lists. If you select "Private Marketing Lists", the Data Analyzer Queries window, launched via the Select which query to use link, will populate with Data Analyzer queries that were saved in the "My Favorites" folder in Data Analyzer. If you select "Public Folder", the Data Analyzer Queries window, launched via the Select which query to use link, will populate with Data Analyzer queries that were saved in the Public Folders folder in Data Analyzer. For example: Public Folders > Personify741 > Customers > Customer Listing by State. Select Report Search field. This field becomes active after selecting where the Data Analyzer query exists. Please note that if you cannot find the DA query you're looking for or want to create a new query, close this window, and click Launch Data Analyzer to open Data Analyzer in a new tab. If you create a new query, you will need to refresh the list on MKT002 when you return. The list of queries is cached when the Creation Method of Data Analyzer is first selected and saved. As long as the new Webi/query is saved in an appropriate folder, you can click Get Latest Query Logic to see it. When a Data Analyzer query is selected, its rules (i.e., the SQL logic) are saved in the system (when this setup is saved and/or the list is loaded). This allows the list creation process (MKT510) to perform quickly, because it does not need to go back to Data Analyzer to look up the selection criteria. However, there is a chance that what is stored in the system for this list can become out of synch with changes made in Data Analyzer. Therefore, you are responsible for using the Get Latest Query Logic button to synchronize these when appropriate. If a query that was specified in the past is no longer available (i.e., it was renamed, removed, etc.), then the SQL will no longer be available. In that circumstance, you should refresh the query list to see what the current options are. Specify Constituent Drop-down. Click the drop-down to select the constituent ID to use. Options include master_customer_id, sub_customer_id, and formatted_detail + cus_address_primary.formatted_address. No of Records to be Obtained Radio buttons. Select the All radio button to generate all of the records, or you can select the Get First radio button, then specify a number in the field to only receive a few records. List Creation Include or Exclude Constituents from the selected opt-in interest area Drop-down. Click the drop-down to select whether to include or exclude customers based on the opt-in interest area specified in the Opt-In Category drop-down. Values are Include and Exclude. Opt-in Category Drop-down. Select this option if you want to either include or exclude customers based on their opt-in interest area. Selecting an option in the drop-down will render the Opt-In Choice drop-down mandatory for saving or processing your record. Values in the drop-down are populated based on the non-fixed codes defined for the CUS "OPT_IN_INTEREST_AREA" system type. Opt-In Choice Drop-down. Select this option to specify the opt-in category to either include or exclude customers from the list by. Follow "Do Not Solicit" preferences of constituents Toggle. When set to YES, the upload process does not create a record for customers/prospects who have indicated that they do not want to be contacted. If these customers/prospects have opted out of communication with the company, no promotional requests on this customer/prospect are permitted. This option is available for all lists except Prospect Import Lists. Promotional Communication Toggle. When set to YES, this option identifies a list as being used to send promotional communication and automatically checks the “Exclude Do Not Call” checkbox on the Generate List Communication (MKT006) screen. Customers who requested to not receive promotional communications (checked the “Do Not Call” checkbox) will be dropped from this list when running the MKT800 communication-generation batch process. This checkbox is used for customers who have specified specific methods in which to not receive solicitations, whereas the Exclude Removal checkbox is used for customers who requested to receive no solicitations at all. Only include current members on the list Toggle. This serves as an additional filter for lists being created from the MKT002 screen. If this flag is set to YES, the system will eliminate any constituents from the generated list who are not current members. Exclude Constituents from this list if the constituent This option allows you to exclude ship-to (order) or bill-to (donation) customers from your list who have purchased a product, campaign, fund, or appeal within the fiscal year, calendar year, or the last six months. This will not appear for Prospect Import Lists, or for merged lists that are composed of only Prospect Import Lists. Has Drop-down. Click the drop-down to select whether customers should be excluded from the list if they have either a donation or an order. If the you select “A donation”, the system will exclude bill-to customers with active donations that meet the other exclusion conditions. If the you select “An order”, the system will exclude order line ship-to customers with active orders that meet the other exclusion conditions. Values in the drop-down are populated based on the fixed defined for the MKT "EXCLUSION_ORDER_TYPE" system type. For Drop-down. Click the drop-down to select whether the donation or order (defined in the Has drop-down) for customers to be excluded should be selected based on a product, campaign, fund or appeal in the order line. Your selection in this drop-down controls the way the following link displays. Values in the drop-down are populated based on the fixed defined for the MKT "EXCLUSION_ORDER_OBJECT" system type. Within Drop-down. Click the drop-down to select the time frame of the donation or order to be used to exclude constituents from the list. Values in the drop-down are populated based on the fixed defined for the MKT "EXCLUSION_TIMEFRAME" system type. Select If/How Households Should Appear in the List Drop-down. If you are using ThreeSixty’s householding feature, you can specify whether the marketing list should contain only households or households and individuals. If you select Households Only, a customer who does not belong to a household will not appear in the list. This option is only displayed if the “Use Householding” app parameter = Y. Values in the drop-down are populated based on the fixed codes defined for the MKT "HOUSEHOLD_INCLUSION_RULE" system type. Optional Information Department Drop-down. Select the department code or the name of the department that owns the list. The code is used to categorize the list to facilitate an easier search for one list among many in an organization. This information is used for reporting purposes only. Values in the drop-down are populated based on the non-fixed codes defined for the MKT "DEPARTMENT" system type. Campaign Drop-down. Select how you want this list to be used to target customers. For example, the list could be used to contact a group of customers to inform them of a fundraising campaign. This information is used for reporting purposes only. Values in the drop-down are populated based on the non-fixed codes defined for the MKT "CAMPAIGN" system type. Assigned To Search field. This field defaults to the logged in user. If necessary, search for and select a different user. List Subject Drop-down. Select a user-defined code to further define the list. For example, Annual Conference or Membership. Values in the drop-down are populated based on the non-fixed codes defined for the MKT "LIST_SUBJECT" system type. Special Notes Text field. Enter any extra notes you want to record about this list.