Excel Function to Convert a Date to the Month

Today I was attempting to pull a PPC report for historical by month over the last three years and wanted to easily convert the date into a month format so that i could show the YoY trendlines for each full calendar year as a unique line within the chart. It frustrates me that Excel only has a function that converts a date to a number that represents the month. Below is a the quick and dirty XLS formula to take that number representation and convert it to the actual month name.

Access a Notepad++ Version of the XLS code here.

Replace B1 in the example with the date based reference cell and voila!


Happy chart making Thursday to you!

Categories: Uncategorized

Comprehensive PPC Audit Checklist and Scorecard Wijnand Meijer

In March I posted links to Wijnand Meijer’s PPC Audit guide which contains a lot of great insights about auditing your paid search campaigns. I had the pleasure of meeting with Wijnand at the 2015 PPC Hero Conference in Portland, OR last month. If you are still looking at best practices for completing your own PPC Account Audit I’d recommend checking out his presentation Re-evaluating PPC Success, the Value of PPC Audits.

The biggest takeaway from his presentation was his sharing of a new PPC audit scorecard and checklist that takes the best practices from his Certified Knowledge blog series and places it into a single word document.

Happy Account Auditing!

Categories: Uncategorized

Updates to How Bing Matches Keywords

Bing announced two items today. First is that Bing will be removing the opt-out functionality for Close Variant Matching on May 21st, 2015. The second announcement was related to how they match keywords to search queries. On May 21st, 2015, Bing will be adjusting how they normalize queries by adding back in what they called “Stop Words” which means terms like “is, a, an, what, the, and, at, to, of, or, about, for, with, on, from, by, ‘ve” to the search query. Additionally with the normalization it means that Bing will also start to show queries with accent characters like “á, ä, é, ә, í, ö, ó, ú, ü, ñ, ß, ӕ, и.”

Review your keyword list and SQRs to add in normalized queries and special characters by May 21st!

What is the action item? If you are currently bidding on broad match, it might not be much. However if you have a majority of phrase and exact match keywords make sure you’ve evaluated your SQR to add in phrase and exact match keywords with special characters and Stop Words.

Categories: Bing, SEM

The Most In-depth PPC Audit Guide

I spend alot of time reading blogs and coming up with projects and tasks for my PPC team. Earlier this year I started a project of using various audit guides to create an extensive PPC Audit Checklist. As I was combing through hundreds of blogs I came across the following series on Brad Geddes (@bgtheory) Certified Knowledge blog and I was blown away. It was not only the most indepth series I have come across, but it also goes to great detail to explain the WHY’s behind why a specific setting or feature should be used. I am in love! I had been planning on writing my own series, but instead I’m going to piggy back off of this amazingly in-depth series to include some additional insights or information of what else you can include in your account audits.

1. Goal Setting
2. Measurement
3. Campaign Settings & Bid Adjustments
4. Ad Extensions
5. Impression Share and Auction Insights
6. Quality Score
7. Account Structure
8. Keywords and Match Types
9. Ad Copy
10. Testing
11. The Lin Rodinsky Ratio

XLS formula to remove Characters from the Keyword column

January 23, 2015 Leave a comment

One of the projects I am working on is to make a template to help standardize the Search Query Report (SQR) process for adding positive and negative keywords. I’ve been racking my brain for the last two days on how to automate some aspects of the SQR from Adwords so that we can more easily identify words to add as negatives.

I kept on getting stuck on something fairly simple: how to remove the match type identifiers from the keyword column. (ie “, +, [,]). I knew that i could use a substitute function to remove the identifiers, but as I embedded the functions together I kept on receiving an Error value.

So before i get started into the solutions, I want to start with what is the substitute function?

Excel Substitute Function

The excel substitute function is a simple function that allows you to identify a cell and find -something-, replace it with something else, and specify if you want it to happen a certain number of times or in every instance.
=substitute(text, old_text, new_text, [instance number])

So in my example, I want to use substitutes on a phrase match keyword to find the quotation marks surrounding the keyword and replace them with “” (NOTHING.) For broad modified keywords, i.e. +keyword, i want to find the + symbol and again replace it with “” nothing. For exact match keywords i want to identify the brackets [] and again replace them with “” nothing. This will give me just the keyword.

The next step is how to use the substitute function to do this, and where I went wrong…

Remove PPC Keyword Match Type Identifiers from the Keyword Column using Embedded Substitute Functions

A really really smart co-worker, Sam James, came up with the following solution that involves embedded substitutes with right and left trim functions. This works and is 100% functional and it removes all of the match type identifiers from the keyword column.

In this example the KEYOWORD column is B. So we’re going to be referencing cell B2 because it is the first row in column B with a keyword.


As I mentioned this is 100% accurate. However, my brain has a really hard time processing the logic because of its complexity. I knew that there had to be an easier more straight forward method. I also decided that instead of hardcoding in the characters like quotation mark, i.e. “”””” four quotes in a row identifies a single text quotation mark, “[” identifies the left bracket in text, i would use the ASCII character identifier so I would be less likely of screwing up the substitutes.

My original embedded substitute was failing because I kept on leaving a additional comma after the new_text. i.e. =SUBSTITUTE(SUBSTITUTE(B2,CHAR(34),””,),CHAR(91),””,)). My logic was that I wanted to substitute for all instances, however it was expecting a value after the comma so it caused my formula to give me the dreaded #VALUE symbol.

