Embedded Analytics inside SaaS with RAW
In my previous post I described one way to sync Excel data with Salesforce fields, here I will explain how to import and display analytics from an external source into a SaaS application (Salesforce).
This process is sometimes called “Reverse ETL” when the data comes from a datawarehouse or a data lake.
The use case I will address here is the one where a SaaS company is selling a number of user licenses and wants to see a chart in Salesforce with the number of licences actually used per month vs. the number sold. The number of licences are computed from a backend system where users’ logins and activities are tracked. Every time a Salesforce user opens an Account detail page they will get a chart displayed like this:
I am going to show how to do this with RAW Labs Just-Ask, some APEX code and a Visualforce Page in Salesforce.
Data source
The user data activity is available as a CSV file in a AWS S3 bucket registered in Just-Ask (this is done in the Setting | S3 menu).
The SaaS software backend updates this file every night at midnight by appending it with the daily activity, just like in a typical data lake scenario.
Very basically, we want to count how many users have had some activity per month.
users_per_month_for_account(account: string) :=
select Month, count(UserID) as Users
from read_csv("S3:///user_activity.csv",
has_header:=true,
delimiters :=[";"])
where Company=account
group by substring(Date,4,2) as Month
order by Month;Here, I have created this function which takes as parameter the account name and returns rows with the month and the number of users. This function has been saved in a package named raw360 (via the publish button).
By entering the following in the Just-Ask scratchpad:
from raw360 import users_per_month_for_account
users_per_month_for_account("ACME")I get this output:
Once published, the function becomes accessible via a REST API in the following form.
curl -X GET https://just-ask.raw-labs.com/api/executor/1/public/query-package/raw360/users_per_month_for_account?account=ACME — header “Authorization: Bearer your_token_here ” — header “X-RAW-output-format: json”To get access authorization to the endpoint, you need an “access_token” which is made available in the ~/.rawcli file after running the following command :
rawcli login -fNow that we have our data endpoint ready let’s look at the Salesforce part.
Salesforce APEX and VF code
Custom development in Salesforce is based on a MVC architecture. To make my use case work, I will need a Visualforce page for the presentation and an APEX class to query the data an populate the page form.
The VF page is based on an Account controller extended with the “rawlabsQuery” Apex class that I have implemented. The chart data is passed through the “usersPerMonth” variable and the maximum number of users via “maxUsers” and drawn as a line overlaying the chart.
For data retrieval, the “UsersPerMonth” inner class is used to deserialize the JSON returned by the API and serves as a List to pass chart data to the Visualforce page.
The rawLabsQuery class retrieves the current account Name and Contractual_maximum_users_c fields. The later being a custom field where the number of users sold by the SaaS company to this account is stored.
You can read the details about how to set authentication and callout mechanisms in this post.
The APEX code needs to be compiled (this is done in the Setup | Apex Classes tab) and the VF page needs to be enabled (Setup | Visual Force page, edit VF and enable check box).
The final thing to do is to add the VF Page to the Account page in the Edit Page menu where you can drag an drop the Object containing the chart on the Account’s detail page for instance.
The chart showing the number of users per month and the maximum number of users is now going to displayed on every account’s detail page whenever opened.
