MARKETING ANALYTICS
Marketing Performance & Sentiment
Where can engagement and customer feedback guide a better marketing strategy?
1,363 reviews · Portfolio case study
Review sentiment · record count
Web visualisation · calculated from supplied dataThe question & data
Customer-journey preparation, engagement reporting, and sentiment enrichment bring behaviour and feedback into one analytical story.
The supplied enriched file contains 1,363 reviews. Related SQL scripts prepare customers, products, engagement, and customer journeys.
The approach
- Joined customer geography and standardised product and engagement fields.
- Used ROW_NUMBER to remove duplicate journeys and a date-level average to fill missing durations.
- Applied NLTK VADER scores and rating-based rules, then organised four Power BI pages around conversion, engagement, and reviews.
What the work reveals
840 reviews are labelled Positive and 226 Negative in the enriched dataset.
Another 282 reviews have mixed sentiment labels; 15 are Neutral.
From findings to next steps
Read recurring themes in mixed and negative reviews, then test specific service or content improvements.
Read the results in context
Sentiment categories combine text scores and star ratings; they are not independent measures of satisfaction. Conversion percentages in the example presentation require denominator validation.
Explore the SQL implementation
-- Common Table Expression (CTE) to identify and tag duplicate records
WITH DuplicateRecords AS (
SELECT
JourneyID, -- Select the unique identifier for each journey (and any other columns you want to include in the final result set)
CustomerID, -- Select the unique identifier for each customer
ProductID, -- Select the unique identifier for each product
VisitDate, -- Select the date of the visit, which helps in determining the timeline of customer interactions
Stage, -- Select the stage of the customer journey (e.g., Awareness, Consideration, etc.)
Action, -- Select the action taken by the customer (e.g., View, Click, Purchase)
Duration, -- Select the duration of the action or interaction
-- Use ROW_NUMBER() to assign a unique row number to each record within the partition defined below
ROW_NUMBER() OVER (
-- PARTITION BY groups the rows based on the specified columns that should be unique
PARTITION BY CustomerID, ProductID, VisitDate, Stage, Action
-- ORDER BY defines how to order the rows within each partition (usually by a unique identifier like JourneyID)
ORDER BY JourneyID
) AS row_num -- This creates a new column 'row_num' that numbers each row within its partition
FROM
dbo.customer_journey -- Specifies the source table from which to select the data
)
-- Select all records from the CTE where row_num > 1, which indicates duplicate entries
SELECT *
FROM DuplicateRecords
-- WHERE row_num > 1 -- Filters out the first occurrence (row_num = 1) and only shows the duplicates (row_num > 1)
ORDER BY JourneyID
-- Outer query selects the final cleaned and standardized data
SELECT
JourneyID, -- Selects the unique identifier for each journey to ensure data traceability
CustomerID, -- Selects the unique identifier for each customer to link journeys to specific customers
ProductID, -- Selects the unique identifier for each product to analyze customer interactions with different products
VisitDate, -- Selects the date of the visit to understand the timeline of customer interactions
Stage, -- Uses the uppercased stage value from the subquery for consistency in analysis
Action, -- Selects the action taken by the customer (e.g., View, Click, Purchase)
COALESCE(Duration, avg_duration) AS Duration -- Replaces missing durations with the average duration for the corresponding date
FROM
(
-- Subquery to process and clean the data
SELECT
JourneyID, -- Selects the unique identifier for each journey to ensure data traceability
CustomerID, -- Selects the unique identifier for each customer to link journeys to specific customers
ProductID, -- Selects the unique identifier for each product to analyze customer interactions with different products
VisitDate, -- Selects the date of the visit to understand the timeline of customer interactions
UPPER(Stage) AS Stage, -- Converts Stage values to uppercase for consistency in data analysis
Action, -- Selects the action taken by the customer (e.g., View, Click, Purchase)
Duration, -- Uses Duration directly, assuming it's already a numeric type
AVG(Duration) OVER (PARTITION BY VisitDate) AS avg_duration, -- Calculates the average duration for each date, using only numeric values
ROW_NUMBER() OVER (
PARTITION BY CustomerID, ProductID, VisitDate, UPPER(Stage), Action -- Groups by these columns to identify duplicate records
ORDER BY JourneyID -- Orders by JourneyID to keep the first occurrence of each duplicate
) AS row_num -- Assigns a row number to each row within the partition to identify duplicates
FROM
dbo.customer_journey -- Specifies the source table from which to select the data
) AS subquery -- Names the subquery for reference in the outer query
WHERE
row_num = 1; -- Keeps only the first occurrence of each duplicate group identified in the subquery
Download SQL ↓Based on supplied portfolio materials. Findings describe the dataset and report; recommendations are proposed next steps, not measured business outcomes.