Project Title: Data Jeng-aha

Objectives:

To analyze subscription data (in basic spreadsheet format) for MavenFlix, focusing on understanding the distribution of paid, unpaid, canceled, and current subscriptions, and ensuring the numbers balance out.

Further, to discover patterns in the use of COUNTIF and COUNTIFS spreadsheet functions that can make their use more memorable.

Bonus challenge: Can you make a micro project out of anything, even just two spreadsheet functions? Count me in!

Project Overview:

In order to meet the objectives, this project involves analyzing subscription records for MavenFlix, a fictitious video streaming platform, covering the period from September 2022 to September 2023. The focus is on utilizing COUNTIF and COUNTIFS functions in Microsoft Excel and Google Sheets to confirm the integrity of the data when performing data analytics.

Key Metrics:

• Total number of subscriptions

• Number of paid and unpaid subscriptions

• Number of canceled subscriptions (paid and unpaid)

• Number of current subscriptions (paid and unpaid)

Analysis:

Set 1. Total Subscriptions

• All Subscriptions — 3069 =COUNTIF(A2:A,"<>")

Total: 3069

Set 2. Paid and Unpaid Subscriptions

• All Subscriptions - Paid — 2936 =COUNTIF(F:F, "Yes")

• All Subscriptions - Unpaid — 133 =COUNTIF(F:F, "No")

Total: 3069

Set 3. Canceled Subscriptions

• Canceled Subscriptions - Paid —1881 =COUNTIFS(F:F, "Yes", C:C, "<>")

• Canceled Subs - Unpaid — 123 =COUNTIFS(F:F, "No", C:C, "<>")

Subtotal: 2004

Set 4. Current Subscriptions

• Current Subs - Paid — 1055 =COUNTIFS(F:F, "Yes", C:C, "")

• Current Subs - Unpaid — 10 =COUNTIFS(F:F, "No", C:C, "")

Subtotal: 1065

Total: 3069

Visualization:

The following chart is a custom-made chart designed to show the balancing of numbers derived from the COUNTIF and COUNTIFS functions.

The COUNTIF-derived numbers equal 3069, the total number of subscriptions.

The COUNTIFS-derived numbers equal 3069, again, the total number of subscriptions.

The COUNTIF functions have fewer slats than COUNTIFS functions because COUNTIF formulas are limited to one set of range and criteria. COUNTIFS formulas can have numerous criteria, which can parse data in dramatically more detail.

The formulas' criteria clearly show the relationship between formulas—having just one criteria different from its obvious counterpart.

The vertical numbers are designed to resemble a familiar stacked bar chart formation, though in combined fashion here.

Conclusions:

The COUNTIF and COUNTIFS functions can be used to confirm the integrity of data, meaning all numbers are accurate and balance against totals.

The distribution of paid, unpaid, canceled, and current subscriptions balances out with the total number of subscriptions.

The makeup of the subscription activity can now be used as a benchmark for past and future 12-month subscription activity.

Pattern recognition in the use of COUNTIF and COUNTIFS spreadsheet functions make their use more memorable.

Recognition #1: Toggling function criteria. i.e. The only difference in each of the sets is one criteria.

Recognition #2: Detail. COUNTIFS formulas can help drill down further into data than COUNTIF formulas because more range/criteria can be added to COUNTIFS formulas.

Recognition #3: Data visualizations of COUNTIF and COUNTIFS functions clearly illustrate their nuances.

Bonus challenge result? You CAN make a micro project out of anything, even just two spreadsheet functions! This micro-sized project demonstrates proficiency in using these functions to derive meaningful insights.

APPENDIX A: COUNTIF/COUNTIFS INFORMATION

The COUNTIF Function

The COUNTIF function accepts one argument, as in COUNTIF(range, criterion)

Think of it this way: =COUNTIF(Where do you want to look?, What do you want to look for?) (src. support.microsoft)

Example: =COUNTIF(A2:A,"<>")

The COUNTIFS Function

The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Example: =COUNTIFS(F:F, "Yes", C:C, "<>")

Note:

Where =COUNTIFS(F:F, "Yes", C:C, "<>"), "<>" means the the corresponding value in column C is not blank; the canceled date is present in the cell.

Where =COUNTIFS(F:F, "Yes", C:C, ""), "" means the the corresponding value in column C is blank; a canceled date is not present in the cell.

APPENDIX B: SPREADSHEET INFORMATION

Column A | customer_id

Column B | created_date

Column C | canceled_date

Column D | subscription_cost

Column E | subscription_interval

Column F | was_subscription_paid