Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

Sample Data

| Customer ID | Signup Date | Churn Date | Total Purchases | Total Spend |

|-------------|-------------|------------|-----------------|-------------|

| 1           | 2022-01-01  | 2023-01-01 | 5               | 5000        |

| 2           | 2022-01-05  | 2023-01-10 | 8               | 7000        |

| 3           | 2022-01-08  | 2023-01-15 | 3               | 4000        |

| 4           | 2022-01-01  | 2023-01-01 | 5               | 5000        |

| 5           | 2022-01-05  | 2023-01-10 | 8               | 7000        |

| 6           | 2022-01-08  | 2023-01-15 | 3               | 4000        |

| 7           | 2022-01-01  | 2023-01-01 | 5               | 5000        |

| 8           | 2022-01-05  | 2023-01-10 | 8               | 7000        |

| 9           | 2022-01-08  | 2023-01-15 | 3               | 4000        |

 

Task :

1. Calculate the tenure (months between Signup Date and Churn Date) for each customer.
2. Use IF function to categorize customers as "Churned" if Churn Date is not blank, otherwise "Active".
3. Calculate the average total spend per purchase using AVERAGEIF function.
4. Create a PivotChart to visualize the total purchases by customer.
5. Use slicers in the PivotChart to filter data by tenure and churn status.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

 

Steps to Perform:

1. Calculate Tenure (Months Between Signup Date and Churn Date) for Each Customer:
   • Use Excel's DATEDIF function to calculate the tenure in months between Signup Date and Churn Date.
   • Example: =DATEDIF(B2, C2, "m") assuming Signup Date is in column B and Churn Date is in column C.
2. Use IF Function to Categorize Customers as "Churned" or "Active":
   • Use the IF function to categorize customers based on whether Churn Date is blank.
   • Example: =IF(C2<>"", "Churned", "Active") assuming Churn Date is in column C.
3. Calculate Average Total Spend per Purchase Using AVERAGEIF Function:
   • Use the AVERAGEIF function to calculate the average total spend per purchase.
   • Example: =AVERAGEIF(D2:D4, ">0", E2:E4) assuming Total Purchases is in column D and Total Spend is in column E.
4. Create a PivotChart to Visualize Total Purchases by Customer:
   • Select the data range including Customer ID and Total Purchases columns.
   • Go to Insert > PivotTable > PivotChart.
   • Drag Customer ID to Axis (Categories) and Total Purchases to Values (Sum).
   • Choose the chart type suitable for visualizing total purchases.
5. Use Slicers in the PivotChart to Filter Data by Tenure and Churn Status:
   • After creating the PivotChart, go to Analyze > Insert Slicer.
   • Select fields like Tenure and Churn Status to add slicers for filtering.
   • Use slicers to dynamically filter and analyze data based on tenure (months since signup) and churn status (active or churned customers).

Detailed Steps:

• Step 1: Calculate Tenure
  • Enter =DATEDIF(B2, C2, "m") in a new column (e.g., D2) to calculate tenure between Signup Date (B2) and Churn Date (C2).
• Step 2: Use IF Function for Churn Status
  • Enter =IF(C2<>"", "Churned", "Active") assuming Churn Date is in column C.
• Step 3: Calculate Average Total Spend per Purchase
  • Enter =AVERAGEIF(D2:D4, ">0", E2:E4) assuming Total Purchases is in column D and Total Spend is in column E.
• Step 4: Create PivotChart
  • Select the data range including Customer ID and Total Purchases.
  • Go to Insert > PivotTable > PivotChart.
  • Design the PivotChart to show total purchases by customer ID.
• Step 5: Use Slicers
  • After creating the PivotChart, go to Analyze > Insert Slicer.
  • Choose fields like Tenure (from Step 1) and Churn Status (from Step 2) to add slicers.
  • Use slicers to filter and analyze data dynamically based on tenure and churn status.