Mautic

Mautic: Cleaning Database

Cleaning the DB depends on your use case. In our company this will work, as we want to keep the DB small and organized, and have a bunch of manual integrations between multiple systems and this Mautic instance.

We specifically do not want to truncate the email_stats table, as this table has a record of all the emails sent to each lead, this is something we would like.

If you truncate email_stats, you will loose the record of which emails have been sent to which recipients, but if you (for example), want to "reset" your system and resend emails to all your users from zero, this may be an option.

 Note that if you have set the type of message as Marketing (meaning the user won't get the same email twice), then truncating the email_stats table will remove this constraint  (as the system will assume no email has been sent to this user) and they will receive the email again.

 

Note: Prior to doing any of this, backup you DB, as you may have an unwanted outcome. If that should be the case you can always restore your backup.

 


TRUNCATE audit_log;

 


SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE ip_addresses;
SET FOREIGN_KEY_CHECKS = 1;

 


DELETE

FROM
lead_points_change_log

WHERE
YEAR(date_added) = YEAR(NOW())
AND MONTH(date_added) < (MONTH(NOW()) - 1);

 


This shows every time the lead has been added to a segment or a campaign. It has no relevance to us. We're deleting everything older than a WEEK.

DELETE
FROM
lead_event_log
WHERE
(YEAR(date_added) = YEAR(NOW())
AND WEEK(date_added) < WEEK(NOW()) - 1);

 


We do not care which device our users are connecting from, as we're a B2B business most of our customers will connect via office computer.

TRUNCATE email_stats_devices;

 


DELETE
FROM
page_hits
WHERE
(YEAR(date_hit) = YEAR(NOW())
AND MONTH(date_hit) < MONTH(NOW()) - 1);

 


This is very tricky and you should truncate only if you know what you're doing. This table show every time a campaign is triggered.

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE campaign_lead_event_log;
SET FOREIGN_KEY_CHECKS = 1;

 

DELETE

FROM
campaign_lead_event_log

WHERE
YEAR(date_triggered) = YEAR(NOW())
AND WEEK(date_triggered) < (WEEK(NOW()))
AND campaign_lead_event_log.channel IS NULL;

 


TRUNCATE campaign_leads;

 


We do not use this.

TRUNCATE lead_frequencyrules;

 


You truncate this if you want "refresh" the leads per segment to possibly avoid corruption. you may do this once every couple of months (do a backup first), and to regenerate the table you need to

mautic:segments:update

 

TRUNCATE lead_lists_leads;

 

Attachments:

Leave a Reply

Your email address will not be published. Required fields are marked *