How do I turn a broad business concern into an analysis question?

Name the decision, population, metric, time period, and comparison. For example, “Are deliveries getting worse?” becomes “Among completed orders in April, what fraction arrived after the promised date, compared with March?” If April has 12 late deliveries among 120 completed orders, its late rate is 10%. If March has 8 among 100, its rate is 8%. The rate rose by 2 percentage points; the counts alone would not express the change fairly.

Why can a join change a total that was correct before the join?

A join repeats a record when its key matches multiple rows on the other side. If one order has a $30 payment and two item rows, joining the payment directly to the items produces two $30 payment values. Summing them gives an incorrect $60. Check the grain and key uniqueness of each table, aggregate items to one row per order when appropriate, then join and reconcile totals.

Should I treat a blank numeric field as zero?

Only when the field definition establishes that blank means none. Zero is an observed quantity; blank may mean unrecorded, unavailable, or inapplicable. For values 0, 5, and blank, the mean of the two known values is 2.5. Replacing blank with zero gives 1.67 and asserts a fact the data does not contain. Record the meaning of missingness before calculating.

Google Data Analytics Certificate Study Guide

Start with concept 1 Choose a topic

Good analysis starts before a calculation. You need to know whose decision the work supports, what each row represents, and whether the available data can answer the question. This guide follows the nine-course Google Data Analytics Professional Certificate sequence from those foundations through cleaning, analysis, visualization, Python, a case study, and job-search applications. Each planned lesson uses a small, original example so you can check both the calculation and its meaning. Pay particular attention to denominators, missing values, duplicate records, and joins: each can produce a convincing but wrong result. The final case study brings the steps together in a business recommendation that acknowledges what the data cannot establish.

Foundations: Data, Data, Everywhere

1. From records to a useful finding

A ticket record is an observation: it may contain an ID, opening time, due time, and completion time. A late flag applies a defined rule to each record, such as completion after the due time. A metric then summarizes those flags. Divide the number of late tickets by the number of tickets eligible for the calculation, and state the period and population. Interpretation comes last. It asks whether the rate differs from a target, a previous period, or another comparable group, and whether the difference matters to a decision. Check that the records are complete and that tickets share the same lateness rule before comparing rates. The Google Data Analytics Professional Certificate develops this path from observations through analysis to communication.

Worked example: A service desk closed 12 tickets this week. Three closed after their due times, so the late rate is 3 ÷ 12 = 0.25, or 25% of closed tickets. That describes this week's records. Without a stated target or a comparable earlier week, it does not establish whether performance improved or requires intervention. Report both the count and rate, then request the comparison needed for the decision.

Watch the distinction: A calculated percentage is not automatically an insight. Saying “25% were late” leaves the reader unable to judge performance until the ticket definition, period, and relevant comparison are clear.

Reference: [1]

2. The analysis process

Start an analysis by turning a business concern into a question with a defined population and outcome. Collect records that can answer it, then inspect their grain and provenance. Preparation establishes usable fields and consistent formats; processing addresses errors such as confirmed duplicate rows while preserving a record of changes. Analysis computes a measure from the corrected data. Sharing explains the measure, its denominator, uncertainty, and implications in language the decision maker can use. Action belongs to the stakeholder, often followed by another measurement cycle. Each stage can send you backward: an unexpected result may reveal a faulty definition or missing source. A tidy sequence is therefore a reasoning aid, not a guarantee that the first calculation is sound. Keep the original extract so corrections can be checked.

Worked example: A manager asks for the return rate among recorded orders. The extract has 40 rows, including one confirmed exact duplicate of an order. Removing that copy leaves 39 distinct orders, four marked returned. The rate is 4 ÷ 39 = 0.10256, about 10.3%. Share “four returned orders among 39 distinct orders,” explain the duplicate correction, and compare with a defined baseline before proposing an action.

Watch the distinction: Dividing four returns by the 40 raw rows gives 10%, but the duplicate makes that denominator inconsistent with an order-level rate. Verify the record grain before reporting.

Reference: [1]

3. The analyst's role

An analyst frames questions, checks data quality, calculates measures, and explains what the evidence can and cannot support. Stakeholders supply operational context and choose among actions because they own the service, budget, and consequences. Useful recommendations connect an observed pattern to a feasible next check or intervention, with assumptions visible. For nonrenewal, begin by defining the renewal opportunity: customers whose terms ended and who could renew. Segment cautiously by plan, tenure, or service issue, comparing rates rather than counts when group sizes differ. A pattern can suggest where to investigate, but observational data alone may not establish why customers left. Present alternatives such as interviews, a process review, or a limited test. The analyst's responsibility is to make the decision more informed, including when the evidence remains thin.

Worked example: Of 40 customers eligible to renew this month, eight did not, so nonrenewal is 8 ÷ 40 = 20%. An analyst could compare that rate by plan and examine recorded support issues, stating each group's size. If one plan looks worse, the analyst can recommend checking its customer experience. The product team decides whether to change the service and how to measure the result.

Watch the distinction: Finding a higher nonrenewal rate in one plan does not authorize the analyst to declare that plan defective. Customer mix and small subgroup counts may explain the difference.

Reference: [1]

4. What a field represents

A column's appearance does not determine its meaning. An identifier names a record, even when it consists of digits; arithmetic on it has no useful interpretation. A category groups records, such as membership tier. An ordered rating supports comparisons like higher or lower, though the gaps between adjacent ratings may not represent equal changes in experience. A quantity has a meaningful unit and can often be added or averaged after checking that records are comparable. Classify fields before selecting formulas or charts. Also inspect missing values, currency, and the unit of observation: adding revenue across duplicate rows would overstate sales. A data dictionary should record each field's definition, allowable values, unit, and whether it identifies a row or a person. This prevents a technically valid calculation from answering the wrong question.

Worked example: One record contains order ID 104, tier Gold, rating 4 on a five-point scale, and revenue $25. Use 104 to locate or join the order, Gold to group it, and 4 to compare its rank with other ratings. Add the $25 to revenue totals if the row is a unique order. Averaging 104 with other order IDs would produce a number with no business meaning.

Watch the distinction: A numeric-looking field is not necessarily a quantity. Order IDs can be stored as integers for convenience, but their mean and sum do not measure order performance.

Reference: [1]

5. Tools across a data workflow

Select a tool by the work it must do and the shape of the data. A spreadsheet makes a small, inspectable table convenient for entry checks, formulas, filters, and quick review. A database query can filter and aggregate a much larger transaction table without copying every row into a manual worksheet. A visualization tool helps an audience compare the resulting values and see the period, measure, and scale. The tools can form one workflow: document a small reference list, query transactions using clear definitions, verify a few totals, then chart the aggregated result. The decisive issue is reproducibility, not which interface feels familiar. Keep the query or formula, source dates, and metric definition so another analyst can recreate the result. A chart should present the computed measure, not silently change its population.

Worked example: A team has a manually entered 20-row category list and 200,000 transaction records. Inspect the list in a spreadsheet for spelling and missing codes. Use SQL to join valid categories and sum transaction amounts by month, then check a sample month against source rows. Plot those monthly currency totals with a labeled axis. The chart communicates the result; the query preserves how it was calculated.

Watch the distinction: Moving 200,000 records into a spreadsheet simply to calculate monthly totals can make checking and repetition harder. Aggregate at the intended grain and retain the calculation.

Reference: [1]

6. Counts need context

A count describes how many events occurred; a rate relates events to the opportunities for them to occur. When sales volume changes, return counts can rise even as the fraction returned falls. Define matching numerator and denominator populations: for example, returned orders from a cohort divided by eligible orders in that cohort. Check timing because a sale near period end may be returned later; mixing this month's returns with this month's sales can distort a comparison if they refer to different orders. State whether the measure is order based or item based, and compare like periods or cohorts. Absolute counts still matter for staffing and cost, while rates help assess frequency. Neither alone captures severity or cause. Interpret a trend only after checking definitions, exposure, and relevant business changes.

Worked example: In period one, 20 of 100 eligible sales are returned: 20 ÷ 100 = 20%. In period two, 30 of 200 are returned: 30 ÷ 200 = 15%. There are 10 more returns, which may increase processing work, but the return rate is five percentage points lower. This interpretation assumes returns and sales are matched to comparable cohorts.

Watch the distinction: “Returns increased by 50%” describes the count change from 20 to 30, not the customer's likelihood of returning an order. The denominator doubled.

Reference: [1]

Ask Questions to Make Data-Driven Decisions

7. Make the question measurable

A useful question states what will be measured, for whom, over what period, and how the result informs a choice. “Reduce returns” lacks each of those details. Define a completed order, specify whether any return or only an accepted return counts, and match returns to the orders eligible for return. Agree on a baseline and a target before examining results, then state the comparison period and whether seasonality or product mix may change. A decision rule might trigger investigation when a rate fails to meet a hypothetical target; it should not imply that one fluctuation proves an intervention worked. This style of business question framing makes the requested analysis testable. Record the definition so stakeholders do not compare rates built from different order populations.

Worked example: Suppose the brief sets a hypothetical goal of fewer than 8 returned orders per 100 completed orders next month. This month, five of 50 completed orders were returned: 5 ÷ 50 = 10%. The baseline misses the target by two percentage points. Next month, use the same completed-order definition and an explicit return observation window before comparing the rates and deciding whether further action is needed.

Watch the distinction: A target such as “fewer returns” can be met in raw counts merely because fewer orders were completed. Define the rate and period before judging success.

Reference: [1]

8. Map stakeholder needs

Stakeholders may need different views of one underlying event. Operations may schedule staff from daily late-order counts; finance may estimate monthly cost from the same orders. Build a common event table with order ID, promised date, actual delivery date, late flag, and attributable cost where available. A daily count groups late flags by date; a monthly cost sums the relevant cost values over the month. Keep dates and cost definitions so each audience can trace its view to records. Avoid treating missing cost as zero, and check whether an order appears only once in each count. Discuss the proposed output with stakeholders before building it: a number can be accurate but too coarse for the action they need. Consistent definitions make their views reconcilable.

Worked example: A three-day sample contains two late orders on Monday, one on Tuesday, and three on Wednesday. Operations sees daily counts of 2, 1, and 3. The period total is 2 + 1 + 3 = six late orders. Finance can sum costs for those same six order IDs once cost records are available; the counts alone do not establish a currency total.

Watch the distinction: Reporting six late orders to finance does not answer a cost question. Keep the shared order IDs and dates, then attach documented costs at the appropriate grain.

Reference: [1]

9. Break a problem into parts

Break a broad outcome into parts that are mutually exclusive under a stated coding rule and collectively cover the observed cases. This makes the whole reconcilable and gives each part a possible operational owner. Start with the late-shipment population, assign each shipment one primary delay code, and count the codes. If a shipment had both a stockout and carrier delay, decide how primary cause is assigned or use a separate multiple-cause analysis; do not quietly count it twice in a partition. Check that the category counts sum to the late total and investigate uncoded cases. The categories suggest different follow-up questions: stockouts point toward inventory availability, while carrier delays point toward handoff and transit. A recorded reason is evidence about a process, not proof that changing one process will eliminate every late shipment.

Worked example: Of 60 shipments, 12 arrived late. Seven late shipments have stockout as their sole primary code and five have carrier delay; there is no overlap or missing code. The check is 7 + 5 = 12. Stockouts account for 7 ÷ 12 ≈ 58.3% of late shipments, and carrier delays for 41.7%. Investigate inventory and carrier records separately before selecting an intervention.

Watch the distinction: Adding overlapping cause tags and calling the sum “late shipments” can exceed the actual count. Use one primary code for reconciliation or label the analysis as multiple-cause.

Reference: [1]

10. Define the metric's denominator

A rate must name the opportunities represented by its denominator. Complaints per delivered order describes complaints relative to completed deliveries; complaints per placed order includes orders that were not delivered. Both calculations can be legitimate, but they answer different questions. Establish whether the numerator counts complaint events or distinct orders with a complaint. If one order can generate several complaints, an event count divided by orders is an event rate and need not be a percentage of orders. Align periods and populations, then label units explicitly. When comparing teams or months, hold this definition steady and look for differences in delivery completion that might change the denominator. A bare percentage is easy to repeat and easy to misunderstand; its definition is part of the finding.

Worked example: Assume eight distinct delivered orders each generated one complaint. Among 200 delivered orders, the complaint share is 8 ÷ 200 = 4%. Among 250 placed orders, the same eight represent 8 ÷ 250 = 3.2% of placed orders. The 50-order difference in denominators changes the question, so report which population the decision concerns.

Watch the distinction: Calling 4% and 3.2% conflicting results overlooks their different denominators. Neither can replace the other without changing the measure's meaning.

Reference: [1]

11. Structure a spreadsheet table

A spreadsheet table works best when each row represents one defined record and each column holds one variable. Put labels in a header row and keep data types consistent beneath them. An order table might use order ID, quantity, unit price, and calculated line total; separate order items need separate rows and an item key. Avoid merged cells, subtotals inside the data range, and values such as “3 units” in a numeric quantity column. Those choices interfere with sorting, filtering, and formulas. Calculate row values from their inputs, then aggregate the calculated column. Validate that the row grain matches the question: summing line totals gives revenue, but counting line rows does not necessarily give distinct orders. Keep source values and formulas visible enough to trace a surprising result.

Worked example: Place two orders in rows 2 and 3, with quantities 2 and 3 in column B and unit price $5 in column C. In D2 use `=B2*C2`, yielding $10; copied to D3 it yields $15. `=SUM(D2:D3)` gives $25. Each row is one order under this example, so the total represents revenue from two orders.

Watch the distinction: Writing both orders into one cell or mixing quantity and price in one column makes filtering and arithmetic unreliable. Store each variable separately and calculate from typed cells.

Reference: [1]

12. Compare feasible actions

Compare options against constraints before ranking their benefits. First define what is fixed, such as a hypothetical spending limit, required service quality, and the period over which savings are expected. Remove options that cannot meet those conditions. Then compare feasible options using the same units and assumptions. Hours saved per month are a projected operational benefit; they are not automatically cash savings unless paid time or capacity can actually be redeployed. Consider implementation effort and measurement: a small pilot can reveal whether the estimate is realistic. If estimates are uncertain, show a plausible range or a break-even condition instead of presenting a point estimate as a guarantee. The recommendation should name the feasible action, its expected benefit, its cost, and what observation would change the decision.

Worked example: Option A is estimated to save 20 staff hours per month at a $500 implementation cost. Option B saves 10 hours per month at $100. With a hypothetical $200 budget and equal quality, A exceeds the limit by $300, while B leaves $100. Recommend B within that constraint, subject to checking actual hours saved. The estimates alone do not show a cash return.

Watch the distinction: Choosing A solely because 20 hours exceeds 10 ignores the binding budget. Comparing benefits is useful only after applying the stated constraints and checking shared assumptions.

Reference: [1]

Prepare Data for Exploration

13. Choose data that fits the question

Choose a source that observes the outcome in the question. Click logs record engagement with a link; order records show purchases. A click is therefore a possible step toward a purchase, not a purchase itself. To relate the two, define an attribution rule, such as a purchase by the same tracked user within a stated window after a click. Check consent and data access, match IDs carefully, and decide how to handle multiple clicks by one person or purchases through other channels. The resulting fraction is conditional on the chosen rule and observed records. It does not prove that the campaign caused those purchases. Before calculating, inspect whether the click count represents events or distinct visitors, because the denominator changes the interpretation.

Worked example: A campaign log contains 80 click events. Under a stated rule that credits at most one purchase to each qualifying click and excludes duplicate credits, order records identify eight attributed purchases. The observed event-based fraction is 8 ÷ 80 = 10%. Report the rule and denominator. If several clicks came from one person, this is not a customer conversion rate.

Watch the distinction: Treating 80 clicks as 80 purchases confuses an interaction with the business outcome. Purchase claims require order evidence and an explicit matching rule.

Reference: [1]

14. Find the unit of observation

The unit of observation, or row grain, says what one row represents. An item table can contain several rows per order, so its row count measures items or order lines rather than orders. Establish the grain from a key and the table definition before aggregating or joining. To count orders from item records, count distinct nonmissing order IDs; then check that every item references a valid order. To calculate revenue, sum item amounts if each row is a unique item line. A join to an order table repeats order-level fields across item rows, which can inflate order-level totals. State the grain in the result, such as “three distinct orders” or “five item lines.” This small habit prevents many plausible but wrong conclusions.

Worked example: Suppose item rows have order IDs O1, O1, O2, O3, and O3. There are five item rows but only three unique IDs: O1, O2, and O3. `COUNT(DISTINCT order_id)` would return three orders in SQL, assuming no null IDs. `COUNT(*)` returns five lines. Use the count that matches the business question.

Watch the distinction: Five rows do not necessarily mean five customers or five orders. A parent record may generate several child rows, so identify the row grain first.

Reference: [1]

15. Judge a sample

A sample supports claims only about a population it can reasonably represent. Selection occurs before analysis: an app survey can reach app users, while offline customers have no chance to enter that sample. Voluntary response adds another concern because people with strong opinions may answer more often. Calculate the observed share correctly, then describe who was invited, who responded, and which customers were excluded. A larger count of biased responses does not erase the selection problem. To learn about all customers, design outreach that includes offline customers and consider differences in response rates across groups. Even a better sample estimates an outcome with uncertainty; it does not turn a satisfaction question into an exact census fact. Keep the population label attached to every reported percentage.

