Sunday, May 31, 2015

Cohort analysis of CRM data using Zoho Reports

Cohort analyses are super handy if you're running a subscription business and want to see changes in ARR/MRR (annual/monthly recurring revenue). I've been thinking about how to achieve this for Zoho CRM data. A tip from Zoho Reports about using the color option in a chart gave me the answer.

To get this chart, I created a query table that combines the Invoice and Account modules and uses the quarter/year functions to group cohorts. The query is:
(year("Accounts"."Created Time") + '-' + quarter("Accounts"."Created Time")) as "Quarter joined",
"Accounts"."Account Name",
FROM  "Accounts" JOIN "Invoices" ON "Accounts"."ACCOUNTID"  = "Invoices"."Account Id" 

Tracking real sales conversion rates for cold leads using Zoho Reports + Zoho CRM

The Zoho Reports team helped me out with a tricky report to show the lead to won potential conversion rate. To explain the report, let's consider this scenario:
1. The business had 1000 leads entered into the CRM through telemarketing
2. Salespeople then qualified those leads and converted 50 of them to opportunities
3. The sales team then closed 10 of those leads

Based on those stats, we'd expect the lead to won potential win rate to be 1% (10/1000). In the default Zoho Reports conversion stats, you can only see potential to closed potential conversion rates. This stat is misleadingly high (10/50 = 20%) because it's only considering converted leads and ignoring junk/lost leads.

If the sales team really closed 20% of cold leads, you would probably see them usurping Brian Tracy on the speaking circuit. In reality, they're decent (1% conversion) but not world beating. I don't know about you, but I prefer to deal with reality.

How to generate true conversion rate stats using Zoho Reports
Courtesy of Anand and the amazing Zoho Reports CS team.

1. Set up a new query table using this query:
l."Lead Source",
l."Created Time",
s."Forecast Type"
FROM  "Leads" l LEFT JOIN "Sales" s ON l."LEADID"  = s."LEADID"  
2.  Set up a new report based on that table
I've used aggregate formulae as well:
e.g Won Potentials = countif("Sale Conversion"."s.Forecast Type"='Won')

How to quickly add call logs to Zoho CRM leads using workflows

Want a faster way to add call logs? Check out this video that shows you how to add call logs by ticking a box. (Warning: Enterprise Edition + deluge scripting skills needed).

Saturday, May 30, 2015

Calculating amount of Zoho CRM potential based on associated products

If you link products to your potentials in Zoho CRM, you can dynamically calculate the total amount of a potential by using a custom function + a workflow.

Here's the code:

Performing mass updates on thousands of Zoho CRM records using custom functions

Need to mass update thousands of Lead records in Zoho CRM and can't be bothered manually clicking through tens of pages and doing mass update? Here's an example of using a custom function to achieve this. In this case, I needed to merge several notes fields into one text area field as the multiple note fields were becoming unwieldy.

Wednesday, May 27, 2015

How to copy and paste form fields in Zoho Creator using Form Definition

I had to replicate a bunch of fields in a Zoho Creator form (i.e. I'd created them for client 1 and then needed to recreate them for client 2). I figured it would be faster to go into the form definition and copy and paste elements there rather than creating them via the UI. It ended up taking longer haha. For some reason the form definition seems to be very temperamental. I can't precisely tell you what ended up allowing it to work, but here are some things that you definitely can't do:

1. Have a field with the same ID as another field (display name can be duplicate though)

2. Have any comments in the form definition.

I was plagued with "Error: null" (gotta love ZC's highly descriptive error messages!)

Wednesday, May 20, 2015

Work around for annoying auto-indentation in Zoho Creator free flow scripting mode

If you're like me, you probably hate the auto-indentation feature in Zoho Creator. It always puts braces in the wrong positions and indents at the wrong level. I got sick of having to backspace 20 times to get indentation looking good so looked for another solution.

I found it in

You can chuck your Deluge script in there and it will indent it nicely for you:)

Getting Zoho CRM user details from Zoho Creator

Creator doesn't have a helper method for accessing Zoho CRM user details so you have to directly access the API. Here's a code snippet that might help:

Tuesday, May 19, 2015

Firefox webdriver not working properly? Try downgrading Firefox

I spent several hours trying to get my Selenium script working properly. FirefoxDriver flat out refused to work, ChromeDriver wasn't taking screenshots, complaining about not having the automation extension installed, and PhantomJS wasn't suitable because I needed to take screenshots of Flash-generated reports (*groan*). Eventually I realised that Firefox had auto-updated to v38 and broken Selenium as a result. I uninstalled v38, reinstalled v34 (and disabled auto updates) and it now works like a charm:) Hope this saves you some pain!

