Data Analyst

Provided strategic recommendations to lower Cost-Per-Acquisition (CPA), increase Click-Through Rate (CTR), and improve ad viewability.

Reduced CPA for key audiences by targeting regions with high conversion efficiency.

Identified optimal ad sizes (300x250, 320x50) and devices (smartphones) for cross-platform success.

Determined top-performing ad exchanges, with Sovrn leading on cost-effectiveness.

Which geographic regions are most cost-effective for customer acquisition?

What audience segments deliver the highest engagement and conversion rates?

How can creative messaging and ad sizes improve campaign outcomes?

Cleaned and analyzed datasets using Google Sheets.

Used Tableau and Excel for data visualization.

Spatial Analysis: Evaluated CPA, conversion rates, and impressions by location.

Audience Analysis: Assessed cost-efficiency of buyer propensity and interest-based segments.

Creative Analysis: Identified effective ad sizes, devices, and messaging.

Inventory Analysis: Calculated composite costs (CPC, CPM, and viewability) to rank ad exchanges.

Visualizing Cost-Per-Acquisition (CPA) and Conversion Rates (CVR) by geographic region to identify the most cost-effective and underperforming areas. The analysis highlights that Mississippi, Minnesota, and Wyoming are the most efficient states for customer acquisition, while Alabama needs improvement.

A breakdown of performance metrics by audience segment, focusing on CPA, CPC, and Conversions. The "People & Society" and "Interest Propensities" segments stand out for their cost-efficiency and high engagement, while "Education" is identified as a low performer.

Analyzing the effectiveness of ad sizes and messaging strategies. The 300x250 size proved most impactful across platforms, and the "Subscribe Today - 10% Discount" message drove the highest click-through rates (CTR). This analysis informs optimal creative strategies for future campaigns.

Ranking ad exchanges based on Composite Costs (CPC, CPM, and Viewability). Sovrn emerged as the most cost-effective exchange, while Kargo and TripleLift were identified as strong performers for impressions and engagement.

Top-performing states: Mississippi, Minnesota, and Wyoming for cost-effectiveness; Iowa, New York, and Vermont for brand viewability.

Best audience segments: "People & Society" and "Interest Propensities" for low CPA and high conversions.

Creative optimizations: "Subscribe Today - 10% Discount" was the top-performing message, driving significant engagement.

Prioritize buyer propensity and interest propensities segments, reallocating funds from underperforming audiences like "Education."

Continue targeting Mississippi, Minnesota, and Wyoming for low-cost, high-conversion opportunities.

Leverage branding potential in Iowa, New York, and Vermont.

Continue using 300x250 and 320x50 ad sizes across devices, with a focus on smartphones for better cross-platform performance.

Replicate the success of the "Subscribe Today - 10% Discount" message and test similar variations.

Allocate higher budgets to Sovrn for cost-effective outreach and viewability while testing other top-performing exchanges like Kargo and TripleLift.

If you’d like to view the complete project presentation with detailed visuals and insights, click here to access it on GitHub.

Recommend the best movie investment strategies to stakeholders by analyzing factors like genre, budget, release date, and classification.

Horror films deliver the highest ROI at 460% due to low budgets and average revenue.

Adventure films generate significant revenue but require high budgets.

Stand-alone films outperformed franchises, making them the safest investment choice.

Which genres provide the highest profitability with minimal risk?

What is the impact of star power and budget on movie ROI?

What release months yield the best box office performance?

Are stand-alone movies or franchises more successful?

Collected and processed movie data to examine relationships between budget, revenue, and ROI.

Used pivot tables to explore genre, release date, and classification trends.

Genre Analysis: Identified top-performing and low-performing genres based on ROI and revenue.

Release Date Trends: Evaluated the impact of release months on movie success.

Classification Insights: Compared stand-alone movies and franchises to determine profitability.

Analyzed ROI by genre, showing Horror as the top performer due to low budgets and creative storytelling. Crime showed low profitability despite average budgets.

Examined box office performance by release month, revealing that July, September, October, and January are optimal months for releasing movies, particularly in the Horror genre.

Investigated the correlation between star power and ROI, finding that high budgets and star inclusion don’t guarantee success. For instance, Ian McKellen's movies had high revenue but moderate ROI compared to low-budget horror films.

Compared the ROI of stand-alone movies versus franchises, finding stand-alone movies deliver higher ROI due to lower production complexity and costs.

Horror films are ideal for low-risk, high-return investments due to their low budget and strong audience appeal.

July, September, October, and January are the most profitable months for releases.

Stand-alone movies outperform franchises, offering safer returns on investment.

High ROI potential with minimal budget requirements.

Focus on releasing in July, September, October, and January.

Safer investments with better returns compared to franchises.

Low ROI despite average budgets makes it less appealing.

For a detailed walkthrough of this project, including visuals and analyses, click here to view on GitHub.

Products: Basic product information including name, type, and pricing.

Customer Information: Personal details like username, password, and phone number.

Orders: Order details like time submitted, customer ID, and associated payment and shipping information.

Promotions: Information about discounts, start/end dates, and descriptions.

Reviews: Customer feedback on products with ratings and comments.

Tracking data for customer behavior beyond what is entered on the website to maintain privacy.

Perform CRUD operations for all core entities (products, customers, orders, payments, etc.).

Enable tracking of shipping and payment info for efficient issue resolution.

Implement constraints (e.g., start/end times for promotions) to avoid conflicts.

Ensure no external user tracking is implemented in the database.

Comprehensive schema with primary and foreign key constraints to enforce data relationships.

Utilized appropriate data types like VARCHAR, INT, DECIMAL, and ENUM for better data storage and precision.

Added indexes on critical columns like orderStatus and customerID (Shipping table), and quantity (Products table) to improve query performance.

Constraints like NOT NULL, UNIQUE, CHECK, and AUTO_INCREMENT applied to enforce data accuracy and consistency.

Products: Tracks product details like name, category, price, and stock quantity.

Customer Info: Stores customer data including username, password, and contact information.

Orders: Links customers, payments, and shipping for each purchase.

Orders and Products (Junction Table): Resolves many-to-many relationships between orders and products.

Payment Info: Records payment types, statuses, and timestamps.

Shipping: Manages shipping addresses, methods, carriers, and order statuses.

Promotions: Handles promotional details like discounts and validity periods.

Reviews: Captures customer feedback and product ratings.

One-to-many between customers and orders.

Many-to-many between orders and products.

One-to-one between orders and payment/shipping information.

One-to-many between products and reviews.

Enforced one-to-one, one-to-many, and many-to-many relationships using primary and foreign keys.

Allowed only one active promotion per product at a time to avoid conflicts.

Used CHECK constraints to prevent invalid data entries, such as negative prices or quantities.

Only supports one promotion per product at a time. To support multiple promotions simultaneously, a many-to-many relationship would be required.

Does not allow for multiple addresses per customer; this must be handled at the application level.

Retrieve all orders for a specific customer.

Identify products with low stock or high ratings.

Fetch payment details for a specific order ID.

Enhance promotion functionality to allow multiple promotions for a single product.

Add support for multiple customer addresses within the database schema.

Improve indexing for more complex queries, such as retrieving data on highly rated products or frequent customers.

To explore the schema, queries, and database design in more detail, click here to view on GitHub.