Sam, person in charge of a department store, wants to analyze the competitiveness of each brand in the store by customer inflow and outflow analysis. This can not only help the department store understand the brand competitiveness, but also help brand decision-makers grasp their brand inflows and outflows.
Sam classifies inflow causes into inflows from other brands, inflows from channels, and inflows from categories, and outflow causes into outflows from brands, outflows from channels, and outflows from categories. After specific classification, the competitiveness of each brand is more obvious.
Inflow: customers who have consumed within six months but not before six months.
Outflow: customers who have consumed before six months but not within six months.
For example, customer A has purchased Nokia's electronic products within six months, but purchased no Nokia's electronic products six months ago. For the Nokia's electronic product category, customer A is considered as an inflow. So which inflow type does customer A belong to?
Inflows from other brands: If customer A purchased electronic products from other brands in the department stores before six months, this customer belongs to this type from the perspective of Nokia electronic products.
Inflows from categories: If customer A only purchased products of other categories (namely purchased no electronic products) in the department store before six months, this customer belongs to this type because he purchased electronic products for personal needs.
Inflows from channels: If customer A has not purchased any products in the department store before six months, this customer belongs to this type as a new customer.
The same principle applies to the three outflow types. For details, see section 2.2 in this document.
Different brands have different categories of products. When products of each category are marketed, they need to be compared with competitors' products of the same category as independent entities.
For clear analysis, Sam analyzed inflows and outflows by category for each brand.
(1) After creating a dashboard, Sam found that the Acme brand had a negative net inflow in office supplies in the table. Then Sam clicked Acme. A message about Acme's inflow and outflow conditions on office supplies is displayed on the right, indicating inflows from brands were serious and special attention was required.
(2) Sam also filtered out the top 10 brands with net outflow, analyzed which brands' competitiveness declined, and hoped to uses them as a reference for later brand settle-in.
(3) Sam then filtered out the top 10 inflows from channels, analyzed which type of products from which brand have outstanding contributions to the introduction of new customers to the department store, and prepared to provide some benefits for these products.
The sample data used in this document is obtained from Dept Store Data.xlsx. (Since this sample data table only collect statistics until December 30, 2016, the inflow and outflow situation calculated in this document is based on the data generated until December 30, 2016.)
You can click Save as in the drop-down list box of Customer Inflow and Outflow Analysis to save it, and view the dashboard and the operation methods in dashboard-used data tables for learning.
(1) Create New Subject to add a subject named Customer Inflow and Outflow Analysis.
(2) Add Dept Store Data and select required fields from Dept Store Data, as shown in the following figure.
(3) Click Group Summary and drag required fields. Select Latest Time from the drop-down list of Order Date, as shown in the following figure.
This helps calculate the last time when customers purchased products of certain categories from certain brands. As shown in the above figure, Jade purchased office supplies from Rogers on 2019-01-01.
(4) Use 2022-12-30 as the calculation time, and filter out the data of products last purchased (6 months ago within 12 months), as shown in the following figure.
That is to say, these customers purchased six months ago, but did not purchase within the past six months. For a certain brand that sells products of a certain category, these customers belong to outflows.
For example, Darcie is considered as Samsung's customer outflow in terms of electronic equipments.
(5) Click Save and Update and rename the table as Customer Outflow Table.
In the previous, customer outflows are filtered out. For a certain brand that sells products of a certain category, these customers purchased products six months ago but no products within the past six months. What is the reason for this?
To figure it out, their behavior within the past six months can be analyzed.
For example, Darcie purchased Samsung's electronic equipments in the first six months.
If Darcie has no purchase record in the department store within the past six months, Darcie is considered as an outflow from the purchase channel.
If Darcie has a purchase record (but purchased no electronic equipment) in department stores within the past six months, Darcie doesn't need equipments of this category recently and is considered as an outflow from this category.
If Darcie purchased electronic equipments from other brands at the department store within the past six months, Darcie is considered as an outflow from brands for Samsung.
(1) Add Dept Store Data, rename it to Customer Purchase Details Within Six Months, and select required fields, as shown in the following figure.
(2) Use 2022-12-30 as the calculation time and obtain the purchase records within the past six months, as shown in the following figure.
(3) Due to the possibility that customers may repeatedly purchase products of a certain category from a certain brand, perform group summary to deduplicate data records, as shown in the following figure.
(4) Click Save and Update for Customer Purchase Details Within Six Months.
All customers' behaviors in the past six months are listed in section "Filtering Out Customers with Purchase Records Within Six Months." However, customer outflows' behaviors in the past six months are required indeed.
(1) Return to Customer Outflow Table and merge it with Customer Purchase Details Within Six Months from left to right. Set Mode to Left Join and Merge Basis to only Customer Name, as shown in the following figure.
The above figure shows the post-merge effect. For example, Effie has purchased electronic equipments and office supplies of other brands in the past six months.
(2) Click Formula Column and add a tagged column to compare behaviors before and after six months and tag records as follows:
If the category of products purchased before and after six months is consistent, tag these records using 2 (indicating outflows from brands).
If the product category is empty after six months, customers have no purchase record in the department store within six months, tag these records using 0 (indicating outflows from channels).
For records except for the above two types, tag them using 1 (outflows from categories).
According to the purchase records shown in the above figure, Montgomery who purchased office supplies for five times in the past six months belongs to outflows from brands for Enermax.
(3) Click Group Summary, select required fields, and select Group Records with Same Value from the drop-down list of each field, as shown in the following figure.
(4) After deduplicating outflow cause tags, perform post-grouping summary, as shown in the following figure.
For deduplicated values 0, 1, and 2, the values obtained after post-grouping summary can only be 0, 1, 2, or 3.
(5) Based on post-summary tags, obtain customer outflow causes. Click Formula Column and add an outflow cause column, as shown in the following figure.
If the value of Outflow Cause Tag is 2 or 3, 1, and 0, the outflow cause is outflows from brands, categories, and channels, respectively.
(6) Perform group summary again to calculate the outflow quantity for each channel, as shown in the following figure.
(7) Click Save and Update.
In the above section, outflow causes have been identified successfully for each brand and category. The inflow cause table can be formulated in the same principle.
(1) Filter out the list of customers who have transactions in the past six months but not before six months (excluding data before December). These customers belong to customer inflows.
(2) Analyze behaviors of customer inflows before six months and determine what type of customer outflows they belong to.
Create a customer inflow table in the same principle. Save the table as a subject according to section 1.4. You can view the specific operation steps of the customer inflow table in the subject, as shown in the following figure.
Create Customer Outflow Table and rename it to Inflow and Outflow Summary Table. Select all fields of Inflow and Outflow Summary Table and click Join to merge the table with Customer Inflow Table from left to right, as shown in the following figure.
(3) Click Save and Update.
Use Inflow and Outflow Summary Table to create a component. As shown in the following figure, the grouping table is made to display outflow quantity, inflow quantity, and net inflow quantity for different categories from each brand.
Net inflow quantity= Inflow quantity - Outflow quantity
Add components according to analysis requirements.
For details, see section "Concept."
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy