Data Analytics Cheat Sheet

Data Analytics Cheat Sheet: KPIs, SQL, Excel, Dashboards & Examples

Data analytics is the process of examining data to identify patterns, understand performance, answer business questions, and support better decisions.

Organizations use data analytics to measure sales, understand customers, improve marketing campaigns, forecast demand, reduce operational costs, and identify business opportunities.

This cheat sheet explains the core concepts, analytical methods, common KPIs, essential tools, visualization techniques, and practical examples that beginners and professionals can use as a quick reference.

1. What Is Data Analytics?

Data analytics involves collecting, preparing, examining, and interpreting data to answer questions or solve problems.

For example, a marketing team might analyze campaign data to understand:

• Which campaign generated the most conversions.

• Which channel delivered the lowest cost per acquisition.

• Which audience generated the highest revenue.

• Why conversion rates declined.

• Where the budget should be allocated next.

The purpose is not simply to produce reports. It is to convert data into actionable insights.

Data Analytics vs. Data Science

The two disciplines overlap considerably.

2. Types of Data Analytics

Four commonly used categories describe different analytical objectives.

Descriptive Analytics

Question: What happened?

Descriptive analytics summarizes historical data.

Examples:

• Monthly sales reports.

• Website traffic summaries.

• Campaign impressions and clicks.

• Revenue by product category.

Diagnostic Analytics

Question: Why did it happen?

Diagnostic analytics investigates the causes or contributing factors behind an observed result.

Examples:

• Investigating a decline in conversion rate.

• Comparing campaign performance across audiences.

• Identifying which products contributed to falling revenue.

Diagnostic analysis can identify plausible explanations, but causal claims may require experiments or other appropriate study designs.

Predictive Analytics

Question: What might happen next?

Predictive analytics uses historical data and models to estimate future outcomes.

Examples:

• Forecasting demand.

• Estimating customer churn risk.

• Predicting sales volume.

Prescriptive Analytics

Question: What action should we take?

Prescriptive analytics evaluates possible actions using models, constraints, business rules, or optimization methods.

Examples:

• Recommending budget allocation.

• Optimizing delivery routes.

• Selecting inventory levels.

3. The Data Analytics Workflow

A practical analytics workflow includes the following stages.

1. Define the business question. Establish the decision the analysis must support.

2. Identify the data sources. Determine which systems contain the necessary information.

3. Collect the data. Retrieve relevant records from databases, spreadsheets, APIs, or platforms.

4. Clean the data. Address missing values, duplicates, inconsistent formats, and invalid records.

5. Transform the data. Standardize fields, combine datasets, and calculate derived metrics.

6. Analyze the data. Use queries, statistical summaries, comparisons, or models.

7. Visualize the results. Present important findings clearly.

8. Interpret the findings. Explain patterns, limitations, and potential reasons.

9. Recommend actions. Connect the evidence to a business decision.

10. Monitor outcomes. Check whether the recommended actions improve performance.

The workflow is iterative. Analysis may reveal that more data or a different metric is required.

4. Essential Data Analytics Terminology

Understanding granularity is especially important. A dataset containing one row per order is different from one containing one row per order item.

5. Essential Statistical Concepts

Statistics helps analysts summarize data, understand variability, and assess relationships.

Mean

The arithmetic average.

[
\text{Mean}=\frac{\sum x_i}{n}
]

Example: The average of 10, 20, and 30 is 20.

Median

The middle value in sorted data, or the average of the two middle values when there is an even number of observations.

The median is often less affected by extreme values than the mean.

Mode

The most frequently occurring value or category.

Range

[
\text{Range}=\text{Maximum}-\text{Minimum}
]

Percentage Change

[
\text{Percentage Change}
=\frac{\text{New Value}-\text{Old Value}}
{\text{Old Value}}\times100
]

If sales increase from 100 to 120:

[
\frac{120-100}{100}\times100=20%
]

The formula requires a nonzero old value.

Weighted Average

A weighted average accounts for the relative contribution of each observation.

[
\text{Weighted Average}
=\frac{\sum w_i x_i}{\sum w_i}
]

For example, overall conversion rate should generally be calculated as total conversions divided by total eligible sessions, rather than by simply averaging the conversion rates of different campaigns.

6. Essential Business KPIs

A KPI measures progress toward an important business objective.

Sales and Revenue

Marketing

The denominator used for conversion rate depends on the defined conversion event and measurement setup.

Website Analytics

Important: Define each KPI before calculating it. Platforms may use different attribution, counting, session, and conversion rules.

7. Excel for Data Analytics

Excel is widely used for data cleaning, calculations, summaries, and reporting.

Useful Functions

Function availability and exact syntax may vary by spreadsheet application and version.

Useful Excel Features

• Sort and filter.

• Conditional formatting.

• PivotTables.

• Charts.

• Data validation.

• Remove Duplicates.

• Text to Columns.

• Power Query.

• Lookup functions.

• Structured tables.

PivotTables

PivotTables summarize large datasets without requiring a separate formula for every group.

For example, a PivotTable can summarize:

• Rows: Campaign.

• Columns: Month.

• Values: Spend and conversions.

• Filters: Channel or region.

This makes it easier to compare performance across dimensions.

8. SQL for Data Analytics

SQL allows analysts to retrieve and summarize data from relational databases.

Filter Records

SELECT *
FROM Campaigns
WHERE spend > 1000;

Aggregate Metrics

SELECT
    campaign_name,
    SUM(spend) AS total_spend,
    SUM(conversions) AS total_conversions
FROM Campaigns
GROUP BY campaign_name;

Calculate Cost per Conversion

SELECT
    campaign_name,
    SUM(spend) / NULLIF(SUM(conversions), 0)
        AS cost_per_conversion
FROM Campaigns
GROUP BY campaign_name;

NULLIF() prevents division by zero by returning NULL when the denominator is zero.

Compare Categories

SELECT
    channel,
    COUNT(*) AS record_count,
    AVG(order_value) AS average_order_value
FROM Orders
GROUP BY channel
ORDER BY average_order_value DESC;

SQL is especially useful when datasets are too large or complex for manual spreadsheet analysis.

9. Data Visualization Cheat Sheet

Choose charts according to the question being answered.

Visualization Best Practices

• Use a meaningful title.

• Label axes and units.

• Choose an appropriate scale.

• Show relevant date ranges.

• Avoid unnecessary decoration.

• Use consistent metric definitions.

• Make comparisons easy to understand.

• Highlight important changes without distorting the data.

• Include context when values need interpretation.

A chart should help answer a question, not merely make a report look attractive.

10. Dashboards and Reporting

A dashboard provides a consolidated view of selected metrics and trends.

Common Dashboard Components

• KPI summary cards.

• Time-series charts.

• Category comparisons.

• Filters and date selectors.

• Detailed tables.

• Target-versus-actual comparisons.

• Alerts or annotations.

• Drill-down views.

Dashboard Design Checklist

1. Identify the audience.

2. Define the decisions the dashboard should support.

3. Select a small set of relevant KPIs.

4. Confirm data accuracy.

5. Use clear metric names and definitions.

6. Provide useful date and category filters.

7. Show comparisons with targets or previous periods where appropriate.

8. Make the dashboard readable on the intended screen.

9. Explain important limitations.

10. Establish a refresh schedule.

A dashboard for an executive may prioritize overall performance, while an operational dashboard may emphasize detailed metrics and exceptions.

11. Power BI and Tableau

Power BI and Tableau are business intelligence platforms used to connect data, create visualizations, and build interactive dashboards.

Power BI

Common capabilities include:

• Connecting to data sources.

• Transforming data with Power Query.

• Building data models.

• Creating measures using DAX.

• Designing reports and dashboards.

• Sharing reports through supported services.

Tableau

Common capabilities include:

• Connecting to different data sources.

• Building interactive visualizations.

• Exploring data through worksheets.

• Combining views into dashboards.

• Creating calculated fields.

• Sharing visual analytics.

Choosing a BI Tool

Consider:

• Existing company technology.

• Data source compatibility.

• Licensing and total cost.

• Sharing and governance requirements.

• Data model complexity.

• Team skills.

• Refresh and performance needs.

Neither tool is universally better. The right choice depends on the environment and requirements.

12. Data Cleaning and Quality Checks

Poor-quality data can lead to misleading conclusions even when calculations are correct.

Common Data Quality Problems

• Missing values.

• Duplicate records.

• Incorrect data types.

• Inconsistent date formats.

• Different spellings for the same category.

• Invalid numeric values.

• Incorrect joins.

• Unexpected changes in row counts.

• Different definitions of the same KPI.

Quality Checklist

• Confirm the expected number of records.

• Check missing values.

• Identify duplicates using the correct business key.

• Validate date ranges.

• Check minimum and maximum values.

• Compare totals against a trusted source.

• Confirm joins do not create unintended duplicate rows.

• Document any assumptions and transformations.

Do not automatically remove every outlier or replace every missing value. Investigate what the values mean before changing them.

13. ETL and ELT

ETL and ELT describe common data integration patterns.

ETL: Extract, Transform, Load

1. Extract data from source systems.

2. Transform it into the required format.

3. Load it into the destination.

ELT: Extract, Load, Transform

1. Extract data.

2. Load it into the destination.

3. Transform it within the destination environment.

ETL may be suitable when data needs transformation before loading. ELT is common in modern analytical platforms that can process large datasets after ingestion.

Both approaches depend on data quality, security, monitoring, and reliable execution.

14. Data Analytics Example: Marketing Campaign Report

Suppose a business runs three campaigns.

Calculate CPC

[
CPC=\frac{\text{Spend}}{\text{Clicks}}
]

• Campaign A: ₹10,000 ÷ 2,000 = ₹5.

• Campaign B: ₹15,000 ÷ 2,500 = ₹6.

• Campaign C: ₹8,000 ÷ 1,000 = ₹8.

Calculate Cost per Conversion

[
CPA=\frac{\text{Spend}}{\text{Conversions}}
]

• Campaign A: ₹10,000 ÷ 100 = ₹100.

• Campaign B: ₹15,000 ÷ 125 = ₹120.

• Campaign C: ₹8,000 ÷ 40 = ₹200.

Calculate Conversion Rate

Assuming clicks are the denominator:

[
CVR=\frac{\text{Conversions}}{\text{Clicks}}\times100
]

• Campaign A: 5%.

• Campaign B: 5%.

• Campaign C: 4%.

What Can We Conclude?

Campaign A has the lowest cost per conversion among the three, while Campaign C has the highest. Campaigns A and B have equal click-to-conversion rates, but different CPC and CPA values.

However, lower CPA alone does not prove that Campaign A is the most profitable. Revenue per conversion, lead quality, attribution, customer lifetime value, and campaign objectives may also matter.

15. A/B Testing and Experimentation

A/B testing compares two or more versions of an experience to evaluate their effect on a defined outcome.

Example:

• Version A: Existing landing page.

• Version B: Landing page with a revised headline.

• Primary metric: Conversion rate.

Basic Experiment Workflow

1. Define the hypothesis.

2. Select the primary outcome metric.

3. Determine how users will be assigned.

4. Estimate the required sample size.

5. Run the experiment for an appropriate period.

6. Check data quality and experiment integrity.

7. Analyze the results.

8. Consider effect size and uncertainty.

9. Make a decision based on the evidence.

Avoid stopping an experiment simply because an early result looks favorable. Sample size, duration, seasonality, and the chosen analysis method affect the reliability of conclusions.

16. Common Data Analytics Mistakes

• Reporting metrics without defining them.

• Comparing periods with different lengths or conditions.

• Ignoring seasonality.

• Using averages when distributions are highly skewed.

• Treating correlation as causation.

• Creating charts with misleading scales.

• Joining tables incorrectly.

• Ignoring missing or duplicated data.

• Using the wrong denominator for a KPI.

• Reporting results without business context.

• Confusing statistical significance with practical importance.

• Failing to verify the source of the data.

A strong analyst checks both the calculations and the assumptions behind them.

17. Data Analytics Interview Questions

What is data analytics?

The process of examining data to generate insights, answer questions, and support decisions.

What are the four types of analytics?

Descriptive, diagnostic, predictive, and prescriptive analytics.

What is a KPI?

A key performance indicator that measures progress toward an important objective.

What is the difference between a dimension and a measure?

A dimension categorizes data, while a measure represents a value that can be analyzed or aggregated.

What is ETL?

Extract, Transform, Load: a process for moving and preparing data.

What is a dashboard?

A visual interface that summarizes selected metrics and information.

What is data cleaning?

The process of identifying and addressing data-quality problems.

What is a PivotTable?

A spreadsheet feature that summarizes data by categories and measures.

What is the difference between a database and a data warehouse?

A database commonly supports operational data processing, while a data warehouse is designed primarily for analytical workloads.

What is A/B testing?

An experiment that compares versions of an experience to estimate their effect on a selected outcome.

What is data granularity?

The level of detail represented by each record in a dataset.

Why is data visualization important?

It helps communicate patterns, comparisons, distributions, and changes clearly.

18. Data Analytics Quick Reference

19. Data Analytics Learning Checklist

Fundamentals

• Explain the four types of analytics.

• Understand dimensions, measures, and KPIs.

• Calculate averages, percentage changes, and weighted averages.

• Understand correlation and its limitations.

Tools

• Use Excel formulas and PivotTables.

• Write SQL queries with filters and aggregations.

• Create visualizations.

• Build a basic Power BI or Tableau report.

Analysis

• Clean and validate data.

• Calculate KPIs correctly.

• Compare performance across categories and periods.

• Interpret findings in business context.

• Document assumptions and limitations.

Communication

• Explain findings in plain language.

• Connect insights to business objectives.

• Make evidence-based recommendations.

• Monitor the results of decisions.

Conclusion

Data analytics helps organizations turn raw data into meaningful information for decision-making. Mastering data preparation, statistics, SQL, Excel, visualization, KPI design, and reporting provides a strong foundation for an analytics career.

Practice with real or sample datasets, start with clear business questions, and validate your findings before presenting recommendations. The most valuable analysis is not simply a collection of charts—it is reliable evidence that helps someone make a better decision.