2 Replies Latest reply on Sep 6, 2019 6:19 AM by Toby Erkson

# I have requirement that calculating effect on sale

@Tableau version 2019.2.2(20192.19.0)64 bit@

..The main requirement is that we have to find out the effect of that campaign on sale ...

means before campaign and after 22 days  campaign sale .

i have campaign start date but dont have campaign end date ..i discussed with teams but not having that field...so we are assuming the scenario  in our database 1 field is there email send date  so which email is send at last we assume that is last day of that particular campaign communication..

though i need sale before each campaign and after each campaign..

for after campaign we assume that max 22 days of transaction period is campaign effectiveness and those customer who make transaction in that 22 days after campaign over then we say here some effect will be done .

eg:

campaign 1 start at======= 1 jan 2019 to 10 jan 2019 as new year sale ...

campaign 2 start at ====15 jan 2019 to last date of email send date of that perticular campaign ..assume 15 jan 2019 to 25 jan 2019 as Republic Sale...

we have to find out that how many customer bought or make transactions after 20 jan 2019 to 12 feb 2019(22 days) for campaign 1 ...

and 15 jan 2019 to 25 jan 2019 as Republic Sale...?

and how many customers come to store and how much sale is done in between (20 jan to 12 feb 2019 in 22 days after campaign1)

likewise other campaign also ..

how much sale before that campaign and after that campaign...

but the cavet is that in 1 month there could be 2-3-4 campaigns are running so .....but for time being sales before capaign and after campaign

and number of customers come in the period of that 22 days ...

.

#### following is the example which i refer for calculation...is this ok?

1.can we make parameter for campaign ===campaign1,campaign2,campaign3...campaign145?

2.can i use {fixed [campaign id] : if [trx dt] <=  [campaign start date] then sale else 0]

{ FIXED [Trx date]:MAX(

IF [Trx date]>=[campaign start date]

AND [Trx date]<=[last email send date of that perticular campaign ]

THEN "campaign id"

END)}

but it shows aggregated values not allowed

3,LOGIC@@

1. Create parameter [Report Date]

2. Calculate the date each customer purchased the catalog, [MMPCAT BILL_DT]

{ FIXED [campaign id]: MAX(IF [PROD_ID] = "MMPCAT" THEN [BILL_DT] END)}

3. Calculate sales before catalog purchase, [SALES BEFORE MMPCAT]

{ FIXED [BSNSS_ID]: SUM(IF [BILL_DT] >= DATEADD('year', -1, [MMPCAT BILL_DT]) AND [BILL_DT] <= DATEADD('year', -1, [Report Date]) THEN [NET_AMT] END)}

4. Calculate sales after catalog purchase, [SALES AFTER MMPCAT]

{ FIXED [BSNSS_ID]: SUM(IF [BILL_DT] >= [MMPCAT BILL_DT] AND [BILL_DT] <= [Report Date] THEN [NET_AMT] END)}

Hope this helps.

Ossai

4. LOGIC@@

Here´s the solution example, first i LEFT JOINED the sales data on the campaign data, then i wrote a calculated field with

IF [DEPT CODE]=[Category (Sales)] AND

DATEDIFF('day',[Sent Date],[Purchase Date])>0 AND

DATEDIFF('day',[Sent Date],[Purchase Date])<30 THEN [Sales Total] END

The Result is

Campaign 1: 500
Campaign 2: 100

Campaign 3: 100

how can i put logic..????i am really getting brained blast.

field:

is email send= total email send

email sent date,---for each customer when email send

campaign start date==for particular campaign when campaign is started

trx date.==when customer make any transaction

partyuid==unique customers

campaign id/promo id== particular events or activities for sale or for social cause for information

member created date=(created)....when that particular customer profile is made

please give me flow and logic calculation which i CAN use  for this requirement....i stuck in all the side

Message was edited by: prashant patel

• ###### 1. Re: I have requirement to create Email Marketing the dashboard ..i am new to tableau so dont have enough knowledge on calculations ...so please help me out

It would help if you include the workbook with the dashboards, not just the data. However, I can already see that you seem to be working with an extract that does not include the raw data for the campaign. Either that or data is not collected with enough detail as the email opening count has already been summed on a per customer basis as far as I can see.

Once you have the appropriate data, I would guess that a COUNTD (count distinct) function would do the job for you. It could be that you need to use a Fixed LOD calculation (there are plenty of resources online for these), but my guess is that a simple COUNTD would be sufficient.

As a quick and dirty approach to use with the current data set you could possibly use a calculated field that generates a 1 for customers that have opened a mail and a zero for those that have not: IF ([Email Open Count]) =0 THEN 0 ELSE 1 END. You could then sum this to get the number of customers that have opened the mail

A couple of tips for your journey into the world of Tableau:

• Take the time to go through the free training videos on the Tableau web site and use the demo workbooks provided with each. It's worth the time. They're very good and I still refer back to them every now and again when I am getting myself confused about something.
• Understand your data. See my post and the attached links here. Going back to the basics of what is in your data source can be extremely helpful when something does not seem to be behaving the way you expect or want. Key word: grain!

Tim

• ###### 2. Re: I have requirement that calculating effect on sale

Please read this document on the proper use of @Mentions:  @Mention Etiquette Guide