Shopping Mall Paid Search Campaign

Image by jcomp on Freepik

Hello everyone👋 Welcome to my Project on the topic of Marketing Analytics

Background Project

CoM, is an e-commerce company that has been in business for 8 years. 2 years ago CoM was looking to expand and introduce digital transactions to their platforms. CoM now has both a mobile app and a website that allows customers to access products and services

CoM is a growing e-commerce company that specializes in selling all products such as electronics and home needs. with the motto ‘you want it you got it’. with that motto CoM is looking to improve marketing strategies for a better understanding of how customers engage with its products and service and looking to increase the number of customers and sales.

The data division on CoM will be analyzing data collected from the website and mobile apps, for tracking ad campaigns, specifically through the use of Google AdWords to track conversions. The data will be used to identify patterns and trends in customer behavior to inform marketing strategy and improve overall sales.

Dataset

The file conversion_data.csv contains 190 observations in 12 variables. Below are the descriptions of the variables.

• Ad Group: category of the advert (coupon/promo code, desktop and/mobile ad, etc.…)
• Month: month of the campaign. The campaign started in July 2021 and ended in November 2021.
• Impressions: metric used in digital marketing to quantify the number of digital views or engagements of an advertisement. Impressions are also referred to as an “ad view.
• Clicks: how many clicks the ad received
• CTR: the number of clicks that your ad receives divided by the number of times your ad is shown: clicks ÷ impressions = CTR.
• Conversions: Conversions are those valuable actions that users take on your site like buying something or filling in a form. Success can be measured in the number of conversions generated at a particular cost.
• Conv Rate: It is the percentage of people who convert after clicking on your ads. Depending on your goals, a conversion may mean they make a purchase, complete a contact form, request a free trial, or take another desired action.
• Cost: Cost is the actual money spent by the advertiser (the “shop”) for the related ad group.
• CPC: it is the cost of the specific ads divided by the click. It is one of the metrics used to evaluate the effectiveness of the campaign in terms of ROI (Return on Investment), therefore a low or decreasing CPC is better than a high or increasing CPC.
• Revenue: the total amount of income generated by the advertisement.
• Sale Amount: The sale Amount for this dataset means the number of sales derived by the single ad group.
• P&L: Profit and Loss, based on the formula Revenue — Cost. For this dataset measures the profit of the specific Ad Group.

Glimpse of Dataset

Ad Group journey

Image by Author

e.g. Ads Journey: Shop — 1:1 — Desk — [shop coupon]

Keyword Match

• Exact match: This is the most strict keyword match type, where the keyword must match the search query exactly, including capitalization and spacing. For example, if the keyword is “flannel blue” the search query must be ”flannel blue” and nothing else.
• 1:1 match: This is similar to an exact match, where the keyword must match the search query exactly, but allows for minor differences in capitalization and spacing. For example, if the keyword is “flannel blue,” the search query could be “Flannel Blue” or “flannel blue.”
• Phrase match: This is a less strict keyword match type, where the keyword must match the search query in the exact order, but allows for additional words before or after the keyword. For example, if the keyword is “flannel blue,” the search query could be “buy flannel blue” or “flannel blue for men.”

Problem Statement

Users persona: Marketing Manager

The Marketing Manager needs to :

• Monitor Campaign Marketing Manager needs to monitor and analyze which campaigns and ads have a high-performance level for the company while keeping costs under control to ensure ROI, and identify areas for improvement to optimize future campaigns
• Purchase behavior and preferences. Marketing Manager needs to gain insights into customer purchasing behavior and preferences by analyzing the information category of the advert
• Conversion Analysis, identify patterns and trends in customer behavior, to inform marketing strategy and improve overall sales.

Goals

• Build a dashboard for the marketing team to monitor and analyze which campaigns and ads have the highest performance for the company
• Monitor with dashboard report to optimize and adjust campaigns to improve performance and achieve desired results over time
• Additionally, the Manager can gain information/insights into customer purchasing behavior and preferences from the website banner

Business Problem & Solution

Image by Author

Who’s the user?

Image by Author

User Storyline

Image by Author

User Flow

Image by Author

Business Metrics

Why do we need metrics? Metrics are important for analysis because metrics can provide quantifiable measures of specific aspects of a business, which can be used to evaluate its performance and progress toward goals

Data Cleaning & Wrangling

in this project, dataset I got from Kaggle, and the results of the cleaning process showed that the dataset was free of missing values and duplicate data, I am not focusing on handling the distribution of the data and including in outliers handling

Splitting process involved dividing the data into separate groups based on the value of the “Ad Group” attribute

# split the "ad_group" column by " - " and create new columns
ecommerce[["name_store", "keyword_match", "device_type", "landing_page"]] = ecommerce["Ad Group"].str.split(" - ", expand=True)

Dataset with new Attribute

After some of the process of cleaning and splitting the data, the next step iis to export the .xlsx for the file can import into Tableau and yeah the data now is ready for analysis and visualization🥳

ecommerce.to_excel('CoM_search_campaign.xlsx', index=False)

