Favorite Store

• The favorite store is the most popular store for each customer.

• We define the favorite store as the store where the customer has made the most purchases.
• We don’t consider “Ecommerce” as a store.
• If the customer has realized the number of orders in 2 different stores, the favorite store will be considered as the one who received the most recent orders.
• Starting from one order, a store could be considered as favorite.
• The favorite store is defined as a new attribute in the Contacts table with the “Store ID” contained in the orders table.

• Personalization :
• Display the favorite store in an email or on the website.
• Push specific offers related to the favorite store to the right customer

• Discovery : Advice customers that a new store has open in their location.

• Analysis :
• Understand how customers are distributed amongst stores.
• Understand what drives the choice of a favorite store > corelation to distance, store size, historical choice / habit, etc.

• Impact on: Web to store, ROPO.

• Level of impact: ⭐️⭐️️

• A Customers dataset with following attributes
• customer_id: unique identifier of the customer who made the purchase.

• An Orders datasets with at least following attributes
• order_id: unique identifier of the transaction.
• store_id: unique identifier of the store in which the order has been made.
• order_date: date at which the purchase was made.
• customer_id: unique identifier of the customer who made the purchase.

• Restrict the scope of orders to a given timeframe, for example : last 3 years.

• Add a higher ponderation to recent orders.

• The output of this recipe is a new Dataset containing a row for each of your contacts who made at least one purchase.

• In the Dataset table, you will have a 3 new attributes
• favorite_store_id : ID of the favorite store for all orders in the scope.
• weekdays_favorite_store_id : ID of the favorite store for orders that during week days.
• weekends_favorite_store_id : ID of the favorite store for orders that during weekends.

• You can then make a join between your new Dataset and your existing Contacts Dataset to map your favorite store attributes.

• Create a new Dataset using SQL expert mode.

• Copy / paste the following SQL template.

• After pasting the template, make sure to modify all variables with the names of your input columns.

• Add your “Favorite Store” Dataset as a source of your Contacts Dataset, map the needed fields to import them into your Contacts Dataset.

sql
/* Favorite Store SQL Template */

/* 
		Ponderation method:
    To add a higher ponderation to recent orders, we assign a weight value for all orders based on the order date.
    Parameter must be in ]0;1] range, a small value of parameter priviliges the recency.
    Setting parameter to 1 gives the same weight to all the orders, which cancels the ponderation.
*/

WITH order_weight AS (SELECT "contact_id",
                             "store_id",
                             "created_at"::TIMESTAMP,
                             POW(
                                     0.95, -- Tweak this parameter to change the weighting, set it to 1 to cancel ponderation.
                                     DATE_PART('day', NOW() - "created_at"::TIMESTAMP) / 30
                                 ) AS "weight"
                      FROM raw.orders
                      WHERE
                          /* Optional filters depending on your dataset */
                          "created_at"::TIMESTAMP BETWEEN CURRENT_DATE - INTERVAL '3 years' AND CURRENT_DATE
                        AND "category" <> 'online' -- Filter E-commerce
                        AND "total_amount" > 0
                        AND "completed" IS TRUE),

     store_scores AS (SELECT "contact_id",
                             "store_id",
                             MAX("created_at")                                              AS "latest_order_date", -- Used in case different stores get same scores
                             SUM("weight")                                                  AS "score",
                             SUM("weight")
                             FILTER (WHERE EXTRACT(ISODOW FROM "created_at") NOT IN (6, 7)) AS "weekdays_score",
                             SUM("weight")
                             FILTER (WHERE EXTRACT(ISODOW FROM "created_at") IN (6, 7))     AS "weekends_score"
                      FROM order_weight
                      GROUP BY "contact_id", "store_id")

SELECT "contact_id",
       (ARRAY_AGG((CASE WHEN "score" IS NOT NULL THEN "store_id" ELSE NULL END)
        ORDER BY "contact_id", "score", "latest_order_date" DESC)
        FILTER (WHERE "score" IS NOT NULL))[1]          AS "favorite_store_id",
       (ARRAY_AGG((CASE WHEN "weekdays_score" IS NOT NULL THEN "store_id" ELSE NULL END)
        ORDER BY "contact_id", "weekdays_score", "latest_order_date" DESC)
        FILTER (WHERE "weekdays_score" IS NOT NULL))[1] AS "weekdays_favorite_store_id",
       (ARRAY_AGG((CASE WHEN "weekends_score" IS NOT NULL THEN "store_id" ELSE NULL END)
        ORDER BY "contact_id", "weekends_score", "latest_order_date" DESC)
        FILTER (WHERE "weekends_score" IS NOT NULL))[1] AS "weekends_favorite_store_id"
FROM store_scores
GROUP BY "contact_id";