magnews provides several internal tables that can be queried using MQL to read data available within the platform. Queries on these tables are read-only; to modify data, you must use specific functions such as mergeContact.
Access to certain tables may require specific permissions, such as the permission to export data from the database.
CONTACTS
The system tables contacts contains all of your contacts.
There is a column for each "field", such as 'email', 'status', 'timezone'....
Special columns:
| Column | Type | Description |
|---|---|---|
| idcontact | Integer | Id of the contact |
| iddatabase | Integer | Id of the database of the contact |
| status | Integer |
Returns a number representing the status of the contact: '0' for subscribed, '97' for unconfirmed, '98' for suspended and '99' for unsubscribed. Use the text value in the query like |
| String | Email of the contact | |
| stats.idchannel | Integer | Id of the delivery channel.stats.idchannel=0 for EMAILstats.idchannel=1 for SMS
|
| stats.ipaddress | Datetime | IP address of delivery of the last opened message deliveries |
| stats.totalsent | Integer | Number of email messages sent using deliveries. If no channel is specified (using stats.idchannel) you will get a record for each channel for which at least one message has been sent |
| stats.totalreceived | Integer | Number of email messages received using deliveries |
| stats.totalhardbounced | Integer | Number of hard-bounced email messages sent using deliveries |
| stats.totalsoftbounced | Integer | Number of soft-bounced email messages sent using deliveries |
| stats.totalopened | Integer | Number of opened email messages sent using deliveries |
| stats.totalclicked | Integer | Number of clicked email messages sent using deliveries |
| stats.totalcomplainted | Integer | Number of complained email messages sent using deliveries |
| stats.totalforwarded | Integer | Number of forwarded email messages sent using deliveries |
| stats.totalunsubscribed | Integer | Number of unsubscribed email messages sent using deliveries |
| stats.totalconverted | Integer | Number of converted email messages sent using deliveries |
| stats.totalopens | Integer | Number of total opens on email messages sent using deliveries |
| stats.totalclicks | Integer | Number of total clicks on email messages sent using deliveries |
| stats.firstsent | Datetime | Timestamp of delivery of the first sent message using deliveries |
| stats.firstreceived | Datetime | Timestamp of delivery of the first sent message which has been received using deliveries |
| stats.firstopen | Datetime | Timestamp of delivery of the first opened message deliveries |
| stats.firstclick | Datetime | Timestamp of delivery of the first clicked message deliveries |
| stats.firstforwarded | Datetime | Timestamp of delivery of the first forwarded message deliveries |
| stats.firstcomplainted | Datetime | Timestamp of delivery of the first complained message deliveries |
| stats.firstunsubscribed | Datetime | Timestamp of delivery of the first unsubscribed message deliveries |
| stats.firstconverted | Datetime | Timestamp of delivery of the first converted message deliveries |
| stats.lastsent | Datetime | Timestamp of delivery of the last sent message using deliveries |
| stats.lastreceived | Datetime | Timestamp of delivery of the last sent message which has been received using deliveries |
| stats.lastopen | Datetime | Timestamp of delivery of the last opened message deliveries |
| stats.lastopenevent | Datetime | Timestamp of the last event which is considered an open |
| stats.lastclick | Datetime | Timestamp of delivery of the last clicked message deliveries |
| stats.lastforwarded | Datetime | Timestamp of delivery of the last forwarded message deliveries |
| stats.lastcomplainted | Datetime | Timestamp of delivery of the last complained message deliveries |
| stats.lastunsubscribed | Datetime | Timestamp of delivery of the last unsubscribed message deliveries |
| stats.lastconverted | Datetime | Timestamp of delivery of the last converted message deliveries |
| stats.engagementlevel | int | Numeric representation of the engagement level: 10 = Contact not subscribed, 20 = Out of recency, 30 = Never-openers, 40 = Ex-openers, 50 = New, 60 = Opener, 70 = Clicker |
| slistsubscription.list_IDLIST.subscriptionstatus | String | Status of explicit subscription for special list IDLIST |
| slistsubscription.list_IDLIST.subscriptiondate | Datetime | Timestamp of subscription for special list IDLIST |
| slistsubscription.list_IDLIST.unsubcriptiondate | Datetime | Timestamp of unsubscription for special list IDLIST |
| slistsubscription.list_IDLIST.subscriptionlastmoddate | Datetime | Timestamp of subscription last modification date for special list IDLIST |
System tables: GROUPS
Contains all audiences of contacts (dynamic and static).
| Column | Type | Description |
|---|---|---|
| idgroup | Integer | Id of the audience |
| name | String | Name of the audience |
| creationdate | Datetime | Timestamp of creation of the audience |
| lastmodificationdate | Datetime | Timestamp of last modification of the audience |
System tables: STATICGROUPMEMBERS
Includes the association between contacts and static audiences.
Each row links a contact to a static audience.
| Column | Type | Description |
|---|---|---|
| idgroup | Integer | Id of the audience |
| idcontact | String | Id of the contact |
System tables: CONTENTS
Contains the contents.
| Column | Type | Description |
|---|---|---|
| idcontent | Integer | Id of content |
| name | String | Name of the content |
| creationdate | Datetime | Timestamp of creation of the content |
| lastmodificationdate | Datetime | Timestamp of last modification of the content |
| taglist | String | List of tags associated to the content (only for WHERE clauses, not for SELECT) |
| template | String | Type of content, for example 'article' |
| totalclicks | Integer | Total count of clicks on messages sent (only on deliveries, not transactional messages) |
System tables: NEWSLETTERS
Contains all the communications.
| Column | Type | Description |
|---|---|---|
| idnewsletter | Integer | ID of the communication |
| idworkflow | Integer | ID of the automated flow referred to the communication (only for notification messages linked to an automated flow) |
| idcampaign | Integer | ID of the journey |
| name | String | Name of the communication |
| subject | String | Subject of the communication (email channel) |
| emailfrom | String | From header of the communication (email channel) |
| creationdate | Datetime | Timestamp of creation of the communication |
| sentdate | Datetime | First final delivery of the communication |
| sent | Boolean | True means the communication has been sent at least once (using deliveries, not transactional messages) |
| draft | Boolean | True means the communication has never been sent (using deliveries, not transactional messages) |
| template | Boolean | True means the communication is a transactional communication |
| emailchannelenabled | Boolean | True means the communication can be used to send email messages |
| smschannelenabled | Boolean | True means the communication can be used to send SMS messages |
| faxchannelenabled | Boolean | True means the communication can be used to send fax messages |
| status | String | 'Active' or 'deleted' |
| designertype | String | 'Titano' or 'giotto' |
System tables: WEBPAGES
Contains single pages linked in landing pages.
| Column | Type | Description |
|---|---|---|
| idwebpage | Integer | Id of the webpage |
| idwebsite | Integer | Id of the website |
| name | String | Name of the webpage |
System tables: SURVEYS
Includes landing pages
| Column | Type | Description |
|---|---|---|
| idsurvey | Integer | Id of the survey |
| name | String | Name of the survey |
| status | String | 'Active' or 'deleted' |
| creationdate | Datetime | Timestamp of creation of the survey |
| lastmodificationdate | Datetime | Timestamp of last modification of the survey |
System tables: SURVEYSESSIONS
Includes survey sessions. Every row represents a survey submission by a contact.
| Column | Type | Description |
|---|---|---|
| idsurvey | Integer | Id of the survey |
| idsurveysession | Integer | Id of the survey session (to be composed with idsurvey) |
| contact | Integer | Id of the contact |
| completed | Boolean | True means the session has been marked as 'completed', false means the session is still active but not yet 'completed' |
| status | String | 'Completed' means the session has been marked as 'completed', 'active' means the session is active but not yet 'completed', 'deleted' means the session has been hidden/deleted |
| startdate | Datetime | Timestamp of the beginning of the session |
| lastevent | Datetime | Timestamp of the last event on the session (useful for incremental downloads of data) |
| ipaddress | String | IP address of the broswer (in case of use of dynamic addresses this is the first IP address used on the session) |
| testsession | Boolean | True means the test submissions will be marked as 'test'. |
System tables: WORKFLOWS
Contains all the existing automated flows.
| Column | Type | Description |
|---|---|---|
| idworkflow | Integer | ID of the automated flow |
| name | String | Name of the automated flow |
| idcampaign | Integer | ID of the journey the automated flow belongs to |
| creationDate | Datetime | Date of creation of the automated flow |
System tables: WORKFLOWSESSIONS
Contains all the active sessions of all existing automated flows.
| Column | Type | Description |
|---|---|---|
| idworkflowsession | Integer | ID of the session |
| contact | Integer | ID of the contact |
| idworkflow | Integer | ID of the automated flow |
| idstartingnode | Integer | Node to start with |
| startdate | Datetime | Starting date of the session |
| lastevent | Datetime | Last modification date of the session |
| testsession | Boolean | True means it is a test session |
System tables: WORKFLOWSESSIONSFINISHED
Contains all the ended sessions of all existing automated flows.
| Column | Type | Description |
|---|---|---|
| idworkflowsession | Integer | ID of the session |
| contact | Integer | ID of the contact |
| idworkflow | Integer | ID of the automated flow |
| status | String | Status of the session. Values: positive, neutral, error, negative, other |
| idstartingnode | Integer | Node to start with |
| startdate | Datetime | Starting date of the session |
| lastevent | Datetime | Last modification date of the session |
| testsession | Boolean | True means it is a test session |
System tables: SIMPLEMESSAGES
Contains all the transactional messages which have been processed
| Column | Type | Description |
|---|---|---|
| idsimplemessage | Integer | Id of the message |
| idcontact | Integer | Id of the contact |
| messagetype | String | Type of message ('email','sms',fax') |
| sentts | Datetime | Timestamp of sending |
| opened | Boolean | True means the message has been opened by the recipient |
| clicked | Boolean | True means the message has been clicked by the recipient |
| complainted | Boolean | True means the contact reported a spam complaint on the message |
| idmessagetype | Integer | Id of the message type |
| status | String | Status of sent messages, used only to make a filter (can be 'notsent' or 'error'). It doesn't consider bounced messages). Example: SELECT idsimplemessage from SIMPLEMESSAGES where status = 'notsent' or status='error' |
| notsentcause | String | Not sent cause as String for the message. Values: 'unknown', 'blacklisted_recipient', 'cancelled', 'unsubscribed', 'forbidden_from', 'forbidden_replyto', 'maxsize_exceeded', 'phonerecipient_notallowed', 'empty_recipient', 'forbidden_smsfrom', 'forbidden_smsnumericfrom', 'no_credit' |
| idbouncecategory | Integer | Bounce category ID. Use describeBounceCategories to get the category id mapping |
| lastupdatets | Datetime | Timestamp of the last event on the message (such as delivery, bounce receipt, click...) |
| externalID | String | The external assigned ID of the message |
| idnewsletter | Integer | Id of the transactional communication |
| destination | String | Type of message ('backendusers' for user messages, 'contacts' for contact messages) |
| idwfsession | Integer | ID of the automated flow session for which the message was sent |
System tables: RECEIVEDMESSAGES
Contains all the received messages
| Column | Type | Description |
|---|---|---|
| idreceivedmessage | Integer | ID of the received message |
| msgfrom | String | From of the message |
| messagetype | String | Type of message (can be 'email' or 'sms') |
| msgto | String | Recipient of the message |
| msgbody | String | (Processed) body of the message |
| idfolder | Integer | ID of the MESSAGERECEIVEDFOLDER |
| msgsubject | String | Subject of the message (only for email) |
| msgreplyto | String | Reply to header of the message (only for email) |
| creationdate | Datetime | Timestamp of receipt of the message |
| lastmodificationdate | Datetime | Timestamp of last modification of the message |
| status | String | Status of the message (can be 'active' or 'deleted'), used only for WHERE clauses |
System tables: RECEIVEDMESSAGESFOLDERS
Contains all the received messages
| Column | Type | Description |
|---|---|---|
| idreceivedmessagefolder | Integer | id of the folder |
| name | String | name of the folder |
| status | String | Status of the folder (can be 'active' or 'deleted'), used only for WHERE clauses |
System tables: CONTENTTAGSTATS
Contact statistics related with content tags
| Column | Type | Description |
|---|---|---|
| idcontact | String | Id of the contact |
| tagname | String | Tag name |
| lastclick | Datetime | Last click on a content with the specified tag |
| lastopen | Datetime | Last open on a content with the specified tag |
| totalclicks | int | Number of clicks on a content with the specified tag |
| totalopens | int | Number of openings on a content with the specified tag |
System tables: NEWSLETTERTAGSTATS
Contact statistics related with communication tags
| Column | Type | Description |
|---|---|---|
| idcontact | String | Id of the contact |
| tagname | String | Tag name |
| lastclick | Datetime | Last click on a communication with the specified tag |
| lastopen | Datetime | Last open on a communication with the specified tag |
| totalclicks | int | Number of clicks on a communication with the specified tag |
| totalopens | int | Number of opens on a communication with the specified tag |
System tables: CONVERSIONEVENTS
Represents the conversion events
| Column | Type | Description |
|---|---|---|
| idcontact | Integer | Id of the contact who converted |
| iddelivery | Integer | Id of the delivery, if the conversion occurred from a delivered communication |
| idsimplemessage | Integer | Id of the transactional message, if the conversion occurred from a transactional communication or automated flow message |
| idsurvey | Integer | Id of the survey, if the conversion occurred from a landing page |
| idgoal | Integer | Id of the conversion goal |
| goalname | String | Name of the conversion goal |
| eventts | Datetime | Timestamp of the event |
| value | Decimal | Value of the conversion (decimal separator '.') |