In the beginner’s mind there are many possibilities.

The developer side of my brain refuses to believe that there is only one way to do things, so after spending a great deal of time and effort to integrate SalesForce data into Omniture using the standard Genesis integration I felt that there had to be another way.  If you have not read the recent posts by Adam Greco on getting CRM data into Omniture, stop reading this post and go read those first.  Then come back!

What I needed to do was find a way to get a few data points out of SalesForce and into Omniture SiteCatalyst.  The solution also needed to be automated so that it could run every 3-4 hours.  After thinking over the topics discussed is Adam’s blog post I had a plan.

The Challenge:

State over time.  That is what this whole exercise comes down to.  How to get Omniture SiteCatalyst to store and measure the state of a data point over a set time.  These data points are up to you and your specific business needs.  Some examples are revenue, leads, etc..

The granular level of data you can/want to pull over is limited only by your your business objectives.  For the purpose of this post, I will only be talking about getting data at a campaign level.

The Setup:

Transaction ID’s and a matching key in SalesForce.  If you can, I strongly recommend using the SalesForce campaign id’s as your web analytics campaign ID’s.  It give you a great 1:1 data point between the two systems.  It also makes it easy to use that campaign ID as your transaction ID.

Incrementor Events.  (2 for every data point in SalesForce that you want to integrate)  These are needed to accomplish the heavy lifting of the state over time.   If you want to track revenue attributed to a specific campaign for example, you create 2 events.  One revenue_positive event, and one revenue_negative event.   Later you will create a calculated metric to get the delta of these two events to get the actual number.

State Manager.  You have to have some mechanism to get the data out of SalesForce and then be able to determine the difference in the value of the data points you want to integrate into Omniture.  There are numerous ways that you could accomplish this, but I chose to write a custom ColdFusion / SQL solution.

The flow chart above shows at a high level all of the steps in the process.

1) Pull a list of transaction ids/ campaign ids from Omniture and store that list in a new db table.

2) Loop over that table and query SalesForce to get the data points you want to move, and store that in a second database table.

3) The very first time you run this process you are creating the state.  This copies the data into the third state database table.  Each time after that, you compare the values in the 3rd table with the data in the 2nd table.   This drives the values you pass into the incrementor events.   If the first time you pull data from SalesForce to find the revenue for a campaign is $100.00 that gets stored in the third table.  You pass 100 to the revenue_positive and 0 to the revenue_negative.  The next data pull the revenue is now 110.  After the comparison, you pass a 10 to the revenue_positive and 0 to the revenue_negative.  Now on a future data pull, you find the revenue is down to 50.  You now send a 0 in the revenue_positive and a 60 to the revenue_negative events.   When you create the calculated metric you will see the new value of 50 for that calculated metric.

<cfset variables.revenuewon_str="#variables.revenue_pos_event#=0|#variables.revenue_neg_event#=0">
<cfif variables.DIF_revenuewon EQ 0>
	<cfset variables.revenuewon_str="#variables.revenue_pos_event#=0|#variables.revenue_neg_event#=0">
<cfelseif variables.DIF_revenuewon GT 0>
	<cfset variables.revenuewon_str="#variables.revenue_pos_event#=#variables.DIF_revenuewon#|#variables.revenue_neg_event#=0">
<cfelse>
	<cfset variables.revenuewon_str="#variables.revenue_pos_event#=0|#variables.revenue_neg_event#=#ABS(variables.DIF_revenuewon)#">
</cfif>

The code sample above evaluates the difference in the data point between tables 2 and 3 and generates the string required to send the data to Omniture.

4) Push the data to Omniture.  After you create the txt file with the transaction id and the values for the events, set up an automatic ftp using the Omniture DataSources.

5) Automate.  Since the controlling code to power this process was ColdFusion, all that was required was to set up a scheduled task within the ColdFusion admin and done!

The Result:

Now, right inside of SiteCatalyst, you can see SalesForce data points that matter to you in your reports.  Of course, the data will look much cleaner that my mocked up image…hopefully.