How do i reclassify fields via ifelse and locate

Question & Answer
1

Hi
my marketing dataset is made up of:
“lead entry date”, “lead name”, “page converted to”.

I want to analyze the “url keywords” that convert the most (compared to a defined list)

Example:
ifelse(locate(‘page converted to’, “smart-working”) > 0,‘smart-working", “nothing”) this for the smart working keyword

but

Example:
ifelse(locate(‘page converted to’, “smart-working”) > 0,‘smart-working",
ifelse(locate(‘page converted to’, “hr”) > 0,‘hr", “nothing”)

if a ‘page converted to’ is “agendadigita.eu/hr/lo-smart-working”,

the “lead name” that have converted from this url will only end in “smart-working” and not in both, I would like them to be counted for both

2

Hi @andreab - each calculated field can hit each row of raw data one time, so if you have both of these conditions in a single ifelse it will only count the first time it satisfies a condition.

If you created these as two different calculated fields, then they can each hit that row of data and it will count it twice. However now you will have two fields so creating the type of visual you want may or may not be possible. If you add them both into a table or bar chart you will be able to count it both times no problem though.

What visual are you trying to create in the end?

3

Hi @Jesse
I work for a network of sites, our articles are cataloged under 40 topics and each topic is made up of a series of keywords.
we are talking about more than 2000 keyword.
my aim was to know over time which is the keyword that converts the most
I came to the conclusion that the only way was to create a calculated field for each keyword. of the 2000 I will try to make a selection.

I was also fine with a table as a graph, then I would have used date filters

4

Yes the only way with the data in the current shape it is in is to have a calculated field per keyword.

Otherwise you will need to reshape the data so that keywords are all in the same column. This will result in many more rows in the data, but that should be fine (we can have up to 500M rows of data per SPICE data set as of today).

You could use AWS Glue DataBrew (or another data prep tool) to parse the ‘page converted to’ field by each ‘/’, and then unpivot (there is a function for it) all those resulting columns into rows. Then you will have just 1 keyword column and can plot it in whatever visual type you want.