Harness the power of Salesforce data with ETL
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.
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.
SELECT
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.
SELECT *,
10 * (ARR + ("Cross-sell Pipeline" * "Win Probability")) AS "Lifetime Value"
FROM (
SELECT
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
THEN 1 ELSE NULL END) /
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.
SELECT
facebook_ads.ad_name AS "Facebook Ads",
sum(facebook_ads.impressions) AS "Impressions",
sum(facebook_ads.clicks) AS "Clicks",
count(DISTINCT
(CASE WHEN mql IS TRUE THEN lead_id ELSE NULL END))
AS "MQLs",
count(DISTINCT opportunity_id) AS "Opportunities",
sum(amount) AS "New Pipeline",
count(DISTINCT
(CASE WHEN WON THEN opportunity_id ELSE NULL END))
AS "Wins",
sum(CASE WHEN WON THEN amount ELSE NULL END)
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
