From Cohort to Root Cause: 4 Ways To Own Your Analytics Workflow

PLAYBOOK

From Cohort to Root Cause: 4 Ways To Own Your Analytics Workflow

It’s the final team sync before the big board meeting. You’re reviewing a dashboard your BI team put together when you notice a sudden drop in conversion from freemium users to paid customers. Everyone starts to panic and ask questions, so you decide to take charge.

You start by drilling directly into the data underpinning the KPI dashboard. Next, you pull in some additional sales and marketing data before using your masterful spreadsheet skills to quickly build out a cohort of freemium users from the last week.

You start taking questions from the audience (your colleagues) and doing rapid iterative analysis until you find the root cause of the problem right there in the meeting. The crowd (conference room) goes wild!

And then you wake up. What really happened?

The Reality of the Traditional Analytics Workflow

Unfortunately, you were unable to access the data underneath the KPI dashboard because you don’t know how to code. So you had to end the meeting early and “take it offline” to ask your BI team for help.

When they told you it would be at least 72 hours before they could get to your request, you decided to take matters into your own hands. You asked them to extract the data from their cloud data platform to a tool you and nearly 1 billion other people know how to use: the spreadsheet.

DID YOU KNOW? 

Even people with technical skills prefer spreadsheets! More than 50% of data experts still use Excel to analyze data, while 34% use Google Sheets.

But you soon discovered that much of the data had been aggregated to fit within Excel’s scale limitations. It was also missing data from a crucial source. Not to mention, it was already out-of-date the second you downloaded it.

You sent an emergency ping to your BI team requesting another extract, this time with all of the data and the missing source. Frustrated and exhausted, you accidentally fell asleep with your head on your desk waiting for your massive spreadsheet to load.

Now it’s almost midnight, and tomorrow you’ll have to tell your boss she’ll have to wait at least 3 days for your BI team to help you complete your analysis. It’s going to be a rough board meeting!


This is a prime example of how the traditional analytics workflow is holding teams back from making data-driven decisions at the speed and scale required by today’s fast-paced and highly competitive market.

The Traditional Analytics Workflow Holding Modern Teams Back

Fortunately, new cloud-native technologies are emerging that empower data-savvy spreadsheet gurus like you to take control and reimagine their analytics workflow. These solutions allow teams to harness their existing Excel skills to independently investigate billions of rows of live data, join additional data sources and drill into granular-level detail, and collaborate to find critical answers on demand — without having to depend on BI.

This guide explores 4 of the most common yet complex and iterative analyses done by cross-functional departments like marketing, finance, and sales. We’ll examine how leading teams are reimagining their analytics workflows and breaking free from traditional spreadsheets to do them more quickly, easily, and accurately than ever before.

#1: Root Cause Analysis

A root cause analysis is a method of problem solving used to identify the origin of business issues and find appropriate solutions to prevent them from occurring in the future. There are multiple methodologies and techniques to perform a root cause analysis to find the source of a problem or trend, but overall, it helps companies:

  • Improve organizational agility and resilience
  • Mitigate blockers to company growth
  • Minimize unnecessary costs
  • Increase operational efficiencies
  • Maximize profitability

How traditional spreadsheets are holding you back

  1. Excel crashes or slows down with data sets of 1 million rows or less, but modern root cause analysis requires teams to analyze up to 100s of billions of rows. As a result, data must be summarized or aggregated before extraction, which prevents teams from drilling in and seeing the level of granularity they need to explore and identify core issues.
  2. As teams ask consecutive questions and make new discoveries in the data, it’s common to identify additional data sources that need to be joined into the analysis. However, doing this in a spreadsheet is a highly manual and messy process — especially if this data is in a non-standard format, like JSON, which requires additional parsing and preparation by a data expert before it can be used in a traditional spreadsheet.
  3. Spreadsheets are static, siloed snapshots of data, which means they’re instantly outdated as soon as your BI team extracts them from their cloud data platform. Sharing them with colleagues creates governance, security and version control problems, and any future updates must be built from scratch using new (and instantly stale) data.

How root cause analysis should be done

A leading Fortune 500 foodservice distributor in the US has 300,000 restaurants and foodservice operators dependent on their deliveries to run their businesses and succeed, resulting in a multi-billion row dataset of service-level data. Employees use a Service Level Impact dashboard in Tableau to identify issues that must be addressed to ensure fulfillments are achieved and SLAs are met.

But they lacked the ability to drill down directly into the dashboard to get to the record level without BI team assistance, so employees had to request an extract of all the data related to each specific issue. Then they would rely on their spreadsheet skills to attempt to determine exactly what the problem was, why it had happened, and which action should be taken to resolve it.

