Zayn's Notes

Ch02-L07 案例研究 周度业务回顾 问题分析

17 min read

案例研究：周度业务回顾——问题分析

我的理解

WBR 案例的核心价值不在于 Excel 或 SQL 本身，而在于示范“是否该用 GenAI”这一决策框架：先识别痛点，再做机会规模评估（opportunity sizing），最后才投入实现。WBR 的三段式工作流（数据收集→分析→呈现）揭示了一个普遍规律：分析师的精力被手工重复劳动占据，真正有价值的洞察因此被挤出，角色因此被矮化为“谨小慎微的机器”。机会规模评估的操作极为简单——直接询问 ChatGPT 某个自动化方案是否可行，比纸上规划更高效。本案例适用于任何工作场景：关键是练习识别工作流中可自动化的痛点，然后用构建者心态快速验证。

相关链接

原文

Lesson 14 of 68 案例研究：周度业务回顾——问题分析 / Case study: Weekly business review - Problem analysis

我们已经介绍了几个独立的小例子，学习了一些有用的原则，并且跃跃欲试。下面，我们用一个端到端的案例研究，来展示如何应用所学的内容。

这个场景叫做“周度业务回顾”（Weekly Business Review，简称 WBR）。它是许多公司（例如亚马逊）每周回顾核心业务指标的一种常规做法。在这个案例中，我们扮演分析师的角色，每周需要为领导层准备评审材料。具体来说，我们需要准备一份 Excel 文件，用于计算、呈现和可视化数据，并向团队发送一封邮件，附上关键发现和这份 Excel 文件，方便团队进一步深入研究。

这里之所以选择 Excel 作为工具，是因为它在很多公司中都很常见。不过，同样的原则也适用于其他替代品，比如 Google Sheets。我们只需要把 ChatGPT 提示词中所有的“Excel”替换成“Google Sheets”即可。我们在这里提供了所有步骤涉及的示例文件和数据，方便你在自己的环境中复现。

授人以渔

我们之所以选择 WBR，是因为它是重复性工作和创造性工作的混合体。WBR 通常曝光度很高——初级分析师产出的结果可能会被总监级别的人看到。然而，大多数 WBR 都充斥着例行、枯燥的指标监控，而非可执行的洞察。这是因为分析师的精力被重复性任务占据，几乎没有空间去产出有价值的洞察。

在深入展开之前，需要说明的是，这只是一个抽象的示例。你的工作可能并不涉及 Excel，也不做周度回顾，甚至和数据无关。但重点在于：1）练习构建者思维；2）养成尽可能将重复任务自动化的习惯；3）把注意力和精力留给创造性的工作。这些收获将会极大地助力你的职业发展。

请记住，我们学习的核心是这些思维方式。有了正确的思维方式和习惯，随着 AI 不断发展，你的影响力也会持续提升。

当前工作流程

WBR 的常见工作流程通常包含三个阶段：

数据收集：人们使用 Excel，主要是看中它在数据可视化和呈现方面的便利性。生产数据通常存放在某些“真正”的数据库中。因此，分析师通常会在数据库上运行预先写好的查询，并把结果复制粘贴到 Excel 中。这对应示例 Excel 文件中的“Data”标签页。

数据分析：在许多情况下，Excel 文件本身就是一个模板，所有的分析逻辑都已经预先内置。在我们的示例文件中，当“Data”标签页被更新后，其余标签页会自动刷新以反映最新数据。具体来说，“Analysis”标签页会自动高亮某些条目，“Visualization”标签页会绘制相应的图表。

数据呈现：随后，分析师会做一些分析，并撰写一封邮件来呈现这些发现。在上面的例子中，分析师可能会先粘贴一张图表作为概览，然后逐一介绍那些被高亮的单元格，并附上相应的解释。这里我们以 Outlook 应用为例。

同样地，有些公司可能使用仪表盘而非 Excel，使用 Confluence 或 JIRA 而非邮件。但“数据收集—数据分析—数据呈现”这一抽象框架是不变的。

第一步：识别痛点

