Consider the Event Queries as a more advanced type of campaign reports. It can provide a variety of contact data, such as details about your campaigns, Flows, further sorted with a date-range and much more! Let's dwell in.
Here is the list for all available event types for which you can run an event query in Loopify. Each has a number, that presents the EventType which is used for returning data.
Tip: Here's more info on Query Language - Elasticsearch Reference 7.3
General overview of queries
Queries are JSON objects with the following structure (examples and details below):
{ size: # number of results to return (defaults is 10) from: # offset into results (defaults to 0). Note that from + size can not be more than the index.max_result_window index setting which defaults to 10,000. query: { # The query element within the search request body allows to define a query using the Query DSL. # details below }, sort: # Allows to add one or more sort on specific fields. Each sort can be reversed as well. aggs : { # A single-value metrics aggregation that calculates an approximate count of distinct values. } }
Query DSL: Overview
Query objects are built up of sub-components. These sub-components are either basic (Leaf query clauses) or compound (Compound query clauses). Compound sub-components can contain other sub-components, while basic cannot. For example:
{ "query": { # compound component "bool": { # compound component "must": [ { # basic component "match": { "type": "11" }, }, { # basic component "term": { "data.campaignId": campaignId } } ] # compound component "filter": [ { # basic component "range": { "stamp": { "gte": "2018-02-02" } } } ] } } }
Here is one simple working query to get all sent emails (notice how the type of event is 5 from the QueryEvents list we mentioned above) on the current account :
{ "query": { "bool": { "must": [{ "match": { "type": "5" } }] } } }
and the same example used for a specific campaign:
{ "query": { "bool": { "must": [ { "match": { "type": "5" } }, { "term": { 'data.campaignId': "5xxxxxxxxxxxxxxxxxx" } } ] } } }
You can also search for sent campaigns within a specific date range. Here's an example with the EmailSent event (type: 5) with a range of one day before (yesterday):
{ "from": 0, "size": 20, "query": { "bool": { "must": [ { "match": { "type": "5" } }, { "range": { "stamp": { "gte": "now-1d/d", "lt": "now/d" } } } ] } } }
Note: In these examples, it will only return the first 20 items found. If you want more returned, you need to increase the size property.
To search for a combination of events in a specific date range, check out this example with EmailSent (type 5) and EmailOpened (type 10) events with a range of one day before (yesterday):
{ "from": 0, "size": 20, "query": { "bool": { "must": [ { "bool": { "should": [ { "match": { "type": "10" } }, { "match": { "type": "5" } } ] } }, { "range": { "stamp": { "gte": "now-1d/d", "lt": "now/d" } } } ] } } }
Example of searching for the last 24-hour performance on a 2 hours interval (EmailSent used):
{ "from": 0, "size": 20, "query": { "bool": { "must": [ { "match": { "type": "5" } }, { "range": { "stamp": { "gte": "now-1d", "lt": "now" } } } ] } }, "aggs": { "sent_over_time": { "date_histogram": { "field": "stamp", "interval": "2h" } } } }
Unique-contact search for last 24-hour performance on 3 hours interval (EmailSent used):
{ "from": 0, "size": 20, "query": { "bool": { "must": [ { "match": { "type": "10" } }, { "range": { "stamp": { "gte": "now-1d", "lt": "now" } } } ] } }, "aggs": { "sent_over_time": { "aggs": { "distinct_contacts": { "cardinality": { "field": "data.contactId.keyword" } } }, "date_histogram": { "field": "stamp", "interval": "3h" } } } }
Unique-contact search, with filtering the source (EmailOpened used):
{ "from": 0, "size": 20, "_source": ["data.contactId"], "query": { "bool": { "must": [ { "match": { "type": "10" } } ] } }, "aggs": { "distinct_contacts": { "cardinality": { "field": "data.contactId.keyword" } } } }
Note! This is filtering the return object. In the example, only the ContactID is returned in the response JSON. Multiple fields are allowed.
How to return campaigns that have more than one count in an activated Flow (EmailSent used):
{ "from": 0, "size": 0, "query": { "bool": { "must": [ { "match": { "type": "5" } }, { "term": { "data.flowActivationId.keyword": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" } } ] } }, "aggs":{ "by_district":{ "terms": { "field": "data.campaignId.keyword", "min_doc_count": 2, "size": 20 } } } }
Note! Another reminder that this returns only the first 20 items found. If you want more, you need to increase size property on aggregation (if not passed, 10 is the default value for size).
Use of nested aggregations to get the Total and Unique clicks per link:
{ "from": 0, "size": 0, "query": { "bool": { "must": [ { "match": { "type": "11" } } ] } }, "aggs":{ "link_clicks":{ "terms": { "field": "data.url.keyword", "size": "20" }, "aggs": { "unique_clicks": { "cardinality": { "field": "data.contactId.keyword" } } } } } }
Note! If specific campaign clicks are needed, include the CampaignID filter.
Comments
0 comments
Please sign in to leave a comment.