Worked example: An app survey receives 100 responses, 80 positive. The positive share among respondents is 80 ÷ 100 = 80%. Because offline customers could not respond and some app users may have declined, 80% cannot be stated as the satisfaction rate of all customers. Report it as an app-respondent result and propose sampling offline customers for a broader comparison.

Watch the distinction: Calling the 80% figure “companywide satisfaction” silently extends the result beyond the sampled population. The arithmetic can be correct while the generalization is unsound.

Reference: [1]

16. Record formats and metadata

Data values need metadata to be interpreted consistently. A date string requires an input format; a number needs a unit; a timestamp needs a time zone and an indication of whether it records an event or a data load. Ambiguous text should not be converted by guessing. Ask the source owner or inspect a reliable specification, then parse values using that convention and store them in an unambiguous form. Record original and transformed fields so exceptions can be audited. For time comparisons, convert timestamps to a common time zone before calculating elapsed time, then display local time where it helps users. Check daylight saving changes where applicable. A parsing operation may succeed syntactically yet place an event in the wrong month, corrupting every monthly total built from it.

Worked example: A source file shows `03/04/2026`. If its documented convention is month/day/year, store the date as `2026-03-04`; under day/month/year it would be `2026-04-03`. The difference is one month. For a delivery timestamp, also record its source time zone before assigning the delivery to a reporting day. Do not infer the convention from this single ambiguous value.

Watch the distinction: A parser accepting `03/04` does not establish what the source meant. Confirm the convention before conversion and preserve the original field for review.

Reference: [1]

17. Keys and related tables

A primary key identifies one row in its table. A foreign key in a related table points to that parent row. In an orders table, order ID should be unique; in an order-items table, the same order ID can appear many times because one order contains several items. The item table also needs its own row identifier or a suitable composite key to distinguish item lines. Before joining, check uniqueness on the supposed parent side and confirm that child IDs match existing parents. The relationship determines what counts mean: after joining, item-level rows remain item-level rows even if order fields are attached. Aggregate item amounts by order before combining them with one-row-per-order measures when necessary. This protects both referential integrity and arithmetic.

Worked example: Orders contains one row for O1. Order-items contains two rows for O1, perhaps items priced at $10 and $20. O1 is the orders primary key and the items foreign key. Joining produces two rows for O1 because there are two items. The order count remains one, while its item total is $10 + $20 = $30.

Watch the distinction: Seeing O1 twice in order-items does not mean the order was duplicated. Repetition is valid on the many side of a one-to-many relationship.

Reference: [1]

18. Privacy in data preparation

Prepare the least detailed dataset that can answer the legitimate question. A branch-level order summary usually needs branch, period, and measures, not names, addresses, or full customer histories. Remove unnecessary fields from the working extract and restrict access to sensitive source records. If linking records requires identifiers, use them only for that step and limit where the linked detail is stored. Review output as well as inputs: a subgroup with one recognizable customer may reveal information despite containing no name. Broaden the category, suppress a revealing cell, or withhold that view as appropriate to the context. Document what was removed and why so the analysis remains reproducible without exposing personal details. The data privacy engineering guide provides a related perspective on designing systems around data use and access.

Worked example: A manager requests total orders by branch. The source extract contains branch, order ID, customer name, address, and amount. Keep branch, order ID, and amount for deduplication and aggregation; exclude name and address from the working table. If a displayed branch subgroup contains one easily identifiable customer, combine it with a broader group before sharing. The requested branch total does not require revealing identity.

Watch the distinction: Removing names alone does not guarantee privacy. A small subgroup or detailed address-like attributes can still identify someone when the audience knows the context.

Reference: [1]

Process Data from Dirty to Clean

19. Profile before cleaning

Profile data before editing it. Count rows, distinct keys, missing values, and unexpected categories; inspect numeric ranges and relationships between fields. Completeness for a field is the number of records with a usable value divided by the number expected to have one. The word “expected” matters: a delivery date may properly be blank for a pending order but concerning for one marked delivered. Separate these cases before deciding whether to correct, exclude, or retain a flagged record. Record the original counts and a short exception list, then repeat the same checks after cleaning. Profiling supplies a baseline against which changes can be verified. It also helps distinguish a data quality problem from a valid process state, preventing a convenient fill or deletion from changing the population silently.

Worked example: A ten-row order extract has two blank delivery dates. As a raw field measure, completeness is 8 ÷ 10 = 80%. Flag the two rows and inspect their statuses. If both orders are pending, the blanks may be expected; if either is marked delivered, its timestamp needs investigation. Keep the exception status visible before calculating delivery-time measures.

Watch the distinction: Automatically filling both blanks with today's date would invent delivery events. First determine whether the records are pending, erroneous, or missing an observed timestamp.

Reference: [1]

20. Keep missing distinct from zero

Zero is an observed amount; missing means the amount is unknown or unavailable. They must remain distinct because most summary functions exclude missing values but include zero. Before choosing a treatment, ask what the field means and why a value is absent. A missing price might indicate an incomplete record, while zero may be a real complimentary transaction. Report the missing count alongside a summary of known values. Filling blanks with zero makes a new assumption and changes both the denominator and the interpretation of the mean. If an analysis requires imputation, explain its rule and compare results with the unfilled data. Never convert missing to zero merely to make a formula run. The same distinction matters in SQL, spreadsheets, and Python, though each tool represents missing values differently.

Worked example: Three amount fields contain $0, $5, and a missing value. The mean of recorded amounts is ($0 + $5) ÷ 2 = $2.50, with one of three amounts missing. If the blank is filled with $0, the mean becomes $5 ÷ 3 ≈ $1.67. That lower figure assumes the unrecorded amount was zero, which the data do not establish.

Watch the distinction: A blank and a zero may look similar in a report, but they make different claims. Replacing blanks with zero can bias totals and averages.

Reference: [1]

21. Identify true duplicates

A duplicate is an extra record of the same event, not simply another row with the same order ID. First identify the table's grain: an order table has one row per order, while an item table can have several rows per order. Then compare the fields that identify an item event, such as order ID, item line number, product, quantity, and price. A repeated import may reproduce an entire row; a customer may also legitimately buy two identical products. If the source supplies a stable line ID, use it to distinguish those cases. If it does not, investigate timestamps or source files before deleting either row. Keep a count of removed rows and check totals afterward. This is part of the cleaning and verification work covered by the Google Data Analytics Professional Certificate.

Worked example: Suppose order O1 has item line 1 for $10, an identical second copy of line 1 for $10, and item line 2 for $20. The repeated line ID and matching fields identify one copied record. Remove that copy. The remaining item total is $10 + $20 = $30, rather than the uncleaned $40. Record that one row and $10 were removed.

Watch the distinction: Deleting every row after the first O1 would discard a valid $20 item. Repeated parent keys are expected in an item table; test whether the item event itself is repeated.

Reference: [1]

22. Standardize text safely

Text values can split a single category into several groups because spaces and letter case differ. Define a canonical representation for a particular field, then apply the same transformation to every value before comparing or grouping. In SQLite, TRIM removes surrounding spaces and LOWER converts letters to lowercase. Preserve the raw field alongside the standardized one so you can inspect unexpected mappings and correct the rule. Normalization alone does not establish that two abbreviations have the same meaning: a mapping from 'NY' to a region name needs an agreed definition. Also distinguish a blank string from a missing value, since both may require review. Limit access to raw fields that carry personal information; the data privacy study guide offers related principles.

Worked example: A SQLite table contains region values 'NY', ' ny ', and 'CA' on three orders. The expression LOWER(TRIM(region)) produces 'ny', 'ny', and 'ca'. Grouping by that expression yields two NY orders and one CA order. Keep the original region column in the working table so an analyst can inspect each change and investigate any value the rule does not recognize.

Watch the distinction: Replacing every short code with a guessed full name is unsafe. Trimming and case normalization fix presentation differences; a code-to-name mapping requires confirmed business meaning.

Reference: [1]

23. Convert formats with a known convention

A text value becomes useful for arithmetic only when its notation and unit are known. Establish the source convention first: currency symbol, decimal separator, thousands separator, sign convention, and whether values represent dollars or cents. Parse into a numeric field, retain the source text, and flag values that fail the rule. A successful numeric conversion is not enough if the unit is wrong: 1200 cents and $1,200 describe different amounts. Compare parsed values with a few original records and check totals before and after conversion where possible. Dates need the same care because 03/04 has two plausible month-day interpretations. Store the established convention in metadata so someone repeating the analysis can explain how each value was interpreted.

