Report API Method

Table of Contents:

Intro

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

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

e.g.

select

Required array

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

e.g.

from

Optional string

"mailing" (default) or "list"

group

Optional array

array of field names to group by

e.g.

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

e.g.

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.

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)

value

Optional string

The field that will be used for payload values.

e.g.

when sending:

The response will be serialized array of mailing IDs.

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:

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.

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.

Example - Retrieve all Campaigns in Last 30 Days

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

Example - Retrieve Stats Grouped by Campaign/Day/Domain

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

Example - Produce Matrix Report Grouped by ESP

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

Example - Produce Matrix Report Grouped by Domain

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

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. 

Example - Get link stats of specific Campaign

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

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

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

Example - Aggregated Campaign Report

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

Example - Response with "calculate_rates" ON

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

Example - Per Email Tag Report

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

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

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

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

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

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