IMPORTANT NOTES: This article refers to our hosted Elasticsearch offering by an older name, Found. Please note that Found is now known as <a href="http://elastic.co/cloud" target="_self">Elastic Cloud</a>. Also note that the add-on does not automatically work with versions 5.x and above. To benefit from 5.x using this add-on, you must index the data into a 2.4.4 cluster and then upgrade.&nbsp;
Our latest Google Sheets add-on makes visualizing your spreadsheet data a snap.
Today, we're rolling out a new Google Sheets add-on that makes it easy to do just that. Using this add-on, along with <a href="/cloud" target="_blank">Found, Elastic's hosted and managed Elasticsearch offering</a>, you can push data from a spreadsheet to your Elasticsearch cluster and get up and running with Kibana visualizations in just a few minutes.
<h3>Setting Up a Cluster</h3>
If you already have a cluster you want to push data into, you can skip to the next section about pushing data. If not, we recommend you take a moment to <a href="/cloud/signup" target="_blank">sign up for a free hosted Elasticsearch cluster</a> before continuing. With just a few clicks, you will have a secure cluster up and running, including Kibana 4 for visualizations.
Watch this short demonstration video on how to spin up a free hosted Elasticsearch cluster.
<div class="video embed-container" style="height: 319.725px;"><img style="width: 677;margin-top: auto;margin-right: auto;margin-bottom: auto;margin-left: auto;display: block;" class="vidyard-player-embed" src="https://play.vidyard.com/wFXdpz67kpoj4icWtiFMrv.jpg" data-uuid="wFXdpz67kpoj4icWtiFMrv" data-v="4" data-type="inline" width="677" /></div>
After completing the sign up and configuration for Found, you should see a couple of endpoints displayed that look like this:<pre class="prettyprint">http://<some-long-id>.us-east-1.aws.found.io:9200 https://<some-long-id>.us-east-1.aws.found.io:9243 </pre>
We will use those later to send data to your cluster.
<h3>Pushing Data to Your Cluster</h3>
Our cluster is ready to go, so now it's time to open up your Google Sheet and get started. The video below walks you through the steps in detail.
<div class="video embed-container" style="height: 319.725px;"><img style="width: 677;margin-top: auto;margin-right: auto;margin-bottom: auto;margin-left: auto;display: block;" class="vidyard-player-embed" src="https://play.vidyard.com/FNHz8yfb1XBhTnRxWeGmb9.jpg" data-uuid="FNHz8yfb1XBhTnRxWeGmb9" data-v="4" data-type="inline" width="677" /></div>
<ol>
 <li>Install the Spreadsheet to Elasticsearch Google Sheets Add-on.</li>
 <li>Under the add-ons menu, choose "Push to Cluster…" to get started.</li>
 <li>Add your connection details for your cluster, including a username and password if required.</li>
 <li>After testing your connection, save it and click "Edit Data Details."</li>
 <li>Verify that the proper data range is selected on your spreadsheet, including the headers in the first row.</li>
 <li>Update the name of the index and document type where your spreadsheet data will be stored in your cluster.</li>
 <li>If you know what you're doing, you can update or remove the Index Template. By default, it will create non-analyzed fields with your data, which is useful when visualizing.</li>
 <li>Click "Push to Cluster." After a few seconds, you should see a note with a link to your data.</li>
</ol>
<h3>Helpful Tips</h3>
For those of you interested in contributing or understanding how this works, feel free to check out the code hosted on the <a href="https://github.com/elastic/google-sheets-add-on" target="_blank">Spreadsheet to Elasticsearch GitHub repo</a>. Here are a few helpful tips in case you get stuck:
<ul>
 <li>The "Host" is the URL of your cluster (or your endpoint from Found), without the https:// or the :9243 at the end.</li>
 <li>The username and password are for accessing your cluster directly, not the ones used to log into the service hosting your cluster such as Found.</li>
 <li>If you want to check to make sure everything is ok, click the "Test" button and the add-on will try to connect to your cluster using the information you provided.</li>
 <li>Empty rows will be skipped, so don't worry about those.</li>
 <li>To avoid duplicates in your cluster, select a unique identifier for each row using the Document ID field. Then, if you update data in your spreadsheet, you can push the updates without creating duplicates.</li>
 <li>If you are not familiar with index templates, just use the default name. If you get a note about indices not matching, just choose a new name for the index template name.</li>