Worked example: A US-formatted export contains '$1,200.00' and '$50.00', with commas marking thousands and periods marking decimals. After removing the dollar signs and thousands commas, parse the values as 1200.00 and 50.00 US dollars. Their sum is $1,250.00. Keep both original strings; if the source convention were unknown, the first value should be flagged before conversion.

Watch the distinction: Removing punctuation without checking locale can turn a valid value into the wrong number. A comma may mark thousands in one source and decimals in another.

Reference: [1]

24. Validate plausible values

A value can be syntactically valid yet conflict with the meaning of its field. Build checks from the data definition: permitted categories, units, ranges, uniqueness, and relationships between fields. A discount stored as a fraction has different valid values from a discount stored as a percentage. Cross-field checks can reveal errors that a range check misses, such as a delivery date preceding its order date or a line total that disagrees with quantity times unit price. Treat a failed check as an exception to investigate, not an automatic reason to delete or replace the record. Separate errors from unusual but possible observations by asking what the source system actually recorded. Record the rule, failing value, resolution, and effect on any reported metric.

Worked example: Suppose a fictional shop defines discount_fraction as a number from 0 through 1. Three rows contain 0.15, 0, and 1.50. The first represents 15% off, the second no discount, and the third fails the defined range because it represents 150%. Flag 1.50 and inspect the source; do not clip it to 1, since that would invent a 100% discount.

Watch the distinction: A value of 1.50 is a valid number but an invalid fraction under this field definition. Parsing checks data type; validation checks whether the value fits its meaning.

Reference: [1]

25. Check referential integrity

Referential integrity means that a child record's foreign key points to an existing parent record. For customer-level analysis, each transaction carrying a customer ID should match a customer row, unless the data model explicitly permits guests or deleted accounts. An unmatched transaction is an orphan. Find orphans with a left join from transactions to customers and filter for a missing customer key on the right. Count both the unmatched records and their monetary value, because a small row count may still affect a large share of revenue. An inner join removes unmatched records from the result, which can quietly bias totals or subgroup rates. Investigate whether the cause is a stale extract, mistyped ID, or permitted exception before deciding how to report it.

Worked example: Four transactions reference customers A, B, C, and X. The customer table contains A, B, and C only. A left join identifies X as unmatched: 1 ÷ 4 = 25% of transaction rows are orphans. An inner join would return only three transactions. Retain and flag X while resolving its status, and report any customer-level total with the unmatched transaction disclosed.

Watch the distinction: An inner join is not a cleaning decision. It silently omits unmatched transactions, so a tidy-looking result can understate transaction count or value.

Reference: [1]

26. Verify spreadsheet formulas

Spreadsheet formulas should express the intended business rule consistently across rows. Check the formula text, its referenced cells, and an independently calculated sample. Relative references should move when a formula is copied; fixed assumptions should use absolute references. A displayed total may be wrong because the input is wrong, the formula points to another row, the formula was overwritten with a typed value, or the business rule includes an adjustment you have not identified. Calculate the expected result from source quantity and unit price, then investigate discrepancies against the underlying transaction. A simple difference column can expose exceptions across a table, but it should not overwrite original amounts. After correcting confirmed errors, recalculate summary totals and retain an audit record of the changes.

Worked example: A row shows quantity 3, unit price $4, and recorded total $15. Under the stated rule total = quantity × unit price, the expected value is 3 × $4 = $12. The discrepancy is $15 − $12 = $3. Check the source order for an extra charge or entry mistake before replacing $15; if no extra charge exists, correct the total to $12.

Watch the distinction: Changing $15 to $12 immediately may erase a legitimate $3 charge. First confirm that the row's total is meant to contain only quantity times unit price.

Reference: [1]

27. Reconcile a cleaning run

A cleaning run needs a reconciliation that connects source records to the final table. Begin with row count and relevant totals, then record each removal, addition, or transformation with its reason. Confirmed duplicates may be removed; records with unresolved missing values can remain with flags. Verify that the final count follows from those decisions and that keys still have the expected uniqueness. Compare totals on unchanged fields to detect accidental loss or multiplication of rows. Exceptions should be visible in a log rather than hidden in a broad rule such as dropping all incomplete rows. Save the definitions and transformation steps so another analyst can reproduce the result. A clean table is trustworthy when its changes and remaining limits can be explained.

Worked example: Start with 10 raw rows. One is a confirmed exact duplicate, so removing it leaves 10 − 1 = 9 rows. Two of those nine lack prices and remain flagged; they have not been removed. Reconcile the output as nine records, one removed duplicate, and two unresolved price exceptions. Price averages should state whether those two rows were excluded.

Watch the distinction: Reporting seven clean rows would hide the two retained records with missing prices. Reconciliation distinguishes rows removed from values still missing in the output.

Reference: [1]

Analyze Data to Answer Questions

28. Filter to the intended population

A sum or average is meaningful only for the population named in the question. Define qualifying statuses, dates, and time zone before calculating. For completed-order revenue, canceled orders should be excluded if they create no recognized sale; a different metric might deliberately include placed-order value. Apply the same filter to every period or group being compared. Inspect boundary dates and status changes: an order placed in one month but completed in another can enter different reports depending on which date defines the period. Check the filtered row count and sum against the unfiltered figures so the exclusion is transparent. If amounts include refunds, tax, or shipping, say whether those components belong in the metric. Filters are part of the metric definition, not cosmetic spreadsheet settings.

Worked example: Five orders have amounts totaling $160. Two are canceled and together account for $40; the other three are completed. If completed-order revenue means the sum of amounts on completed orders, filter by completed status and calculate $160 − $40 = $120. Report that the $120 covers three completed orders, then apply the same status rule to any comparison period.

Watch the distinction: A visible table filtered to completed orders does not guarantee a separate total formula uses the same rows. Check the formula's range and criteria before reporting $120.

Reference: [1]

29. Calculate with spreadsheet criteria

Conditional spreadsheet functions let you calculate a metric for a defined subgroup without moving rows. SUMIF adds amounts whose status meets a criterion; COUNTIF counts matching status cells. Dividing those two results gives an average amount per matching row if each row represents one order and every matching amount is numeric. Check that the criteria ranges align with the amount range and that status spelling is consistent. A blank amount on a paid row creates a trap: COUNTIF may count the paid status even though SUMIF contributes no amount. In that situation, count paid rows with valid amounts or resolve the missing value before forming an average. State the unit of the result and the qualifying population. Practice questions can help rehearse these distinctions.

Worked example: Suppose A2:A4 contain $10, $20, and $30, while B2:B4 contain paid, paid, and void. SUMIF(B2:B4,"paid",A2:A4) returns $30, and COUNTIF(B2:B4,"paid") returns 2. The paid-order average is $30 ÷ 2 = $15 per paid order. The $30 void row does not enter either calculation.

Watch the distinction: Averaging all three amounts gives $20 and answers a different question. Conditional averages require the same qualifying rows in both numerator and denominator.

Reference: [1]

30. Control copied references

When you copy a spreadsheet formula, a relative reference moves with the destination cell. An absolute reference, marked with dollar signs, stays fixed. Use relative references for each row's own inputs and absolute references for a shared assumption such as a hypothetical markup rate. A mixed reference fixes only the marked row or column, which helps in a two-dimensional table but should be used deliberately. Inspect the first and last copied formulas to catch drift. Also check whether the fixed cell contains a fraction such as 0.08 formatted as 8%, rather than the number 8. Document the assumed rate and what the output represents. A formula can be mechanically correct yet answer the wrong question if it applies a tax rate, discount, or markup at the wrong stage.

Worked example: Put a hypothetical 8% markup, stored as 0.08, in B1 and prices $50 and $100 in A2:A3. Enter =A2*(1+$B$1) in C2 and copy it to C3. The formulas give $50 × 1.08 = $54 and $100 × 1.08 = $108. A2 changes to A3; $B$1 stays fixed.

Watch the distinction: Using B1 without dollar signs can shift the reference to B2 when copied down. A blank B2 may then produce an apparently plausible but incorrect result.

Reference: [1]

31. Weight grouped averages correctly

An overall mean combines all observations, so each subgroup contributes in proportion to its number of observations. If you have group means and counts, multiply each mean by its count, add those products, and divide by the total count. An ordinary average of subgroup means gives each group equal weight, regardless of size. That can be appropriate when the question concerns the typical group, but it does not recover the typical order or person. Confirm that groups do not overlap and use the same units and metric definition. Missing amounts also matter: weight each group by the number of observed amounts used to compute its mean, not automatically by the number of rows. Label the result with its unit of observation.

Worked example: Store A has one order worth $100. Store B has nine orders worth $10 each, totaling $90. Across both stores, revenue is $100 + $90 = $190 over 10 orders, so mean order value is $19. Averaging the two store means gives ($100 + $10) ÷ 2 = $55, which describes the equally weighted stores instead.

