Question? 800.340.3844

Stats First - Insights into Internet Marketing Blog

AdWords to MySQL

Posted on by .

Did you know it’s possible to write AdWords data directly to a database (Google Cloud SQL, MySQL, SQL Server, etc.)? I’ve seen little mention of it, but Google announced in March that JDBC is now supported in AdWords Scripts.

Here’s an example for how to write your complete list of AdWords Keywords into a table on Google Cloud SQL (MySql). Please note: I am not a programmer. I simply modify example code to accomplish what I want. There very well could be a better, faster, more secure way to perform the same function.

CODE

//Get list of Keywords
var report = AdWordsApp.report(
'SELECT ExternalCustomerId, CampaignId, AdGroupId, Id, KeywordText, KeywordMatchType, 
FinalUrls ' +
'FROM KEYWORDS_PERFORMANCE_REPORT'); 

//Connect to Database
var conn = Jdbc.getConnection(dbUrl, user, userPwd);
conn.setAutoCommit(false);

//Update Keyword Table 
var stmt = conn.prepareStatement('REPLACE INTO KEYWORDS '
 + '(ExternalCustomerId, CampaignId, AdGroupId, KeywordId, KeywordText, 
KeywordMatchType, FinalUrls) values (?, ?, ?, ?, ?, ?, ?)');
 
var rows = report.rows(); 
 while (rows.hasNext()) {
 var row = rows.next();
 
 stmt.setLong(1, row["ExternalCustomerId"]);
 stmt.setLong(2, row["CampaignId"]);
 stmt.setLong(3, row["AdGroupId"]);
 stmt.setLong(4, row["Id"]);
 stmt.setString(5, row["KeywordText"]);
 stmt.setString(6, row["KeywordMatchType"]);
 stmt.setString(7, row["FinalUrls"]);
 stmt.addBatch();
 }

 var batch = stmt.executeBatch();
 conn.commit();
 conn.close();

END CODE

Instead of using third party programs, importing data from downloaded reports or using convoluted php scripts, you can use relatively simple AdWords Scripts and automate the process. The example above was simply for a list of Keywords, but think of all the data available
in AdWords Scripts and having access to that in your own database!

For more details on using JDBC with AdWords Scripts, please see Apps Script Developers Guide.

 

 

Related Posts:

  • No Related Posts

Tiered PPC Bidding Strategy Rectified

Posted on by .

Yesterday Justin Freid posted an article on Search Engine Watch, How to Implement a Tiered PPC Bidding Strategy. While I agree with Justin’s premise that you should be bidding on all three match types, I don’t believe he has the approach correct.

In Paid Search, as in other advertising channels, Marketers strive for control – if I decrease the bid on keyword X, I can expect impressions, clicks and cost to decline. Unless you have each keyword + match type in their own campaign, with the keywords from the lower level match type as a negative keyword, you don’t have that control – impressions will migrate from one keyword to another.

Continue reading

Related Posts:

Determining your Max Cost per Lead

Posted on by .

For most business Sales, not Leads, are most important; however, there are often both Lead and Sales goals. If Sales are having a good month, but Leads are off, it would be foolish to cut back on Spend or attempt to switch tactics to try to fix the Lead shortfall, as you would be giving up Revenue. But what if Sales happen over time? That is, what if a Lead today might not make a purchase until days / weeks / months later. In that case, you may want to make some adjustments, as there is an opportunity cost in the Lead shortfall.

In this article and in the attached Excel spreadsheet, Max CPL Worksheet, I’ll attempt to show you how to account for a shortfall in Leads or in the Revenue per Sale (RPS) as you optimize your campaigns during the month.

Continue reading

Related Posts:

Paid Search Math

Posted on by .

I’d like to show you how metrics are interrelated in Paid Search and how that can influence the tactics you take to meet your goals and the perception of your campaign’s performance.

For all of the different variables used in Paid Search (Impressions, Clicks, CPC, Cost, Leads, CPL, ROAS, etc.) each can be a Dependent (Result), Independent (Input) or Antecedent (Influence) variable. In addition, each variable can be used as a Goal in and of itself, as a Result or as an Input in optimization.

Continue reading

Related Posts:

Welcome

Posted on by .

Welcome to the Stats First – Insights into Internet Marketing blog. This is my “Hello World” post. I hope to have more useful articles posted soon, but for now, Welcome.

Related Posts:

  • No Related Posts