This lack of timely access to data negatively impacted employees’ ability to fully understand and resolve issues, which led to missed SLAs, penalties, and ultimately, customer retention and acquisition challenges. Not to mention, the BI team was spending 20% of its time answering ad hoc questions and extracting data – data that needed to be continuously refreshed as issues evolved or requirements changed.

To solve this issue, the BI team recreated the Service Level Impact analysis in a cloud-native analytics solution and embedded a link to the analysis in the Tableau dashboard. Now 2000+ employees can click into the live, governed, multi-billion row dataset underpinning the dashboard to investigate the data and create pivot tables just as they had in Excel. Teams are able to quickly spot and address potential issues before they become serious problems.

#2: Campaign Spend Attribution

Campaign spend attribution is the science of determining which touch points, channels, and messages along the customer journey have the greatest impact on conversions that drive pipeline. The goal is to identify the tactics and initiatives that generate the highest return on investment (ROI) to:

  • Minimize customer acquisition costs
  • Justify and more effectively allocate budgets
  • Increase marketing campaign conversion rates
  • More accurately forecast sales performance
  • Maximize the return on marketing spend

How traditional spreadsheets are holding you back

  1. Each prospect and customer interaction creates dozens of data points. Over time, this adds up to millions or even billions of rows of data from different sources that must be analyzed at once for accurate insights. Spreadsheets tap out at around a million rows. Aggregating or summarizing data to fit within these limitations prevents marketers from being able to understand the efficacy of individual messages or designs.
  2. Multi-touch campaign spend attribution requires an end-to-end view of the customer journey across each marketing touch point, through the sales process, all the way to customer success. Getting a complete picture means joining data across dozens if not hundreds of disparate data sources to capture the full lead journey. This is extremely difficult if not impossible to do in spreadsheets. That’s why most companies settle for first or last-touch attribution models that leave out all the valuable touchpoints in the middle of the customer journey.
  3. Given the customer journey touches so many different departments and teams, coming to a fair and accurate consensus about which activities generate the highest returns is a highly collaborative process with multiple stakeholders. But the only way to share traditional spreadsheets is via email. This not only drastically slows analysis, but is also a recipe for data governance, security, and version control issues.

How campaign spend attribution should be done

Yesware, a popular sales productivity platform, had four years of marketing pageview and product trial data they wanted to merge to better understand their customer journey and attribute ROI across marketing efforts. But isolated data sources combined with complicated BI tools made it nearly impossible for the team to surface impactful data insights.

“We spent a lot of money on marketing campaigns that didn’t go anywhere and didn’t produce desired results. That’s expensive.” says Ian Adams, Yesware’s VP of Sales and Marketing.

To ensure Yesware’s continued success, it was obvious that they needed to harness the power of data insights to optimize their funnel and align the efforts of their sales, marketing, and product teams. Luke Bussey, Yesware’s growth consultant, set out to help them reach that goal by turning to cloud-native analytics.

Soon, the marketing team at Yesware was able to use their spreadsheet skills to combine data from Customer.io, Google Analytics, Salesforce, Zendesk, and Facebook ads for a complete view of user behavior at each stage of the funnel — without writing a single line of code. They can now analyze each touchpoint, past and present, and see the results of every interaction.

“We can finally tie together the complete buyer journey from the first impression with our brand to the time they buy and then even through customer lifecycle marketing,” says Adams. “This has dramatically dropped our spend. We’ve cut our customer acquisition costs in half.

This has dramatically dropped our spend. We’ve cut our customer acquisition costs in half.

Ian Adams

VP of Sales and Marketing, Yesware

#3: Cohort Analysis

Cohort analysis segments data sets with information about users, prospects, or customers into smaller subsets based on shared characteristics, behaviors, and/or experiences within a given timeframe or funnel.

Businesses use cohort analyses to better understand how to engage and interact with these specific groups or cohorts to:

  • Minimize customer acquisition costs
  • Increase marketing and sales conversion rates
  • Improve customer loyalty and retention
  • Maximize customer lifetime value
  • Boost product or service adoption

How traditional spreadsheets are holding you back

  1. Every cohort analysis should start with an educated hypothesis, but identifying your highest value cohorts requires trial and error. Rapid iteration and data investigation is necessary to create, discover, and refine cohorts based on an unlimited combination of data points. This requires business teams who know exactly what they’re looking for to be able to do rapid what-if analysis at scale in a way that spreadsheets don’t allow.
  2. Because of the large datasets involved, cohort analyses done via spreadsheet extracts often present data at an aggregate level and don’t allow users to dig into the complete data with record-level detail. However, the ability to slice cohorts down to an individual in-app message or SKU often yields the most surprising and impactful insights.
  3. Insights are only as good as they are recent. Cohort analyses should be done by querying live data — not reviewing static, weeks-old extracts. Timing becomes especially critical when you have to operate within a narrow or fixed window (i.e. you’re running a promotion that lasts just a few days or you just introduced a new product).

