How to Analyze Customer Churn Using Looker
Tracking customer churn is one of the most important things you can do for your business, and Looker is an incredibly powerful tool for getting the job done. But building an accurate and useful churn analysis dashboard isn't always straightforward. This guide will walk you through exactly how to set up your data, define your metrics using LookML, and build a meaningful dashboard to understand who is leaving and why.
First Things First: Understanding Customer Churn
Before jumping into Looker, let's quickly align on what churn is and why it's so critical. Customer churn rate is the percentage of subscribers or customers who stop doing business with you during a specific period. If you start a month with 100 customers and lose 5, your monthly churn rate is 5%.
Monitoring this isn't just a vanity metric - it's a health check for your entire business. A high churn rate can signal problems with your product, pricing, customer service, or onboarding process. Because it costs far more to acquire a new customer than to keep an existing one, lowering churn is one of the fastest paths to profitable growth.
Looker is particularly well-suited for this analysis because it connects directly to your company's database, ensuring your reports are always built on live, fresh data. Its secret sauce, LookML, allows you to create a single source of truth for complex metrics like churn, so everyone on your team is using the exact same calculation.
Step 1: Get Your Data Ready for Analysis
You can't build a great dashboard without great data. Before you even open Looker, you need to make sure the right information is being collected and organized in your data warehouse (like BigQuery, Snowflake, or Redshift). Accurate churn analysis hinges on having clean, well-structured data.
What Data Do You Need?
To analyze churn effectively, you'll need a few key pieces of information about your customers, ideally in a single, unified table or via well-defined joins:
- Customer Identifiers: A unique
user_idorcustomer_id. - Timeline Data: Key dates like
signup_date,subscription_start_date, and critically,subscription_end_dateorchurn_date. - Subscription Details: The customer's current or last known
plan_typeorsubscription_tier. - Behavioral Data (Optional but powerful): Information like
last_login_date, number of key actions taken (projects_created,reports_run, etc.), orsupport_tickets_opened. - Firmographic/Demographic Data (Optional): Details like
company_size,industry, orregion, which help with segmentation.
For a subscription business, the cleanest way to define a churned customer is when their paid subscription is no longer active and has not been renewed. This is usually marked by a subscription_end_date that has passed.
Step 2: Define Your Churn Metrics in LookML
LookML is the heart of Looker. Think of it as a central rulebook where you define your business logic once, so everyone can explore data without having to rewrite calculations. This prevents one person from calculating churn one way and another person from doing it completely differently.
Here’s a simplified approach to defining churn in your LookML model. You’ll be working within a Looker "view" file - typically a file tied to your users or subscriptions table.
1. Create a Dimension to Flag Churned Users
First, you need a simple way to identify a user who has churned. You can create a LookML dimension that returns a "Yes" or "No" based on whether their subscription has ended. This dimension will act as a filter for all your churn measures.
dimension: is_churned {
type: yesno
sql: ${TABLE}.subscription_end_date < CURRENT_DATE() ,,
}2. Create a Measure to Count Churned Users
Now, build a measure that counts the distinct number of churned users. This measure leverages the is_churned dimension you just created as a filter.
measure: churned_user_count {
type: count_distinct
sql: ${user_id} ,,
filters: [is_churned: "Yes"]
label: "Number of Churned Users"
}3. Create a Measure to Count Total Active Users
Next, you’ll need the denominator for your churn rate calculation - the total number of active users at the start of the period.
measure: total_active_user_count {
type: count_distinct
sql: ${user_id} ,,
filters: [is_churned: "No"]
label: "Number of Active Users"
}Note: Calculating the "total users at the start of the period" can get complicated, but for a simplified monthly churn model, counting current active users and churned users in the given month can provide a good baseline. Advanced models might use more complex LookML to make this timing more precise.
4. Put It Together to Calculate Churn Rate
Finally, create a measure for the churn rate itself. This is often done as a table calculation directly in an Explore or can be predefined in LookML by dividing your two measures. For simplicity's sake, creating this calculation in an Explore is often the quickest approach.
(${churned_user_count} / ${total_active_user_count}) * 100With these few pieces of LookML, you’ve created reusable, reliable definitions for churn that your entire team can now use to build reports.
Step 3: Build Your Churn Analysis Dashboard
Now for the fun part: visualizing the data. In Looker, you'll start with an "Explore" to build individual charts (called "Looks") and then assemble them onto a "Dashboard." Here are some essential visualizations every churn dashboard should have.
1. Headline Churn Rate (Today vs. Yesterday)
At the top of your dashboard, you need the headline number. Your CEO wants to know one thing: What's our churn rate right now?
- Visualization Type: Single Value
- How to Build It: In an Explore, select your new "Churn Rate" calculation and a simple time filter for "is in the last 30 days." Looker can also display a comparison, such as "vs. previous period," to give the number immediate context.
2. Churn Rate Trend Over Time
Is your churn getting better or worse? A line chart is the perfect way to answer this question at a glance.
- Visualization Type: Line Chart
- How to Build It:
- What it tells you: You'll quickly see if there was a spike in churn after a recent price change, a disappointing feature launch, or a major competitor campaign.
3. Churn by Customer Segment
Knowing your overall churn rate is fine, but the actionable insights come from knowing who is churning. Break down your churn report using different customer segments.
- Visualization Type: Bar Chart or Table
- How to Build It:
4. Cohort Analysis: The Gold Standard
A cohort retention chart is one of the most powerful visualizations for understanding user behavior over time. It groups users by their sign-up date and tracks how many of them are still active in the following months.
- Visualization Type: Cohort Heatmap Table
- How to Build It:
- What it tells you: Did product changes made in June improve retention for all the customers who signed up that month? A cohort chart is the best way to prove it.
Step 4: Dig Deeper for Actionable Insights
A good churn dashboard doesn't just present data, it invites questions and further exploration. Looker has a few features that make this easy.
- Drill Downs: This is a core feature of Looker. Is there a big peak in churn on your trend chart for last May? Simply click on the data point to drill down and see the list of actual customers who churned. This allows you to move from a high-level view to the raw, granular records for better analysis.
- Set Up Alerts: You shouldn't have to check your dashboard every single hour to know if there is a fire. Looker lets you set alerts so you receive an email or Slack notification if your churn rate for your highest-tier users suddenly goes above a threshold.
- Join With Behavior Data: The most advanced churn analyses link to product engagement data. By joining in event tables with data on product use (like how many dashboards a user has built or how often they've used key features), you can answer critical questions like "Which behaviors predict who is likely to churn?". This helps move you to proactive churn prevention instead of passive analysis.
Final Thoughts
Analyzing customer churn in Looker moves you from simply knowing your churn rate to deeply understanding the drivers behind it. By structuring your data properly, defining metrics in LookML, and creating the right visualizations (trends, segments, cohorts), you can arm your team with the insights they need to build a stickier product, improve the customer experience, and grow faster.
While setting all of this up in Looker is extremely effective, it does require a bit of upfront data modeling and a comfort level with LookML. At our company, we wanted to help teams get straight to the insights without the setup hurdles. With Graphed, you simply connect your subscription data source (like Stripe) and your customer source (like HubSpot) and then ask questions in plain language like "Show churn last month by plan and compare it to the previous month." We instantly build the visualizations for you so you can focus on taking action rather than building reports.
Related Articles
Best Lead List Generator: 10 Tools to Build Targeted Prospect Lists in 2026
We evaluated the top lead list generators in 2026 for data accuracy, enrichment depth, and pricing. Here are the 10 best tools to build targeted prospect lists fast.
Add-Ons for Google Analytics
Discover the best add-ons for Google Analytics to automate reporting and enhance data visualization. Simplify your analytics workflow effortlessly.
Looker Studio vs Metabase: Which BI Tool Actually Fits Your Team?
Looker Studio and Metabase both help you turn raw data into dashboards, but they take completely different approaches. This guide breaks down where each tool fits, what they are good at, and which one matches your actual workflow.