Data Cleaning for the Google Data Analytics Certificate

Data cleaning for the Google Data Analytics Certificate means making data suitable for a stated question while preserving a record of what changed. Start by defining what one row represents. A repeated customer ID is not automatically a duplicate order, and an empty amount is not automatically zero. Those two decisions can change the entire result.

Process Data from Dirty to Clean covers data integrity, cleaning, verification, and documentation. The exercises below are original practice, not Coursera assessment answers. They connect spreadsheet inspection to SQL checks you can use before the Analyze stage or a capstone.

What counts as a duplicate?

A duplicate repeats the same real-world observation at the intended row grain. For an orders table with one row per order, order_id is the candidate key. For an order-lines table, several rows can legitimately share an order_id because each represents a different product. Write the grain in your data dictionary before removing anything.

Use this small synthetic export. All amounts are USD; blank means unknown. The question is: what recorded revenue came from completed orders in each region?

C1 appears three times, but that does not justify deleting all except one customer row. A1 and A2 are distinct orders. The second A2 is an exact duplicate in this constructed example. If two A2 records had different amounts, you would need an authoritative timestamp or another business rule; selecting whichever appears first would hide the conflict.

How do you inspect before transforming?

Keep an unchanged raw copy and perform cleaning in a new worksheet, view, or derived table. Count rows, distinct candidate keys, missing amounts, and status values. Record the import date, file origin, and intended units. A column called amount is ambiguous until you know whether it includes tax, refunds, or shipping.

In a spreadsheet, put these five columns in A1:E6. The five data rows contain four order IDs. Sort the entire range, including every column, if you want repeated IDs together. Sorting column A alone can detach an order ID from its amount and silently corrupt the export. Use filters to inspect missing values without immediately deleting their rows.

In SQL, SELECT order_id, COUNT(*) AS row_count FROM orders GROUP BY order_id HAVING COUNT(*) > 1; returns A2 with a count of two. Replacing order_id with customer_id answers a different question: how many rows each customer has. That query is useful for customer activity but cannot establish duplicate orders.

Which rules should you apply to the sample?

• Normalize region: trim surrounding spaces and standardize case. East and “ east ” become EAST.
• Remove the confirmed duplicate: retain one A2 only because every recorded field matches and the export grain is one order.
• Retain the missing amount: A3 remains a completed order whose revenue is unknown. Flag it for investigation.
• Filter the analytical population: exclude cancelled A4 from this completed-order report, while keeping it in the cleaned source for other questions.

In Google Sheets, =UPPER(TRIM(C2)) returns EAST for the first sample region, and the same formula filled down returns EAST for the space-padded value. This is a transformation of representation, not proof that the underlying region is correct. A typo such as EATS needs an approved mapping or source confirmation.

For an exact-copy example in SQL, a common table expression can first select distinct source rows, then use UPPER(TRIM(region)) in the grouped query. Do not generalize SELECT DISTINCT into a universal cleaning solution: it cannot decide which conflicting update is authoritative, and legitimate events can have identical measured fields if their identifiers are absent.

Should you fill a missing amount with zero?

No: zero says the order has a known value of nothing, whereas NULL says the value is unknown. Substituting zero changes an average and can make the report look more complete than it is. For this example, report known revenue alongside the number of completed orders and the number with unknown revenue.

After removing the duplicate and filtering cancelled orders, there are three completed orders: A1, A2, and A3. Known revenue is 40 + 60 = 100. Only two completed orders have known amounts, so the average of known amounts is 100 / 2 = 50. Treating the blank as zero would produce 100 / 3 = 33.33, a different metric that has not been justified.

EAST has two completed orders and $100 known revenue. WEST has one completed order with an unknown amount. Do not display WEST as “$0 revenue” without qualification. A useful output includes region, completed_order_count, known_amount_count, missing_amount_count, and known_revenue so the limitation travels with the result.

How can you prove the cleaning did what you intended?

Reconcile the counts: five raw rows minus one confirmed duplicate equals four cleaned orders. Four cleaned orders minus one cancelled order equals three completed orders. The known-value count is two and the missing-value count is one. Those counts must add to the completed-order count; otherwise a filter or join has changed the population.

Use a second method for a small check: manually total A1 and A2, then compare that $100 with your SQL aggregation or pivot table. Inspect a few changed and unchanged records. If you normalize a region column, verify that the number of nonmissing region entries did not fall unexpectedly. Test edge cases such as a completely blank region and a region containing only spaces.

What belongs in a cleaning log?

Write one entry per rule: affected field, original condition, transformation, affected-row count, reason, and verification. For example: “Removed one exact duplicate A2 from five raw rows; order-level export confirmed; four unique order IDs remain.” A separate entry records trimming region values. Another says the unknown A3 amount is retained and flagged rather than replaced.

A reviewer should be able to repeat those steps without asking which rows you clicked. Preserve the raw input, cleaned output, formulas or SQL, and the log together. Avoid placing customer emails or other personal information in a public capstone. A synthetic example like this one is useful for practice precisely because its complete records can be shared safely.

Verified October 14, 2026: course scope checked against Coursera; SQL behavior checked against the BigQuery query reference. The orders and expected totals above are illustrative.

Practice next

Use the Google Data Analytics Certificate overview to place this topic in the current course sequence. For more query practice, use the SQL exercises; use the spreadsheet worksheet to check the same kinds of totals.

The Google Data Analytics study guide PDF includes cleaning references, SQL practice, and spreadsheet formula sheets. The 242-page expanded edition includes current Python labs, 120 multiple-choice questions, 69 applied prompts, and companion practice files. It is an independent supplement to the official coursework. For questions as you practice, explore the separate SimpuTech Google Data Analytics study coach.