ETL Formula to extract numeric ID at the end of campaign name

KimHeinemann1113 · July 16

Hello - does anyone know how to write an ETL formula to extract the numeric ID at the end of this campaign name: Display_Retargeting Package_07/09/24-12/31/24_BA:4114356 so that I can get a new column with just the campaign ID?

Thank you so so much for any help!

MarkSnodgrass · July 16

Glad you got it to work. Yes, using the SPLIT_PART function with it looking for the underscore character will return the ID you want.

MarkSnodgrass · July 16

If it is always after the the colon, you can use the SPLIT_PART function:

SPLIT_PART(fieldname,':',2)

If it is always a certain number of digits, you can use the RIGHT function:

RIGHT(fieldname,7)

GrantSmith · July 16

a REGEX version would be:

REGEXP_REPLACE(`field`, '^.*(\d+)$', '$1')

ColemenWilson · July 16

You can use the SUBSTRING_INDEX() function in a formula tile in Magic ETL:

SUBSTRING_INDEX(String, ':', -1)

KimHeinemann1113 · July 16

Thank you so much Mark :) I tried both of your formulas and this is the result I am getting. I was hoping to get the numeric string at the end instead of the name within a campaign. Any thoughts? Thanks again I truly appreciate the help.

MarkSnodgrass · July 16

Can you provide a few more sample campaign names? The word Expense is not in your first sample campaign name that you provided initially so I think I need to see a few more sample rows to make sure my suggestions will work for you.

KimHeinemann1113 · July 16

Of course, here you go, copied and pasted from the dataset

Display_Retargeting Package_07/09/24-12/31/24_BA:4019796

Search_AEC Package_07/09/24-12/31/24_BA:4019782

MarkSnodgrass · July 16

Are you referencing the correct field in your formula tile? Neither of these two samples contain the word Expense so there is no way the preview could display Expense in the result of the RIGHT function usage. I would double check that you are referencing the correct field in the formulas.

Based on these two samples, both of my suggested functions should return the numeric id you are looking to extract.

KimHeinemann1113 · July 16

I got it to work THANK YOU SOO MUCH! I didnt realize the data set had campaigns with the Expense name in it so I ran it and was able to get a column with just the campaign Id. :)

I have one follow up question if that is ok? Would I use the formula below to extract the numeric ID at the end of this utm_content name: ResponsiveSearch_4019775

SPLIT_PART(`utm_content`,'_',2)

What I am trying to figure out is how I can join two data sets, one with the campaign name with ID and one with utm content with ID, so I can get campaign and spend data in one card at the end. I figured extracting the ID's at the end of both of those fields and then joining on the newly created ID columns would be the best way.

MarkSnodgrass · July 16

Glad you got it to work. Yes, using the SPLIT_PART function with it looking for the underscore character will return the ID you want.

KimHeinemann1113 · July 16

Thank you so very much you have been super!

ETL Formula to extract numeric ID at the end of campaign name

Best Answer

Answers

Categories