Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
In my work, I have many "promotions" that consist of a number of discounted products. I also send out a weekly email that features many of these promotions at a time.
I have created a "PromoCode" that is unique to each promotion, as well as created a PromoCode reference table that contains relevant information. For example:
Fields: PromoCode PromotionName Discount PromoStartDate PromoEndDate
Data: abc123 Promotion1 30% 1/1/2016 2/1/2016
xyz456 Promotion2 50% 1/15/2016 2/15/2016
I also have an Email Metrics table that records the various send/open/click statistics for each email send. I would like to connect the PromoCode reference table to the Email Metrics table using the PromoCode. However, I need a way to connect multiple PromoCodes to each email iteration, as multiple promotions are included within each email. My table below fails because the "PromoCode" field from the reference table does not match up with the "PromoCode1", "PromoCode2", "PromoCode3", etc. fields within the Email Metrics table:
Fields: Email Name Subject Sent Opened PromoCode1 PromoCode2 PromoCode3
Data: Email1 Subject1 100 100 abc123 xyz456
Email2 Subject2 200 50 xyz456 def000 hij111
How can I resolve the naming issue but still connect individual PromoCodes to an email send (Email Name)?
Thanks in advance for any recommendations you may have!
Use a CrossTable load to change the structure of the email table so it matches up with the promotions
CrossTable(Dummy, PromoCode, 4)
LOAD [Email Name], Subject, Sent, Opened, PromoCode1, PromoCode2, PromoCode3
FROM ...;
Hi gysbert,
Thank you for your response. I ended up changing the data source to mirror a CrossTable output:
Fields: Email Name Subject Sent Opened PromoCode
Data: Email1 Subject1 100 100 abc123
Email1 Subject1 100 100 xyz456
Email2 Subject2 200 50 xyz456
Email2 Subject2 200 50 def000
Email2 Subject2 200 50 hij111
However, I'm wanting to use "Sent" and "Open" fields as measures. Using the format above for the "Count" function is giving me 800 "Sent" and 350 "Opened", when the numbers should be 300 "Sent" and 150 "Opened". I can use the "distinct" to only count unique numbers in these fields, but I'm certain future emails will have the same "Sent" and "Opened" numbers. Is there an expression that will count "Sent" and "Opened" by another field distinctly, such as "Email Name"?
Thank you for your time!
My apologies. In my previous response, I meant to say "Using the format above for the 'Sum' function is giving me...", not "Count".
Count(distinct ...) will give you the count of unique values for the given combination of dimension values.
If the combination of Email Name and Subject is not unique then you need to find a way to make a unique key for which there is only one distinct Sent and one distint Opened value.
Could I not use the Email Name as the unique key? I only want to count Sends/Opens once for each email.
My thinking is along these lines: For each distinct Email Name, record Sends and Opens once. If the line item below has the same Email Name, do not count Sends and Opens. Continue this until reaching the next unique Email Name, then record Sends and Opens. And so on...
Can I use a combination of Sum and Distinct to achieve this?
Thank you again for your assistance!