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)

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 platform had the highest number of clicks? What was 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?

Glossary of Terms:

• Platform: A specific social media/advertising channel, like Facebook.
• Impression: An impression represents one advertising view. If five people seen an ad, there are five impressions.
• Click: When a person sees an ad and clicks on it.
• Transaction: A purchase.
• Click-Thru Rate: The percent of views that resulted in a click.
• Conversion Rate: The percent of clicks that resulted in a transaction (purchase).
• ROAS: This is the percent return on ad spend. It is identical to ROI. You may be asked to calculate it using revenue and/or profit.
• Pivot Tables: This is a simple but powerful data analysis tool built into Microsoft Excel. If you havent used pivot tables before (or dont remember how to use them), Ive provided a series of short tutorial videos in the content module.

DATA:

Date Day of Week Channel Impressions Clicks Transactions Revenues 1/1/2021 Monday Facebook 43806 1326 48 $ 7,008.00 1/1/2021 Monday Instagram 42976 1275 35 $ 6,615.00 1/1/2021 Monday TikTok 14998 1833 37 $ 6,993.00 1/1/2021 Monday Twitter 32274 1182 43 $ 6,536.00 1/2/2021 Tuesday Facebook 10263 1959 34 $ 6,290.00 1/2/2021 Tuesday Instagram 41376 540 15 $ 990.00 1/2/2021 Tuesday TikTok 49723 1769 49 $ 6,566.00 1/2/2021 Tuesday Twitter 23944 1893 24 $ 1,824.00 1/3/2021 Wednesday Facebook 35090 1676 17 $ 2,941.00 1/3/2021 Wednesday Instagram 18977 827 39 $ 3,432.00 1/3/2021 Wednesday TikTok 25139 571 34 $ 5,236.00 1/3/2021 Wednesday Twitter 41473 890 23 $ 2,300.00 1/4/2021 Thursday Facebook 43432 1461 36 $ 5,112.00 1/4/2021 Thursday Instagram 47541 1244 34 $ 5,678.00 1/4/2021 Thursday TikTok 47159 1611 16 $ 1,808.00 1/4/2021 Thursday Twitter 35663 823 40 $ 4,760.00 1/5/2001 Friday Facebook 16201 1235 21 $ 3,633.00 1/5/2001 Friday Instagram 45357 1834 50 $ 4,600.00 1/5/2001 Friday TikTok 18855 1655 30 $ 4,560.00 1/5/2001 Friday Twitter 12331 1907 37 $ 3,996.00 1/6/2021 Saturday Facebook 24462 1370 32 $ 3,264.00 1/6/2021 Saturday Instagram 18565 1719 37 $ 2,923.00 1/6/2021 Saturday TikTok 47560 985 43 $ 2,150.00 1/6/2021 Saturday Twitter 44750 1622 27 $ 3,510.00 1/7/2021 Sunday Facebook 41387 1462 33 $ 5,115.00 1/7/2021 Sunday Instagram 47104 1853 40 $ 3,400.00 1/7/2021 Sunday TikTok 19072 1322 46 $ 7,314.00 1/7/2021 Sunday Twitter 15794 703 18 $ 2,808.00 1/8/2021 Monday Facebook 11066 1490 27 $ 1,485.00 1/8/2021 Monday Instagram 49791 1646 10 $ 1,880.00 1/8/2021 Monday TikTok 39031 1377 42 $ 6,720.00 1/8/2021 Monday Twitter 12915 1098 30 $ 2,220.00 1/9/2021 Tuesday Facebook 19850 1355 28 $ 1,820.00 1/9/2021 Tuesday Instagram 30174 141 47 $ 6,063.00 1/9/2021 Tuesday TikTok 12327 1182 43 $ 7,568.00 1/9/2021 Tuesday Twitter 26752 1557 23 $ 2,553.00 1/10/2021 Wednesday Facebook 23050 102 28 $ 2,352.00 1/10/2021 Wednesday Instagram 17594 1214 13 $ 2,340.00 1/10/2021 Wednesday TikTok 21797 1650 16 $ 2,240.00 1/10/2021 Wednesday Twitter 30228 1203 31 $ 2,883.00 1/11/2021 Thursday Facebook 23326 740 48 $ 6,480.00 1/11/2021 Thursday Instagram 45497 1525 17 $ 2,992.00 1/11/2021 Thursday TikTok 44931 460 36 $ 2,664.00 1/11/2021 Thursday Twitter 23143 1566 20 $ 3,820.00 1/12/2021 Friday Facebook 46183 319 34 $ 6,630.00 1/12/2021 Friday Instagram 48337 438 17 $ 1,649.00 1/12/2021 Friday TikTok 42442 1862 37 $ 3,515.00 1/12/2021 Friday Twitter 33160 1562 15 $ 1,620.00 1/13/2021 Saturday Facebook 23637 1154 20 $ 2,560.00 1/13/2021 Saturday Instagram 15556 1733 10 $ 1,140.00 1/13/2021 Saturday TikTok 23987 950 11 $ 1,947.00 1/13/2021 Saturday Twitter 17341 364 24 $ 3,456.00 1/14/2021 Sunday Facebook 47285 430 49 $ 8,624.00 1/14/2021 Sunday Instagram 39824 1057 16 $ 2,384.00 1/14/2021 Sunday TikTok 26295 1724 29 $ 2,813.00 1/14/2021 Sunday Twitter 20449 652 47 $ 4,700.00 1/15/2021 Monday Facebook 13878 609 30 $ 3,690.00 1/15/2021 Monday Instagram 20774 1995 32 $ 2,944.00 1/15/2021 Monday TikTok 12022 1504 24 $ 4,176.00 1/15/2021 Monday Twitter 31068 1039 34 $ 6,562.00 1/16/2021 Tuesday Facebook 47289 1069 41 $ 2,378.00 1/16/2021 Tuesday Instagram 37905 1655 19 $ 3,021.00 1/16/2021 Tuesday TikTok 15546 694 26 $ 4,602.00 1/16/2021 Tuesday Twitter 13990 630 19 $ 1,007.00 1/17/2021 Wednesday Facebook 28508 1942 17 $ 3,349.00 1/17/2021 Wednesday Instagram 46049 1769 30 $ 4,440.00 1/17/2021 Wednesday TikTok 48777 332 37 $ 6,623.00 1/17/2021 Wednesday Twitter 20084 1581 22 $ 4,356.00 1/18/2021 Thursday Facebook 40329 1919 22 $ 1,430.00 1/18/2021 Thursday Instagram 35993 1178 21 $ 1,785.00 1/18/2021 Thursday TikTok 49510 748 21 $ 3,318.00 1/18/2021 Thursday Twitter 42707 585 17 $ 2,159.00 1/19/2021 Friday Facebook 40752 462 29 $ 1,479.00 1/19/2021 Friday Instagram 29052 1666 42 $ 7,014.00 1/19/2021 Friday TikTok 29306 1780 49 $ 9,359.00 1/19/2021 Friday Twitter 24594 342 34 $ 3,434.00 1/20/2021 Saturday Facebook 43841 777 11 $ 1,023.00 1/20/2021 Saturday Instagram 40605 1165 26 $ 2,236.00 1/20/2021 Saturday TikTok 27673 127 28 $ 2,212.00 1/20/2021 Saturday Twitter 26774 1059 12 $ 804.00 1/21/2021 Sunday Facebook 12530 120 36 $ 6,084.00 1/21/2021 Sunday Instagram 34714 232 48 $ 5,424.00 1/21/2021 Sunday TikTok 27482 289 13 $ 2,379.00 1/21/2021 Sunday Twitter 38659 489 45 $ 6,660.00 1/22/2021 Monday Facebook 17757 1784 37 $ 5,402.00 1/22/2021 Monday Instagram 16414 993 18 $ 1,332.00 1/22/2021 Monday TikTok 46665 917 34 $ 6,494.00 1/22/2021 Monday Twitter 15976 1186 39 $ 6,396.00 1/23/2021 Tuesday Facebook 23092 399 47 $ 9,259.00 1/23/2021 Tuesday Instagram 21040 557 30 $ 5,070.00 1/23/2021 Tuesday TikTok 23126 682 11 $ 1,529.00 1/23/2021 Tuesday Twitter 45439 1359 25 $ 3,950.00 1/24/2021 Wednesday Facebook 11590 1195 21 $ 2,604.00 1/24/2021 Wednesday Instagram 33252 317 43 $ 2,580.00 1/24/2021 Wednesday TikTok 45356 1579 35 $ 5,810.00 1/24/2021 Wednesday Twitter 17703 475 32 $ 1,728.00 1/25/2021 Thursday Facebook 22028 170 13 $ 2,132.00 1/25/2021 Thursday Instagram 26710 522 28 $ 1,596.00 1/25/2021 Thursday TikTok 10710 940 13 $ 806.00 1/25/2021 Thursday Twitter 24655 1751 16 $ 1,376.00 1/26/2021 Friday Facebook 10215 1772 12 $ 1,404.00 1/26/2021 Friday Instagram 10495 928 34 $ 5,406.00 1/26/2021 Friday TikTok 13807 1201 48 $ 6,288.00 1/26/2021 Friday Twitter 44867 984 42 $ 5,208.00 1/27/2021 Saturday Facebook 30467 954 30 $ 1,800.00 1/27/2021 Saturday Instagram 14058 1250 31 $ 1,550.00 1/27/2021 Saturday TikTok 18441 979 36 $ 2,448.00 1/27/2021 Saturday Twitter 17430 1024 41 $ 2,911.00 1/28/2021 Sunday Facebook 10517 1058 29 $ 1,450.00 1/28/2021 Sunday Instagram 32065 714 15 $ 2,205.00 1/28/2021 Sunday TikTok 23498 1223 35 $ 4,305.00 1/28/2021 Sunday Twitter 18220 731 47 $ 2,585.00 1/29/2021 Monday Facebook 34951 1349 14 $ 742.00 1/29/2021 Monday Instagram 49929 305 14 $ 840.00 1/29/2021 Monday TikTok 12798 288 42 $ 6,426.00 1/29/2021 Monday Twitter 27801 486 14 $ 2,758.00 1/30/2021 Tuesday Facebook 31396 1882 25 $ 3,350.00 1/30/2021 Tuesday Instagram 23121 235 21 $ 3,360.00 1/30/2021 Tuesday TikTok 43459 497 31 $ 2,821.00 1/30/2021 Tuesday Twitter 47444 126 27 $ 1,728.00 1/31/2021 Wednesday Facebook 24554 115 26 $ 3,198.00 1/31/2021 Wednesday Instagram 28139 794 31 $ 3,596.00 1/31/2021 Wednesday TikTok 33717 1722 47 $ 3,572.00 1/31/2021 Wednesday Twitter 12062 1565 44 $ 5,192.00