Tableau Dashboard- MySQL & AdWords

Now use the power of Tableau to find business insights for AdWords data. With this implement you can forget the days of manually uploading .csv files into Tableau Software to perform data analysis. Now you have to just connect to your database, build dashboard, storyline and many more things.

With the help of Tableau Online tool, you can create dashboard with live connection to your database. What it means? Its means you have create dashboard only one time and management or your clients can directly view the dashboard on their mobile or tablets to check daily Google AdWords campaigns performance.

Click here to view final outcome : https://public.tableau.com/views/MySQL-AdWordsConnectionDemo/CampaignsPerformance?:embed=y&:display_count=yes&:showTabs=y

Follow below steps to implement this code:

If you already have your own MySQL, Microsoft SQL Server, or Oracle database, you can connect to it through Apps Script’s JDBC service. First, you need to ensure that your database accepts connections from any of Apps Script’s IP addresses. These are the address ranges you’ll need to whitelist:

 64.18.0.0 - 64.18.15.255
 64.233.160.0 - 64.233.191.255
 66.102.0.0 - 66.102.15.255
 66.249.80.0 - 66.249.95.255
 72.14.192.0 - 72.14.255.255
 74.125.0.0 - 74.125.255.255
 173.194.0.0 - 173.194.255.255
 207.126.144.0 - 207.126.159.255
 209.85.128.0 - 209.85.255.255
 216.239.32.0 - 216.239.63.255

Note that the JDBC service can only connect to ports 1025 and above. Ensure your database is not serving off a lower port.

For More Information visit: https://developers.google.com/adwords/scripts/docs/features/external-data#jdbc

How to Whitlist above IP CIDR in your Database.

1. Login into your cpanel

2. After successful login into cpanel, scroll down to Databases section.

3. Click on Remote MySQL. as show in the below snapshot

4. Enter all IP Address, which are mentioned above one by one to enable remote connection to your database via Google Apps Scripts.

E.g. To add 64.18.0.0 – 64.18.15.255 IP CIDR as valid host, you can use wildcard. It will save your time and effort.
I will write 64.18.%.%, this will cover all ip address ranging from 64.18.0.0 to 64.18.15.255.

P.S This could be vulnerable to external attacks. Try to avoid usage of wildcard in host entry.

Writing Code Into AdWords Panel:

1. Open your AdWords Account panel by signing in at adwords.google.com. Then go to Bulk Operations tab where you need to click Scripts.

2. Then click on +Script in red color.

This will send AdWord Account data directly to your MySQL database.

Replace the following with actual credentials:
1. ADDRESS: It is the database address/url
2. DB_NAME
3. DB_USER
4. DB_PASSWORD

//This part of code will fetch data for specified metrics, you can modify the number of metric and type of report. For more infomation you need to check out AWQL on AdWords Developers Guide.
function main() {
var report = AdWordsApp.report(
'SELECT Date, CampaignName, Impressions, Clicks, Cost, Conversions, ViewThroughConversions ' +
'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
'WHERE Impressions > 0 ' +
'DURING YESTERDAY ');
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var campaignDate = row['Date'];
var campaignName = row['CampaignName'];
var impressions = row['Impressions'];
var clicks = row['Clicks'];
var cost = row['Cost'];
var conversions = row['Conversions'];
var viewThroughConversions = row['ViewThroughConversions'];

var dbUrl="jdbc:mysql://ADDRESS:3306/DB_NAME";
var user='DB_USER';
var userPwd='DB_PASSWORD';

// Write rows of data to a table in a single batch.
//function writeManyRecords() {
var conn = Jdbc.getConnection(dbUrl, user, userPwd);
conn.setAutoCommit(false);
var start = new Date();
var stmt = conn.prepareStatement('INSERT INTO campaignPerformance '
+ '(campaignDate, CampaignName, Impressions, Clicks, Cost, Conversions, ViewThroughConversions) values (?, ?, ?, ?, ?, ?, ?)');
stmt.setString(1, campaignDate);
stmt.setString(2, campaignName);
stmt.setString(3, impressions);
stmt.setString(4, clicks);
stmt.setString(5, cost);
stmt.setString(6, conversions);
stmt.setString(7, viewThroughConversions);
stmt.execute();
conn.commit();
conn.close();
}
}

You can also schedule the script to run at specific time.