New, lost, and returning customers

Overview

The tables below show two separate lists of customers: one, a list of customers that bought something from the company in the current year, and the other, a list of customers that bought something from the same company in the previous year.

This article explains how, using automated queries, to divide these customers into three groups:

• those that bought something this year and last year (returning customers)
• those that bought something last year but not this year (lost customers) and
• those that bought something this year but not last year (new customers)

Steps

1. Create queries for your source tables

• For both tables, click inside the table in the Excel worksheet, then click the From Table/Range icon on the Data tab in Excel to create queries of both tables.
• Call the queries: ThisYearCustomers and LastYearCustomers.

2. Append queries, Merge columns, Remove duplicates

• Click the Append button and select these two queries in the drop-down boxes, as shown below, then click OK.

A new query will be created, and the Query Editor will open.

• Call the new query Customers. The query will show the result of the append operation, as shown below:

The next thing to do is to turn this table into a single list of customers.

• Select both columns in the table in the Query Editor, then right-click and select Merge Columns from the right-click menu.
• Leave the drop-down box with the separator value at the default of None, and then click OK.

The output should look like below:

This merged table of customers still contains some duplicated customer names.

• To remove these duplicates, click the Remove Duplicates button on the Home tab in the Query Editor toolbar.

The output will be as shown:

Rename the column in your table from Merged to Customer by double-clicking the column header and entering the new name.

3. Define logic

Now that you have a complete list of customers, you need to find which of them appear in the list of this year’s customers. To do this you can merge the current query with the query called ThisYearCustomers.

• Click the Merge Queries button on the Home tab in the Query Editor toolbar
• Then, select ThisYearCustomers in the drop-down box of the Merge dialog, select the Customer column and the CustomersThisYear column as shown below, then click OK.

The previous step adds a new column to the table containing the merged rows from the CustomersThisYear query.

• Click the Expand icon in the new column, select the Aggregate radio button, and then click the drop-down box next to the column name to select the Count (Not Blank) option, as shown below. Click OK.

This will replace the new column with a column containing the number of rows in CustomersThisYear that matched the current row—that is, it will contain the value 1 when the customer bought something in the current year and 0 if the customer did not buy anything.

• Rename the column created in the previous step ThisYear by right-clicking the column header and entering the new name. The table should be as shown below.

• Repeat the earlier steps, but use the LastYearCustomer query and call the new column created LastYear. The output should be as shown below.

4. Add a custom column

• To classify each customer, click the Custom Column button on the Add Custom tab in the Query Editor toolbar, call the new column Classification, and enter the following expression, as shown below:

   if [ThisYear]=1 and [LastYear]=1 then "Returning" else if [ThisYear]=1 and [LastYear]=0 then "New" else "Lost" 

Finally, select the ThisYear and LastYear columns, right-click, and select Remove Columns. The final output of the query should be as shown below.

5. The ‘M’ code

let
    //Append the LastYearCustomers table to the ThisYearCustomers table
    Source = Table.Combine({ThisYearCustomers, LastYearCustomers}),
    //Merge the two columns into one
    #"Merged Columns" = Table.CombineColumns(Source,{"Customers This Year", "Customers Last Year"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    //Remove duplicate customers
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Merged", "Customer"}}),
    //Merge this query with the ThisYearCustomers table
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Customer"}, ThisYearCustomers, {"Customers This Year"}, "ThisYearCustomers", JoinKind.LeftOuter),
    //Aggregate the resulting column of tables by Count (Not Blank)
    #"Aggregated ThisYearCustomers" = Table.AggregateTableColumn(#"Merged Queries", "ThisYearCustomers", {{"Customers This Year", List.NonNullCount, "Count (Not Blank) of Customers This Year"}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Aggregated ThisYearCustomers",{{"Count (Not Blank) of Customers This Year", "This Year"}}),
    //Merge this query with the LastYearCustomers table
    #"Merged Queries1" = Table.NestedJoin(#"Renamed Columns1", {"Customer"}, LastYearCustomers, {"Customers Last Year"}, "LastYearCustomers", JoinKind.LeftOuter),
    //Aggregate the resulting column of tables by Count (Not Blank)
    #"Aggregated LastYearCustomers" = Table.AggregateTableColumn(#"Merged Queries1", 
    "LastYearCustomers", {{"Customers Last Year", List.NonNullCount, "Count (Not Blank) of Customers Last Year"}}),
    //Rename the new column to LastYear
    #"Renamed Columns2" = Table.RenameColumns(#"Aggregated LastYearCustomers",{{"Count (Not Blank) of Customers Last Year", "Last Year"}}),
    //Use the ThisYear and LastYear columns to classify each customer
    #"Added Custom" = Table.AddColumn(#"Renamed Columns2", "Classification", each if [This Year]=1 and [Last Year]=1 then "Returning" else if [This Year]=1 and [Last Year]=0 then "New" else "Lost"),
    //Remove unwanted columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"This Year", "Last Year"})
in
    #"Removed Columns"