This blog post is going to cover some integration work with Zuora to Salesforce.com, 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 Salesforce.com 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 Salesforce.com 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 Salesforce.com 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 Salesforce.com had the following objects:  Plan, Plan Assignment, Rate Plans, Plan Quota, Commission Claim, and Commission Line Item.

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.

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.

I did have issues keeping Zuora and Salesforce.com 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 Salesforce.com 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.

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 Salesforce.com and relate them to the Subscription Tracker.

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.

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.

I hope that you find this post useful.