Skip to main content

Mass Mail

If you are willing to use mass mail function which is available in Atollon, you should probably follow next steps before you starts.

Introduction - how does it work?

First of all you need a distribution group. This can be done by following examples

  1. create report and use send mass mail button,
  2. create your own distribution group within Contact module and drag&drop contact you would like to have in this group,

  3. use some nifty sql functions to create your distribution group :).

Wanna language-based sending?

This feature is actually not working in mass mailing so the nifty sql's are needed to create language-based distribution groups. Then you of course can send emails which speak the same language recipient does.

Just to show you how you might do that:

  1. Create empty distribution group in contacts.
  2. Find out it's id using SELECT id from contact_distributiongroups where name like 'name-of-your-group';
  3. Insert all contact which belongs to your organization and speak Czech:
    insert into contact_bind_distrgroup(dgroup, contact)
        select $YOUR-DISTRIBUTION-GROUP-ID, occ.contact from organization_contact_context occ
        left join
            contact_personinfo cpi on occ.contact = cpi.contact
        where
            occ.organization = 1085000 and
            cpi.preferedlang = 2607000
    ;

If you are done with distribution group creation process you can switch to real sending. But wait. We should speak more about what's behind it. Sooo

Default folder aka organization_contact_context table

I would say you probably don't know what do I speak about. Let me just explain you. Every contact has its own default folder. As you probably know, every contact can have more than one folder. In this case - who knows which one is the default one? The answer is default folder. And also, you probably don't want to create folder for every person you have in your system. In this case Default folder is your very big friend again.

If you don't want to create Folder for every person in the system (what you probably really don't want to) you can easily update Default Folder using person's relationship with employer. This is pretty great because if you update Default Folder for the people you are willing to send your newsletter to, this message will actually stay on the person's employer's Folder.

What's the organization_contact_context table structure

That's quite simple. Table takes in consideration the contact might have different Default Folder accross organizations which are implemented in the system. So for that you can see following columns within it:

  • organization
  • contact
  • subject
  • project
  • activity

Updating default folder

But first of all you have to update Primary Employer. In order to do that there is already (working) function within the Atollon. Just open up windows client and go to Settings -> Contacts -> Contacts maintainance -> Setup default main contact. Run it and make yourself a cup of coffee.

Output of the following command is delete/insert sql function list. You can use \o /tmp/your-update-scrip.sql directly within psql so the output of the select is sent to the update script on your drive and you can import it after using psql -d $YOUR-DATABASE -f /tmp/your-update-scrip.sql. The command includes two values you should find and replace before you'll go to use it.

  1. $ORGANIZATION-ID - is id of your organization, of course,
  2. $ORGANIZATION-SUJBECT - is subject id of the organization.
SELECT 'delete from organization_contact_context where contact = '||cm.id||'; insert into organization_contact_context(organization, contact, subject, project) values($ORGANIZATION-ID, '||cm.id||', '||ss.id||', NULL);' from contact_main cm
left join (
    select distinct on (cpc.contact) s.id, cpc.contact as contact from subject s
        inner join contact_personcompany cpc on s.contact = cpc.company
        where
            cpc.isprimary = true
        order by contact
    ) ss on cm.id = ss.contact

where
    ss.id > 0 and

    cm.id in (
        select contact from contact_personcompany where company in (
            SELECT contact from subject where organization = $ORGANIZATION-ID group by contact
            )
        ) and
    cm.id not in (
        select contact from organization_contact_context where subject <> $ORGANIZATION-SUJBECT
    )
order by
    cm.id
;

 

The command itself might be a bit more updated because:

  1. company can have two Folders and we are selecting just first one (but how we can know which is the correct one?),
  2. person can have only one Primary Employer but he might also be excluded (he got fired) from that company and then validTo timestamp should be considered in that select too.

Done with distribution groups, ready for mass mailing

If you did update default folder, created your distribution group and are happy what you have you can switch to the real  mass mail sending.

Bugging

What if something went bad ...

Flash player crashed by mass mailing

Well ... that's great question. I was checking contact_mmlist_temp table for some changes and experienced some status changes but are those messages being still send or not?

Small hint - use following command where $MASS-MAIL-ISSUE-ID is id of your last sending (you might find it by selecting over all issues and trying to remember for how many contact did you send that mass mail) and check for changes.

SELECT status, count(status) from cONTACT_MMLIST_TEMP where issue = $MASS-MAIL-ISSUE-ID group by status;

Can not create mass mail list on the server side

This means your temporary mass mail list couldn't be created. This might be probably really bad bug. In my case the problem was in the insert of the distributiongroupid and contactid in the contact_mmlist_temp. I found out which insert was the wrong one and tried to apply it manually within plsql where I didn't experience any problem.

Back to top