Using the Query Tool
The Query Tool is an interactive UI within the Platform, which you can use to build, test and run queries.
The Query Tool helps you explore the datasets you can reference, and to build a query using InfoSum's query language, IQL.
Table of contents
Activating multiple key columns
Creating additional columns (such as segment name)
How to activate the results of your query
How to use the Query Tool
When you use the Query Tool, the first thing to select is the query type, either Insight or Activation, then start writing the query.
- Insight queries return aggregated, statistical results
- Activation queries return a list of identifiers that relate to specific individuals. In order to submit an activation query, you must have selected an output column during the data normalization phase.
Tip: If you don't see the Activation query type, you will need to contact support@infosum.com to enable this option.
Writing IQL
Queries are written in InfoSum Query Language (IQL), you can learn more about it reading the overview of IQL and how to use the IQL functions. Please reach out to your InfoSum representative for additional training and support in customizing your queries.
Copy to console shortcut
The left-hand side list of datasets contains a shortcut to help you write queries faster. Click the Copy to console icon in the left pane next to the dataset to copy the name of a dataset, category, or category and property to the query text.
Query History
On the right-hand side of the Query Tool is the Query history. In this panel you can see the queries that you (individual user) have already run in this or previous sessions, including metadata about the query, the query itself and the results. The Query History will save the last 20 queries performed by your account.
Failed queries will show as red.
Clicking "View Query and Results" will populate the query text box with the query that was run, and display the full result of that saved query at the bottom of the screen, in the same way as if the query had just been run.
This is particularly useful if you wish to run these queries again or consult previous results you’ve generated. Please note you will only be able to re-run the queries if you have active permissions to the datasets included.
Insight Queries
For example:
SELECT
Aggregate(
ACME.Age {20,30,40,50},
AutoSports."sports lover"
)
FROM ACME INTERSECT AutoSports
When you're ready, select SUBMIT and then you will see the anonymized statistics in interactive histograms, joyplots and pie graphs. You can also review the query quality and view the query in JSON format. If your query contains any errors, a warning message appears under the query.
The above example query returns aggregate bins and representations data for a total of 34,100 records found at the intersection of two datasets, as shown.
Changing match key
The Platform automatically works out the best key for matching based on the highest fill rate (Email in this example). Using Advanced Settings you can override the InfoSum Platform automatic key choice based on the highest fill rate, as shown in the example below.
Resubmitting the example query with the override key MAID as the matching key returns different results, with the total matching records now shown as 17,500 (down from 34,100 for the platform selected key, Email).
Activation Queries
To run an activation query, the activation bunker you are querying must contain an imported dataset. Two activation Bunkers cannot be included in a single query.
For example:
SELECT TestActivation.device_id
FROM TestActivation INTERSECT (ACME INTERSECT IdentityX)
When building an activation query, the Platform automatically works out the best key for matching based on the highest fill rate (Cookie ID in this example), as shown below.
Using Advanced Settings you can override the InfoSum Platform automatic key choice based on the highest fill rate, as shown.
Using the override key Email as the matching key builds an activation query with different results, with the total rows for the output column device_id now 21,000 (down from 33,500 for the platform selected key, Cookie ID).
Activating multiple key columns
You can use the IQL syntax below to export multiple output columns from an activation dataset to a destination CSV file. Click here to see the maximum number of output columns you can export.
SELECT
activationTable.{column1,column2}
FROM activationTable
For example:
SELECT
MultiOutput.{email,"cookie id",alternate_email,"Alternate Vehicle Registration Number"}
FROM MultiOutput
Creating additional columns (such as segment name)
You can add multiple columns of identical string values to every row in your campaign’s output CSV file. These string values can then be used by in-house/downstream systems to recognize each identifier in the campaign’s output CSV file. This is very useful for recognizing and grouping a list of identifiers into different audience segments across technology providers.
Select Add a column of string values to add one or more columns of identical string values to every row in your campaign’s output CSV file. Here, you can specify the column name and value for each column (there is no limit to the number of columns you can add). The example adds the column names Segment_1 and Segement_2 and the column values value_1 and value_2 as string values in your output file.
Segment_1 |
Segment_2 |
value_1 |
value_2 |
value_1 |
value_2 |
Select the Column Delimiter used to separate each output column in your destination CSV file, such as tab, colon, semi-colon, pipe, space or caret. The example uses comma as the column delimiter.
email,cookie id,HeaderOverride,Alternate Vehicle Registration Number,segment
Select a different delimiter as the Multi-value Column Delimiter - this delimiter is used to separate multiple values within single columns of your destination CSV file. The example uses pipe as the multi-value column delimiter.
|
test1@test.com|test2@test.co.uk|test3@test.com|test4@test.com |
Header override
Select Override queried column headers with new headers to rename column headers in your campaign (please contact your Customer Success Representative if override queried column headers are not available). The new column headers you type here replace the specified headers to override in your destination CSV file. Always include the name of the activation file when specifying the header to override. The example renames the column header MultiOutput.alternate_email to HeaderOverride.
HeaderOverride |
demo@test.com |
demo2@test.com |
How to activate the results of your query
If you've built an activation query, name the campaign and select Create campaign. You will then be taken to the Activation tab, where you can view the size of the resulting dataset and choose to push the results to an external destination.