Connecting Salesforce to Google Data Studio: A Comprehensive Guide

By: Published: July 28, 2020

Introduction

The multifaceted Salesforce database offers a variety of features to manage data efficiently. It is a robust cloud computing service which can be further integrated with Data Studio to enhance data manipulation. In this article, you can explore the key features of these platforms, along with how to connect Salesforce to Google Data Studio. The article also talks about the challenges in the integration process and alternative solutions.

Here is an outline of what you will cover in this blog:

Hevo, A Simpler Alternative to Integrate your Data for Analysis

Hevo offers a faster way to move data from databases or SaaS applications like Salesforce into your data warehouse to be visualized in a BI tool for free. Hevo is fully automated and hence does not require you to code.

Get Started with Hevo for Free

Check out some of the cool features of Hevo:

  • Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Real-time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
  • Scalable Infrastructure: Hevo has in-built integrations for 100 plus sources(Including 30+ Free Sources like Salesforce) that can help you scale your data infrastructure as required.
  • 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support call.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

Salesforce Highlights

The Salesforce database has a comprehensive architecture build with the central goal of creating a customizable interface for the customers. It enables reliable data security and protection, along with making sure that the integrity of sensitive data is not compromised. 

The database is a well-designed platform which is increasingly being used by brands and businesses for their data. It stands to be one of the top CRM platforms with a variety of solutions for different kinds of companies.  

Key Features of Data Studio 

Data Studio is a free Google tool that is intended for efficient reporting and dashboard organization. It allows the development of informative dashboards that can be easily customized. The information rendered is equally easy to share for a more collaborative approach. You can transform data into easy to understand and informative reports. These are more appealing as compared to a mundane analysis and can give a visual insight to follow the performance of a specific product in the market. You can further track business objectives and create periodic reports to analyze various aspects. 

There are some exciting features that Data Studio offers, which can enhance data manipulation when used in integration with your database. One highlight is the generation of unlimited reports. You can generate as many appealing reports as you want to analyze your data more effectively. It also offers a live data connection. With built-in connectors, you can easily connect to live data sources where data is being refreshed periodically. This eliminates the drawbacks of accessing redundant data. 

The Data Studio platform also provides access to a number of pre-built connectors to connect data sources. This forms a vast library including connectors such as DoubleClick, Google AdWords, Google Analytics, Google Analytics 360, Google Attribution 360, BigQuery, Google Cloud SQL, Google Sheets, MySQL, PostgreSQL, YouTube, Data Uploader and Calculated Metrics.

Data Studio Dashboard
Data Studio Dashboard- Image via Windsor

Why Integrate Salesforce and Google Data Studio? 

One might ask the question, why to connect Salesforce to Google Data Studio in the first place. The answer is pretty simple! Salesforce is a robust database platform, and with the added features of Data Studio, data manipulation can be a real dream for many businesses. Especially for the sales organization and business teams dealing with large data chunks, the efficient analysis of the same is critical.

How to Connect Salesforce to Google Data Studio?

To connect your data from Salesforce to Google Data Studio, a simple three-step process can be adopted. To connect and transfer data from Salesforce to Google Data Studio:

Step 1. Extract Data from Salesforce

The Salesforce API permits you to create a connection that can transmit data programmatically. Start by initiating a connection to extract data from Salesforce. This process might require up to several months to organize and consequently initiate and extract.

To authenticate access from DataStudio for the extraction of salesforce data you need to authorize access from your Salesforce Account.

Allow Access
Image via Astreait

The following Python Script can be implemented to extract data files to any desired destination.

folder = Path(“C:/Users/kimsmall/Documents/ClientData/ActivityData”)
activityFile = folder / “ActivityBase.csv”
activityBatch1 = folder / “ActivityBatch1.xlsx”
activityBatch2 = folder / “ActivityBatch2.xlsx”
activityBatch3 = folder / “ActivityBatch3.xlsx”
activityBatch4 = folder / “ActivityBatch4.xlsx”
activityTable = activityTable.read_csv()
activityBatches = np.array_split(activityTable, 4)
activityBatches[0].toExcel(activityBatch1, na_rep=””, index=False)
activityBatches[1].toExcel(activityBatch1, na_rep=””, index=False)
activityBatches[2].toExcel(activityBatch1, na_rep=””, index=False)
activityBatches[3].toExcel(activityBatch1, na_rep=””, index=False)