</ul>
<h3>Visualizing it with Kibana</h3>
After you have your data indexed into Elasticsearch, the easiest way to visualize it is with Kibana. If you are using Found, the URL for Kibana 4 can be found under the "Configuration" section of your cluster. You can browse the <a href="https://www.elastic.co/guide/en/kibana/8.8/get-started.html" target="_blank">Kibana 4 documentation</a> or check out this helpful <a href="/webinars/getting-started-with-kibana" target="_blank">Getting Started with Kibana Webinar</a>.

spreadsheet-to-elasticsearch.png

Easily Visualize Spreadsheet Data with Elasticsearch & Kibana

In the spirit of Dreamforce and being part of Elastic, I’m excited to introduce a new <a href="https://github.com/logstash-plugins/logstash-input-salesforce" target="_blank">open source plugin for Logstash</a> that makes it easy to pull vast amounts of data out of Salesforce and into Elasticsearch.This plugin allows you to query almost any object in Salesforce and pull it into the Logstash pipeline. &nbsp;From there, you can output it to your analytics tool of choice (ours is Kibana) to discover insights that you may have missed otherwise. We use it internally to keep an eye on our our sales pipeline and customer health over time.&nbsp; Working directly with sales and marketing operations, we outlined a number of challenges they had that might be solved with this solution. Those included: <ul><li>Interactive time-series snapshot analysis across a number of dimensions. By sales rep, by region, by campaign and more.</li><li>Which sales reps moved the most pipeline the day before the end of month/quarter?&nbsp;What was the progression of Stage 1 opportunities over time.</li><li>Correlating data outside of Salesforce (like web traffic) to pipeline building and demand. By region/country/state/city and associated pipeline.</li></ul>It’s very challenging to look back in time and see trends in the data. Many companies have configured Salesforce to save reporting snapshots, but if you're like me, you want to see the data behind the aggregate report. I want the ability to drill down to any level of detail, for any timeframe, and find any metric. We found that Salesforce snapshots just aren't flexible enough for that.Logstash already allows users to pull in vast amounts of data from a variety of sources, and with this plugin, we are adding Salesforce as a source. Once the data is in our Elasticsearch cluster, we use Kibana to quickly filter and display the data in ways we couldn't before using Salesforce’s built in reporting.Now, let’s focus on how you can set up the Logstash Input for Salesforce for your own organization.<h3>Querying Salesforce from Logstash</h3>As I mentioned earlier, we’ve been using the <a href="https://www.elastic.co/guide/en/logstash/current/plugins-inputs-salesforce.html" target="_blank">Logstash Salesforce Input plugin</a> internally to handle all of this data. We decided others could benefit from this plugin as well so we <a href="https://github.com/logstash-plugins/logstash-input-salesforce" target="_blank">open sourced it</a> and made it available for anyone to install using the standard Logstash plugin framework. With a fresh install of the latest Logstash, you should be able to install this plugin with: <pre class="prettyprint">bin/plugin install logstash-input-salesforce </pre>Based on your configuration, the plugin will query sObjects from Salesforce and create an event for each row of data returned. You can then filter and transform the data as needed before&nbsp;pushing it to wherever it needs to go. To configure this plugin, we will need to gather some credentials from Salesforce. This involves creating a new <a href="https://help.salesforce.com/apex/HTViewHelpDoc?id=connected_app_create.htm" target="_blank">Connected App with OAuth</a> in Salesforce and generating a user token. Depending on your Salesforce permissions, you might need to ask your Administrator to do this for you. You should only need to do this once.Here is an example of the minimum information you need to enter. Of course, the scope can be configured as needed but just make sure to give enough access for the sObjects you want to query. Also, for the callback URL, you can just enter localhost since the plugin will be using password authentication. Once you have a new application set up, you will need the Consumer Key and Consumer Secret values, so keep track of those.While you’re in Salesforce, you should also go ahead and generate a token for the username you will be using for authentication. <a href="https://help.salesforce.com/apex/HTViewHelpDoc?id=user_security_token.htm" target="_blank">Here are some detailed instructions</a> on how to generate or reset your token if you don’t already have one.Now we have everything we need for connecting to Salesforce from Logstash. &nbsp;Let’s start with a simple Logstash config that will connect to your sandbox Salesforce instance (test.salesforce.com) and pull in all the Opportunity objects.<pre class="prettyprint">input { salesforce { use_test_sandbox =&gt; true #you are testing in a sandbox right? client_id =&gt; 'CONSUMER_KEY_FROM_SALESFORCE' client_secret =&gt; 'CONSUMER_SECRET_FROM_SALESFORCE' username =&gt; 'you@example.com.sandbox' password =&gt; 's3crEt!' security_token =&gt; 'SECURITY_TOKEN_FROM_SALESFORCE' sfdc_object_name =&gt; 'Opportunity' } } output { stdout { codec =&gt; rubydebug } } </pre>Here, we are telling the plugin to use the test sandbox, connect with our credentials, and pull in all the Opportunity objects. By default, the input will pull in all the fields for that sObject. If you would only like to see selected fields, you can specify them using the <a href="https://www.elastic.co/guide/en/logstash/master/plugins-inputs-salesforce.html#plugins-inputs-salesforce-sfdc_fields" target="_blank">sfdc_fields</a> option:<pre class="prettyprint">sfdc_fields =&gt; ['Id','Name','Type','CloseDate'] </pre>I like to pull everything in because it gives me the most flexibility later. By default, the input will use the standard Salesforce API Names as the document key fields. If you’d like to normalize them from camel case (e.g. LastModifiedBy) to something a little more Logstash like (e.g. last_modified_by), you can set the <a href="https://www.elastic.co/guide/en/logstash/master/plugins-inputs-salesforce.html#plugins-inputs-salesforce-to_underscores" target="_blank">to_underscores</a> option to true.For a full list of input options and what they do, check out the <a href="https://www.elastic.co/guide/en/logstash/master/plugins-inputs-salesforce.html" target="_blank">Logstash Salesforce Input documentation</a>. If you want additional sObjects loaded into Logstash, for now, you will need to duplicate that config for each object. We are currently pulling in Leads, Contacts, Accounts, Opportunities&nbsp;and more using this process.Also, by default, the plugin uses version 26 of the Salesforce API. If you’re using a newer object, you can specify a different api version using the <a href="https://www.elastic.co/guide/en/logstash/master/plugins-inputs-salesforce.html#plugins-inputs-salesforce-api_version" target="_blank">api_version</a> config option.<h3>Pushing the Data to Elasticsearch</h3>Now that we have the data flowing into Logstash, we can adjust the output to write data wherever we need. For this example, we are going to push the data directly into our cluster using the <a href="https://www.elastic.co/guide/en/logstash/master/plugins-outputs-elasticsearch.html" target="_blank">Elasticsearch output</a>:<pre class="prettyprint">output { elasticsearch { index =&gt; "salesforce" index_type =&gt; "opportunity" host =&gt; "localhost" } } </pre>That’s the simplest way to push data into an Elasticsearch instance running on your laptop. Of course, if you’d like to not worry about managing your own cluster, you can push the data directly to a <a href="https://www.elastic.co/found" target="_blank">Found</a>&nbsp;(hosted Elasticsearch)&nbsp;cluster as well. The advantage there is that in just a few clicks, you can have an Elasticsearch cluster up and running with Shield for security and Kibana for visualizations. We use it internally as much as possible. This is great for a single download of the data. If you want to keep gathering this information on a regular interval, it’s trivial to setup a cron job to do just that. We have ours collect this data every 24 hours, but you can adjust yours as needed. <pre class="prettyprint">0 5 * * * /opt/logstash/bin/logstash -f /opt/logstash/sfdc-snapshot.conf -w 4 </pre><h3>Connecting the Data</h3>When you start pulling this data in, you will notice that Salesforce has a lot of Id and Lookup fields. Looking at an 18 character OwnerId really doesn’t help you much. &nbsp;Once you have a process to load the raw data, you can start to enrich it by connecting the sObjects together. You might create a separate Logstash configuration for doing just that. As an input, it will query new data from the Salesforce index, use the <a href="https://www.elastic.co/guide/en/logstash/master/plugins-filters-elasticsearch.html" target="_blank">Elasticsearch filter</a> to look up related documents&nbsp;from an id, attach that&nbsp;document&nbsp;to the original, and finally write that information to the existing index or a separate one. A sample configuration might look like this:<pre class="prettyprint">input { elasticsearch { hosts =&gt; ['localhost'] index =&gt; 'salesforce' query =&gt; '{ "query": { "query_string": { "query":"_type:\"opportunity\" AND @timestamp:[now-12h TO now]" } } }' #everything from the past 12 hours. } } filter { if [OwnerId] { elasticsearch { hosts =&gt; [ 'localhost:9200' ] query =&gt; 'id:"%{[OwnerId]}" AND _type:user AND @timestamp:[now-12h TO now]' fields =&gt; ["Name","sfdc_owner_name"] } } } output { elasticsearch { index =&gt; "salesforce-enriched" index_type =&gt; "opportunity" host =&gt; "localhost" } } </pre>Of course you can add as many lookups as you want to connect Accounts, Opportunities, Contacts or whatever custom sObjects you might have as well. <h3>Visualizing the Data with Kibana</h3>Once the data is in Elasticsearch, the easiest way to start visualizing trends is to use Kibana. There are lots of fun things to look at but you can start by creating a <a href="https://www.elastic.co/guide/en/kibana/5.3/vertical-bar-chart.html" target="_blank">vertical bar chart</a> histogram and slicing the columns by a particular Salesforce field you are interested in. In the chart below we can see pipeline changes on a daily basis by stage. We noticed a drop in stage 2 pipeline on the 9th and because we have the data snapshots saved, we can easily see exactly which opportunities we changed at that time. This is just some sample data, but hopefully it gives you some ideas about how you can start slicing and dicing your pipeline. <img src="https://api.contentstack.io/v2/assets/575e4cb2d8edd48f76936409/download?uid=blte035c41aabb23b00?uid=blte035c41aabb23b00" data-sys-asset-uid="blt3364396744968044" alt="Pipeline By Stage Over Time" style="max-width: 100%;"/><h3>Conclusion</h3>Liberating your data from Salesforce and putting it into Elasticsearch will allow you to dive much deeper and see trends that you might have otherwise missed. Our new Logstash input plugin is a quick and easy way to pull your data out of Salesforce and start tinkering with it in Kibana. Please let us know what improvements you would like to see by filing issues in the <a href="https://github.com/logstash-plugins/logstash-input-salesforce/issues" target="_blank">Github repo</a> and have a great time at Dreamforce!

