Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

brand switch analysis

Hi,

 

I want to do a brand switch analysis, 

 

i have following table, where in 2020/6 the brand "ABC" did an advertising campaign for their product "water".

 

I want to get 3 values:

1) new customer (during campaign) -> 1 (customer id no. 2)

2) brand switch (during campaign) -> 1 (customer id no. 1, switch from DEF to ABC)

3) repeat (no change in purchase behaviour)  -> 1 (customer id no.3 , no change before and after the campaign bought ABC's water)

 

year-monthcustomer idproduct (bought)brand nameadvertising flag
2020/51waterDEF 
2020/53waterABC 
2020/62waterABCyes
2020/61waterABCyes
2020/63waterABCyes
1 ACCEPTED SOLUTION
2 REPLIES 2
Anonymous
Not applicable

thanks, these articles were helpful!

I thought I'd share my solution quickly.

 

new customer=

RANKX(
FILTER(
     'table',
          [product_name] = EARLIER([product_name])
   && [customer_id]=EARLIER([customer_id])

),
[date],,ASC
)
 
 
brand-change customer =
IF (
COUNTAX (
FILTER (
        'table',
              [customer_id] = EARLIER([customer_id])
       && [date] <= EARLIER([date])
       && [product_name] = EARLIER([product_name])
       && [brand name] <> EARLIER([brand name])
), [customer_id]
) = 1,
1,
0
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.