Step 2. Load Data into a Unified Space for Access with Data Studio

Once extracted from Salesforce, the data needs to be loaded into a unified space such as a data warehouse. This will act as a centralized space for access with Data Studio. You can choose from a number of data warehouses for this purpose. Google BigQuery, Amazon Redshift. Snowflake, PostgreSQL, and SQL Server are a few options you can consider for the shared data space. Data from here can be accessed with Data Studio to carry out the analysis process.

The Salesforce connector enables a direct connection as well, in which case you might not require the additional space. Here you can select the object that you want to access and start the connection.

Salesforce connector

You can see the control files with the following commands:

LOAD DATA INFILE sh_sales.dat APPEND INTO TABLE sales
FIELDS TERMINATED BY "|"
(PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD)

Pre-existing object directories are required to point to the directory locations, for instance:

CREATE TABLE sales_transactions_ext
(PROD_ID NUMBER, CUST_ID NUMBER,
 TIME_ID DATE, CHANNEL_ID NUMBER,
 PROMO_ID NUMBER, QUANTITY_SOLD NUMBER,
 AMOUNT_SOLD NUMBER(10,2), UNIT_COST NUMBER(10,2),
 UNIT_PRICE NUMBER(10,2))
ORGANIZATION external (TYPE oracle_loader
  DEFAULT DIRECTORY data_file_dir ACCESS PARAMETERS
  (RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    PREPROCESSOR EXECDIR:'zcat' 
    BADFILE log_file_dir:'sh_sales.bad_xt'
    LOGFILE log_file_dir:'sh_sales.log_xt'
    FIELDS TERMINATED BY "|" LDRTRIM
    ( PROD_ID, CUST_ID,
      TIME_ID         DATE(10) "YYYY-MM-DD", 
      CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD,
      UNIT_COST, UNIT_PRICE))
  location ('sh_sales.gz') 
)REJECT LIMIT UNLIMITED;

Data can then be loaded using SQL Queries:

$  sqlldr control=sh_sales.ctl direct=true
Username:
Password:

Step 3. Build Data Studio Dashboards and Analyze Data 

Once extracted from the unified space, Data Studio offers multiple options to represent data in different appealing ways for easy analysis.

In Data Studio, you will need to set up the metrics as per your requirement. Once the object is selected, Data Studio displays a list of all available fields. You can edit your connections as well as change the aggregations as required.

Edit connection

After this process, the visualization of the data can be initiated. Create a report and use the metric picker to find the data that you need.

Create a report

A number of scheduled reports can then be created to analyze periodically updated data efficiently.

Challenges for Integration

Given the merits, the task of integration in itself isn’t an easy one! Relaying data from Salesforce to Google Data Studio can be done through several methods. However, most of these will create a resource overhead for your team. Building custom integrations and carrying out transfers in iterations while still ensuring the security creates an additional task.  

If you can find a more natural way to get your data from Salesforce API to Data Studio, then the actual benefits of the platforms can be reaped. You can, in fact, gather all kinds of data, including standard objects, leads, contacts, accounts, and even custom objects. This is all made worth the benefits if a smooth integration is made possible.

Hevo: An Easy Integration 

Hevo allows initiating an easy integration between several platforms. It enables direct conjunction between Salesforce and Data Studio while also providing an array of features to make the transfer and management of data more accessible.

As a no-code platform, Hevo offers features to efficiently manage and monitor data irrespective of your technical background. With an easy-to-use, fully-managed platform, the system prompts automatic integration between Salesforce and Google Data Studio for free, so, you can now focus on the actual analysis of data instead of worrying about how to transfer it! 

Visit our Website to Explore Hevo

Want to take Hevo for a ride? Sign Up and simplify your Data Integration process.

Share your thoughts on connecting Salesforce to Google Data Studio in the comments below!

Aman Sharma
Freelance Technical Content Writer, Hevo Data

Driven by a problem-solving approach and guided by analytical thinking, Aman loves to help data practitioners solve problems related to data integration and analysis through his extensively researched content pieces.

Easily visualize your data in Google Data Studio