Digital Ad Genius: Maximizing E-commerce Impact with Precision Digital Marketing Analytics

Objective:

Analyze and optimize digital marketing ad campaigns for FreshCart, an online grocery store, to maximize return on investment (ROI), enhance customer acquisition, and drive business growth.

Business Overview and Problem Statement

Fresh Cart, like many other companies, relies heavily on online advertising to attract new customers and keep the loyal ones coming back. But with ad costs fluctuating and competition heating up, they’re facing some tough challenges:

Inefficient Ad Spend: Fresh Cart suspects that not every dollar spent on digital advertising is yielding the desired ROI. Some campaigns may be costing more than they generate in revenue.

Identifying Effective Channels: With multiple advertising platforms available — from search engines to social media — Fresh Cart is struggling to determine which channels offer the best ROI.

As competition in the online grocery sector intensifies, the cost of customer acquisition is rising. Fresh Cart needs to find the most efficient way to optimize its digital ad spend and reduce costs.

Rationale for the Project

By analyzing their ad campaign data, Fresh Cart can uncover hidden gems, such as which demographics are most responsive to their ads or which platforms offer the best value for money. 

Marketing analytics involves using data to evaluate marketing campaigns' performance and identify improvement areas. It helps businesses understand the most effective strategies, where to allocate budget, and how to better connect with customers.

By analyzing the performance of different advertising channels, Fresh Cart can:

Identify demographics or regions where ads perform best.

Determine which campaigns are under or over-funded and reallocate funds accordingly.

Assess which platforms or strategies yield the highest returns.

Aim of the Project

My goal is to help Fresh Cart make every advertising dollar count. We’ll look at what’s working, what’s not, and how to get the most out of their ad spend. Specific objectives include to:

• Evaluate the effectiveness of current advertising campaigns across platforms.

• Identify top-performing advertising channels.

• Assess demographic engagement and conversion rates.

• Recommend strategies for better budget allocation.

Data Description

The marketing team has shared a treasure trove of data, including details like where the ads were shown, how long they ran, and how much they cost. We’ll be digging into this to find actionable insights. The data includes:

• CampaignID: Unique identifier for each campaign.

• Platform: Where the ad was shown (e.g., Google Ads, Facebook, Instagram, Twitter, third-party sites).

• AdDuration: Duration of the ad in weeks.

• Cost: Amount spent on the ad.

• Impressions: Number of times the ad was shown.

• Clicks: Number of times the ad was clicked.

• Conversions: Number of actions taken as a result of the ad.

• ConversionValue: Total sales value generated from the conversions.

• CPC (Cost Per Click): Amount spent for each click.

Tech Stack

The data will be analyzed in Power BI, with Microsoft Excel used as the data source.

Project Scope

Data Integration: Download the dataset from AMDARI and connect it to Power BI.

Data Cleaning: Clean and transform data to the required format using Power Query.

Data Modeling: Create data models in Power BI, including DAX functions for date and measure tables.

Analysis & Visualization: Define Key Performance Indicators (KPIs) and visualize data in Power BI.

Reporting & Recommendations: Write a report of the analysis and provide recommendations.

Improvements: Run further analysis on the data to unearth even more insights using Time Series and Geographical Analysis.

Key Performance Indicators (KPIs)

• To guide the analysis, the following KPIs will be used:

• Impressions: Number of times an ad was displayed to the user.

• Conversion Rate: Number of conversions divided by total clicks on the ad.

• Cost per Conversion: Cost of the ad divided by the number of conversions.

• Return on Ad Spend (ROAS): Revenue generated from the ad divided by the cost of the ad.

• Click-Through Rate (CTR): Total clicks on the ad divided by total impressions, multiplied by 100.

Data Modelling

A star schema data model was implemented in Power BI to facilitate the analysis of ad campaign performance. This structure involved connecting fact tables, containing key metrics such as clicks, conversions, and cost, to dimension tables for dates. This approach enabled the extraction of valuable insights into campaign effectiveness.

Key Metrics

• Total Ad Spend: $28.5K

• Sales Revenue: $97.52K

• Cost Per Conversion: $12.89

• Conversions: 2210

• Conversion Rate: 6.24%

• ROAS: 342.20%

• Campaign Count: 50

• Average Duration: 3.62 days

• Sum of Costs: $28.5K

Insights by Platform

1. YouTube Ads

• Conversion Rate: 6.79%

• ROAS: 1298.84%

• Sales Revenue: $29K

• Highlights:

  • YouTube emerged as the top-performing platform, delivering the highest conversion rate and ROAS.

  • Strong audience engagement and high returns make it the most cost-effective channel.

2. Twitter Ads

• Conversion Rate: 6.41%

• Cost Per Conversion: $14.21

• Sales Revenue: $28K

• Highlights:

  • Twitter demonstrated strong sales revenue and conversion rates.

  • Slightly higher Cost Per Conversion suggests room for cost optimization.

3. Google Ads

• Cost Per Conversion: $35

• ROAS: 170.88%

• Highlights:

  • Google Ads underperformed compared to other platforms, with the highest cost per conversion and the lowest ROAS.

  • Indicates a need for strategic reassessment to improve profitabilit

Actionable Recommendations

1. Invest More in YouTube:

   • Maximize budget allocation to YouTube campaigns, given their high ROAS and conversion rate.

   • Scale efforts by testing different ad creatives to sustain engagement.

2. Optimize Twitter Campaigns:

   • Focus on reducing the Cost Per Conversion through enhanced targeting and bidding strategies.

   • Experiment with ad formats and refine audience segments to lower costs.

3. Reassess Google Ads Strategy:

   • Reevaluate targeting parameters and keyword selection to enhance campaign efficiency.

   • Consider pausing underperforming ads or reallocating budgets to better-performing channels.

4. Conduct A/B Testing Across Platforms:

   • Test different creatives, messaging, and call-to-actions to identify elements driving the best results.

   • Use insights from YouTube and Twitter successes to inform Google Ads optimizations.

Visualization and Reporting

Using Power BI, I created dynamic dashboards that provided:

• A comparison of platform performance metrics (ROAS, Conversion Rates, and Costs).

• Insights into sales revenue trends and campaign engagement.

• Interactive filters for granular analysis of individual campaigns.

Key Performance Metrics:

YouTube emerges as the top-performing platform, boasting a 6.79% conversion rate and an impressive ROAS of 1298.84%, offering the highest return per dollar spent.

Twitter closely follows with a 6.41% conversion rate, but its higher cost per conversion ($14.21) compared to YouTube suggests potential for cost optimization.

In terms of sales revenue, YouTube and Twitter lead the pack with $29K and $28K, respectively, reflecting strong audience engagement.

Google Ads, however, lags behind with the highest cost per conversion ($35) and the lowest ROAS (170.88%), indicating a need for strategic reassessment. 

This project demonstrated my ability to manage complex datasets, derive actionable insights, and deliver data-driven recommendations to optimize digital marketing performance.