Watch the distinction: The $55 figure is not an arithmetic error; it answers a different question. Use order counts as weights when reporting the average across orders.

Reference: [1]

32. Aggregate with SQL

SQL aggregation changes the grain of a result. A query grouped by region returns one row per region, while SUM and COUNT summarize the source rows inside each group. In SQLite, select the grouping field, apply SUM(amount), and include COUNT(*) to show how many rows contributed. Check whether each source row is truly one order; item rows require a different interpretation of COUNT(*). A missing amount is skipped by SUM, while COUNT(*) still counts its row, so consider COUNT(amount) when assessing completeness. Filtering in WHERE happens before grouping; HAVING filters the grouped results. Verify a small group manually before trusting larger totals, and name output columns so a reader can distinguish revenue from order count.

Worked example: A SQLite orders table has (East, 10), (East, 20), and (West, 5), with amounts in dollars and one row per order. `SELECT region, SUM(amount) AS revenue, COUNT(*) AS orders FROM orders GROUP BY region` returns East, $30, 2 and West, $5, 1. East's total is $10 + $20 = $30 across two orders.

Watch the distinction: COUNT(*) here counts order rows only because the table has one row per order. Running the same query on item rows would count items instead.

Reference: [1]

33. Prevent join double-counting

Joining tables can multiply rows when their keys are not unique at the same grain. An order may have several items but one payment; joining the payment to item rows repeats the payment amount once per item. Before joining, identify the grain and uniqueness of each input. Aggregate item amounts to one row per order, then join to a payment table that is also one row per order. If payments can be split, aggregate those too. Check row counts and compare sums before and after the join. A many-to-many join can multiply both sides, so DISTINCT is rarely a reliable repair: it may discard legitimate equal-valued transactions. Design the result around the question, such as one order row with item total and payment total.

Worked example: Order O1 has two item rows, $10 and $20, and one payment row of $30. Joining payment to each item produces two rows containing $30, so summing joined payment values falsely gives $60. First sum items by order to $30, then join that one O1 row to the one $30 payment row. Both order-level totals are $30.

Watch the distinction: Applying DISTINCT to payment amounts may appear to fix O1, but two legitimate $30 payments would collapse incorrectly. Fix the grain before summing.

Reference: [1]

34. Understand SQL nulls

SQL NULL means a value is absent or unknown; it is not the number zero. In SQLite, COUNT(*) counts rows, COUNT(column) counts non-NULL values, and AVG(column) divides the sum of known numeric values by their non-NULL count. Most arithmetic involving NULL yields NULL, and a comparison such as column = NULL does not identify missing rows. Use IS NULL or IS NOT NULL for that purpose. A WHERE condition retains rows only when its expression is true, so comparisons involving NULL do not pass an ordinary equality filter. When reporting a mean, disclose how many rows were missing. Filling unknown values with zero changes both the denominator and the meaning of the metric, and should require a documented reason.

Worked example: A SQLite column contains 5, NULL, and 0. COUNT(*) is 3 rows; COUNT(value) is 2 known values. AVG(value) is (5 + 0) ÷ 2 = 2.5. `WHERE value = 0` selects only the zero row, while `WHERE value IS NULL` selects the missing row. Treating NULL as zero would produce 5 ÷ 3 ≈ 1.67 instead.

Watch the distinction: COUNT(value) is not a count of all records. It omits NULL values, so use COUNT(*) when you need the full row count.

Reference: [1]

35. Compare rates across time

Counts across periods need an exposure measure when the number of opportunities changes. A defect rate divides defects by inspected units under a consistent definition; an order return rate divides returned orders by eligible orders. Calculate each period's numerator and denominator separately before comparing rates. Describe an absolute rate change in percentage points and a relative rate change as a percentage of the earlier rate. Different periods may also have different product mixes, inspection rules, or observation windows, which can affect interpretation. A lower rate is useful evidence of a different observed frequency, but it does not by itself show why the change happened. Include counts beside rates so readers see whether the estimate is based on a small or large number of opportunities.

Worked example: January has two defective units among 20 inspected: 2 ÷ 20 = 10%. February has three among 60: 3 ÷ 60 = 5%. The defect count increased by one, while the rate fell by 10% − 5% = 5 percentage points. This comparison assumes both months used the same defect definition and counted each inspected unit once.

Watch the distinction: Three defects exceed two, but that does not mean February performed worse per inspected unit. Compare rates when exposure differs, then examine whether inspection conditions were comparable.

Reference: [1]

36. Association and causation

A difference between groups is an association until the comparison supports a causal claim. Ask how people entered each group. If a promotion was offered to customers already likely to buy, the promoted group can have a higher purchase rate even if the promotion changed nothing. Compare the groups' baseline characteristics, timing, and eligibility; watch for other changes that occurred at the same time. Random assignment can make groups more comparable when practical, while careful observational analysis can reduce some measured differences but may leave unmeasured ones. State the observed rates and their difference without turning that difference into an effect estimate automatically. The business decision may still be to test the promotion, with a predefined purchase measure and the same follow-up window for both groups.

Worked example: Among 40 promoted customers, 12 buy: 12 ÷ 40 = 30%. Among 40 unpromoted customers, eight buy: 8 ÷ 40 = 20%. The observed gap is 10 percentage points. If the promoted group was selected for prior interest, selection may explain some or all of it. Randomly assign a comparable future group before attributing a change to the promotion.

Watch the distinction: Calling the 10-point gap the promotion's effect assumes the groups would otherwise buy at the same rate. These counts alone cannot establish that assumption.

Reference: [1]

Share Data Through the Art of Visualization

37. Match chart to question

Choose a chart by the relationship you need the reader to see. Bars compare quantities across categories using a common baseline; a line is useful when ordered points, often time periods, form a meaningful sequence. A histogram groups numeric observations into intervals and answers a distribution question rather than a category-total question. Check whether the plotted measure is a count, amount, rate, or average and label its unit. Sort unordered categories when it clarifies ranking, but preserve a natural order when one exists. If categories have different exposure sizes, a chart of raw counts may mislead; plot comparable rates or show both. The chart should let a reader recover the main numerical comparison without depending on color alone.

Worked example: A service desk records 20 tickets in category A and 10 in category B during the same week. Use two bars on a shared count axis, labeled 20 and 10 tickets. Category A has 20 ÷ 10 = 2 times as many tickets. A connecting line would suggest movement from A to B, although these categories have no sequence.

Watch the distinction: A line chart is not automatically clearer because it looks smooth. Connecting unordered categories implies continuity that the data do not contain.

Reference: [1]

38. Use honest scales

A visual scale changes how large a difference appears. For bars, readers compare lengths, so starting the value axis above zero can make a small numerical gap look enormous. Begin bar lengths at zero and label values when precision matters. A line chart can sometimes use a narrower vertical range to show small changes over time, but its axis and scale must remain visible so readers can judge magnitude. Inspect whether axes are reversed, unevenly spaced, or changed between panels. Also check that category bars have equal width and that a rate is not presented as a count. Ask what impression a quick glance gives, then verify it against the actual numbers. An honest chart makes both the direction and size of a comparison legible.

Worked example: Two teams complete 98 and 100 tasks. The absolute difference is 100 − 98 = 2 tasks, or about 2.0% of 98. In a bar chart whose axis starts at 98, one bar could appear to have almost no height. Start at zero, show both labels, and describe the difference as two tasks rather than a dramatic performance gap.

Watch the distinction: A truncated bar axis can be labeled correctly and still give a distorted first impression. Check the baseline as well as the printed values.

Reference: [1]

39. Show the distribution

A mean compresses all observations into one number and can be pulled upward by a long upper tail. The median is the middle value after sorting, so it helps describe a typical observation when a few cases are unusually large. Report a spread measure or show the individual values when the dataset is small; otherwise, two groups with the same mean can conceal very different experiences. Investigate whether an extreme value is an error, a valid exceptional case, or a distinct process that deserves separate analysis. Do not remove it merely because it is inconvenient. For service times, explain whether the question concerns typical experience, total staffing demand, or unusually slow cases. Each question may call for a different summary or visual.

Worked example: Five completion times are 2, 3, 3, 4, and 18 hours. Their sum is 30 hours, so the mean is 30 ÷ 5 = 6 hours. The sorted middle value is 3 hours, and the range is 18 − 2 = 16 hours. Show the 18-hour case in a dot plot; six hours overstates the typical observed time.

Watch the distinction: The mean of six hours is mathematically correct. Calling it the typical completion time without showing the long 18-hour case gives an incomplete picture.

Reference: [1]

40. Dimensions and measures in Tableau

