With AdWords, sometimes Excel just doesn’t do the trick. When analyzing very large campaigns, it is sometimes useful to use other tools to navigate through the many past active campaign days of analytic data. MySQL can do this amazingly fast if you know which queries to use.

The first step is to import your whole campaign from AdWords so go to Edit > Export and chose CSV

Use your very favorite tool to Import this file directly into a new database.

From there, you can start the magic. All the data will be in one large table, so you will want to analyzing it in other smaller tables or using queries.

Lets start with the queries,

After the import you may have to clean up one or more of the fields. In my case it is always unwanted quotes that coming the ad fields for Headline, Description Line 1, and Description Line 2. Here is a query that will remove the quotes from the Headline field. Just repeat this process for the others replacing the `Headline` part with the fielt you want to fix, put the characters you want to replace in between the ” ” in the Replace Function (leave it as is if you are trying to remove something completely), then also put the thing you are trying to replace (in this case a quote) in-between the %Percentage% signs.

UPDATE AdWordsDataTable SET `Headline` = REPLACE(`Headline`, ‘”‘, ”) WHERE `Headline` LIKE ‘%”%’;

Repeat this process for each field that contains characters you want to remove. Probably mainly import errors, but you can also replace the + in broad modifiers, the [] in Exact Match, or the ” in Phrase Match if they somehow come into play after using your choice of import methods or AdWords data sources.

To start to analyze the ads, use

SELECT

Headline,

`Description Line 1`,

`Description Line 2`,

FORMAT(

SUM(Impressions / Clicks),

‘%’

) AS CTR,

SUM(Impressions) AS Imp,

SUM(Clicks) AS CLKS,

FORMAT(SUM(Cost), 2) AS Cost,

SUM(Conversions) AS Convs

FROM AdWordsDataTable WHERE Headline IS NOT NULL

GROUP BY Headline ORDER BY Headline;

To Start to Analyze the Positive Keywords Use

SELECT

Keyword,

SUM(Impressions) AS Imp,

SUM(Clicks) AS CLKS,

FORMAT(SUM(Impressions / Clicks),’%’) AS CTR,

FORMAT(SUM(Cost),2) AS Cost,

SUM(Conversions) AS Convs,

`Criterion Type` AS `Match Type`FROM AdWordsDataTable WHERE Keyword IS NOT NULL AND `Criterion Type` NOT LIKE “%Negative%”

GROUP BY Impressions ORDER BY Impressions;

Here is a useful list of SQL functions & Operators for analyzing PPC performance data. Use your own formulas using these functions to analyze whatever metrics you want within your Adwords or Bing data. Follow this link for full list of MySQL Operators & Functions Useful for PPC Analysis.

**Operators**

Name | Description |
---|---|

`AND` , `&&` | Logical AND |

`=` | Assign a value (as part of a `SET` statement, or as part of the `SET` clause in an`UPDATE` statement) |

`:=` | Assign a value |

`BETWEEN ... AND ...` | Check whether a value is within a range of values |

`BINARY` | Cast a string to a binary string |

`&` | Bitwise AND |

`~` | Invert bits |

`|` | Bitwise OR |

`^` | Bitwise XOR |

`CASE` | Case operator |

`DIV` | Integer division |

`/` | Division operator |

`<=>` | NULL-safe equal to operator |

`=` | Equal operator |

`>=` | Greater than or equal operator |

`>` | Greater than operator |

`IS NOT NULL` | NOT NULL value test |

`IS NOT` | Test a value against a boolean |

`IS NULL` | NULL value test |

`IS` | Test a value against a boolean |

`<<` | Left shift |

`<=` | Less than or equal operator |

`<` | Less than operator |

`LIKE` | Simple pattern matching |

`-` | Minus operator |

`% or MOD` | Modulo operator |

`NOT BETWEEN ... AND ...` | Check whether a value is not within a range of values |

`!=` , `<>` | Not equal operator |

`NOT LIKE` | Negation of simple pattern matching |

`NOT REGEXP` | Negation of REGEXP |

`NOT` , `!` | Negates value |

`||` , `OR` | Logical OR |

`+` | Addition operator |

`REGEXP` | Pattern matching using regular expressions |

`>>` | Right shift |

`RLIKE` | Synonym for REGEXP |

`SOUNDS LIKE` | Compare sounds |

`*` | Multiplication operator |

`-` | Change the sign of the argument |

`XOR` | Logical XOR |

String Comparison Functions

Name | Description |
---|---|

`LIKE` | Simple pattern matching |

`NOT LIKE` | Negation of simple pattern matching |

`STRCMP()` | Compare two strings |

String Functions

Name | Description |
---|---|

`ASCII()` | Return numeric value of left-most character |

`BIN()` | Return a string containing binary representation of a number |

