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

送信することにより、Elasticの<a href="/jp/legal/elastic-cloud-account-terms" target="_self">利用規約</a>を読み、同意したものと見なされます。また、上記で提供された連絡先に<a href="/jp/legal/privacy-statement#how-we-use-the-information" target="_self">特定の通知</a>が送信されることを認めたことになります。詳細については、<a href="/jp/legal/privacy-statement/" target="_self">Elasticのプライバシーポリシー</a>をご覧ください。これらの同意についてはいつでもオプトアウトすることが可能です。

By submitting you acknowledge that you've read and agree to our <a href="/legal/elastic-cloud-account-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.By submitting this form, I agree to Elastic sharing my contact information with Google Cloud. I also agree to Elastic giving my information to the vendor for the purpose of shipping my food package.

GDPR Text + Google Cloud

送信することにより、Elasticの<a href="/jp/legal/elastic-cloud-account-terms" target="_self">利用規約</a>を読み、同意したものと見なされます。また、関連する製品やサービスについて、上記で提供された情報を使用してElasticから<a href="/jp/legal/privacy-statement#how-we-use-the-information" target="_self">連絡する</a>場合があることを認めたことになります。詳細については、<a href="/jp/legal/privacy-statement/" target="_self">Elasticのプライバシー</a>ポリシーをご覧ください。これらの同意についてはいつでもオプトアウトすることが可能です。このフォームを送信することにより、Elasticが私の連絡先情報をGoogle Cloudに共有することに同意します。また、Elasticが私に食材パッケージを発送する目的で私の情報をベンダーに提供することにも同意します。

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="/jp/legal/serverless-preview-terms" target="_self">利用規約</a>を読み、同意したものと見なされます。また、関連する製品やサービスについて、上記で提供された情報を使用してElasticから<a href="/jp/legal/privacy-statement#how-we-use-the-information" target="_self">連絡する</a>場合があることを認めたことになります。詳細については、<a href="/jp/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="/jp/agreements/global/cloud-services-monthly" rel="nofollow">Elastic Cloud利用規約</a>に同意したものとみなされます。個人情報は<a href="/jp/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="/jp/legal/terms-of-use" target="_self">利用規約</a>を読み、同意したものと見なされます。また、関連する製品やサービスについて、上記で提供された情報を使用してElasticから<a href="/jp/legal/privacy-statement#how-we-use-the-information" target="_self">連絡する</a>場合があることを認めたことになります。詳細については、<a href="/jp/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

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

Search Integrations

All Solutions

Video for

See when this webinar starts in my time zone

現在のタイムゾーンでこのウェビナー開始時間を見る

Register to Watch

See All Posts

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

他の予定と重なってしまった…という場合も、問題ありません。参加登録されると、録画配信をご利用いただけます。関連コンテンツのご案内もメールでお届けします。

Author

Articles by Russ Savage

Business Systems Architect, Elastic

Easily Visualize Spreadsheet Data with Elasticsearch & Kibana

Analyzing Salesforce Data with Logstash, Elasticsearch, and Kibana