Campaign Reports

Power Query
1

Hi

I’m building a series of campaign reports.

For each report I have the list of customers we mailed and the sales as a result. Each mailing list includes a 50/50 split test.

Do I keep the mailing list and the sales separate or do I build the sales onto the mailing list using calculated columns?

Thanks

2

@KieftyKids,

My inclination here would be to have at minimum four dimension tables - Dates, Customers, Products and Mailings, and one fact table with each row containing at least Customer ID, Product ID, Mailing ID, Sales Date and Sales Quantity, with the dimension tables connected to the fact table by one-to-many relationships (i.e., standard star schema).

The Mailings dimension table would consist of the Mailing ID, the start and end dates of that mailer campaign, number mailed out, and all the attributes you are varying in the A/B testing. This structure would allow you to slice and analyze based on campaign attributes, easily calculate conversion rates, total sales, etc. by campaign and attributes. (Note: this assumes that for any given pairing of date and customer, only one mailer is active/valid at that time. If that’s not the case, the structure gets a whole lot messier…)

Ultimately though the key is to start with the questions you want to answer, and structure your data model in the way that makes those questions easiest to analyze.

I hope this is helpful.

  • Brian
3

Hi Brian

Thanks. That makes sense.

Before reading your reply I had tried using Query merge. However, I found not all of my sales connected with the mailing list for various reasons, mainly with online sales receiving a new customer number that is kept when the duplicate merge program combines the two records.