Google Merchant and Multiple Currencies

Hello,
I have my shop at USD as default currency and I have added my products to google merchant. I've made separate feed for each currency to show them in different countries, because as I understand google does not make automatic currency change for each country. For example I've made separate feed for CAD to saw my products to CANADA through google, sperate feed in € to saw my products in Europe.
For some reasons google merchant gives a price mismatch at some products, and rejects them (see screenshot) although I have pause auto update of each product source. I think that I have to give the feed a little time before I put my store to its default currency, which in this case is USD and not CAD or EUR, maybe a few hours, to let google process each product feed, to avoid that price conflict.
Has anyone have any similar experience. I'm sorry for my bad English, I hope I was clear, it's not my first language...

The recommended feed
https://www.opencart.com/index.php?rout ... on_id=2500

Free feed but for OC2 maybe you can adapt it
https://www.opencart.com/index.php?rout ... n_id=22344

Untested feed offered by Joe1234

Joe1234 wrote: ↑

Wed Nov 20, 2024 8:31 am

I made this before I had a better understanding of OC workings, so you have to set up a cron to hit this. Or you can turn it into a function and activate it every time a checkout happens as well as hit it with the cron. It has facebook merchant setup as well. I took out a bunch of things that are exclusive to my shop so it wont be that confusing, but you should be able to see the basic format to get it to work for you. Just make sure when you make the adjustments the output lines match up with the headers. If memory serves me, you have to organize the google/facebook layout/order to match the files generated.

Code: Select all

<?php
// Replace the following variables with your own values

	$servername = "";
	$username = "";
	$password = "!!";
	$dbname = "";
	$file_nameG = "/path/to/Google_Merchant_Export";
	$file_nameFB = "/path/to/Facebook_Merchant_Export.csv";


	// Create connection
	$conn = mysqli_connect($servername, $username, $password, $dbname);


	// Check connection
	if (!$conn) {
		die("Connection failed: " . mysqli_connect_error());
	} else {
	//	print_r( "Connected successfully<br>");
	}


	$sql = "SELECT
		DISTINCT
		p.sku as id,
		p.model,
		p.image AS image_link,
		i.image AS additional_image_link,
		p.status AS availability,
		p.date_available AS availability_date,
		Round(p.weight, 4) AS shipping_weight,
		p.quantity,
		Round(p.price, 2) as price,
		Round(s.price, 2) as sale_price,
		d.name as title,
		d.description,
		m.name AS brand,
		u.keyword as link,
		max_cat.category_id AS highest_category_id,  -- Selecting the highest category_id
		SUBSTRING_INDEX(group_concat(CASE a.attribute_id WHEN '34' THEN a.text END), ',', 1) `condition`,
		SUBSTRING_INDEX(group_concat(CASE a.attribute_id WHEN '35' THEN a.text END), ',', 1) `gender`,
		SUBSTRING_INDEX(group_concat(CASE a.attribute_id WHEN '36' THEN a.text END), ',', 1) `size`,
		SUBSTRING_INDEX(group_concat(CASE a.attribute_id WHEN '37' THEN a.text END), ',', 1) `color1`,
		SUBSTRING_INDEX(group_concat(CASE a.attribute_id WHEN '38' THEN a.text END), ',', 1) `color2`,
		SUBSTRING_INDEX(group_concat(CASE a.attribute_id WHEN '41' THEN a.text END), ',', 1) `material`
	FROM
		db_product_attribute a
	LEFT JOIN
		db_product p ON p.product_Id = a.product_id
	LEFT JOIN
		db_product_special s ON p.product_id = s.product_id
	LEFT JOIN
		db_product_description d ON p.product_id = d.product_id
	LEFT JOIN
		db_manufacturer m ON p.manufacturer_id = m.manufacturer_id
	LEFT JOIN
		db_product_image i ON p.product_id = i.product_id
	LEFT JOIN
		db_seo_url u ON CONCAT('product_id=',p.product_Id) = u.query
	LEFT JOIN (
		SELECT c.product_id, c.category_id
		FROM db_product_to_category c
		INNER JOIN (
			SELECT product_id, MAX(category_id) AS max_category
			FROM db_product_to_category
			GROUP BY product_id
		) AS subq ON c.product_id = subq.product_id AND c.category_id = subq.max_category
	) AS max_cat ON p.product_id = max_cat.product_id  -- Left join the subquery for max category
	WHERE
		AND p.quantity > 0
		AND p.status = 1
	GROUP BY
		a.product_id;";


	// Execute the query
	$result = mysqli_query($conn, $sql);
	//print_r($result->rows);

	// Set the headers for the Excel file
	// Set the headers for the tab-delimited file
	header('Content-Type: text/plain');


	// Generate a downloadable file
	// header('Content-Disposition: attachment; filename="' . $file_name . '.txt"');


	// Create the headers for the file
	$googlel = "id \t title \t google_product_category \t description \t price \t condition \t link \t image_link \t additional_image_link \t availability \t availability_date \t sale_price \t brand \t adult \t age_group \t color \t gender \t size \t material \t shipping_weight\n";
	$facebookl = "id \t title \t fb_product_category \t google_product_category \t description \t availability \t condition \t custom_label_0 \t price \t quantity_to_sell_on_facebook \t link \t image_link \t additional_image_link \t brand \t sale_price \t is_adult_product \t age_group \t color \t gender \t size \t material \t is_outfit_set \t shipping_weight \t is_costume\n";


	getProductIdOnCondition($result, $googlel, $facebookl);


	mysqli_close($conn);


	function getProductIdOnCondition($result, $googlel, $facebookl) {

		global $file_nameG;
		global $file_nameFB;


		$AVAILABILITY_G = "in_stock";
		$AVAILABILITY_F = "in stock";
		$ADULT = "no";

		// Open the file for writing
		$facebook_handle = fopen($file_nameFB, 'w');


		$facebook_array = explode("\t", $facebookl);
		fputcsv($facebook_handle, $facebook_array);


		// Loop through the data and output it to the Excel file
		while ($row = mysqli_fetch_assoc($result)) {

			if () {

				//a bunch of conditions where the variables and data is set or you can skip this and set the info directly from the array

			}

			$googlel .= $row['id'] . "\t" . $row['title'] . "\t" . $goog_category . "\t" . $row['description'] . "\t" . $row['price'] . " USD \t" . $CONDITION . "\t https://yourwebsite.com/" . $row['link'] . "\t https://yourwebsite.com/image/" . $row['image_link'] . "\t https://yourwebsite.com/image/" . $row['additional_image_link'] . "\t" . $AVAILABILITY_F . "\t" . $row['availability_date'] . "\t" . $SALE_PRICE . "\t" . $row['brand'] . "\t" . $ADULT . "\t" . $AGE_GROUP . "\t" . $COLOR . "\t" . $GENDER . "\t" . $SIZE . "\t" . $row['material'] . "\t" . number_format($row['shipping_weight'], 4, ".", "") . "\n";

			$facebook_string = $row['id'] . "\t" . $row['title'] . "\t" . $fb_category . "\t" . $goog_category . "\t" . $row['description'] . "\t" . $AVAILABILITY_F . "\t" . $CONDITION . "\t" . $row['custom_label_0'] . "\t" . $row['price'] . " USD \t" . $row['quantity'] . "\t https://yourwebsite.com/" . $row['link'] . "\t https://yourwebsite.com/image/" . $row['image_link'] . "\t https://yourwebsite.com/image/" . $row['additional_image_link'] . "\t" . $row['brand'] . "\t" . $SALE_PRICE . "\t" . $ADULT . "\t" . $AGE_GROUP . "\t" . $COLOR . "\t" . $GENDER . "\t" . $SIZE . "\t" . $row['material'] . "\t" . $SET . "\t" . number_format($row['shipping_weight'], 4, ".", "") . " lb\t" . $COSTUME . "\n";

			$facebook_array = explode("\t", $facebook_string);

			fputcsv($facebook_handle, $facebook_array);

		}

		file_put_contents($file_nameG . '.txt', $googlel);

		fclose($facebook_handle);

	}
