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 Scheduled Job

    • 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