Data Management and Spreadsheet Applications

Introduction and Context

Media organisations depend on data to plan, produce, distribute and evaluate content. A broadcaster records programme costs, transmission times and audience ratings; a newspaper monitors circulation, advertising revenue and digital subscriptions; an agency manages client budgets, campaign impressions and conversion results. Although these activities differ, they share a requirement: data must be organised accurately before it can support managerial judgement.

Spreadsheets are among the most accessible tools for this purpose. They allow users to store tabular data, perform calculations, test scenarios, summarise records and communicate findings visually. However, a spreadsheet is not automatically reliable merely because it produces a number. Poorly designed workbooks can contain duplicated records, inconsistent labels, hidden formulas, incorrect references and unsupported assumptions. Data management therefore combines technical skills with principles of accuracy, documentation, validation, security and interpretation.

This chapter explains how media professionals can use spreadsheets and basic database concepts to manage budgets, audience records and campaign-planning data. It also introduces formulas, functions, pivot tables, charts, data validation and governance practices.

Learning Objectives

By the end of this chapter, students should be able to:

1. distinguish data, information, records, fields and databases;
2. design a structured spreadsheet for media-management tasks;
3. use formulas, relative and absolute references, and common functions;
4. validate, clean and audit media data;
5. summarise records using pivot tables and appropriate charts;
6. apply spreadsheet tools to budgeting, audience analysis and campaign planning;
7. recognise limitations, ethical risks and governance requirements;
8. evaluate spreadsheet outputs rather than accepting them uncritically.

1. Data, Information and the Media Organisation

Data are recorded observations, measurements or descriptions. Examples include a programme code, an invoice amount, a viewer age band or the number of impressions delivered. Information is data that has been processed and interpreted so that it supports a purpose—for example, discovering that a campaign exceeded its planned reach at a lower-than-expected cost.

A spreadsheet normally represents data in a table. A field is a column describing one attribute, such as Campaign ID or Cost. A record is one complete row describing an entity or event, such as one invoice or one audience response. A dataset is a related collection of records.

Good media datasets follow several principles:

• each row represents one clearly defined unit;
• each column contains one type of variable;
• column headings are unique and meaningful;
• values use consistent formats and units;
• blank, unknown and not-applicable values are distinguished;
• calculations are separated from source data where practical;
• the dataset has an owner, date and description.

A table containing a campaign’s daily delivery should not place several dates in one cell or combine impressions, clicks and cost in a single text field. Such practices make sorting, filtering and analysis unreliable.

Structured and Unstructured Data

Structured data fit a defined schema, such as rows and columns. They are well suited to spreadsheets and relational databases. Semi-structured data, such as JSON exports from an advertising platform, contain labels but may have variable structures. Unstructured data include video, audio, images and free-text comments. A media manager may store all three types, but a spreadsheet generally contains structured extracts or descriptive metadata rather than the media files themselves.

2. Spreadsheet Architecture

A professional workbook should be designed as a small information system rather than as an improvised page. A useful architecture contains:

1. Read Me or Control sheet: purpose, owner, update date, definitions and assumptions;
2. Raw Data sheet: imported or entered records retained with minimal alteration;
3. Clean Data sheet: standardised fields and documented transformations;
4. Calculations sheet: formulas, targets and scenario assumptions;
5. Summary or Dashboard sheet: tables, pivot tables and charts for decision-makers.

The separation between raw data and calculations improves traceability. If an imported advertising report is overwritten, the organisation may be unable to reproduce the analysis. A file naming convention such as Campaign_Performance_2025-03-31_v02.xlsx also supports version control.

Data Types and Formatting

Common spreadsheet data types include text, numbers, dates, times, Boolean values and errors. Formatting changes appearance, not necessarily the underlying value. For example, a cell displayed as $1,000 may contain the numeric value 1000. Dates require special care because systems may interpret 03/04/2025 as 3 April or March 4.

Percentages should be stored consistently. A value of 0.15 displayed as 15% is mathematically different from the text 15%, which may not calculate correctly. Currency should include a stated currency and, where relevant, an exchange-rate assumption.

