Module 9 Assignment This Module Assignment is a little different than previous Module Assignments. You will apply skills you learned in your School of Business prerequisites (specifically math, business communication, accounting/finance/Excel, and lower-division business classes) to a "real world" marketing scenario. I will be evaluating two of the Undergraduate Business Program Learning Goals, Problem Solving & Decision Making and Business Communication (LG1 & LG3). Detailed formatting guidelines and grading rubrics for each Learning Goal can be found at the end of this document. You are a digital marketing intern at a small footwear company located in Portland, OR. You have been asked to evaluate the financial performance of your social media advertising campaigns and make recommendations about how to allocate your digital advertising budget. Your manager gave you the following information: Online sales earn a 12% profit margin. Your monthly social media advertising budget is $20,000 You currently allocate 30% to Facebook, 30% to Instagram, 20% to TikTok, and 20% to Twitter. Part 1: Data Analysis Download the Campaign Data file attached to the Assignment Dropbox. Use pivot tables and formulas to evaluate this data. For example, this screenshot is calculating total clicks, grouped by social media channel, and by day of the week. (Pro Tip: Notice how I used conditional formatting to highlight the highest and lowest values in each data range) Sum of Clicks Column Labels Row Labels Sunday Facebook 1730 1401 2661 17602 Instagram Monday Tuesday Wednesday Thursday Friday Saturday (blank) Grand Total 2768 2877 3107 1623 3053 2025 3094 2118 1957 2366 2518 1853 882 14289 3058 2335 2323 2271 TikTok 2021 1788 2526 15827 Twitter 4168 3780 1758 1387 1971 17701 (blank) Grand Total 9851 12216 10869 9987 8027 6429 8040 65419 Include all of your calculations, pivot tables, and graphs in your excel document (in other words, "show all of your work"). Calculate the following values and enter them in the "Answer Sheet" tab of the Campaign Data file: 1. Which pla had the highest number of clicks? it? 2. Which platform had the lowest number of clicks? What was it? 3. Which day of the week had the highest number of clicks? What was it? 4. Which day of the week had the lowest number of clicks? What was it? 5. Which platform had the highest Click-Thru rate? What was it? 6. Which platform had the lowest Click-Thru rate? What was it? 7. Which day of the week had the highest Click-Thru rate? What was it? 8. Which day of the week had the lowest Click-Thru rate? What was it? 9. Which platform had the highest conversion rate? What was it? 10. Which platform had the lowest conversion rate? What was it? 11. Which day of the week had the highest conversion rate? What was it? 12. Which day of the week had the lowest conversion rate? What was it? 13. Which platform had the highest total revenue? What was it? 14. Which platform had the lowest total revenue? What was it? 15. Which day of the week had the highest total revenue? What was it? 16. Which day of the week had the lowest total revenue? What was it? 17. Which Platform had the highest Average Revenue per Transaction? What was it? 18. Which Platform had the lowest Average Revenue per Transaction? What was it? 19. Which day of the week had the highest Average Revenue per Transaction? What was it? 20. Which day of the week had the lowest Average Revenue per Transaction? What was it? 21. Which platform had the highest total profit? What was it? 22. Which platform had the lowest total profit? What was it? 23. Which day of the week had the highest total profit? What was it? 24. Which day of the week had the lowest total profit? What was it? 25. Which Platform had the highest Average Profit per Transaction? What was it? 26. Which Platform had the lowest Average Profit per Transaction? What was it? 27. Which day of the week had the highest Average Profit per Transaction? What was it? 28. Which day of the week had the lowest Average Profit per Transaction? What was it? 29. Which platform had the highest ROAS (Revenue)? What was it? 30. Which platform had the lowest ROAS (Revenue)? What was it? 31. Which platform had the highest ROAS (Profit)? What was it? 32. Which platform had the lowest ROAS (Profit)? What was it? Date Day of Week 1/1/2021 Monday 1/1/2021 Monday Channel Facebook Instagram 1/1/2021 Monday TikTok 1/1/2021 Monday Twitter 1/2/2021 Tuesday Facebook 1/2/2021 Tuesday Instagram TikTok 1/2/2021 Tuesday 1/2/2021 Tuesday Twitter Facebook Instagram TikTok 1/3/2021 Wednesday Facebook 1/3/2021 Wednesday Instagram 1/3/2021 Wednesday TikTok 1/3/2021 Wednesday Twitter 1/4/2021 Thursday 1/4/2021 Thursday 1/4/2021 Thursday 1/4/2021 Thursday 1/5/2001 Friday 1/5/2001 Friday 1/5/2001 Friday 1/5/2001 Friday 1/6/2021 Saturday 1/6/2021 Saturday Twitter Facebook Instagram TikTok Twitter Facebook Instagram TikTok 1/6/2021 Saturday 1/6/2021 Saturday Twitter Facebook 1/7/2021 Sunday 1/7/2021 Sunday Instagram TikTok 1/7/2021 Sunday 1/7/2021 Sunday 1/8/2021 Monday 1/8/2021 Monday 1/8/2021 Monday 1/8/2021 Monday 1/9/2021 Tuesday 1/9/2021 Tuesday 1/9/2021 Tuesday Twitter Facebook Instagram TikTok Twitter Facebook Instagram TikTok 1/9/2021 Tuesday 1/10/2021 Wednesday 1/10/2021 Wednesday 1/10/2021 Wednesday 1/10/2021 Wednesday Twitter Facebook Instagram TikTok Twitter 1/11/2021 Thursday Facebook 1/11/2021 Thursday Instagram TikTok 1/11/2021 Thursday 1/11/2021 Thursday Twitter 1/12/2021 Friday Facebook 1/12/2021 Friday Instagram TikTok 1/12/2021 Friday 1/12/2021 Friday 1/13/2021 Saturday 1/13/2021 Saturday Twitter Facebook Instagram TikTok Twitter 1/13/2021 Saturday 1/13/2021 Saturday 1/14/2021 Sunday 1/14/2021 Sunday Facebook Instagram TikTok 1/14/2021 Sunday 1/14/2021 Sunday Twitter 1/15/2021 Monday Facebook. Impressions Clicks Transactions Revenues 43806 1326 42976 1275 14998 1833 32274 1182 48 $ 7,008.00 35 $ 6,615.00 37 $ 6,993.00 43 $ 6,536.00 10263 1959 34 $ 6,290.00 15 $ 990.00 49 $6,566.00 24 $ 1,824.00 17 $ 2,941.00 41376 540 49723 1769 23944 1893 35090 1676 18977 827 25139 571 41473 890 43432 1461 47541 1244 47159 1611 39 $ 3,432.00 34 $ 5,236.00 23 $ 2,300.00 36 $5,112.00 34 $ 5,678.00 16 $ 1,808.00 40 $ 4,760.00 35663 16201 823 1235 21 $ 3,633.00 45357 1834 50 $ 4,600.00 30 $4,560.00 18855 1655 12331 1907 37 $ 3,996.00 32 $3,264.00 37 $2,923.00 43 $ 2,150.00 27 $ 3,510.00 33 $5,115.00 40 $3,400.00 46 $ 7,314.00 18 $ 2,808.00 24462 1370 18565 1719 47560 985 44750 1622 41387 1462 47104 1853 19072 1322 15794 703 11066 1490 49791 1646 39031 1377 12915 1098 19850 1355 30174 141 12327 1182 26752 1557 23050 102 17594 1214 21797 1650 30228 1203 23326 740 45497 1525 44931 460 23143 1566 46183 319 27 $ 1,485.00 10 $ 1,880.00 42 $ 6,720.00 30 $2,220.00 28 $ 1,820.00 47 $6,063.00 43 $ 7,568.00 23 $2,553.00 28 $ 2,352.00 13 $2,340.00 16 $2,240.00 31 $ 2,883.00 48 $ 6,480.00 17 $ 2,992.00 36 $ 2,664.00 20 $ 3,820.00 34 $ 6,630.00 48337 438 42442 1862 33160 1562 23637 1154 15556 1733 23987 950 17341 364 47285 430 39824 1057 26295 1724 20449 652 13878 609 17 $ 1,649.00 37 $ 3,515.00 15 $1,620.00 20 $2,560.00 10 $ 1,140.00 11 $ 1,947.00 24 $3,456.00 49 $8,624.00 16 $ 2,384.00 29 $ 2,813.00 47 $4,700.00 30 $3,690.00 1/15/2021 Monday 1/15/2021 Monday 1/15/2021 Monday 1/15/2021 Monday 1/16/2021 Tuesday 1/16/2021 Tuesday 1/16/2021 Tuesday 1/16/2021 Tuesday 1/17/2021 Wednesday 1/17/2021 Wednesday 1/17/2021 Wednesday TikTok 1/17/2021 Wednesday Twitter 1/18/2021 Thursday 1/18/2021 Thursday Facebook Instagram TikTok 1/18/2021 Thursday 1/18/2021 Thursday Twitter 1/19/2021 Friday Facebook Instagram TikTok 1/19/2021 Friday 1/19/2021 Friday 1/19/2021 Friday 1/20/2021 Saturday Twitter Facebook 1/20/2021 Saturday 1/20/2021 Saturday Instagram TikTok 1/20/2021 Saturday Twitter 1/21/2021 Sunday Facebook 1/21/2021 Sunday Instagram TikTok 1/21/2021 Sunday 1/21/2021 Sunday Twitter 1/22/2021 Monday Facebook 1/22/2021 Monday Instagram 1/22/2021 Monday TikTok 1/22/2021 Monday Twitter 1/23/2021 Tuesday Facebook Instagram TikTok Twitter 1/23/2021 Tuesday 1/23/2021 Tuesday 1/23/2021 Tuesday 1/24/2021 Wednesday 1/24/2021 Wednesday 1/24/2021 Wednesday 1/24/2021 Wednesday 1/25/2021 Thursday 1/25/2021 Thursday 1/25/2021 Thursday 1/25/2021 Thursday 1/26/2021 Friday 1/26/2021 Friday Facebook Instagram TikTok Twitter Facebook Instagram TikTok Twitter Facebook Instagram TikTok 1/26/2021 Friday 1/26/2021 Friday 1/27/2021 Saturday Twitter Facebook Instagram TikTok 1/27/2021 Saturday 1/27/2021 Saturday 1/27/2021 Saturday 1/28/2021 Sunday Twitter Facebook Instagram TikTok 1/28/2021 Sunday 1/28/2021 Sunday 1/28/2021 Sunday Twitter 1/29/2021 Monday Facebook 1/29/2021 Monday Instagram 1/29/2021 Monday TikTok 1/29/2021 Monday Twitter Facebook Instagram TikTok Twitter Facebook Instagram TikTok Twitter Facebook Instagram 13878 20774 12022 609 1995 1504 31068 1039 47289 1069 37905 1655 15546 13990 28508 1942 694 630 46049 1769 48777 332 20084 40329 1581 1919 35993 1178 49510 748 42707 585 462 40752 29052 1666 29306 1780 24594 43841 342 777 40605 1165 127 1059 27673 26774 12530 120 34714 232 27482 289 38659 489 17757 1784 16414 993 46665 917 15976 1186 23092 399 21040 557 23126 682 45439 1359 1195 33252 317 11590 45356 1579 17703 475 22028 170 26710 522 940 10710 24655 1751 10215 1772 10495 928 13807 1201 44867 984 30467 954 14058 1250 18441 979 17430 1024 10517 1058 32065 714 23498 1223 18220 731 34951 1349 49929 305 12798 288 27801 486 30 $3,690.00 32 $ 2,944.00 24 $4,176.00 34 $6,562.00 41 $ 2,378.00 19 $3,021.00 26 $4,602.00 19 $ 1,007.00 17 $ 3,349.00 30 $ 4,440.00 37 $6,623.00 22 $ 4,356.00 22 $ 1,430.00 21 $ 1,785.00 21 $3,318.00 17 $ 2,159.00 29 $ 1,479.00 42 $ 7,014.00 49 $9,359.00 34 $3,434.00 11 $ 1,023.00 26 $2,236.00 28 $2,212.00 12 $ 804.00 36 $ 6,084.00 48 $ 5,424.00 13 $ 2,379.00 45 $ 6,660.00 37 $ 5,402.00 18 $ 1,332.00 34 $ 6,494.00 39 $ 6,396.00 47 $9,259.00 30 $ 5,070.00 11 $ 1,529.00 25 $3,950.00 21 $ 2,604.00 43 $2,580.00 35 $ 5,810.00 32 $ 1,728.00 13 $ 2,132.00 28 $ 1,596.00 13 $ 806.00 16 $ 1,376.00 12 $ 1,404.00 34 $ 5,406.00 48 $ 6,288.00 42 $5,208.00 30 $ 1,800.00 31 $1,550.00 36 $ 2,448.00 41 $ 2,911.00 29 $1,450.00 15 $2,205.00 35 $ 4,305.00 47 $ 2,585.00 14 $ 742.00 14 $ 840.00 42 $ 6,426.00 14 $ 2.758.00 Metric Day/Platform Value Format 1 Number 2 Number 3 Number 4 Number 5 Percentage 6 Percentage 7 Percentage 8 Percentage 9 Percentage 10 Percentage 11 Percentage 12 Percentage 13 Currency 14 Currency 15 Currency 16 Currency 17 Currency 18 Currency 19 Currency 20 Currency 21 Currency 22 Currency 23 Currency 24 Currency 25 Currency 26 Currency 27 Currency 28 Currency 29 Percentage 30 Percentage 31 Percentage 32 Percentage