salesforce-data-elasticsearch-logstash-kibana-analyzing-full-bleed.jpg

salesforce-data-elasticsearch-logstash-kibana-analyzing-thumbnail.jpeg

Analyzing Salesforce Data with Logstash, Elasticsearch, and Kibana

Start free trial

Blog Footer CTA

Sign up for Elastic Cloud free trial

Business Systems Architect

Russ Savage

By submitting you acknowledge that you've read and agree to our <a href="/legal/elastic-cloud-account-terms" target="_self">Terms of Service</a>, and you agree to receive the <a href="/legal/privacy-statement#how-we-use-the-information" target="_self">selected communications</a> at the contact details you provided above. See <a href="/legal/privacy-statement/" target="_self">Elastic’s Privacy Statement</a> for more details or to opt-out at any time.

Communication Preferences - ONLY for /communication-preferences

제출하면 귀하가 <a href="/kr/legal/elastic-cloud-account-terms" target="_self">Elastic 서비스 약관</a>을 읽고 이에 동의하며, 위에 제공된 연락처로 <a href="/kr/legal/privacy-statement#how-we-use-the-information" target="_self">선택한 커뮤니케이션</a>을 수신하는 데 동의한 것으로 간주됩니다. 언제든지 수신을 거부할 수 있으며 자세한 내용은 <a href="/kr/legal/privacy-statement/" target="_self">Elastic 개인정보 취급방침</a>을 참조하시기 바랍니다.

