Report API Method

Table of Contents:

Intro

This is the primary aggregated reports API method.

In fact, all the Analytics reports one finds in the Ongage UI, such as the Aggregate report, Matrix report, Transactional, etc. are all generated with this API method.

(info) 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. 

(info) 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"
      ]
   ]
}

Example - Retrieve all Campaigns in Last 30 Days

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

{ 
   "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"
   ]
}

Example - Retrieve Stats Grouped by Campaign/Day/Domain

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

{ 
   "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
}

Example - Produce Matrix Report Grouped by ESP

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

{
   "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"
   ]
}

Example - Produce Matrix Report Grouped by Domain

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

{
   "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"
   ]
}

Example - Analytics Report Using Complex Grouping

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

This report includes both Events and Campaigns Stats in the same report. 

{
    "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": []
}

Example - Get link stats of specific Campaign

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

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

Example - Get aggregated campaign report with NOT LIKE/LIKE Operator

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

{
 "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
      ]
   ]
}