Integrating with Salesforce with Zuora, Commissions and Some ETL fun

This blog post is going to cover some integration work with Zuora to, building a custom commission application, and Pentaho’s Data Integration.

Originally I was responsible for commission plans that were fairly straight forward. The business model paid on direct and channel sales at point of sales from distributors. To setup the payouts it was as simple as creating reports in for the various payouts. Then the business model changed from a premise one-time sales to include recurring revenue model with a cloud solution. Also it was decided to pay commissions external partners (agents) on business they brought to the company.

The business unit management and business integration team selected Zuora as the ERP solution to handle the subscription billing. It has integration with two packages, so that made this administrator happy.  During the initial testing in the sandboxes, it looked pretty straight-forward on setting up commission.

In 2012 I had created a set of custom objects and wrote a bunch of APEX code in to track and insert commission records.  To support paying agents, I ended up overhauling the the objects and removed the apex coding as the criteria and complexity started to exceed my hack-style coding skills.

The new version of the commission application I created in had the following objects:  Plan, Plan Assignment, Rate Plans, Plan Quota, Commission Claim, and Commission Line Item.

Schema Builder – Commission App

Creating commission plans and quota and then assigning internal sales reps to them was pretty straight forward.  A simple excel file and bulk load them in with the data, done in 15 minutes, and typically only have to do so once a year.  However, the challenge was how to assign agents to commission plans, and if they changed plans to make sure that new business was paid under the new plan, and old business on the old plan.  This was a challenge and one that took several months to get right.  Finally one day I took a break from working on the commission processor to work on my data warehouse.  It hit me that I could use a data warehouse technic called slowly changing dimension (see Ralph Kimball’s blog on SCD)

Since the complexity of the commission plans are now beyond my coding skills, I utilize Pentaho’s ETL (Extract, Translate, Load) tool Pentaho Data Integration (Kettle/Spoon). I use this tool to build our data warehouse pushing data in to MySQL database.

I already have all the objects I need syncing down to my local db, so I created a transformation that will track when agents sign agent contract and when they sign a different level.

Transformation that takes the agreements and created the SCD table, which will later upsert into using the technical key as the external id.

Integration with the Subscription Billing and Generating Commissions

Zuora’s go live was a year ago and we have discovered several unexpected things that caused many headaches for commission processing.  First issue was we planned on paying agents and our internal sales teams on invoices; however, the Zuora package did not include Invoice Line Items which we needed to calculate commission rates by product.  Luckily they do have a fairly decent API and documentation.  So back to Pentaho Data Integration for some integration work.

Job the gets all variables, validates access to the API, exports data from Zuora, parses xml to local MySQL, and then upserts to
Get Zuora Invoice Line Items with SOAP request create and JS to extract the XML results
Transformation to parse the XML file and load into the local table to later upsert to

I did have issues keeping Zuora and in sync originally.  The first integration job used date ranges, but if for some reason that day there were to many invoices for the API call, I would end up missing invoice line items.  I ended up adding a roll-up summary field on the Zuora Invoice object to count line items, then if 0 or the line item amount and invoice’s amount didnt match, the Pentaho job would query just those invoices until all line items are inserted.  Haven’t had a missing line item since!

The next surprise was when an amendment is created and applied to a subscription, the subscription record is deleted out of and then re-inserted with a new version number.  So this caused the record to be re-evaluated for commission rules and created duplicate claims.  Another issue the deletion/insert cause was we originally had the subscription record related to the opportunity and agent account records.  Well, this data would not be on the new version unless a script added it back (which the business integration team eventually wrote one to do so).

To work around this, I created an object called Subscription Tracker.  This object does not get deleted and I could develop ETL job to update the information, such as who the agent(s) are, track LTV (Life Time Value) and segment the customer type.

Schema Builder – Subscription Tracker
Pentaho Job to create the subscription tracker and enrich it with agent, commission plan(s), and much more. There are also transformations to help make sure the data quality of the opportunity is as expected. Since we could have multiple opportunities for a single account, have to have a way to know which opp goes with which subscription.

Another surprise with the Zuora Subscription management is how they handle Subscription Product and Charges.  When an amendment is applied for a recurring item, the Product Charge Effective Start Date is changed from the original date to the new Product Charge Effective Start Date for the amendment.  At first this was not a big deal as we were paying commissions off invoices, but for our internal sales team, the commission plan changed to pay them a one-time commission payout for the initial activation, and then off the amendments for the first 90 days.  Well, if the Product Charge Effective Start Date is changed this could (and did) cause over payments.  Lets say it started off with a monthly charge of $35 per month and they increased their service to $40, it looks like its a new line item (remember Zuora deletes and re-inserts records) so the commission processor will pick up and process the payment off $40 instead of the increment change of $5.

Back to Pentaho with a bit of dimensional knowledge to solve this problem.  Luckily I had all the deleted records cached, so I was able to insert all the new and amended product charges into a new object Subscription Product Charge Tracker in and relate them to the Subscription Tracker.

Transformation to extract the deleted and current subscription product charges and create a slow changing dimension table. Shows the key lookup and the fields I populate. I used the technical key as the unique id for upserting into Salesforce to keep the tables in sync.
This transformation step creates the delta changes from amendment changes for the items.

Now that I have all the supporting transformations to make sure the subscription data is there, related to the appropriate agent, opportunity, and commission plan; i can generate commission claims with line items.  An important thing that I recently added is a field on the commission claim and claim line item, pentaho_uid.  This field is set to be an external id and unique, and is built using unique keys to prevent duplicate claims from being inserted if they system has issues and needs to be re-ran.

Transformation that processes internal commissions for one time payouts from Zuora
Transformation that processes agent commissions from Zuora’s Invoice Line Items

Audit the Process

So as much as I hate auditing, when you are dealing with other people’s money you need to make sure its correct.

To match auditing easier, I added fields on the invoice and commission claims that would relate them to the Subscription Tracker object.  Then added transformations to update the records with the tracker’s id.  Once I did this I was able to write several reports and create a dashboard that looked at the number and value of invoices and commission claims paid out to make sure they matched.

Sample of what the Subscription tracker looks like. Internal Commission Claims are also associated, just didnt make the screen capture.
Sample of what the Subscription tracker looks like. Internal Commission Claims are also associated, just didnt make the screen capture.

I hope that you find this post useful.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s