Segmenting data with queries

You can use the Segment process to segment data based on the results of a query that you create.

Before you begin

Segmenting considerations

About this task

Segmenting by query is most useful when it is necessary to filter the data in a field to create the required segments. For example, assume that you want to divide your customers into high-value (more than $500), medium-value ($250-$500), and low-value (under $250) segments that are based on their purchase history over the last year. The PurchaseHistory field in your customer database stores the total dollar amount of each customer's purchases. Use a separate query to create each segment, selecting records with values in the PurchaseHistory field that meet the criteria of the segment.

Note: You can also use raw SQL to segment data.

Procedure

  1. Open a flowchart for editing.
  2. Drag a Segment process Head in brackets from the palette to your flowchart.
  3. Connect at least one configured process, such as a Select process, as input into the Segment process.
  4. Double-click the Segment process in the flowchart to open the Segment process configuration dialog.

    Cells from processes that are connected to the Segment process appear in the Input list.

  5. On the Segment tab, open the Input list and select the input to the Segment process. To select multiple cells, use the ellipsis button (...) next to the Input list.
  6. Select Segment by query.
  7. Determine the number of segments that you want to create, and enter that number in the # of segments field.
  8. Now you will construct a query for each segment: Select the first segment and click Edit to open the Edit segment dialog.
    Note: For reference information about each available control, see Segment process: New segment and Edit segment controls.
  9. If you want to include all IDs from the segment or table that you selected as the Input source, choose Select all <audience> IDs (The word <audience> indicates the audience level, such as Customer, that is specified for the selected input data source.)
  10. If you prefer to use a subset of IDs from the selected data source, choose Select <audience> IDs with, so you can do a query for the records that you want.

    The Expression box shows columns labeled Field name, Oper, Value, and And/Or.

    When the button says Text Builder, you can build expressions by populating the columns labeled Field name, Oper, Value, And/Or.

    When the button says Point & click, you can view or edit raw SQL and you can use the Formula helper to pick macros, operators, and string functions from a list.

    With either method (Text Builder / Point & click), you can select fields from the Available fields list, including Unica Campaign Generated Fields and Derived fields.

  11. Construct a query to determine which records will be used to create the segment:
    1. To specify which field to query, click in the Field name cell. The Available fields list should appear. If the list does not appear, click in the Field name cell again. In the Available fields list, expand the fields until you see the one that you want. Select an available field by double-clicking it or by highlighting it and clicking Use.
      Note: When deciding which field to use, you can highlight an available field and click Profile to see a list of values in that field. Then use the Profile selected field dialog to examine the field values. For more information, see Previewing field values from your user data.
      Note: When the list of Available fields is displayed, you can use the Derived fields button if you want to create or use derived fields. For more information, see Derived fields.
    2. Click in the Oper cell, then double-click a comparison operator in the Operators list (=, <, >, <=, >=, <>, In, Not in, Between).
    3. Click in the Value cell, then double-click a value. If no values appear, double-click (Profile...) in the Values list to open the Profile selected field dialog so you can select from a list of values. You can also double-click in the Value cell to edit the value directly.
      Note: If you do not see the expected list (Available fields, Operators, Values, For selected expresssion), try either single clicking or double clicking on a cell in the Expression area.
    You now have an expression that consists of a field name, operator, and value.
    Segment process expression
  12. Click Check syntax to confirm whether the query syntax is valid. Checking the syntax does not put any load on the database server.
  13. To add and combine multiple expressions, follow the guidelines below:
    1. To add another expression, click the And/Or cell, then double-click AND or OR in the Values list to indicate how to combine the expressions.
    2. Build your next expression, consisting of a field name, operator, and value.
    3. To add parentheses to control evaluation order, double-click the Field Name in any row to display the For selected expression list. In the list of expressions, double-click Add ( ) to add a set of parentheses, Remove ( ) to remove a single set of parentheses, or Clear all ( ) to remove all of the parentheses in the selected expression. Parentheses allow you to group expressions when defining complex queries. For example, (AcctType = 'Gold' AND Rank = 'A') OR NewCust = 'Yes' is different from AcctType = 'Gold' AND (Rank = 'A' OR NewCust = 'Yes').
    4. To reorder the selected expression, double-click Move up or Move down.
    5. To add a blank row below the selected expressions, double-click Insert.
    6. To delete the selected expression, double-click Delete.
  14. Optionally, click the Text Builder button so its label changes to Point & click. When the button says Point & click, you can view or edit raw SQL. You can also use the Formula helper to pick macros, operators, and string functions from a list.
    Note: If your query includes a table field that has the same name as an Unica Campaign generated field, you must qualify the field name. Use the following syntax: <table_name>.<field_name>.
  15. Click the Point & click button to change back to the original query mode. The button label changes back to Text Builder.
  16. (Optional) Use Test query to see how many IDs the query returns.

    A progress bar is displayed while the query is being tested. Close the progress window if you want to cancel the test. When testing is complete, Unica Campaign indicates the number of rows the query returned.

    Important: Global suppressions and cell size limits are not applied in test query counts. Test queries might also return non-normalized data. To obtain an accurate result count, test run the process.
  17. Click OK to save the query for this segment and close the Edit segment dialog.
  18. In the Segment process configuration dialog, edit the remaining segments to define queries for them.
  19. Continue configuring the Segment process by using the controls on the Segment, Extract, and General tabs. For more information, see the following topics:
  20. Click OK to save your selections and close the Segment process configuration dialog.

    The process is now configured. You can test the process to verify that it returns the results you expect.