Creating a duplicate Increment_ID as an INT type

[matt_richards]

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!

Matt

[chris_schmid]

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:

A::int

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.

Best,

Chris