在讨论如何使用生成式 AI（GenAI）来自动化这个工作流之前，更重要的也许是先讨论在这里是否该使用 GenAI。有多种思考这个决策的角度，我们会在后续模块中更详细地展开。但一个典型的方法包含两个方面：1）我们当前的痛点；2）其中有多少能够被 GenAI 解决。

痛点高度依赖于具体的工作流程和所用工具。多年来，我们总结出了 WBR 的以下痛点：

数据收集既人工又耗时：在很多场景下，分析师需要登录 SQL 数据库，运行一些查询，等待查询完成，然后再把结果复制粘贴到 Excel 文件中。这是一个手工过程，每一步都需要时间。在此期间，分析师会被很大程度地占用，几乎无法做其他事情。

数据呈现重复且依赖人工：邮件大多基于模板，但分析师仍然需要把图表从 Excel 文件中一张张复制粘贴到邮件里。

缺乏洞察：由于分析师每周的时间都耗在重复性工作上，往往没有足够的时间去探索规律、提出假设、并形成可执行的洞察。结果就是，分析师被困在低技能的劳动里。

结合我们已经学到的内容，可以从多个角度借助 GenAI 来改进这个过程：

与其手工登录 SQL 数据库并执行查询，不如让 ChatGPT 写一段 Python 程序来完成这件事。

与其手工把结果复制粘贴到 Excel 文件，不如把这一步也交给 Python 程序来做。

同样地，把 Excel 中可视化好的图表复制到邮件草稿这一步，也可以通过编程方式完成。

第二步：评估机会大小

顺着这一思路，我们应当养成立即通过动手构建来验证这些想法可行性的习惯。为了在“探索”和“利用”之间合理分配时间，在做决策前对机会进行规模评估是一种很好的实践。具体来说，我们会问 ChatGPT 这件事到底是否可行、是否容易做到，并据此判断 GenAI 能否解决我们的痛点。

练习 1

========================================================

构造提示词，向 ChatGPT 询问上述想法是否可行、是否容易实现。

提示：

评估时不必把所有细节都过一遍。结合提出的解决方案，凭借自己的判断来评估难度即可。这可能需要一些经验，但只要开始练习，你很快就会找到感觉。

========================================================

做完这个练习之后，我们会发现，这三件事其实都是可行而且并不困难的。

第三步：尽可能自动化

这样一来，原本的工作流就被压缩成了一个单步的工作流：运行程序。程序会负责登录、执行 SQL 查询、收集结果、写入 Excel 文件、收集图表，并起草邮件。运行结束后，我们会得到一份可以直接编辑的邮件草稿。这将极大地减少手工流程，让分析师在程序运行期间可以腾出手来去做其他事情，从而切实地解决痛点。基于这一判断，我们决定继续使用 GenAI 来对整个工作流进行自动化。

English Original

We’ve covered several small standalone examples, learned a few helpful principles, and are eager to try them out. Let’s use an end-to-end case study to showcase how we can apply what we’ve learned.

The scenario is called Weekly Business Review (WBR). It’s a practice of reviewing core business metrics each week in many companies, such as Amazon. In this case, we play the role of an analyst who needs to prepare the materials for the leadership team to review each week. Specifically, we need to prepare an Excel sheet to calculate, present, and visualize the data, and send an email to the team with key findings and the Excel sheet attached, in case the team wants to dig deeper.

Here we use Excel as the tool because it’s a common choice among many companies. However, the same principles apply to alternatives, such as Google Sheets. We just need to change all “Excel”s in the ChatGPT prompt to “Google Sheets.” We provide sample files and data for all the steps here so you can easily reproduce them in your environment.

Learning to fish

The reason we chose WBR is because it’s a blend of repetitive tasks but requires creative work. Usually, WBR has high visibility – results produced by entry level analysts can be seen by directors. However, most WBR are filled with routine and boring metric monitoring, rather than actionable insights. This is because the analysts’ bandwidth is consumed by the repetitive task, leaving little space for valuable insights.