3. Formulas, References and Functions

A formula begins with an equals sign. Operators include addition, subtraction, multiplication and division. Parentheses should be used to make the intended order explicit. For example, total campaign cost can be calculated as:

If media cost is 12,000, production cost is 4,000 and agency fee is 2,000, total cost is 18,000 currency units. The result is meaningful only if all amounts refer to the same campaign, period and currency.

Relative and Absolute References

A relative reference changes when a formula is copied. If cell E2 contains =C2D2, copying it to E3 normally produces =C3D3. An absolute reference remains fixed, commonly written as $B$1. It is useful when every row uses one tax rate, exchange rate or target.

A mixed reference fixes either the row or column, such as $B2 or B$2. Correct reference design prevents silent errors when a model is extended.

Common Functions

Functions should express a documented business rule. For example, a budget status might use =IF(Actual>Budget,"Over budget","Within budget"). Users should confirm whether equality belongs in the first or second category and whether a missing value should be treated as zero. It is usually unsafe to replace every error with zero, because this can conceal missing or invalid data.

Media Performance Metrics

Several media metrics use straightforward ratios. Cost per thousand impressions (CPM) is:

Cost is measured in currency and impressions are counted exposures. The formula assumes impressions are positive and that cost and impressions refer to the same placement and period.

If cost is 9,000 and impressions are 600,000:

The CPM is 15 currency units per thousand impressions.

Click-through rate (CTR) is:

For 7,500 clicks and 600,000 impressions, CTR = 1.25%. The denominator must be non-zero, and clicks should be defined consistently across platforms.

Return on advertising spend (ROAS) is:

If attributed revenue is 36,000 and advertising cost is 9,000, ROAS = 4. This is a ratio, not a percentage, and it does not by itself prove that advertising caused all reported revenue. Attribution rules and omitted costs must be examined.

4. Data Quality, Validation and Cleaning

Data quality is multidimensional. Accuracy means values represent reality; completeness means required values are present; consistency means the same concepts use the same formats; validity means values conform to permitted rules; timeliness means data are current enough for the decision; and uniqueness means records are not improperly duplicated.

Validation Techniques

Spreadsheet validation can restrict a field to a list, a date range, a whole number or a decimal interval. A Campaign Status field might allow only Planned, Live, Paused or Completed. A data-entry form can also require a Campaign ID and prevent impossible negative impressions.

Useful checks include:

• duplicate detection using a unique record identifier;
• conditional formatting for blank required cells;
• reconciliation of detailed totals to invoice or platform totals;
• range checks for percentages between 0% and 100% where appropriate;
• date checks ensuring an end date is not earlier than a start date;
• lookup checks confirming that codes exist in a reference table;
• reasonableness checks comparing results with historical or planned values.

Cleaning should preserve the original source. Standardisation may involve trimming spaces, correcting case, mapping alternative labels to one approved label and converting text numbers to numbers. However, an alteration should be logged when it affects interpretation.

Missing Data and Outliers

A blank may mean unknown, not collected, not applicable or accidentally omitted. These meanings should not be combined. Replacing missing audience data with zero falsely implies no audience. A separate missing-value code or status field is often safer.

An outlier is an unusually large or small observation. It may indicate a genuine major event, a data-entry error or a different measurement definition. It should be investigated, not automatically deleted. Winsorising, imputation or exclusion requires a defensible method and documentation.

5. Basic Database Concepts

A database is an organised collection of data managed through defined structures and rules. In a relational database, information is divided into tables connected through keys. A primary key uniquely identifies each record, while a foreign key refers to a record in another table.

For a campaign system, tables might include:

• Campaign: CampaignID, ClientID, StartDate, EndDate, Objective;
• Client: ClientID, ClientName, Industry;
• Placement: PlacementID, CampaignID, Channel, Cost, Impressions;
• DailyResult: PlacementID, Date, Clicks, Conversions.

