top of page
5 days ago3 min read

How We Improved Marketing Analytics Dashboard Performance: From 90 to Under 10 Seconds

Marketing teams rely heavily on analytics dashboards to make data-driven decisions. However, these dashboards are only as good as their usability. When reports take over a minute to load, frustration mounts, and decision-making slows down. This was the challenge we faced on a recent project where load times on a Looker dashboard powered by SuperMetrics and BigQuery stretched to a painful 90 seconds.

Through thoughtful optimization, we reduced load times to under 10 seconds. Here’s a breakdown of what we did, the tradeoffs we made, and additional strategies you can use to speed up your dashboards.


The Stack and the Problem

The marketing analytics stack included:

  • Looker for dashboard visualization.

  • SuperMetrics for data extraction from marketing platforms like Google Ads, Facebook, and others.

  • BigQuery as the cloud data warehouse.

The dashboards were critical for marketers tracking campaign performance, ROI, and audience engagement. However, with up to 90 seconds of load time, the experience was less than ideal.

Root Causes:

  1. Complex Queries: Looker’s visualizations relied on raw, unaggregated data in BigQuery, resulting in high query complexity.

  2. Real-Time Data: The dashboards were designed to pull live data, but the volume and granularity overwhelmed the system.

  3. Unoptimized Data Flow: SuperMetrics extracted data directly into BigQuery without much preprocessing, adding unnecessary processing overhead.



The Solution: BigQuery Scheduled Queries

Our primary focus was to reduce query complexity and streamline data access. Here’s how we did it:

  1. Preprocessing with BigQuery Scheduled Queries:

    • We created scheduled queries in BigQuery to aggregate raw data into summary tables every hour.

    • These tables included pre-calculated metrics like CTR, CPC, and ROI, grouped by relevant dimensions such as campaign, region, and date.

    • By doing this, the dashboards accessed ready-to-use data instead of processing raw data on the fly.

  2. Adjusting Real-Time Expectations:

    • Real-time data is often desirable, but in our case, marketers were fine with data that was up to an hour old.

    • This tradeoff significantly reduced the processing load and query complexity.

  3. Streamlining Looker Visualizations:

    • We reviewed the dashboard layout and eliminated redundant visualizations.

    • Wherever possible, we reduced the number of fields and filters to simplify the underlying queries.

The result? Load times dropped from 90 seconds to under 10 seconds, creating a vastly improved user experience.


Additional Tips for Speeding Up Dashboards

While our solution focused on this specific stack, the principles can be applied broadly. Here are a few more strategies to optimize dashboard performance:

  1. Pre-Aggregate Data:

    • Similar to our use of scheduled queries, pre-aggregate data into summary tables or materialized views in your data warehouse. This avoids repeatedly calculating the same metrics.

  2. Optimize Query Performance:

    • Use indexes and clustering where applicable.

    • Partition your tables by time or other frequently queried fields.

    • Remove unnecessary joins or subqueries.

  3. Reduce Data Granularity:

    • Avoid pulling in more detail than the user needs. For instance, daily aggregates might suffice instead of minute-by-minute data.

  4. Limit Dashboard Complexity:

    • Too many visualizations, filters, or large datasets on a single dashboard can slow performance. Prioritize the most important metrics and keep things streamlined.

  5. Cache Results:

    • Leverage your BI tool’s caching capabilities to store results of commonly accessed queries.

  6. Monitor and Test:

    • Regularly review query performance and dashboard usage.

    • Use tools like Looker’s Performance Monitoring or BigQuery’s Query Insights to identify bottlenecks.



Final Thoughts

In today’s fast-paced marketing environment, slow dashboards can hinder decision-making. By optimizing query performance, reducing complexity, and aligning data freshness with user needs, you can dramatically improve the usability of your analytics tools.

The steps we took—using BigQuery scheduled queries and simplifying Looker dashboards—show how impactful small changes can be. If you’re facing similar challenges, these strategies are a great place to start.

Have your own tips for speeding up dashboards? Let’s discuss in the comments!


 

Based in Burbank, California since 2015, Vimware offers IT strategy and software development services. Our expertise helps small to midsize businesses excel in the digital arena. Originally a .NET/SQL shop, we now focus on AWS, Azure, and low-code Microsoft solutions, and also have extensive experience with React/JS and WordPress. As a certified Amazon AWS partner with experience in over 60 services, we are ready to help your organization thrive. Please Contact Us to discuss how we can assist you.

bottom of page