By submitting you acknowledge that you've read and agree to our <a href="/legal/serverless-preview-terms" target="_blank">Terms of Service</a>, and that Elastic may <a href="/legal/privacy-statement#how-we-use-the-information" target="_blank">contact you</a> about our related products and services, using the details you provide above. See <a href="/legal/privacy-statement/" target="_blank">Elastic’s Privacy Statement</a> for more details or to opt-out at any time.

Serverless GDPR Text

제출하면 귀하가 Elastic <a href="/kr/legal/serverless-preview-terms" target="_self">서비스 약관</a>을 읽고 이에 동의하며, 위에 제공된 세부 정보를 사용하여 Elastic이 관련 제품 및 서비스에 대해 귀하에게 <a href="/kr/legal/privacy-statement#how-we-use-the-information" target="_self">연락할 수 있음</a>을 이해한 것으로 간주됩니다. 언제든지 수신을 거부할 수 있으며 자세한 내용은 <a href="/kr/legal/privacy-statement/" target="_self">Elastic 개인정보 취급방침</a>을 참조하시기 바랍니다.

By submitting you agree to <a href="/agreements/global/cloud-services-monthly" rel="nofollow">Elastic Cloud Terms of Service</a>. Your personal data will be processed in accordance with <a href="/legal/privacy-statement">Elastic's Privacy Statement</a>.

