Information about Excel Tools to Unlock Hidden PPC Data

Published on November 2, 2017

Author: hanapinmarketing

Source: slideshare.net

2. Presenters • Rachael Law – Senior Account Analyst at Hanapin Marketing – PPC Hero Blogger – @Law_Rachael • Krista Hsieh – Account Manager at Hanapin Marketing – PPC Hero Blogger – @kristashort16

3. – Run the world’s most popular PPC blog and conference. – We manage and optimize global paid search, paid social, and display programs. – Within 12 months, brands can expect a 250% increase in their growth trajectory. Who is Hanapin?

4. Our Clients

5. Join the conversation Include the hashtag #thinkppc in your Twitter tweets. Or use the webinar question box to send us questions.

6. Live Poll Question #1 How long have you been in PPC? #thinkppc A. Less than 1 year B. 1-3 years C. 3-5 years D. 5+ years

7. Agenda ● Getting data report ready ● Analysis ● Troubleshooting

8. Getting Your Data Report Ready

9. But will it pivot? Is your data in a format that you can pivot? Is your data laid out in a way that you want to pivot?

10. Text-to-columns

11. Text-to-columns

12. Text-to-columns We want to pivot based on category

13. Text-to-columns

14. Use it for merging data sources VLOOKUP

15. VLOOKUP

16. Lookup_value = what you want to find on the other sheet VLOOKUP

17. Lookup_value = what you want to find on the other sheet Table_array = where you’re looking VLOOKUP

18. Lookup_value = what you want to find on the other sheet Table_array = where you’re looking Col_index_num = which column do you want to return? VLOOKUP

19. Lookup_value = what you want to find on the other sheet Table_array = where you’re looking Col_index_num = which column do you want to return? [range_lookup] = true or false, false = exact match VLOOKUP

20. Index Match Use it as an alternative to VLOOKUP Useful if you have a huge workbook with lots of data, or if you want to search using more than 1 criteria

21. Index Match

22. Index Match Use 2 criteria in your match function by using the “&” operator in between both the lookup values & the lookup arrays

23. Index Match Use 2 criteria in your match function by using the “&” operator in between both the lookup values & the lookup arrays To do this, you must also make the formula an array by pressing Command + Shift + Return

24. Index Match The Match function returns a number corresponding to the position of the matched cell.

25. Index Match The Match function returns a number corresponding to the position of the matched cell. Example: The formula on the right returns “4”, since 13 is in the 4th cell down.

26. Concatenate =CONCATENATE

27. Concatenate =CONCATENATE

28. Cell Formatting TRIM function CLEAN function TEXT function Other numerical formats

29. Cell Formatting TRIM function - removes all spaces from a cell (besides spaces between words)

30. Cell Formatting CLEAN function - removes all non-printable characters

31. Cell Formatting TEXT function - format a cell

32. Analysis IF Functions

33. IF Statements Think of IF functions like a simple if statement: If this set of criteria is true, → do this. If it’s false, → do something else. That’s it!

34. IF Statements Types of IF Statements ● IF ● IFERROR ● SUMIF ● SUMIFS ● AVERAGEIF

35. IF Statements Types of IF Statements IF → If this, then that IFERROR SUMIF SUMIFS AVERAGEIF

36. IF Statements Types of IF Statements IF IFERROR→ If error, then this SUMIF SUMIFS AVERAGEIF

37. IF Statements Types of IF Statements IF IFERROR SUMIF → If this, sum these SUMIFS AVERAGEIF

38. IF Statements Types of IF Statements IF IFERROR SUMIF SUMIFS →If these criteria are true, sum these AVERAGEIF

39. IF Statements Types of IF Statements IF IFERROR SUMIF SUMIFS AVERAGEIF → If this, average these

40. IF Example

41. IFERROR Example

42. SUMIF Example

43. SUMIF Example

44. SUMIFS Example

45. SUMIFS Example

46. SUMIFS Example

47. SUMIFS Example

48. AVERAGEIF Example

49. AVERAGEIF Example

50. Analysis Pivot Tables

51. Pivots Pivot when data is too big for Excel’s filtering option. One category with multiple metrics.

52. Pivots Common PPC pivot tables ● Search query reports ● Placement reports ● Geographic analysis ● Day of week analysis

53. Pivots Where to find the Pivot

54. Pivots Using pivot tables for search query analysis...

55. Pivots How to set up the pivot

56. Pivots Filter for what matters to your account

57. Pivots Analyze the results

58. Pivots Geographic report

59. Pivots Geographic report

60. Pivot Formatting Creating fancy pivot tables...

61. Pivots Client facing pivot tables

62. Pivots Client facing pivot tables

63. Pivots Heat mapping pivots

64. Pivots Geographic report

65. Pivots Client facing pivot tables

66. Budgeting / Heatmaps

67. Analysis Correlation

68. Statistics The Correlation Function

69. Statistics The Correlation Function Just Kidding - Excel will do this for you!

70. Statistics The Correlation Function Are these two things related?

71. Statistics The Correlation Function Your result will be a number from -1 to 1 1 = perfect positive correlation -1 = perfect negative correlation

72. Troubleshooting

73. Common Errors ERROR WHAT IT MEANS // TOP CAUSES FIX IT #VALUE A general error for “something is wrong” Double check your formula and the cells it’s referencing #NA Formula can’t find what it’s been asked to look for. Fix the formula, or wrap it in an IFERROR function #REF! Formula refers to a cell that’s not valid . This happens most often when cells that were referenced by formulas get deleted, or pasted over. Adjust the formula to reference the correct cells #NAME? Typo in the formula name, missing double quotation marks for text values, colon was omitted in a range Check your formula for typos #DIV/0! Whoops, you tried to divide by 0. Luckily instead of the universe imploding, Excel just gives you this error. Try wrapping your formula in an IFERROR

74. It’s returning the wrong values! My Formula Isn’t Giving an Error, But...

75. My Formula Isn’t Giving an Error, But... It’s returning the wrong values! ● QA your formula again ○ Are the correct rows/columns selected? ○ Are you using wildcards correctly? ○ Are you using the right formula? ● Check that the values are actually wrong

76. Offers! A. I’d like to be notified when more information about the Excel Extravaganza comes out! B. I’d like to stay up-to-date on Hanapin’s all-PPC conference, Hero Conf! C. Both D. No Thanks!

77. Live Q&A Time!

78. Thank you for attending our webinar! #thinkppc Contact Us Directly: marketing@hanapinmarketing.com Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero and Hero Conf

## Add a comment