Created
March 15, 2017 21:02
-
-
Save neil90-db/e17cb2572d9b350d60b19cb7bf229c07 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| package adwords_crm; | |
| import com.google.api.ads.adwords.axis.factory.AdWordsServices; | |
| import com.google.api.ads.adwords.axis.v201605.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.util.*; | |
| import java.util.Date; | |
| public class CustMatchData { | |
| /** | |
| * Converts ResultSet to ListMultiMap | |
| * | |
| * @param resultSet | |
| * @return | |
| */ | |
| public static String sTDUser = "##"; | |
| public static String sTDPassword = "##"; | |
| public static String sTDUrl = "jdbc:teradata://acsctd2/database=##"; | |
| public static Date date = new Date(); | |
| public static String modifiedDate= new SimpleDateFormat("yyyy-MM-dd").format(date); | |
| private static ResultSet qryCustMatch(){ | |
| ResultSet rs = null; | |
| try | |
| { | |
| Class.forName("com.teradata.jdbc.TeraDriver"); | |
| //String sSelAll = "SELECT TOP 100 * FROM mktdevusers.CSP_GOOG_CUSTMATCH_APITEST WHERE campaignid = 747614532 ORDER BY EmailAddress256Hashed"; | |
| String sSelAll = "SELECT TOP 100 FileDesc,EmailAddress256Hashed, ClientID, CampaignID FROM MKTDEVUSERS.CSP_GOOG_CUSTMATCH_APITEST "; | |
| Connection con = DriverManager.getConnection(sTDUrl, sTDUser, sTDPassword); | |
| Statement stmt = con.createStatement(); | |
| //stmt.setFetchSize(1000); | |
| rs = stmt.executeQuery(sSelAll); | |
| } | |
| catch(Exception e) | |
| { | |
| System.out.println(e.getMessage()); | |
| System.exit(0); | |
| } | |
| //ArrayList<List<String>> test = null; | |
| return rs; | |
| } | |
| private static Set<List<String>> createClientAdGroupMap(ResultSet rs) throws SQLException { | |
| Set<List<String>> clientCampaignTie = new HashSet<>(); | |
| while (rs.next()) { | |
| clientCampaignTie.add(Arrays.asList(rs.getString(3), rs.getString(4))); | |
| } | |
| return clientCampaignTie; | |
| } | |
| private static Multimap<String, String> emailClientAdGrpMap(ResultSet rs, Set<List<String>> clientAdMap) throws SQLException { | |
| Multimap<String, String> multiMap = ArrayListMultimap.create(); | |
| while (rs.next()) { | |
| for (List<String> clientAd : clientAdMap){ | |
| if (clientAd.get(0).equals(rs.getString(3)) && clientAd.get(1).equals(rs.getString(4))) { | |
| System.out.println(clientAd.get(0) + " " + clientAd.get(1)+ " " + rs.getString(3) + " " + rs.getString(4)); | |
| multiMap.put(rs.getString(1) + " " + rs.getString(3) + " " + rs.getString(4) , rs.getString(2)); | |
| break; | |
| } | |
| } | |
| } | |
| return multiMap; | |
| } | |
| private static void uploadEmails(Multimap<String, String> emailMappings) { | |
| //Generate a refreshable OAuth2 credential. | |
| try { | |
| Credential oAuth2Credential = new OfflineCredentials.Builder() | |
| .forApi(Api.ADWORDS) | |
| .fromFile() | |
| .build() | |
| .generateCredential(); | |
| // Construct an AdWordsSession. | |
| AdWordsSession session = new AdWordsSession.Builder() | |
| .fromFile() | |
| .withOAuth2Credential(oAuth2Credential) | |
| .build(); | |
| for (Object key : emailMappings.keySet()) { | |
| String[] keyString = key.toString().split(" "); | |
| String fileDesc = keyString[0]; | |
| String clientId = keyString[1]; | |
| String campaignId = keyString[2]; | |
| session.setClientCustomerId(clientId); | |
| AdWordsServices adWordsServices = new AdWordsServices(); | |
| AdwordsUserListServiceInterface userListService = | |
| adWordsServices.get(session, AdwordsUserListServiceInterface.class); | |
| // Create a user list. | |
| CrmBasedUserList userList = new CrmBasedUserList(); | |
| userList.setName(fileDesc); | |
| userList.setDescription("Sample Upload"); | |
| // 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); | |
| // Add email address hashes. | |
| operand.setMembers(emailMappings.get(key.toString()).toArray(new String[emailMappings.get(key.toString()).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 }); | |
| // 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", | |
| emailMappings.get(key.toString()).size(), userListResult.getName(), userListResult.getId()); | |
| } | |
| //Campaign operation | |
| CampaignCriterionServiceInterface campaignCriterionService = | |
| adWordsServices.get(session, CampaignCriterionServiceInterface.class); | |
| long campId = Long.parseLong(campaignId); | |
| CriterionUserList userListCamp = new CriterionUserList(); | |
| userListCamp.setUserListId(userListId); | |
| CampaignCriterion criterion = new CampaignCriterion(); | |
| criterion.setCampaignId(campId); | |
| criterion.setCriterion(userListCamp); | |
| CampaignCriterionOperation operation_camp = new CampaignCriterionOperation(); | |
| operation.setOperand(criterion); | |
| operation.setOperator(Operator.ADD); | |
| CampaignCriterionOperation[] operations = new CampaignCriterionOperation[] {operation_camp}; | |
| CampaignCriterionReturnValue result_camp = campaignCriterionService.mutate(operations); | |
| /* | |
| CampaignServiceInterface campaignService = | |
| adWordsServices.get(session, CampaignServiceInterface.class); | |
| long campId = Long.parseLong(campaignId); | |
| CriterionUserList userList_crit_camp = new CriterionUserList(); | |
| userList_crit_camp.setUserListId(userListId); | |
| CampaignCriterion criterion = new CampaignCriterion(); | |
| criterion.setCampaignId(campId); | |
| criterion.setCriterion(userList_crit_camp); | |
| BiddableAdGroupCriterion criterions = new BiddableAdGroupCriterion(); | |
| criterions.setAdGroupId(campId); | |
| criterions.setCriterion(userList_crit_camp); | |
| CampaignCriterionOperation operation_ad = new CampaignCriterionOperation(); | |
| operation_ad.setCampaignId(criterion); | |
| operation_ad.setOperator(Operator.ADD); | |
| AdGroupCriterionOperation[] operations = new AdGroupCriterionOperation[] {operation_ad}; | |
| AdGroupCriterionReturnValue resultad = campaignService.mutate(operations); | |
| */ | |
| } | |
| } | |
| catch(Exception e) | |
| { | |
| System.out.println(e.getMessage()); | |
| System.exit(0); | |
| } | |
| } | |
| public static void main(String[] args) throws SQLException { | |
| System.setProperty("javax.net.ssl.trustStore","C:/Users/e659383/Documents/cacertss"); | |
| Set<List<String>> uniquePair = createClientAdGroupMap(qryCustMatch()); | |
| Multimap<String, String> uploadList = emailClientAdGrpMap(qryCustMatch(),uniquePair); | |
| uploadEmails(uploadList); | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment