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.
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 functions are allowed and only on regular fields and virtual fields. They can't be used in Group.
- max
- min
- count
- sum
You may use this function in select, filter 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/reports/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 ] ] }