How To Export Clickers Or Opens From the Mailwizz Database

BJA

New Member
Hi Guys,

I wanted to share a query to select openers/clicks from a given campaign or click url. You can to edit this
query to fit your needs as you may find dupes or remove unsubscribe links.

/****** Export Openers from Campaign ****/
-- CAMPAIGN UID is the frontend mailwizz campaign id.
set @campaignid = '<CAMAPIGN UID>';

select email,a.ip_address, subject, a.date_added as dateofopen
from mw_campaign_track_open a inner join mw_campaign b
on a.campaign_id =b.campaign_id
inner join mw_list_subscriber c on c.subscriber_id = a.subscriber_id
where b.campaign_uid = @campaignid ;


/****** Export Clickers from Campaign ****/

-- Set a specific Campaign id
set @campaignid = '<CAMAPIGN UID>';

select email, a.ip_address, destination , a.date_added as dateofclick
from mw_campaign_track_url a inner join mw_campaign_url b
on a.url_id =b.url_id
inner join mw_campaign c on c.campaign_id=b.campaign_id
inner join mw_list_subscriber d on d.subscriber_id = a.subscriber_id
where c.campaign_uid = @campaignid ;


/****** Export ALL DB Clickers of a given URL ****/

select email, a.ip_address, destination , a.date_added as dateofclick
from mw_campaign_track_url a inner join mw_campaign_url b
on a.url_id =b.url_id
inner join mw_campaign c on c.campaign_id=b.campaign_id
inner join mw_list_subscriber d on d.subscriber_id = a.subscriber_id
where destination like '%<ADD URL>%';
 
Hi,

I have a sql query that will insert till openers to new database with email, opendate, subscriberid and listid field, Then you can import data to mailwizz from database live import. Mailwizz does not have database queue else this could have been set as cron.
 
This allows you to get historical data, so that you can get results from campaigns/urls in the past etc
I thought the query may open up some creative people to start to do some analysis...
 
Back
Top