Newsletter queries

Last updated
Save as PDF

Learn how to run queries for information about newsletters.

Queries: text items

In the following sections a number of queries. And also see par. 13.1 Result button and par. 13.5.3 Making selections.

For example, to find out which text items have been created:

SELECT id, title, valid_tot from newsletter_item;

SELECT * from newsletter;

SELECT * from newsletter_link where newsletter_id = 8;

The newsletter is a composition of text_items (with a newsletter_id) and is in the 'newsletter' table.

The link between 'newsletter' on the one hand and 'newsletter_items' on the other hand goes via the 'newsletter_link' table

A composite newsletter is included as a message with the actor in the message table. The child items to be sent for this actor are included in the table 'message_newsletter_items'.

For now, a copy is made from the table 'newsletter' to 'message_newsletter_items'. Later this process has to be adjusted by looking per customer which newsletter_items are relevant for the customer concerned. the corresponding (actor) labels.

During the 'gathering' both are filled.
In the 'processing', the XML is compiled on the basis of these items.

Query: number of newsletters sent

SELECT n.id, n.description, n.sent, count (*) as number from newsletter n, newsletter_actor a where a.newsletter_id = n.id and not isnull (n.verzonden) group by n.id;

E.g.
+ ---- + --------------- + ------------ + -------- +
| id | description sent number of
+ ---- + --------------- + ------------ + -------- +
| 7 | February 2014 | 2014-02-21 | 18577 |
+ ---- + --------------- + ------------ + -------- +

Query: how often clicked on link or logo shown

SELECT n.id, n.scription, n.sayed, count (*) as number from newsletter n, actor_web_doorlink d where d.function = 'NBF' and d.id = n.id and d.used> 0 and not isnull (n.solded) group by n.id;

E.g.

+ ---- + --------------- + ------------ + -------- +
| id | description sent number of
+ ---- + --------------- + ------------ + -------- +
| 7 | February 2014 | 2014-02-21 | 8683 |
+ ---- + --------------- + ------------ + -------- +

Query: how often clicked on maintenance newsletter

SELECT n.id, n.scription, n.sayed, count (*) as number from newsletter n, actor_web_doorlink d where d.function = 'AFM' and d.id = n.id and d.used> 0 and not isnull (n.solded) group by n.id;

E.g.
+ ---- + --------------- + ------------ + -------- +
| id | description sent number of
+ ---- + --------------- + ------------ + -------- +
| 7 | February 2014 | 2014-02-21 | 173 |
+ ---- + --------------- + ------------ + -------- +

Query: links used in the newsletter

SELECT link_id, description, count (*) as actor_clicks, sum (number) as total_clicks, count (distinct actor_id) as actors from actor_web_click c, newsletter_links l where l.id = c.link_id group by link_id;