How cohort analysis should be done

The sales of a highly recognized producer of home and office equipment was heavily dependent on big box retailers like Staples, Best Buy, and Office Depot. As those chains face increasing market volatility, they knew they needed to invest in direct-to-consumer marketing to mitigate risk.

Rather than having to continuously go to the BI team for help or rely on limiting data extracts, the company turned to the latest cloud analytics technology. Their goal was to empower their marketing team to independently answer ad-hoc questions against hundreds of millions of rows of data — and fast.

Soon, marketers were able to put their Excel skills to use to investigate these massive data sets in granular detail. They quickly created rich customer cohort analyses based on characteristics like email habits, past purchases, what they own, what they click on, etc. Now they’re able to personalize customer experiences in near real-time, which has led to an improvement in customer retention and sales revenue!

#4: Scenario Modeling

Scenario modeling involves exploring “what-if?” questions to evaluate possible outcomes based on different variables including budget allocations. Unlike conventional forecasting, which is based on a “most likely” or “best case” prediction, scenario modeling produces a multitude of possible futures based on different inputs and variables.

This level of insight allows companies to create flexible long-term plans, develop contingencies, and make decisions that:

  • Circumvent risks and financial losses
  • Optimize capital and operational expenditures (CAPEX and OPEX)
  • Adjust lead journeys to maximize campaign conversion rates
  • Accelerate corporate growth and profitability
  • Improve organizational resilience and agility

How traditional spreadsheets are holding you back

  1. Accurate scenario modeling requires up-to-the-minute data from across multiple systems such as CRM, marketing automation, accounting software, and others. The data from these sources often comes in different formats and requires BI teams to normalize it before it can be used. What’s more, because spreadsheets are instantly outdated the second they’re downloaded, updating them with the latest data requires constant reconciliation across each of these systems. This is an extremely manual and time-consuming process that takes hours away from more strategic and impactful work.
  2. Frequent manual updates are sure to result in human error and inaccurate scenarios. This critical issue is compounded by the need to run scenarios by different key stakeholders to help develop contingency plans and make decisions. Each time a spreadsheet is attached to an email it creates a new data silo — not to mention a major data governance and security risk!
  3. Due to the size and performance limitations of traditional spreadsheets, extracts must either aggregate or leave out large data sets. This makes it difficult, if not impossible, to do analysis spanning long term trends over decades of global data down to granular detail to identify subtle but important trends or factors.

How scenario modeling should be done

As a rapidly growing SaaS start-up, Sigma Computing requires its finance team to update its revenue forecast and run cash flow projections on a monthly basis. But creating best, likely, and worst-case scenarios is traditionally a painfully tedious, time-consuming, and error-prone process. It involves manually updating spreadsheets with new subscriptions and different renewal, upsell/downsell, and churn percentages for each new scenario.

Fortunately, Sigma’s finance team is now ahead of the curve by using cloud-native analytics to run “what if” analyses and rapidly generate multiple scenarios around key metrics that are critical for Sigma’s success. Rather than using stale, siloed spreadsheets, all of the data they need is live, governed, and comes directly from the Snowflake Data Cloud.

“The fact that I’m able to use my spreadsheet skills to directly access and analyze billions of rows of live data from different sources in a single Excel-like interface is a game changer,” says Firdaus Khan, Sigma’s Senior Financial Analyst. “I’m able to set revenue goals based on historical performance that are also aligned with our corporate objectives. It’s also easy to collaborate with stakeholders around multiple sales capacity scenarios, so we can now create holistic booking forecasts in minutes rather than hours.”

We can now create holistic booking forecasts in minutes rather than hours.

Firdaus Khan

Senior Financial Analyst, Sigma

Reimagine Your Analytics Workflow with Sigma

Traditional spreadsheets simply weren’t built for modern business needs — but they are still the most beloved and flexible analytics tool ever created. Most cloud BI solutions were created to handle the complexity and vastness of today’s data — but they limit access and analysis to only the most technical users.

That’s why Sigma delivers an analytics spreadsheet experience at cloud scale and speed. Sigma’s cloud-native analytics solution was purpose-built to empower teams across finance, sales, marketing, and more to independently investigate live data at scale, easily find answers to ad hoc questions, and work together to get to the heart of complex problems in real-time.

Don’t fall asleep waiting for answers. Independently doing complex, iterative analysis like scenario modeling and campaign spend attribution on billions of rows of live data isn’t a dream. Be the data hero your company needs and reimagine your analytics workflow with Sigma!

Request a demo or sign up for our virtual hands-on lab to see Sigma in action!