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. At a minimum, calculate the following values: Impressions Clicks Click-Thrus Transactions Conversions Revenues Profits Total Total Clicks Click-Thru Rate Total Transactions Conversion Rate Total Revenues Total Profits Impressions Cost Per 1,000 Impressions Cost Per Click Cost Per Transaction Perhaps most importantly, you should also calculate Return On Ad Spend (ROAs) expressed in both revenues and profits. You will need to add several new columns, formulas, and tabs to the spreadsheet to calculate many of these values, and you will need to consider when it's appropriate to calculate totals, averages, and rates (%). For example, average revenue per click (total revenues/total clicks) will tell you how much revenue you earn for each click on average), but it won't tell you which social media platform generates more clicks, more revenue, or more average revenue per click. Total Impressions Total Clicks Total Transactions Total Revenues 3760350 134942 3717 $ 460,017.00 R S T V W Y Z AA AB AC AD AE Facebook 4,176.00 2,376.00 3,800.00 1,825.00 2,880.00 1,914.00 6,210.00 4,141.00 2,160.00 5,363.00 2,300.00 1,316.00 2,968.00 2,301.00 1,207.00 1,219.00 1,768.00 TikTok 33729 24764 45232 32227 29717 27746 19760 30750 11544 22066 27084 16498 14874 15329 39483 14422 23135 10961 11693 32877 47147 32305 35753 29372 47247 5,238.00 5,900.00 M N P Q 1 Date Day of Week Channel Impressions Clicks Transactions Revenues 1/1/21 Monday Facebook 22805 287 48 $ 9,456.00 1/1/21 Monday Instagram 27850 818 37 $ 1,998.00 1/1/21 Monday TikTok 12495 809 12 $ 1,800.00 1/1/21 Monday Twitter 36058 1096 50 $ 7,200.00 1/2/21 Tuesday Facebook 47761 1277 30 $ 2,280.00 1/2/21 Tuesday Instagram 46208 123 39 $ 7,332.00 1/2/21 Tuesday TikTok 20973 1431 37 $ 4,921.00 1/2/21 Tuesday Twitter 32400 1501 16 $ 1,712.00 1/3/21 Wednesday Facebook 47091 1385 19 $ 1,691.00 1/3/21 Wednesday Instagram 23397 230 23 $ 2,484.00 1/3/21 Wednesday TikTok 44997 1601 17 $ 1,173.00 1/3/21 Wednesday Twitter 23797 1924 19 $ 1,767.00 1/4/21 Thursday Facebook 34889 898 10 $ 880.00 1/4/21 Thursday Instagram 29676 891 27 $ 1/4/21 Thursday TikTok 49790 702 17 $ 1,377.00 1/4/21 Thursday Twitter 45759 651 28 $ 2,996.00 1/5/01 Friday Facebook 38139 784 50 $ 1/5/01 Friday Instagram 18528 489 40 $ 6,160.00 1/5/01 Friday TikTok 35537 1577 24 $ 4,128.00 1/5/01 Friday Twitter 19696 1892 50 $ 3,250.00 1/6/21 Saturday Facebook 11543 1556 31 s 1/6/21 Saturday Instagram 12007 1566 43 $ 4,300.00 1/6/21 Saturday TikTok 17692 1041 19 $ 2,622.00 1/6/21 Saturday Twitter 45645 1796 15 $ 2,955.00 1/7/21 Sunday Facebook 27652 1721 39 $ 5,382.00 1/7/21 Sunday Instagram 20266 1899 49 $ 2,744.00 1/7/21 Sunday TikTok 17434 1139 41 $ 4,633.00 1/7/21 Sunday Twitter 12152 1458 49 $ 5,635.00 1/8/21 Monday Facebook 48800 1454 32 $ 3,584.00 1/8/21 Monday Instagram 14249 1320 32 $ 5,376.00 1/8/21 Monday TikTok 24548 1353 27 $ 3,645.00 1/8/21 Monday Twitter 31684 703 18 $ 2,430.00 1/9/21 Tuesday Facebook 39133 1539 37 $ 6,216.00 1/9/21 Tuesday Instagram 40044 1005 34 $ 4,012.00 1/9/21 Tuesday TikTok 40451 564 26 $ 1/9/21 Tuesday Twitter 32437 1367 36 $ 3,816.00 1/10/21 Wednesday Facebook 46201 348 12 $ 1/10/21 Wednesday Instagram 10229 1582 48 $ 7,248.00 1/10/21 Wednesday TikTok 34969 144 41 $ 4,346.00 1/10/21 Wednesday Twitter 24506 937 36 $ 1/11/21 Thursday Facebook 44247 1799 25 $ 4.125.00 6,360.00 2 1/11/21 Thursday Instagram 1/11/21 Thursday TikTok 1/11/21 Thursday Twitter 1/12/21 Friday 1/12/21 Friday Instagram 1/12/21 Friday TikTok 1/12/21 Friday Twitter 1/13/21 Saturday Facebook 1/13/21 Saturday Instagram 1/13/21 Saturday 1/13/21 Saturday Twitter 1/14/21 Sunday Facebook 1/14/21 Sunday Instagram 1/14/21 Sunday TikTok 1/14/21 Sunday Twitter 1/15/21 Monday Facebook 1/15/21 Monday Instagram 1/15/21 Monday TikTok 1/15/21 Monday Twitter 1/16/21 Tuesday Facebook 1/16/21 Tuesday Instagram 1/16/21 Tuesday TikTok 1/16/21 Tuesday Twitter 1/17/21 Wednesday Facebook 1/17/21 Wednesday Instagram 1/17/21 Wednesday TikTok 1/17/21 Wednesday Twitter 1/18/21 Thursday Facebook 1/18/21 Thursday Instagram 1/18/21 Thursday TikTok 1/18/21 Thursday 1/19/21 Friday Facebook 1/19/21 Friday Instagram 1/19/21 Friday TikTok 1/19/21 Friday Twitter 1/20/21 Saturday Facebook 1/20/21 Saturday Instagram 1/20/21 Saturday TikTok 1/20/21 Saturday Twitter 1/21/21 Sunday Facebook 1/21/21 Sunday Instagram 1/21/21 Sunday TikTok 4,247.00 1604 1115 1851 1521 1869 671 630 558 196 782 1350 1198 742 1465 906 1485 1439 138 148 105 369 161 1285 450 1065 565 1999 1426 1758 1375 1216 940 1932 477 217 1981 121 818 760 1187 1721 1642 48 $ 18 $ 38 $ 25 $ 15 $ 29 $ 46 $ 41 $ 15 $ 31 $ 20 $ 14 $ 28 $ 13 $ 17 $ 23 $ 13 S 40 $ 36 $ 36 $ 22 $ 44 $ 30 $ 18 $ 46 $ 23 $ 44 $ 39 $ 37 $ 16 $ 30 $ 10 $ 27 $ 38 $ 23 $ 45 $ 31 $ 18 $ 39 $ 35 $ 14 $ 19 $ 3 1/21/21 Sunday Twitter 1/22/21 Monday Facebook 1/22/21 Monday Instagram 1/22/21 Monday TikTok 1/22/21 Monday Twitter 1/23/21 Tuesday Facebook 1/23/21 Tuesday Instagram 1/23/21 Tuesday TikTok 1/23/21 Tuesday Twitter 1/24/21 Wednesday Facebook 1/24/21 Wednesday Instagram 1/24/21 Wednesday TikTok 1/24/21 Wednesday Twitter 1/25/21 Thursday Facebook 1/25/21 Thursday Instagram 1/25/21 Thursday TikTok 1/25/21 Thursday Twitter 1/26/21 Friday Facebook 1/26/21 Friday Instagram 1/26/21 Friday TikTok 1/26/21 Friday Twitter 1/27/21 Saturday Facebook 1/27/21 Saturday Instagram 1/27/21 Saturday TikTok 1/27/21 Saturday Twitter 1/28/21 Sunday Facebook 1/28/21 Sunday Instagram 1/28/21 Sunday TikTok 1/28/21 Sunday Twitter 1/29/21 Monday Facebook 1/29/21 Monday Instagram 1/29/21 Monday TikTok 1/29/21 Monday Twitter 1/30/21 Tuesday Facebook 1/30/21 Tuesday Instagram 1/30/21 Tuesday TikTok 1/30/21 Tuesday Twitter 1/31/21 Wednesday Facebook 1/31/21 Wednesday Instagram 1/31/21 Wednesday TikTok 1/31/21 Wednesday Twitter 47267 41797 21296 17829 17192 41682 13602 46316 11833 16444 49447 33762 48249 49622 40383 41730 31324 40174 48879 18710 23456 44881 17381 13441 28885 39640 11327 21080 41864 19312 39021 36151 25604 28102 12844 25641 32979 22398 39274 40984 17665 6,048.00 3,744.00 2,904.00 4,928.00 3,240.00 2,268.00 5,704.00 4,577.00 3,960.00 6,357.00 2,923.00 2,672.00 5,370.00 1,820.00 5,157.00 4,370.00 2,346.00 3,330.00 3,193.00 1,368.00 4,329.00 2,835.00 2,660.00 3,363.00 993 737 896 162 1041 417 1004 308 889 556 1918 1579 427 1557 1167 1719 1888 439 218 410 1370 1098 1742 1732 1422 1868 1210 1732 386 1131 1746 905 207 1707 1266 1851 1212 183 1479 896 1579 44 $ 6,336.00 38 $ 2,356.00 47 $ 2,726.00 27 $ 2,538.00 49 $ 4,851.00 11 $ 770.00 17 $ 1,870.00 12 $ 1,776.00 11 $ 649.00 12 $ 1,860.00 33 $ 5,742.00 39 $ 3,081.00 48 $ 3,120.00 44 $ 3,696.00 29 $ 3,161.00 35 $ 5,145.00 31 $ 6,107.00 45 $ 6,660.00 21 $ 3,591.00 15 $ 2,685.00 33 $ 2,277.00 18 $ 1,692.00 43 $ 6,020.00 29 $ 3,422.00 24 $ 2,376.00 32 $ 4,416.00 31 $ 5,828.00 26 $ 3,250.00 42 $ 5,754.00 11 $ 770.00 46 $ 6,624.00 32 $ 5,376.00 40 $ 4,280.00 18 $ 2,484.00 28 $ 2,940.00 21 $ 2,436.00 37 $ 4,588.00 26 $ 4,992.00 41 $ 5,617.00 10 $ 700.00 44 $ 8,316,00 Twitter 13780 30691 43675 25001 16025 42421 34279 26455 41484 41054 38039 36714 34768 36979 34936 29576 45555 3,666.00 1,896.00 6,768.00 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. At a minimum, calculate the following values: Impressions Clicks Click-Thrus Transactions Conversions Revenues Profits Total Total Clicks Click-Thru Rate Total Transactions Conversion Rate Total Revenues Total Profits Impressions Cost Per 1,000 Impressions Cost Per Click Cost Per Transaction Perhaps most importantly, you should also calculate Return On Ad Spend (ROAs) expressed in both revenues and profits. You will need to add several new columns, formulas, and tabs to the spreadsheet to calculate many of these values, and you will need to consider when it's appropriate to calculate totals, averages, and rates (%). For example, average revenue per click (total revenues/total clicks) will tell you how much revenue you earn for each click on average), but it won't tell you which social media platform generates more clicks, more revenue, or more average revenue per click. Total Impressions Total Clicks Total Transactions Total Revenues 3760350 134942 3717 $ 460,017.00 R S T V W Y Z AA AB AC AD AE Facebook 4,176.00 2,376.00 3,800.00 1,825.00 2,880.00 1,914.00 6,210.00 4,141.00 2,160.00 5,363.00 2,300.00 1,316.00 2,968.00 2,301.00 1,207.00 1,219.00 1,768.00 TikTok 33729 24764 45232 32227 29717 27746 19760 30750 11544 22066 27084 16498 14874 15329 39483 14422 23135 10961 11693 32877 47147 32305 35753 29372 47247 5,238.00 5,900.00 M N P Q 1 Date Day of Week Channel Impressions Clicks Transactions Revenues 1/1/21 Monday Facebook 22805 287 48 $ 9,456.00 1/1/21 Monday Instagram 27850 818 37 $ 1,998.00 1/1/21 Monday TikTok 12495 809 12 $ 1,800.00 1/1/21 Monday Twitter 36058 1096 50 $ 7,200.00 1/2/21 Tuesday Facebook 47761 1277 30 $ 2,280.00 1/2/21 Tuesday Instagram 46208 123 39 $ 7,332.00 1/2/21 Tuesday TikTok 20973 1431 37 $ 4,921.00 1/2/21 Tuesday Twitter 32400 1501 16 $ 1,712.00 1/3/21 Wednesday Facebook 47091 1385 19 $ 1,691.00 1/3/21 Wednesday Instagram 23397 230 23 $ 2,484.00 1/3/21 Wednesday TikTok 44997 1601 17 $ 1,173.00 1/3/21 Wednesday Twitter 23797 1924 19 $ 1,767.00 1/4/21 Thursday Facebook 34889 898 10 $ 880.00 1/4/21 Thursday Instagram 29676 891 27 $ 1/4/21 Thursday TikTok 49790 702 17 $ 1,377.00 1/4/21 Thursday Twitter 45759 651 28 $ 2,996.00 1/5/01 Friday Facebook 38139 784 50 $ 1/5/01 Friday Instagram 18528 489 40 $ 6,160.00 1/5/01 Friday TikTok 35537 1577 24 $ 4,128.00 1/5/01 Friday Twitter 19696 1892 50 $ 3,250.00 1/6/21 Saturday Facebook 11543 1556 31 s 1/6/21 Saturday Instagram 12007 1566 43 $ 4,300.00 1/6/21 Saturday TikTok 17692 1041 19 $ 2,622.00 1/6/21 Saturday Twitter 45645 1796 15 $ 2,955.00 1/7/21 Sunday Facebook 27652 1721 39 $ 5,382.00 1/7/21 Sunday Instagram 20266 1899 49 $ 2,744.00 1/7/21 Sunday TikTok 17434 1139 41 $ 4,633.00 1/7/21 Sunday Twitter 12152 1458 49 $ 5,635.00 1/8/21 Monday Facebook 48800 1454 32 $ 3,584.00 1/8/21 Monday Instagram 14249 1320 32 $ 5,376.00 1/8/21 Monday TikTok 24548 1353 27 $ 3,645.00 1/8/21 Monday Twitter 31684 703 18 $ 2,430.00 1/9/21 Tuesday Facebook 39133 1539 37 $ 6,216.00 1/9/21 Tuesday Instagram 40044 1005 34 $ 4,012.00 1/9/21 Tuesday TikTok 40451 564 26 $ 1/9/21 Tuesday Twitter 32437 1367 36 $ 3,816.00 1/10/21 Wednesday Facebook 46201 348 12 $ 1/10/21 Wednesday Instagram 10229 1582 48 $ 7,248.00 1/10/21 Wednesday TikTok 34969 144 41 $ 4,346.00 1/10/21 Wednesday Twitter 24506 937 36 $ 1/11/21 Thursday Facebook 44247 1799 25 $ 4.125.00 6,360.00 2 1/11/21 Thursday Instagram 1/11/21 Thursday TikTok 1/11/21 Thursday Twitter 1/12/21 Friday 1/12/21 Friday Instagram 1/12/21 Friday TikTok 1/12/21 Friday Twitter 1/13/21 Saturday Facebook 1/13/21 Saturday Instagram 1/13/21 Saturday 1/13/21 Saturday Twitter 1/14/21 Sunday Facebook 1/14/21 Sunday Instagram 1/14/21 Sunday TikTok 1/14/21 Sunday Twitter 1/15/21 Monday Facebook 1/15/21 Monday Instagram 1/15/21 Monday TikTok 1/15/21 Monday Twitter 1/16/21 Tuesday Facebook 1/16/21 Tuesday Instagram 1/16/21 Tuesday TikTok 1/16/21 Tuesday Twitter 1/17/21 Wednesday Facebook 1/17/21 Wednesday Instagram 1/17/21 Wednesday TikTok 1/17/21 Wednesday Twitter 1/18/21 Thursday Facebook 1/18/21 Thursday Instagram 1/18/21 Thursday TikTok 1/18/21 Thursday 1/19/21 Friday Facebook 1/19/21 Friday Instagram 1/19/21 Friday TikTok 1/19/21 Friday Twitter 1/20/21 Saturday Facebook 1/20/21 Saturday Instagram 1/20/21 Saturday TikTok 1/20/21 Saturday Twitter 1/21/21 Sunday Facebook 1/21/21 Sunday Instagram 1/21/21 Sunday TikTok 4,247.00 1604 1115 1851 1521 1869 671 630 558 196 782 1350 1198 742 1465 906 1485 1439 138 148 105 369 161 1285 450 1065 565 1999 1426 1758 1375 1216 940 1932 477 217 1981 121 818 760 1187 1721 1642 48 $ 18 $ 38 $ 25 $ 15 $ 29 $ 46 $ 41 $ 15 $ 31 $ 20 $ 14 $ 28 $ 13 $ 17 $ 23 $ 13 S 40 $ 36 $ 36 $ 22 $ 44 $ 30 $ 18 $ 46 $ 23 $ 44 $ 39 $ 37 $ 16 $ 30 $ 10 $ 27 $ 38 $ 23 $ 45 $ 31 $ 18 $ 39 $ 35 $ 14 $ 19 $ 3 1/21/21 Sunday Twitter 1/22/21 Monday Facebook 1/22/21 Monday Instagram 1/22/21 Monday TikTok 1/22/21 Monday Twitter 1/23/21 Tuesday Facebook 1/23/21 Tuesday Instagram 1/23/21 Tuesday TikTok 1/23/21 Tuesday Twitter 1/24/21 Wednesday Facebook 1/24/21 Wednesday Instagram 1/24/21 Wednesday TikTok 1/24/21 Wednesday Twitter 1/25/21 Thursday Facebook 1/25/21 Thursday Instagram 1/25/21 Thursday TikTok 1/25/21 Thursday Twitter 1/26/21 Friday Facebook 1/26/21 Friday Instagram 1/26/21 Friday TikTok 1/26/21 Friday Twitter 1/27/21 Saturday Facebook 1/27/21 Saturday Instagram 1/27/21 Saturday TikTok 1/27/21 Saturday Twitter 1/28/21 Sunday Facebook 1/28/21 Sunday Instagram 1/28/21 Sunday TikTok 1/28/21 Sunday Twitter 1/29/21 Monday Facebook 1/29/21 Monday Instagram 1/29/21 Monday TikTok 1/29/21 Monday Twitter 1/30/21 Tuesday Facebook 1/30/21 Tuesday Instagram 1/30/21 Tuesday TikTok 1/30/21 Tuesday Twitter 1/31/21 Wednesday Facebook 1/31/21 Wednesday Instagram 1/31/21 Wednesday TikTok 1/31/21 Wednesday Twitter 47267 41797 21296 17829 17192 41682 13602 46316 11833 16444 49447 33762 48249 49622 40383 41730 31324 40174 48879 18710 23456 44881 17381 13441 28885 39640 11327 21080 41864 19312 39021 36151 25604 28102 12844 25641 32979 22398 39274 40984 17665 6,048.00 3,744.00 2,904.00 4,928.00 3,240.00 2,268.00 5,704.00 4,577.00 3,960.00 6,357.00 2,923.00 2,672.00 5,370.00 1,820.00 5,157.00 4,370.00 2,346.00 3,330.00 3,193.00 1,368.00 4,329.00 2,835.00 2,660.00 3,363.00 993 737 896 162 1041 417 1004 308 889 556 1918 1579 427 1557 1167 1719 1888 439 218 410 1370 1098 1742 1732 1422 1868 1210 1732 386 1131 1746 905 207 1707 1266 1851 1212 183 1479 896 1579 44 $ 6,336.00 38 $ 2,356.00 47 $ 2,726.00 27 $ 2,538.00 49 $ 4,851.00 11 $ 770.00 17 $ 1,870.00 12 $ 1,776.00 11 $ 649.00 12 $ 1,860.00 33 $ 5,742.00 39 $ 3,081.00 48 $ 3,120.00 44 $ 3,696.00 29 $ 3,161.00 35 $ 5,145.00 31 $ 6,107.00 45 $ 6,660.00 21 $ 3,591.00 15 $ 2,685.00 33 $ 2,277.00 18 $ 1,692.00 43 $ 6,020.00 29 $ 3,422.00 24 $ 2,376.00 32 $ 4,416.00 31 $ 5,828.00 26 $ 3,250.00 42 $ 5,754.00 11 $ 770.00 46 $ 6,624.00 32 $ 5,376.00 40 $ 4,280.00 18 $ 2,484.00 28 $ 2,940.00 21 $ 2,436.00 37 $ 4,588.00 26 $ 4,992.00 41 $ 5,617.00 10 $ 700.00 44 $ 8,316,00 Twitter 13780 30691 43675 25001 16025 42421 34279 26455 41484 41054 38039 36714 34768 36979 34936 29576 45555 3,666.00 1,896.00 6,768.00