Creating a calculated column from another table based on date criteria

[Anonymous]

Example file with Sales and Mailers Tables

Example End Product Conversion Chart by Campaign

Background:

We help boat dealers sell boats, we do marketing for them.

I have 2 tables:

• Mailer table – telling us who we physically mailed a marketing campaign to (as well as details on the mailer)
• Sales Table – telling us who actually bought our widget

Problem:

I'm looking to visualize conversions by different physical mail "Campaign" we send out. Right now there’s no column in our Sales table that says which campaign resulted in a sale.

I need to add a “Last attributed Campaign” column in my Sales Table so I can look at (Count of campaign type mailed [Mailers Table]) /(Count of campaign type resulting in a sale [Sales Table]) – to get conversion by campaign. All I need is the column in the sales table to do this.

Complex If / Vlookup type request

• How can we add a “Last Attributed Campaign” column into the Sales table with the below conditions?
1. Look at the “Deal Date” in the Sales Table, and if it is on the 13th of the month or before, then the column should return the Campaign from the previous month or earlier, whatever is latest.
1. For example, if someone bought a boat on May 5th,  we want to attribute that Boat sale to the "Campaign" that was sent before May.
2. If the deal date is on the 14th of the month or after, simply return the most recent "Campaign" sent – could be the same month
3. Every row in the Sales table should be filled in based on what’s in the Mailer table.

[parry2k]

@Anonymous quick question, I think you want to get the campaign id based on store / customer, not only just dealdate

[Anonymous]

I reviewed this post when I was researching my problem but I think it is off point.  I have a string of tables that are related to one another Booking => Order => Division.  I am trying to create a calculated column in my Booking table based on something in my Division table.  I beleive I need to create two related statements with the lookupvalue statement but am not sure how to do this.    

[Anonymous]

Hi @parry2k  - Yes, you are right, sorry for the confusion!

Basically the last Campaign the customer received before they actually bought a boat, based on those date criterias.

[Anonymous]

We can send the same type of campaign to multiple stores

[parry2k]

@Anonymous how about store? Does that matter? I noticed campaigns are by store

[Anonymous]

@parry2k Store is important because we send campaigns on behalf of a store, but it's really which campaign triggered the customer to buy.

Then I can show which campaigns are performing well for individual stores.

Thank you so much for your help on this!

[parry2k]

@Anonymous before i go deep into it, can you check if following campaign id i calculated for each dealdate looks correct based on the logic you provided.

[Anonymous]

Hi @parry2k  - sorry for late response - everything in your original table looks correct, except for 2 callouts.

• Customer ID BM0001118195, the answer should be M1, not M5
• Customer ID BM000190996 – should be M1 not M5

[parry2k]

@AnonymousI have solution ready, let me know where you want to send me. atleast this will get you started. Although on larger schema I will improve/make changes to data model.

[Anonymous]

Thank you so much @parry2k - if you're not able to add it here (feel free if you prefer to add for anyone else to see), I have google drive or one-drive, not sure what works best for you to share?

[Anonymous]

Hi @parry2k !

Thank you again, do you mind uploading to this one-drive folder please?

https://1drv.ms/f/s!AuheBS0uRNVlmDiCBAHEaJ7Yt-LV

Thanks again!

[parry2k · Accepted solution]

@Anonymous attached with this reply. 

[Anonymous]

@parry2k - sorry for late response, just a follow up to better understand campaign sales in the mailers table.

How exactly does this formula below work?

It gives me the right answer, but having trouble following the logic.

Campaing Sales = CALCULATE( COUNTROWS( Sales ), TREATAS( VALUES( Mailers[Campaign] ), Sales[Campaign] ) )

[Anonymous]

@parry2k  

I did a bit more research, and It would be a ton of help if I could understand the logic below, but also I'm wondering why we didn't use a summarize along with customer id so we join on both customer id and mailers?

It seems to be doing everything correctly, but I saw others using a summarize function.

Campaing Sales = CALCULATE( COUNTROWS( Sales ), TREATAS( VALUES( Mailers[Campaign] ), Sales[Campaign] ) )

[Anonymous]

@parry2k  Thank you so much!

[parry2k]

@Anonymous this is how sales looks like against each campaign based on the data, does it make sense?

[Anonymous]

Hi @parry2k !

Yes that looks right if all the numbers for Campaign sales are correct, I think I only saw 2 examples that didn't flow through, but the below is absolutely correct for what I'm looking for !!!

If I'm able to add the "Campaign Attributed" Column in the Sales table - I should be able to break out the performance of campaigns for different dealers as well!