In Tableau, a dimension defines how records are partitioned in a view, while a measure supplies a numeric value to aggregate within each partition. Putting region in the view groups the records by region; placing Sales as SUM adds their amounts. Switching Sales to AVG divides each region's sales sum by its number of contributing records and answers a different question. Confirm the source grain before interpreting either number: two item rows are not necessarily two orders. Filters can change both group membership and the denominator of an average, so display the selection when sharing a view. Check whether missing Sales values are present and how they affect the calculation. Name the chart and axis for the aggregation shown, such as total sales dollars or average sale per row.

Worked example: A Tableau data source has two East sales rows, $30 and $20, with one row per sale. With Region as the dimension and SUM(Sales) as the measure, East displays $30 + $20 = $50 total sales. Change the aggregation to AVG(Sales), and East displays $50 ÷ 2 = $25 per sale. Label the measure accordingly.

Watch the distinction: A $25 average is not a revised total. Changing the aggregation changes the question, and its denominator is sale rows only if each row represents one sale.

Reference: [1]

41. Keep dashboard filters coherent

A dashboard rate must use a numerator drawn from its denominator. Define the base population first, such as delivered orders in the selected period, then count late orders within that same population. A branch, date, or status filter should update both counts together. Display the active selection beside the rate so a viewer knows which orders it describes. Check that the filtered late count cannot exceed the filtered delivered count, and reconcile branch counts with the overall view when branches do not overlap. A filter can also change the question: filtering to late orders before calculating a late rate would remove on-time orders from the denominator. Test dashboard controls with a small set of records whose expected counts you can calculate by hand. This habit makes an interactive dashboard easier to trust.

Worked example: A dashboard starts with 10 delivered orders, four late: 4 ÷ 10 = 40%. Selecting branch B leaves four delivered orders, two late: 2 ÷ 4 = 50%. The display should read “Branch B, delivered orders, selected period” and show both counts. Keeping the overall denominator of 10 would produce 20%, a rate for no coherent population.

Watch the distinction: A common error is to filter the late-order chart but leave its denominator card unchanged. Recalculate both counts from the same selected orders before interpreting the percentage.

Reference: [1]

42. Make findings accessible

A chart should communicate its categories, values, units, and comparison even when a reader cannot distinguish its colors. Give bars direct labels or a nearby data table, use descriptive category names, and state what the vertical axis measures. Patterns or shapes can add a second cue when several series share a chart. Provide concise alternative text that names the main finding and any limitation that matters to interpretation; it need not repeat every decorative detail. Keep text legible and preserve sufficient visual contrast. Reading order matters in a dashboard too: a title, filter state, chart, and explanatory note should form a sensible sequence. If a reader must infer which bar is “late” solely from red versus green, the chart has withheld essential information. Accessibility also helps anyone viewing a small image or a monochrome printout.

Worked example: A delivery chart has bars labeled “Late: 12” and “On time: 48,” with the axis titled “Deliveries.” The total is 12 + 48 = 60 deliveries, and the late rate is 12 ÷ 60 = 20%. Its alternative text can state: “Twelve of 60 deliveries were late, or 20%.” The result remains readable without the red and green fills.

Watch the distinction: A legend that identifies categories only by color does not make the data accessible. Put category names and values on or beside the marks.

Reference: [1]

43. Build an evidence-led story

A useful data story moves from the business question to comparable evidence and then to a decision that the evidence can support. Establish a baseline with its population, period, and metric definition. Present the later result using the same rules, then describe the difference in the correct units. For two percentages, subtracting them produces percentage points; a relative percent change answers a different question. Explain relevant context, such as a change in order mix or missing records, before suggesting what to inspect next. A visual should expose the underlying counts when samples are small. Stakeholder questions from business analysis can help clarify what decision the story is meant to inform. An observed change alone does not establish why it happened, so phrase the proposed next step as an investigation or test.

Worked example: In one week, four of 20 delivered orders are late: 4 ÷ 20 = 20%. The next week, six of 20 are late: 6 ÷ 20 = 30%. The rate rose by 10 percentage points, with two more late orders. Because both weeks use the same delivered-order definition, they are comparable; the team can inspect routes and staffing before attributing a cause.

Watch the distinction: Calling a rise from 20% to 30% a “10% increase” confuses percentage points with relative change. The relative increase is 50%, but neither figure identifies a cause.

Reference: [1]

44. Present a bounded recommendation

A recommendation is more useful when it names the action, expected benefit, assumptions, cost or downside, and a way to check the result. Treat a projected saving as a calculation based on assumptions, not as an observed outcome. State which workflow steps change, which staff are affected, and whether the estimate is gross or net of implementation effort. If quality could suffer, include an error measure alongside time saved. A small pilot can compare the new process with a recorded baseline using the same workload definition. Decide in advance what evidence would justify keeping, changing, or stopping the workflow. Communicate uncertainty plainly: an estimate based on one week of activity may fail during a busier period. The stakeholder can then weigh a bounded option instead of mistaking a single forecast for a guaranteed result.

Worked example: A proposed workflow is estimated to save eight staff hours each week. At a hypothetical labor value of $20 per hour, gross estimated savings are 8 × $20 = $160 weekly. Pilot it for four weeks, recording hours and errors against a comparable baseline. If setup takes six staff hours, account for that cost before claiming net savings; the $160 remains a forecast until measured.

Watch the distinction: Presenting $160 per week as money already saved turns an assumption into a result. Label it as an estimate and measure actual hours, costs, and errors.

Reference: [1]

Introduction to Data Analysis Using Python

45. Python values and types

Python assigns types to values, and an operation is valid only when those types support it. An integer can be added to another integer, while a string stores characters even when those characters look like digits. Data read from a text file often arrives as strings, so inspect or validate the value before conversion. `int()` accepts a suitable whole-number string; `float()` handles some decimal strings, though money calculations may need a decimal type to avoid binary floating-point rounding. Converting data should follow its meaning: an order ID that happens to contain digits is still an identifier, not a quantity to add. Handle blanks and malformed values deliberately instead of silently replacing them with zero. An error message such as `TypeError` or `ValueError` tells you whether the operation or the conversion failed, which points to different fixes.

Worked example: In Python 3, `'12' + 3` raises `TypeError` because text and an integer cannot be added. If the field is a validated count, `int('12') + 3` returns 15. If it is order ID `'12'`, keep it as text. A blank field needs a missing-value decision; `int('')` raises `ValueError`.

Watch the distinction: A digit-looking string is not automatically a number. Converting every such field can destroy identifier meaning, including leading zeros in codes.

Reference: [1]

46. Work with strings

String cleaning should follow a known input format. `strip()` removes surrounding whitespace, `lower()` gives consistent case for matching, and `split()` divides a string at a specified delimiter. Order matters: stripping the whole line leaves spaces inside its parts, so strip each field after splitting. Check the expected number of fields before assigning meanings; an extra comma may signal a malformed record rather than a third valid value. Convert a numeric part only after checking that it is present and has the expected form. Preserve the original text when cleaning data so you can audit surprising values and revise the rule. Normalization can improve grouping, but it can also merge labels that were intended to differ. Define a mapping when abbreviations, accents, or business names require more than simple case and whitespace changes.

Worked example: In Python 3, `raw = ' North, 12 '`. Running `raw.strip().lower().split(',')` gives `['north', ' 12']`. Then `parts = [part.strip() for part in raw.lower().split(',')]` gives `['north', '12']`; `int(parts[1])` is 12. This works under the stated two-field, comma-separated format. A line with another comma should be flagged before indexing.

Watch the distinction: Stripping only the complete string does not remove spaces around each separated field. Clean the parts individually, and check their count before conversion.

Reference: [1]

47. Lists and tuples

Lists and tuples both store ordered values, so indexing and iteration work with either. The choice turns on whether the collection itself should change. A list supports operations such as `append()`, making it useful when accumulating observations from a file. A tuple is immutable: it suits a fixed record whose positions have defined meanings, such as `(branch, count)`. Immutability does not make the underlying data automatically correct, and a tuple can contain a mutable object, so choose it for the intended structure rather than as a validation shortcut. Avoid changing a list while iterating over it if that can skip elements; create a filtered result instead. For records with many fields, names in a dictionary or DataFrame may be clearer than positional indexes. Whatever structure you choose, keep units and field meanings consistent across entries before aggregating them.

Worked example: In Python 3, `values = [2, 3]`; after `values.append(4)`, the list is `[2, 3, 4]` and `sum(values)` is 9. The tuple `fixed = (2, 3)` has two positions, but `fixed.append(4)` raises `AttributeError`. If these are counts, the list suits accumulating new counts; the tuple suits an unchanged two-value record.

