Help with analysing AdWords results

Hi All,

I need help working through the statistics of Google AdWords campaigns. I'm trying to analyse the performance of certain keywords and phrases that are used over multiple campaigns. I've attached an example problem that I made up to help you understand what I mean Example Problem.xlsx. I do not mind if I have to make a template and copy the results (statistics sheet in example) and relevant keywords in to get the final outcome.

Currently I've been using SUMIF functions with wildcards to sort out the performance of keywords but only just realised that it will include valid values (or keywords) that I do not want i.e. Want Clicks of "Luxury..." and not Clicks for "Luxury Garden...". I need to know a way to do a SUMIF and COUNTIF where I can exclude these valid values.

Need to be able to SUMIF phrases that may have a variable in the middle i.e. "phrase X phrase" where X is variable.

Also, I need to calculate the average Cost-Per-Click (CPC) for keywords and phrases without including some valid values (same issue as above), and not dividing by valid keywords that have $0 CPC or could use 0 clicks as the value (in the example I accidentally have keywords with 0 clicks having an avg CPC). There are 2 ways I can potentially do this that I can see, A) with AVERAGEIF formula or B) using COUNTIF and dividing the SUMIF of valid CPC's by a COUNTIF of valid keywords.

I would prefer to make a template where I could literally just copy keywords and the address in and it spits the values out i.e. automatically searches both arrays for valid values and exclusion values/keywords. I'm making a master copy where I'm summing multiple campaigns results to spit out a performance of each keyword under multiple tests.

SUMMARY:

• Need to Sum multiple values if valid, yet exclude some valid values e.g. Clicks for "Luxury..." but not for "Luxury gardens"
• Need to Average Cost-per-click (CPC) of valid values yet exclude some valid values (same as above)
• Need to Average Cost-per-click (CPC) of valid values yet exclude resulting value that is less than 0 i.e. avg = #/Total, exclude count in total of valid values less than 0
• Need to Sum phrases that have changing variable in the middle e.g."phrase X phrase" where X is a changing variable
• Would like to make it automatic i.e. searches both array's for valid and exclusion values e.g. searches array one for exclusion values that have keyword in them, then searches array two for valid values and SUMS valid except the exclusion values
• Can be combined into a master template where I can copy paste values in and it automatically works out values

I know this is hectic, and I'm really hoping excel has the power to do this!

Thank you!

Hi Glenn,

Thanks for the prompt reply.

I've had this thought as well, but I don't want to have to enter in every exclusion value. There may be 10+ exclusions in a campaign for example. I want it to automatically exclude all keywords in the column except the keyword I'm after.

Thanks mate.

I hope that still means it's possible haha

Any luck Glenn?

Hi Glenn,

What does the <> do in the formula?

Of course it does

Originally Posted by Glenn Kennedy

...........You can do this (maybe a bit tedious to set up)

=SUMIFS(C:C,A:A,"*luxury*",A:A,"<>*china*",A:A,"<>*alabama*",A:A,"<>*garden*")

...........C'mon Gurus. Embarrass me... what am I doing wrong? Show us how!!

Not a Guru, and at risk of embarrassing myself I put a lookup table (alabama, china, garden) in R2:R4. I entered this array formula in cell G2 and filled down.

Formula:

Please Login or Register  to view this content.

Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

BTW: I couldn’t tell if the desired outcome is a single formula or row by row. It returned “Clicks” for “luxury for sale” only (20).

This will require more lookup tables I’m sure……or several helper columns.

Any chance this gets us started in workable direction?

Won't let me edit my post for some reason. The Xlist in the formula is that lookup table.

I should be able to test it tomorrow. Bit swamped at work at the moment to be able to think about it