Report API Method

Table of Contents:

Intro

This is the primary Aggregated reports API method.

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!

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

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