Before we dive deeper, it’s worth noting that this is an abstract example. It’s possible that your role doesn’t touch Excel or perform weekly reviews, or even not data related. But the point is to 1) practice a builder’s mindset; 2) build a habit to automate your repetitive tasks as much as possible; 3) save your attention and efforts on the creative tasks. Your career will benefit greatly from these learnings.

Remember that the focus of our learning are the mindsets. With the right mindsets and habits, your impact will keep increasing with better development of AI.

Current workflow

A common workflow for WBR consists of three stages:

Data Collection: People use Excel mainly for its ease of data visualization and presentation. The production data live in some “real” databases. So typically the analyst would run some pre-written queries on the database, and copy-pasting the results to Excel. This corresponds to the “Data” tab in our sample excel file.

Data Analysis: in many cases, the Excel sheet is a template, and already has all the analysis magic baked in. In our sample file, when the “Data” tab gets updated, the remaining tabs will automatically update to reflect the latest data. Specifically, the “Analysis” tab will automatically highlight some entries, and the “Visualization” tab will plot some charts.

Data Presentation: then the analyst would do some analysis, and write an email to present the findings. In our example above, the analyst would probably paste in the chart to provide an overview, and then introduce the highlighted cells, with potential explanation. Here we use the Outlook app as an example.

Again, it’s possible that some companies use dashboards rather than Excel, use Confluence or JIRA rather than email. But the abstract framework of data collection, data analysis, and data presentation remain unchanged.

Step 1: Identify the pain point

Before we talk about how to use GenAI to automate the workflow, it’s potentially more important to first discuss whether to use GenAI here. There are multiple possible ways to think about this decision. And we will cover them in more detail in the following modules. But a typical approach involves two sides: 1) our current pain points, and 2) how much of them can be resolved by GenAI.

The pain points heavily depend on the specific workflow and tools used. Over the years, we have identified the following pain points with WBR:

Manual and time-consuming data collection: In many cases, the analyst needs to log in to the SQL database, run some queries, wait for them to finish, and then copy-paste the results to the excel file. It’s a manual process and each stage takes time to complete. During this process, the analyst is much occupied and can hardly do anything else.

Repetitive and manual data presentation: The email is mostly derived from a template, but the analyst still needs to copy-paste the charts from the Excel file to the email.

Lack of insights: Because the analyst’s time is spent on repetitive work every week. There is often not enough time invested in exploring patterns, coming up with hypotheses, and deriving actionable insights. As a result, the analyst is stuck in low skill labor.

Based on what we have learned, there are several ways we could use GenAI to improve this process:

Instead of manually logging in the SQL database and running the query, we could ask ChatGPT to write some Python program to do it.

Instead of manually copy-pasting the results to the Excel file, we could also delegate this to the Python program.

Similarly, the copy-paste of the Excel visualized charts to our email draft can also be done programmatically.

Step 2: Opportunity sizing

Following the conjecture, we should build the habit of immediately testing the feasibility of these ideas by building. To wisely spend our time to balance explore vs. exploit, it’s a good practice to size the opportunities before we make the decision. Specifically, we ask ChatGPT whether this is possible at all or easy to do, and decide whether GenAI could resolve our pain points based on the responses.

Exercise 1

========================================================

Construct prompts to ask ChatGPT whether the aforementioned ideas are feasible and easy to do.

Hint:

It’s not necessary to go over the full details to assess the situation. Use your judgment on the level of difficulty by reviewing the proposed solution. This may need some experience, but it’s easy to get a feeling soon after beginning doing that.

========================================================

After the exercise, we will find that all the three tasks are actually feasible and easy.

Step 3: Automate as much as possible

This will change the original workflow into a single step workstream: run the program. The program will take care of the log in, SQL query execution, result collection, Excel file pasting, chart collection, and email drafting. After the execution, we will have an email draft ready to edit. This will greatly reduce the manual workflow, and can relieve the analyst to do other tasks while the program is executing. And therefore it could effectively address the pain point. Based on this, we decide to proceed to automate the workflow using GenAI.

Graph View

Backlinks