RAW Labs
Published in

RAW Labs

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:

# users per month for current account

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.

User activity in CSV format

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:

users_per_month_for_account(“ACME”) 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 -f

Now 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.

Chart generated via Callout embedded into Salesforce Account page

--

--

--

Platform to Create, Publish and Manage Data Products

Recommended from Medium

FlexBox — The Saviour

Clickhouse

How to set up a usable distributed sqlite DB using a microk8 multi-architecture cluster

Formatting NeoVim for Golang Development on OSX

Deploying React-App to Heroku using Github.

Test HTTP Handlers in Go

Useful Tools in Software Testing

Still learning Java for the past 6 months. Here’s what i found.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Georges Lagardère

Georges Lagardère

More from Medium

Why IaC is complex but simple

Cloud Migration & Transformation Strategies (Migration Framework)

Cloud Transition & Migration Strategy — Migration Framework

No Code Tableau and Salesforce Integration

Simplify Your Business’ Document Generating Experience by Creating Reusable Templates that Meet the…