Sign inGet started
← Back to all posts

From spreadsheets to notebooks: the BizOps playbook

By Christopher Hui

Updated on March 27, 2023

BizOps is all about optimization. Learn how to improve your analytical workflows by replacing spreadsheets with notebooks.

Illustrative image for blog post

This is part one in a series of guest posts from our friends at Tracked. Find out more about Tracked’s Analytics for All program — built in partnership with Deepnote — here.

Ask 10 different people what business operations — or BizOps — is, and you’re bound to get 10 different answers. But at its core, this business function has a very straightforward mission: Help organizations work faster and smarter to reduce costs and increase sales.

BizOps is all about optimizing business strategies and processes to deliver better outcomes, greater efficiencies, and more productive teams. And it’s all done using data-driven insights.

For decades now, those insights have lived inside spreadsheets. But it’s time to upgrade the BizOps toolkit for a big data world. Here’s how BizOps teams can optimize their own workflows by trading spreadsheets for a modern data notebook (you can follow along in the accompanying notebook here).

bizops-workflow.png

Moving past spreadsheet limitations

Well before programming languages such as SQL and Python became more broadly accessible to the general public, data-intense industries required tools for rigorous analysis. Companies needed a way for analysts to validate, test, and model assumptions.

Enter the spreadsheet.

First there was VisiCalc for the Apple II. Then there was Lotus 1-2-3 for IBM PCs. And then there was Microsoft Excel — once considered the bleeding edge of business technology. Excel’s improved computational capacity and friendlier user interface enabled mass adoption that still influences how many BizOps teams work today.

But it’s no longer 1987. The inherent limitations of spreadsheets don’t support modern BizOps workflows. Teams are trying to use spreadsheets in ways that were never intended, up to and including using them as internal databases.

For most BizOps teams, exploring, collaborating on, and sharing data in a spreadsheet is not optimal. But modern data notebooks — built for exploratory and collaborative data analysis — allow you to improve on existing spreadsheet workflows and overcome the most common challenges: capacity, collaboration, and traceability.

spreadsheet-vs-notebook.png

Capacity

Excel is famous for its limit of 1 million rows. But even if your dataset doesn’t exceed this limit, analysis doesn’t easily scale to your business needs. Teams end up with 100MB spreadsheets that take too long to open and load results.

Modern notebooks automatically scale with your analysis, whether it’s a million rows or a billion. If you need more computing power, you can simply spin up a larger virtual machine as the scale of your data increases.

Collaboration

Data analysis is collaborative. It requires cooperation across technical and non-technical teams. But spreadsheets are inherently single-user. It’s all too easy for collaborators to manipulate data and break logic in a spreadsheet, either on purpose or by accident. More often than not, collaboration devolves into an endless back-and-forth email thread featuring countless versions of a file.

Modern notebooks are collaborative by default. Multiple users can work together in the same environment simultaneously or tag and leave comments for teammates to work together asynchronously. Just as importantly, notebooks feature granular role-based access controls to manage how collaborators can interact with data.

Traceability

It’s best practice to make sure all formulas are shown in a spreadsheet, but this isn’t always the case. This makes reproducing analysis a challenge. Meanwhile, nested references to multiple source sheets can quickly become an analyst's worst nightmare (not to mention that nested calculations within nested calculations make tracking unnecessarily complicated).

Modern notebooks feature built-in version history and audit logs that make it easy to track all changes and system events, revert back to previous versions, and reproduce teammates’ work.

Optimizing workflows with Python

Say you’re a BizOps analyst responsible for updating a monthly sales dashboard. You need to create a series of visuals summarizing the sales performance of different locations, grouped by customer type and product line. The inclusion of a forecast, while not mandatory, would be much appreciated.

You know that data ingestion for every monthly run takes 10-15 minutes, with every formula update taking another five minutes or so. You sigh, open up your trusty spreadsheet, and prepare to use the well-worn functional workflow of VLOOKUP, pivot tables, and aggregations to summarize the performance of different products sold.

