Module 1 - Unit 3: Data

Management
Confidence Not Confident

Last Edited @October 31, 2024 12:28 PM

Data Management
Your first question might be: Why is this important?
There are several reasons for this, with the first being growth. The amount of
digital information being produced is increasing at an accelerating rate.
In 1999, Lyman and Varian estimated that 1.5 exabytes of unique information were
produced, and by 2003, this grew to 5 exabytes. To give you context, an exabyte
is one billion gigabytes. Since then, IDC, a consultancy, calculated that from 2006
to 2012, the amount of data generated increased from 161 to 2,837 exabytes—
much faster than anticipated.
Sources of Data

Where is all this data coming from?

It’s from a variety of sources, many of which you’re already familiar with.
For example:

Facebook has 1.2 billion active daily users, posting 300 million photos and
generating 4.5 billion likes every day.

Twitter averages 6,000 tweets posted every second.

Google processes over 40,000 search queries per second.

Additionally, new sources of data that didn’t exist at the turn of the millennium are
contributing to this growth. Social media, while a major contributor, is not the only

Module 1 - Unit 3: Data Management 1

 source.
Supply chains are also a significant factor:

Amazon sells more than 480 million unique items to 240 million customers,
and this number continues to grow.

Walmart processes more than a million customer transactions every hour,

feeding databases that contain over 2.5 petabytes of data. To clarify, a
petabyte is one million gigabytes.

UPS delivers 20 million packages daily to over 8 million delivery points using
more than 100,000 vehicles, with most deliveries done by surface transport.
Imagine the amount of tracking data required to handle all this.

Macy’s adjusts the pricing of 73 million items in real-time based on demand

and inventory.

Others Contributions

Sensors are also adding to the influx of data. Today’s cars have between 60
and 100 sensors, with estimates suggesting future models could have over
200 sensors. These sensors transmit a constant stream of data.

Even smart packaging is being developed to monitor perishable items, capturing

temperature, humidity, and other conditions in real-time during transit.

Diagnostic tests on your computer or server provide information about system

health, adding to the data load. This also applies to - The Internet of Things
(IoT)—machines communicating with other machines. Currently, over 6 billion
connected devices exist worldwide, a number expected to grow to 20 billion
within four years.

Regulations can drive data growth too.

For example, the 2013 Drug Supply Chain Security Act requires serialized
numeric identifiers for pharmaceuticals. Before this act, a batch of 10,000 units of
a drug might have generated 4 transactions. Post-act, this requires 60,000
transactions, increasing data generation from 2 kilobytes to 11 megabytes.

The Data Challenge: Size, Velocity, Variety, and

Veracity

Module 1 - Unit 3: Data Management 2

 It's not just about the size of the data, though.
The velocity and variety of the data are equally important.

Velocity refers to how fast we need to analyze the data. Is it streaming data,
or can we analyze it after a delay?

Variety refers to the different formats of the data, from structured (like
financial data) to unstructured (like Twitter feeds or call logs).

A good model to visualize this comes from Rozados and Tjahjono, who show
volume and velocity on one axis and variety on another. Supply chain data, for
instance, falls into three categories:

1. Core transactional data (e.g., CRM transactions, demand forecasts,

transportation costs)

2. Internal systems data (e.g., ERP and MRP systems)

3. Other data sources (e.g., IoT, Twitter feeds, email records, call logs)

Finally, IBM adds a fourth "V": veracity—the reliability of the data. Just because
it's in a database doesn't mean it's true. We must always question the accuracy of
the data.

Module 1 - Unit 3: Data Management 3

 Example of Using Big Data: Tiny Co.
Let's dive into an example of using big data—though, in this case, it's a small
example, just to give you insights into handling large data sets.
The company in this example is Tiny Co., which sells products to retail outlets.
You’ve recently been hired by Tiny Co. to work in the supply chain group, and
your first assignment comes from Edith, the VP of Supply Chain. She has tasked
you with understanding the domain of forecasting processes.

As part of this, Edith asked you to focus on a specific store, meet with the store
manager, and report back. Meanwhile, other members of your team will explore
other stores.

You asked the store managers for sales data from the past three years, and the
data team provided you with a spreadsheet. Here's an excerpt from their email:

"Welcome to Toy Co.! I've attached the data you asked for in the spreadsheet. It
contains six tabs—one for the SKU master and separate tabs for each store. The
store tabs contain daily sales information for the last two years (the only data we
could find) for each respective store. This includes a unique database or
transaction ID, the date, the SKU, the quantity sold, and total revenue from sales.
The SKU master tab contains additional information on each SKU that might be
useful. It took a lot of effort to gather this data, so I hope this is everything you
need."

This scenario highlights a common challenge in big data. While there is plenty of
data available, it’s often difficult to access the right data from the right people for
the right processes.

The Data
Let’s look at the data provided:

1. SKU Master Table:

Module 1 - Unit 3: Data Management 4

 Every company that sells products maintains a SKU master table, which holds
all relevant information for each Stock Keeping Unit (SKU). In this case, we
have attributes such as:

Department

Class

Style

Color

The SKU number is simply a concatenation of these attributes. Additionally, the

table includes:

Cost

Selling price

Vendor

In real companies, this table may also contain other attributes like weight, cube,
insurance value, and more. Maintaining the SKU master table is a dynamic and
non-trivial task, as every new product, size, color, or packaging change results
in a new SKU being added to the table.

1. Transaction Files:

You’ll focus on Store 312. The transaction data includes:

Module 1 - Unit 3: Data Management 5

 Database ID

Stock Keeping Unit (SKU)

Store ID

Date

Number of units sold that day

Dollar sales (revenue) for that transaction

Edith wants you to characterize the total sales, both in units and dollar value.
This is a straightforward task: analyze the total number of units sold and the
corresponding revenue.

Characterizing the Data

To characterize the distribution of units sold and revenue, we will focus on two
main areas:

1. Central Tendency:
You can measure central tendency using:

Mode

Median

Mean

Module 1 - Unit 3: Data Management 6

 2. Dispersion:

To measure the spread or dispersion of the data, use:

Range

Interquartiles

Variance

Standard deviation

Coefficient of variation (ratio of standard deviation over the mean)

Your task is to characterize the distribution of units sold and revenue for Store
312. Once you’ve calculated these metrics, be sure to record the numbers, and
we’ll discuss them in the next session.

Cleaning Data

Data [Link]

This is quite easy to do in a spreadsheet. It should take only a few seconds.

However, as I look at the results, some things appear strange. For example,
negative sales—how can that happen? Does it make sense? Also, notice the
average is nearly 30 times higher than the median. This skew indicates something
significant: the data is skewed to the right, toward larger numbers. The maximum
value further illustrates this: on one day, a SKU sold 60,000 units—a stark
contrast to a day with negative 6 sales.
These discrepancies should raise alarms. Another major flag is the coefficient of
variation of 18—a massive value, indicating substantial variability.

These summary statistics highlight potential data issues. All the mathematical
methods we’ve covered so far—whether optimization, summary statistics,
simulation, or data analysis—assume clean data. But as many of you know from

Module 1 - Unit 3: Data Management 7

 experience, data is rarely clean. I’ve never encountered a perfectly clean data
set.
When dealing with data, you need to assume it’s dirty and prepare to clean it. You
should ask:

Is the data clean?

Is it complete?

What assumptions are you making?

Do the results make sense?

How can you verify this?

Cleaning data is never an afterthought. A common mistake is to assume the data

is fine and not allocate time for basic checks. There's a saying for this: "garbage
in, garbage out." Sometimes, the worst-case scenario is when the bad data goes
unnoticed, rippling through the system undetected.

So always allow time for data checks, including:

Module 1 - Unit 3: Data Management 8

 Invalid values: Are there negatives? Is text where numbers should be? Are the
values too small or too large? Are there missing values?

Mismatches between related data sets, especially when merging data from
multiple sources.

Duplications: Unique identifiers might not be as unique as you think.

Human error: Mistyped data.

