New and Returning Customers in Excel

In this blog let's take a look at how to compute New and Returning Customers with Excel's Dynamic Array Functions and Power Query.

I have this sample data that has Purchases made by the Customers on a given date, we will call this table Sales.

We want to get the count of new and returning customers by each date, so on 2025-02-03 we will have 5 new customers but on 2025-03-01 we will have 3 new (6, 7, and 8) and 2 returning (1 and 4)

We first start with 2 variables that hold the reference to Customer ID and Order Date.

=LET(
    CustomerID, Sales[Customer ID],
    OrderDate, Sales[Order Date]
)

Next we compute the First Purchase Date of each customer, this date allows us to identify on which date a customer is to be considered new and after this date they are to be considered as returning customer.

To obtain this we use the GROUPBY function.

CustomerFirstPurchase, GROUPBY(CustomerID, OrderDate, MIN, 0, 0)

Which returns the following table

Now we can merge these First Pruchase Dates to every instance of that customer with XLOOKUP.

=LET(
    ...
    Merge, XLOOKUP(
        CustomerID,
        CHOOSECOLS(CustomerFirstPurchase, 1),
        CHOOSECOLS(CustomerFirstPurchase, 2)
    ),
    HSTACK(CustomerID, Merge)
)

So now on the result of XLOOKUP we can create a boolean Flag that returns 1 when First Purchase Date = Order Date and 0 when they aren't equal.

=LET(
    ...,
    Flag, IF(Merge = OrderDate, 1, 0),
    Flag
)

Then we can use this column in the value fields of GROUPBY and on the rows we will use Order Date column.

=LET(
    ...
    Group, GROUPBY(
        OrderDate, Flag,
        HSTACK( SUM, LAMBDA(x, SUM(IF(x = 1, 0, 1))) ),
        0, 0
    ),
    Group
)

In the 3rd argument of GROUPBY I have provided 2 calculations, first is just a SUM over Flag that gets the new customers, and second one is LAMBDA which converts 0 to 1 so that we can sum the returning customers. Finally we treat the column headers and return the calculation.

Complete code:

=LET(
    CustomerID, Sales[Customer ID],
    OrderDate, Sales[Order Date],
    CustomerFirstPurchase, GROUPBY(CustomerID, OrderDate, MIN, 0, 0),
    Merge, XLOOKUP(
        CustomerID,
        CHOOSECOLS(CustomerFirstPurchase, 1),
        CHOOSECOLS(CustomerFirstPurchase, 2)
    ),
    Flag, IF(Merge = OrderDate, 1, 0),
    Group, GROUPBY(
        OrderDate,
        Flag,
        HSTACK(SUM, LAMBDA(x, SUM(IF(x = 1, 0, 1)))),
        0,
        0
    ),
    Result, VSTACK(
        {"Order Date", "New Customers", "Returning Cutomers"},
        DROP(Group, 1)
    ),
    Result
)

Result:

Doing this in Power Query is also fairly simple:

let
    Source = Sales,
    CustomerFirstPurchaseDate = Table.Group (
        Source,
        "Customer ID",
        { 
            "First Purchase Date", 
            each List.Min ( [Order Date] ), 
            type date 
        }
    ),
    Merge = Table.Join ( 
        Source, "Customer ID", 
        CustomerFirstPurchaseDate, "Customer ID" 
    ),
    Flag = Table.AddColumn ( 
        Merge, 
        "Flag", 
        each 
            if [Order Date] = [First Purchase Date] then 1 else 0, 
        Int64.Type 
    ),
    Group = Table.Group (
        Flag,
        "Order Date",
        {
            { "New Customer", each List.Sum ( [Flag] ), Int64.Type },
            { 
                "Returning Customer", 
                each List.Count ( List.Select ( [Flag], each _ = 0 ) ), 
                Int64.Type 
            }
        }
    )
in
    Group