MUST COMPLETE ALL 8 ASSIGNMENTS EACH ASSIGNMENT SHOULD HAVE ITS OWN FILE, YOU MUST TURN 8 FILES TOTAL.
Buhis different marketing teams are competing for funding based on past performance. You must determine what efforts are the most effective, so the marketing budget can be allocated to the best performers. Your first job is to download and clean the web, sales, and search ad campaign data.
To clean the data properly, you must find and remove:
Outliers
Impossible values
Incorrect Values
Be careful though. Each data file will require different data-cleaning transformations.
Once youve completed your transformations, youll need to answer questions for the Analytics Report.
Youll also have to recommend marketing budget allocations for search ad campaigns.
Objectives
Youve been given access to three Buhi files, containing multiple sets of data:
Web data
Sales data
Search ad campaign data
You can access these datasets and explore their contents by downloading the attached zip file containing the corresponding CSV files. Open the CSV files in Microsoft Excel or Google Sheets.
Your first task is to clean the data by finding and removing outliers, impossible values, and incorrect values.
*You can clean the data manually or use formulas and functions to speed up the process. Make sure to check the Guides tab inside the Help menu in the top right corner. If you need help using formulas, watch the Excel tutorial videos.
Lets Review the Process
Download and Clean the Data
To clean the data properly, you must find and remove:
Outliers
Impossible values
Incorrect values
Each dataset will require different data-cleaning transformations. Youll come across different types of data in the web file than the sales file. Be sure to adjust your data-cleaning methods accordingly.
Upload Your Cleaned Files
Upload your cleaned file paying close attention to the existing data structures.
Answer Analytics Questions
You will answer a series of questions after youve cleaned each dataset. Then, youll come up with a new budget for search ad campaigns. This should be based on the calculated return on ad spend (ROAS).
Assignments
Assignment 1:
Landing Page – Conversions
Recent inconsistencies and errors in Buhi’s web data have made it hard to collect insightful information. You must clean the web data to move forward with your website analysis.
1 – Download Q1 Web Data
The web data contains the following data:
Conversions
2 – Perform Basic Data Transformations
Remove extreme outliers
Remove incorrect value formats (e.g., age = twelve instead of 12)
Remove impossible values (e.g., age = 203 or state = Canada)
Remove any date outside of Q1 2019 (e.g., date = 07/21/2019)
Note: To remove outliers, impossible values, and incorrect values, clear the individual cells, NOT entire rows. After clearing a cell, simply leave it empty.
3 – Upload Cleaned CSV Files
Accepted File Type(s): csv
File must contain 6 columns with the names: “ID”, “Landing Page”, “Date Range”, “Country”, “Ad Campaign Clicks”, “Converted Sales”
File must contain 390 rows (+- 10) .
Assignment 2 :
Landing Page – Traffic
Recent inconsistencies and errors in Buhi’s web data have made it hard to collect insightful information. You must clean the web data to move forward with your website analysis.
1 – Download Q1 Web Data
The web data contains the following data:
Traffic
2 – Perform Basic Data Transformations
Remove extreme outliers
Remove incorrect value formats (e.g., age = twelve instead of 12)
Remove impossible values (e.g., age = 203 or state = Canada)
Remove any date outside of Q1 2019 (e.g., date = 07/21/2019)
Note: To remove outliers, impossible values, and incorrect values, clear the individual cells, NOT entire rows. After clearing a cell, simply leave it empty.
3 – Upload Cleaned CSV Files: Accepted File Type(s): csv
File must contain 7 columns with the names: “ID”, “Landing Page”, “Date”, “Country”, “Organic Visits”, “Referral Visits”, “Direct Visits”
File must contain 390 rows (+- 10) .
ASSIGNMENTS 3:
Sales – January
Recent inconsistencies and errors in Buhi’s Sales report have made it hard to collect insightful information. You must clean the January sales data to move forward with your Q1 sales analysis.
1 – Download January Sales Data
The Q1 sales data contains the following data:
January product sales
2 – Perform Basic Data Transformations
Remove extreme outliers
Remove incorrect value formats (e.g., age = twelve instead of 12)
Remove impossible values (e.g., age = 203 or state = Canada)
Remove any date outside of Q1 2019 (e.g., date = 07/21/2019)
Note: To remove outliers, impossible values, and incorrect values, clear the individual cells, NOT entire rows. After clearing a cell, simply leave it empty.
3 – Upload Cleaned CSV Files
Upload January Sales Data
Accepted File Type(s): csv
File must contain 8 columns with the names: “ID”, “Name”, “Shipping Address State”, “Product Purchased”, “Order Number”, “Date of Purchase”, “Quantity of Item purchased”, “Price paid per item”
File must contain 1000 rows (+- 20) .
ASSIGNMENT 4
Sales – February
Recent inconsistencies and errors in Buhi’s Sales report have made it hard to collect insightful information. You must clean the February sales data to move forward with your Q1 sales analysis.
1 – Download February Sales Data
The Q1 sales data contains the following data:
February product sales
2 – Perform Basic Data Transformations
Remove extreme outliers
Remove incorrect value formats (e.g., age = twelve instead of 12)
Remove impossible values (e.g., age = 203 or state = Canada)
Remove any date outside of Q1 2019 (e.g., date = 07/21/2019)
Note: To remove outliers, impossible values, and incorrect values, clear the individual cells, NOT entire rows. After clearing a cell, simply leave it empty.
3 – Upload Cleaned CSV Files
Upload February Sales Data
Accepted File Type(s): csv
File must contain 8 columns with the names: “ID”, “Name”, “Shipping Address State”, “Product Purchased”, “Order Number”, “Date of Purchase”, “Quantity of Item purchased”, “Price paid per item”
File must contain 1000 rows (+- 20) .
ASSIGNMENT 5
Sales – March
Recent inconsistencies and errors in Buhi’s Sales report have made it hard to collect insightful information. You must clean the March sales data to move forward with your Q1 sales analysis.
1 – Download March Sales Data
The Q1 sales data contains the following data:
March product sales
2 – Perform Basic Data Transformations
Remove extreme outliers
Remove incorrect value formats (e.g., age = twelve instead of 12)
Remove impossible values (e.g., age = 203 or state = Canada)
Remove any date outside of Q1 2019 (e.g., date = 07/21/2019)
Note: To remove outliers, impossible values, and incorrect values, clear the individual cells, NOT entire rows. After clearing a cell, simply leave it empty.
3 – Upload Cleaned CSV Files
Upload March Sales Data
Accepted File Type(s): csv
File must contain 8 columns with the names: “ID”, “Name”, “Shipping Address State”, “Product Purchased”, “Order Number”, “Date of Purchase”, “Quantity of Item purchased”, “Price paid per item”
File must contain 1000 rows (+- 20) .
ASSIGNMENT 6
Search Ad Campaign
Recent inconsistencies and errors in Buhi’s search ad campaign data have made it hard to collect insightful information. You must clean the search ad campaign data to move forward with your analysis.
1 – Download Q1 Search Ad Campaign Data
The Q1 search ad campaign data contains the following data:
Weekly budget amount
Impressions
Clicks
Conversions
Revenue
2 – Perform Basic Data Transformations
Remove extreme outliers
Remove incorrect value formats (e.g., age = twelve instead of 12)
Remove impossible values (e.g., age = 203)
Remove any date outside of Q1 2019 (e.g., date = 07/21/2019)
3 – Upload Cleaned CSV File
Note: To remove outliers, impossible values, and incorrect values, clear the individual cells, NOT entire rows. After clearing a cell, simply leave it empty.
Upload Search Ad Campaign Data
Accepted File Type(s): csv
File must contain 10 columns with the names: “ID”, “Ad Campaign Name”, “Date”, “Budget allocated to campaign”, “Total impressions from the campaign”, “Total clicks”, “Total Cost”, “Total Conversions”, “Total Revenue”, “Cost Per Click”
File must contain 90 rows (+- 5) .
ASSIGNMENT 7
Analytics Report
Now that youve finished transforming the data, answer the questions below. Make sure to use the cleaned and transformed data for your calculations, rather than the original data files.
Analytics Questions – Basic Data Transformation
Answer the 10 questions below worth a total of 100 points.
In total, how many referral visits were there to all landing pages in Q1 2019?
Enter a whole number (ex. 1, 10, 100, 1000)
How many customers made their first purchase of the quarter in March?
Enter a whole number (ex. 1, 10, 100, 1000)
In total, how many organic visits were there to all landing pages in Q1 2019?
Enter a whole number (ex. 1, 10, 100, 1000)
Using total clicks and total sales by landing page for all of Q1 2019, which landing page had the highest conversion rate?
/backpack/medium/orange
/duffle/leather/blue
/bag/gym/black
/tote/canvas/white
How many items, in total, were purchased in the most recent month?
Enter a whole number (ex. 1, 10, 100, 1000)
Using total clicks and total conversions by campaign for all of Q1 2019, which search ad campaign had the highest conversion rate?
Duffle bags 20% off
Free shipping weekend
Womens bag clearance
New line of travel bags
Which search ad campaign had the highest total revenue in Q1 2019?
New line of travel bags
Backpack sale
Free shipping weekend
Womens bag clearance
Which search ad campaign had the highest total profits in Q1 2019? Your manager defines a search ad campaigns profits as total revenue minus campaign cost.
Duffle bags 20% off
Free shipping weekend
Backpack sale
Womens bag clearance
Which search ad campaign had the highest total clicks in Q1 2019?
New line of travel bags
Free shipping weekend
Womens bag clearance
Backpack sale
Which search ad campaign had the highest return on ad spend (ROAS)? Your manager defines ROAS as a campaigns revenue divided by its cost.
New line of travel bags
Free shipping weekend
Backpack sale
Womens bag clearance
ASSIGNMENT 8
Recommend Budget Allocation
Recommend marketing budget allocations for Buhis search ad campaigns based on your transformed data and the questions you answered.
Recommend Budget Allocations
Based on your decision, you will see a ROAS for next months search ad campaigns. If the data are perfectly cleaned and the budget is allocated wisely, you should see an optimal performance.
Use the boxes below to recommend budget allocations for the search ad campaigns. You must allocate the budget to at least two search ad campaigns. You can allocate a maximum of $5,000 to a single campaign.
