Articles in this section

Managing Queries

Avatar
Daniel Williams
Updated
Follow

This article is part of a series. At the bottom is a link to the next article. To start with an overview of the Query Tool, please click here to go to the beginning of the series.

Table of Contents

Landing Page

Click on the Reports menu and Query Tool.

  • The landing page includes a button in the top right corner to Create a New Query.

For more information, skip down to Creating a Query.

  • The page also contains a list of your saved queries with several options including an option to run the saved query and see the results, export the results, edit the query, or delete the query.

For more information, skip down to Running a Saved Query.

  • It also shows the date that the query was created, the user that previously changed the query, and the date of the change.

Creating a Query

  1. Click on the Reports menu and Query Tool.
  2. Click Create A New Query in the top right corner.
  3. Choose a Data View by clicking on the drop-down list.

Below that is a "Fields" panel on the left and a "Columns" panel on the right.

  • The "Fields" panel will show a list of available fields for the selected data view.
  • The "Columns" panel will show a list of the fields that you add to your query.
  1. In the Fields panel, click Add next to the field that you wish to add and it will appear on the right. If you make a mistake, over in the "Columns" panel, click Remove next to the field that you wish to remove, and it will disappear.

You can also change the order of your fields in the Columns panel by clicking and holding the button next to a field and dragging it up or down.

  1. Once you have added all desired fields, click Set Filters in the bottom right corner.
  2. This screen will allow you to add one or more filters. If nothing is added, then it will return all profiles or records. If you want to add a filter, click Add A Filter and choose a Filter Field. If you make a mistake, you can click Remove on the far right.

The filter does not have to be a field that you added.

  1. Choose an operator and value. Possible operators include:
    • Equals: For some fields, this operator will look at the value and if the field matches this value, it will be included in the results.
    • Not Equal: For some fields, this operator will look at the value and if the field does NOT match this value, it will be included in the results.
    • Greater Than: For some fields, this operator will look at the value and if the field is greater than this value, it will be included in the results.
    • Less Than: For some fields, this operator will look at the value and if the field is less than this value, it will be included in the results.
    • Contains: For some fields, this operator will look at the value and if the field contains this value, it will be included in the results.
    • Does Not Contain: For some fields, this operator will look at the value and if the field does NOT contain this value, it will be included in the results.
    • In the Period: For some fields, this operator will look at the value and if the field matches this value, it will be included in the results. Examples of values: Last 30 Days, Year to Date, and more. Click here for more information about the Period options.

For example, in the "Basic Profile Info" view, there is a filter field for Member Checkbox. By adding this, setting the operator to Equals, and the value to True, it will filter the query to member profiles.

One more example, also in the "Basic Profile Info" view, is a filter field for Member Since Date. By adding this, setting the operator to Greater Than Or Equal To, and the value to 1/1/2023, it will filter the query to profiles who were members since the beginning of 2023.

There are some things to know if you want to have multiple filters. For more about that, please review the later section: Advanced Filters.

  1. When you have added your filters, click Set Sort Order in the bottom right corner if you would like to specify one or more fields by which to sort the results. Click Add a Sorting Rule and choose a field by which to sort. If you make a mistake, you can click Remove on the far right. 
  2. When you have added your sort orders, click Run This Query in the bottom right corner, or on the left menu. This screen will show a grid with the results of your query and the number of records at the bottom right (Record Count).

You may not see everything on one screen if you have many records, so be sure to look at the bottom center of your screen for additional pages.

  1. Before continuing, you may want to save this for future use. If you want to save it, click Save Query Definition in the top right corner, and enter a Name, Description, and Assignee.

If you do not set an Assignee, it will be shared with other MC Trade users that have access to this tool. If you set the Assignee to yourself then it will be private.

  1. To export the results, click Export These Results in the bottom right corner.
  2. To access the profiles related to the results, click Open These Results In Profile Selector in the bottom right corner. This requires Profile ID to be a field/column selected in the query. 
  3. If there is a need to make changes to the code directly or create your own custom SQL query, you can click View the SQL Code on the far left.

Running a Saved Query

  1. Click on the Reports menu and Query Tool.
  2. If there are any saved queries, it will display a list, and you can click the play button on the far left to run the query. Running the query will display the results.
  1. If you want to skip the results and go straight to exporting the data, click the Actions button on the far right and choose Export data.

  1. If you want to go straight to opening the results in the profile selector, click the Actions button on the far right and choose Open results in profile selector.

If you want to change the filtering or sorting then you will need to edit the query first, with one exception: if there are any date fields to the Filters section, you'll be prompted to enter any new date values prior to exporting or opening in the profile selector. 

Editing a Saved Query

  1. Click on the Reports menu and Query Tool.
  2. Next to the saved query, click Edit.

Deleting a Saved Query

  1. Click on the Reports menu and Query Tool.
  2. Next to the saved query, click the Actions button on the far right, and choose Delete.

Next in series: Advanced Filtering for Queries

 

Powered by Zendesk