Data model

[deepakqlikview_123]

Hello Expert,

PFA.

Can you please suggest how to build data model for this.

Thanks,

Deepak

[puttemans]

Hi,

You need to find the common links first, and make sure there is only 1. I guess that customer and customer ID will contain the same information, so by renaming one of them similar to the other in the load statement, you create a link within QV. If you only have 1 campaign every month, then the start field of the campaign may be linked with the month field in clicks. If that is not the case, then you need to look further into your data (I don't know them). If you do not find a good connector, then you may need to create one first if that's possible.

Regards,

[#3]

upload a sample of each table and I will tell you

Cheers,

Byron

[vikasmahajan]

Hii

Please find sample application will help you build data model.

  Vikas

[deepakqlikview_123]

Hello Bryn

Cliks table

EmailOpenTime	Customer id	Month	Year	Clicked
12/29/2012 12:00	1	12	2012	Clicked
2/2/2013 12:00	2	2	2013	No Click
3/2/2013 7:00	3	3	2013	Clicked
12/25/2012 16:15	4	12	2012	Clicked
12/26/2012 16:15	5	12	2012	Clicked
12/27/2012 16:15	6	12	2012	No Click
12/28/2012 16:15	7	12	2012	Clicked
12/29/2012 16:15	8	12	2012	Clicked
12/30/2012 16:15	9	12	2012	No Click
12/31/2012 16:15	10	12	2012	Clicked
1/1/2013 16:15	11	1	2013	Clicked
1/2/2013 16:15	12	1	2013	Clicked
1/3/2013 16:15	13	1	2013	Clicked
1/4/2013 16:15	14	1	2013	Clicked
1/5/201316:15	15	1	2013	No Click
1/6/2013 16:15	16	1	2013	Clicked
3/7/2013 16:15	17	3	2013	Clicked
1/8/2013 16:15	18	1	2013	No Click
1/9/2013 16:15	19	1	2013	Clicked

campaign table

Campaign   id	Start	End	Name	Cost
1	12/25/2012	1/2/2013	XMAS	50K
2	1/1/2013	1/29/2013	JAN SALES	75k

Customer table

Customer	Age	Country	Gender
1	24	UK	Male
2	33	France	Female
3	17	Germany	Male
4	58	UK	Male
5	53	UK	Female
6	33	UK	Male
7	55	Germany	Male
8	42	UK	Female
9	44	UK	Male
10	45	UK	Male
11	51	UK	Female
12	22	UK	Male
13	24	UK	Male
14	36	Germany	Female
15	50	UK	Male
16	12	UK	Male
17	30	UK	Female
18	15	UK	Male
19	12	UK	Male
20	17	Germany	Female

can you please send me   screenshot for data model I do not have licenced version,

thanks

Deepak

[#6]

Paste this code in your load script after you have done a load of the original tables

/***********************************************************************************

Step 1: Cleanup and Primary Key

***********************************************************************************/

CampaignFinal:

NoConcatenate

Load

  "Campaign id" as CampaignID,

  Alt(Date#(Start,'MM/DD/YYYY'),Start) as Start,

  Alt(Date#(End,'MM/DD/YYYY'),End) as End,

  Capitalize(Lower(Name)) as Name, //Cleaner look

  if(Right(Upper(Cost),1)='K',num(PurgeChar(Upper(Cost),'K'))*1000,num(Cost)) as Cost //Proper numberic formatting, assumed there is either a 1000 indicator i.e. k or straight value

Resident Campaign;

Drop Table Campaign;

CliksFinal:

Load

  CustomerID,

  Month,

  Year,

  EmailOpenTime,

  Date(Floor(EmailOpenTime)) as EmailOpenDate;

Load

  "Customer id" as CustomerID, //"Customer id" and Customer in Customers table are the same

  Month,

  Year,

  Alt(Date#(EmailOpenTime,'MM/DD/YYYY hh:mm'),EmailOpenTime) as EmailOpenTime

Resident Cliks;

Drop Table Cliks;

CustomersFinal:

NoConcatenate

Load

  Customer as CustomerID, //Customer and "Customer id" in Cliks table are the same

  Age,

  if(Country='UK','United Kingdom',Capitalize(Lower(Country))) as Country, //EU will now see a clean list box of full country name

  Capitalize(Lower(Gender)) as Gender //To ensure same text formatting

Resident Customers;

Drop Table Customers;

/***********************************************************************************

Final Step: Join Interval Match Table

***********************************************************************************/

IntervalMatch (EmailOpenTime)  // Create an interval link table to EmailOpenTime

JOIN (CampaignFinal)

StartEndDate:

Load

Start, End

Resident CampaignFinal;

exit script;

[#7]

For those that don't have personal edition, here is the qvw

[deepakqlikview_123]

Hi Byron,

Thanks it is very useful for me

Can u plz tell me what to do in below scenario

An online marketing team sends out two email campaigns to customers using their CRM system. The first is their Christmas campaign and the second is the New Year sales campaign.

You are asked to deliver an interactive dashboard to help the CEO analyse the performance of the email marketing campaign over Christmas and the New Year sales.

thanks

[#9 · Accepted solution]

Hi

Load this script in you application.

Click:

LOAD Date(EmailOpenTime) as EmailOpenTime,

     [Customer id] as Customer,

     Month,    

     Year,

     Clicked

FROM

C:\Users\windows\Desktop\QVDS\Click.qvd

(qvd);

Campaign:

LOAD [Campaign id],

     Start,

     End ,

     Name,

     Cost

FROM

C:\Users\windows\Desktop\QVDS\Campaign.qvd

(qvd)

where [Campaign id] <> '' ;;

Join

IntervalMatch (EmailOpenTime)

LOAD Start,

     End as CampingEndDate

Resident Campaign;

Customer:

LOAD Customer,

     Age,

     Country,

     Gender

FROM

C:\Users\windows\Desktop\QVDS\Customers.qvd

(qvd);

[#10]

Hi Deepak,

Its a pleasure. If the above solution is the right one, please mark it correct so others know as well.

With regards to your next quesiton, once again you need data which im sure they provided in the question. The question is quite simple; compare performance for the different campaigns.

Since the data will most probably share exactly the same fields with the exception that there should be a field that indicates which campaign, you would concatenate

Concatenate(Christmas)

LOAD *

Resident NewYear;

...or along those lines

Cheers,

Byron