User Profile Lookup

Use our new Profile Lookup feature to create a set query of your users, search for specific users, and view historical results for the user you've specified.

Prerequisites

• A database connection has already been set up in Accelerator.
• A basic understanding of which data you want to display in your profile lookup results.
• To set up the profile parameters you want to search on, you must have a Data Admin or System Admin role.
• To perform the profile lookup query, you must have a Campaign Admin or System Admin user role.

Steps

• Set up the query and parameters that you want to display for the user profile lookup.
• Use the profile lookup to search for a specific user.

Set up the profile lookup query and parameters

Complete the following steps to build the query and set parameters users will see on the Profile lookup screen:

1. Navigate to Admin > Users and Accounts > Profile lookup builder.
2. Select the Database connection you want to query.
   
3. In the Description field, enter a description to explain any specifics about the data format users must follow to return valid results. For example, if the date uses YYYY-MM-DD HH:mm:ss format. The description displays on the Profile lookup page and in the search form.
4. In the SQL Queries section, enter the Profile SQL query you want to use.
   
   Note that the Profile query expects a single return, with any included attributes desired. The only required parameter is Unique ID, which needs to be represented as criteria within the query. To copy the syntax for that criteria, click the copy icon next to Unique ID.
5. To test the results of your query, select Preview and enter a Unique ID. The results display as a table, but the parameters and order should match what you want to display on the Profile lookup page when users search for profiles.
   
6. For the History query, repeat steps 4 and 5. The History query is meant to display numerous results for the user, using the three parameters of Unique ID, Start date, and End date. You also have the option to copy the SQL syntax for those parameters. You must use those three parameters as criteria for the History query to work, and the query is automatically limited to 100 results. To test the query, select Preview.
   

What data should I use for the Profile query?

If you followed our best practices when structuring your data, you should find most of your profile information in the recipient source data table. Most commonly you’ll find personalization, unique channel identifiers, demographic data, and aggregate data element there. You can add tables to the query to cover purchase behavior, site visit information, or points/coupons redeemed.

What data should I use for the History query?

If you followed our best practices when structuring your data, some recommended locations for Historical data include:

• Audience Recording - should contain campaign metadata such as:
  • Campaign name
  • Segment codes
  • Contact date
• Event feeds - where you’ll find:
  • Send - this is relevant if the Audience Recording table won’t have enough metadata regarding the campaign build out to inherit it from there
  • Open - channel opens if applicable (Email, PUSH)
    • In the case of Email, opens from mail tools to pre-fetch/pre-populate the rendered content may make the definition of a “True Open” or “Adjusted Open” something we want to ensure gives confidence to what we report: A channel reader did not falsely represent this as engagement - so perhaps an open is only “True” if it occurs post-delivery after a minimum number of seconds (8-10sec?).
  • Click - channel link clicks occurred (aggregated) - as this reflects engagement WITH the given campaign. If you blow this out more, as in per link label - it could get busy or overly muddy on a quick lookup view from the form, so consider that before exhaustively collecting and displaying ALL
  • Unsubscribe - signifies opt-outs and on what campaign they occurred
• Additional tables can be included for data points such as
  • Purchase behavior
  • Site visit
  • Point/coupons redeemed

Using Profile lookup

Once you've configured your Profile lookup query, the parameters are available to query a set user. Complete the following steps to look up a user's profile:

1. Navigate to People > Profile lookup. The Profile lookup form automatically opens and displays the description you provided to advise users of formatting or other data syntax requirements.
   
2.  Enter your search parameters and select Search.

The results of the query display two sections: Profile and History.

If the results return more than a single value for the Profile query, an error message displays. The Profile lookup currently only supports a single query return.

Query formatting recommendations

The Profile lookup displays the raw output of your SQL queries as constructed, thus it we recommend considering the ease of readability for users who will execute a search. When formatting the output, it is good practice to make the UNIQUE ID something an end-user can provide along with relevant begin and end dates for search, taking into consideration these concepts or ideas:

• If leveraging Audience Recording or Sent Event feed data, ensure date ranges and a single recipient identifier are used, so the initial data returned is small.
• When assembling any event metrics FROM this, joins to additional tables: Audience Recording to SENT, OPEN, CLICK and UNSUB are all quick lookups on: RecipientId and RequestId. These two elements will drastically narrow down the data returned and make lookups easier to manage. The nature of a RequestId alone will simplify returning date ranges, as any query against Audience Recording or Sent by Recipient and Date Range will give you a good starting result set (drastically smaller than the ENTIRE TABLE/DB).
• Once you get results flowing back in, remember to LEFT JOIN on those EVENTS and seek to remove duplication by employing a ROW_NUMBER() or RANK() function so every Open/click/etc. are counted one time - even if returning the FIRST OPEN/CLICK/etc. and the LAST OPEN/CLICK/etc.