Cafe Rewards Data Dash

Initially, preparation in Power Query involved mainly creating a friendlier naming structure for the offers. The Difficulty, Reward, and Duration columns were merged with '-' as a separator, and then the type was appended to the relevant offer.

Three dimension tables were created to filter by Age, Income Level, and Gender. Age and Income levels were grouped into buckets using a calculated column in the model. Usually, I prefer Power Query for such tasks, but this approach was suitable given the small table size.

During the analysis, it was crucial to attribute transactions to completed offers. This was achieved by duplicating the 'events' table, filtering for 'offer completed', and then merging it back to the original events table on [customer_id] where both "transaction" and "offer completed" occurred simultaneously.

A disconnected table was also created for placing transaction amounts into buckets, each defined by a MIN and MAX column. For instance, a $10-$20 range has a MIN of 10 and a MAX of 20. This method, though simple, effectively leverages the power of DAX to organize large data amounts into easily understandable buckets.

Transactions_Range =

CALCULATE(

SUM(events[amount]),

events[event] = "transaction",

FILTER(

events,

COUNTROWS(

FILTER(

DIM_transaction_range,

events[amount] > DIM_transaction_range[Min] &&

events[amount] <= DIM_transaction_range[Max]

)

) > 0

)

)

This measure filters rows from the 'events' table that fall between the selected row's MIN and MAX columns in the matrix and sums the transaction values.

Calculated Columns and DAX Measures:

Several modifications were made to the 'events' table. A [days] column was created to represent time in days rather than hours, aiding in analysis and visualization:

days = [time] / 24

A [customer_event_index] column was added to facilitate data auditing by tracking individual member behaviors throughout the event period. This was invaluable for auditing and understanding any anomalies, as well as comprehending the dataset and its context.

customer_event_index =

VAR customer_id = [customer_id]

VAR event_time = [time]

RETURN

CALCULATE(

COUNT(events[customer_id]),

FILTER(

events,

events[time] <= event_time &&

events[customer_id] = customer_id

)

)

When it became apparent that many members completed multiple offers simultaneously, I needed to count and monitor this throughout the 30-day period:

Customers_Multiple_Offers_Same_Time =

VAR OfferCompletions =

SUMMARIZE(

FILTER(events, events[event] = "offer completed"),

events[customer_id],

events[time],

"Offer_Completions", COUNTROWS(events)

)

VAR MultipleOffersFilter =

FILTER(

OfferCompletions,

[Offer_Completions] > 1

)

RETURN

COUNTROWS(MultipleOffersFilter)

To determine the effectiveness of individual channels, a simple yet clear visual was created using a matrix with icons, employing the CONTAINSSTRINGEXACT function to the matrix with Channels at the columns and the Offers in the row:

offer_channel =

VAR _channel = SELECTEDVALUE(channel[channel])

RETURN

IF(CONTAINSSTRINGEXACT(SELECTEDVALUE(offers[channels]), _channel), 1, 0)

Next we apply the conditional formatting to the values using only the icons for clarity. If the offer channel column matches the individual channel name that is in the column then we use a O and an X if it doesn't match :

and then ta da!

While many other measures, methods, and techniques were used in this project, these were some of the most interesting developments I wanted to share.