Translating Data into Actionable Insights: Development of Data Studio Connector for FreshSales Deals

Startups lack resources. They start small, think big and wish to scale fast. At the same time, they expect data and analytics to lie at the heart of their decision-making process.

Despite the lack of engineering resources in growth teams, one of the fundamental focus of the new-age SaaS companies is empowering their Sales & Marketing teams (non-engineers) to make data-driven decisions independently. 

Additionally, these SaaS startups (<2 Mn USD ARR) rarely have the capability to shell out resources on tasks pertaining to sales and marketing analytics like building custom Business Intelligence reporting dashboards, handling data integration initiatives, implementing Customer Data Infrastructure or even, purchasing licenses of custom BI tools.

In the last couple of weeks, as we worked closely with multiple SaaS startups, we became more familiar with their growth processes, sales frameworks, and marketing automation stacks. 

One of the Startups we worked with used Freshsales as its primary CRM. The S&M team was heavily reliant on the tool and handled outbound and inbound marketing from the same interface.

Weaving & streamlining outbound and inbound growth together in one place is an intricate task. As we went around customizing the CRM modules, workflows, list management etc., we discovered that the internal growth team failed to collect, transform, access, understand and act on the information they have available.

“Marketers & SDRs were spending as much as 70% of their time in managing data, which left little room to build and execute their growth strategy.”

With the aim of empowering growth teams with true data agility, we started focussing on the bottom of the sales funnel, where we expected to find the best signs of product-market fit. We discovered that the collection of data in the deal pipeline was already solved. SDRs were diligently updating deals (stages, notes, inferences, expected close date etc.), assigning owners etc. The problem, however, lay in accessing granular data in a consumable format, that makes it easy for the sales team to derive actionable insights from it.

As we scouted for solutions to the problem, we realized the need to develop a custom reporting dashboard, powered by Freshsales Deals API. Given that we had low budgets, we were inclined towards a low-cost BI tool with minimal upfront resource requirements. We chose to go ahead with Google Data Studio.

The idea was fairly simple — we will develop a Google Data Studio Community Connector for Freshsales Deals API, which will accept Freshsales API Key and Deal pipeline’s view Id as inputs. We will use the connector to build a data source that fetches data from Freshsales API and renders it in the widgets/charts in a Google Data Studio report.

We went through the Freshsales Deals API (auth, pagination, GET/POST request, payloads etc.) and identified relevant endpoints for fetching data.

We started exploring methods to develop Google Data Studio community connector using Google Apps Script and went through the standard API reference, docs and tutorials.

Every Google Data Studio connector implements an interface provided by Google, with the following functions -

1. getAuthType — identifies authentication required for 3rd party services, if needed. Since we were accepting Freshsales API key from the users before connecting the connector to the report, we had set it to NONE.

function getAuthType() {
  var AuthTypes = cc.AuthType;
  return cc
    .newAuthTypeResponse()
    .setAuthType(AuthTypes.NONE)
    .build();
}

2. getConfig — provides the configuration of the connector including user inputs. All inputs — Freshsales API key and Deal pipeline view Id — and their types were defined here.

function getConfig() {
  var config = cc.getConfig();
  config
    .newTextInput()
    .setId('api_key')
    .setName(
      'Enter your Freshsales API Key'
    )
    .setHelpText('You can find your Freshsales API Key in Settings of your Freshsales Account')
  
  config
    .newTextInput()
    .setId('view_id')
    .setName(
      'Enter your Deals View Id'
    )
    .setHelpText('You can find your Deals View Id in the URL of your Deals Pipeline of your Freshsales Account')

  return config.build();
}

3. getSchema — provides the schema for the given request. In this case, we had created a function called getFields, where we defined 5 dimensions — Id, Name, Amount, Deal Stage and Created At. These were the primary properties which we wanted to work within the reporting dashboard.

function getFields() {
  var fields = cc.getFields();
  var types = cc.FieldType;
  var aggregations = cc.AggregationType;
  
  fields
    .newDimension()
    .setId('amount')
    .setName('Amount')
    .setType(types.CURRENCY_INR);

  fields
    .newDimension()
    .setId('id')
    .setName('Id')
    .setType(types.NUMBER);
  
  fields
    .newDimension()
    .setId('name')
    .setName('Name')
    .setType(types.TEXT)
  
  fields
  .newDimension()
  .setId('deal_stage')
  .setName('Deal Stage')
  .setType(types.TEXT)
  
  fields
  .newDimension()
  .setId('created_at')
  .setName('Created At')
  .setType(types.YEAR_MONTH_DAY)

  return fields;
}

4. getData — fetches data for all requests. We made API calls to Freshsales Deals API endpoint here and parsed the response in the correct format for feeding into the report.

function getData(request) {
  
  var requestedFields = getFields().forIds(
    request.fields.map(function(field) {
      return field.name;
    })
  );
  
  var page_number,total_pages=1;
  var apiResponse, normalizedResponse, data, total_data;
  
  try {
    for (page_number=1; page_number<=total_pages; page_number++){
      apiResponse = fetchDataFromApi(request,page_number);
      normalizedResponse = normalizeResponse(request, apiResponse);
      [data, total_pages] = getFormattedData(normalizedResponse, requestedFields);
      if (page_number == 1)
        total_data = data;
      else
        total_data = total_data.concat(data);
    }
  } catch (e) {
    cc.newUserError()
      .setDebugText('Error fetching data from API. Exception details: ' + e)
      .setText(e.toString()
      )
      .throwException();
  }

  return {
    schema: requestedFields.build(),
    rows: total_data
  };
}

Given that Freshsales Deals API provides deal stage ids, instead of the names of deal stages, we implemented a utility function to map the deal stage id against the relevant stage.

After developing the connector, we edited the manifest file, with correct properties and values, and finally, deployed it.

The connector’s configuration screen accepted Freshsales API Key and Deals View Id.

Upon filling the relevant details, we could see the requested fields — Amount, Created At, Deal Stage, Id and Name in the connector.

As we created the report, GDS automatically created a tabular chart, listing all the deals in the Deal Pipeline.

Upon customization of the report (addition of filters, custom variables, added charts etc.), we arrived at the following dashboard -

Using a simple setup like this, we were able to quickly track and measure the pipeline value, sales velocity, drop off in deal funnel at each step, win rate etc. The internal growth team appreciated our work and liked the report as it provided a comprehensive view of the deal pipeline.

Interventions like this empower sales and marketing teams, and boost their confidence in the decision making process. These initiatives bring data at the forefront, influence mindsets and behaviours of teams, and help startups in developing and shaping a data-driven culture.

P.S.- At SaaS Growth Advisory, we engage closely with SaaS startups and help them build scalable and repeatable customer acquisition processes. We are engineering SaaS growth and constantly blending technology with marketing to deliver superior frameworks, tools, and processes to unlock 10x growth of SaaS startups. If you wish to get in touch with us, please write to me at shikhar[at]saasgrowthadvisory.com or reach out to me over LinkedIn.