Search code examples
filterlooker-studio

getting first purchase date for each customer

I want to get the first order date for each customer in a new field in Google Data Studio.

For example, I want this result:

cust_id order_id order_dateTime first_order_dateTime
a1 wer 09/13/2018 2:23:10 PM 09/13/2018 2:23:10 PM
a1 sdf 09/13/2018 2:25:07 PM 09/13/2018 2:23:10 PM
a1 aqf 11/24/2018 9:11:43 AM 09/13/2018 2:23:10 PM
b7 nmk 02/15/2018 7:40:14 AM 02/15/2018 7:40:14 AM
c41 dst 03/08/2019 1:33:14 PM 03/08/2019 1:33:14 PM
c41 mvh 04/23/2020 7:51:21 AM 03/08/2019 1:33:14 PM

I get it in Power BI using this code:

first_order_dateTime = CALCULATE(min('table'[order_dateTime]) , ALLEXCEPT('table', 'table'[cust_id]))

but I don't know How can I get it in google data studio.

Note that I want the first_order_dateTime field in DateTime format.

Solution

  • To get this result you need to do:

    Create a data combination, combining the base with itself. In the combination, use the cust_id binding key. In the base to the right, insert the dimension order_dateTime, in the base to the left insert the metric with aggregation of type MIN to order_dateTime, calling this metric first_order_dateTime.

    After performing the combination, just insert your data into a table to view the result, inserting the cust_id, order_daterTime and first_order_dateTime columns.

    ]