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

Durch das Absenden erklären Sie, dass Sie unsere <a href="/de/legal/elastic-cloud-account-terms" target="_self">Nutzungsbedingungen</a> gelesen haben und mit ihnen einverstanden sind und dass Sie Elastic erlauben, <a href="/de/legal/privacy-statement#how-we-use-the-information" target="_self">ausgewählte Nachrichten und Informationen</a> an die oben von Ihnen bereitgestellten Kontaktangaben zu senden. Weitere Informationen, darunter auch dazu, wie Sie Ihre Einwilligung zurückziehen können, finden Sie in der <a href="/de/legal/privacy-statement/" target="_self">Datenschutzerklärung von 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

Durch das Absenden erklären Sie, dass Sie unsere <a href="/de/legal/serverless-preview-terms" target="_self">Nutzungsbedingungen</a> gelesen haben und mit ihnen einverstanden sind und dass Sie Elastic erlauben, bezüglich unserer zugehörigen Produkte und Dienstleistungen und unter Nutzung der von Ihnen oben bereitgestellten Kontaktangaben <a href="/de/legal/privacy-statement#how-we-use-the-information" target="_self">mit Ihnen in Kontakt zu treten</a>. Weitere Informationen, darunter auch dazu, wie Sie Ihre Einwilligung zurückziehen können, finden Sie in der <a href="/de/legal/privacy-statement/" target="_self">Datenschutzerklärung von 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

Beim Absenden akzeptieren Sie die <a href="/de/agreements/global/cloud-services-monthly" rel="nofollow">Nutzungsbedingungen von Elastic Cloud</a>. Die Verarbeitung Ihrer personenbezogenen Daten unterliegt der <a href="/de/legal/privacy-statement">Datenschutzerklärung von 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

Durch das Absenden erklären Sie, dass Sie unsere <a href="/de/legal/terms-of-use" target="_self">Nutzungsbedingungen</a> gelesen haben und mit ihnen einverstanden sind und dass Sie Elastic erlauben, bezüglich unserer zugehörigen Produkte und Dienstleistungen und unter Nutzung der von Ihnen oben bereitgestellten Kontaktangaben <a href="/de/legal/privacy-statement#how-we-use-the-information" target="_self">mit Ihnen in Kontakt zu treten</a>. Weitere Informationen, darunter auch dazu, wie Sie Ihre Einwilligung zurückziehen können, finden Sie in der <a href="/de/legal/privacy-statement/" target="_self">Datenschutzerklärung von 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.

Der Inhalt dieser Seite ist in der ausgewählten Sprache nicht verfügbar. Wir bei Elastic arbeiten daran, die bereitgestellten Inhalte in verschiedenen Sprachen anzubieten. Bis dahin bitten wir Sie um etwas Geduld und hoffen auf Ihr Verständnis!

Author

Watch now (no PT)

Watch now

See all top stories

All (no PT translation)

Contact information

Press Release

Share on Reddit

Articles by

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

Sie können nicht dabei sein? Registrieren Sie sich, damit wir Ihnen die Aufzeichnung zukommen lassen können. Außerdem erhalten Sie eine E‑Mail mit Begleitmaterial.

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

Learn More

Read less

You'll also receive an email with related content

Wir schicken Ihnen zudem relevante Informationen, die von Interesse sein könnten.

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

Vielen Dank für Ihre Registrierung. Sie erhalten in Kürze eine Bestätigungs-E‑Mail von uns.

Author

Articles by Russ Savage

Business Systems Architect, Elastic

Easily Visualize Spreadsheet Data with Elasticsearch & Kibana

Analyzing Salesforce Data with Logstash, Elasticsearch, and Kibana