Results of a one-month Promotional Offer and rewards.

Playing the role of a Senior Marketing Analyst, I created this dashboard to show the results from a marketing campaign that has just ended. The report and dashboard is meant to be used by the CMO of the company.

Dataset

The dataset consists of three main tables:

• customers: Dimension Table - Demographic data for each member
• offers: Dimension Table - Details on the offers sent to customers during the 30-day period
• events: Fact Table - Data on customer activity, with records for transactions, offers received, offers viewed, and offers completed

Assumptions

Looking at the dataset, I assume that one customer can avail an offer only once (perhaps a customer-specific offer code is sent to each of them). If they want to avail the offer the second time, then they have to wait for the next launch of the offer.

Definitions

Reach: Total number of unique customers who have seen the promotion ad.

Views: Number of times promotion ad has been seen after receiving by the customers.

Impressions: Number of times an ad has been displayed or received on customers' screens.

Click-Through Rate (CTR): A metric that measures the percentage of ads which were clicked on, out of the total number of ads which were received by the customers. It is calculated as (Number of Views ÷ Number of Impressions) × 100.

Completion Per Customer: Number of times a single customer has completed a particular offer

Planning for the Dashboards

Since I see two dimension tables in the dataset, I believe that we can analyze the data from two different perspective: Customers and Offers.

Hence, I created two pages for the report:

• Customer Segment: shows the distribution of customers according to different attributes, how they respond to different advertisement channels, offer rewards and offer durations.
• Promotions & Offers: shows the performance of offers during the whole month, and over the period of the time. Also, it shows what channels and durations are the most successful for different offers.

Data Transformation

Customers

Firstly, I removed the customers where age is 118, gender is blank and income is null; it doesn't seem to be very realistic. (I may be wrong though!) I created a Date (type: Date) column from "became_member_on"

Offers

I created two columns for "channels", (channels2 and channels3) which format the data into a shorter form, which can save space in the dashboard and convey the same message:

channel = ['web', 'email', 'mobile', 'social']

channel2 = Web, Email, Mobile, Social

channel3 = WEMS

Events

I did some basic clean up and capitalized each word in certain columns, etc. Then I added a few columns.

• I broke down the "value" column into three different columns: "offer id", "Amount", "Reward". This helps me to keep a track of "offer id" of every row in a separate column.

• I sorted the rows in the Query Editor in ascending order according to 1. time, 2. customer_id 3.offer_id. This helps me to identify if one offer appears multiple time for one customer during the 30-day period. From there, I noticed that one offer is sent to a certain customer to be completed within a limited number of days. After this offer is expired, the same offer is sent to the same customer with another duration. Hence, I made the assumption, which I discussed in the Assumptions Section.