Cloud Trial GDPR Text

제출하면 <a href="/kr/agreements/global/cloud-services-monthly" rel="nofollow">Elastic Cloud 서비스 약관</a>에 동의하시게 됩니다. 개인 데이터는 <a href="/kr/legal/privacy-statement">Elastic의 개인정보 취급방침</a>에 따라 처리됩니다.

By submitting you acknowledge that you've read and agree to our <a href="/legal/terms-of-use" target="_blank">Terms of Service</a>, and that Elastic may <a href="/legal/privacy-statement#how-we-use-the-information" target="_blank">contact you</a> about our related products and services, using the details you provide above. See <a href="/legal/privacy-statement/" target="_blank">Elastic’s Privacy Statement</a> for more details or to opt-out at any time.

Newsletter GDPR Text

제출하면 귀하가 Elastic <a href="/kr/legal/terms-of-use" target="_self">서비스 약관</a>을 읽고 이에 동의하며, 위에 제공된 세부 정보를 사용하여 Elastic이 관련 제품 및 서비스에 대해 귀하에게 <a href="/kr/legal/privacy-statement#how-we-use-the-information" target="_self">연락할 수 있음</a>을 이해한 것으로 간주됩니다. 언제든지 수신을 거부할 수 있으며 자세한 내용은 <a href="/kr/legal/privacy-statement/" target="_self">Elastic 개인정보 취급방침</a>을 참조하시기 바랍니다.

Explore similar demos

Overview

Speakers

Close

See more insights

The content on this page is not available in the selected language. As Elastic grows globally, we continue to support content in multiple languages.

The content on this page is not available in the selected language.

이 페이지의 콘텐츠는 선택하신 언어로 제공되지 않습니다. Elastic은 다양한 언어로 콘텐츠를 제공하기 위해 최선을 다하고 있습니다.조금만 더 기다려주세요!

Author

Articles by

Learn more

Watch now (no PT)

Watch now

See all top stories

All (no PT translation)

Contact information

Press Release

Share on Reddit

Share this story

Share by Email

Thank you for your interest!

Contact Info

Follow us on Youtube

Load More

Share on LinkedIn

Follow us on LinkedIn

Search Integrations

All Solutions

Video for

Follow us on Twitter

See when this webinar starts in my time zone

내 표준 시간대로 웨비나 시작 시간 확인하기

Register to Watch

Register now

See All Posts

Can't make it? Register and we'll send you the recording. You'll also receive an email with related content

참여가 어려우신가요? 등록하시면 녹화 자료를 보내드리겠습니다. 이메일로 관련 콘텐츠도 받아보실 수 있습니다.

Small image for

On-demand webinar

Featured webinar

Register to Attend

Share on Facebook

Reset all

Upcoming webinar

View more posts

Print

Hosted by

Sign In to Attend

View next

Follow us on Facebook

Share

Highlights

Read less

You'll also receive an email with related content

이메일을 통해 관련 내용을 보내드리겠습니다.

Thank you for registering. We will send you a confirmation email soon.

등록해 주셔서 감사합니다. 곧 확인 이메일을 보내드리겠습니다.

Author

Articles by Russ Savage

Business Systems Architect, Elastic

Easily Visualize Spreadsheet Data with Elasticsearch & Kibana

Analyzing Salesforce Data with Logstash, Elasticsearch, and Kibana