Harness the power of Salesforce data with ETL

by Babak Salimi  
12 min read  • 27 Apr 2018

It is not uncommon for businesses these days to use 10 to 30 different tools to run their go-to-market strategy. Business users like sales, marketing and operations teams all want to easily analyze data, gain insights and make decisions based on the data as near real-time as possible. But to make better data-driven decisions it is critical to integrate the data from all the various apps and silos. For example, using data from customer success experience to create more effective marketing campaigns. Or combining data from Google Analytics, Google AdWords, Salesforce and your marketing automation tool to answer questions like ‘Is this lead new or existing?’ and ‘What channel did this sale come from?’

Get the most out of your Salesforce data

For many organizations, Salesforce.com (cloud-based CRM) is a rich source of customer data, such as Accounts, Opportunities, Services, Community, Activities and Leads. Sales or marketing operations teams rely on this data to manage lead lists, enrich those lists with data providers and integrate marketing, product and external data to maximize sales efficiency and customer lifetime value, as well as reduce sale cycles. Today, savvy sales teams even before contacting a potential lead, can know what solutions they use, when the contract of a competing product expires, how many times they visited the website, which content they were interested in, what campaigns they responded to and which social media channels they are active on.

Extracting all this rich customer data (stored in Salesforce standard objects like Accounts, Contacts, Opportunities, or custom objects) and putting in a data warehouse (such as Google BigQuery), Amazon Redshift, or Snowflake) provides a single source of truth. Enriching the data will help prepare your data for analytics by cleansing, validating, joining, sorting, aggregating, mapping, derivation and adding information for context. A complete and enriched data warehouse enables complex analytical queries on large sets of data or streaming events for a 360-degree customer view, full customer journey details, real-time analytics, machine learning for personalization and more. Given the growing number of data sources with different formats to bring together, it is critical to be able to guarantee the accuracy and integrity of the transformed data before getting to the insights.

Most data teams rely on an Extract-Transform-Load (ETL) tool which brings data from disparate sources, databases or applications, such as marketing, support, ecommerce and sales, to the data warehouse. Alooma is an ETL platform for secure replication and enrichment of all such data (ie. Salesforce standard and custom objects) to a data warehouse in real-time. For speed and large volumes of data, Alooma uses the Salesforce Bulk API (vs. the standard REST API) most of the time to move millions of objects and fields from Salesforce to a data warehouse. You can even specify a limit on number of API calls if your Salesforce account requires the control. Alooma will try to read the updates as fast as possible while adhering to the API limit.

image alt text

Few example use cases:

  • Salesforce data integrated with Marketing Automation data Create a unified lead funnel to have an accurate view of the pipeline with all associated metrics in real-time that your sales & marketing teams can agree on. Plus automated feedback loop or insights on programs and campaign effectiveness. image alt text
  CASE WHEN campaigns.name IS NULL THEN 'No Campaign' ELSE campaigns.name END AS "Campaign",
  count(*) AS Leads,
  count(CASE WHEN leads.mql THEN 1 ELSE NULL END) AS "MQLs",
  count(opportunity_id) AS "Opportunities",
  sum(opportunities.amount) AS "New Pipeline",
  count(CASE WHEN opportunities.won THEN 1 ELSE NULL END) AS "Wins",
  sum(CASE WHEN opportunities.won THEN opportunities.amount ELSE 0 END) AS "New Revenue"
FROM leads
  LEFT JOIN opportunities ON (leads.opportunity_id = opportunities.id)
  LEFT JOIN campaigns ON (leads.source = campaigns.name)
WHERE date_trunc('year', leads.created_date) = '2017-01-01'
GROUP BY campaigns.name
  • Salesforce data integrated with Finance data Automate the cross-sell and renewal opportunities to maximize customer lifetime value. Centralize all your CRM and ERP information to have a single source of truth.

image alt text

  10 * (ARR + ("Cross-sell Pipeline" * "Win Probability")) AS "Lifetime Value"
    netsuite_customers.company           AS "Customer",
    max(netsuite_customers.arr)          AS "ARR",
    max(netsuite_customers.next_renewal) AS "Renewal Date",
    count(CASE WHEN is_open
      THEN 1 ELSE NULL END)              AS "Open Opportunities",
    sum(CASE WHEN is_open
      THEN amount ELSE 0 END)            AS "Cross-sell Pipeline",
    count(CASE WHEN won
    count(CASE WHEN won OR lost
      THEN 1 ELSE NULL END)              AS "Win probability"

  FROM netsuite_customers
    LEFT JOIN opportunities ON netsuite_customers.salesforce_account_id = opportunities.account_id

 GROUP BY netsuite_customers.company
  • Salesforce data integrated with Google Analytics, Facebook Ads or Mixpanel pixel tracking data Optimize your advertising budget and campaign effectiveness with personalization and feedback loop. Improve your sales funnel as well as the quantity and quality of your leads.

image alt text

  facebook_ads.ad_name            AS "Facebook Ads",
  sum(facebook_ads.impressions)   AS "Impressions",
  sum(facebook_ads.clicks)        AS "Clicks",
                                  AS "MQLs",
  count(DISTINCT opportunity_id)  AS "Opportunities",
  sum(amount) AS "New Pipeline",
          (CASE WHEN WON THEN opportunity_id ELSE NULL END)) 
                                  AS "Wins",
                                  AS "New Revenue"

FROM facebook_ads
  LEFT JOIN leads on lead_source_ad_id = facebook_ads.ad_id
  LEFT JOIN opportunities on leads.converted_opportunity_id = opportunities.opportunity_id
GROUP BY facebook_ads.ad_name

Make life easy

Alright, so this all sounds good. But how hard is it? Modern ETL tools like Alooma can make life easier by saving hours of painstaking manual work. For example, you can simply connect Alooma to your Salesforce account and select the data you like to move or enrich.

  1. Create Connections between your Salesforce site and your destination data warehouse.
  2. Select Salesforce objects to copy and specify the fields you'd like to replicate from Salesforce. By default Alooma moves the Lead, Opportunity, Contact and Account objects.
  3. Define the date you'd like to start pulling data from.
  4. Update the data in the data warehouse with fresh data from Salesforce automatically.
  5. If you have strict Salesforce API call quotas, you can set a quota for the daily and bulk APIs.
  6. Sit back and watch your data flow from Salesforce into the data warehouse in near real-time.

Automatic Schema Creation

You don't need to prepare the data warehouse - Alooma simply creates the tables, corresponding to the Salesforce objects in the data warehouse automatically.

Change Data Capture (CDC)

Copying data from Salesforce to your data warehouse is just a part of the problem. Real-time analytics require data in the data warehouse to be constantly up-to-date with Salesforce. Alooma’s ETL platform will painlessly ensure you always have the most current data from Salesforce in your data warehouse.

After the first replication of all the Salesforce data, subsequent replications update the data warehouse data incrementally with refreshes from Salesforce, in near real-time. Data warehouse data will always be up-to-date in a matter of minutes automatically without any user intervention.

If you already are using Alooma for another integration and would like to add Salesforce, you can get started by referring to our documentation or get in touch with us.

This might interest you as well