At most one email a week. Unfollow anytime with one click.
Transcript
The full narration of this video. The current sentence is highlighted while the video plays; click any sentence to jump there.
Show transcript
Introduction
Hi, and welcome to the data analysis session. In this session we'll build a monthly report from our own data, and it all starts with one step that people often skip. That step is cleaning the data. We'll recognize dirty-data problems, protect personal data, give Claude standard rules, and then check the results.
Step 1: Why data needs cleaning
There's a saying in the data world: garbage in, garbage out. Even the best analysis will fall apart if the data is messy. Cleaning data means making a table uniform and fixing its contents without changing their meaning. It's boring work when done by hand, and that's exactly where Claude helps a lot. In many small businesses and offices, cleaning data can take longer than analyzing it. With Claude, hours of work can become a few minutes, as long as we give clear rules and still check the results. There are five problems that come up most often. First, inconsistent date formats. Some write one slash ten, some write the full date with the year, and some use the month's name. Second, inconsistent product names. The one-liter iced coffee can be written three different ways, and the computer treats them as three products. Third, duplicate rows: the same order recorded twice, for example once from WhatsApp and once from the form. Fourth, empty cells, such as a quantity or price someone forgot to fill in. Fifth, odd numbers, for example a typo with an extra zero, so fifty-four thousand becomes five hundred forty thousand. These five problems can make total sales and best-selling products come out wrong. That's why we clean the data before analyzing it.
Step 2: Spotting problems and protecting data
This is a snippet of Sunset Coffee's October order data, combining Dimas's notes from WhatsApp and from the form. There are four hundred twelve rows in total. The first row looks normal. In the second row, the date format is different, the product name is written another way, and the total uses a comma. The third row uses the month's name, and the product name just says cold brew. The fourth row is suspicious: same customer, same product, same date. It's most likely a duplicate order. In the fifth row, the quantity column is empty. And the sixth row: three cups of iced coffee should be fifty-four thousand, but it says five hundred forty thousand. One zero too many. Notice that the customer column earlier contained codes, not names. That's on purpose. Before uploading, Laras removes the phone number and address columns. Then customer names are replaced with codes: P01, P02, and so on. For sales analysis, codes are enough. The key file that matches codes to customer names stays on Laras's own computer and isn't uploaded. Finally, keep a copy of the original data before changing it. If the cleaning goes wrong, we can always go back to the starting data.
Step 3: Giving Claude standard rules
Now we give Claude the standard rules. The task is to clean the orders file, and the result must be a new file so the original data stays safe. The data section has the official product list with prices. This is key: with official prices, Claude can spot totals that don't make sense. The format section sets a uniform shape for dates, numbers, and product names. The limits explain what counts as a duplicate row, and most importantly: if unsure, ask first, and don't guess numbers. Finally, ask for a change report: what was changed, how many rows, and which rows we need to decide on ourselves. Laras uploads the orders file along with the standard rules. Claude reads the whole file. Notice the gray line: Claude analyzes four hundred twelve rows, not just the snippet we can see. Claude reports the results: dates standardized, product names standardized, nine duplicate rows removed. And as the rules say, there are three rows it doesn't guess at, and hands over to Laras instead. Laras decides: empty quantities are filled in from the total divided by the price, and the odd total is fixed because it's clearly a typo. The clean file is ready to download: four hundred three rows, with total sales of thirty-eight million four hundred sixty thousand rupiah. All the numbers here are sample data. One good habit: ask Claude to show ten sample rows before and after the change. That way Laras can see at a glance whether the standard rules were applied correctly.
Step 4: Reading the results and change report
Let's look at the result. The dates are now uniform, complete with the year. Product names follow the official list. In the second row, the total has no separator, and the product name now matches the first row. Now the computer counts them as the same product. Only one cold brew row is left. Its duplicate has been removed. The hamper now has a quantity of one, from the total divided by the price. And the three cups of iced coffee are now worth fifty-four thousand. Notice that the two rows Laras decided on are shown in a different color. The raw data tab is still right next to it. The original data was never overwritten, just as our rules said. Laras asks for the cleaning report as a one-page summary for her records. The report appears in the right panel. At the top are the before and after numbers: four hundred twelve starting rows, nine duplicates removed, four hundred three final rows. Below that is the action for each type of problem. This report is a record. If one day Dimas asks why the number of orders in the report is lower than in his notes, the answer is here. Keep this report with the clean file, for example in the same folder named after the month. This small habit helps a lot when you combine several months of data later.
Step 5: Checking results and preventing dirty data
Don't trust the cleaning results right away, including results from Claude. First check: the row count. Four hundred twelve minus nine equals four hundred three. It matches. Second, total sales make sense compared with last month. If they suddenly double, suspect something is wrong. Third, pick five random rows and match them against the original data. Laras checks five orders and they all match. Fourth, make sure there are no product names outside the official list. The quick way is to ask Claude to list the unique products with their counts. Even better is preventing dirty data from the start. First, on the order form, products are picked from a list instead of typed freely. Second, dates are picked from a calendar, so the format is automatically uniform. Third, important fields such as quantity are made required. Fourth, save those standard rules as a reusable prompt, or put them in the Project instructions. Next month, Laras just uploads the new file and writes: clean it according to the standard rules. Sunset Coffee's October data is now clean. In the next episodes, this is the data we'll analyze all the way into a monthly report.
Done!
We've recognized five dirty-data problems, protected personal data, given standard rules, read the change report, checked the results, and prevented dirty data at the form. The data is clean. In the next episode we start asking the numbers questions: which product sells best, which day is busiest, and what it means for the business. See you there!
Practice exercises
Try these on your own after watching to lock in what you learned.
Remove the personal data columns from one of your files, then upload it to Claude for cleaning
Write standard rules for product names and date formats in your business or office
Match the row count and totals before and after cleaning
Ask the class material
Type a question or keyword; we find the part of this episode that explains it and jump the video there.
My notes
Notes are saved with the current video time and are only visible to you.
Questions & answers
Stuck on a step? Ask here; the instructor and other learners can answer.
No questions yet.
Your instructor
Cerdas Inovasi Digital Team
Software developers, BIM & 3D engineers, and AI practitioners
53 video classes1,286 episodes
We build software, BIM models, games and AI automations for clients every day, and turn that hands-on work into step-by-step classes: every step is shown on screen and comes with a PDF guide you can keep.
