Get the initial marketing channel for a customer with multiple transactions

[aukev]

I`m trying to get the initial marketing channel for a customer. I have three tables which simplified are:

Customer

customerID

initialMarketingChannel (this is where I want the marketing channel to show)

Transactions

date

customerID

transactionID

GAtransactionID (there's a chance that this is 0 if no marketing data is available for this order)

MarketingData

date

GAtransactionID

MarketingChannel

All tables have many to many relationships.

My goal is to find the initial marketing channel for each customer (if available).

[TeigeGao]

Hi @aukev ,

Could you please share some sample data and expected result to us for analysis?

Best Regards,

Teige

[aukev]

Sure, thanks!

This is a sample of the MarketingData

date	GATransactionID	MarketingChannel
Thursday, May 2, 2019	1397214386	Paid Search
Thursday, May 2, 2019	1397214390	Email
Saturday, March 16, 2019	1397201370	Paid Search
Wednesday, January 16, 2019	1397184884	Organic
Wednesday, January 16, 2019	1397184778	Direct
Wednesday, January 16, 2019	1397184822	Paid Search
Wednesday, January 16, 2019	1397184832	Organic
Wednesday, January 16, 2019	1397184790	Paid Search
Wednesday, January 16, 2019	1397184986	Paid Search
Wednesday, January 16, 2019	1397185038	Paid Search
Wednesday, January 16, 2019	1397185050	Social
Tuesday, January 15, 2019	1397184556	Paid Search
Tuesday, January 15, 2019	1397184530	Social
Tuesday, January 15, 2019	1397184520	Organic
Tuesday, January 15, 2019	1397184768	Direct
Tuesday, January 15, 2019	1397184766	Email
Tuesday, January 15, 2019	1397184704	Referral

Transactions:

date	customerID	transactionID	GATransactionID
01-02-2019	1	1	1397214386
01-03-2019	2	2	1397214390
01-04-2019	3	3	1397201370
01-05-2019	4	4	1397184884
01-06-2019	1	5	0
01-07-2019	2	6	1397184778
01-08-2019	2	7	1397184822
01-09-2019	4	8	1397184832
01-10-2019	6	9	0
01-11-2019	7	10	0
01-12-2019	8	11	1397184790
01-13-2019	2	12	1397184986
01-14-2019	9	13	1397185038
01-15-2019	1	14	1397185050
01-16-2019	7	15	0
01-17-2019	8	16	1397184530
01-18-2019	9	17	1397184520
01-19-2019	6	18	1397184768
01-20-2019	1	19	1397184766
01-21-2019	11	20	1397184704
01-22-2019	1	21	1397184756

Expected Result:

customerID	initialMarketingChannel
1	Paid Search
2	Email
3	Paid Search
4	Organic
5	None
6	Direct
7	None
8	Paid Search
9	Paid Search
10	None
11	Referral

[aukev]

Hi @TeigeGao , I`m still struggling with this. Any idea how best to solve this? Thanks!

[TeigeGao · Accepted solution]

Hi @aukev ,

In your scenario, we can use the following DAX query to create a calculated table: 

Result =
ADDCOLUMNS (
    VALUES ( Transactions[customerID] ),
    "iniID", CALCULATE (
        MIN ( Transactions[GATransactionID] ),
        TOPN (
            1,
            FILTER (
                Transactions,
                Transactions[customerID] = EARLIER ( [customerID] )
                    && Transactions[GATransactionID] <> 0
            ),
            Transactions[date], ASC
        )
    )
)

Then we can create a calculated column:

initialMarketingChannel =
VAR temp =
    LOOKUPVALUE (
        MarketingData[MarketingChannel],
        MarketingData[GATransactionID], Result[iniID]
    )
RETURN
    IF ( temp = BLANK (), "None", temp )

The result will like below:

Best Regards,

Teige