how to build a simple integration tool like zapier?

sqlagent007
sqlagent007 used Ask the Experts™
on
I am looking to build an alternative to Zapier "zap" using a digital ocean droplet. Can anybody recommend a technology? The first thing that comes to mind is Node.js or PHP. Zapier is great, and we love it, however we are now being asked to processes files with up to 10,000 lines per file per month. This brings our zapier bill from @20 per month to $300. I would like to have a webHook online with custom URL. (myDomain.com/myWebHook)

our zap is very simple:
* push FirstName, LastName, Email to a webook
* add the user to a mail chimp list
* add the user to a wordPress memberPress subscription
* send the user a custom welcome email from an office 365 account

There are a few reasons it might make sense to build our own integration tool:
1 ) if we are going to get 10,000 items to process every month or so, it it much nicer to just budget $50 per month for an AWS or Digital Ocean VM
2 ) During the memberpress user create, then add to the correct subscription, the smallest time pause that Zapier allows is 1 minute. When we are processing 10K of new users, this can extend out for quite some time. It might be easier to have the ability to control down to a 15 second wait time.
3 ) I am not sure how zapier runs each zap, but during the memberPress adds it might be nice to know they are all running in serial. I often wonder if zapier runs them in parallel. We frequently get DB timeout errors when the zaps are running.

What I am looking for is advice on the following:
* What is the right technology to use for this? (PHP / node.js / something else?)?
* What are some good resources where we can start building these skills (books / udemy videos/ ??)?

Thanks in advance experts!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

First I would check what API or SDK is available (also check if there is an active community) for the apps you like to connect / sync.
For example here is the doc for mailchimp https://developer.mailchimp.com/documentation/mailchimp/
https://developer.mailchimp.com/documentation/mailchimp/guides/about-webhooks/

I would use PHP and RESTful (maybe SOAP this is not the same as RESTful but maybe more adapted in some case) (node may not be needed).
You will probably need to create a webhook for automatization.
https://rudrastyh.com/mailchimp-api/webhooks.html
https://rudrastyh.com/plugins/mailchimp-sync-wordpress-and-woocommerce-memberships

Udemy have great course for each language, plurialsight too.

https://www.udemy.com/rest-api/
https://www.pluralsight.com/courses/rest-fundamentals
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
I find when things appear to be simple and just work, there is in fact great complexity that created the simplicity. What you are seeing with zapier is just that.

As your use builds up, so does the cost of being easy as you have found out. At some point you decide it is worth the cost of your own time or hiring somebody.

You have done a good job of breaking this down to 5 major steps.

If your site is using WordPress, you can accomplish all of the by creating a custom plug in and let WP do the heavy lifting.

Which programming  languages are you versed in? where is data originating from? (WordPress site etc)
sqlagent007Technology professional

Author

Commented:
Thank you experts!!!
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

sqlagent007Technology professional

Author

Commented:
The data originates from a call center. The call center is supposed to send the new members to the ZAP a single member at a time, however what they have been doing as of late is sending an excel file that has ALL of the members. I only need the new members. With the excel file I have been using powershell to figure out what members are new vs what members are existing. Your are correct in that I could do this on the WordPress side and that might be the best way to go as I would need to query the database to check if this person is an existing member before processing HIM/HER.

I have done things like this with PowerShell and SQL Server Integration Services (SSIS), however to my knowledge I can't really plug those into a web hook.

The other reason for wanting to roll my own is so I can expand on this without an exponential cost. At times I may end up with situations where I am not able to process the entire file because I will have used even the MAX amount of tasks for a month.
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
WordPress wouldn't be good for this situation.

What exactly is this, "The call center is supposed to send the new members to the ZAP a single member at a time"  what is the zap?

I would either build a web app for data entry used by the call center or process the spreadsheet locally where it reads the spreadsheet, adds to the database, hits MailChimp and any other function.

you can us php,. net or other serverside languages to call a powershell script. of course that means a Windows server though.  That way you can use a language you already know.
We have been in the same place, but for a different reason.
Zapier is extensive, but it definitely does not cover all API services in the world, and often integrates with only a subset of the API specs.
What we did, was create private webhook endpoints with AWS Lambda and the AWS API Gateway, use it for the unsupported stuff, and then use Zapier for the existing integrations.
In your case, I would do the preprocessing of checking for user existence in the Lambda, and then push only qualified new contacts to Zapier.
You don't even have to create a webhook with API Gateway.
You can create a scheduled Lambda execution that will pick up a file from a predefined location, or create a trigger to pick up new files from S3.

