Project Overview:

Jumping into the role of Sr. Marketing Analyst at Maven Cafe. I've been tasked with identifying key customer segments and develop a data-driven strategy for future promotional messaging & targeting.

A test has been ran by sending different combinations of promotional offers to existing rewards members. Now that the 30-day period for the test has concluded, an interactive PowerBI Dashboard will be created to convey the findings, give insights and make recommendations.

Some questions to ask of the data are:

• What is the make up of Maven Cafe's customer base?
• What is the minimum amount it’s customers want to spend on offers?
• What is the best way to communicate with the customer base?
• What type of discounts do customers prefer?

The results need to be summarized in a report that will be presented to the CMO.

Preparing the Data:

Data cleaning and manipulation were done in Python. After downloading the csv files were uploaded into Jupyter notebook.

Customer’s Table: Reviewed data information and checked for duplicate customer ids. ‘Age’ column was discovered to have a series of outliers. Age values of 118yrs corresponded to ‘NA’ gender and 0 value in income. The resulting 2175 records were dropped.

Offers Table: ‘Channel’ column was cleaned up by removing special symbols.

Events Table: ‘Value’ column was separate to leave offer id. Cleaned data tables were extracted from Jupyter Notebook then loaded into PowerBI.

PowerBI/Power Query:

Power Query – Customer’s Table

• Data types were established and date value broken down into months.
• Groupings were created for ‘age’, ‘income’ and duration of membership which will be used later in the analysis.
• Events and Offers tables – columns were renamed to better convey their values.

PowerBI

Events Table:

• Each event (offer received, viewed, completed and transactions) were separated out into their own table.
• Columns were created to hold the conversions for ‘hours_passed’ to days,
• Offer information was merged to correspond to their offer ids.
• Data groups created.
• Tables were created to sort the group variables created earlier

Measures: After reviewing the data to determine what KPIs to show, various measures were created for each event category.

Data Modeling: Relationships were established between tables.

Dashboard

Design, theme and icons were done in PowerPoint. The Maven Café logo was created using Canva.

Customer Overview Dashboard:

There are 2 sections to the customer dashboard. How Customers respond to the offers and customer demographics. KPI’s are done in the form of card visuals.

Customer Offer Habits:

• Even though the initial dataset started with 17,000 customers, after cleaning and removing invalid customer entries, the number dropped to 14,825.
• Customer offer habits are based on ‘received offers’ (76,277). The category is broken down into ‘Offers Viewed’ and ‘Offers Completed’.
• Each offer category displays number of offers and their percentage, Previous Months total and percentage of change and ‘Avg. Days..’ to either view or complete the offer. ‘Previous month’ totals are visible when selecting individual months from the side menu slicer.

Offer Insights: The number of customers viewing received offers remained fairly constant at 70% or more, so customers are looking at their offers. Of that 70%, approximately half (40%) of the customers follow the offer to completion in an average of 3.5 days.

Customer Demographics:

The demographics were done in three main sections: Age, Loyalty and Income. This is where the table created for sorting the grouped customer information come into play.

• Maven Café seems to attract an older segment of the population with 40-70yr olds making up a large segment of its customers.
• The loyalty program sees customers drop off after 1-2 years. New customers do not seem interested in the loyalty program. 2017 saw the biggest leap in customer growth.
• Income levels in the lower to mid-level middle class are another popular customer group, with the $45-75k segment being the highest.

The core group of customers for Maven Café are Male aged 50-60yrs old with an income of 60-75k. They make, on average, 8 transactions with a total purchase amount of $13.

Added a special segment for Maven Cafe's top customer. Detailed customer information is available by hovering over the value within the table snippet.

Rewards Overview

Sales & Transaction:

Maven Café Sales: Reward overview started off with the overall sales total for all transactions, both from offers and regular sales. In all, it total $1.7M from 138k transactions. From there, that total was filtered out using various DAX expression as either measures or calculated tables.

Offer Sales: More DAX was used to determine ‘OfferDrivenTransactions’ and ‘OfferDrivenSales’. Offer ‘Rewards Payout’ is based on the transactions from completed offers. This amount was also used to determine ‘Non-Reward Sales’. Sales from offers totaled $616k.

To go along with the offers and sales, the average time in days it takes customers to view and act on the offers were also included in that section. It was determined that it took customers an average of 2.4 days to view an offer after receiving it and 3.7 days to complete said offer. The average days are based on valid (non-expired) offers.

Rewards & Offers:

I choose to focus on three aspects of Maven Café’s rewards program:

• What is the minimum amount it’s customers want to spend on offers.?
• What is the best way to communicate to the customer base?
• What type of discounts do customers prefer?

Minimum to spend: The $10 amount is popular with both types of reward offers; bogo, discount. { More information on customer purchasing needed to see what type of products are popular} 25% of customers fall in the $10 minimum to spend range. The $20 minimum is the least popular with approximately 10% of customers.

This section was based on overall customers.

Best way to communicate: Next was what channels or lines of communication yielded the highest customer response. Using all the channels: web, email, mobile, social(WEMS) had the highest engagement at 46%. Web and Email only had the lowest interaction at 8%. More and more, cell phones are used to access information and social media.

Preferred Discount:

Both rewards options start out strong in terms of received and viewed. The redeemed rate is slightly more than half of the received amount.

The ‘Discount’ offer has a slight lead over ‘BOGO’ in terms of 'received offers'. BOGO has a higher viewership, but for redeeming offers, 'Discount' is again the most popular.

The top reward amount customers receive is $5 for both BOGO and Discount.

Recommendations:

With over half of the offers being viewed, more research - information is needed as to why the completion rate is not as high as the viewed rate.

With an older customer base, has the thought of offering ‘Senior’ discounts been looked into? More information needed if the current discount offer also includes senior discounts.

A case study or survey should be conducted to see why the low number of new members exist . Having either short surveys available at the register or other ways to capture customer information. It would also be beneficial to survey existing loyalty customers for feedback and or suggestions.

Does Maven Cafe have a mobile app? Mobile applications have surged in popularity and are a great way to keep customers up to date on offers and specials. If no mobile app is currently available, the Marketing team should research the feasibility of this.