Watch the distinction: A tuple is not just a list written with parentheses. Calling a list mutation method on it fails; choose the sequence according to whether additions are expected.

Reference: [1]

48. Dictionaries and sets

A set answers a membership or distinctness question: repeated values collapse to one member. A dictionary maps each key to a value, so it can retain a count for each customer ID. These structures answer different questions. `len(set(ids))` counts distinct IDs, while a frequency dictionary shows which IDs repeat and how often. A set has no useful business order, so do not rely on its display order for reporting. Dictionary keys must be unique; assigning the same key again replaces its previous value unless you explicitly increment a count. Check whether an ID is truly a customer identifier and whether missing IDs should be excluded. A row count, a distinct-customer count, and a repeat-customer count can all be correct for the same dataset, provided you label the unit of each result.

Worked example: In Python 3, `ids = ['A', 'A', 'B']` contains three records but `len(set(ids))` is two distinct customers. A loop using `counts[id_] = counts.get(id_, 0) + 1` yields `{'A': 2, 'B': 1}`. Thus A appears twice and B once; neither the set nor the dictionary proves that the two A rows are duplicates.

Watch the distinction: Counting distinct IDs does not count visits or reveal duplicate records. Two rows for one customer may represent two valid events.

Reference: [1]

49. Loops over records

A loop processes each observation under the same rule. Initialize an accumulator before the loop, update it once per eligible record, and inspect the final value after the loop. For a conditional total, keep the eligibility test inside the loop and make sure excluded records do not change either the total or its count. Python's built-in `sum()` is a useful independent check for a simple numeric list; a separately reasoned hand total can catch mistakes shared by two code paths. Avoid using a variable name such as `sum` that would hide the built-in function. When records have units, accumulate compatible quantities only: dollars can be added to dollars, while an average requires both a total and a count. For large tabular datasets, a library operation may be shorter, but the loop clarifies what that operation must do.

Worked example: In Python 3, start `total = 0`, then run `for value in [3, 5, 2]: total += value`. The running totals are 3, 8, and 10, so the result is 10 units. `sum([3, 5, 2])` independently returns 10. If these are daily ticket counts, 10 is the three-day total, not a daily average.

Watch the distinction: Starting an accumulator with an earlier result can silently add old data. Initialize it for each calculation and check that each intended record contributes once.

Reference: [1]

50. Conditions and missing values

A missing value means the observation is unavailable; zero is an observed quantity. In Python, `None` commonly represents an absent value, and `is None` tests for it directly. A condition such as `if value` is unsuitable for this distinction because zero, empty text, and `None` are all false in a Boolean test. When averaging recorded values, filter out missing entries, then divide by the number retained. Report how many values were missing, since the average may describe only a subset of the intended population. Do not assume missingness is harmless: absent delivery times could cluster among problem orders. In pandas, missing numeric values usually appear as `NaN`, which needs methods such as `isna()` rather than an identity test against `None`. The choice to exclude, investigate, or impute missing values should follow the field's meaning.

Worked example: In Python 3, `values = [0, None, 5]`. `known = [v for v in values if v is not None]` gives `[0, 5]`, while one value is missing. The known-value mean is `sum(known) / len(known) = 5 / 2 = 2.5`. Replacing `None` with zero would instead give `5 / 3 ≈ 1.67`, changing the interpretation.

Watch the distinction: Using `if value` to find known numbers drops the genuine zero. Test missingness explicitly so zero remains in the denominator and numerator.

Reference: [1]

51. Reusable functions

A reusable function gives a calculation one definition and a predictable response to edge cases. For a rate, specify what numerator and denominator count, require compatible units, and decide how to represent an undefined result. Division by zero is undefined, so returning `None` can signal “no eligible observations” to the caller. Validate impossible inputs if the function is intended for event counts: negative counts or a numerator larger than its denominator indicate a data or definition problem. Keep calculation separate from presentation; return a proportion such as `0.4`, then format it as 40% where it is shown. A function should also be checked with ordinary cases, boundary cases, and invalid cases. Clear names and a short description help another analyst reuse the same metric without silently changing its population.

Worked example: In Python 3, define `def rate(events, total): return events / total if total > 0 else None`. Then `rate(2, 5)` returns `0.4`, displayed as 40%. `rate(0, 0)` returns `None`, meaning no rate can be calculated. For production use, also reject `events > total`, since that violates an event-within-total definition.

Watch the distinction: Returning zero for `0/0` falsely reports a measured 0% rate. There were no eligible observations, so mark the rate undefined.

Reference: [1]

52. Read a CSV deliberately

CSV stores text fields, even when a column looks numeric in a spreadsheet. Python's `csv.DictReader` uses the header row as dictionary keys and returns field values as strings. Inspect the header, expected units, missing entries, and number format before converting. For whole-number amounts, `int()` may be appropriate; decimal currency needs a suitable decimal representation. Convert row by row and flag a value that does not parse, rather than dropping it silently. A blank amount is missing, not automatically zero. Keep the original field in the exception log so a reviewer can trace a bad conversion to its source. Quoted commas inside a CSV field are handled by the CSV parser, which is another reason to use it instead of splitting each line manually. After loading, reconcile the number of rows read and the amount of valid numeric data.

Worked example: Suppose a CSV has header `amount` and two rows containing `10` and `20`. In Python 3, `csv.DictReader` produces strings `'10'` and `'20'`. With `rows` holding those dictionaries, `sum(int(row['amount']) for row in rows)` returns 30 units. Concatenating the strings gives `'1020'`, which is text, not the total. A blank amount requires separate handling.

Watch the distinction: A CSV column's visual appearance does not establish its Python type. Check and convert valid values before arithmetic, while preserving blanks as missing.

Reference: [1]

53. Inspect data with pandas

Start a DataFrame inspection by checking row count, column names, data types, missing counts, and a few values. A summary statistic is only as meaningful as the rows and values it includes. In pandas, `mean()` on a numeric Series skips missing values by default, so pair the result with the number of observations used. A recorded zero remains in the calculation. If the column contains numeric-looking text, convert it deliberately and inspect any entries that fail conversion; a coercion option can turn bad text into missing values without explaining why. Look for implausible ranges and duplicate row identifiers before treating a summary as final. When reporting, state whether the mean describes all rows or only those with recorded amounts. Missing values may reflect a systematic process, so investigating their pattern can matter more than choosing a convenient fill value.

Worked example: In pandas, `df = pd.DataFrame({'amount': [0, None, 5]})` has three rows. `df['amount'].isna().sum()` returns one missing amount; `df['amount'].count()` returns two recorded amounts. `df['amount'].mean()` computes `(0 + 5) / 2 = 2.5` units. Report both the 2.5-unit mean among recorded values and the one missing row.

Watch the distinction: A pandas mean is not necessarily based on every row. Check the nonmissing count before presenting it as the average for the full dataset.

Reference: [1]

54. Group data in pandas

Grouping changes the unit of each output row. Before using `groupby`, identify what each input row represents, which field defines the groups, and whether you need a sum, count, or average. A sales sum per region answers a revenue question; a mean per transaction answers a typical-transaction question. Check that category labels have been standardized, since `East` and `east` otherwise form different groups. Also inspect missing group labels: pandas normally excludes missing keys from grouped output unless you request their inclusion. Reconcile grouped totals with the eligible input total to catch lost rows or unexpected duplicates. If an order has several item rows, summing item sales can be valid, but counting rows as orders would overstate order volume. Label the resulting measure and grain so a viewer knows that one result represents a region rather than a transaction.

Worked example: In pandas, `df = pd.DataFrame({'region': ['East', 'East', 'West'], 'sales': [10, 20, 5]})`. Running `df.groupby('region')['sales'].sum()` gives East 30 and West 5 currency units. The check is 30 + 5 = 35, matching the input sum 10 + 20 + 5. Each output entry is a regional total, not an individual sale.

Watch the distinction: `groupby` does not decide what “sales” should mean. Choosing `mean()` instead of `sum()` changes East's result from 30 total units to 15 units per row.

Reference: [1]

Google Data Analytics Capstone: Complete a Case Study

55. Frame a case study brief

A case study begins with a decision that a stakeholder could actually make. Specify the population, the unit of each record, the outcome, and the comparison before calculating anything. For delivery performance, an order-level table needs a unique order ID, branch, promised date, and actual delivery evidence. Define “on time” as actual delivery no later than the promise, using consistent time zones and date rules. Decide which orders enter the denominator: delivered orders with a known delivery time are measurable, while unresolved delivery records need a separate status. Comparing branches also requires attention to order mix and small samples. State what evidence would lead to a practical action, such as checking timestamp capture or investigating a route. This framing gives the cleaning and analysis steps a purpose and makes the case study understandable to a reviewer.

