Skip to content

Instantly share code, notes, and snippets.

@neil90-db

neil90-db/CustMatchData.java

Created March 21, 2017 15:37
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save neil90-db/38eedae265004df6e47d6d27e9b53465 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save neil90-db/38eedae265004df6e47d6d27e9b53465 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
CustMatchData.java
package adwords_crm;
import com.google.api.ads.adwords.axis.factory.AdWordsServices;
import com.google.api.ads.adwords.axis.v201609.cm.CampaignCriterion;
import com.google.api.ads.adwords.axis.v201609.cm.AdGroupCriterionOperation;
import com.google.api.ads.adwords.axis.v201609.cm.AdGroupCriterionReturnValue;
import com.google.api.ads.adwords.axis.v201609.cm.AdGroupCriterionServiceInterface;
import com.google.api.ads.adwords.axis.v201609.cm.AdGroupServiceInterface;
import com.google.api.ads.adwords.axis.v201609.cm.ApiException;
import com.google.api.ads.adwords.axis.v201609.cm.BiddableAdGroupCriterion;
import com.google.api.ads.adwords.axis.v201609.cm.CampaignCriterionOperation;
import com.google.api.ads.adwords.axis.v201609.cm.CampaignCriterionReturnValue;
import com.google.api.ads.adwords.axis.v201609.cm.CampaignCriterionService;
import com.google.api.ads.adwords.axis.v201609.cm.CampaignCriterionServiceInterface;
import com.google.api.ads.adwords.axis.v201609.cm.CampaignServiceInterface;
import com.google.api.ads.adwords.axis.v201609.cm.CriterionUserList;
import com.google.api.ads.adwords.axis.v201609.cm.Operator;
import com.google.api.ads.adwords.axis.v201609.rm.AdwordsUserListServiceInterface;
import com.google.api.ads.adwords.axis.v201609.rm.CrmBasedUserList;
import com.google.api.ads.adwords.axis.v201609.rm.MutateMembersOperand;
import com.google.api.ads.adwords.axis.v201609.rm.MutateMembersOperandDataType;
import com.google.api.ads.adwords.axis.v201609.rm.MutateMembersOperation;
import com.google.api.ads.adwords.axis.v201609.rm.MutateMembersReturnValue;
import com.google.api.ads.adwords.axis.v201609.rm.UserList;
import com.google.api.ads.adwords.axis.v201609.rm.UserListOperation;
import com.google.api.ads.adwords.axis.v201609.rm.UserListReturnValue;
import com.google.api.ads.adwords.lib.client.AdWordsSession;
import com.google.api.ads.common.lib.auth.OfflineCredentials;
import com.google.api.ads.common.lib.auth.OfflineCredentials.Api;
import com.google.api.ads.common.lib.conf.ConfigurationLoadException;
import com.google.api.ads.common.lib.exception.OAuthException;
import com.google.api.ads.common.lib.exception.ValidationException;
import com.google.api.client.auth.oauth2.Credential;
import com.google.common.collect.ArrayListMultimap;
import com.google.common.collect.ListMultimap;
import com.google.common.collect.Multimap;
import java.rmi.RemoteException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.util.*;
import java.util.Date;
public class CustMatchData {
private static String sTDUser = "###";
private static String sTDPassword = "###";
private static String sTDUrl = "jdbc:teradata://acsctd2/database=###";
private static Date date = new Date();
private static String modifiedDate= new SimpleDateFormat("yyyy-MM-dd").format(date);
private static Connection con;
/**
* Creates a connection to Teradata database
* @return Connection con
*/
private static Connection getConnection() {
try {
Class.forName("com.teradata.jdbc.TeraDriver");
try {
con = DriverManager.getConnection(sTDUrl, sTDUser, sTDPassword);
} catch (SQLException ex) {
// log an exception. from example:
System.out.println("Failed to create the database connection.");
}
} catch (ClassNotFoundException ex) {
// log an exception. for example:
System.out.println("Driver not found.");
}
return con;
}
/**
* Queries FROM MKTDEVUSERS.CSP_GOOG_CUSTMATCH_APITEST to get
* emails/clientids/campaignids/filedesc to be used to upload to Adwords API
* @param con
* @return ResultSet rs
*/
private static ResultSet qryCustMatch(Connection con){
ResultSet rs = null;
//Creates an ordered Result of combination of ClientId/CampaignID
String sSelAll = "SELECT FileDesc,EmailAddress256Hashed, ClientID, CampaignID FROM MKTDEVUSERS.CSP_GOOG_CUSTMATCH_APITEST ORDER BY ClientID, campaignid";
try
{
Statement stmt = con.createStatement();
stmt.setFetchSize(10000);
rs = stmt.executeQuery(sSelAll);
}
catch(Exception e)
{
System.out.println(e.getMessage());
System.exit(0);
}
//ArrayList<List<String>> test = null;
return rs;
}
/**
* Uploads the grouped Hashed emails(Grouped via ClientId/CampaignId) to appropriate ClientId
* and adds the FileDesc as the name of the upload. Then returns Unique Set of List of userListId/Campaign
* @param emailMappings
* @param session
* @return Set<List<String>> userListIdCampId
*/
private static Multimap<String, String> uploadEmailsClientId(String clientId, String campaignId,
List<List<String>> chunkhashedEmails, String fileDesc, AdWordsSession session, AdWordsServices adWordsServices) {
//Generate a refreshable OAuth2 credential.
Multimap<String, String> clientIduserListIdCampIdMap = ArrayListMultimap.create();
try {
session.setClientCustomerId(clientId);
//AdWordsServices adWordsServices = new AdWordsServices();
AdwordsUserListServiceInterface userListService =
adWordsServices.get(session, AdwordsUserListServiceInterface.class);
int cnt = 1;
for (List<String> hashedEmails : chunkhashedEmails) {
// Create a user list.
CrmBasedUserList userList = new CrmBasedUserList();
userList.setName(Instant.now().getEpochSecond() + "_" + fileDesc + "_part_" + cnt);
userList.setDescription("Uploaded via Adwords Java API");
// See limit here: https://support.google.com/adwords/answer/6276125#requirements.
//userList.setMembershipLifeSpan(30L);
// Create operation.
UserListOperation operation = new UserListOperation();
operation.setOperand(userList);
operation.setOperator(Operator.ADD);
// Add user list.
UserListReturnValue result = userListService.mutate(new UserListOperation[] { operation });
// Display user list.
UserList userListAdded = result.getValue(0);
System.out.printf("User list with name '%s' and ID %d was added.%n",
userListAdded.getName(), userListAdded.getId());
// Get user list ID.
Long userListId = userListAdded.getId();
// Create operation to add members to the user list based on email addresses.
MutateMembersOperation mutateMembersOperation = new MutateMembersOperation();
MutateMembersOperand operand = new MutateMembersOperand();
operand.setUserListId(userListId);
// You can optionally provide this field.
operand.setDataType(MutateMembersOperandDataType.EMAIL_SHA256);
//Convert List<String> to String[]
// Add email address hashes.
operand.setMembers(hashedEmails.toArray(new String[hashedEmails.size()]));
mutateMembersOperation.setOperand(operand);
mutateMembersOperation.setOperator(Operator.ADD);
// Add members to the user list based on email addresses.
MutateMembersReturnValue mutateMembersResult =
userListService.mutateMembers(new MutateMembersOperation[] { mutateMembersOperation });
//Add userListId and CampaignId to list of list so that we can add to Campaign
clientIduserListIdCampIdMap.put(clientId, Long.toString(userListId) + " " + campaignId);
// Display results.
// Reminder: it may take several hours for the list to be populated with members.
for (UserList userListResult : mutateMembersResult.getUserLists()) {
System.out.printf("%d email addresses were uploaded to user list with name '%s' and ID %d "
+ "and are scheduled for review.%n",
hashedEmails.size(), userListResult.getName(), userListResult.getId());
}
cnt += 1;
}
}
catch(Exception e)
{
System.out.println(e.getMessage());
System.exit(0);
}
System.out.println("end of UserList to CLientId");
return clientIduserListIdCampIdMap;
}
/**
* Gets Map all the UserListIds Uploaded to Client
* @param clientIdUserIdCampIdMap
* @param session
* @param adWordsServices
* */
private static void assignUserListCampaign(Multimap<String, String> clientIduserListIdCampIdMap, AdWordsSession session,
AdWordsServices adWordsServices) throws Exception {
for (Object key : clientIduserListIdCampIdMap.keySet()) {
String clientId = key.toString();
session.setClientCustomerId(clientId);
CampaignCriterionServiceInterface campaignCriterionService =
adWordsServices.get(session, CampaignCriterionServiceInterface.class);
for (String userCampId : clientIduserListIdCampIdMap.get(clientId)) {
String[] userCampId_list = userCampId.split(" ");
long userListId = Long.parseLong(userCampId_list[0]);
long campId = Long.parseLong(userCampId_list[1]);
CriterionUserList userListCamp = new CriterionUserList();
userListCamp.setUserListId(userListId);
CampaignCriterion criterion = new CampaignCriterion();
criterion.setCampaignId(campId);
criterion.setCriterion(userListCamp);
CampaignCriterionOperation operation_camp = new CampaignCriterionOperation();
operation_camp.setOperand(criterion);
operation_camp.setOperator(Operator.ADD);
CampaignCriterionOperation[] operations = new CampaignCriterionOperation[] {operation_camp};
CampaignCriterionReturnValue result_camp = campaignCriterionService.mutate(operations);
System.out.println("Assigned UsersList: " + userListId + " to CampaignId: " + campId);
}
}
}
/**
* Chunks the hashed email grouping into the parition sized(in this case 500k) returns list of list of 500k emails
* @param hashedEmails
* @return splitHashedEmails
* */
private static List<List<String>> splitHashedEmails (List<String> hashedEmails){
int partitionSize = 250000;
List<List<String>> partitions = new ArrayList<>();
for (int i=0; i<hashedEmails.size(); i += partitionSize) {
partitions.add(hashedEmails.subList(i, Math.min(i + partitionSize, hashedEmails.size())));
}
return partitions;
}
/** Will go through the result set and create the Client/Campaign email groupings and uploads as it goes through it
* @param rs
* @param session
* @param adWordsServices
* @throws Exception
*/
private static void runner(ResultSet rs, AdWordsSession session,
AdWordsServices adWordsServices) throws Exception {
//read First line of row to set up
rs.next();
String fileDesc = rs.getString(1);
String clientId = rs.getString(3);
String campaignId = rs.getString(4);
List<String> hashedEmails = new ArrayList<String>();
hashedEmails.add(rs.getString(2));
int cnt = 1;
while (rs.next()) {
if (!clientId.equals(rs.getString(3)) || !campaignId.equals(rs.getString(4))) {
System.out.println("Uploading " + hashedEmails.size() +" HashedEmail List to clientId: " + clientId + " and CampaignId: " + campaignId);
List<List<String>> chunkedHashedEmails = splitHashedEmails(hashedEmails);
Multimap<String, String> userListIdCampIdPair = uploadEmailsClientId(clientId, campaignId,
chunkedHashedEmails, fileDesc, session, adWordsServices);
assignUserListCampaign(userListIdCampIdPair, session, adWordsServices);
cnt += hashedEmails.size();
fileDesc = rs.getString(1);
clientId = rs.getString(3);
campaignId = rs.getString(4);
System.out.println("Reassigning ClientId to: " + clientId + " and CampaignId to: " + campaignId);
System.out.println("Clearing List");
hashedEmails = new ArrayList<String>();
hashedEmails.add(rs.getString(2));
}
else {
hashedEmails.add(rs.getString(2));
}
}
System.out.println(cnt);
}
public static void main(String[] args) throws Exception {
final long startTime = System.currentTimeMillis();
//Use Cacertss for SSL issues
System.setProperty("javax.net.ssl.trustStore","C:/Users/e659383/Documents/updatecacerts");
System.setProperty("javax.net.ssl.trustStorePassword","changeit");
// Generate a refreshable OAuth2 credential.
Credential oAuth2Credential = new OfflineCredentials.Builder()
.forApi(Api.ADWORDS)
.fromFile()
.build()
.generateCredential();
// Construct an AdWordsSession.
AdWordsSession session = new AdWordsSession.Builder()
.fromFile()
.withOAuth2Credential(oAuth2Credential)
.build();
// Instantiate AdWorsServices
AdWordsServices adWordsServices = new AdWordsServices();
//Create Connection to TD
System.out.println("Creating TD Connection");
Connection cnxn = getConnection();
System.out.println("Creating TD ResultSet");
ResultSet rs = qryCustMatch(cnxn);
runner(rs, session, adWordsServices);
final long endTime = System.currentTimeMillis();
System.out.println("Total execution time: " + (endTime - startTime) );
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment