A Visual Guide to 转换: the Salesforce Data Model

Analysis-ready data 模型 are built using sequences of transformations. Here's an example using Fivetran’s data model for Salesforce.
2022年2月16日

Data transformation is an essential step in data integration. It's the process by which raw data is cleansed 和 manipulated to get to a usable analysis-ready state. 转换可以连接不同的数据集, 删除重复的记录, 过滤掉的数据, 进行计算以更改数据和时间格式, 和更多的. 它非常有价值,因为没有转换, companies lack the ability to convert data into meaningful insights that influence critical business decisions. 

Fivetran数据模型 are off-the-shelf tools that allow data analysts or data engineers to effortlessly transform raw data from common sources into data 模型 for reports 和 dashboards. 这为分析人员节省了大量的时间和精力. 

今天许多公司依赖于数据 Salesforce 对他们的客户群有更深入的了解. 的 customer relationship management (CRM) platform unites marketing, 销售, 商务, 和 service functions with a wide range of products 和 services to sell smarter 和 market more effectively. As a result, Salesforce data sets are often large 和 complex. 在这篇博客, we walk through creating a running snapshot of a 销售 team’s performance using the Fivetran data model for Salesforce.  

的 data model for Salesforce allows you to read from the following raw tables from the Salesforce API [1]:

  • 账户:当前和潜在客户 
  • 用户销售人员和他们的经理     
  • 机会:正在进行中的交易
  • 用户角色:经理职位的参考名单

的 raw tables are ultimately turned into the following finished data 模型 [2]:

  • 销售快照: a summary of performance in the last 月 和 quarter in terms of bookings, 管道, 损失, 和赢利率
  • 主人的性能: bookings, 管道, 损失, 和赢利率 by individual 销售person
  • 管理器的性能:预订,管道,损失和赢得率由经理
  • 机会增强: an 机会 enriched with related data about the 账户 和 机会 owner.

皇冠手机app下载详细讨论生成的步骤 销售快照 [3] data model, which summarizes bookings, 管道, 损失, 和 wins in the last 月 和 quarter.


机会, 账户用户 表用于生成 销售快照 模型, 机会增强 作为中间步骤. 严格地说, 账户用户 不需要构造 销售快照 模型,但对于构造 主人的性能管理器的性能 模型.

的 first step in the sequence of transformations is staging the data in a format that will be easier to work with. 对于每个原始数据表 id的名字 columns are re的名字d to be unique to avoid confusion when working with columns from all three tables. This pr事件 duplicate columns between tables, making the joined table easier to interpret. 具有重命名列的staging表以 stg.



机会 table, we also need to determine whether each 机会 was created 和/or closed in the last 月 和 quarter in order to create the snapshot. 这将导致许多新的列: is_created_this_月, is_created_this_quarter, days_since_created, days_to_close, is_closed_this_月is_closed_this_quarter.

皇冠手机app下载需要比较 created_date 与当前日期, 月, 和 quarter to determine how old the 机会 is 和 if it was created in the current 月 和 quarter, 分别.

之间的区别 created_dateclose_date 这个机会需要多长时间才能关闭. 

close_date is compared with the current 月 和 quarter to determine if the 机会 was closed in the current 月 和 quarter, 分别.

的 “staging” tables for `账户` 和 `用户` are joined to the staging table for `机会` using the `账户_id` 和 `用户_id` keys.


每条记录的 机会 is assigned a 状态 – “booking,” “管道,” “lost,” “won” 和 “other” – based on flags for is_wonis_closed 和指定的类别 forecast_category. For each 机会, columns are also created for amounts 和 counts by quarter 和 月.

的 final data model aggregates the data from `机会增强` by 状态. It has a number of fields that provide counts 和 amounts for the following 销售 状态es in the last 月 和 quarter:

  • 预订
  • 管道
  • 失去了
  • 赢得%

的se are created by joining together common table expressions derived from the `机会_enhanced` table on the basis of `状态` values 和 from summing up closed 和 created figures.

的 finished data model offers metrics you can use to produce a report or dashboard summarizing your 销售 team’s performance in the past 月 和 quarter. 

Fivetran’s data 模型 are constantly being updated 和 refined. This Salesforce example is current as of January 2022 和 does not by any means exhaustively represent the analysis that is possible using Salesforce data. Expect improvements to existing 模型 和 new 模型 in the future!

学习 how to monitor 管道s, load data 和 trigger dbt 模型 to transform data using Fivetran转换.

开始免费

Join the thous和s of companies using Fivetran to centralize 和 transform their data.

谢谢你!! 你的意见书已收到!
哦! 在提交表单时出现了错误.