Skip to main content
Italiano
Help Desk

Tables available for querying with MQL

Updated

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 SELECT * FROM CONTACTS WHERE status='subscribed'
email String Email of the contact
stats.idchannel Integer Id of the delivery channel.
stats.idchannel=0 for EMAIL
stats.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

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

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

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)

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'

DELIVERIES

Represents the list of deliveries created when a communication is sent via a scheduled delivery (not through transactional messages or workflows), whether for a test or an actual send.

Colonna Tipo Descrizione
iddelivery Integer ID of the delivery
idnewsletter Integer ID of the communication
trial Boolean True means that this delivery is a test
status String
The current status of the delivery. 
Possible values are:
  • unconfirmed: The delivery has not been confirmed.
  • scheduled: The delivery is waiting to be sent at the scheduled time.
  • sending: The delivery is being prepared or sent.
  • error: An error occurred during preparation or sending.
  • finished: The delivery has completed successfully.
  • paused: The delivery has been paused.
  • stopped: The delivery was interrupted or stopped.
target String A list of audience ID (comma separated) representing the delivery target
targetUsesAdditionalFilter Boolean True indicates that an additional filter has been applied to the contacts in the delivery target
requestedMessages String

A map representing the number of messages for which the send was requested, broken down by channel, e.g.:

"requestedMessages": {
       "sms": 0,
       "email": 12
   }
startDate Datetime Start date and time of the delivery (actual or scheduled)
schedule String

A map describing the delivery schedule; for example, if the delivery is immediate, the value will be:

"schedule": {
       "type": "immediate"
   }

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

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

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

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

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

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

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

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

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

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

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

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 '.')