Dashboard on Tableau

Dashboard Overview

Based on Visualization, observe the dataset and answer these questions based on analysis:

How many impressions and clicks were generated along July — November?

• During the period of July to November, a total of 924,503 clicks were generated with 2,674,699 impressions.

What is the overall cost of the ad campaign?

• The overall cost of the ad campaign was $635,372.

Which device type is most commonly used by customers to generate clicks?

• Based on the data, it appears that mobile apps are the most commonly used device type by customers to generate clicks, with a total of 689.564 clicks (74% more than mobile). The second most commonly used device type is the website with a total of 234.939 (25 % compared to mobile) clicks.

How many impressions did each ad receive in a specific match keyword?

• In the match keyword, the ad with the most impressions is 1:1 type match keyword
• The ad “1:1 match keyword” received 1,755,536 impressions and the ad “exact match keyword” received 523,594 impressions, while the ad “phrase match keyword” received 395,559 impressions.

What is the click-through rate for each ad group?

Based on the data, it appears that The 3 top ad groups using the 1:1 match keyword and mobile device have a click-through rate of 40% or higher

• Shop — 1:1 — Mob — [shop promo code] 44% ctr
• Shop — 1:1 — mob [shop coupon code] 44%
• Shop — 1:1 — mob [shop discount code] 40%

How many Conversion rates were generated from November and what was the total revenue generated?

there was an 8.64% conversion rate generated in November, resulting in a profit and loss CoM in November loss of 27.922. But CoM In November, the impressions scaled up by 257.6% compared to October. reach until 1m impression

Analyze these business questions.

Based on data from November, which ad group is most suitable to be scaled in December and well performance to focus on increasing impression and CTR?

• Based on the data, it appears that the ad group “Shop — 1:1 — Mob — [shop coupon]” has the highest impressions and CTR at the 7th position in November, making it a suitable candidate for scaling in December.
• To focus on increasing impressions and CTR, it would be beneficial to concentrate on this ad group.

Using data from July — November the company wants to focus on creating new ad groups for increasing clicks and conversions. What 3 ads group top recommendations? and is it suitable for implementing and scaling in the next December ad program?

• When analyzing keyword matches, the highest conversion rate is found in the 1:1 keyword match, followed by the exact match. Among the landing pages, shop promo coupons have the highest click-through rate at 41.40% and a relatively low cost per click of $0.7- $0.9

• In terms of device platforms, mobile devices have a higher volume of clicks compared to desktop platforms. However, mobile devices also show the highest conversion rate among all devices.
• Taking these factors into consideration, the company can create the following 3 top-recommended ad groups:

Shop: 1:1 — Mob — [shop promo code]

Shop: 1:1 — Mob —[ free shipping]

Shop: Exact — Mob — [shop coupon code]

• These ad groups target customers who use mobile devices and are searching for keywords with a 1:1 match or an exact match. The focus is on landing pages related to shop coupons, free shipping, and shop coupon codes, which have shown high click-through rates and conversion rates in the data analyzed.

How can the company optimize its advertising budget to maximize profitability?

• The company spent a total of $635,372 on advertising costs, with the highest spending occurring in November at $282,754. Despite this, the company only saw a profit in October with a profit of $1,046, and the most significant loss was recorded in November. The overall calculation for the loss is $73,409.
• To address this issue, the company should focus on generating more profit through product sales and reducing the cost of advertising.
• To improve the efficiency of advertising efforts, the company can utilize data-driven insights to create new ad groups that are optimized for click-through rates and conversion rates.
• Following 3 ad groups above: These ad groups have been specifically chosen to target customers using mobile devices, with a focus on keyword match using either 1:1 or exact match. The landing pages for these ad groups are centered around promotions and discounts (e.g. Shop Coupon, Free Shipping, Shop Coupon Code) which have been shown to generate high click-through and conversion rates. By focusing on these ad groups, the company can maximize the return on investment (ROI) from its advertising budget and ultimately improve profitability.

Conclusion

• The Marketing Manager can use this analysis to optimize future campaigns by focusing on the “Shop Coupon Code” Ad Group, targeting customers on Mobile devices, using Exact match and 1:1 match Keyword match type, and promoting discounts and offers through the “Shop Coupon” landing page. This will help increase customer engagement and drive sales for CoM.
• The marketing and Data Division could consider monitoring results regularly by dashboard monitor to make sure that it is on track to maximize profitability and re-evaluate its advertising strategy and spending to ensure it is effectively reaching its target audience and generating a positive return on investment (ROI)

Reference

• Vallaeys, F. (2021, June 23). Google phrase match: Everything you need to know. Search Engine Journal. Retrieved January 18, 2023, from https://www.searchenginejournal.com/google-phrase-match-guide/410514/
• Task Management Dashboard on Figma by Collins Donye
• Carlos Silva. (2022 December 15). 12 SEO Techniques to Boost Organic Traffic + Rankings. Retrieved January 29, 2023, from https://www.semrush.com/blog/seo-techniques/

Tableau & GitHub

Dashboard

Repository