Update Wordpress Custom Field to Hubspot

[DanielRowles]

Hi

We need to update a range of custom fields from Wordpress to Hubspot using the API. If we do this for every user using a CRON job it will slow down our web server. What's the best way fo approaching this for a Wordpress site with thousands of users? Need to suggest to my developer the most sensible approach.

Thanks

Daniel

[quentin_lamamy · Accepted solution]

Well it's definitfly not the right way, no need to create a hubspot submission and use a wordpress page. He juste have to create a php file with the hubspot sdk.

Inside is file :

- Make a mysql query
- Do his stuff with hubspot api

It cost lot of ressource to use wordpress for that process and seems on my mind totally useless.

Maybe your developer should participate to this topic.

[DanielRowles]

Thank you for confirming my suspicions. Yes, they definitely should!

Thanks for your help.

Daniel

[DanielRowles]

Current code is here - looks like developer is simulating a form submission to achieve this. These all appear to be showing as marketing contacts even though API and non-Hubspot forms are defaulted to be non-marketing contacts. (This has lead to 14k new marketing contacts and a very large bill that I am now requesting is reversed!). Anyway, is there a better way to do this?

<?php    
include('wp-load.php');
global $wpdb; 


//Process a new form submission in HubSpot in order to create a new Contact.

$hubspotutk      = $_COOKIE['hubspotutk']; //grab the cookie from the visitors browser.
$ip_addr         = $_SERVER['REMOTE_ADDR']; //IP address too.
$hs_context      = array(
   /* 'hutk' => $hubspotutk,
    'ipAddress' => $ip_addr,*/
    'pageUrl' => 'https://www.targetinternet.com/cron_hubspot.php',
    'pageName' => 'Created by CRON Job'
);
$hs_context_json = json_encode($hs_context);

$getRegisteredUsers = $wpdb->get_results("SELECT ID, user_login, user_registered FROM yn9ikp_users order by ID desc");	