Worked example: A fictional manager asks whether branches A and B differ in on-time delivery. Define one row per order and, for this analysis, include delivered orders with known delivery timestamps. An order is on time when its timestamp is no later than its promised deadline. Compute each branch's on-time count divided by its eligible delivered-order count. Keep unresolved orders visible separately before comparing rates.

Watch the distinction: Counting item rows as orders would change the denominator for multi-item purchases. Confirm order-level grain and a unique order ID before computing delivery rates.

Reference: [1]

56. Audit the case data

Audit the case data against the brief before producing a branch comparison. Confirm that each row is one order, inspect order IDs for repeats, and compare repeated rows field by field. An exact copied row can be removed with its reason recorded; two different events sharing an order ID require investigation instead. Check that branch values and date fields match their definitions, then record missing delivery timestamps as exceptions. Do not convert an absent timestamp into a late delivery, because the missing value does not establish when or whether delivery occurred. Preserve raw data and document each transformation so the final table can be reconciled with the extract. The exception log should contain the affected order, the issue, the action, and its impact on the metric. That makes the later rate reproducible and prevents cleaning choices from disappearing inside a spreadsheet or script.

Worked example: The extract has seven rows: orders 1–6 plus an exact copy of order 3. Removing that copy leaves six unique order rows, three assigned to A and three to B. Order 6 has no delivery timestamp, so retain it with an unresolved flag. The log records one duplicate removed and one unresolved timestamp; five orders currently have assessable delivery times.

Watch the distinction: Dropping every row with a missing delivery timestamp hides the data-quality problem and can bias the branch comparison. Retain and flag the unresolved order.

Reference: [1]

57. Analyze the case result

Calculate rates only after fixing the eligible population and the meaning of the outcome. Here, five orders have assessable delivery times: three in A and two in B. Compute each branch's on-time numerator from those same eligible rows. Show the counts with the percentages because one order can move a small sample substantially. Then test the unresolved B order as a sensitivity analysis, explicitly assuming it is eventually confirmed delivered. If it was late, B's numerator stays one while its denominator rises to three. If it was on time, both rise. If the order was never delivered, it remains outside this delivered-order rate and may require a separate nondelivery measure. The scenario bounds describe possible interpretations; they do not predict the missing result. This is a stronger analysis than ranking branches by a single incomplete percentage.

Worked example: A has two on-time orders among three assessed deliveries: 2 ÷ 3 ≈ 66.7%. B has one among two: 1 ÷ 2 = 50%. If B's unresolved third order is confirmed delivered late, B becomes 1 ÷ 3 ≈ 33.3%; if confirmed delivered on time, B becomes 2 ÷ 3 ≈ 66.7%. Thus B's ranking depends on the missing fact.

Watch the distinction: Calling A definitively better from 66.7% versus 50% ignores B's unresolved order and tiny denominator. Show the conditional outcomes before drawing a conclusion.

Reference: [1]

58. Present the case decision

Present a case study so another person can follow the decision from question to evidence. Lead with the order-level definition and the observed branch counts, then show the cleaning choice that affected them. A simple labeled comparison should distinguish assessable orders from the unresolved B order. State the practical implication in proportion to the evidence: the current figures justify checking data capture and monitoring more orders, not declaring one branch inherently better. Recommend an owner and a next check, such as finding order 6's delivery evidence and rerunning the same rate. In a portfolio, include a compact methods note with the input fields, duplicate rule, metric formula, and sensitivity result. A reviewer should be able to reproduce the arithmetic and understand what remains uncertain. Keep a claim of improved operations separate from a proposal to improve measurement.

Worked example: A has 2/3 on time, or 66.7%; B has 1/2 assessed on time, or 50%. One exact duplicate was removed, and B's third order lacks a delivery timestamp. If that order proves delivered late, B is 33.3%; if on time, 66.7%. Recommend resolving the timestamp and observing a larger period before judging branch performance; show these steps in the portfolio narrative.

Watch the distinction: A polished chart cannot compensate for an unstated excluded record. Explain the unresolved order beside the branch rates and make the next verification explicit.

Reference: [1]

Accelerate Your Job Search with AI

59. Match skills to evidence

AI can help compare a job description with a candidate's work, but the evidence must come from real artifacts. Extract the requested skills, map each to a project, and record what the project actually demonstrates. A SQL cleaning case may show deduplication and validation; a Tableau dashboard may show visualization and stakeholder communication. Describe the task, action, and result without inventing business impact or implying paid experience when the work was a practice project. Ask an AI tool to draft a concise summary from those verified facts, then inspect each noun, tool, number, and outcome against the artifacts. Remove confidential data before sharing material with a tool. Where evidence is weak, identify a skill gap or make a new work sample instead of stretching a claim. The Google Data Analytics Professional Certificate includes a job-search course, while the candidate remains responsible for application accuracy.

Worked example: A role requests SQL, Tableau, and data cleaning. The candidate has one SQL case that documents duplicate removal and validation, plus one Tableau dashboard with labeled delivery rates. Those two artifacts support all three skill terms. A truthful summary is: “Built a SQL cleaning case and a Tableau delivery dashboard using documented checks and rate definitions.” Verify the wording against both files before applying.

Watch the distinction: AI may turn a practice dashboard into “production impact” or claim a tool was used when it was not. Check every experience and outcome against the work.

Reference: [1]

60. Organize and rehearse a search

A job-search tracker should make the next action visible without relying on memory. Give each application its own row with employer, role, submission date, status, evidence used, and planned follow-up date. Update statuses from actual events, not assumptions. AI can suggest interview questions and critique an answer, but rehearse with verifiable examples and correct any confident overstatement it introduces. For a case study, explain the metric denominator, the cleaning decision, and the unresolved record before discussing implications. That demonstrates judgment as well as tool use. Keep sensitive personal details out of shared prompts when they are unnecessary. Review the tracker regularly so a follow-up is timely and tailored to the role; do not treat the count of applications as proof of search quality. An accurate record also prevents accidentally presenting different versions of the same project to different employers.

Worked example: Record application A as submitted October 1, awaiting response, with a planned October 8 follow-up; record B as submitted October 5, interview invited, with its own next step. In rehearsal, say branch A was 2/3 ≈ 66.7% on time and B was 1/2 = 50% among assessed deliveries. B's unresolved order could change its rate; reject any AI-drafted claim that branch caused the difference.

Watch the distinction: A fluent interview answer can still be false. Do not describe the small branch comparison as a proven causal result or omit the unresolved order.

Reference: [1]

Official references and further reading

Google Data Analytics Professional Certificate | Coursera

All study guides

Foundations: Data, Data, Everywhere

1. From records to a useful finding

2. The analysis process

3. The analyst's role

4. What a field represents

5. Tools across a data workflow

6. Counts need context

Ask Questions to Make Data-Driven Decisions

7. Make the question measurable

8. Map stakeholder needs

9. Break a problem into parts

10. Define the metric's denominator

11. Structure a spreadsheet table

12. Compare feasible actions

Prepare Data for Exploration

13. Choose data that fits the question

14. Find the unit of observation

15. Judge a sample

16. Record formats and metadata

17. Keys and related tables

18. Privacy in data preparation

Process Data from Dirty to Clean

19. Profile before cleaning

20. Keep missing distinct from zero

21. Identify true duplicates

22. Standardize text safely

23. Convert formats with a known convention

24. Validate plausible values

25. Check referential integrity

26. Verify spreadsheet formulas

27. Reconcile a cleaning run

Analyze Data to Answer Questions

28. Filter to the intended population

29. Calculate with spreadsheet criteria

30. Control copied references

31. Weight grouped averages correctly

32. Aggregate with SQL

33. Prevent join double-counting

34. Understand SQL nulls

35. Compare rates across time

36. Association and causation

Share Data Through the Art of Visualization

37. Match chart to question

38. Use honest scales

39. Show the distribution

40. Dimensions and measures in Tableau

41. Keep dashboard filters coherent

42. Make findings accessible

43. Build an evidence-led story

44. Present a bounded recommendation

Introduction to Data Analysis Using Python

45. Python values and types

46. Work with strings

47. Lists and tuples

48. Dictionaries and sets

49. Loops over records

50. Conditions and missing values

51. Reusable functions

52. Read a CSV deliberately

53. Inspect data with pandas

54. Group data in pandas

Google Data Analytics Capstone: Complete a Case Study

55. Frame a case study brief

56. Audit the case data

57. Analyze the case result

58. Present the case decision

Accelerate Your Job Search with AI

59. Match skills to evidence

60. Organize and rehearse a search

Official references and further reading

Frequently Asked Questions

Related Study Guides

Certificate in ESG Investing (CFA Institute)

Certificate in Investment Performance Measurement (CIPM)

Certified Data Privacy Solutions Engineer (CDPSE)