I have 2 tables:
I'm looking to add a "Last Attributed Template Type" from our Mailer table into our Sales Table. Right now there’s no column in our Sales table that says which Template resulted in a sale.
I need to add a “Last attributed Template” column in my Sales Table so I can look at (Count of template type mailed [Mailers Table]) /(Count of template type resulting in a sale [Sales Table]) – to get conversion by template type.
@parry2k was awesome and helped me to get the right attribution for the "Last Attributed Campaign", I was later asked to also add last attributed for "Template Type", and others - all of which worked with that formula except for the "Last Attributed Template Type".
For example customer MB000139332 is incorrectly pulling from a different row in the Mailers Table that all the other "Last Attributed" Columns are pulling from - should be template "E" as opposed to "H"
How can I get it so that "Last Attributed TemplateType" also pulls from the same row as the other Last Attributed Columns - not sure why it's behaving differently?
Each of the calculated columns calls MAX on a text field.
In the case of 1393332, there are two records for the getLatestCampaignDate (both on 1st March 2018). The DAX code for the calculated columns returns the larger of the column required (so it'll be the later in alphabetical order)
For the templateType, 'H' is bigger than 'E' so that gets returned.
For the % likely to buy, '81-90' is bigger than '31-50' so that gets returned.
That's why it's happening. You'll have to decide how to return the correct fields if there are two records for the same date.
Thank you @HotChilli !
So I basically want the same values as shows up for the "Last Attributed Campaign", is there a way for me to just get the Deal type to return the corresponding template that is on the same row as the "Last Attributed Campaign"?
"Last Attributed Campaign" works on the same basis i.e. uses MAX on a text column. Usually one formulates a business rule to decide which fields to select and return. Is this possible?
Hi @HotChilli - First off, thank you so much!!
The business is fine with even returning the first non-blank value since it's essentially a tossup to attribute a sale to 1 mailer when 2 or more were sent in the same month.
I think maybe swapping out Max in this case for first non-blank would handle it.
If I leave the "getlatestcampaigndate" variable as a max, but swapped Max for firstnonblank for variable after that for all the "Last Attributed columns"- they should all pull from the same row correct? And still pull from the most recent column based on the criteria we laid out earlier (still march in the problem example we had earlier)?
On a side note - is there a resource I might be able to look at different business rule options? I have a feeling that the business would like to know what their options are.
@HotChilli - I did test adding the firstnonblank for Last Attributed Templatetype, and though that worked correctly, when I changed the other "Last Attributed" columns to firstnonblank, it ended up changing to different columns again, so unless I just change the Templatetype column to first-nonblank, that seems to make it work in this case, though not sure if that logic is the best way to ensure that the values are pulling from the same column.