Showing results for 
Search instead for 
Did you mean: 

Creating a duplicate Increment_ID as an INT type

Creating a duplicate Increment_ID as an INT type

The increment_ID field in the sales_order table is pulled through as VARCHAR(32).


However the increment_id is what appears in Google Analytics as the transactionID, but when we import the Google Analytics data the transactionID is a number and as such we can't join.


What's the best way of duplicating the increment_ID field to make it an integer so that I'm able to join these tables?


Thanks in advance!



Re: Creating a duplicate Increment_ID as an INT type

Hi Matt,


Assuming you are an admin user and subscribed to MBI Pro, the easiest way to do this is by setting up a new SQL calculated column in your sales_order table, which casts the increment_id to an integer.

If the increment_id in your Magento instance is actually an integer, and is only saved like a varchar, then your SQL logic could be as simple as:



If instead the increment_id sometimes contains non-numeric characters, you'll need to adjust your logic to account for those use-cases (i.e. in cases where an order is split, you might have an original increment_id of 1000 that is split into 1000-1 and 1000-2. In this case you would need to decide how to format the result as a simple integer, which may include splitting the string at the `-` character and truncating off the second part).

After saving your new calculated column as an int in sales_order, you should be able to join directly to GA's integer transaction_id.



Chris Schmid
Sr. Product Analyst