how to build a simple integration tool like zapier?
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!
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!
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)
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)
ASKER
Thank you experts!!!
ASKER
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.
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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all!!!
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