Sunday, May 17, 2015

Changing lead privacy settings in Zoho CRM

Need to make leads private so that salespeople can't see each other's leads? Here's a quick guide.

Go to Setup

Click on data sharing settings

Review the current settings

Edit the data sharing settings for Leads

Change Default Access to Private

Click on Save

Saturday, May 16, 2015

Deserialization in Ember Data (CLI edition May 2015)

I was working on a side project to learn Ember JS recently and hit a major roadblock with deserializing data from a REST API. I have a model that looks like this:

Ember therefore expects a JSON payload that looks like this:
However, the API (from Kimono) actually returns a payload with the people array embedded within a results array:
I spent hours trying to figure out how to use the deserialize methods to extract the people array from the results array. What frustrated me was that I couldn't really find any examples of how to use the deserialize methods. Maybe it's super basic for most people but I'm an Ember noob and couldn't figure it out for ages.
To help you avoid my teeth gnashing, here's the code I wrote for my serializer.
To explain what's going on here, we have to override a few methods: extractSingle (if there's only one person returned), extractArray (if you used findAll) and normalizeHash (used by both of the two previous methods).
extractSingle and extractArray are frustratingly simple. To look at them, you'd think it took me 5 minutes to write the code not 4 hours hahaha! Hopefully you'll find it super easy to write deserializers now.
The key things to note are that you can delete elements from the payload, e.g. delete payload.results; In this case, I completely overrode the payload as the only thing that I needed was the people array.
Something else to be mindful of is that if the API has different variable names to your model, you can add a new variable name to the hash (e.g. hash.fullName =;) and then delete the old variables.
It does seem to be relatively important to clean up unused variable names because otherwise Ember warns you about variables that aren't found in the model. It won't break your application but it does make your console messier than it needs to be.
Hope this little guide helps! Let me know if you have any questions.

Monday, May 11, 2015

How to mass update lookups in Zoho CRM

You'll notice that you can't mass update lookup fields in Zoho CRM via the UI. Here's a code snippet to show how to update lookup fields using Deluge custom functions.

You then have to set up a workflow to run that custom function on a certain trigger. Feel free to ping me if you can't figure it out.

Saturday, May 9, 2015

Accessing query params from a route in Ember CLI in May 2015

I just spent 90 minutes trying to figure this out so hopefully this will help other people avoid similar pain! If you want to access query params from a route in Ember it's actually really simple: just declare the query params in the controller first.

export default Ember.Controller.extend({

queryParams: ['city', 'company'],

And then in the route:
model: function(params) {
var city =;
var company =;

Friday, May 8, 2015

Trick to calling other custom functions from within Zoho CRM custom function

One thing that has bugged me about Zoho CRM custom functions is that they can quickly become very bloated because the programming interface steers you towards putting all the code in one method. I realised today that there is a way around that though. If you add the namespace 'workflowspace' before the custom function name, you can call it from another custom function.

E.g. workflowspace.round_robin_engagements(engagement_id.toLong());

Monday, May 4, 2015

Using PhantomJS with PHPUnit

I found it pretty hard to get PHPUnit working with PhantomJS so thought I'd put together a quick tutorial.

Step 1: Make sure you have the following dependencies in your composer.json and run composer update:
"require-dev": { 
      "facebook/webdriver": "dev-master",  
       "phpunit/phpunit": "4.0.*", 
       "phpunit/phpunit-selenium": ">=1.2" 
Step 2: Download PhantomJS and Selenium Server and put them in a new directory 'bin' in your tests folder. (Might not be the most optimum place to stash them but it works for now)

Step 3: Add the following code to your tests folder as TestScaffold.php. It will start the Selenium server if it's not already running and boot up PhantomJS for each test.
Step 4: Write a test that extends the scaffold