MailChimp Integration

MailChimp is a common email marketing platform. It allows you to have regular contact with you customers. See https://mailchimp.com/

Using Shipper, you can upload data from your Shipper Parties, to a MailChimp list of subscribers. MailChimp will take it from there.

Pre-Requisites:

  1. MailChimp account
  2. API Key (see https://kb.mailchimp.com/integrations/api-integrations/about-api-keys)
  3. Email support@hurkin.com and have them update your API Key in your instance settings. You should only need to do this once.

Setting it up:

  1. You need a SQL query to pull the subscribers you want. Don't worry! We'll help. See the attachment to this page for an example.
  2. The query needs to contain a unique column named "Email".
  3. You will need to create a List, and know the exact case-sensitive name of that list.
  4. The query columns will all be used to do a MERGE function and SUBSCRIBE each email address to the LIST you specify.
  5. You will need to create a System Event
    • Parameter 1 is the SQL Query
    • Parameter 2 is the Name of the List - exactly - from #3.
  6. Let it run and see the results.

An example:

The code is here:

declare @YearsAgo int = 3
select
    p1.Name Company,
    isnull(p2.Name,p1.Name) FNAME,
    ISNULL(p2.Email,p1.Email) Email,
    b.LastPurchase,
    s.LastSale,
    sh.LastShip,
    case when nullif(isnull(p2.PortalPassword,p1.PortalPassword),'') is not null then 1 else 0 end PortalUser
from
    dbo.Party p1
    join dbo.PartyClass pc1 on pc1.PartyClassId = p1.PartyClassId and pc1.Mnemonic = 'COMPANY'
    left outer join dbo.PartyRelation pr 
        join dbo.Party p2 on p2.PartyId = pr.ToPartyId
            and p2.IsActive = 1
            and p2.IsMarketingOptedIn = 1
            and p2.IsTransactionOptedIn = 1
        join dbo.PartyClass pc2 on pc2.PartyClassId = p2.PartyClassId and pc2.Mnemonic = 'PERSON'
    on pr.FromPartyId = p1.PartyId
    left outer join (select max(CreatedDate) LastPurchase, Buyer_PartyId
    from dbo.Agreement
    where CreatedDate > DATEADD(year, -@YearsAgo, getutcdate())
    group by Buyer_Partyid) b on b.Buyer_PartyId = p1.PartyId
    left outer join (select max(CreatedDate) LastSale, Seller_PartyId
    from dbo.Agreement
    where CreatedDate > DATEADD(year, -@YearsAgo, getutcdate())
    group by Seller_PartyId) s on s.Seller_PartyId = p1.PartyId
    left outer join (select max(CreatedDate) LastShip, ShipTo_PartyId
    from dbo.Agreement
    where CreatedDate > DATEADD(year, -@YearsAgo, getutcdate())
    group by ShipTo_PartyId) sh on sh.ShipTo_PartyId = p1.PartyId
where
    p1.IsActive = 1
    and p1.IsMarketingOptedIn = 1
    and p1.IsTransactionOptedIn = 1
    and NULLIF(ISNULL(p2.Email,p1.Email),'') is not null
    and (b.LastPurchase is not null or s.LastSale is not null or sh.LastShip is not null)
order by 
    1, 2, 3