Table of Contents:

Intro

 Note: Notice that in each API call you must provide the List ID that you are working on, otherwise the default List ID will be used. 

Methods Summary

POST /api/reports/query

Description 

This method fetches reports data from "mailing_data". Those residing under the Analytics in the UI of your account. 

 Important notice: When querying from mailing, stats_date filter must exist. Otherwise, results will include data from last month only.

Request

list_ids

Optional array or "all" value

When sending "all" it will filter by your allowed list IDs

e.g.

[ "list_ids": "all" ]

Or you can send an array of specific list IDs to filter by

e.g.

[ "list_ids": [ 1001, 1002, 1003 ] ]

select

Required array

select fields, can be sent as string of the field name, or a 2 arguments array [field name, alias]

e.g.

[ "segment_id", "segment_name", [ "COUNT(clicks)", "total_clicks" ] ]

from

Optional string

"mailing" (default) or "list"

group

Optional array

array of field names to group by

e.g.

[ "email_message_id", "segment_id" ]

For grouping by dates, there are some predefined group aliases we have in the system: day, week, month, year.

e.g.

[ "stats_date", "day" ]

order

Optional array

array of field names to order by, or a 2 arguments array [field name, order direction] (order direction = ASC/DESC)

e.g.

[ "email_message_id", [ "segment_name", "DESC" ] ]

filter

Optional array

each filter is a 3 arguments array [field_name, operator, value]The optional field_names are written below at "All mailing_data optional fields:"

e.g (in the example below we show 2 filters used at once)

[
 [ "email_message_id", "=",  3001         ],
 [ "stats_date",       ">=", "2013-01-15" ]
]

value

Optional string

The field that will be used for payload values.

e.g.

when sending:

"value": "mailing_id"

The response will be serialized array of mailing IDs.

[ 811111, 822222, 833333 ]

offset

Optional number

format

Optional string

null (default) / "csv"

calculate_rates

Optional boolean

The response will include the counts rates.

include_conversion_points

Optional boolean

The response will include conversion points stats.

All list_data optional fields:

• record_date
• active
• not_active
• complaints
• unsubscribes
• bounces
• opened
• clicked
• no_activity

All mailing_data optional fields:

• mailing_id (is the CID of the campaign, and in the case of Events it is the Trigger ID)
• mailing_instance_id (is the ID of the trigger instance)
• mailing_type
• mailing_name
• split_type
• is_test_campaign (0/1 - will define if results will include test campaigns or not)
• email_message_id
• email_message_type
• email_message_name
• email_message_subject
• email_tag_id
• email_tag_title
• language_iso
• language_name
• segment_id
• segment_name
• esp_id
• esp_name
• esp_connection_id
• esp_connection_title
• isp_id
• isp_name
• link_id
• link_url
• mailing_sending_start_date
• mailing_sending_end_date
• event_id
• event_name
• stats_date
• schedule_date
• type
• from_name
• from_address
• reply_address
• targeted
• sent
• success
• failed
• hard_bounces
• soft_bounces
• complaints
• unsubscribes
• opens
• clicks
• unique_opens
• unique_clicks
• country_iso
• continent_iso
• platform_id
• platform_title
• platform_type
• browser_id
• browser_title
• Conversion points field names (extra1/extra2/extra3/extra4/extra5)

Virtual fields

In addition to the mailing_data optional fields, you may also use the following special virtual fields in you select:

Virtual string fields:

• mailing_name_id (string) [ string that contain the mailing name and the mailing id: "My campaign [12345]"]
• esp_name_title (string) [ string that contain the esp name and the esp connection title: "Dyn - my account" ]
• isp_name_or_others (string) [ string that contain the isp name if isp_id exist or the string "others" ]

Virtual sum fields:

