CASE STUDY: BELLABEAT MARKETING ANALYTICS

Role: Junior Data Analyst

Objective: To analyze smart device fitness data and identify consumer usage trends to inform and optimize marketing strategies for Bellabeat’s health-focused product line.

PROJECT OVERVIEW

Bellabeat, a high-tech manufacturer of health products for women, aims to expand its global footprint. By decoding how users interact with fitness technology, this project uncovers growth opportunities and provides data-driven recommendations to the executive leadership team.

CORE FOCUS

1. Data Insight: Decoding consumer habits through smart device data. 2. Strategic Impact: Translating fitness trends into actionable marketing pivots.

KEY DELIVERABLE: THE “ACTIVE COMPANION” STRATEGY

+ Proposal: Pivot from “Passive Tracker” to “Active Motivator.” + Target Product: The Bellabeat App & “Time” Watch. + Business Goal: Drive global brand scaling through high-retention features.

Step 1: Merge Data Batches (SQL)

Begin by combining our two data sources using UNION ALL to create a unified dataset for analysis.

CREATE TABLE daily_activity_merged AS
SELECT *, 'Batch 1' as Source_Batch FROM batch_1
UNION ALL
SELECT *, 'Batch 2' as Source_Batch FROM batch_2;

Step 2: Initial Duplicate Check

Validate the dataset by checking for duplicate entries on specific dates.

SELECT
  Id,
  ActivityDate,
  COUNT(*) as occurrence_count
FROM daily_activity_merged
GROUP BY Id, ActivityDate
HAVING occurrence_count > 1
ORDER BY occurrence_count DESC
LIMIT 5;
5 records
Id ActivityDate occurrence_count
1503960366 4/12/2016 2
1624580081 4/12/2016 2
1844505072 4/12/2016 2
1927972279 4/12/2016 2
2022484408 4/12/2016 2

Discovery: The result shows duplicates in data collected on 2016-04-12.

Step 3: Validate Duplicate Records (Fingerprinting)

Need to confirm if these are exact copies (which can be safely deleted) or different records sharing the same date (which requires investigation). I do this by creating a “fingerprint” of the data metrics.

SELECT
  Id,
  ActivityDate,
  /* Concatenating columns to create a unique 'fingerprint' for the row */
  COUNT(DISTINCT TotalSteps || '-' || TotalDistance || '-' || Calories) AS unique_row_fingerprints
FROM daily_activity_merged
WHERE ActivityDate = '4/12/2016' OR ActivityDate = '2016-04-12'
GROUP BY Id, ActivityDate
HAVING unique_row_fingerprints > 1;
Displaying records 1 - 10
Id ActivityDate unique_row_fingerprints
1503960366 4/12/2016 2
1624580081 4/12/2016 2
1844505072 4/12/2016 2
1927972279 4/12/2016 2
2022484408 4/12/2016 2
2026352035 4/12/2016 2
2320127002 4/12/2016 2
2347167796 4/12/2016 2
2873212765 4/12/2016 2
3977333714 4/12/2016 2

Discovery: The fingerprinting reveals that duplicate IDs/Dates are non-identical. They contain differing metric values despite sharing the same date. This indicates a sync conflict, not a database error.

Step 4: Investigate Data-Origin Conflicts

Isolate a single user to trace the data lifecycle and identify the root cause of the duplicate.

SELECT 
  *
FROM
  daily_activity_merged
WHERE
  Id = 4702921684 
  AND (ActivityDate = '4/12/2016' OR ActivityDate = '2016-04-12');
2 records
Id ActivityDate TotalSteps TotalDistance TrackerDistance LoggedActivitiesDistance VeryActiveDistance ModeratelyActiveDistance LightActiveDistance SedentaryActiveDistance VeryActiveMinutes FairlyActiveMinutes LightlyActiveMinutes SedentaryMinutes Calories Source_Batch Source_Batch:1
4702921684 4/12/2016 0 0.00 0.00 0 0 0 0.00 0 0 0 0 1440 0 Batch 1 Batch 1
4702921684 4/12/2016 7213 5.88 5.88 0 0 0 5.85 0 0 0 263 718 2947 Batch 2 Batch 2

Discovery: The system appears to pre-allocate records. One row shows 1,440 sedentary minutes (24 hours) and 0 activity. This is likely a “placeholder” row created before the device synced.

Step 5: Evaluate Data Integrity

Check if removing these “placeholder” rows resolves the issue, or if other types of duplicates exist.

SELECT
  Id,
  ActivityDate,
  COUNT(DISTINCT TotalSteps || '-' || TotalDistance || '-' || Calories) AS unique_row_fingerprints
FROM
  daily_activity_merged
WHERE
  SedentaryMinutes < 1440 -- Ignoring the placeholders
GROUP BY
  Id,
  ActivityDate
HAVING
  unique_row_fingerprints > 1;
Displaying records 1 - 10
Id ActivityDate unique_row_fingerprints
1503960366 4/12/2016 2
1624580081 4/12/2016 2
1844505072 4/12/2016 2
1927972279 4/12/2016 2
2022484408 4/12/2016 2
2026352035 4/12/2016 2
2320127002 4/12/2016 2
2347167796 4/12/2016 2
3977333714 4/12/2016 2
4020332650 4/12/2016 2

