Published on November 2, 2017
1. 1 www.dublindesign.com Excel Tools to Unlock Hidden PPC Data HOSTED BY:
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?
12. Text-to-columns We want to pivot based on category
14. Use it for merging data sources 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
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: email@example.com Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero and Hero Conf