if(!empty($getRegisteredUsers)) {
	foreach($getRegisteredUsers as $keys => $val) {
		$array[$val->ID]['email'] = $val->user_login;
		$array[$val->ID]['firstname'] = get_user_meta($val->ID, 'first_name', true);	
		$array[$val->ID]['lastname'] = get_user_meta($val->ID, 'last_name', true);	
		$array[$val->ID]['company'] = get_user_meta($val->ID, 'company', true);	
		$array[$val->ID]['jobtitle'] = get_user_meta($val->ID, 'job_title', true);
		$array[$val->ID]['interested_cim'] = get_user_meta($val->ID, 'interested_cim', true);
		$array[$val->ID]['ph_no'] = get_user_meta($val->ID, 'phone_number', true);
		$array[$val->ID]['tel_no'] = get_user_meta($val->ID, 'telephone_number', true);
		$array[$val->ID]['sign_up_date'] = date('m/d/Y', strtotime($val->user_registered));
		
		$array[$val->ID]['whole_dce_date'] = get_user_meta($val->ID, 'Quali_section1_date', true);
		$array[$val->ID]['whole_do_date'] = get_user_meta($val->ID, 'Quali_section2_date', true);
		$array[$val->ID]['whole_mds_date'] = get_user_meta($val->ID, 'Quali_section3_date', true);
		
		if(!empty($array[$val->ID]['whole_dce_date'])) {
			
			if($array[$val->ID]['whole_dce_date'] == 'Not sure') {
				$array[$val->ID]['dce_date'] = 'None Selected';
			} else {
				$array[$val->ID]['dce_date'] = date('F y', strtotime($array[$val->ID]['whole_dce_date']));
			}
		}
		
		if(!empty($array[$val->ID]['whole_do_date'])) {
			if($array[$val->ID]['whole_do_date'] == 'Not sure') {
				$array[$val->ID]['do_date'] = 'None Selected';
			} else {
				$array[$val->ID]['do_date'] = date('F y', strtotime($array[$val->ID]['whole_do_date']));
			}
		}
		
		if(!empty($array[$val->ID]['whole_mds_date'])) {
			if($array[$val->ID]['whole_mds_date'] == 'Not sure') {
				$array[$val->ID]['mds_date'] = 'None Selected';
			} else {
				$array[$val->ID]['mds_date'] = date('F y', strtotime($array[$val->ID]['whole_mds_date']));
			}
		}
		
		$array[$val->ID]['benchmark'] = $wpdb->get_results("SELECT `quiz_started_time` FROM `yn9ikp_benchmark_result` WHERE `user_id` = $val->ID ORDER BY `id` desc LIMIT 1 ");
		$array[$val->ID]['benchmark_last_completed'] = date('m/d/Y', strtotime($array[$val->ID]['benchmark'][0]->quiz_started_time));
		
		$array[$val->ID]['purchase'] = $wpdb->get_results("SELECT MIN(`start_date`) as start_date, MAX(`expiry_date`) as expiry_date, SUM(`valid_period`) as `valid_period` FROM `yn9ikp_purchases` WHERE `user_id` = $val->ID ");
		
		$array[$val->ID]['purchase_amount'] = $wpdb->get_results("SELECT SUM(`product_amount`) as product_amount FROM `yn9ikp_purchases` WHERE `user_id` = $val->ID AND subscr_type = 'inuse'");
		
		$array[$val->ID]['other'] = $wpdb->get_results("SELECT `expiry_date`, `is_canceled`, `valid_period`, `subscr_type` FROM `yn9ikp_purchases` WHERE `user_id` = $val->ID ORDER BY `id` DESC LIMIT 0,1");
		
		$array[$val->ID]['courses'] = $wpdb->get_results("SELECT COUNT(`id`) FROM `yn9ikp_xapi_data` WHERE `user_email` = $val->user_login  AND `score` >= 0.7 GROUP BY `content` ");
		$array[$val->ID]['courses_completed_count'] = sizeof($array[$val->ID]['courses']);
		
		if(!empty($array[$val->ID]['ph_no'])) {
			$phone_number = $array[$val->ID]['ph_no'];
		} else if(!empty($array[$val->ID]['tel_no'])) {
			$phone_number = $array[$val->ID]['tel_no'];
		} else {
			$phone_number = '';
		}
		
		$role = get_user_meta($val->ID, 'yn9ikp_capabilities', true);	
		
		if(array_key_exists('individual',$role)) {
			$role = 'individual';
			
			if(!empty($array[$val->ID]['purchase'][0]->start_date)) {			
				$exp_date = strtotime($array[$val->ID]['other'][0]->expiry_date);
				$get_expirt_date = date('Y-m-d', $exp_date);
				$today = date('Y-m-d');
				$date_difference = (strtotime($get_expirt_date)) - (strtotime($today));
				$valid_period = floor($date_difference / (60*60*24) );
				
				
				$array[$val->ID]['next_payment_date'] = date('m/d/Y', strtotime('+1 day', strtotime($array[$val->ID]['purchase'][0]->expiry_date)));
				
				$payment_frequency = $array[$val->ID]['other'][0]->valid_period;
				
				switch($payment_frequency){
					case 1: $array[$val->ID]['payment_frequency'] = 'Monthly';
							break;
					case 6: $array[$val->ID]['payment_frequency'] = '6 Monthly';
							break;
					case 12: $array[$val->ID]['payment_frequency'] = 'Annually';
							break;
					
				}
				
				if( $array[$val->ID]['other'][0]->subscr_type == 'trial') {
					if( $valid_period < 0) {	
						$array[$val->ID]['membership_status'] = 'Cancelled Trial Individual';
					} else {
						$array[$val->ID]['membership_status'] = 'Trial Individual';
					}
				} else {
					if( $valid_period < 0) {
						$array[$val->ID]['membership_status'] = 'Cancelled Paid Individual';
					} else {
						$array[$val->ID]['membership_status'] = 'Active Individual';
					}
				}
				
				$array[$val->ID]['total_months_active'] = $array[$val->ID]['purchase'][0]->valid_period;
				$array[$val->ID]['total_spend'] = $array[$val->ID]['purchase_amount'][0]->product_amount;
				
			} else {
				$array[$val->ID]['membership_status'] = 'Free Individual';
				$array[$val->ID]['payment_frequency'] = '';
				$array[$val->ID]['next_payment_date'] = '';
				$array[$val->ID]['total_months_active'] = '';
				$array[$val->ID]['total_spend'] = '';
			}
			$array[$val->ID]['landing_page'] = 'Digital Marketing';
		} else if(array_key_exists('browsers',$role)) {
			$array[$val->ID]['membership_status'] = 'Free Individual';
			$array[$val->ID]['payment_frequency'] = '';
			$array[$val->ID]['next_payment_date'] = '';
			$array[$val->ID]['landing_page'] = 'Digital Marketing';
			$array[$val->ID]['total_months_active'] = '';
				$array[$val->ID]['total_spend'] = '';
		} else if(array_key_exists('team',$role)) {
			$array[$val->ID]['inactive'] = get_user_meta($val->ID, 'wpduact_status', true);
			$array[$val->ID]['free_user'] = get_user_meta($val->ID, 'free_user', true);
			
			if(!empty($array[$val->ID]['inactive'])) {
				$array[$val->ID]['membership_status'] = 'Cancelled Corporate';
			} else {
				if(!empty($array[$val->ID]['free_user'])) {
					$array[$val->ID]['membership_status'] = 'Corporate Trial';
				} else {
					$array[$val->ID]['membership_status'] = 'Active Corporate';
				}
			}
			$array[$val->ID]['payment_frequency'] = '';
			$array[$val->ID]['next_payment_date'] = '';
			$array[$val->ID]['total_months_active'] = '';
			$array[$val->ID]['total_spend'] = '';
			$array[$val->ID]['landing_page'] = 'Corporate';
		} 
		
		if(get_user_meta($val->ID, 'benchmark_quiz_completed', true) == 1 ){
			$array[$val->ID]['benchmark_complete'] = 'true';
		} else {
			$array[$val->ID]['benchmark_complete'] = 'false';
		}
		
		if( $array[$val->ID]['interested_cim'] == 1 ){
			$array[$val->ID]['cim_status'] = 'true';
		} else {
			$array[$val->ID]['cim_status'] = 'false';
		}
		
		//echo '<pre>',print_r($array);
		
		
		//Need to populate these variable with values from the form.
		$str_post = "firstname=" . urlencode($array[$val->ID]['firstname']) 
			. "&lastname=" . urlencode($array[$val->ID]['lastname']) 
			. "&email=" . urlencode($array[$val->ID]['email']) 
			. "&phone=" . urlencode($phone_number) 
			. "&company=" . urlencode($array[$val->ID]['company']) 
			. "&sign_up_date=" . urlencode($array[$val->ID]['sign_up_date']) 
			. "&membership_status=" . urlencode($array[$val->ID]['membership_status']) 
			. "&payment_frequency=" . urlencode($array[$val->ID]['payment_frequency']) 
			. "&next_payment_date=" . urlencode($array[$val->ID]['next_payment_date']) 
			. "&landing_page=" . urlencode($array[$val->ID]['landing_page']) 
			. "&benchmark_complete=" . urlencode($array[$val->ID]['benchmark_complete']) 
			. "&benchmark_last_completed=" . urlencode($array[$val->ID]['benchmark_last_completed']) 
			. "&total_months_active=" . urlencode($array[$val->ID]['total_months_active']) 
			. "&total_spend=" . urlencode($array[$val->ID]['total_spend']) 
			. "&cim_status=" . urlencode($array[$val->ID]['cim_status']) 
			. "&courses_completed_count=" . urlencode($array[$val->ID]['courses_completed_count']) 
			. "&dce_date=" . urlencode($array[$val->ID]['dce_date']) 
			. "&do_date=" . urlencode($array[$val->ID]['do_date']) 
			. "&mds_date=" . urlencode($array[$val->ID]['mds_date']) 
			. "&hs_context=" . urlencode($hs_context_json); //Leave this one be
				
		//replace the values in this URL with your portal ID and your form GUID
		//$endpoint = 'https://forms.hubspot.com/uploads/form/v2/{portalId}/{formGuid}';
		$endpoint = 'https://forms.hubspot.com/uploads/form/v2/8900005/f1b783db-4da8-4e54-b05a-6e5a79702ec1';

		$ch = @curl_init();
		@curl_setopt($ch, CURLOPT_POST, true);
		@curl_setopt($ch, CURLOPT_POSTFIELDS, $str_post);
		@curl_setopt($ch, CURLOPT_URL, $endpoint);
		@curl_setopt($ch, CURLOPT_HTTPHEADER, array(
			'Content-Type: application/x-www-form-urlencoded'
		));
		@curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
		$response    = @curl_exec($ch); //Log the response from HubSpot as needed.
		$status_code = @curl_getinfo($ch, CURLINFO_HTTP_CODE); //Log the response status code
		@curl_close($ch);
		echo $response;
		
	}
}




