Paid Advertising Recommendation for Buhi Supply Co.

 

The Director of Digital Marketing for Buhi Supply Co. is curious if there's an opportunity to improve the return on investment of the paid advertising budget for Buhi in the upcoming year. You've been asked to analyze the data and make a recommendation for them to read when they're away on an upcoming business trip...

 

Buhi's budget for paid advertising this past year was approximately $11,000 and it's currently invested in two strategies across six 3rd party Channels:

Paid Search (SEM)-Google
Paid Search (SEM)-Bing
Paid Social-Facebook
Paid Social-Instagram
Paid Social-LinkedIn
Paid Social-Twitter
 

With a performance-focused goal of maximizing the number of Conversions, the director believes there's an opportunity to streamline the number of paid advertising Channels from six this year to the three for next year. The total paid advertising budget for next year is 'flat' at $11,000; however, the Channels have changed their terms, and each one requires a minimum investment of $3,000.

 

Analyze this year's data following these questions to enable you to make a data-supported recommendation for next year's paid advertising budget:

Questions:

1. What are the three Channels you'd recommend continue to be used next year?

2. How would you allocate the budget across these Channels to maximize the number of Conversions?

3. What's the forecasted change in Conversions next year based on this 'top three Channels' paid advertising strategy?

 
 

Transcribed Image Text:

File Home Insert Page Layout Formulas Data Review View Help 1 PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing F24 fx 6500 A B C D E F G H M N P Q R Average Campaign 1 Type Input Cost per Recommended Source Campaign Budget Impression Impression CTR Clicks 2 Paid Search Bing Yellow Backp $ 104.00 S 3 Paid Social Instagram Duffel Bag $ 234.00 $ 0.04 0.01 2,861 2.0% 21,200 1.0% CPC 65 $ 312 $ Cost 1.58 $ 0.68 $ Budget Max Conversion CPA (Cost Per Conversion Conversion) Rate Revenue Per Return on Investment Conversion Revenue Margin 103.00 212.00 $ S 2,751.00 4 $ 1,285.00 14 $ 25.75 15.14 7.0% $ 5.0% $ 4 Paid Search Bing Duffel Bag S 178.00 $ 0.04 4,140 2.0% 93 $ 1.92 $ 178.00 S 2,818.00 6 $ 29.67 5 Paid Search Bing Satchel $ 196.00 $ 0.02 8,727 2.0% 213 $ 0.90 S 192.00 $ 2,906.00 12 $ 16.00 6 Paid Search Google Travel Bag $ 279.00 $ 0.03 8,387 2.0% 136 $ 1.91 $ 260.00 $ 1,998.00 8 $ 32.50 7.0% $ 6.0% $ 6.0% $ 192.00 $ 768.00 156.00 $ 2,184.00 165.00 $ 990.00 Profits 70% $ 537.60 (ROI) 1 422% 48% $ 1,048.32 394% 79% $ 782.10 339% 7 Paid Social Twitter Duffel Bag $ 257.00 $ 0.01 25,300 1.0% 271 $ 0.93 $ 253.00 $ 2,382.00 10 $ 25.30 4.0% $ 8 Paid Search 9 Paid Search 10 Paid Social Google Google Facebook Pouch $ 164.00 $ 0.03 6,000 3.0% 164 $ 0.92 $ 150.00 $ 527.00 7 $ 21.43 5.0% $ Yellow Backp $ 167.00 $ 0.02 7,455 2.0% 162 $ 1.01 $ 164.00 $ 1,919.00 8 $ 20.50 5.0% $ Tote $ 169.00 S 0.01 11,929 1.0% 160 $ 1.04 $ 167.00 $ 1,235.00 7 $ 23.86 5.0% $ 11 Paid Social 12 Paid Search 13 Paid Search 14 Paid Social 15 Paid Search 16 Paid Social Instagram Bing Bing Pouch $ 195.00 $ 0.01 13,286 1.0% 80 $ 2.33 $ 186.00 $ 2,468.00 4 $ 46.50 4.0% $ Pouch $ 111.00 $ 0.03 3,281 2.0% 56 $ Travel Bag $ 202.00 $ 0.04 4,477 2.0% 95 $ 1.88 $ 2.08 $ 105.00 S 529.00 4 $ 26.25 Twitter Bing Blue Backpac $ Tote 208.00 $ 0.01 19,500 1.0% 252 $ 0.78 $ $ 288.00 $ 0.05 5,878 3.0% 173 $ 1.66 $ 197.00 $ 195.00 S 288.00 S 833.00 2,155.00 6 $ 32.83 11 $ 17.73 789.00 9 $ 32.00 17 Paid Search Facebook Google Duffel Bag $ Tote 259.00 $ 0.01 18,846 1.0% 230 $ 1.07 $ S 228.00 $ 0.04 5,136 3.0% 154 $ 1.47 $ 18 Paid Social Instagram 19 Paid Search Bing 20 Paid Search 21 Paid Social 22 Paid Social 23 Paid Search 24 Paid Search Google Blue Backpad $ Blue Backpac $ 283.00 $ 0.03 10,481 1.0% 133 $ 175.00 $ 0.02 7,130 2.0% 176 $ 2.13 $ 0.93 $ 164.00 $ 245.00 $ 226.00 S 283.00 $ 331.00 5 $ 49.00 869.00 2,287.00 1,912.00 10 $ 22.60 5 $ 56.60 7.0% $ 6.0% $ 4.0% $ 5.0% $ 2.0% $ 6.0% $ 4.0% $ 12 $ 13.67 7.0% $ 81.00 $ 972.00 190.00 $1,520.00 118.00 $ 1,180.00 200.00 $1,400.00 150.00 $ 1,200.00 186.00 $1,302.00 191.00 $ 764.00 101.00 $ 404.00 171.00 $ 1,026.00 61.00 $ 671.00 124.00 $ 1,116.00 188.00 $ 940.00 194.00 $ 1,940.00 196.00 $ 980.00 52.00 $ 624.00 86% $ 835.92 335% 71% $ 1,079.20 315% 84% $ 991.20 292% 41% $ 574.00 283% 52% $ 624.00 280% 47% $ 611.94 266% 89% $ 679.96 266% 89% $ 359.56 242% 58% $ 595.08 202% 84% $ 563.64 189% 74% $ 825.84 187% 73% $ 686.20 180% 30% $ 582.00 158% 72% $ 705.60 149% 64% $ 399.36 144% Bing Rolling Bag $ 111.00 $ 0.04 2,861 2.0% 63 $ Facebook Facebook Google Purse $ 121.00 S 0.01 10,091 1.0% 128 $ 1.63 $103.00 S 0.87 $ 776.00 4 $ 25.75 6.0% $ 111.00 $ 1,135.00 3 $ 37.00 2.0% $ Rolling Bag $ 158.00 $ 0.01 11,846 1.0% 126 $ 1.23 154.00 $ 2,029.00 5 $ 30.80 4.0% $ Blue Backpad $ Duffel Bag $ 123.00 S 0.03 3,294 2.0% 53 $ 2.11 $ 179.00 0.03 6,500 3.0% 167 $ 25 Paid Search Bing Handbag $ 145.00 $ 0.05 3,222 2.0% 78 $ 26 Paid Social Twitter Handbag $ 167.00 S 0.03 6,560 1.0% 68 $ 1.01 $ 1.85 $ 2.40 $ 27 Paid Search 28 Paid Social 29 Paid Search Google Instagram Google Rolling Bag $ 235.00 $ 0.03 8,630 3.0% 257 $ 0.91 $ Satchel $ 110.00 $ 0.02 5,941 1.0% 43 $ 2.33 $ Purse $ 262.00 $ 0.04 6,730 3.0% 171 $ 1.46 $ 112.00 $ 169.00 $ 145.00 S 164.00 $ 233.00 $ 101.00 $ 249.00 $ 841.00 3 $ 37.33 2,121.00 9 $ 18.78 6.0% $ 5.0% $ 1,785.00 4 $ 36.25 486.00 3 $ 54.67 2,911.00 13 $ 17.92 1,873.00 2 $ 50.50 2,893.00 9 $ 27.67 30 Paid Search Google 31 Paid Social Satchel $ 217.00 $ 0.04 5,205 2.0% 91 $ 2.23 $ 32 Paid Social 33 Paid Social 34 Paid Social 35 Paid Social LinkedIn Instagram Facebook Instagram: Instagram Hand Bag S 157.00 $ 0.02 7,895 1.0% 80 $ 1.88 $ 203.00 S 150.00 S 576.00 6 $ 33.83 5.0% $ 5.0% $ 5.0% $ 4.0% $ 5.0% $ 7.0% $ 92.00 $ 368.00 148.00 $ 444.00 97.00 $ 485.00 133.00 $ 399.00 65.00 S 585.00 179.00 $ 716.00 68% $ 250.24 143% 59% $ 261.96 136% 73% $ 354.05 130% 60% $ 239.40 114% 59% $ 345.15 104% 41% $ 293.56 102% 2.590.00 2 $ 75.00 2.0% $ Travel Bag $ Purse 258.00 $ 0.02 11,136 1.0% 105 $ 2.34 $ 245.00 $ 1,091.00 4 $ 61.25 4.0% $ $ 286.00 $ 0.02 17,500 1.0% 121 $ 2.32 $ 280.00 S 2,699.00 3 $ 93.33 3.0% $ Rolling Bag $ 287.00 $ 0.03 9,367 1.0% 111 $ 2.54 $ 281.00 S 2,531.00 4 $ 70.25 3.0% $ Rolling Bag $ 199.00 $ 0.02 10,611 1.0% 139 $ 36 Paid Social 37 Paid Social 30 Danial LinkedIn LinkedIn Blue Backpad $ Satchel $ 181.00 $ 0.02 9,765 1.0% 85 $ 291.00 S 0.02 15,833 1.0% 214 $ 1.37 $ 1.95 $ 1.33 $ 191.00 166.00 S 285.00 S 1,581.00 5 $ 38.20 n 15200 Y 400 202 415.00 593.00 2,755.00 2112.00 2 $ 83.00 8 $ 35.63 3.0% $ 2.0% $ 4.0% $ 162.00 $ 486.00 49.00 $ 637.00 105.00 $ 210.00 134.00 $ 1,206.00 153.00 918.00 162.00 $ 324.00 108.00 $ 432.00 188.00 S 564.00 165.00 $ 660.00 85.00 $ 425.00 145.00 $ 290.00 45.00 S 360.00 60% $ 291.60 78% 62% $ 394.94 70% 78% $ 163.80 62% 33% $ 397.98 60% 35% $ 321.30 58% 72% $ 233.28 56% 72% $ 311.04 63% $ 27% 355.32 27% 54% $ 356.40 27% 53% $ 225.25 65% $ 188.50 89% $ 320.40 18% 14% 12% 10.67 01.00 312.00 100 Comments Share U File Home Insert Page Layout Formulas Data Review View Help 1 PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing F24 fx 6500 A B C D E F G H M N P Q R Average Campaign 1 Type Input Cost per Recommended Source Campaign Budget Impression Impression CTR Clicks 2 Paid Search Bing Yellow Backp $ 104.00 S 3 Paid Social Instagram Duffel Bag $ 234.00 $ 0.04 0.01 2,861 2.0% 21,200 1.0% CPC 65 $ 312 $ Cost 1.58 $ 0.68 $ Budget Max Conversion CPA (Cost Per Conversion Conversion) Rate Revenue Per Return on Investment Conversion Revenue Margin 103.00 212.00 $ S 2,751.00 4 $ 1,285.00 14 $ 25.75 15.14 7.0% $ 5.0% $ 4 Paid Search Bing Duffel Bag S 178.00 $ 0.04 4,140 2.0% 93 $ 1.92 $ 178.00 S 2,818.00 6 $ 29.67 5 Paid Search Bing Satchel $ 196.00 $ 0.02 8,727 2.0% 213 $ 0.90 S 192.00 $ 2,906.00 12 $ 16.00 6 Paid Search Google Travel Bag $ 279.00 $ 0.03 8,387 2.0% 136 $ 1.91 $ 260.00 $ 1,998.00 8 $ 32.50 7.0% $ 6.0% $ 6.0% $ 192.00 $ 768.00 156.00 $ 2,184.00 165.00 $ 990.00 Profits 70% $ 537.60 (ROI) 1 422% 48% $ 1,048.32 394% 79% $ 782.10 339% 7 Paid Social Twitter Duffel Bag $ 257.00 $ 0.01 25,300 1.0% 271 $ 0.93 $ 253.00 $ 2,382.00 10 $ 25.30 4.0% $ 8 Paid Search 9 Paid Search 10 Paid Social Google Google Facebook Pouch $ 164.00 $ 0.03 6,000 3.0% 164 $ 0.92 $ 150.00 $ 527.00 7 $ 21.43 5.0% $ Yellow Backp $ 167.00 $ 0.02 7,455 2.0% 162 $ 1.01 $ 164.00 $ 1,919.00 8 $ 20.50 5.0% $ Tote $ 169.00 S 0.01 11,929 1.0% 160 $ 1.04 $ 167.00 $ 1,235.00 7 $ 23.86 5.0% $ 11 Paid Social 12 Paid Search 13 Paid Search 14 Paid Social 15 Paid Search 16 Paid Social Instagram Bing Bing Pouch $ 195.00 $ 0.01 13,286 1.0% 80 $ 2.33 $ 186.00 $ 2,468.00 4 $ 46.50 4.0% $ Pouch $ 111.00 $ 0.03 3,281 2.0% 56 $ Travel Bag $ 202.00 $ 0.04 4,477 2.0% 95 $ 1.88 $ 2.08 $ 105.00 S 529.00 4 $ 26.25 Twitter Bing Blue Backpac $ Tote 208.00 $ 0.01 19,500 1.0% 252 $ 0.78 $ $ 288.00 $ 0.05 5,878 3.0% 173 $ 1.66 $ 197.00 $ 195.00 S 288.00 S 833.00 2,155.00 6 $ 32.83 11 $ 17.73 789.00 9 $ 32.00 17 Paid Search Facebook Google Duffel Bag $ Tote 259.00 $ 0.01 18,846 1.0% 230 $ 1.07 $ S 228.00 $ 0.04 5,136 3.0% 154 $ 1.47 $ 18 Paid Social Instagram 19 Paid Search Bing 20 Paid Search 21 Paid Social 22 Paid Social 23 Paid Search 24 Paid Search Google Blue Backpad $ Blue Backpac $ 283.00 $ 0.03 10,481 1.0% 133 $ 175.00 $ 0.02 7,130 2.0% 176 $ 2.13 $ 0.93 $ 164.00 $ 245.00 $ 226.00 S 283.00 $ 331.00 5 $ 49.00 869.00 2,287.00 1,912.00 10 $ 22.60 5 $ 56.60 7.0% $ 6.0% $ 4.0% $ 5.0% $ 2.0% $ 6.0% $ 4.0% $ 12 $ 13.67 7.0% $ 81.00 $ 972.00 190.00 $1,520.00 118.00 $ 1,180.00 200.00 $1,400.00 150.00 $ 1,200.00 186.00 $1,302.00 191.00 $ 764.00 101.00 $ 404.00 171.00 $ 1,026.00 61.00 $ 671.00 124.00 $ 1,116.00 188.00 $ 940.00 194.00 $ 1,940.00 196.00 $ 980.00 52.00 $ 624.00 86% $ 835.92 335% 71% $ 1,079.20 315% 84% $ 991.20 292% 41% $ 574.00 283% 52% $ 624.00 280% 47% $ 611.94 266% 89% $ 679.96 266% 89% $ 359.56 242% 58% $ 595.08 202% 84% $ 563.64 189% 74% $ 825.84 187% 73% $ 686.20 180% 30% $ 582.00 158% 72% $ 705.60 149% 64% $ 399.36 144% Bing Rolling Bag $ 111.00 $ 0.04 2,861 2.0% 63 $ Facebook Facebook Google Purse $ 121.00 S 0.01 10,091 1.0% 128 $ 1.63 $103.00 S 0.87 $ 776.00 4 $ 25.75 6.0% $ 111.00 $ 1,135.00 3 $ 37.00 2.0% $ Rolling Bag $ 158.00 $ 0.01 11,846 1.0% 126 $ 1.23 154.00 $ 2,029.00 5 $ 30.80 4.0% $ Blue Backpad $ Duffel Bag $ 123.00 S 0.03 3,294 2.0% 53 $ 2.11 $ 179.00 0.03 6,500 3.0% 167 $ 25 Paid Search Bing Handbag $ 145.00 $ 0.05 3,222 2.0% 78 $ 26 Paid Social Twitter Handbag $ 167.00 S 0.03 6,560 1.0% 68 $ 1.01 $ 1.85 $ 2.40 $ 27 Paid Search 28 Paid Social 29 Paid Search Google Instagram Google Rolling Bag $ 235.00 $ 0.03 8,630 3.0% 257 $ 0.91 $ Satchel $ 110.00 $ 0.02 5,941 1.0% 43 $ 2.33 $ Purse $ 262.00 $ 0.04 6,730 3.0% 171 $ 1.46 $ 112.00 $ 169.00 $ 145.00 S 164.00 $ 233.00 $ 101.00 $ 249.00 $ 841.00 3 $ 37.33 2,121.00 9 $ 18.78 6.0% $ 5.0% $ 1,785.00 4 $ 36.25 486.00 3 $ 54.67 2,911.00 13 $ 17.92 1,873.00 2 $ 50.50 2,893.00 9 $ 27.67 30 Paid Search Google 31 Paid Social Satchel $ 217.00 $ 0.04 5,205 2.0% 91 $ 2.23 $ 32 Paid Social 33 Paid Social 34 Paid Social 35 Paid Social LinkedIn Instagram Facebook Instagram: Instagram Hand Bag S 157.00 $ 0.02 7,895 1.0% 80 $ 1.88 $ 203.00 S 150.00 S 576.00 6 $ 33.83 5.0% $ 5.0% $ 5.0% $ 4.0% $ 5.0% $ 7.0% $ 92.00 $ 368.00 148.00 $ 444.00 97.00 $ 485.00 133.00 $ 399.00 65.00 S 585.00 179.00 $ 716.00 68% $ 250.24 143% 59% $ 261.96 136% 73% $ 354.05 130% 60% $ 239.40 114% 59% $ 345.15 104% 41% $ 293.56 102% 2.590.00 2 $ 75.00 2.0% $ Travel Bag $ Purse 258.00 $ 0.02 11,136 1.0% 105 $ 2.34 $ 245.00 $ 1,091.00 4 $ 61.25 4.0% $ $ 286.00 $ 0.02 17,500 1.0% 121 $ 2.32 $ 280.00 S 2,699.00 3 $ 93.33 3.0% $ Rolling Bag $ 287.00 $ 0.03 9,367 1.0% 111 $ 2.54 $ 281.00 S 2,531.00 4 $ 70.25 3.0% $ Rolling Bag $ 199.00 $ 0.02 10,611 1.0% 139 $ 36 Paid Social 37 Paid Social 30 Danial LinkedIn LinkedIn Blue Backpad $ Satchel $ 181.00 $ 0.02 9,765 1.0% 85 $ 291.00 S 0.02 15,833 1.0% 214 $ 1.37 $ 1.95 $ 1.33 $ 191.00 166.00 S 285.00 S 1,581.00 5 $ 38.20 n 15200 Y 400 202 415.00 593.00 2,755.00 2112.00 2 $ 83.00 8 $ 35.63 3.0% $ 2.0% $ 4.0% $ 162.00 $ 486.00 49.00 $ 637.00 105.00 $ 210.00 134.00 $ 1,206.00 153.00 918.00 162.00 $ 324.00 108.00 $ 432.00 188.00 S 564.00 165.00 $ 660.00 85.00 $ 425.00 145.00 $ 290.00 45.00 S 360.00 60% $ 291.60 78% 62% $ 394.94 70% 78% $ 163.80 62% 33% $ 397.98 60% 35% $ 321.30 58% 72% $ 233.28 56% 72% $ 311.04 63% $ 27% 355.32 27% 54% $ 356.40 27% 53% $ 225.25 65% $ 188.50 89% $ 320.40 18% 14% 12% 10.67 01.00 312.00 100 Comments Share U