cancel
Showing results for 
Search instead for 
Did you mean: 

Creating a duplicate Increment_ID as an INT type

Highlighted

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!

Matt

1 REPLY 1
Highlighted

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:

 

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

Chris Schmid
Sr. Product Analyst
Adobe