This structure avoids repeating client details on every placement record and reduces update anomalies. Normalisation is the process of organising data to reduce unnecessary duplication and dependency problems. A spreadsheet may remain denormalised for reporting convenience, but the underlying logic should still distinguish entities and relationships.

A spreadsheet lookup can imitate a database join by matching a key in one table to a related value in another. The key must be unique where uniqueness is expected. Approximate matching should not be used accidentally for exact campaign codes.

6. Pivot Tables and Analytical Summaries

A pivot table groups and aggregates records without requiring a new formula for every category. Typical areas are:

• Rows: channel, programme, client or month;
• Columns: device, audience segment or campaign status;
• Values: sum of cost, sum of impressions, count of placements or average CTR;
• Filters: date range, region or campaign.

A pivot table is only as reliable as its source data. Blank headings, merged cells, mixed data types and subtotal rows in the source can produce misleading summaries. Refresh procedures should be documented when source data change.

For example, a channel summary can display cost and impressions, then calculate CPM from aggregated totals:

This is generally preferable to averaging channel CPMs when channels have different impression volumes. An unweighted average gives each channel equal influence even when their scales differ.

7. Charts and Communication

Charts convert patterns into visual form, but chart choice should follow the analytical question.

Axes should be labelled, units stated and time periods ordered logically. A truncated axis can exaggerate small differences, while a three-dimensional chart can obscure them. Colour should have a meaning and remain interpretable for users with colour-vision differences. Every dashboard should identify its data period and source.

A chart communicates an interpretation, not merely a dataset. A rising line may reflect genuine growth, a change in measurement or an incomplete reporting period. Analysts should annotate material events and avoid implying causation without evidence.

8. Applied Uses in Media Management

Budgeting

A budget workbook can include planned cost, committed cost, actual cost, variance and status. A common variance definition is:

Under this definition, a positive variance indicates overspending. A percentage variance is:

The denominator must not be zero. If planned cost is 20,000 and actual cost is 18,500, variance = −1,500 and variance percentage = −7.5%, indicating underspending of 7.5% under the stated convention.

A budget model should distinguish approved, committed and paid amounts. It should also record tax treatment, currency, supplier, purchase-order number and approval status. Scenario cells can test the effect of changes in rates or volume, but scenario assumptions must be clearly labelled as estimates.

Audience Records

Audience data may include programme, date, platform, audience segment, reach, frequency and completion rate. Definitions are essential: reach may mean unique users, while impressions may count repeated exposures. A table should not combine these measures under a vague heading such as “views.” Personally identifiable information should be minimised, access controlled and retained only as long as justified.

Campaign Planning

A campaign schedule can connect objectives, audiences, channels, dates, creative versions, budgets and key performance indicators. Conditional formatting can flag placements whose dates overlap incorrectly or whose spend exceeds a threshold. A pivot table can compare planned and delivered impressions by channel, while a line chart can identify delivery gaps over time.

9. Illustrative Case: Northstar Media Campaign Review

Background

Northstar Media, a fictional regional broadcaster, ran a four-week campaign promoting a streaming service. Management needed to compare planned and delivered performance across social, search and display channels.

Problem

The platform exports used different channel labels, two dates were stored as text, and the finance sheet contained a duplicated invoice. Management’s first summary understated total cost and produced an inflated average CPM.

Analysis

The analyst preserved the original exports, created a standard channel lookup, converted dates to a single format and assigned a unique invoice ID. Duplicate checking identified the repeated invoice. The clean table contained one row per placement-day with fields for campaign ID, date, channel, cost, impressions, clicks and conversions.

A pivot table summarised totals by channel. Rather than averaging row-level CPM values, the analyst calculated CPM from total channel cost and total channel impressions. The dashboard included delivery against plan, CTR, cost variance and a time-series chart.

Solution and Outcome

The corrected data showed that social delivered 520,000 impressions at a cost of 6,240, search delivered 180,000 at 5,400, and display delivered 300,000 at 4,200. Total cost was 15,840 and total impressions were 1,000,000, producing an overall CPM of 15.84. Search had the highest channel CPM, but its conversion rate was also strongest; therefore, management did not reduce it solely on the basis of CPM.

