trailhead

Import Spreadsheet Into Analytics

What You’ll Learn

This Analytics charts project introduces you to creating Analytics datasets and producing useful charts from those datasets. You’ll learn how to:
  • Load a CSV file into Analytics and create a dataset from that file
  • Choose the right data for what you're trying to communicate
  • Find the best chart for visualizing your data
  • Create the appropriate exploration (or lens) for the chart.

From Spreadsheet Smarts to Chart Intelligence

Sometimes looking at a table of data is helpful, and other times it's difficult to take it all in, see the bigger picture, or draw meaningful insights from those rows of numbers. With Analytics charts, you can transform tables of data into beautiful and informative visualizations that won't require your audience to do math in their heads. They'll get the insights quickly, and you'll spend less time explaining. What's not to like?

But how do you go from a table of data to a chart? Which chart should you choose? And what's the right data to visualize? Before we can answer these questions, let's first ask: What's the overall goal?

We want to create visualizations that are useful and provide insights that your team can act on.

Imagine you're a business analyst keeping track of opportunities for your United States based electronics distribution company. Like many people, you've been using a spreadsheet to tabulate opportunities in the current fiscal year and to keep an eye on the product pipeline.

Here's what your spreadsheet looks like:

A traditional spreadsheet with columns and rows of data

You create reports from this spreadsheet, but you often find yourself having to explain the numbers to colleagues and managers. You wish there was an easy way to create eye-catching, informative graphics that can be easily presented and shared. That wish is Analytics's command.

First we need to get the spreadsheet data into Analytics. Start by signing up for a special kind of Developer Edition Org that has Analytics enabled.

Sign Up for an Analytics-enabled Developer Edition Org

For this project, you can’t use an existing Developer Edition org. You must sign up for a special Developer Edition because it comes with a limited Analytics Cloud Platform license.

Begin by signing up for a Salesforce Analytics-enabled Developer Org using this URL:

  1. Go to developer.salesforce.com/promotions/orgs/wave-de.
  2. Fill out the form using an active email address. Your username must also look like an email address and be unique, but it doesn’t need to be a valid email account. For example, your username can be yourname@waverocks.de, or you can put in your company name.
  3. After you fill out the form, click Sign me up. A confirmation message appears.
  4. When you receive the activation email, open it and click the link.
  5. Complete your registration, and set your password and challenge question.
  6. Click Save.

    You’ll be logged in to your Developer Edition org and redirected to the Setup page.

    Tip

    Tip

    Write down or remember your credentials. To log in and play, just go to login.salesforce.com.

Upload a CSV File to Analytics

Normally you'd export the spreadsheet to a CSV file, then import the file into Analytics as a dataset, but we've already created a CSV file for this project. Download the CSV file and save it to your computer.

To upload the CSV file to Analytics:

  1. In your Developer Edition org, open Analytics. Click the app launcher (App Launcher icon), and then click the Analytics Studio tile.
  2. In Analytics Studio, click Create in the upper-right of the screen and choose Dataset.
  3. Click CSV as the source of the data.
    Select a data source for creating a dataset
  4. In the new dataset screen, click Select file or drag file here... for CSV files.
  5. In the file-selection window that opens, select the file CSV file you downloaded and click Open (or the correct button in your operating system to upload the file). The file should be named ProductPipelineUSA.csv.
  6. In Dataset Name, name the dataset ProductPipelineUSA and click Next.
  7. In Edit Field Attributes, click Upload File.
  8. Click Got It! to complete the upload and create the new dataset.

Give it several seconds. When the dataset is ready, it will appear in the list of datasets.

The ProductPipelineUSA dataset

Before we learn how to use this dataset, let's decide what to use it for. In our scenario, there are certain insights your company wants you to provide:

  • A breakdown of the current product pipeline by stage
  • Information on the geographic distribution of opportunities
  • A comparison of the different products by opportunity values

Until now, you had to extract and communicate these insights from your spreadsheet data using rows of numbers, but now you have the power of Analytics charts at your mouse clicks! #micdrop

The Right Chart for the Job

There are more than a dozen types of Analytics charts available, but not all charts are created equal. Some are just better at visualizing data in a way that pops out the specific insights you're trying to communicate. In Analytics, each chart has certain characteristics that determine the type of data it can display.

Waterfall chart with tooltip visible in the Chart Type menu

Hover over the chart type, and the resulting tooltip tells you the number of dimensions (columns with categorical data) and measures (columns with numerical data) required to use the chart.

For example, in the image above, the tooltip shows that the Waterfall chart requires 1 measure and at least 1 grouping.

But even if two different charts could display the same data based on the information in their tooltips, one might do a better job at conveying the insights you're looking to find and communicate. For example, you could show pipeline stages using a bar chart or a waterfall chart.

Comparison of waterfall and bar charts displaying the same data

Notice anything? The bar chart doesn't display the total value of opportunities. Visually, too, the waterfall chart does a better job of separating out the stages or outcomes, letting you see each stage as it contributes to (or takes away from) total opportunities. The waterfall chart also highlights the relative relationships between the stages in terms of how opportunities flow toward a close.

Next, you'll create a waterfall chart out of the dataset you just uploaded to break down the dollar value of pipeline opportunities by stage.

retargeting