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

KimHeinemann1113 · 2024-07-16T15:31:33+00:00

There was an error rendering this rich post.

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!

Find more posts tagged with

Accepted answers

MarkSnodgrass

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.

All comments

MarkSnodgrass

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

a REGEX version would be:

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

ColemenWilson

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

SUBSTRING_INDEX(String, ':', -1)

KimHeinemann1113

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

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

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

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

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

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

Thank you so very much you have been super!