The final workbook included a data dictionary, validation rules, a duplicate check, source links and a refresh date. It was circulated with a note explaining that conversions were platform-attributed and should not be interpreted as purely causal effects.

Learning Points

The case demonstrates that cleaning precedes analysis, aggregated ratios must use suitable denominators, and a cheaper exposure is not automatically a better business outcome. It also shows why an auditable workbook should expose assumptions and data limitations.

10. Governance, Ethics and Security

Media data may include customer identifiers, viewing histories, location, demographic attributes and behavioural profiles. Collection and use should follow applicable privacy and data-protection law, organisational policy and the principles of purpose limitation, data minimisation, accuracy, security and retention control. Consent or another lawful basis may be required for personal-data processing.

Access should follow least privilege: users receive only the access needed for their role. Sensitive workbooks should be protected through approved storage, authentication and controlled sharing. Password protection alone is not a complete security strategy. Audit logs, backups and version histories support accountability.

Ethical risks include discriminatory audience targeting, opaque profiling, manipulation through selective metrics and re-identification of supposedly anonymous data. Small audience segments may be identifiable when several variables are combined. Analysts should aggregate or suppress results where necessary and avoid reporting sensitive characteristics without a legitimate purpose.

AI tools can assist with formula suggestions, classification, anomaly detection and natural-language summaries. They can also introduce fabricated results, expose confidential data or encode bias. Generated formulas and interpretations must be tested against known examples, and confidential media or audience data should not be uploaded to an unapproved service.

11. Limitations and Best Practices

Spreadsheets are flexible but become fragile as complexity, user numbers and data volume increase. Common weaknesses include broken links, inconsistent versions, hidden logic, accidental overwriting, weak access control and difficulty reproducing transformations. A relational database or specialised business-intelligence platform may be more suitable when many users need concurrent access, when records are numerous, or when transaction-level controls are required.

Best practice includes:

1. define the decision and unit of analysis before building the file;
2. keep source data separate from transformations and outputs;
3. use tables, stable identifiers and controlled vocabularies;
4. validate inputs and test formulas with known cases;
5. document definitions, assumptions, units and refresh dates;
6. use checks for totals, duplicates and missing values;
7. protect sensitive data and restrict editing;
8. use charts that reflect the question and avoid visual distortion;
9. obtain peer review for important budgets and reports;
10. retain versions so results can be reproduced.

Contemporary Developments

Cloud spreadsheets enable collaboration and near-real-time updating, but simultaneous editing increases the need for permissions, change history and defined ownership. Application programming interfaces can automate imports from advertising and analytics systems; automation should still include schema checks and exception reports. Business-intelligence tools increasingly connect databases, spreadsheets and dashboards, while machine-learning systems identify patterns in audience behaviour. These developments improve speed but do not remove the need for data literacy, governance or professional judgement.

Chapter Summary

Media data become useful information only when they are accurately defined, structured, validated and interpreted. A professional spreadsheet separates source data, transformations, calculations and communication outputs. Formulas and functions support budgeting and performance measurement, while absolute and relative references determine whether copied formulas remain correct. Metrics such as CPM, CTR and ROAS are meaningful only when their definitions, denominators, units and attribution assumptions are clear.

Data validation, cleaning, duplicate checks and reconciliation protect analytical quality. Database concepts—including fields, records, primary keys, foreign keys and normalisation—help media managers design more reliable tables and understand when a spreadsheet is no longer adequate. Pivot tables summarise records efficiently, and charts communicate comparisons, trends and relationships when designed honestly. Finally, privacy, security, fairness, documentation and auditability are essential because media datasets often affect audiences, clients and organisational decisions.

Key Terms

Data: Recorded observations or values.

Information: Processed data interpreted for a purpose.

Field: A column representing one attribute.

Record: A row representing one entity or event.

Dataset: A related collection of records.

Data validation: Rules that restrict or check entered values.

