How to Make a Sankey Diagram in Looker

A Sankey diagram is a powerful way to visualize the flow of users, resources, or revenue from one stage to the next. Instead of getting bogged down in tables of numbers, you can instantly see where your biggest traffic sources come from, where users drop off in your conversion funnel, or how your budget is being allocated. This article will walk you through exactly how to build a Sankey diagram in Looker, from preparing your data to configuring the final visualization.

What Exactly Is a Sankey Diagram?

Think of a Sankey diagram as a flow map. It shows a process from left to right, with nodes representing stages and links representing the flow between those stages. The key feature is that the width of the link is proportional to the quantity of the flow. A thicker line means a larger volume is moving between those two stages.

This makes them perfect for answering questions like:

• Website User Flow: How do users navigate through my site? Which landing pages lead to the most product views, and from there, to checkouts?

• Marketing Funnel Analysis: Of all the leads generated from paid search, how many requested a demo, and how many of those closed as customers? A Sankey diagram immediately highlights drop-off points.

• Budget and Resource Allocation: How is our company's marketing budget distributed across different channels, campaigns, and regions?

For example, you could visualize your website traffic: a wide band might show 'Google / Organic' traffic flowing to your 'Homepage'. Smaller bands from the homepage could then flow to 'Product Pages' and your 'Blog'. The diagram makes it immediately obvious that organic search is your main acquisition channel and your homepage is the main entry point.

Data You Need to Create a Sankey Chart

Before you can build anything in Looker, you need a dataset structured in a very specific way. Sankey diagrams require just a simple table with three specific columns:

1. Source: The starting point or beginning stage of a flow. Let’s call this the source node.

2. Destination: The end point or next stage of the flow. We can call this the target node.

3. Value: A number representing the quantity of the flow. Let's call this the value.

Your finished data should look something like this, representing a simple user journey:

Getting your raw data into this friendly format is often the most challenging part. It typically involves using SQL to define each “step” of the journey as a separate query, and then combining them all using UNION ALL. Each query defines one specific flow (e.g., from Homepage to Product Page) and calculates the value for it (e.g., number of unique users who made that specific move).

How to Build Your Sankey Diagram in Looker: A Step-by-Step Guide

Let's get into the practical steps. Building a custom visualization like a Sankey diagram involves three main phases: installing the visualization from the Marketplace, preparing the data with LookML, and then building the chart in the Explore interface.

Step 1: Install the Sankey Visualization from the Looker Marketplace

Looker doesn’t include a Sankey diagram as one of its default chart types. You’ll need to add it from the Looker Marketplace, which hosts various pre-built blocks, applications, and custom visualizations.

• Navigate to the Marketplace from the main menu bar at the top of your Looker instance.

• In the Marketplace, select visualizations.

• Use the search bar to find "Sankey".

• Click on the Sankey visualization and then click the blue Install button. Follow the prompts to complete the installation.

Once installed, the Sankey diagram will be available as a chart option in any Explore.

Step 2: Use LookML to Define Your Data Structure

This is where the magic happens. You need to create a LookML view that transforms your raw data into the necessary Source, Target, and Value structure. The most common and flexible way to do this is with a derived table.

A derived table lets you write a custom SQL query and treat its output as a standard database table within Looker.

Here’s a basic LookML view file. The SQL inside the derived_table creates the three columns we need by stacking different user path segments on top of each other with UNION ALL. In this example, assume we have a table called web_events that tracks user page visits.

view: user_flow_sankey { derived_table: { sql: -- Step 1: Traffic from external sources to the homepage SELECT traffic_source AS source, 'Homepage' AS target, COUNT(DISTINCT user_id) AS value FROM web_events WHERE landing_page = TRUE GROUP BY 1, 2

}

dimension: source { type: string sql: ${TABLE}.source }

dimension: target { type: string sql: ${TABLE}.target }

measure: value { type: sum sql: ${TABLE}.value } }

What this LookML code does:

• view: user_flow_sankey: Defines a new view named 'user_flow_sankey'.

• derived_table: { sql: ... }: This section contains the SQL query.

• The SELECT statements: Each SELECT statement defines one part of the user journey. The first one finds the initial traffic sources (like 'Social', 'Email', 'Paid Search'). The second finds users moving from the Homepage to other pages. The third tracks users moving from a product page to the cart.

• UNION ALL: This SQL command combines the results from each individual SELECT statement into one clean table.

• Dimension & Measure Definitions: Below the derived table, we define the dimensions (source, target) and the measure (value) so that Looker users can select and work with them in an Explore.

You’ll need to save this LookML code in a view file within your Looker project and join it into your model. This will expose the "User Flow Sankey" view in your Explore.

Step 3: Build the Sankey Chart in an Explore

With the LookML setup complete and the visualization installed, building the chart is the easy part.

1. Navigate to your Explore where the new user_flow_sankey view is available.

2. Select the fields:

   • From the User Flow Sankey view, click on the Source dimension.

   • Click on the Target dimension.

   • Click on the Value measure.

3. Run the query: Click the Run button. You should see a table appear in the data pane with the three columns.

4. Choose the Sankey Visualization:

   • In the Visualization pane, click the ... button to the right of the default chart options.

   • A menu with all available custom visualizations will appear. Select Sankey.

Looker will automatically read your data. Because you've already named your fields "source," "target," and "value," the chart will render without needing any further data mapping.

Step 4: Customize Your Sankey Diagram

Your Sankey chart is now visible! To make it more insightful and presentable, click the Edit button in the top-right corner of the visualization pane.

Here you can customize various settings, such as:

• Colors: You can apply a color palette to either the links or the nodes to make the chart easier to read. For example, you could assign a unique color to each of your initial traffic sources.

• Labels: Adjust whether labels are shown, and sometimes, the font size.

• Link Style: Modify the opacity of the links to either soften them or make them stand out.

Experiment with these settings until the visualization clearly tells the story you want to share.

Final Thoughts

Creating a Sankey diagram in Looker is an incredibly effective way to tell a story about flow and conversions. Once you understand the required source, target, and value data structure, you can tackle the LookML to transform your data and finally build a clear, compelling visualization in the Explore interface.

While Looker is fantastic for technical users comfortable with LookML, sometimes you need to answer questions about your marketing and sales funnels right now. For that, we built Graphed. Instead of creating complex SQL derived tables, you can connect your advertising platforms, analytics tools, and CRM in a few clicks. Then, just ask a question like, "Show me my user journey from our Facebook Ads campaigns to purchases in Shopify" and watch as a live, interactive visualization is built for you in seconds.