You start running the report, but halfway through you remember that your manager wants the median product performance segmented by customer type and location, as the average is subject to outliers that skew the results. Darn it. You’ll need to run the report again. Hopefully, by your second coffee break, the spreadsheet will have the numbers you need.

Inefficiencies like these add up. They’re frustrating, tedious, and time-consuming.

Here’s what the same workflow looks like using Python after you’ve uploaded your data to a modern data notebook:

1. Use .map() function: In place of VLOOKUP, we’ll use the .map() function to apply a dictionary-key pairing mimicking our lookup ranges.

2. Chain .groupby() and .agg() functions: In place of a pivot table, we’ll chain our .groupby() and .agg() functions together, simulating a chained Excel function. This allows us to nest multiple aggregations in different columns, which isn’t possible in Excel.

3. Create a no-code visualization: Simply click Visualize to create presentation-ready charts with zero code and turn your notebook into an interactive dashboard (you can learn more about data visualization here).

From there, you can store, organize, and make your analysis accessible in a dedicated workspace that houses all necessary components, including the Python version you used, any required libraries, and your machine specifications (i.e., RAM, number of cores).

It’s fast, easy, and 100% scalable. And it’s just one example of how modern data notebooks help BizOps professionals master their day-to-day work.

Optimizing workflows with SQL

But what if you’d rather use SQL instead of Python? No problem. Modern data notebooks work as flexibly as you do, allowing you to pivot between Python and SQL seamlessly.

Here’s what the workflow above looks like using SQL after you’ve uploaded your CSV to a modern data notebook:

1. Select your CSV: Click your desired CSV and right-click Query with SQL. This will create a SQL block where you can write native SQL queries against your CSV and have the results stored immediately in a DataFrame. No exporting, importing, or waiting for results.

2. Write your query: Write your native SQL query in the SQL block. Everything you need is contained within it (and is fully auditable and traceable).

3. Create a no-code visualization: This part is exactly the same: Point, click, and create. Building beautiful data visualizations is incredibly easy.

Oh, and remember how our teammates wanted a forecast? Generally, if we were to do this using our standard workflow, we’d be limited to the forecasting functions available to us within the confines of a spreadsheet. But with a notebook, we can simply import the latest forecasting library and run our forecast using the data we’ve extracted.

Let’s close this out in Python using Prophet, Facebook’s forecasting library:

1. Format time series: Format your time series into either a YYYY-MM-DD or YYYY-MM-DD HH:MM:SS format so it’s a recognizable pandas Timestamp object.

2. Fit & train data: Fit and train your data using the **prophet.fit(data)** syntax.

3. Run forecast: Specify the period you want your forecast for and run the make_future_dataframe(periods=X) function.

4. Impress your boss: Eliminate subjectivity with data-driven forecasting.

BizOps is about helping companies perform at their best, and that starts by fine-tuning BizOps workflows. Modern data notebooks give BizOps teams what they need to work faster, easier, and more collaboratively — all with a low barrier to entry and a very high ceiling on what’s possible.

Supercharge your BizOps workflows

Get started for free to see how you can upgrade your BizOps workflows with a collaborative data notebook.

Want to learn more about how to replace spreadsheets with a scalable solution built for BizOps workflows? Check out the Analytics for All program and see how you can use notebooks for entity relationship diagrams, data modeling, and much more!

Christopher Hui

Head of Product, Strategy, and Operations @ Tracked

Follow Christopher on LinkedIn

Blog

Illustrative image for blog post

Beyond AI chatbots: how we tripled engagement with Deepnote AI

By Gabor Szalai

Updated on April 3, 2024

That’s it, time to try Deepnote

Get started – it’s free
Book a demo

Footer

Solutions

  • Notebook
  • Data apps
  • Machine learning
  • Data teams

Product

Company

Comparisons

Resources

  • Privacy
  • Terms

© Deepnote