Integrating OFN with Mailchimp (the hacky way)

As many of you know, in the UK we have set up OFN users to integrate with Mailchimp using Zapier.
This has been integral to building our business model as it is one of our paid features and most of the users that pay for OFN use this feature.

It’s super simple. After the initial set up and first integration, future integrations to Mailchimp for other users take about 2 minutes.

How to do it:

  1. You need a Zapier account. To get a paid account for free you need to put the Zapier logo somewhere on your website and then get in touch with them saying you’re a not-for-profit and would like to take advantage of their free basic plan for NFPs. You’ll need to put their logo on your website somewhere… as UK have done here.

  2. You’ll then need to ask a sysadmin to set up a specific user on your OFN DB such that Zapier can access the OFN DB to find out when new users order. In the UK we have set up a secure user that can only access the DB from a single IP (the Zapier IP) with a super secure password (like 60 random characters). This should take a sysadmin an hour tops. See instructions here.

  3. Log into Zapier and set up a Zap that connects Postgres to Mailchimp. Zapier gives really good instructions on how to do this here. Essentially you just need to connect your DB user to Zapier (once only, for all future Zaps this will be remembered). You then put in a custom query that looks something like:
    select spree_addresses.firstname, spree_orders.email from spree_addresses, spree_orders where spree_orders.bill_address_id = spree_addresses.id and state = 'complete' and distributor_id = 12345;
    This ‘Zap’ be triggered for every order for this enterprise (12345) and will return the name and email address for every new order placed for distributor 12345. Obviously you will set the distributor ID for the distributor you are integrating.

  4. You also need to connect the Mailchimp of the user you are integrating. This is best done over the phone as you’ll need them to give you their username and password and sometimes Mailchimp with text them a code to confirm. In the UK all users have been happy to do this over the phone.

  5. In setting up your Zap make sure you ensure that the Zap first checks if the user is already subscribed to the mailing list. For privacy you should set the Zap to send an email to the user asking if they would like to be on the Mailing List. You can set the wording of this email.

  6. And wallah! Now every time a new user orders they will be sent an email asking if they want to join the Mailchimp mailing list. And users can keep in touch with all the people that order from them via email. Users can decide if they want to manage a separate list for people ordering and for website signups, or these can go on the same list. Up to the user!

1 Like

Really nice one!
We may have that SQL query available as an API endpoint already, if not, it should be pretty straight forward to build a new API endpoint for this simple query.

OH MY OH MY how I would love to just give you all the SQL queries I want as endpoints and just have them appear.

Even better (while we are building up the API) could we create a proper Zapier integration for OFN… I would love to put some time into a Zapier layer that talks to OFN endpoints and creates a proper Zapier integration to avoid connecting the DB directly.

@luisramos0 How can I find out the list of endpoints we already have?

that will be the result of this issue: https://github.com/openfoodfoundation/openfoodnetwork/issues/3001

For this query you could use the orders endpoint (do not play with this in a live environment if you are super admin because you will be pushing the app a lot by listing all orders in the system):
https://app.katuma.org/api/orders
I know the result has the fields you need: full_name, email, state and distributor.id
I am not sure if these can be used in filters.

1 Like

Cooooool, can’t wait for the results of this issue. Also very very much looking forward to being involved in conversations in which we start to iterate on endpoints.

1 Like

just want to say that while I have no idea of the specifics and tech details referenced in this post - I can’t wait until the day comes when someone without tech expertise who is an instance super-admin can make integrations happen easily. Will that day come? I accept that at some point, I might need to take a crash course with someone on ‘how to’ for this stuff. Maybe thats an agenda for a future super admin zoom call. Or do we think that an instance super-admin should always be a developer (in which case I’ll start looking for a replacement) THANKS to you all for working on this integration stuff!!!

Techie is a big spectrum :wink:

yep - and I’m on one far end of it. (Although I might be moving along it - but ever so slowly) :persevere:

@maikel just pinging you on this post as I’m going to be asking you questions about this re. Aus and wanting to make sure you’ve seen it :slight_smile:

@lin_d_hop not sure if i saw this somewhere - are you also already using zapier to connect to accounting systems?

Instructions for Step 2 were written by @matt-yorkley and can be found here: https://github.com/openfoodfoundation/ofn-install/issues/230

Hello @lin_d_hop can you add the instructions for step 2 mentioned by Brandy :point_up: in your initial post? François was asking me some questions, and we both didn’t see this comment :blush:

Also, Zapier will ask to fill this form out: https://zapier.typeform.com/to/nr8baR

So it might be easier to fill the form before contacting them. Maybe something to add as well? Thanks :slight_smile:

Made wiki so people can add any additional instruction they need :slight_smile:

1 Like

Hey @lin_d_hop the user who wants to try out in France will soon open several enterprises. Can we linked the same Mailchimp account to different enterprises?

yep :slight_smile:
filling characters…

FYI, I just used Zapier with SendInblue. This require only the user to give the API v2 code, so a lot easier than mailchimp. However you cannot personnalize any fields on Zapier side (like adding a user confirmation etc).