Extracting ONLY the Parameters from GA UTM URLs

Question

I would like to extract the parameters from tracking URLs but I only want the parameters. I found this very helpful formula to extract them from URLs. This formula solves the problem best for me except that I only want the parameters without "utm_xxx." I like the way I can drag it to a few columns

=IF((LEN($A2)-LEN(SUBSTITUTE($A2,"utm_","")))/4

Extract GA UTM parameters from URL in excel

The sample urls given were: http://www.site.com.br/porta-montada-sanfonada?utm_source=google&utm_medium=display&utm_term=468x60&utm_content=34545&utm_campaign=nome_campanha

http://www.site.com.br/torneira-para-banheiro-mesa-bica-baixa-cromada?utm_source=facebook&utm_medium=carrossel&utm_term=120x600&utm_content=87648715&utm_campaign=nome_campanha

In case I am not being clear, I am hoping to just extract what is after "&" and, when it applies, before "=".

Thanks for any help!

Answer 1

You can use this formula to do what you want:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF((LEN($A2)-LEN(SUBSTITUTE($A2;"utm_";"")))/4<COLUMN(A2);"";"utm_"&SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2;"utm_";REPT(" ";LEN($A2)));COLUMN(A2)*LEN($A2);LEN($A2)));"&";""));"utm_source=";"");"utm_medium=";"");"utm_campaign=";"");"utm_content=";"");"utm_term=";"")

Put the URL with UTMs in A2 and the formula in cell B2 and copy over and down (if there are other URLs):

Example based on this URL: http://www.site.com.br/porta-montada-sanfonada?utm_source=google&utm_medium=display&utm_term=468x60&utm_content=34545&utm_campaign=nome_campanha

EDIT

To add other parameters and get the value:

In formula in A2 add this at the start:

SUBSTITUTE(

and this at the end:

;"utm_expid=";"")

then copy over and down

Answer 2

In this specific case, I have a different method that is simpler and more intuitive.

See Google Sheet https://docs.google.com/spreadsheets/d/1lUQlMS_mIwr2AyG_ey2W1ofBaSmUiwMxx99smPSpmqY/edit?usp=sharing

I would explain here, but it's easier to understand just by looking at the sheet.