Salesforce.com as free, bolt-on analytics engine for any database app
Last Updated on Wednesday, 2 June 2010 02:47 Written by Steve Wednesday, 2 June 2010 02:47
Over my career I’ve noticed that most applications have very limited reporting and charting features. I’ve tried to write some of my own and realized quickly why this is–analytics is hard! Visual query builders, dynamic charting, aggregation of data, it’s all really tough to do right. I also think most application teams start out with the feature list of business processes that need supporting–recording transactions, editing information, etc. I think they often punt on the analytics engine for these reasons–it’s hard and the value is limited if the app doesn’t even do what it needs to do yet. Because of this, most apps come later, if at all to the analytics game.
And because most of the apps we use have lousy analytics, we often don’t know what we’re missing. In my CRM consulting it was very common for people to under-value the analytics compared to the process streamlining aspects. Getting things done more quickly is important, but analytics can help tell you if you’re doing the right things.
Salesforce.com has a great analytics engine. It’s an amazing blend of efficiency and power. Easy things are in the reach of most users, and hard things are possible without coding. Why are the analytics so good? Salesforce.com is a billion dollar company. They’ve spent amazing amounts of money building their platform, and as part of that, the killer analytics engine. They’ve made it a strategic priority, invested in it, and iterated over 10 years. It really shows.
Great, so Salesforce.com has good analytics. You might think that if you want to use those analytics tools, you need to ditch your app and rewrite the whole thing on Salesforce.com, right? Porting a functioning application is a big project, and while sometimes it might makes sense to do that, a lot of times it doesn’t. You may not have the money to do it. You may be on an open source platform and not want to go to a proprietary one.
But it turns out you don’t need to ditch your app to get Salesforce.com analytics for your data. Using free and open source tools you can bolt the Force.com Free Edition onto your application and very quickly get the power of Salesforce.com analytics for 100 users. For free. I did it myself last weekend. Here’s how it works:
- Get your app up and running
- Get Force.com Free Edition, or use your existing Salesforce.com organization
- Create tables in Salesforce matching the key tables in your app
- Create some simple triggers to get all your data relationships right
- Set up an extract-transform-load (ETL) server like Jitterbit to pull data from your app and push it to Salesforce.com.
- Set up reports and dashboards on that data
- Let your data inform the strategic direction of your organization
If you do all this, you can pull data from your application directly into Salesforce.com, automatically updating it as changes are made in your app. Then your end users can get the analytics they can’t get in the app. And your key decision makers, who probably don’t use the app in the first place, can get the top level numbers and trends that help them manage the impact of your organization.
I’ll now go into detail on each of the steps above to show you how it can be done.
Step 1: Get your app up and running
I chose to work with Mifos, an open source application that helps microfinance institutions track their loans and borrowers. Microfinance is a global effort to get working capital in the hands of the poorest people. It’s amazing work being done by organizations all over the world. Mifos happens to be built on Tomcat with a MySQL database. But which app I use really isn’t important. All that matters is that is has extensive functionality for specific business cases, and it has a database we can directly access. If it does these things, it’s a candidate for Salesforce analytics.
I installed Mifos on an Amazon.com EC2 server, which saved me the trouble of trying to get it to run on my Mac. I love EC2!
Step 2: Get Salesforce
I got a free Developer account for this test case, but you could get Salesforce.com donated to your nonprofit, go with the Force.com Free Edition, or purchase Salesforce.com.
Step 3: Create your schema
Creating analogous tables to all the tables in your app is one of the more time-consuming parts of this process. I sped things up by using Model Metrics’s great Cloud Converter tool. I got the field names and one row of values for each table I cared about–you don’t need to get every table, just the ones that figure into your analytics. I created an Excel tab for each table, and pasted the header names and values into each. I then let the Cloud Converter loose on it, and the tables were created in just a few minutes! There might be some cleanup necessary with field types–Cloud Converter guesses what type the field should be, but sometimes it guesses incorrectly. You can do this by hand, or use the metadata API with Eclipse for the fastest action.
All the tables in your app probably have primary keys–some unique Id field that marks each row. In your Salesforce schema, you want to mark the analagous fields are External Ids. This will allow you to use Salesofrce’s Upsert web service method. Without External Ids, you need to know the Salesforce.com record Id in order to Update it. Upsert is incredibly amazing for this use case–it makes it all so easy. Just pass it the primary key from the app and it will find the right record in Salesforce.
The app you’re coming from has relationships between tables (using the primary key fields I just talked about), so you need to recreate those relationships in Salesforce. Cloud Converter might be able to do this for you, but I didn’t try that out. Create your lookup relationships between the objects and you’re good to go.
Step 4: Create your triggers
The ETL server will create your Salesforce records just fine, but it won’t make sure they’re related to the correct parent records for you. To do this you need simple triggers on each object to look at the foreign key from your app find the right Salesforce object, and relate the two. Here’s an example where the Account Payment object looks for the parent Account when it’s inserted or updated. If it finds it, the relationship gets recorded and the Account Payments show up under the Account:
trigger accountPaymentParentfinder on account_payment__c (before insert, before update) {
Set<Integer> accountIds = new Set<Integer>();
Map<Integer,Id> accountIdMap = new Map<Integer,Id>();
//loop through the trigger set and build a set of all the Account Ids
for(account_payment__c myAccountPayment : Trigger.new){
if(myAccountPayment.account_ID__c!=null){
accountIds.add(myAccountPayment.account_ID__c.intValue());
}
}
//go query for those Account Ids and create a map connecting them with the Salesforce Ids
for(account__c thisaccount : [select id,account_Id__c from account__c where account_Id__c IN :accountIds]){
accountIdMap.put(thisaccount.account_Id__c.intValue(),thisaccount.id);
}
//put the right Salesforce Id in the lookup relationship
for(account_payment__c myAccountPayment : Trigger.new){
if(myAccountPayment.account_ID__c!=null){
myAccountPayment.account__c = accountIdMap.get(myAccountPayment.account_ID__c.intValue());
}
}
}
You need to do this for each object. If an object has more than one lookup relationship, you’ll need to create the Set and Map structure for each. They all follow the same pattern. Easy peasy.
Step 5: Set up your ETL server and transformations
I chose Jitterbit for my ETL server. They have a free Community edition but I used a 30 day trial of their Enterprise edition, only because there was a machine image on Amazon.com EC2 for the enterprise version. Installation was a 10 minute affair.
The Jitterbit client is where you create the operations that pull data from the source and get it where you want it to go. I created an operation to log in to Salesforce.com and store my session Id. Then you pretty much create one operation for each table in your app, grabbing the data and passing it to Salesforce.com’s Upsert web service method. This will try to update a record that exists with that External Id. If it can’t find the record, it will create one. Brilliant.
It’s a ton of drag-and-drop mapping–this app field maps to this field on the Salesforce object. But once you get the hang of it, you’ll crank through all your tables.
When your operations are set up you run them, starting with the login, until all your data comes over. These operations can be scheduled to run as often as makes sense to you. You can pull over only data modified since you last pulled. I created a very simple data flow–one way from my app to Salesforce.com. But you can really do just about anything. You can send data back to your app. You could pull from 25 different apps all to Salesforce. It’s up to you.
Step 6: Set up your reports and dashboards
Once your data is in Salesforce.com and related correctly, start creating your reports and dashboards. What I find is that when I create a dashboard the next one immediately comes to mind. Loan default rate over time. But does marital status have an impact on that? What about number of children the borrow has? And what are the patterns for defaults? Do loans tend to default more frequently during the dry season? In the months before harvest? Do non-agriculture loans behave differently? People who work at microfinance institutions ask themselves these questions. They see patterns and want the data to confirm or challenge their assumptions. Because Salesforce.com makes analytics easy, users can ask these questions and get immediate answers. Dashboards can even be sent to them via email every week.
Then step back and take a look at your analytics. Here are some I created with a sample data set in my app:
- Customer Info
- Default Info
- Delinquency Info
- Historic Delinquency Rates
- Loan Demographics
- Loan Info
- Loan Products
I was very pleased with the story I am just starting to tell about the loan programs at this pretend microfinance institution. I think the take away is this: Salesforce.com can add value by helping you be creative in telling the story of your data. It’s a story that might be hard for you to tell right now. But with very little cost other than time and technical ability, you can add flexible, user-driven analytics to any database application by following this recipe.
You can add this power to your app. But the really amazing part is how easy it is to share this kind of setup. Once you create a schema that matches Mifos or OpenMRS or CiviCRM you can package that up in Salesforce and share it with anyone. Just a couple clicks and they can install all the objects, triggers, and dashboards for that app. Jitterbit operations can be shared as well. You export them as a “jitterpak” and anyone can use them with their setup. Just change the login information, point to your Salesforce.com web services and it should work without changes.
And perhaps the most important thing is that you’re not embedding Salesforce.com into your app. You’re not requiring that everyone using the app use Salesforce.com. I hop this example shows that Salesforce.com can be “bolted-on”, adding value along side your mission-critical application. The cost can be very low, the impact can be high, and you can do it without making big decisions about app architecture direction.
Learn MoreSalesforce sending SMS through FrontlineSMS
Last Updated on Thursday, 27 May 2010 07:15 Written by Steve Thursday, 27 May 2010 07:13
FrontlineSMS is an open source application used primarily in the developing world for communicating with large numbers of cell phone users. It’s a locally installed application that pushes SMS messages out through your tethered cell phone. It’s a really cool app with all sorts of great real-world usage. My friend Dale Zak just built an awesome reminders module for it, and that got me looking at it again.
The newest version of FrontlineSMS (1.6) has a very simple API for sending SMS messages. I have been successful in getting Salesforce.com to use this API to send SMS messages to single Contacts and to Leads and Contacts who are members of a Campaign. Here’s a short video of it in action:
Here’s what’s going on in the one-off send:
- I’m using Visualforce to create UI that is called by the Send SMS buttons
- The Visualforce page accepts the SMS text input
- Clicking send shows an iFrame that calls the FrontlineSMS API correctly: http://localhost:<port>/sms_number/sms_message
- A Task is related to the Contact with the data and content of the SMS message
Why use the iFrame? Since FrontlineSMS is locally installed, we have to communicate with it from the browser. A more robust option would be to create Javascript that makes the localhost calls and listens for the success or failure and proceeds accordingly.
To all the members of a Campaign, we do the following:
- The Visualforce page accepts the SMS text input
- Clicking Send exposes an Action Poller, which calls code every 5 seconds
- When the Action Poller calls code, 10 Campaign Members of the correct Status are queried
- 10 iFrames are populated with the FrontlineSMS API URLs for the messages
- The Campaign Member records have their Status changed so they won’t be queried again
- Tasks are related to the Campaign and the Contact or Lead
Here are the Salesforce code bits.
Visualforce page for one-off sends:
<apex:page standardController="Contact" extensions="extSendSMS" > <apex:form > <apex:sectionHeader title="FrontlineSMS" subtitle="Send SMS via FrontlineSMS"> <description> Send an SMS using FrontlineSMS: <ul> <li>Install FrontlineSMS on your computer</li> <li>Make sure FrontlineSMS is running</li> <li>Make sure your phone is connected to FrontlineSMS</li> </ul> </description> </apex:sectionHeader> <apex:pageMessages id="pageMessages"/> <apex:pageBlock id="smsblock" title=""> <apex:pageBlockSection id="smssection"> <apex:outputField value="{!contact.MobilePhone}"/> <apex:inputField value="{!task.Description}"/> </apex:pageBlockSection> <apex:pageBlockButtons id="smsbuttons"> <apex:commandButton value="Send SMS" action="{!sendSMS}" rerender="smsiframe"/> </apex:pageBlockButtons> </apex:pageBlock> <apex:pageBlock id="smsiframe" title=""> <apex:iframe id="frontlinesmsIframe" src="{!endpoint}" scrolling="no" rendered="{!showFrontlineSMSiframe}"/> </apex:pageBlock> </apex:form> </apex:page>Apex controller for one-off sends:
public with sharing class extSendSMS { public Task task {get;set;} public String endpoint {get;set;} public Boolean showFrontlineSMSiframe {get;set;} private Contact contact; private Lead lead; public extSendSMS(ApexPages.StandardController stdController) { this.contact = (Contact)stdController.getRecord(); this.contact = [select id, MobilePhone from Contact where Id=:contact.id]; this.task = new Task(); this.task.WhoId = contact.Id; this.task.type = 'SMS'; this.task.ActivityDate = Date.Today(); this.task.status = 'Completed'; showFrontlineSMSiframe = false; } public PageReference sendSMS(){ this.task.subject = 'SMS: ' + this.task.Description; insert task; String strippedMobilePhone = ''; strippedMobilePhone = this.contact.mobilePhone; strippedMobilePhone = strippedMobilePhone.replaceAll('[^0-9]', ''); endpoint = 'http://localhost:8565/'+ strippedMobilePhone +'/'+ this.task.Description; showFrontlineSMSiframe = true; return null; } }Visualforce page for Campaign sends:
<apex:page standardController="Campaign" extensions="extSendSMSCampaign" > <apex:form > <apex:sectionHeader title="FrontlineSMS" subtitle="Send SMS via FrontlineSMS"> <description> Send an SMS using FrontlineSMS: <ul> <li>Install FrontlineSMS on your computer</li> <li>Make sure FrontlineSMS is running</li> <li>Make sure your phone is connected to FrontlineSMS</li> </ul> </description> </apex:sectionHeader> <apex:pageMessages id="pageMessages"/> <apex:pageBlock id="smsblock" title=""> <apex:pageBlockSection id="headersection" > <apex:outputLabel for="membercount">Member Count</apex:outputLabel><apex:outputText id="membercount" value="{!memberCount}"/> <apex:outputLabel for="numbersent">Number Sent</apex:outputLabel><apex:outputText id="numbersent" value="{!numberSent}"/> <apex:inputField value="{!dummyTask.Description}"/> </apex:pageBlockSection> <apex:pageBlockSection id="smssection" rendered="{!showFrontlineSMSiframe}"> <apex:outputLabel for="endpoint1">SMS 1 (debug)</apex:outputLabel><apex:outputText id="endpoint1" value="{!endpoint1}"/> <apex:outputLabel for="endpoint2">SMS 2 (debug)</apex:outputLabel><apex:outputText id="endpoint2" value="{!endpoint2}"/> <apex:outputLabel for="endpoint3">SMS 3 (debug)</apex:outputLabel><apex:outputText id="endpoint3" value="{!endpoint3}"/> <apex:outputLabel for="endpoint4">SMS 4 (debug)</apex:outputLabel><apex:outputText id="endpoint4" value="{!endpoint4}"/> <apex:outputLabel for="endpoint5">SMS 5 (debug)</apex:outputLabel><apex:outputText id="endpoint5" value="{!endpoint5}"/> <apex:outputLabel for="endpoint6">SMS 6 (debug)</apex:outputLabel><apex:outputText id="endpoint6" value="{!endpoint6}"/> <apex:outputLabel for="endpoint7">SMS 7 (debug)</apex:outputLabel><apex:outputText id="endpoint7" value="{!endpoint7}"/> <apex:outputLabel for="endpoint8">SMS 8 (debug)</apex:outputLabel><apex:outputText id="endpoint8" value="{!endpoint8}"/> <apex:outputLabel for="endpoint9">SMS 9 (debug)</apex:outputLabel><apex:outputText id="endpoint9" value="{!endpoint9}"/> <apex:outputLabel for="endpoint10">SMS 10 (debug)</apex:outputLabel><apex:outputText id="endpoint10" value="{!endpoint10}"/> </apex:pageBlockSection> <apex:pageBlockButtons id="smsbuttons"> <apex:commandButton value="Send SMS" action="{!sendSMS}" rerender="smsblock,smsiframe,actionblock"/> </apex:pageBlockButtons> </apex:pageBlock> <apex:pageBlock id="actionblock" title=""> <apex:actionPoller action="{!sendSMSToMembers}" rerender="smsblock,smsiframe,actionblock" interval="5" rendered="{!showActionBlock}"/> </apex:pageBlock> <apex:pageBlock id="smsiframe" title="" > <apex:pageBlockSection id="smsisection" rendered="{!showFrontlineSMSiframe}"> SMS 1 (debug) <apex:iframe id="frontlinesmsIframe1" height="30" src="{!endpoint1}" scrolling="no" /> SMS 2 (debug) <apex:iframe id="frontlinesmsIframe2" height="30" src="{!endpoint2}" scrolling="no" /> SMS 3 (debug) <apex:iframe id="frontlinesmsIframe3" height="30" src="{!endpoint3}" scrolling="no" /> SMS 4 (debug) <apex:iframe id="frontlinesmsIframe4" height="30" src="{!endpoint4}" scrolling="no" /> SMS 5 (debug) <apex:iframe id="frontlinesmsIframe5" height="30" src="{!endpoint5}" scrolling="no" /> SMS 6 (debug) <apex:iframe id="frontlinesmsIframe6" height="30" src="{!endpoint6}" scrolling="no" /> SMS 7 (debug) <apex:iframe id="frontlinesmsIframe7" height="30" src="{!endpoint7}" scrolling="no" /> SMS 8 (debug) <apex:iframe id="frontlinesmsIframe8" height="30" src="{!endpoint8}" scrolling="no" /> SMS 9 (debug) <apex:iframe id="frontlinesmsIframe9" height="30" src="{!endpoint9}" scrolling="no" /> SMS 10 (debug) <apex:iframe id="frontlinesmsIframe10" height="30" src="{!endpoint10}" scrolling="no" /> </apex:pageBlockSection> </apex:pageBlock> </apex:form> </apex:page>Apex controller for campaign sends:
Learn Morepublic with sharing class extSendSMSCampaign { public Campaign campaign {get;set;} public Task dummyTask {get;set;} public String SMSmessage {get;set;} public String endpoint1 {get;set;} public String endpoint2 {get;set;} public String endpoint3 {get;set;} public String endpoint4 {get;set;} public String endpoint5 {get;set;} public String endpoint6 {get;set;} public String endpoint7 {get;set;} public String endpoint8 {get;set;} public String endpoint9 {get;set;} public String endpoint10 {get;set;} public Integer memberCount {get;set;} public Integer numberSent {get;set;} private List<String> endpoints = new List<String>(); private List<Task> SMSTasks = new List<Task>(); private Id whoId; public Boolean showActionBlock {get;set;} public Boolean showFrontlineSMSiframe {get;set;} private Contact contact; private Lead lead; private CampaignMember campaignMember; public extSendSMSCampaign(ApexPages.StandardController stdController) { this.campaign = (campaign)stdController.getRecord(); memberCount = [select count() from CampaignMember where CampaignId=:this.campaign.id AND status='sent' and (Contact.MobilePhone <> null OR Lead.MobilePhone <> null)]; showFrontlineSMSiframe = false; showActionBlock=false; SMSmessage = 'test message'; numberSent =0; dummyTask = new Task(); } public PageReference sendSMS(){ showActionBlock = true; showFrontlineSMSiframe = true; return null; } public PageReference sendSMSToMembers(){ endpoints = new List<String>(); endpoint1 = ''; endpoint2 = ''; endpoint3 = ''; endpoint4 = ''; endpoint5 = ''; endpoint6 = ''; endpoint7 = ''; endpoint8 = ''; endpoint9 = ''; endpoint10 = ''; SMSTasks = new List<Task>(); List<CampaignMember> myCampaignMembers = new List<CampaignMember>(); myCampaignMembers = [select id, Contact.MobilePhone,Contact.Id,Lead.Id, Lead.MobilePhone, status from CampaignMember where CampaignId=:this.campaign.id AND status='sent' and (Contact.MobilePhone <> null OR Lead.MobilePhone <> null) limit 10]; if(myCampaignMembers.size()>0){ for(Integer i=0;i<myCampaignMembers.size();i++){ CampaignMember thisMember = myCampaignMembers[i]; String strippedMobilePhone = ''; if(thisMember.Contact.MobilePhone!=null){ strippedMobilePhone = thisMember.contact.mobilePhone; whoId = thisMember.Contact.Id; } else if(thisMember.Lead.MobilePhone!=null){ strippedMobilePhone = thisMember.contact.mobilePhone; whoId = thisMember.Lead.Id; } strippedMobilePhone = strippedMobilePhone.replaceAll('[^0-9]', ''); endpoints.add('http://localhost:8565/'+ strippedMobilePhone +'/'+ dummyTask.Description); SMSTasks.add(new Task(WhoId = whoId,WhatId=this.campaign.id,type = 'SMS',ActivityDate = Date.Today(),Description=dummyTask.Description,Status='Completed',Subject='SMS: '+dummyTask.Description)); } numberSent += myCampaignMembers.size(); if(endpoints.size()>0){ While(endpoints.size()<10){ endpoints.add(''); } endpoint1 = endpoints[0]; endpoint2 = endpoints[1]; endpoint3 = endpoints[2]; endpoint4 = endpoints[3]; endpoint5 = endpoints[4]; endpoint6 = endpoints[5]; endpoint7 = endpoints[6]; endpoint8 = endpoints[7]; endpoint9 = endpoints[8]; endpoint10 = endpoints[9]; } for(CampaignMember myMember : myCampaignMembers){ myMember.status='Responded'; } update myCampaignMembers; insert SMSTasks; } else { showActionBlock = false; } return null; } }
Strategic impact and making multi-level relationships look nice
Last Updated on Wednesday, 5 May 2010 02:08 Written by Steve Wednesday, 5 May 2010 02:03
I’ve been doing a little prototyping in Salesforce.com as a volunteer project for a nonprofit in town. They’ve got an interesting need to track their strategic plans in Salesforce. I’ve seen this kind of thing built many times and have built it myself for another org or two. For this org they’ve got 3 levels of custom objects to hold their data. Driver is their high-level strategic focus. Goal is the next level down–there can be many goals for each Driver. And Metrics our measurable things related to each Goal.
Salesforce lets you create these objects and relationships between them very easily. I created the schema in just a few minutes. I created three objects and related them together–in this case I made them master-detail relationships. You can see in the diagram what my schema ended up looking like.
Now tracking your strategic goals is great, and most nonprofits do that. But where I think it gets more interesting is when we can tie the actual work we do directly to the metrics we’re trying to deliver on. So you’ll see in the diagram that there is a 4th object called project. Project is used to track all the work efforts of the org that are worth recording. I created a lookup relationship from Project to Metric so every project this groups takes on can be explicitly related to a Metric we need to deliver on. Which is in turn related to a Goal, which is related to a Driver. So, why are you updating that website for the 100th time this year? Because the website Project is meant to deliver on the Metric of increasing engagement of our supporters 25%, which is part of a Goal to grow the direct impact our supporters have in the world, which in turn feeds our Driver which calls for us to lead the world with a model of catalytic impact.
Those strategic objectives are made up for this example, but you get the idea how daily work can roll up to why your organization exists. Seeing that direct relationship can be very powerful. So when you look at the Project record in Salesforce, what do you see? Because there is only a relationship to the Metric object, you only see the Metric on the page layout.
Because the Project isn’t directly related to Goal or Driver, we can’t have lookup fields to those objects. Lookups are only for direct relationships, from child to parent, not up to grandparent and beyond. So we need another solution, and it turns out to be formula fields. On the Project I can create a Goal field and a Driver field that are formula fields of type Text. Here’s the formula for each of them:
for Goal: HYPERLINK("/" + Metric__r.Goal__r.Id , Metric__r.Goal__r.Name,"_self" )
for Driver: HYPERLINK( "/" + Metric__r.Goal__r.Driver__r.Id , Metric__r.Goal__r.Driver__r.Name,"_self" )
We’re creating a hyperlink to fake a real lookup. The URL of the hyperlink is relative, so it starts with / rather than http://. It then has the Id of the object, which we get by traversing up the relationships in Salesforce. From project we follow the Metric__r relationship to the Metric object, then up the Goal__r relationship to the Goal where we can grab any fields we want. With Driver we do the same thing, just go up one more level. If you think this looks hard, don’t worry, the formula field editor is really easy to use and does all the hard work for you–you just click the relationship you want to traverse.
The text of the link is the Name of the object in question. And then we target the hyperlink to “_self” so that it will load the record in question into the current window–just like a regular lookup would.
After adding these formula fields to the page layout, the end result is this:
Now we’re telling the full story about this project with a little help from simple formula fields.
Learn More