After pulling my hair, screaming bloody murder and wanting to cry. I used Sam’s logic above and removed the extra comma.


Trim removes extra spaces including a proceeding space incase your first word in the keyword phrase was broad modified.
This works! Wohoo. Now on to creating a formula to idenifty when a search query term matches exactly to a exact match term so I know where to add NEGATIVES. :)

When I’m done with all of this goodness it’ll be posted to the PointIt.com blog.

Happy Friday!

Categories: Uncategorized

Humbled for the Nomination of PPC Experts to follow on Twitter

January 12, 2015 Leave a comment

I’m humbled to be included in Search Engine People’s list of individuals nominated by the SEM community of PPC Experts you should follow on Twitter!

If you’re looking for a list of individuals I follow and regularly ask questions to here are some of my most often looked to individuals:

Matt Umbro – @Matt_Umbro 

Ginny Marvin – @GinnyMarvin

Kirk Williams – @PPCKirk

James Svoboda – @Realicity

Bryant Garvin – @BryantGarvin

John Lee – @John_A_Lee

David Szetela – @szetela

John Gagnon – @jmgagnon

Marty Weintraub – @martyweintraub

Matt Van Wager – @mvanwagner

Aaron Levy – @bigalittlea

Julie Bacchini – @neptunemoon

Lisa Sanner – @lisasanner

Luke Alley – @lukealley

Rae Hoffman – @sugarrae

Katy Tonkin – @katytonkin

Brad Geddes – @bgtheory

Elizabeth Marsten – @ebkendo

Sam Owen – @samowenppc

Lisa Raehsler – @lisarocksSEM

Michelle Moorehouse  – @michellemsem

Steve Hammer – @armondhammer

Christine Churchill – @chrischurch

Ruth Burr-Reedy  – @ruthburr

Duane Forrester – @DuaneForrester

John Ellis – @johnwellis

Amy Hoffman – @hoffman8

Jennifer Sleg – @jenstar

Purna Virji – @purnavirji

Bill Sebald – @billsebald

Annie Cushing – @AnnieCushing

Matt Cuts – @mattcutts

Mark Traphagen – @marktraphagen

Jennifer Sable Lopez – @jennita

Dana Di Tomaso – @daniditomaso

Eric Enge – @stonetemple

Brittan Bright – @BrittanBright

Matt Decuir – @mattbasically

Rick Galan – @RickGalan

Jonathon Colman – @jcolman

Greg Gifford – @greggifford

Jabez Lebret – @jabezlabret

Shah Menz – @michellemsem

Matt Siltala – @Matt_Siltala

Mike Arnesen – @Mike_Arnesen

Ian Lurie – @portentint

Pete Meyers @dr_pete

John Doherty – @dohertyjf

Rand Fishkin – @randfish

Danny Sullivan – @dannysullivan

Okay, so it’s not a comprehensive list, and I know that I still missed some awesome digital marketers on this list, but these are a handful of individuals I’d highly recommend you follow to get some wonderful tidbits on PPC, SEO and a few social people.

Categories: Uncategorized

Using PPC Audit Checklists to Prioritize Work

January 4, 2015 Leave a comment

I love what I do! I love working in Search engine marketing however it has a down side –it’s an always on space and there is ALWAYS something more we can/could do. I’m finding that some people feel like they need to work extra hours to stay ahead or just keep up to meet expectations. I’ve been digging in this last week to work on processes to share with my amazing PPC team members at Point It. Not because anyone is doing anything wrong or because I’m finding issues in our accounts, but more so as a tool to help people prioritize their day to day work schedules so they can leave work at the office at the end of the day.

I’ve made it one of my goals for this quarter to automated some of the PPC audit that we do regularly, and to establish processes to help with prioritizing work so they can challenge requests that are busy work and won’t move the needle. My end goals: reduce burn out and keep people happy.

What do I mean by establish PPC processes?

I’m working through a PPC audit checklist for weekly, monthly, and quarterly PPC Audits and tasks. Each of the audits will help uncover areas of opportunity that the account managers know where to focus to improve the performance of the PPC Campaigns that they are managing. Will it be more work? Initially yes, because whenever you start using new processes it takes a while to get used to them. However, if I can audit the PPC Audit process for Adwords via the use of Scripts it will save quite a bit of time.

Here is what I’ve pulled together so far. I’d love your thoughts and opinions:


Weekly PPC Checklist:

  • Campaign Budget Monitoring
  • Performance audit to prioritize which campaigns/ad groups need work
  • Bid Optimization

Monthly PPC Checklist:

  • Search Query Report Analysis  (Bi-Weekly to Monthly depending on the amount of ad spend)
  • Quality Score Audit & Pruning
  • Ad Extensions Refresh / Audit
  • Ad Copy Audit
  • Landing Page Audit
  • Remarketing List Audit

Quarterly PPC Checklist:

  • Comprehensive PPC Audit
    • Goals & Targets Analysis
    • Campaign Settings
    • Ad Extensions Audit
    • Bid Modifiers: Device/Location/Demographic/Ad Scheduling
  • Competitive Analysis & Audit
  • Specific Audits surrounding new features that were launched/released in the previous quarter
  • Develop quarterly test plans
  • Planning for upcoming launches & new campaigns
Categories: Uncategorized

Get every new post delivered to your Inbox.