?>

Do your landing pages for each feed match the currency for that feed?

ADD Creative wrote: ↑

Mon Dec 02, 2024 6:15 pm

Do your landing pages for each feed match the currency for that feed?

You mean the products that are on the first page like featured?

No, the links in your feeds. Does the USD feed take to to a product page that has the price USD? Does the EUR feed take to to a product page that has the price in EUR?

ADD Creative wrote: ↑

Tue Dec 03, 2024 12:41 am

No, the links in your feeds. Does the USD feed take to to a product page that has the price USD? Does the EUR feed take to to a product page that has the price in EUR?

Oh, I'm sorry, no I have only added a currency option in my store on the header and used an exchange rate extension...before I update the feed for google I change my default currency to what I want, for example I switch to CAD, create the feed for Canada, and update the feed for the Canada on the product sources. I have created four separated product sources for each country and currency and update them separately. Maybe that's not the correct way to do it, but it only hits error mismatch to some products, not all of them...

Check your web access logs for the Storebot. You probably find it doesn't fetch all your products the instance you upload your feed.

The products without a mismatch have probably not been crawled yet.

ADD Creative wrote: ↑

Tue Dec 03, 2024 6:03 pm

Check your web access logs for the Storebot. You probably find it doesn't fetch all your products the instance you upload your feed.

The products without a mismatch have probably not been crawled yet.

I'm sorry but I do not know how to do that . But Even in this case,it looks like I cannot do anything since Google always crawls web store to see changes...it will always accept the default currency in the store and reject the other feeds . It's like it cannot be informed that it has multi currency option enabled at the store...

Some of the third party Google product feed extensions get around that by including currency URL parameter and use that to set the currency for the store.

ADD Creative wrote: ↑

Wed Dec 04, 2024 5:38 pm

Some of the third party Google product feed extensions get around that by including currency URL parameter and use that to set the currency for the store.

Thank you, I will check that, my extension is free There is also an option on google merchant for those products that need fixes that your store store is multi currency and it does not change by IP of the customer..... but it says you can only request review once....so I have not chosen this option yet....

UPDATE: for anyone interested I found a solution for free, I downloaded this extension https://www.opencart.com/index.php?rout ... enyrichard
and I just added "&currency=..." at the landing pages on merchant center that have this problem.
It approved the product, hope it will work in the future...because I got really frustrated with Google...still learning....