CRM categoryCertified by Stitch

Expected Salesforce data

Here’s a sample of the raw Salesforce data that Stitch will replicate to your analytics warehouse:

Accounts

Represents an individual account, which is an organization or person involved with your business (such as customers, competitors, and partners).

Table name:account

Opportunities

Represents an opportunity, which is a sale or pending deal.

Table name:opportunity

Leads

Represents a prospect or potential Opportunity.

Table name:lead

Users

Contains info about the users in your organization.

Table name:user

Contacts

Contains info about your contacts, who are individuals associated with accounts in your Salesforce instance.

Table name:contact

View all tables

Learn more about the Salesforce integration

Documentation

Detailed documentation on how to start syncing Salesforce data.

Salesforce Documentation

Salesforce to your data warehouse in minutes

ETL your Salesforce data to the destination of your choice.

Jumpstart your Salesforce analytics with reusable blocks

dbt packages can speed up your work

Once you replicate your Salesforce data with Stitch, you can use it in many ways. For example, you can use the data modeling and transformation tool dbt to prepare data for reporting, analytics, or machine learning applications.

Dbt has prebuilt packages for many Stitch data sources, including Salesforce. Here’s a look at code for modeling Salesforce data. This particular block of code creates a daily model of your Salesforce opportunities.

View the source on GitHub →

--the output of this model is a day per opportunity history that the opp was active
{{
    config(
        materialized='table',
        sort='date_day',
        dist='opportunity_id'
    )
}}
{%- set custom_fields = var('opportunity_history_custom_fields') -%}
with opp_history as (
    select distinct
        date_trunc('day', created_date)::date as start_date,
        date_trunc('day', active_to)::date as end_date,
        opportunity_id,
        account_id,
        owner_name,
        stage_name
        {{ "," if (custom_fields|length) > 0 }}
        {% for custom_field in custom_fields %}
            last_value({{custom_field}}) ignore nulls over (partition by opportunity_id,
                created_date order by created_date rows between unbounded preceding and
                unbounded following) as {{custom_field}}{{"," if not loop.last}}
        {% endfor %}

    from {{ref('sf_opportunity_history_joined')}}

),

days as (

      {{ dbt_utils.date_spine(datepart="day",
        start_date="to_date('{{ var('first_record') }}', 'mm/dd/yyyy')",
        end_date="dateadd(week, 1, current_date)") }}

),

opp_days as (
--this creates the final output of one row per day the stage was active
    select
    
        days.date_day,
        date_trunc('month', days.date_day)::date as date_month,
        opp_history.*
    
    from days
    inner join opp_history
      on days.date_day >= opp_history.start_date
     and days.date_day < opp_history.end_date

)

select
    {{ dbt_utils.surrogate_key('date_day','opportunity_id') }} as id,
    *
from opp_days

... or use Looker blocks with your Salesforce data

We've developed a Looker Block for Salesforce data provisioned by Stitch. This block includes prebuilt code to create dashboards and models that can help uncover insights from your Salesforce data.

This Looker Block includes three dashboards that provide analysis on sales and marketing leadership, sales ops management, and sales representative performance. The dashboards highlight top-level sales metrics, conversion rates between funnel stages, and various metrics to assess your pipeline health. The LookML file shown here produces a dashboard that can be used to monitor lost and won deals, revenue, and win rate by representative to evaluate performance of your sales organization.

View the source on GitHub →

- dashboard: representative_performance
  title: "Sales Representative Performance Dashboard"
  layout: grid
  rows:
    - elements: [count_won_deals, salesrep_total_revenue, count_lost_deals, win_percentage ]
      height: 150
    - elements: [opportunities_to_wins_trend_peers]
      height: 400
    - elements: [salesrep_revenue_won_comparison]
      height: 400
    - elements: [salesrep_win_rate_comparison, salesrep_revenue_pipeline_comparison]
      height: 400

  filters:

  - name: sales_rep
    type: field_filter
    explore: opportunity
    field: opportunity_owner.name

  - name: sales_segment
    type: field_filter
    explore: account
    field: account.business_segment

  elements:

  - name: count_won_deals
    title: 'Count of Won Deals (This Quarter)'
    type: single_value
    model: salesforce
    explore: opportunity
    measures: [opportunity.count_won]
    listen:
      sales_segment: account.business_segment
      sales_rep: opportunity_owner.name
    filters:
      opportunity.close_date: last quarter
    limit: 500
    font_size: small
    text_color: '#49719a'
    width: 3
    height: 2

  - name: salesrep_total_revenue
    title: 'Salesrep - Total Revenue (This Quarter)'
    type: single_value
    model: salesforce
    explore: opportunity
    measures: [opportunity.total_revenue]
    listen:
      sales_segment: account.business_segment
      sales_rep: opportunity_owner.name
    filters:
      opportunity.close_date: 'last quarter'
    limit: 500
    font_size: small
    text_color: '#49719a'
    width: 3
    height: 2

  - name: count_lost_deals
    title: 'Count of Lost Deals (This Quarter)'
    type: single_value
    model: salesforce
    explore: opportunity
    measures: [opportunity.count_lost]
    listen:
      sales_segment: account.business_segment
      sales_rep: opportunity_owner.name
    filters:
      opportunity.close_date: 'last quarter'
    limit: 500
    font_size: small
    text_color: '#49719a'
    width: 3
    height: 2

  - name: win_percentage
    title: 'Win Percentage of Closed Deals (This Quarter)'
    type: single_value
    model: salesforce
    explore: opportunity
    measures: [opportunity.win_percentage]
    listen:
      sales_segment: account.business_segment
      sales_rep: opportunity_owner.name
    filters:
      opportunity.close_date: 'last quarter'
    limit: 500
    font_size: small
    text_color: '#49719a'
    width: 3
    height: 2

  - name: opportunities_to_wins_trend_peers
    title: 'Opportunities to Wins by Rep'
    type: looker_line
    model: salesforce
    explore: opportunity
    dimensions: [opportunity.created_month, opportunity_owner.rep_comparitor]
    pivots: [opportunity_owner.rep_comparitor]
    measures: [opportunity.count, opportunity.count_won]
    dynamic_fields:
    - table_calculation: opportunities_to_won
      label: opportunities_to_won
      expression: 1.0*${opportunity.count_won}/${opportunity.count}
      value_format: '#.0%'
    hidden_fields: [opportunity.count_won, opportunity.count]
    listen:
      sales_rep: opportunity_owner.name_select
    filters:
      opportunity.created_month: 9 months ago for 9 months
    sorts: [opportunity.created_month desc, opportunity_owner.rep_comparitor]
    limit: 500
    column_limit: 50
    query_timezone: America/Los_Angeles
    stacking: ''
    colors: ['#FFCC00', '#1E2023', '#3399CC', '#CC3399', '#66CC66', '#999999', '#FF4E00', '#A2ECBA', '#9932CC', '#0000CD']
    show_value_labels: false
    label_density: 25
    font_size: small
    legend_position: center
    x_axis_gridlines: false
    y_axis_gridlines: true
    show_view_names: true
    y_axis_combined: true
    show_y_axis_labels: true
    show_y_axis_ticks: true
    y_axis_tick_density: default
    y_axis_value_format: '#%'
    show_x_axis_label: true
    show_x_axis_ticks: true
    x_axis_scale: auto
    show_null_points: true
    point_style: none
    interpolation: linear
    width: 12
    height: 4

  - name: salesrep_revenue_won_comparison
    title: 'SalesRep - Revenue Won comparison'
    type: looker_bar
    model: salesforce
    explore: opportunity
    dimensions: [opportunity_owner.rep_comparitor]
    measures: [opportunity.average_revenue_won]
    listen:
      sales_rep: opportunity_owner.name_select
    sorts: [opportunity_owner.rep_comparitor]
    limit: 500
    query_timezone: America/Los_Angeles
    stacking: ''
    colors: ['#FFCC00', '#1E2023', '#3399CC', '#CC3399', '#66CC66', '#999999', '#FF4E00', '#A2ECBA', '#9932CC', '#0000CD']
    show_value_labels: true
    label_density: 25
    label_color: ['#3399CC']
    font_size: small
    legend_position: center
    hide_legend: false
    x_axis_gridlines: false
    y_axis_gridlines: true
    show_view_names: false
    y_axis_combined: true
    show_y_axis_labels: true
    show_y_axis_ticks: true
    y_axis_labels: [Total Revenue Won]
    y_axis_tick_density: default
    show_x_axis_label: false
    show_x_axis_ticks: true
    x_axis_scale: auto
    show_null_labels: false
    width: 6
    height: 3

  - name: salesrep_win_rate_comparison
    title: 'SalesRep - Win Rate Comparison'
    type: looker_bar
    model: salesforce
    explore: opportunity
    dimensions: [opportunity_owner.rep_comparitor]
    measures: [opportunity.win_percentage]
    listen:
      sales_rep: opportunity_owner.name_select
    sorts: [opportunity_owner.rep_comparitor]
    limit: 500
    query_timezone: America/Los_Angeles
    stacking: ''
    colors: ['#FFCC00', '#1E2023', '#3399CC', '#CC3399', '#66CC66', '#999999', '#FF4E00', '#A2ECBA', '#9932CC', '#0000CD']
    show_value_labels: true
    label_density: 25
    label_color: ['#3399CC']
    font_size: small
    legend_position: center
    hide_legend: false
    x_axis_gridlines: false
    y_axis_gridlines: true
    show_view_names: false
    y_axis_combined: true
    show_y_axis_labels: true
    show_y_axis_ticks: true
    y_axis_labels: [Opportunity Win Rate]
    y_axis_tick_density: default
    show_x_axis_label: false
    show_x_axis_ticks: true
    x_axis_scale: auto
    show_null_labels: false
    width: 6
    height: 3

  - name: salesrep_revenue_pipeline_comparison
    title: 'SalesRep - Revenue Pipeline comparison'
    type: looker_bar
    model: salesforce
    explore: opportunity
    dimensions: [opportunity_owner.rep_comparitor]
    measures: [opportunity_owner.average_revenue_pipeline]
    listen:
      sales_rep: opportunity_owner.name_select
    sorts: [opportunity_owner.rep_comparitor]
    limit: 500
    query_timezone: America/Los_Angeles
    stacking: ''
    colors: ['#FFCC00', '#1E2023', '#3399CC', '#CC3399', '#66CC66', '#999999', '#FF4E00', '#A2ECBA', '#9932CC', '#0000CD']
    show_value_labels: true
    label_density: 25
    label_color: ['#3399CC']
    font_size: small
    legend_position: center
    hide_legend: false
    x_axis_gridlines: false
    y_axis_gridlines: true
    show_view_names: false
    y_axis_combined: true
    show_y_axis_labels: true
    show_y_axis_ticks: true
    y_axis_labels: [Total Revenue Pipeline]
    y_axis_tick_density: default
    show_x_axis_label: false
    show_x_axis_ticks: true
    x_axis_scale: auto
    show_null_labels: false
    width: 6
    height: 3

Thanks to Stitch we get the granular insight we need into our data. Having our Salesforce and internal data in one place was a key factor in helping us scale out our new business function.

Taylor Udell

Lead Solutions Architect, Heap

Why our customers choose Stitch

Stitch is a simple, powerful ETL service built for developers. Stitch connects to your first-party data sources – from databases like MongoDB and MySQL, to SaaS tools like Salesforce and Zendesk – and replicates that data to your warehouse. With Stitch, developers can provision data for their internal users in minutes, not weeks.

Explore all of Stitch's features

Simple setup: Start replicating data in minutes, and never worry about ETL maintenance.
Own your own data infrastructure: Stitch replicates to your warehouse, meaning you’re always in control.
Mature replication engine: Accurate data from any structure, all the time.

Explore all of Stitch's features

Start replicating your Salesforce data

Select your integrations, choose your warehouse, and enjoy Stitch free for 14 days.

Set up in minutesUnlimited data volume during trial

Connect to your ecosystem of data sources

Stitch integrates with leading databases and SaaS products. No API maintenance, ever, while you maintain full control over replication behavior.