Discovery: Residual non-absolute duplicates remained even after ignoring placeholders. This signals that the issue is more complex than just empty rows.

Step 6: Re-investigate Data-Origin Conflicts

Look at a different user ID where the placeholder theory failed.

SELECT
  *
FROM
  daily_activity_merged
WHERE
  Id = 4558609924 
  AND (ActivityDate = '4/12/2016' OR ActivityDate = '2016-04-12');
2 records
Id ActivityDate TotalSteps TotalDistance TrackerDistance LoggedActivitiesDistance VeryActiveDistance ModeratelyActiveDistance LightActiveDistance SedentaryActiveDistance VeryActiveMinutes FairlyActiveMinutes LightlyActiveMinutes SedentaryMinutes Calories Source_Batch Source_Batch:1
4558609924 4/12/2016 1260 0.83 0.83 0 0 0 0.82 0 0 0 76 555 722 Batch 1 Batch 1
4558609924 4/12/2016 5135 3.39 3.39 0 0 0 3.39 0 0 0 318 1122 1909 Batch 2 Batch 2

Second Discovery (Partial-Day Snapshots): > I identified that the records from Batch 1 had significantly lower activity (e.g., 1,260 steps) compared to Batch 2 (5,135 steps).

The Logic: If I summed these rows, the total minutes would exceed 24 hours, creating a physically impossible day. This confirms that Batch 1 contains “work-in-progress” snapshots exported mid-day, while Batch 2 represents the finalized sync.

Step 7: Prioritize Finalized Syncs (Cleaning)

Apply a cleaning algorithm that prioritizes Batch 2 (finalized syncs) and discards the incomplete Batch 1 records. I use a Window Function to rank the rows.

CREATE TABLE daily_activity_cleaned AS
WITH ranked_data AS (
    SELECT 
        *,
        -- We rank by Source_Batch DESC so 'Batch 2' is always #1
        ROW_NUMBER() OVER (
            PARTITION BY Id, ActivityDate 
            ORDER BY Source_Batch DESC
        ) as rn
    FROM daily_activity_merged
)
SELECT * FROM ranked_data
WHERE rn = 1;

Step 8: Post-Cleaning Verification

Confirm full resolution of duplicates. The final dataset is now integral and prepared for exploratory analysis.

SELECT count(*) as Remaining_Duplicates
FROM (
    SELECT Id, ActivityDate, COUNT(*) as cnt
    FROM daily_activity_cleaned
    GROUP BY Id, ActivityDate
    HAVING cnt > 1
);
1 records
Remaining_Duplicates
0

Step 9: Persona Analysis

Now that the data is clean, I compare usage differences between users to inform our market strategy. I segment users based on their activity intensity and sedentary behavior.

SELECT
  CASE 
    WHEN VeryActiveMinutes > 180 THEN 'Superhuman (Elite Athlete)'
    WHEN VeryActiveMinutes BETWEEN 60 AND 180 AND SedentaryMinutes >= 800 THEN 'Sprinter (Intense/Sedentary)'
    WHEN LightlyActiveMinutes >= 300 AND VeryActiveMinutes < 30 AND SedentaryMinutes < 700 THEN 'Steady Mover (Low Intensity)'
    WHEN (VeryActiveMinutes + FairlyActiveMinutes) BETWEEN 30 AND 300 AND VeryActiveMinutes < 60 AND SedentaryMinutes >= 800 THEN 'Moderate (Balanced)'
    ELSE 'Low Activity'
  END AS user_persona,
  
  COUNT(*) AS total_days,
  AVG(TotalSteps) AS avg_steps,
  AVG(Calories) AS avg_calories
FROM
  daily_activity_cleaned
GROUP BY
  user_persona
ORDER BY 
  total_days DESC;

Visualization: The “Low Activity” Opportunity

# Prepare data for plotting
# Note: persona_df exists because we saved it in the previous SQL chunk
persona_plot_data <- persona_df %>% 
  mutate(
    percentage = total_days / sum(total_days),
    is_target = user_persona == "Low Activity"
  )

# Define custom colors
bellabeat_red <- "#FF6B6B"
bellabeat_gray <- "#C4C4C4"

# Create the chart
ggplot(persona_plot_data, aes(x = reorder(user_persona, percentage), y = percentage)) +
  geom_col(aes(fill = is_target)) +
  
  # Highlight only the target segment
  scale_fill_manual(values = c("FALSE" = bellabeat_gray, "TRUE" = bellabeat_red)) +
  
  # Formatting
  coord_flip() +
  scale_y_continuous(labels = scales::percent) +
  theme_minimal() +
  labs(
    title = "Market Gap: 72% of Users are in the 'Low Activity' Segment",
    subtitle = "Standard step-counting goals are failing this majority. They need efficiency, not volume.",
    x = "",
    y = "Percentage of User Base"
  ) +
  theme(legend.position = "none", 
        plot.title = element_text(face = "bold", size = 16),
        axis.text.y = element_text(size = 12))

STRATEGIC RECOMMENDATION: THE “ACTIVE COMPANION”