`BIT_LENGTH()` | Return length of argument in bits |

`CHAR_LENGTH()` | Return number of characters in argument |

`CHAR()` | Return the character for each integer passed |

`CHARACTER_LENGTH()` | Synonym for CHAR_LENGTH() |

`CONCAT_WS()` | Return concatenate with separator |

`CONCAT()` | Return concatenated string |

`ELT()` | Return string at index number |

`EXPORT_SET()` | Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string |

`FIELD()` | Return the index (position) of the first argument in the subsequent arguments |

`FIND_IN_SET()` | Return the index position of the first argument within the second argument |

`FORMAT()` | Return a number formatted to specified number of decimal places |

`HEX()` | Return a hexadecimal representation of a decimal or string value |

`INSERT()` | Insert a substring at the specified position up to the specified number of characters |

`INSTR()` | Return the index of the first occurrence of substring |

`LCASE()` | Synonym for LOWER() |

`LEFT()` | Return the leftmost number of characters as specified |

`LENGTH()` | Return the length of a string in bytes |

`LIKE` | Simple pattern matching |

`LOAD_FILE()` | Load the named file |

`LOCATE()` | Return the position of the first occurrence of substring |

`LOWER()` | Return the argument in lowercase |

`LPAD()` | Return the string argument, left-padded with the specified string |

`LTRIM()` | Remove leading spaces |

`MAKE_SET()` | Return a set of comma-separated strings that have the corresponding bit in bits set |

`MATCH` | Perform full-text search |

`MID()` | Return a substring starting from the specified position |

`NOT LIKE` | Negation of simple pattern matching |

`NOT REGEXP` | Negation of REGEXP |

`OCT()` | Return a string containing octal representation of a number |

`OCTET_LENGTH()` | Synonym for LENGTH() |

`ORD()` | Return character code for leftmost character of the argument |

`POSITION()` | Synonym for LOCATE() |

`QUOTE()` | Escape the argument for use in an SQL statement |

`REGEXP` | Pattern matching using regular expressions |

`REPEAT()` | Repeat a string the specified number of times |

`REPLACE()` | Replace occurrences of a specified string |

`REVERSE()` | Reverse the characters in a string |

`RIGHT()` | Return the specified rightmost number of characters |

`RLIKE` | Synonym for REGEXP |

`RPAD()` | Append string the specified number of times |

`RTRIM()` | Remove trailing spaces |

`SOUNDEX()` | Return a soundex string |

`SOUNDS LIKE` | Compare sounds |

`SPACE()` | Return a string of the specified number of spaces |

`STRCMP()` | Compare two strings |

`SUBSTR()` | Return the substring as specified |

`SUBSTRING_INDEX()` | Return a substring from a string before the specified number of occurrences of the delimiter |

`SUBSTRING()` | Return the substring as specified |

`TRIM()` | Remove leading and trailing spaces |

`UCASE()` | Synonym for UPPER() |

`UNHEX()` | Return a string containing hex representation of a number |

`UPPER()` | Convert to uppercase |

Mathematical Fuctions

Name | Description |
---|---|

`ABS()` | Return the absolute value |

`ACOS()` | Return the arc cosine |

`ASIN()` | Return the arc sine |

`ATAN2()` , `ATAN()` | Return the arc tangent of the two arguments |

`ATAN()` | Return the arc tangent |

`CEIL()` | Return the smallest integer value not less than the argument |

`CEILING()` | Return the smallest integer value not less than the argument |

`CONV()` | Convert numbers between different number bases |

`COS()` | Return the cosine |

`COT()` | Return the cotangent |

`CRC32()` | Compute a cyclic redundancy check value |

`DEGREES()` | Convert radians to degrees |

`EXP()` | Raise to the power of |

`FLOOR()` | Return the largest integer value not greater than the argument |

`LN()` | Return the natural logarithm of the argument |

`LOG10()` | Return the base-10 logarithm of the argument |

`LOG2()` | Return the base-2 logarithm of the argument |

`LOG()` | Return the natural logarithm of the first argument |

`MOD()` | Return the remainder |

`PI()` | Return the value of pi |

`POW()` | Return the argument raised to the specified power |

`POWER()` | Return the argument raised to the specified power |

`RADIANS()` | Return argument converted to radians |

`RAND()` | Return a random floating-point value |

`ROUND()` | Round the argument |

`SIGN()` | Return the sign of the argument |

`SIN()` | Return the sine of the argument |

`SQRT()` | Return the square root of the argument |

`TAN()` | Return the tangent of the argument |

`TRUNCATE()` | Truncate to specified number of decimal places |

Regular Expressions

Name | Description |
---|---|

`NOT REGEXP` | Negation of REGEXP |

`REGEXP` | Pattern matching using regular expressions |

`RLIKE` | Synonym for REGEXP |