AWS Lambda has a free tier, and even once a minute execution may cost you zero.
Developer & EE Moderator
Fellow 2018
Most Valuable Expert 2013
Commented:
Break this down to these parts

Data from call center
Sent in blulk via csv/excel
Enter one at a time


How do deal with each option?

Add member to memberpress
https://docs.memberpress.com/article/261-developer-tools-actions
https://docs.memberpress.com/article/261-developer-tools-actions#create-member


Do this first because after reading the memberpress documentation, it can interact with MailChimp


Set up memberpress to sync with MailChimp
https://docs.memberpress.com/article/85-mailchimp

In MailChimp you can create an option to send an email to newsubscribers
https://mailchimp.com/help/send-a-file-to-new-subscribers/


If you keep breaking down these steps instead of tackling them all at once, this should be straight forward. The hurdles I see are controlling how data is received from the data center and that will relate to how you start the process of adding members to memberpress.
sqlagent007Technology professional

Author

Commented:
Thank you again experts!!! Great discussion! I have no idea how to award points as everyone has added a ton of value.

I am still leaning into @Scott Fell's idea of writing this in PHP / Python / c#,  however I am on the fence about building it as a plugin or just standing up a $30 VM that I can deploy the new API on. This will allow us to get around the being charged per step in the ZAP and having such a fluctuating cost.

The workflow of how the new members will be sent from the call centers is still in work. The call centers are 3rd party entities that have their own proprietary software used to run the call center. I was told that each new member would be sent to the zapier webhook, however as of late the call center has been just sending us a file with all the members (current and new). I have a feeling this is due to them not being able to figure out how to send the members 1 at time. At this point, I must assume that BOTH is an option. Having the ability to check my existing user database before proceeding in the workflow is a great option. The main problem I am solving with Zapier is:
* adding new members to memberpress as they come in or in bulk (solved with custom WP development)
* keeping memberpress in sycn with MailChimp (I see now this can be done using the WP framwork)
* sending a new welcome email to the new user (I see now this can be done using MailChimps framework)

Thanks again experts! This has been great discussion and very helpful!
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet Software

Commented:
Using Excel's built in functionality (Power Query) you can reach out to SQL Server and remove all rows that are for existing members.
So that step can be simplified very quickly & easily.

API/REST calls can be done from a local app running on your machine and using Excel as the data source. In other words, it's not clear why you need a VM in the middle.
David FavorFractional CTO
Distinguished Expert 2018
1 ) if we are going to get 10,000 items to process every month or so, it it much nicer to just budget $50 per month for an AWS or Digital Ocean VM.

Neither.

a) AWS will be slow... well you can always speed AWS up by throwing in massive amounts of money, well beyond $50/month.

Tip: Search EE for Surprise Bills related to AWS.

b) Any type of VM system will run slow.

c) Lease OVH physical machines. They're as cheap os VMs. You'll never get a Surprise Bill. They're way faster than VMs, as they're bare metal machines.

2 ) During the memberpress user create, then add to the correct subscription, the smallest time pause that Zapier allows is 1 minute. When we are processing 10K of new users, this can extend out for quite some time. It might be easier to have the ability to control down to a 15 second wait time.

You're hitting a common problem, making a 3rd party service callout to any site... Zapier... InfusionSoft... all 3rd party service callouts produce the exact same problem.

a) If the 3rd party site is slow, your site is slow... including Zapier calls...

b) If the 3rd party site is down, your site is down...

c) Worst, hardly any developer ever considers correct coding to handled 3rd party services being slow or down.

d) The correct way to call 3rd party services is to cache all data required for your 3rd party callouts on site, then in background periodically probe all 3rd party services + continually make API calls till the API calls work. This is the only way to have 100% correct data synced onsite + offsite.

3 ) I am not sure how zapier runs each zap, but during the memberPress adds it might be nice to know they are all running in serial. I often wonder if zapier runs them in parallel. We frequently get DB timeout errors when the zaps are running.

All calls to Zapier are made based on how you make the the calls.

These are almost certainly made sequentially.

4) What is the right technology to use for this? (PHP / node.js / something else?)?

PHP. It's fast, easy to understand, installed by default every where.

5) What are some good resources where we can start building these skills (books / udemy videos/ ??)?

There's very little skill required in what you describe.

First, I'd skip using Zapier, it's expensive + just another layer of code to fail, which you have to work around.

The process you've designed is very simple. Just do all this yourself, rather than using Zapier.

Just keep in mind what I mentioned in #1d. You must write all your code to handle the case of all 3rd party services being dead.

Whether you use Zapier to call MailChimp or MailChimp API calls directly, #1d still applies.
sqlagent007Technology professional

Author

Commented:
Thank you all!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial