Graphed LogoGraphed
Login

How to Make a Control Chart in Looker

Cody Schneider•

A control chart transforms a simple line graph into a powerful tool for understanding your business processes. Instead of just seeing data move up and down, you can instantly spot what’s normal, predictable variation versus what’s a significant change that demands your attention. This article will walk you through exactly how to build a control chart in Looker using table calculations, step-by-step.

What Exactly Is a Control Chart?

At its core, a control chart is a time series graph used to monitor whether a process is stable and in a state of statistical control. It visualizes your data over time and adds three important lines to provide context:

  • Center Line (CL): This is the average (mean) of your data points. It represents the central tendency of your process.

  • Upper Control Limit (UCL): This line is typically set at three standard deviations above the center line.

  • Lower Control Limit (LCL): This line is typically set at three standard deviations below the center line.

The space between the UCL and LCL defines the range of expected, normal variation in your process. This is called "common cause" variation - the routine, everyday fluctuations you can expect. A data point that falls outside these limits signals a "special cause" variation. It's an outlier, an unexpected event - either good or bad - that likely has a specific, identifiable cause worth investigating.

For example, you could use a control chart to monitor:

  • Daily Website Conversions: Are your conversion numbers fluctuating normally, or did a recent site change cause an unusual drop below the LCL?

  • Customer Support Ticket Volume: Is the daily ticket count stable, or did an outage cause a massive spike well above the UCL?

  • Marketing Campaign CPA: Is a campaign's daily Cost Per Acquisition staying within a predictable range, or did an algorithm change throw it out of control?

This method helps you distinguish between the random "noise" of a process and the significant "signals" you need to act on.

Preparing Your Data in Looker

Before you start building, you need to make sure your data is ready inside a Looker Explore. The good news is that control charts have simple data requirements. All you need is an Explore with access to two key fields:

  1. A Time Dimension: This will be the x-axis of your chart. It can be a date, week, month, or even hour, depending on the granularity you need. For example, Order Date or User Registration Week.

  2. A Numeric Measure: This is the metric you want to monitor, which will form the y-axis. It needs to be a number, like Order Count, Number of Sessions, or Average Session Duration.

For the control limits to be statistically meaningful, it's also best to have a decent number of data points. A general rule of thumb is to have at least 20-25 data points in your time series to establish a reliable average and standard deviation.

Step-by-Step: Building Your Control Chart in Looker

Once you have your data ready, building the chart involves creating a few table calculations to draw the center line and control limits. Let's walk through it with an example where we want to monitor Daily Active Users.

Step 1: Start with a Simple Line Chart

Begin by creating the basic time-series chart that will serve as the foundation of your control chart. This is the simplest part of the process.

  1. Navigate to your chosen Explore in Looker.

  2. Select your time dimension (e.g., Users Activity Date).

  3. Select your numeric measure (e.g., User Count).

  4. Click "Run" to generate the data table.

  5. Under the Visualization tab, choose the Line chart option to see your metric plotted over time.

At this point, you have a standard line chart showing the daily fluctuations in active users. Now, let's add the control lines to give it context.

Step 2: Calculate the Center Line (Average)

The center line represents the average of your metric across the entire time period you've selected. We'll create this using a table calculation.

  1. Click on the Calculations button on the data bar (it looks like a calculator icon).

  2. Click "Add calculation" and then "Table Calculation".

  3. In the popup window, give your calculation a descriptive name like "Center Line" or "Average".

  4. In the formula box, use the mean() function to calculate the average of your measure. The placeholder name for your measure column is created by combining the view name and field name, like ${view_name.field_name}.

The formula will look like this:

mean(${users.count})

Click "Save". A new column for "Center Line" will appear in your data table, with the same average value repeated for every row. You'll also see a flat line appear on your chart representing this average.

Step 3: Calculate the Upper and Lower Control Limits (UCL & LCL)

Next, we'll calculate the control limits. As mentioned, these are typically set at three standard deviations from the mean. We can calculate this using a combination of the mean() and stddev_pop() functions.

To create the Upper Control Limit (UCL):

  1. Open the Table Calculations editor again and click "Add calculation".

  2. Name this one "UCL (Upper Control Limit)".

  3. The formula will take your average and add three times the standard deviation.

The formula will look like this:

mean(${users.count}) + (3 * stddev_pop(${users.count}))

To create the Lower Control Limit (LCL):

  1. Create one more table calculation.

  2. Name it "LCL (Lower Control Limit)".

  3. The formula will take your average and subtract three times the standard deviation.

The formula will look like this:

mean(${users.count}) - (3 * stddev_pop(${users.count}))

After saving both, you'll have all the data you need for your control chart in your data table: the original measure, the center line, the UCL, and the LCL. Your chart will likely look messy at this point with all the lines. The final step is to clean up a bit!

Step 4: Configure Your Visualization

Now you can fine-tune the visualization settings to make your control chart clear and easy to read.

  1. In the Visualization panel, click the Edit button.

  2. Go to the Series tab. This is where you can customize each line on your chart.

  3. STYLE HIERARCHY: Think about what's most important. You want the actual data to be the focus and the control limits to be the context indicators. Configure each line with this in mind:

  • User Count: This is your primary data. You can set its Type to "Line & Points" to make the individual data points stand out. Make the color something bold, like blue or black.

  • Center Line: This line is important for context. Set its Type to "Line" and choose a solid but less-demanding color, like orange or dark gray. You could also change its Line Style to "Dashed".

  • UCL & LCL: These are boundaries. It's common to make them look distinct. Set them both to "Line," use a lighter color like red or gray, and set their Line Style to "Dashed" or "Dotted" to distinguish them from the solid center line. The lighter colors ensure the limits give context and do not steal the attention from your raw data.

After configuring your series, your chart should clearly display your daily active users as a primary moving line, framed by a center line and the upper and lower control limit boundaries.

How to Read and Use Your Control Chart

Creating the chart is just the first step, the real value comes from interpreting it.

A Process "In Control"

If nearly all your data points fall randomly between the upper and lower control limits, your process is considered "stable" or "in control." The variation is normal and predictable. This is great information! It tells you not to overreact to every small tick up or down. Expending energy to "fix" a fluctuation within the control limits is often a waste of time, as you're just reacting to routine noise.

A Process "Out of Control"

A data point outside the control limits is your signal to investigate. This "special cause" variation suggests something unusual happened.

  • Point Above the UCL: Did an amazing new blog post go viral, causing a huge surge in active users? That's a positive event you'll want to understand and replicate.

  • Point Below the LCL: Did a login server fail, preventing users from accessing your site? This is a negative event you need to investigate and prevent from happening again.

Other Patterns to Watch For

Beyond single outliers, there are other patterns that can indicate a process is shifting even if no points cross the control limits:

  • The Rule of Seven: Look for seven or more consecutive points that are either all above or all below the center line. This suggests that the process average has actually shifted, even if it's still within the bounds of the old limits. It might be time to recalculate the control limits based on more recent data.

  • A Clear Trend: If you see a series of points consistently trending up or down, the process is not stable, and you should investigate the cause of the trend.

Final Thoughts

By using Looker's table calculation features, you can go beyond basic line graphs and build valuable control charts to monitor the health and stability of your key business metrics. This approach helps you make smarter decisions by distinguishing between predictable statistical noise and significant signals that require action.

Of course, this process in Looker requires several manual steps - defining each calculation, configuring chart settings, and interpreting the raw output. At Graphed, we've focused on automating away this complexity. Instead of wrestling with formulas, you can connect your data sources and simply ask in plain English: "Show me a control chart for website sessions from Google Analytics over the last 60 days." and instantly get an interactive, fully-configured chart. If you're looking for a faster way to get actionable insights without the manual setup, you should try Graphed.