CODEWITHSUNDEEP
tridion
user1841124
user1841124

Reputation: 29

SDL Tridion Mailing statistics using DB query

SDL tridion doesn't provide any feature that helps getting report on mailing statistics for multiple publication in desired format. I am using following query (please ignore optimization of query) to get desired result but it is different than what is being shown on interface. For example I am getting 67 count for link accessed on interface where in DB it is 97 using following query. Seems I am missing some filters. Any pointer on what is missing here would be great.

/*DB Query*/
select e.EMAIL_LINK_URL, COUNT(e.EMAIL_LINK_URL) as maxcount 
from Mailings a, EMAILS b, EMAILSTATUSES c, EMAILSTATUS_TYPES d, EMAIL_LINKURL e
where a.ID=b.MAILING_ID 
and b.ID=c.EMAIL_ID 
and c.STATUS_ID = d.ID 
and c.EMAIL_LINK_ID = e.ID 
and c.STATUS_ID = 5 (/*Status Type - 5 is for link accessed*/) 
and a.ID = 2628 (/*Mailing ID*/)
group by e.EMAIL_LINK_URL 
order by maxcount desc

Upvotes: 2

Views: 116

Answers (1)

Peter Kjaer
Peter Kjaer

Reputation: 4316

Although writing your own, specific database query will likely perform better than querying the API for each Mailing -- you can easily miss stuff, as you found out. The queries done and the design of the database tables might also change without notice in future versions.

The API is future-proof and supported; direct database queries are not (although it's true that we usually don't fret read-only queries)

For the reasons mentioned above, I would suggest that anyone looking for this kind of information would first attempt it using the Audience Management API.

Upvotes: 2

Related Questions