deMarketing

The main data table used to analyze Marketing data is deMarketing. Most of the data is collected from the S_SENT_MESSAGE and S_SHIPMENT tables. This is what it contains when shipped from us:

Based on (table.column) Type Column alias Localised english name Comment
Calculated values        
NrImage / NrSent * 100 C CalculatedColumn3 Open rate (%)  
NrLink, NrImage C CalculatedColumn2 Response Status Output localized text
nr_link / nr_sent * 100 C CalculatedColumn Response rate  
         
Categories        
contact.name C ContactName Company  
person.lastname, person.firstname, person.middlename P PersonName(Last,First) Person name (Last,First)  
business.name P Business Business  
category.name P Category Category  
s_shipment.description P Shipment Mailing  
s_shipment.status P StatusText Mailing  
s_sent_message.email P Email E-mail  
         
Value column        
s_sent_message.id E NrSent No. sent Do distinct count
s_shipment.id E NrShipment No. shipments Do distinct count
s_link_customer.id P Total Total Used for simple grouping
s_link_customer.id, s_shipment.shipment_id, s_link_customer.shipment_id,
s_shipment.customer_id, s_link_customer.customer_id		 E NrLink No. clicked links s_link_customer.link_type=1
s_link_customer.id, s_shipment.shipment_id, s_link_customer.shipment_id,
s_shipment.customer_id, s_link_customer.customer_id		 E NrImage No. opened s_link_customer.link_type=2
s_shipment.when_sent, s_link_customer.id, s_shipment.shipment_id,
s_link_customer.shipment_id, s_shipment.customer_id, s_link_customer.customer_id		 E LinkResponseTime Link response time s_link_customer.link_type=1
s_shipment.when_sent, s_link_customer.id, s_shipment.shipment_id,
s_link_customer.shipment_id, s_shipment.customer_id, s_link_customer.customer_id		 E ImageResponseTime Image response time s_link_customer.link_type=2
         
Dates        
start_date P StartDate Start Date  
start_date P StartDate2 Start Date2  
when_sent P SentDate Sent Date  
when_sent P SentDate2 Sent Date2  
when_read P ReadDate Read Date  
when_read P ReadDate2 Read Date2  
end_date P EndDate End Date  
end_date P EndDate2 End Date2  
s_link_customer.pressed, s_shipment.shipment_id, s_link_customer.shipment_id,
s_shipment.customer_id, s_link_customer.customer_id		 E ImageDate Image Date s_link_customer.linktype=2,
s_link_customer_statical.link_type=2
s_link_customer.pressed, s_shipment.shipment_id, s_link_customer.shipment_id,
s_shipment.customer_id, s_link_customer.customer_id		 E LinkDate Link Date s_link_customer.linktype=1,
s_link_customer_statical.link_type=1
         
For filtering        
s_shipment.id P ShipmentId ShipmentId  
s_picture_folder.id P FolderId FolderId  
s_shipment.id P Status Status  
contact.contact_id P ContactId ContactId  
person.person_id P PersonId PersonId  
contact.business_id P BusinessId BusinessId  
contact.category_id P CategoryId CategoryId