Data cleaning: Correcting, standardising or documenting data problems.

Primary key: A value that uniquely identifies a record.

Foreign key: A value linking one table to a record in another table.

Normalisation: Organising tables to reduce unnecessary duplication.

Relative reference: A cell reference that changes when copied.

Absolute reference: A fixed cell reference, such as $B$1.

Pivot table: An interactive summary of grouped records.

CPM: Cost per thousand impressions.

CTR: Click-through rate.

ROAS: Attributed revenue divided by advertising cost.

Dashboard: A consolidated visual display of important measures.

Data dictionary: Documentation of fields, definitions, formats and rules.

Data governance: Policies and responsibilities controlling data quality, access and use.

Review Questions

Short Answer Questions

1. Distinguish data from information using a media example.
2. What is the difference between a field and a record?
3. Why should raw data be kept separate from calculations?
4. State the formula for CPM and identify its required inputs.
5. What is the purpose of a primary key?

Long Answer Questions

1. Explain how a media organisation can design a reliable budgeting workbook.
2. Discuss the roles of validation, cleaning and reconciliation in audience-data analysis.
3. Explain relative, absolute and mixed spreadsheet references with examples.
4. Compare pivot tables with ordinary formula-based summaries.
5. Discuss ethical and governance issues in managing audience and campaign data.

Analytical / Application Questions

1. A campaign costs 24,000 and generates 1,600,000 impressions. Calculate CPM and explain two assumptions needed to interpret it.
2. A workbook reports a lower average CPM after adding a very small, high-cost placement. Identify possible calculation or denominator problems and propose checks.
3. Design a table structure for a campaign involving several clients, placements and daily results. Identify a suitable primary key and at least one foreign key.

Multiple Choice Questions with Answers

Question 1

Which spreadsheet design best supports auditability?

A. Place raw data, formulas and charts on one unlabelled sheet

B. Separate raw data, calculations and summary outputs

C. Replace source data after every monthly update

D. Use merged cells for every category

Correct Answer: B. Separate raw data, calculations and summary outputs

Question 2

A campaign costs 9,000 and produces 600,000 impressions. What is its CPM?

A. 1.50

B. 6.67

C. 15.00

D. 66.67

Correct Answer: C. 15.00

Question 3

Which function is most appropriate for adding costs only for the Social channel?

A. SUMIF

B. COUNT

C. AVERAGE

D. MAX

Correct Answer: A. SUMIF

Question 4

What is the main purpose of a primary key?

A. To format a table as currency

B. To uniquely identify a record

C. To create a chart automatically

D. To hide confidential data

Correct Answer: B. To uniquely identify a record

Question 5

Which chart is generally most suitable for showing campaign delivery across four weeks?

A. Line chart

B. Pie chart with one slice

C. Scatter plot without axes

D. Three-dimensional doughnut chart

Correct Answer: A. Line chart

Question 6

Why can averaging individual CPM values be misleading?

A. CPM cannot be displayed as a number

B. It gives equal weight to channels with different impression volumes

C. It always excludes cost

D. It converts impressions into clicks

Correct Answer: B. It gives equal weight to channels with different impression volumes

Question 7

Which action is an example of data validation?

A. Allowing any spelling of campaign status

B. Restricting status to an approved list

C. Deleting all blank cells automatically

D. Changing every error to zero

Correct Answer: B. Restricting status to an approved list

Question 8

If planned cost is 20,000 and actual cost is 18,500, what is the variance defined as Actual minus Planned?

A. −1,500

B. 1,500

C. −7.5

D. 7.5

Correct Answer: A. −1,500

Question 9

In a relational campaign database, which field most likely acts as a foreign key in a Placement table?

A. PlacementName

B. CampaignID

C. CostFormat

D. ChartTitle

Correct Answer: B. CampaignID

Question 10

Which practice best protects personal audience data?

A. Share every workbook publicly for transparency

B. Collect unlimited attributes for possible future use

C. Apply access controls and data minimisation

D. Remove all documentation

Correct Answer: C. Apply access controls and data minimisation