Data Analytics 100, Video 30: Capstone, A Real Social Media Campaign Performance Report#

• Video thirty of the hundred-video real-world data analytics series, the real final video of this marketing and social media domain.
• Pulling every real dataset from this domain back together into one real consolidated marketing report.
• Let's get into it.

This capstone is for learners who have worked through the marketing and social media lessons in the series and want to see how separate analyses become one report. The business question is the one a marketing director asks at the end of a quarter: across everything we measured, what are the headline numbers, and where should we act first?

Rather than introducing new data, the notebook reloads seven raw files from earlier lessons: social posts (social_posts_raw.csv), telecom churn (telco_churn_raw.csv), a mobile game A/B test (ab_test_cookie_cats_raw.csv), online retail transactions (online_retail.csv), website sessions with a source lookup, industry email benchmarks and airline tweets. Each headline metric is recomputed from scratch rather than copied from earlier results.

In this lesson you will:

• rebuild one headline metric per dataset and gather them into a single summary table
• draw a 2x2 dashboard with plt.subplots and write a plain-text executive summary
• run cross-dataset quality and consistency checks before signing off

You should be comfortable with groupby, boolean means as percentages and f-strings; revisit the earlier lessons in this section if any dataset feels unfamiliar.

Part 1: Real Capstone, Real Fresh Numbers#

A capstone touches many datasets, so it helps to load every tool once at the top. The code imports pandas and NumPy for data work, matplotlib for the dashboard, re for regular expressions (text patterns used to extract hashtags) and Counter for counting. There is no output, which is expected for imports. If any import fails here, fix it before going further, because every later step depends on these libraries.

Part 2: Real Reload, Social Posts#

The capstone recomputes every number from the raw files, so the first dataset is reloaded from disk rather than reused from memory. The code reads social_posts_raw.csv and checks .shape. The output (944, 3) matches the 944 posts and three columns used in the hashtag lesson. Checking the shape straight after loading is the quickest way to notice if you have picked up a different or truncated version of a file.

Part 3: Real Fresh Hashtag Recount#

This step rebuilds the social listening metric: how many distinct hashtags appear. A lambda applies re.findall(r'#(\w+)', ...) to each lowercased tweet, returning a list of hashtags per post. A list comprehension flattens these into one list, and len(set(...)) counts unique tags. The output is 1514 distinct hashtags, matching the earlier lesson exactly. Lowercasing before matching is what keeps #Love and #love from counting as two tags.

Part 4: Real Reload, Customer Churn#

Churn, the share of customers who leave, is the core retention metric. The code reads the telecom file, compares Churn with 'Yes' to get True or False per customer, and takes .mean(), which gives the share of Trues, multiplied by 100. The output shows 486 customers with a 25.3% churn rate, so about one in four customers left. Showing the record count alongside the rate gives the reader a sense of how much data sits behind the percentage.

Part 5: Real Fresh Churn by Contract#

An overall churn rate is not actionable on its own; you need to know which customers are most at risk. The code groups by Contract and applies a lambda that computes the churn percentage within each group. The output shows Month-to-month customers churn at 42.2%, compared with 4.1% for One year and 2.7% for Two year contracts. That is a huge gap and the clearest retention lever in the whole domain: moving customers to longer contracts.

Part 6: Real Reload, A/B Test#

The A/B test compared two versions of a mobile game, with the first gate placed at level 30 or level 40. This step reloads the file and computes seven-day retention, the share of players still playing a week after installing, for each version. retention_7 is True or False, so its mean per version is a share. .to_dict() turns the result into a compact dictionary. The output shows 2,256 players, with gate_30 retaining 19.18% and gate_40 retaining 17.71%.

Part 7: Real Reload, Purchase Funnel#

The purchase funnel metric needs clean customer data first. The code reads the retail transactions, drops rows with no CustomerID using dropna(subset=['CustomerID']), and keeps only rows with a positive Quantity, which removes returns and cancellations. .nunique() then counts distinct customers. The output shows 4,339 unique customers. Note that this is a count of customers, not transaction rows, which matters when comparing record counts across datasets later.

Part 8: Real Fresh Repeat Purchase Rate#

Repeat purchase rate shows how many customers come back after their first order, a key measure of loyalty. The code converts InvoiceDate to dates, then collapses line items to one row per order with the earliest timestamp. It finds each customer's first order date, attaches it with merge, and counts customers with any order dated after their first. Dividing by all customers gives the output: 65.5% of customers made a repeat purchase. Using dates rather than invoice counts avoids treating two invoices at the same moment as a repeat.

Part 9: Real Reload, Web Traffic#

Web traffic attribution needs the session data joined to the source lookup, so each session has a source type. The code reads both files and joins them on Source_Key with .merge(). The output is 731, matching the number of sessions in the original traffic table, which confirms the inner join did not drop any rows. Comparing row counts before and after a join is a quick habit that catches missing or mismatched keys.