• soft_and_hard_bounces (number) [ sum of soft_bounces + sum of hard_bounces ]
• sum_active_inactive (number) [ (sum of active and inactive contacts - this is only available in "list" table ]

Virtual rate fields

• ctr (number) [ (sum of clicks * 100) / sum of success ]
• uctr (number) [ (sum of unique_clicks * 100) / sum of success ]
• clicks_opens_rate (number) [ (sum of clicks * 100) / sum of opens ]
• unique_clicks_unique_opens_rate (number) [ (sum of unique_clicks * 100) / sum of unique_opens ]
• opens_rate (number) [ (sum of opens * 100) / sum of success ]
• unique_opens_rate (number) [ (sum of unique_opens * 100) / sum of success ]
• success_rate (number) [ (sum of success * 100) / sum of sent ]
• failure_rate (number) [ (sum of failed * 100) / sum of sent ]
• unsubscribes_rate (number) [ (sum of unsubscribes * 100) / sum of success ]
• complaints_rate (number) [ (sum of complaints * 100) / sum of success ]
• post_back_clicks_rate (number) [ (sum of post_back_clicks * 100) / sum of success ]
• hard_bounces_rate (number) [ (sum of hard_bounces * 100) / sum of success ]
• soft_bounces_rate (number) [ (sum of soft_bounces * 100) / sum of success ]

Conversion point virtual fields :

• extra{conversion_point_number}_rpm (int) e.g: extra1_rpm, extra2_rpm.. [ sum of the conversion point * 100 / sum of sent]

Example of usage:

 "select": [ "ctr", "uctr", ["failure_rate", "failure_rate_custom_name"], "extra4_rpm" ] 

Operator options 

For strings / emails

* = : Equals

* != : Not equals

* LIKE : Contains

* NOT LIKE : Doesn't contain

* LIKE_ : Begins with

* _LIKE : Ends with

* empty : Is empty

* notempty : Is not empty

For numeric / id

* < : Is lower than

* <= : Is lower than or equals

* > : Is greater than

* >= : Is greater than or equals

* = : Equals

* != : Is different from

* empty : Is empty

* notempty : Is not empty

* >< : Range

For dates

* < : Before

* <= : Before or on

* > : After

* >= : On or after

* = : On

* != : Not on

* empty : Is empty

* notempty : Is not empty

Aggregation functions

Only the following aggregation function are allowed and only on regular fields (aggregation functions are not allowed on virtual fields)

• max
• min
• count
• sum

You may use this function in select, filter, group and order.

e.g.

select: [ ["min(schedule_date)", "min_schedule_date"], [ "sum(clicks)", "sum_of_clicks" ] ]

Special mailing_data filters

• is_mailing_deleted

Error codes

412 - Missing mandatory fields

Protected boolean

Parameters

$aggregation_value$allowed_aggregate_fields$key

Returns

boolean

Throws

Ongage\ResourceBundle\Model\Exception\InvalidData

Examples

Example - Retrieve Stats from All Lists

POST URL: https://api.ongage.net/api/reports/query

Please note list_ids: "all" option used in following sample JSON.

{
   "select":[
      "mailing_name",
      [
         "MAX(`stats_date`)",
         "stats_date"
      ],
      "sum(`sent`)",
      "sum(`success`)",
      "sum(`failed`)",
      "sum(`opens`)",
      "sum(`unsubscribes`)",
      "sum(`complaints`)",
      "sum(`clicks`)",
      "sum(`unique_clicks`)",
      "list_id"
   ],
   "from":"mailing",
   "group":[
      "list_id",
      "mailing_id"
   ],
   "list_ids" : "all",
   "get_extra_conversion_points":true,
   "filter":[
      [
         "mailing_type",
         "!=",
         "transactional"
      ],
      [
         "stats_date",
         ">=",
         "2015-05-01"
      ],
      [
         "stats_date",
         "<=",
         "2015-06-20"
      ]
   ]
}

{ 
   "list_ids":[ 
      "1111"
   ],
   "select":[ 
      "mailing_name",
      "mailing_id",
      "stats_date"
   ],
   "filter":[ 
      [ 
         "stats_date",
         ">=",
         "2016-09-22"
      ],
      [ 
         "schedule_date",
         ">=",
         "2016-09-22"
      ]
   ],
   "from":"mailing",
   "group":[ 
      "mailing_id"
   ]
}

{ 
   "select":[ 
      "mailing_name",
      [ 
         "MAX(`stats_date`)",
         "stats_date"
      ],
      "isp_name",
      "sum(`sent`)",
      "sum(`success`)",
      "sum(`failed`)",
      "sum(`opens`)",
      "sum(`unique_opens`)",
      "sum(`unsubscribes`)",
      "sum(`complaints`)",
      "sum(`clicks`)",
      "sum(`unique_clicks`)"
   ],
   "from":"mailing",
   "group":[ 
      "mailing_id",
      [ 
         "stats_date",
         "day"
      ],
      "isp_id"
   ],
   "order":[ 
      [ 
         "stats_date",
         "desc"
      ]
   ],
   "filter":[ 
      [ 
         "is_test_campaign",
         "=",
         0
      ],
      [ 
         "stats_date",
         ">=",
         "2015-01-01"
      ]
   ],
   "calculate_rates":true
}

{
   "filter":[
      [
         "is_test_campaign",
         "=",
         0
      ],
      [
         "email_message_type",
         "=",
         "email_message"
      ],
      [
         "stats_date",
         ">=",
         1615919400
      ],
      [
         "stats_date",
         "<=",
         1617215399
      ]
   ],
   "select":[
      [
         "MAX(`stats_date`)",
         "stats_date"
      ],
      [
         "MAX(`delivery_timestamp`)",
         "delivery_timestamp"
      ],
      "sum(`targeted`)",
      "sum(`sent`)",
      "sum(`success`)",
      "sum(`failed`)",
      "sum(`hard_bounces`)",
      "sum(`soft_bounces`)",
      "sum(`opens`)",
      "sum(`unique_opens`)",
      "sum(`clicks`)",
      "sum(`unique_clicks`)",
      "sum(`unsubscribes`)",
      "sum(`complaints`)",
      "esp_name_title",
      "isp_name_or_others",
      "sum(`post_back_clicks`)"
   ],
   "from":"mailing",
   "order":[
      [
         "MAX(`delivery_timestamp`)",
         "desc"
      ]
   ],
   "group":[
      "esp_connection_id"
   ]
}

{
   "filter":[
      [
         "is_test_campaign",
         "=",
         0
      ],
      [
         "email_message_type",
         "=",
         "email_message"
      ],
      [
         "stats_date",
         ">=",
         1615919400
      ],
      [
         "stats_date",
         "<=",
         1617215399
      ]
   ],
   "select":[
      [
         "MAX(`stats_date`)",
         "stats_date"
      ],
      [
         "MAX(`delivery_timestamp`)",
         "delivery_timestamp"
      ],
      "sum(`targeted`)",
      "sum(`sent`)",
      "sum(`success`)",
      "sum(`failed`)",
      "sum(`hard_bounces`)",
      "sum(`soft_bounces`)",
      "sum(`opens`)",
      "sum(`unique_opens`)",
      "sum(`clicks`)",
      "sum(`unique_clicks`)",
      "sum(`unsubscribes`)",
      "sum(`complaints`)",
      "esp_name_title",
      "isp_name_or_others",
      "sum(`post_back_clicks`)"
   ],
   "from":"mailing",
   "order":[
      [
         "MAX(`delivery_timestamp`)",
         "desc"
      ]
   ],
   "group":[
      "isp_id"
   ]
}

{
    "select": [
    "mailing_type",
    "event_id",
    "mailing_id",
    "mailing_instance_id",
    "mailing_name",
    "event_name",
    "email_message_id",
    "email_message_name",
    "sum(`sent`)",
    "sum(`success`)",
    "sum(`opens`)",
    "sum(`clicks`)",
    "sum(`hard_bounces`)",
    "sum(`soft_bounces`)",
    "sum(`unsubscribes`)",
    "sum(`complaints`)"
    ],
    "filter": [
        [ "stats_date", ">=", "2015-07-01" ]
    ],
    "from": "mailing",
    "group": [     "mailing_type",
    "event_id",
    "mailing_id",
    "mailing_instance_id",
    "mailing_name",
    "event_name",
    "email_message_id",
    "email_message_name" ],
    "order": []
}

{
    "select": [
    "link_id",
        [ "sum(`sent`)", "total_sent" ]
    ],
    "filter": [
        ["mailing_id",  "=",  555555       ],
        [ "stats_date", ">=", "2013-01-15" ]
 ],
    "from": "mailing",
    "group": [ "link_id" ],
    "order": []
}

{
 "select":[
 "mailing_name",
 "sum(`sent`)",
 "sum(`opens`)",
 "sum(`unique_opens`)",
 "sum(`unsubscribes`)",
 "sum(`complaints`)",
 "sum(`hard_bounces`)",
 "sum(`soft_bounces`)",
 "sum(`clicks`)",
 "sum(`unique_clicks`)"
 ],
 "from":"mailing",
 "filter":[
 [
 "is_test_campaign",
 "=",
 0
 ],
 [
 "mailing_name",
 "NOT LIKE",	// you can use LIKE here //
 "%_Text%"		// TEXT here is any word in your campaign names which you want to use to ADD/DISCARD to get the reports. //
 ],
 [
 "stats_date",
 ">=",
 "2021-03-01"
 ]
 ],
 "group":[
 "mailing_name"
 ],
 "list_ids" : "all",

"get_extra_conversion_points":true
}

Example - Aggregated Campaign Report

POST URL: https://api.ongage.net/<list_id>/api/reports/query

{
 "select": [
  "mailing_name",
  ["MAX(`stats_date`)", "stats_date"],
  "sum(`sent`)", "sum(`success`)", "sum(`failed`)", "sum(`opens`)", "sum(`unique_opens`)", "sum(`unsubscribes`)",
  "sum(`complaints`)", "sum(`clicks`)", "sum(`unique_clicks`)"
 ],
 "from": "mailing",
 "group": [ "mailing_id" ],
 "order": [ [ "mailing_name", "asc" ] ],
 "filter": [
   [ "is_test_campaign",   "=",  0            ],
   [ "stats_date",         ">=", "2013-01-15" ]
 ]
}

Example - Response with "calculate_rates" ON

POST URL: https://api.ongage.net/<list_id>/api/reports/query

{
  "mailing_id": "78121",
  "mailing_name": "2121",
  "sent": "2",
  "success": "1",
  "failed": "1",
  "opens": "0",
  "unique_opens": "0",
  "unsubscribes": "0",
  "complaints": "0",
  "clicks": "0",
  "unique_clicks": "0",
  "success_percent": "50%",
  "failed_percent": "50%",
  "opens_percent": "0%",
  "clicks_percent": "0%",
  "unsubscribes_percent": "0%",
  "complaints_percent": "0%",
  "unique_clicks_percent": "0%",
  "unique_opens_percent": "0%"
}

Example - Per Email Tag Report

POST URL: https://api.ongage.net/<list_id>/api/reports/query

{
 "select": [
  "email_tag_id", "email_tag_title", "mailing_name", "email_message_name",
  ["MAX(`stats_date`)", "stats_date"],
  "sum(`sent`)", "sum(`success`)", "sum(`failed`)", "sum(`opens`)", "sum(`unique_opens`)", "sum(`unsubscribes`)",
  "sum(`complaints`)", "sum(`clicks`)", "sum(`unique_clicks`)"
 ],
 "from": "mailing",
 "group": [ "email_tag_id" ],
 "order": [ [ "email_tag_id", "asc" ] ],
 "filter": [
   [ "is_test_campaign",   "=",  0            ],
   [ "stats_date",         ">=", "2013-01-15" ]
 ]
}

Example - Retrieving Results of A/B Split Test Per Email / Email subject split type

POST URL: https://api.ongage.net/<list_id>/api/reports/query

{
   "select":[
      "email_message_name",
      "sum(`sent`)",
      "sum(`success`)",
      "sum(`hard_bounces`)",
      "sum(`soft_bounces`)",
      "sum(`opens`)",
      "sum(`unique_opens`)",
      "sum(`clicks`)",
      "sum(`unique_clicks`)",
      "sum(`unsubscribes`)",
      "sum(`complaints`)"
   ],
   "from":"mailing",
   "group":[
      "email_message_id"
   ],
   "filter":[
      [
         "mailing_id",
         "=",
         123456
      ],
      [
         "stats_date",
         ">=",
         1456519557
      ],
      [
         "stats_date",
         "<=",
         1460839557
      ]
   ]
}

Example - Retrieving Results of A/B Split Test Per ESP split type

POST URL: https://api.ongage.net/<list_id>/api/reports/query

{
   "select":[
      [
         "case when esp_connection_title!='' then CONCAT(esp_name, ' - ', esp_connection_title) else `esp_name` end",
         "esp_connection_title"
      ],
      "sum(`sent`)",
      "sum(`success`)",
      "sum(`hard_bounces`)",
      "sum(`soft_bounces`)",
      "sum(`opens`)",
      "sum(`unique_opens`)",
      "sum(`clicks`)",
      "sum(`unique_clicks`)",
      "sum(`unsubscribes`)",
      "sum(`complaints`)"
   ],
   "from":"mailing",
   "group":[
      "esp_connection_id"
   ],
   "filter":[
      [
         "mailing_id",
         "=",
         123456
      ],
      [
         "stats_date",
         ">=",
         1456519557
      ],
      [
         "stats_date",
         "<=",
         1460839557
      ]
   ]
}

Example - Retrieving Results of A/B Split Test Per Segment split type

POST URL: https://api.ongage.net/<list_id>/api/reports/query

{
   "select":[
      "segment_name",
      "sum(`sent`)",
      "sum(`success`)",
      "sum(`hard_bounces`)",
      "sum(`soft_bounces`)",
      "sum(`opens`)",
      "sum(`unique_opens`)",
      "sum(`clicks`)",
      "sum(`unique_clicks`)",
      "sum(`unsubscribes`)",
      "sum(`complaints`)"
   ],
   "from":"mailing",
   "group":[
      "segment_id"
   ],
   "filter":[
      [
         "mailing_id",
         "=",
         123456
      ],
      [
         "stats_date",
         ">=",
         1456519557
      ],
      [
         "stats_date",
         "<=",
         1460839557
      ]
   ]
}