?>

[quentin_lamamy]

Well it's strange that you succeed to kil la webserver with update request. Can you send you code an post it inside code blocks here ?

When you say database are you talking about your mysql or the hubspot data ?

[quentin_lamamy]

Hi @DanielRowles ,

THank @dennisedson for the ping

Does this action is a daily sync of user that is necessary do at a specific time ? In my mind there is there possibilities :

- If it's possible, split you update in multiple batch based on id (maybe by batch of 10) and displatch it during the day

- Or like most of website, you can make you sync during an idle time based on a dynamic read of your http incoming traffic or juste based on your visitor metrics, if you have less people between midnight and 4AM do it on this time.

You also have to keep in mind that a contact update if pretty fast and it's not a big deal for a webserver and for the hubspot api. Actually on my personal computer ( so my bandwith an performance are under a server) updating 500 contact took me 00:03:17

and i'm not using batch and doing other stuff on my computer on the same time.

[DanielRowles]

Hi Quentin

Batch is a good idea - when we run the Cron it's killing our server so this could be poor code problem. I'll share code in case anyone is willing to point out what could be the problem...

I'd like to trigger an update when a database field is changed - any ideas on this?

Thanks

Daniel

[dennisedson]

Hey @DanielRowles 

Welcome to the Community!

A few questions

Does users = contacts?

If yes, do these contacts already exist in HubSpot and you need to update some contact properties or will it be net new?

Are the contacts stored with something like WooCommerce?  If so, this thread may be of some relevance.

From the HubSpot API standpoint, you will want to make sure to take advantage of the batch endpoints rather than posting each individually.

@quentin_lamamy , @Bryantworks  do you have any suggestions?

[DanielRowles]

Thanks Dennis.
Yep I mean contacts - most will be in Hubspot already but some will be new (this shouldn't happen after the first run).

It's a custom build subscription plugin so not Woo. Thanks for pointer on batch endpoints - I'll look into this.

Any further ideas very welcome. Has anyone used triggers or similar to update Hubspot when a database change is made?

Thanks

Daniel