Part 10: Real Fresh Top Traffic Source#

This step recomputes which traffic source brings the most visitors. The code groups by Source_Type, counts Session_Id values and sorts from highest to lowest. The output shows Social leading with 135 sessions, then Organic (129) and Paid (125), with Email last at 113. These match the web traffic lesson exactly. Remember from that lesson that Social led on volume while Organic led on engagement, a nuance worth carrying into any recommendation.

Part 11: Real Reload, Email Benchmarks#

Email benchmarks give the report an external reference point. The code reloads the benchmark file and takes the median open rate across industries, rounded to two decimals. The output shows 45 industries with a median open rate of 43.75%. The median is used rather than the mean so that a few unusually high or low industries do not distort the headline. This is the figure a marketer can compare their own open rate against when no industry-specific benchmark is available.

Part 12: Real Reload, Airline Sentiment#

Brand sentiment closes the social side of the report. The code reloads the airline tweets, checks whether each airline_sentiment equals 'negative', and takes the mean of that boolean as a percentage. The output shows 341 tweets with a 38.7% negative rate. This matches the airline sentiment lesson and gives the report one simple measure of how customers feel about the brand in public.

Part 13: Real Cross-Dataset Summary Table#

A summary table brings every headline into one place, which is what a stakeholder actually reads. The code builds a DataFrame with three columns: dataset name, record count and a headline metric written as text with f-strings. :.1f formats the retention numbers to one decimal place. The output lists all seven datasets, from 944 social posts with 1514 distinct hashtags to 341 airline tweets with a 38.7% negative rate. Note the Purchase Funnel row counts customers (4,339), not transactions.

Part 14: Real Total Records Analyzed#

A total record count is a simple way to show the scale of the work behind the report. The code sums the records column of the summary table. The output is 9,142. Treat this figure carefully: it adds together different units, such as posts, customers, sessions, players and industries, so it describes the breadth of the analysis rather than a single population. That caveat belongs in the report next to the number.

Part 15: Real 2x2 Consolidated Dashboard#

A dashboard lets a reader take in several findings at a glance. plt.subplots(2, 2) creates a figure with four panels, and axes[row, col] picks each one. The four bar charts show churn by contract, seven-day retention by gate version, sessions by source and airline sentiment counts. For the last panel, the code indexes value_counts() with an explicit list so bars always appear in negative, neutral, positive order. The figure is saved to capstone_marketing_dashboard.png and closed. When you open it, look for the tall month-to-month churn bar.

Part 16: Saving the Real Capstone Metrics Table#

Saving the summary table means anyone can open it in a spreadsheet without running Python. The code writes the table to capstone_marketing_metrics.csv with index=False, so no extra index column is added, then reads it back and compares shapes. The output True confirms the file was written completely. This round-trip check is a good habit for every file you hand over.

Part 17: Real Written Executive Summary#

Numbers in a table still need a written narrative for busy readers. The code builds a multi-line f-string, using triple quotes so the text can span several lines, with one sentence per area: social listening, retention, experiments, purchasing, traffic, email and sentiment. {total_records:,} adds a thousands separator. There is no output because the text is only stored in summary_text, not displayed. Writing the summary from variables rather than typed numbers keeps it accurate if the data changes.

Part 18: Printing the Real Executive Summary#

Printing the summary lets you proofread it exactly as a reader will see it. print() displays the stored text with its line breaks. The output opens with 9,142 total records, then lists the key findings: 25.3% churn with month-to-month contracts most at risk, gate_30 beating gate_40 on retention, 65.5% of 4,339 customers repeating a purchase, Social leading traffic, a 43.75% median open rate and 38.7% negative airline tweets. Note that the text says analyzed and behavior; on the site you would spell these analysed and behaviour.

Part 19: Saving the Real Executive Summary#

A plain-text summary is easy to email, paste into a chat or attach to a ticket. The code opens a file in write mode with with open(..., 'w'), which closes the file automatically when the block ends, and writes the summary. It then imports os and uses os.path.exists() to confirm the file is on disk. The output True confirms it was saved. Using with is safer than calling open() and close() by hand, because the file is closed even if an error occurs.

Part 20: Real Cross-Dataset Correlation Check#

Despite the heading, this step does not calculate a correlation: it places three volume figures side by side. The code builds a small DataFrame with the number of distinct hashtags, web sessions and airline tweets. The output shows 1514 hashtags, 731 sessions and 341 tweets. These measure different things, so comparing them directly says little. A true correlation would need two measures recorded for the same units, for example sessions and conversions per day.

Part 21: Real Domain-Wide Data Quality Recap#

Data quality varies between sources, and a domain-wide check shows where to be cautious. The code applies .isna().sum().sum() to each dataset: the first .sum() counts missing values per column, and the second adds those up into one total. The output shows zero missing values in five datasets, but 1,538 in the airline tweets, mostly from optional fields such as coordinates and the empty gold-label columns. The retail file is not included here, even though it had missing CustomerID values earlier.

Part 22: Real Retention Gap in Absolute Terms#

Percentages can be described as an absolute gap or a relative one, and stakeholders often confuse the two. This step subtracts gate_40 retention from gate_30 retention. The output is 1.47, meaning gate_30 kept 1.47 percentage points more players after seven days. That is the absolute gap. Relative to gate_40's 17.71%, it is roughly an 8% improvement, which sounds larger. Always say which one you mean when you report an A/B test result.

Part 23: Real Highest-Churn Payment Method, Fresh#

Contract type was one churn driver; payment method is another worth checking. The code groups by PaymentMethod and computes each group's churn percentage with the same lambda as before. The output shows Electronic check customers churn at 41.7%, about double Mailed check (20.5%) and more than three times Credit card (automatic) at 12.1%. Automatic payment methods churn least, which suggests that nudging customers towards automatic payments could be a practical retention tactic.

Part 24: Real Top Complaint Reason, Fresh#

The capstone should also confirm the top complaint from the sentiment lesson. value_counts() counts each negativereason, and .idxmax() returns the label with the highest count. The output is 'Customer Service Issue', matching the earlier lesson. That makes customer service the single clearest action point on the social side of the report, alongside contract length on the retention side.

Part 25: Real Best Email Industry, Fresh#

A quick look-up of the best-performing email industry gives readers a benchmark ceiling. email_benchmarks['Open_Rate_Pct'].idxmax() returns the row label of the highest open rate, and .loc[row, 'Industry'] pulls the industry name from that row. The output is 'Religion', which had the highest open rate in the email lesson. Using .loc with both a row and a column label is a neat way to fetch a single value in one step.

Part 26: Real Sanity Check, All Reloads Non-Empty#

An empty dataset would silently break every metric built on it, so this check confirms each reload worked. The code puts the size of each dataset in a list and uses a generator expression inside all(), which returns True only if every count is above zero. The output True confirms all seven sources loaded with data. It is a cheap safeguard, especially when files are refreshed automatically.

Part 27: Real Sanity Check, Percentages in Range#

Every rate in the report should fall between 0 and 100, and a value outside that range would point to a calculation error. The code uses a chained comparison, 0 <= v <= 100, inside all() to check churn rate, repeat rate, median open rate and negative rate in one line. The output True confirms all four are valid percentages. Chained comparisons like this work on plain numbers, but not on whole pandas Series, where you need & instead.

Part 28: Real Sanity Check, Fresh Numbers Match Earlier Videos#

The capstone's credibility rests on its numbers matching the earlier lessons. This step compares the fresh churn rate with 25.3 and the fresh negative rate with 38.7. The output np.True_ is NumPy's version of True and confirms both match. Comparing rounded decimals with == works here because both sides were rounded the same way, but for unrounded values you would use np.isclose() to avoid tiny floating-point differences.

Part 29: Real Domain Wrap Table#

Before closing a capstone it helps to see every dataset side by side, ranked by size, so you know which conclusions rest on plenty of data and which rest on very little. The code picks the dataset and records columns from the capstone summary and sorts them with sort_values('records', ascending=False). The output runs from the Purchase Funnel at 4,339 records and the A/B Test at 2,256 down to Email Benchmarks at just 45. Treat findings from the smallest datasets with more caution: a pattern in 45 rows can easily be noise.

Part 30: Real Recap Print#

The final recap sentence is the one-line version of the whole domain. The f-string uses {total_records:,} for a thousands separator and pulls in stored metrics. The output reads: this domain analysed 9,142 records across seven datasets, from 1514 hashtags to 38.7% negative airline sentiment. A sentence like this works well as the opening line of an email to stakeholders, with the dashboard and summary file attached.

Wrap-Up: What You Learned#

• A real capstone earns trust by recomputing every headline number fresh from raw source files, never by copy-pasting cached results.
• A real 2x2 dashboard image communicates a whole domain's worth of findings faster than any single one of the ten videos that built it.
• Real written executive summaries, saved as a real plain text file, are what actually get read by a real busy stakeholder.
• Auditing real missing data across an entire real domain, not just one dataset, reveals genuinely different data-quality patterns.
• Confirming that freshly recomputed numbers exactly match earlier videos is a real trustworthy final check before calling any analysis complete.
• That wraps the real Marketing and Social Media Analytics domain. Ten real videos, seven real datasets, real code every step of the way.

Practice on your own

• Add an eighth row to capstone_summary for the email data showing the best open-rate industry (best_open_industry_fresh) and its rate, then re-save the CSV.
• Replace the airline sentiment panel in the 2x2 dashboard with churn by PaymentMethod, and save the figure under a new file name.
• Extend the missing data audit to include the raw retail table and report which of its columns account for the missing values.

This notebook closes the marketing and social media section of the series, so watch the video walkthrough to see how the report comes together, then carry these habits into the next section.