= Table.Sort(#"Changed Type 'Reward Amount'",{{"time", Order.Ascending}, {"customer_id", Order.Ascending}, {"offer id", Order.Ascending}})

• I created a column which concatenates "customer_id" and "offer_id". This is linked to the previous point where I can see if the offer appears for a second time for the same customer during 30-day period - I will assign a counter of 2 to that offer; for third time it will be 3, and so on. Hence the concatenated column would look like 'customer_id;offer_id;counter'.

• Finally I merged it with another table that I created: valid customers, as explained below.

Valid Customers

Table created where only one column is present "customer_id". This table is created after filtering out the invalid customers in "Customers" table as explained before.

I use the Valid Customers table to filter out the invalid customers in "Events" table. The method is explained below:

1. Filter the Customers table according to the criteria.
2. Right click on Customers table, and click Duplicate. A duplicate table is created.
3. Remove all the columns in the table except the customer_id column.
4. Click on Events table. Go to Home > Combine > Merge Queries. Do an inner join for the Events table and the newly created table based on customer_id.
5. Expand the Query by clicking on the newly created column in Events.
6. Right click on this new column and click Remove.

Columns Created in DAX

Customers Table

AgeGroup:

Grouping the customers according to age.

AgeGroup = 
SWITCH(
    TRUE(),
    customers[age] >= 18 && customers[age] <= 34, "18-34 yrs.",
    customers[age] >= 35 && customers[age] <= 49, "35-49 yrs.",
    customers[age] >= 50 && customers[age] <= 64, "50-64 yrs.",
    customers[age] >= 65 && customers[age] <= 74, "65-74 yrs.",
    customers[age] >= 75, "75+ yrs.",
    "Unknown"
)

IncomeGroup:

Grouping the customers according to income.

IncomeGroup = 
SWITCH(
    TRUE(),
    customers[income] >= 25000 && customers[income] <= 49999, "$25-$49.9k",
    customers[income] >= 50000 && customers[income] <= 74999, "$50-$74.9k",
    customers[income] >= 75000, "Over $75k",
    "Unknown"
)

MembershipLevel

Grouping the customers according to membership level.

MembershipLevel = 
SWITCH(
    TRUE(),
    INT(LEFT(customers[became_member_on],4))<2016, "Legacy Member (Pre 2016)",
    INT(LEFT(customers[became_member_on],4))<2018, "Loyal Member (2016, 2017)",
    "New Member (2018)"
)

Events Table

time_to_view

Time in hours that has passed after the customer has received the offer, when the customer views it.

time_to_view = 
VAR currentOfferID = 'events'[CustomerOfferId2]
VAR offerReceivedTime = 
    CALCULATE(
        MIN('events'[time]),
        FILTER(
            'events',
            'events'[CustomerOfferId2] = currentOfferID &&
            'events'[event] = "offer received"
        )
    )
RETURN 
    IF(
        'events'[event] = "offer viewed",
        'events'[time] - offerReceivedTime,
        BLANK()
    )

time_to_complete

Time in hours that has passed after the customer has received the offer, when the customer completes it.

time_to_complete = 
VAR currentOfferID = 'events'[CustomerOfferId2]
VAR offerReceivedTime = 
    CALCULATE(
        MIN('events'[time]),
        FILTER(
            'events',
            'events'[CustomerOfferId2] = currentOfferID &&
            'events'[event] = "offer received"
        )
    )
RETURN 
    IF(
        'events'[event] = "offer completed",
        'events'[time] - offerReceivedTime,
        BLANK()
    )

days_to_act

Grouping the 'time_to_view' and 'time_to_complete' in terms of number of days.

days_to_act = 
VAR SelectedValue = 
    IF(
        NOT(ISBLANK([time_to_view])),
        [time_to_view],
        [time_to_complete]
    )
RETURN
    IF(
        ISBLANK(SelectedValue),
        BLANK(),
        SWITCH(
            TRUE(),
            SelectedValue < 24, "< than a day",
            SelectedValue >= 24 && SelectedValue <= 120, "2 - 5 days",
            SelectedValue >= 121 && SelectedValue <= 168, "5 - 7 days",
            "More than 7 days"
        )
    )

seq_no

Column showing the nth time a certain offer has been received by a particular customer. From the dataset it looks like that one offer is sent to a customer multiple times during the 30-day period, so a counter helps to keep track which offer is the customer completing.

seq_no = 
VAR CurrentKey = events[customer_offer_id]
VAR CurrentTime = events[time]
VAR ReceivedTimeCount = 
    COUNTROWS(
        FILTER(
            events,
            events[customer_offer_id] = CurrentKey && 
            events[time] <= CurrentTime &&
            events[event] = "offer received"
        )
    )
RETURN ReceivedTimeCount

CustomerOfferId2

Concatenating 'customer_offer_id', 'seq_no'. The result contains customer id, offer id and seq_no, which helps us to determine the number of times an offer has been sent to a particular customer, as explained above.

CustomerOfferId2 = 
events[customer_offer_id] & " ; " & events[seq_no]

In the table above, we can see one offer being sent to a customer twice during the 30-day period.

Days

Expressing the 'time_to_view' and 'time_to_complete' in terms of days.

Days = 
VAR _SelectedValue = 
    IF(
        NOT(ISBLANK([time_to_view])),
        [time_to_view],
        [time_to_complete]
    )
RETURN
    IF(
        ISBLANK(_SelectedValue),
        BLANK(),
       (_SelectedValue)/24
    )

Data Model

Observations

• We reached 100% out of our total 14,825 membership customers.

• 29.6K (23.9%) of the Sales to the customers was related to Offers out of the total 124.0K. This shows a significant impact of offers on the sales, however, more data would be needed to compare our current-month sales with our routine monthly sales to measure the true impact of offers.

• 606.4K (34.9%) of the Sales Revenue was driven by Offers out of the total 1,734.9K - a significant amount of sales has been driven by offers. However, more data would be needed to compare it with our routine sales.

• Our targeted membership customers are aged 35 yrs. or more, with a moderate or high average-income. Any offer that fits into their lifestyle and routine would be attractive. More than 80% of the Promotion-Driven Sales came from our Legacy and Loyal Membership customers, which show their trust in our products and our established customer base; motivating us to launch such promotions in the future.

• Customers prefer two or more channels for ads, especially Mobile and Social Media, where they can view the ad quickly.

• Average time for completion of offers is well below half of the offer duration, suggesting that the customers react fast when offer is announced. This implies that offers can be announced more frequently and with probable less duration. This trend is seen for all three different types of durations.

• Customers aged 35 yrs. and above like to know about the offers - view the offer after receiving it. However, customers aged 50 years and above like to avail the offers after viewing it. Since they constitute majority of our membership base, they drove more than 70% of the Promotion-Driven Sales, with customers aged 50-65 yrs generating 40% of Sales Revenue.

• The promotion is more successful when using two or more channels, especially Mobile and Social Media, since the customers can view the offer quickly. Sales Revenue generated by 'Discount' and 'BOGO' is very close, however, % of Completion is relatively more in Discount. It may show that BOGO generates more Revenue per Completion but customers desire a 'discount' more than an extra cup of coffee.

• Customers view the offer quickly after its announcement, and make relevant purchases. The response fades as time passes, which picks up once the offer is re-announced. Same quick response is seen whether the offer is announced weekly or twice a week. It shows the potential to announce offers frequently to generate more Sales.

• Revenue from BOGO and Discount is almost same, however, Rewards for BOGO are much more than those for Discount Offers - Rewards for BOGO are 111.1 K (69% of 160.5K), implying that Discount Offers should be promoted more, which is more attractive to the customers and profitable for the company.

Recommendations

• Continue focusing on our customer base aged 35+, because the offer was highly successful among this group. Additionally, any offers suitable for the youth should be launched which may be attractive to their lifestyle.

• Middle and high-Income customers have a high CTR and Completion Rate, suggesting that we can continue to launch such offers in the future for this group. However, the low-income customers have a low CTR and Completion Rate; Completion Rate is only 36% in this group as compared to overall 49%. We may have to launch different offers for such customers, encouraging them to buy.

• New members, who joined in 2018, utilize the offer very less as compared to our loyal and old members. Launching offers for new joiners may be a marketing area to explore, where we can give discount on first couple of purchases.

• We sent weekly offers for the first two weeks and then we sent offers twice a week. The success rate was similar; which means that we can make frequent offers (perhaps twice a week), if possible. This will generate more revenue. The frequency also reflects the fact that the purchases are more when the offer is launched, and then the response fades out. Another offer has to be launched to boost the sales.