Outliers: Explore the top and bottom values, as they can reveal interesting
insights.

It’s also essential to stay organized during the cleaning process. Use versioning to
track changes, allowing you to go back if needed.

Here’s what I did after looking closer at the data:

I created an issue table to track problems. I like to add a column in the

spreadsheet as an issue code, indicating whether to keep or reject data for
specific reasons. Here’s what I found:

21 records with negative sales: These could be returns, so I flagged them but
kept them for now.

34 records with zero sales: Removed.

50 records with null or blank values: Removed.

30 records with non-numeric data: Removed.

11 records with very large sales values (like the 60,000 units in one day): I
corrected these to the appropriate magnitude and flagged them for further
review with the store manager.

Module 1 - Unit 3: Data Management 9

 Always log issues and actions, maintaining an audit mindset. You may need to
justify changes and undo them if necessary. Therefore, never fully delete data—
archive it, and create a clean data set for analysis.

Next steps: Validate decisions with process owners, using graphs like scatter
plots and histograms to visualize data. Spreadsheets have limitations, and as data
sets become more complex, tools like pivot tables become necessary. However,
this may push the boundaries of what spreadsheets can handle, making the case
for transitioning to databases.

Post-cleaning results: After cleaning, my numbers look more reasonable. The

average units sold per day is about 5.8, and the average revenue per SKU per
day is $76. The coefficient of variation is still high, but now within a reasonable
range.

This simple analysis raises further questions, such as:

How do different SKUs behave?

How do revenues and sales differ by month, week, or day?

Are sales prices consistent over time?

Are there trends in sales, both in units and dollars?

Do returns or negative sales correlate with other sales?

How are sales of different SKUs related?

These types of queries require more than spreadsheets can handle, leading us to
databases, which we’ll explore next.

Querying data in spreadsheets

Let's break down how to answer each of Edith's six questions based on the
specific subset of data from the MA Excellent Products vendor. Here's how you
can tackle them using Excel, and why spreadsheets might be cumbersome for
more advanced or repetitive analysis:

Module 1 - Unit 3: Data Management 10

 1. Which month has the highest average sales in dollars for the
store?
Approach:

Use the MONTH() function to extract the month from the transaction dates.

Create a pivot table where:

Rows = Month

Values = Average or Sum of Sales

This will allow you to see sales per month and easily identify which has the
highest average.

Why Excel is challenging:

Managing months over multiple years can be tricky since you might have
overlapping years, so you’ll need to group the data by both month and year or
ensure you're looking at data for one specific year.

2. Which week has the highest average sales in dollars for the
store?
Approach:

Use WEEKNUM() to extract the week number from the dates.

Combine it with the YEAR() function to differentiate weeks from different years.

In the pivot table:

Rows = Week Number (e.g., "Week 1 of 2014", "Week 1 of 2015")

Values = Average or Sum of Sales per week

Why Excel is challenging:

You’ll need to concatenate week and year, which makes it more complex when
you have data spanning multiple years, as the same week number may occur
in different years.

3. Correlation between the three SKUs from MA Excellent

Products?

Module 1 - Unit 3: Data Management 11

 Approach:

Aggregate weekly sales for each SKU using the SUMIFS() function or a pivot
table (grouping by week).

Use the CORREL() function to calculate correlations between the SKUs' sales.

Why Excel is challenging:

Setting up correlations in Excel for multiple time series (like sales over time for
three SKUs) is possible but manual, especially if you're frequently updating
the dataset.

4. How does the profit margin for sales change over time?
Approach:

You’ll need to pull in cost data from the SKU master table, merging it with the
sales data using either a lookup function ( VLOOKUP() or INDEX-MATCH ).

Once you have costs, calculate profit margins as (Sales - Cost) / Sales .

In the pivot table, track how profit margins change over time (using Month or
Week as rows).

Why Excel is challenging:

Merging large datasets in Excel is not as seamless as in databases, and the

risk of errors increases with more complex lookups.

5. Which day of the week is best for running a promotion based

on low sales for store 312?
Approach:

Use the WEEKDAY() function to determine which day of the week each
transaction occurred on.

Use a pivot table where:

Rows = Day of the week

Values = Average sales per day.

Sort the results to identify the day with the lowest average sales, which could
be the best day for a promotion.

Module 1 - Unit 3: Data Management 12

 Why Excel is challenging:

Excel handles this well, but the issue arises when you need to track data over
multiple stores or when day-of-week analysis becomes a small part of a larger
dataset.

6. How do the SKUs behave differently in other stores?

Approach:

This requires looking at sales across multiple stores, so you’ll need to filter by
SKU and by store in your pivot table.

Create a pivot table that includes store as one dimension and SKU as another
to analyze how the SKUs perform in each store.

Why Excel is challenging:

Scaling this analysis across multiple stores can become cumbersome,

especially if you need to look at more than just MA Excellent Products SKUs,
or if you plan to apply the analysis repetitively.

Summary:
These tasks highlight Excel's utility in quick, ad hoc analysis. However, as the
dataset grows or the complexity of analysis increases (e.g., merging multiple
datasets, tracking across time periods, or performing correlations), a database or
a more specialized tool would be better suited.
Pivot tables are a great way to summarize data, but for structured and repetitive
tasks, databases or data analysis tools like Python's pandas or R become more
efficient.

Data management challenges

Opportunities of Big Data:
1. Digital Disruption: Companies like Uber, which don't own the traditional assets
(like taxis), demonstrate how data management has become the core value

Module 1 - Unit 3: Data Management 13

 driver. The ability to manage, track, and optimize data is what enables these
companies to disrupt entire industries.

Example: Uber matches riders with drivers, but its true value lies in its
ability to manage vast amounts of data in real-time.

2. Widespread Use Across Industries: Data analytics has become integral

across multiple sectors. Retailers, consumer packaged goods (CPG)
companies, and supply chain operations heavily rely on data for things like:

Sales forecasting and promotions: Predicting demand or setting prices

based on data patterns.

Product recommendations: Using customer data to suggest relevant

products.

Supply Chain Analytics: Tracking shipments, assets, and inventory in

real-time, enabling businesses to optimize their networks.

Advanced Applications:

Micro-segmentation: Going beyond traditional ABC segmentation to

personalize services or products at a much finer level.

Digital Twin Technology: Creating virtual replicas of physical assets to

monitor and predict performance or failures using sensor data.

3. Data-Driven Optimization: With analytics, companies can predict costs,

monitor assets, and ensure operational efficiency, providing a competitive
advantage.

Challenges of Big Data:

Module 1 - Unit 3: Data Management 14

 1. The Myth of Instant Insights: There’s a common misconception that applying
an algorithm to raw data will automatically generate insights. However, data
scientists know that extracting value from data is a much more complicated
process. It requires:

Data Cleaning: Before any meaningful analysis can occur, data must be
cleaned, organized, and pre-processed. This is often the most time-
consuming step.

Combining Data: Data is often siloed, scattered across multiple sources

and systems, making it a complex task to integrate it into a unified dataset.

TinyCo Example: Even in a simple example like TinyCo, we saw that

significant preparation was necessary just to answer straightforward
business questions.

2. Programming and Complexity: Raw data often requires programming skills

and advanced tools to manipulate, clean, and analyze it, which can be
resource-intensive.

3. Data Silos: Data in organizations typically exists in isolated systems. Breaking

down these silos to combine data into a cohesive structure for analysis is
difficult.

4. Big and Growing: As data continues to grow in volume, handling it becomes

increasingly complex. Storage, processing, and analyzing these massive

Module 1 - Unit 3: Data Management 15

 datasets require scalable infrastructure, advanced tools, and a skilled
workforce.

Conclusion:
While big data presents enormous opportunities, it comes with significant
challenges. Successful companies must invest in both technology and expertise
to extract meaningful insights from their data. It's not just about gathering massive
amounts of data but developing the right processes, tools, and skilled personnel
to clean, structure, and analyze that data effectively.

Module 1 - Unit 3: Data Management 16