ICDL Data Analytics (ICDL) cheat sheet

Four analytics types in increasing complexity: descriptive (what happened), diagnostic (why), predictive (what will happen), prescriptive (what should be done).

Data types: nominal (unordered labels), ordinal (ordered ranks), interval (equal spacing, no true zero), ratio (equal spacing with true zero).

Population = entire set of interest; sample = subset drawn from it. Statistics computed on samples estimate population parameters.

Mean is sensitive to outliers; median is robust; mode suits categorical data. Use median for skewed distributions.

Standard deviation measures average spread around the mean; variance is its square. Larger values indicate greater dispersion.

Correlation coefficient r ranges from -1 to +1; r near 0 means no linear relationship. Correlation does not imply causation.

Data quality dimensions: accuracy, completeness, consistency, timeliness, validity, uniqueness.

Structured data fits rows and columns; semi-structured uses tags (JSON, XML); unstructured includes free text, images, and audio.

Chart selection rule: compare categories with bar charts, show trends over time with line charts, show parts of a whole with pie or stacked charts, show relationships with scatter plots.

Pie charts work only for a small number of categories summing to a meaningful whole; prefer bar charts for precise comparison.

Scatter plots reveal correlation and outliers; add a trendline to visualize linear fit direction and strength.

Histograms bin continuous data to show distribution shape (normal, skewed, bimodal); bar charts compare discrete categories.

Pivot tables aggregate large datasets by rows, columns, values, and filters without altering the source data.

Sorting reorders records; filtering hides rows that fail criteria without deleting them.

Outlier detection rules: values beyond 1.5 x IQR from quartiles, or beyond 3 standard deviations from the mean.

Avoid misleading visuals: truncated y-axes exaggerate differences, and inconsistent scales distort trend perception.

Relational database keys: primary key uniquely identifies a row; foreign key references a primary key in another table.

Normalization reduces redundancy by organizing tables; denormalization may be used deliberately to speed reporting queries.

SQL core clauses execute logically as FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY; WHERE filters rows, HAVING filters groups.

Aggregate functions (SUM, COUNT, AVG, MIN, MAX) collapse rows; GROUP BY defines the aggregation groups.

Data cleaning steps: remove duplicates, standardize formats, handle missing values, correct invalid entries, validate ranges.

Dashboard design: place the most important KPI top-left, limit metrics per view, label axes and units, and show data refresh dates.

Report types: operational (daily detail), tactical (periodic summaries), strategic (long-term trend and KPI views).

Backup and version control protect data integrity; maintain an audit trail of changes for traceability.

KPI criteria: linked to a strategic objective, measurable, time-bound, and actionable by the decision owner.

SMART goals: specific, measurable, achievable, relevant, time-bound.

A/B testing compares a control group against a variant; statistical significance (typically p < 0.05) indicates the difference is unlikely due to chance.

Confirmation bias favors evidence supporting existing beliefs; base decisions on pre-defined metrics rather than post-hoc selection.

Sample size affects confidence: small samples produce wider confidence intervals and less reliable conclusions.

Leading indicators predict future outcomes; lagging indicators confirm results already achieved.

Cost-benefit analysis compares total expected benefits against total costs; ROI = (benefit - cost) / cost x 100%.

Data storytelling structure: state the question, present the evidence, interpret the finding, recommend an action.

Four analytics types in increasing complexity: descriptive (what happened), diagnostic (why), predictive (what will happen), prescriptive (what should be done).

Data types: nominal (unordered labels), ordinal (ordered ranks), interval (equal spacing, no true zero), ratio (equal spacing with true zero).

Population = entire set of interest; sample = subset drawn from it. Statistics computed on samples estimate population parameters.

Mean is sensitive to outliers; median is robust; mode suits categorical data. Use median for skewed distributions.

Standard deviation measures average spread around the mean; variance is its square. Larger values indicate greater dispersion.

Correlation coefficient r ranges from -1 to +1; r near 0 means no linear relationship. Correlation does not imply causation.

Data quality dimensions: accuracy, completeness, consistency, timeliness, validity, uniqueness.

Structured data fits rows and columns; semi-structured uses tags (JSON, XML); unstructured includes free text, images, and audio.

Chart selection rule: compare categories with bar charts, show trends over time with line charts, show parts of a whole with pie or stacked charts, show relationships with scatter plots.

Pie charts work only for a small number of categories summing to a meaningful whole; prefer bar charts for precise comparison.

Scatter plots reveal correlation and outliers; add a trendline to visualize linear fit direction and strength.

Histograms bin continuous data to show distribution shape (normal, skewed, bimodal); bar charts compare discrete categories.

Pivot tables aggregate large datasets by rows, columns, values, and filters without altering the source data.

Sorting reorders records; filtering hides rows that fail criteria without deleting them.

Outlier detection rules: values beyond 1.5 x IQR from quartiles, or beyond 3 standard deviations from the mean.

Avoid misleading visuals: truncated y-axes exaggerate differences, and inconsistent scales distort trend perception.

Relational database keys: primary key uniquely identifies a row; foreign key references a primary key in another table.

Normalization reduces redundancy by organizing tables; denormalization may be used deliberately to speed reporting queries.

SQL core clauses execute logically as FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY; WHERE filters rows, HAVING filters groups.

Aggregate functions (SUM, COUNT, AVG, MIN, MAX) collapse rows; GROUP BY defines the aggregation groups.

Data cleaning steps: remove duplicates, standardize formats, handle missing values, correct invalid entries, validate ranges.

Dashboard design: place the most important KPI top-left, limit metrics per view, label axes and units, and show data refresh dates.

Report types: operational (daily detail), tactical (periodic summaries), strategic (long-term trend and KPI views).

Backup and version control protect data integrity; maintain an audit trail of changes for traceability.

KPI criteria: linked to a strategic objective, measurable, time-bound, and actionable by the decision owner.

SMART goals: specific, measurable, achievable, relevant, time-bound.

A/B testing compares a control group against a variant; statistical significance (typically p < 0.05) indicates the difference is unlikely due to chance.

Confirmation bias favors evidence supporting existing beliefs; base decisions on pre-defined metrics rather than post-hoc selection.

Sample size affects confidence: small samples produce wider confidence intervals and less reliable conclusions.

Leading indicators predict future outcomes; lagging indicators confirm results already achieved.

Cost-benefit analysis compares total expected benefits against total costs; ROI = (benefit - cost) / cost x 100%.

Data storytelling structure: state the question, present the evidence, interpret the finding, recommend an action.