John Bellanger | Advanced Analytics Consultant

Meta Ads Campaign Performance

View Dashboard View Presentation SQL Code

1. Context: Business Problem & Scope

Core Business Question:

"Why did escalating advertising expenditures on Meta Ads fail to generate proportional gross revenue growth, and which specific acquisition segments, regional markets, and attribution distortions are destroying capital?"

Making media allocation decisions based solely on platform self-reported metrics exposes leadership to unknowingly subsidizing unprofitable customer acquisition. To eliminate guesswork, this audit joins raw operational tables from Meta Ads Manager with verified first-party transactions in Google Analytics 4 using BigQuery. The objective is to identify margin leaks, isolate the structural causes of negative ROAS in December, and reallocate capital toward provably profitable segments.

2. Methodology & Decision-Making Visual Standards (Protocols)

Data Cleaning & Pipeline Protocol: Raw ad-click and transaction logs routinely contain duplicate tracking hits, mismatched currency conversions, and attribution overlaps. I duplicated raw tables in BigQuery to maintain immutable data lineage, standardized timestamp and date formats across logs, cleaned strings using LOWER() and TRIM() for naming consistency, converted expense columns to unified numeric data types, and joined tables into a normalized flat table for reliable cross-platform validation.

Unified Visual Standard (Gray / Orange Rule):

■ Gray (#64748B): Depicts baseline performance, nominal distribution volumes, and operationally compliant benchmarks.
■ Orange (#F97316 / #FADB91): Exclusively highlights the critical focal point requiring executive intervention (operational bottlenecks, margin leaks, and high-priority recovery levers). Whenever an element appears in orange, leadership action is mandated.

Standard Distribution Protocol: Every statistical metric across descriptive and diagnostic sections is consistently evaluated across four dimensions: Percentages (relative budget consumed vs revenue generated), Minimum & Maximum (absolute efficiency boundaries vs worst-case deficits), and Mean vs. Median (evaluating arithmetic averages against median baselines to identify distribution skewness and volatility spikes via the +15% rule).

3. Phase 1: Macro Performance Audit (ROAS vs. CPA Trajectory)

Explanatory Cap & Business Problem: Before auditing individual ad sets, executive leadership must establish the aggregate trajectory of channel efficiency to identify the precise inflection point where unit economics broke down.

The Why & The How: We hypothesized that customer acquisition costs outpaced conversion values toward year-end, driving aggregate ROAS below the break-even threshold. ROAS evaluates gross capital productivity (Revenue / Spend), while CPA measures direct customer acquisition expense (Spend / Orders).

Applied Calculation: ROAS = Attributed Revenue / Total Ad Spend | CPA = Total Ad Spend / Verified Conversions

Distributions & Empirical Findings: ROAS hit a floor in December (falling below the critical 1.0 break-even line) versus a peak efficiency of 7.19 during properly timed seasonal promotions. The aggregate Q4 average was heavily distorted by pre-holiday weeks, masking rapid late-season margin erosion.

Conditional Decision Rules (IF / ELSE):

IF ROAS < 1.0 (Break-Even Threshold): The channel burns cash on marginal sales → Action: Immediate scaling freeze and auction bid audit. (December period isolated in Orange on chart)

ELSE: The channel generates positive gross contribution margin → Maintain or scale budgets predictably. (Compliant months rendered in Gray)

ROAS vs CPA Trajectory

The analysis confirmed the stakeholder's concerns: an overall low ROAS for Meta Ads traffic, and a conversion rate for this same channel that is the lowest of all acquisition channels. A particularly negative ROAS was observed in December, signaling a major underperformance.

→ Operational Transition: Having isolated December as the primary window of value destruction, we next drill down geographically to determine whether this deficit was systemic across all North American regions or concentrated in specific metropolitan areas.

4. Phase 2: Geographic Diagnostic (ROAS by City - Toronto Anomaly)

Explanatory Cap & Business Problem: National advertising campaigns often mask severe regional variations where high-performing markets silently subsidize saturated or non-converting urban centers.

The Why & The How: We hypothesized that an individual metropolitan market consumed disproportionate impression share while delivering near-zero conversion velocity. Measuring regional ROAS establishes location-specific bid modifiers and stops budget dilution across non-receptive audiences.

Applied Calculation: Regional ROAS = Revenue within Metro Area / Ad Spend consumed in Metro Area

Distributions & Empirical Findings: Toronto accounted for substantial ad spend, but delivered an abnormally low conversion rate compared to other Canadian metro areas. While Canadian cities collectively maintained a sustainable average ROAS, Toronto represented a massive negative outlier that skewed regional profitability.

Conditional Decision Rules (IF / ELSE):

IF Regional ROAS < 50% of National Baseline: Market suffers from audience saturation or logistics friction → Action: Apply immediate negative geographic exclusions to Toronto across broad prospecting ad sets. (Isolated in Orange on chart)

ELSE: Metropolitan market absorbs customer acquisition costs → Maintain standard geographic delivery. (Compliant cities in Gray)

ROAS by City (Toronto Anomaly)

I analyzed performance by country and city. I observed that the city of Toronto in Canada was particularly problematic in terms of profitability.

→ Operational Transition: Excluding underperforming geographies plugs an immediate margin leak, but it does not account for the entire deficit. We now audit the campaign management structure to evaluate individual promotional messages and seasonal flighting.

5. Phase 3: Campaign Unit Economics (Campaign Profitability Comparison)

Explanatory Cap & Business Problem: Even within a technically sound account, strategic calendar mismatches and poorly timed creative messaging can turn peak media investments into dead losses.

The Why & The How: We hypothesized that specific promotional campaigns were deployed in direct contradiction with customer buying cycles. Campaign-level ROAS isolates the commercial return of individual messaging variants, target audiences, and creative concepts.

Applied Calculation: Campaign ROAS = Revenue per Campaign / Budget consumed per Campaign

Distributions & Empirical Findings (The "Aha Moment"): Two campaigns drove the vast majority of account losses: "Valentines_Promo" and "Post-Holiday-Sale". Date-stamped performance analysis revealed that the "Post-Holiday-Sale"—an offer structurally designed for post-Christmas clearance—was launched in early December. Running clearance messaging before the holidays meant paying premium Q4 ad auction rates for an offer consumers were not looking for yet, burning substantial budget at the worst possible time.

Conditional Decision Rules (IF / ELSE):

IF Campaign ROAS < Break-Even Threshold: Campaign fails to recover media spend → Action: Immediately pause out-of-sequence creative assets and overhaul the marketing launch calendar. (Loss-making campaigns in Orange)

ELSE: Campaign demonstrates proven product-market fit → Gradually scale daily budgets. (Profitable campaigns in Gray)

Campaign Profitability Comparison (ROAS)

I reviewed the campaign performance. I noticed that two campaigns, "Valentines_Promo" and "Post-Holiday-Sale," were showing very low profitability.

→ Operational Transition: Before finalizing operational recommendations, an empirical integrity check is mandatory: why didn't the internal team detect this deficit earlier? We must compare platform self-reported metrics against verified first-party transactions.

6. Phase 4: Attribution Integrity & Tracking Divergence (Meta Ads vs. GA4)

Explanatory Cap & Business Problem: Ad network view-through attribution models frequently claim credit for direct, branded search, and organic transactions, creating an illusion of profitability that misleads decision-makers.

The Why & The How: We hypothesized that Meta Ads Manager significantly over-reported attributed conversions relative to transactions verified on-site by Google Analytics 4. The attribution gap quantifies platform self-reporting inflation:

Applied Calculation: Attribution Gap (%) = [(Meta Reported Conversions - GA4 Verified Conversions) / GA4 Verified Conversions] × 100

Distributions & Empirical Findings: Meta Ads claimed 51% more conversions than GA4 recorded via first-party tracking (152 self-reported conversions versus 101 verified conversions on the Post-Holiday-Sale campaign). Leadership assumed the campaign was breaking even based on the Ads Manager dashboard, while the company's bank balance reflected an ongoing net cash loss.

Conditional Decision Rules (IF / ELSE):

IF Attribution Gap > 20%: Pixel tracking is distorted by non-incremental view-through claims → Action: Designate GA4 / BigQuery server-side verified orders as the sole authoritative source of truth for media allocation. (Inflation gap isolated in Orange)

ELSE: Reporting divergence is acceptable → In-platform bidding optimization permitted. (Verified GA4 volume in Gray)

Attribution Gap: Meta vs GA4 Conversions

I quantified Meta's contribution to the total acquisition and measured the attribution gap. I found that Meta's attribution model overestimated its conversions, attributing 51% more to itself than GA4.

7. Chronological Executive Action Plan & Financial Impact Roadmap

1. Immediate Actions (Within 48 Hours)

Stop Margin Bleeding: Immediately pause misaligned promotional campaigns ("Post-Holiday-Sale" launched prematurely) and loss-making creative assets.
Geographic Safeguards: Implement negative audience exclusions for Toronto on broad acquisition ad sets.

2. Tactical Improvements (Within 15 Days)

Dual-Validation Launch Protocol: Establish a required dual-signoff workflow between the Marketing and Analytics teams before scheduling seasonal promotions.
Optimization Signal Shift: Reconfigure bid optimization models to rely on first-party server-side verified conversions (GA4) rather than inflated platform pixels, eliminating the +51% attribution distortion.

3. Strategic Initiatives (30 to 60 Days)

Seasonal Flighting Overhaul: Reschedule the "Post-Holiday-Sale" for its true conversion window (immediately following Christmas, late December through mid-January).
Quantified Financial Impact (Predictive Model): Based on the benchmark winter sales campaign (ROAS 7.19), launching the Post-Holiday-Sale during its proper window is projected to generate +$3,240 in incremental revenue and +56 net conversions for the exact same advertising budget.

8. Demonstrated Technical Skills

Databases & SQL Logic: Joined Meta Ads and GA4 to create a flat table, ensuring data standardization.
Statistical & Predictive Analysis: Scenario modeling to provide a quantifiable forecast ($3,240 additional revenue).
Data visualization & Executive Reporting: Designed structured business intelligence frameworks to guide executive decision-making.

Google Ads: Prediction on Ads

View Dashboard View Presentation SQL Cleaning R Prediction

1. Context: Business Problem & Scope

Core Business Question:

"Which acquisition channels and catalog categories offer proven marginal profitability, and how should media budgets be reallocated using predictive linear models to maximize net returns?"

Allocating advertising capital based on aggregated historical revenue frequently masks severe category-level deficits where profitable product lines cross-subsidize loss-making inventories. To eliminate speculative media planning, this study applies econometric predictive modeling using R and BigQuery SQL. The scope covers complete session logs and transaction events, evaluating Google/CPC, TikTok, and catalog segmentation across Electronics and Home categories.

2. Methodology & Decision-Making Visual Standards (Protocols)

Data Cleaning & Pipeline Protocol: Raw operational tables contained inconsistent UTM naming, text-encoded currency strings, and missing transaction records. Working across BigQuery and R, I standardized timestamp formats, cleaned non-numeric symbols using REGEXP_REPLACE, handled missing dates with SAFE.PARSE_DATE, and unified fragmented traffic sources (such as harmonizing "google/cpc" and "Google/CPC"). Finally, I constructed a master flat table that retained all user sessions—including non-converting visits—to ensure mathematically accurate conversion rate and elasticity modeling.

Unified Visual Standard (Gray / Orange Rule):

■ Gray (#64748B): Depicts baseline metrics, nominal volume distributions, and proven commercial engines (such as the Electronics portfolio).
■ Orange (#F97316 / #FADB91): Exclusively isolates critical points of executive intervention (capital drains, un-correlated marketing channels, and loss-making catalog segments). Elements highlighted in orange mandate immediate budget reallocation.

Predictive & Distribution Protocol: Metrics are systematically evaluated through statistical spread (percentages, minimum/maximum boundaries, mean vs median) and validated through econometric specifications using ordinary least squares linear regression: Revenue = β₀ + β₁(Ad Spend). Model fit is evaluated using R² and p-value significance thresholds (α = 0.05).

3. Phase 1: Macro Channel Audit (Revenue by Acquisition Channel)

Explanatory Cap & Business Problem: Before committing media capital, leadership must separate primary revenue drivers from secondary awareness channels that fail to produce commercial volume.

The Why & The How: We hypothesized that Google/CPC generates the highest concentration of high-intent purchase demand relative to alternative discovery platforms. We quantified gross revenue attribution across all active acquisition levers.

Applied Calculation: Channel Contribution Share (%) = [Revenue per Acquisition Channel / Total Gross Revenue] × 100

Distributions & Empirical Findings: Google/CPC represents the dominant commercial engine of the business, generating top-tier transaction volume and proving that paid search acts as the primary conversion closer in the customer journey.

Conditional Decision Rules (IF / ELSE):

IF Channel Revenue Share > 35% AND High Conversion Velocity: Validated frontline performance → Maintain core budget prioritization. (Rendered in Gray)

ELSE: Speculative acquisition channel → Cap budget allocation and mandate strict ROI verification. (Isolated in Orange)

Revenue by Acquisition Channel

The Google/CPC channel is one of the main revenue generators, confirming the effectiveness of our paid campaigns on this lever.

→ Operational Transition: Having confirmed Google/CPC as the primary revenue channel, we must look inside this lever to inspect product catalog concentration and determine which inventory categories drive this volume.

4. Phase 2: Category Concentration Audit (Revenue by Product Category)

Explanatory Cap & Business Problem: Allocating ad spend uniformly across catalog lines risks over-subsidizing low-margin products that fail to generate adequate basket sizes.

The Why & The How: We hypothesized that revenue is highly concentrated in high-ticket categories, creating an asymmetrical distribution that should guide product-level ad bidding.

Applied Calculation: Category Revenue Share (%) = [Revenue from Product Category / Total Google/CPC Revenue] × 100

Distributions & Empirical Findings: The "Electronics" category overwhelmingly dominates gross revenue, far outpacing Home, Fashion, and Accessories. Utilizing reorder() in R's ggplot2 exposed a steep revenue hierarchy, showing that consumer purchase intent is sharply concentrated around technical electronics.

Conditional Decision Rules (IF / ELSE):

IF Category Revenue Share > 50%: Core commercial anchor → Prioritize inventory visibility and dedicated campaign bidding. (Rendered in Gray)

ELSE: Secondary category → Require category-specific margin justification before scaling media bids. (Isolated in Orange)

Revenue by Category (Google CPC)

The "Electronics" category overwhelmingly dominates revenue, far ahead of all others. I used the reorder() function in ggplot2 to sort the bars and make this hierarchy immediately visible.

→ Operational Transition: Descriptive observations identify historical winners, but business leadership requires forward-looking financial forecasting. We now deploy linear regression models in R to predict the exact revenue outcomes of budget reallocations.

5. Phase 3: Predictive Econometric Modeling (Linear Regression via R)

Explanatory Cap & Business Problem: How will top-line revenue respond if we scale or cut media spending across specific product lines and channels? Using the lm() package in R, we establish mathematical response functions for each investment decision.

A. Growth Vector: Double Google/CPC Budget on Electronics

Hypothesis & Empirical Fit: Strong consumer purchase intent and superior average order value create an elastic revenue curve with high marginal return.

Model Specification: Revenue_Electronics = β₀ + 2.11 × (Ad Spend) | R² > 0.85, p < 0.001

Decision Rule (IF / ELSE):

IF Marginal ROAS > 2.0: Aggressive capital expansion validated → Action: Double Google/CPC budget from $10,472 to $20,944. Projected revenue increases to $46,976 (+$22,000 net incremental gain).

Electronics Prediction: Ad Spend vs Revenue (lm model)

Double the Google advertising budget/CPC for the Electronics category, from 10,472 to 20,944.

B. Margin Protection: Freeze Advertising on "Home" Category

Hypothesis & Empirical Fit: Paid acquisition for Home products operates at a negative marginal return, eroding company gross margins.

Model Specification: Revenue_Home = β₀ + 0.85 × (Ad Spend) | Marginal Return = $0.85 per $1.00 spent

Decision Rule (IF / ELSE):

IF Marginal Return < $1.00: Every ad dollar generates an immediate cash loss → Action: Cease paid media investments on Home category immediately. Mandate a full merchandising and pricing audit before reinvesting.

Home Prediction: Ad Spend vs Revenue (lm model)

Do NOT invest further in the "Home" category at this time. Launch an advertising strategy audit.

C. Capital Safeguard: Terminate Direct-Conversion TikTok Campaigns

Hypothesis & Statistical Test: Scatter plot distribution and correlation testing reveal no statistically meaningful relationship between TikTok click volume and store revenue.

Statistical Test: Pearson Correlation r ≈ 0.08, p > 0.15 (Statistically Independent)

Decision Rule (IF / ELSE):

IF p > 0.05 (No Statistical Correlation): Clicks do not translate into transactional value → Action: Terminate direct-conversion TikTok ad budgets immediately. Reallocate freed capital directly into the validated Google/CPC Electronics engine.

TikTok Scatter: Clicks vs Revenue (No Correlation)

Pause TikTok campaigns targeting direct revenue.

6. Chronological Executive Action Plan & Financial Roadmap

1. Immediate Actions (Within 48 Hours)

Eliminate Capital Drains: Immediately pause all direct-revenue TikTok campaigns and freeze paid media allocations for the "Home" category ($0.85 return per $1 spent).
Consolidate Active Budgets: Redirect unspent media reserve into core Google/CPC campaigns.

2. Tactical Improvements (Within 15 Days)

Scale Proven Winner: Implement a phased budget increase on Google/CPC Electronics from $10,472 toward $20,944, monitoring marginal return stability.
Catalog & Pricing Audit: Investigate basket economics and shipping thresholds for Home products to establish sustainable gross margins.

3. Strategic Initiatives (30 to 60 Days)

Continuous Econometric Pacing: Deploy automated R scripts to re-estimate regression slopes weekly, preventing diminishing returns as spend increases.
Quantified Financial Impact: Reallocating capital based on model specifications projects +$22,000 in net incremental revenue on Electronics (reaching $46,976 total at 2.11 ROAS) while preventing thousands in ongoing operational losses across Home and TikTok.

7. Demonstrated Technical Skills

Econometric Modeling & R Programming: Predictive regression modeling using lm(), hypothesis validation, and advanced visualizations via ggplot2.
Data Engineering & BigQuery SQL: Complex join logic, string normalization via REGEXP_REPLACE, and data type coercion.
Decision Intelligence: Translating statistical coefficients into clear, actionable executive P&L decision frameworks.

Apple: Cash Management Analysis

View Dashboard View Presentation

1. Context: Business Problem & Scope

Core Business Question:

"Does Apple's liquidity structure and operating cycle provide sufficient financial flexibility to fund aggressive strategic initiatives—such as massive internal AI infrastructure or major acquisitions—without compromising short-term operational solvency?"

Evaluating corporate cash management requires looking beyond top-line revenue to examine working capital mechanics, cash conversion cycles, and balance sheet resilience. This corporate finance audit examines Apple Inc.'s FY2025 financial disclosures (covering $1,004,592M in Net Sales and $247,948M in Net Income) to assess balance sheet solvency, working capital dynamics, and capital allocation pathways.

2. Methodology & Decision-Making Visual Standards (Protocols)

Financial Audit Protocol: Financial statements were audited across liquidity ratios, working capital efficiency, and debt maturity schedules. Current assets ($281,518M) and current liabilities ($165,631M) were isolated to calculate structural liquidity, while operational cash flow was reconciled against accounts receivable, inventory turnover, and accounts payable to determine working capital efficiency.

Unified Visual Standard (Gray / Orange Rule):

■ Gray (#64748B): Depicts standard balance sheet distributions, solvent baseline liquidity, and compliant operating benchmarks.
■ Orange (#F97316 / #FADB91): Exclusively highlights critical executive decision thresholds (working capital leverage, debt-service coverage boundaries, and high-stakes capital allocation pivots). Elements in orange signify executive strategic interventions.

Liquidity & Distribution Protocol: Financial ratios are benchmarked against industry solvency standards: Current Ratio (target > 1.2), Working Capital Requirement (WCR), and Net Working Capital (NWC). Ratios are evaluated to determine whether capital reserves reflect true discretionary cash or encumbered operational liquidity.

3. Phase 1: Revenue Stream Diversification & Product Concentration

Explanatory Cap & Business Problem: How dependent is Apple's operational cash engine on its flagship hardware, and does product diversification adequately insulate the business from market shocks?

The Why & The How: We hypothesized that hardware revenue—specifically iPhone sales—remains the primary liquidity generator funding corporate R&D and capital return programs.

Applied Calculation: Revenue Stream Share (%) = [Net Sales per Product Category / Total Net Sales] × 100

Distributions & Empirical Findings: iPhone accounts for 48.5% of total sales ($1,004,592M aggregate top-line), remaining the dominant cash cow. However, Services has expanded into a high-margin recurring anchor, creating an ecosystem barrier that mitigates hardware upgrade cyclicality.

Conditional Decision Rules (IF / ELSE):

IF Flagship Product Share > 45%: Significant portfolio concentration risk → Action: Reinvest hardware cash flow into recurring services and ecosystem lock-in. (Hardware isolated in Orange)

ELSE: Balanced product distribution → Standard operational reinvestment. (Rendered in Gray)

Revenue Streams Distribution

iPhone represents the primary revenue driver, contributing 48.5% of Apple's total sales in FY2025.

→ Operational Transition: Having audited top-line revenue streams, we examine balance sheet mechanics to evaluate how efficiently operating cash is captured and managed through working capital.

4. Phase 2: Operating Cycle & Negative Working Capital Engine

Explanatory Cap & Business Problem: Does Apple require external borrowing to fund daily operations, or does its supply-chain power create an internal self-funding mechanism?

The Why & The How: We evaluated the Working Capital Requirement (WCR) and Current Ratio to gauge short-term liquidity safety margins.

Applied Calculations: Current Ratio = Current Assets / Current Liabilities | WCR = (Receivables + Inventories) - Payables

Distributions & Empirical Findings: Apple operates with a deeply negative WCR of -$119,739M. The company collects cash from consumers virtually immediately while negotiating extended payment terms with global suppliers, effectively turning suppliers into an interest-free source of capital. The Current Ratio stands at a fortress-level 1.8 ($281,518M current assets vs $165,631M current liabilities), with assets growing (+3.0%) while short-term debt drops (-6.1%).

Conditional Decision Rules (IF / ELSE):

IF WCR < 0 AND Current Ratio > 1.5: Structural liquidity dominance → Action: Full strategic flexibility confirmed. Corporate management can fund large-scale growth or buyback programs without external financing constraints. (Favorable liquidity balance highlighted)

ELSE: Liquidity friction present → Mandate working capital optimization and debt repayment reserves.

Working Capital Profile

A negative WCR (-$119,739M) acts as a source of cash that funds growth, reflecting a highly efficient operating cycle.

→ Operational Transition: With fortress liquidity and self-funding operating cash confirmed, leadership must decide how to deploy this massive capital surplus. We evaluate three strategic pathways.

5. Phase 3: Strategic Capital Allocation Scenarios

Explanatory Cap & Business Problem: How should Apple deploy its surplus liquidity to defend its competitive moat while maximizing return on invested capital (ROIC)?

Scenario A: Massive Internal AI Infrastructure & Silicon R&D

Apple possesses unencumbered balance sheet capacity to fund multi-billion-dollar custom neural silicon fabrication and on-device model architectures without issuing commercial paper or altering dividend policies.

Scenario B: Strategic Ecosystem AI Partnerships

Partnering with frontier model providers (e.g., Gemini) enables rapid product integration while preserving capital liquidity for opportunistic large-scale acquisitions.

Scenario C: Accelerated Capital Return to Shareholders

With negative WCR continuing to generate organic operating cash, Apple can sustain aggressive share repurchases and dividend increases without reducing treasury safety margins.

6. Chronological Executive Action Plan & Financial Roadmap

1. Immediate Actions (Within 48 Hours)

Treasury Rebalancing: Optimize yield on short-term marketable securities ($281,518M current asset pool) against evolving interest-rate benchmarks.
Supplier Terms Preservation: Maintain commercial agreements sustaining negative WCR (-$119,739M) across tier-1 manufacturing partners.

2. Tactical Improvements (Within 15 Days)

R&D Capital Ring-Fencing: Allocate dedicated internal R&D tranches for AI and custom silicon development without compromising the 1.8 Current Ratio liquidity buffer.
Services Margin Expansion: Accelerate high-margin software monetization to reduce reliance on the hardware upgrade cycle.

3. Strategic Initiatives (30 to 60 Days)

Strategic Deployment: Execute hybrid capital allocation: accelerate internal AI capability development while maintaining aggressive annual share buyback targets.
Quantified Solvency Assurance: Confirmed zero insolvency risk; Apple maintains permanent strategic autonomy and complete operational resilience under all modeled market scenarios.

7. Demonstrated Technical Skills

Corporate Finance & Balance Sheet Auditing: Working Capital Requirement (WCR), Net Working Capital (NWC), Current Ratio, and cash conversion cycle modeling.
Financial Statement Modeling: Cross-statement reconciliation across 10-K filings, cash flow dynamics, and segment concentration analysis.
Strategic Decision Architecture: Formulating executive capital allocation strategies based on empirical solvency and risk thresholds.

Back Pain Sentiment Analysis

View Presentation R Analysis Code

1. Context: Business Problem & Scope

Core Business Question:

"How can unstructured consumer discourse from public healthcare forums be scientifically mined using Natural Language Processing (NLP) to engineer an empathetic advertising strategy that provably maximizes click-through rates and customer retention?"

Digital healthcare advertising frequently fails by relying on generic clinical jargon or unverified assumptions about consumer motivation. To replace guesswork with empirical linguistic evidence, this study scrapes and analyzes thousands of spontaneous conversations from chronic back pain communities on Reddit via Apify, deploying complete text processing, sentiment scoring, inferential hypothesis testing, and unsupervised machine learning in R.

2. Methodology & Decision-Making Visual Standards (Protocols)

Text Engineering & NLP Protocol: Raw conversational text contains conversational noise, emojis, and irregular punctuation. The pipeline in R implemented string normalization with tolower(), regex punctuation and digit stripping via gsub(), and stopword elimination using removeWords(stopwords()). Emotional polarity was quantified using the sentimentr engine and Plutchik's 8-dimensional emotion classifier via emotion_by().

Unified Visual Standard (Gray / Orange Rule):

■ Gray (#64748B): Depicts baseline conversational distributions, neutral competitor sentiment, and control group benchmarks.
■ Orange (#F97316 / #FADB91): Exclusively isolates primary emotional catalysts and statistical conversion levers (dominant fear/sadness clusters, high-yield bigrams like "relief", and statistically superior ad CTRs). Elements in orange signify executive messaging priorities.

Inferential & Machine Learning Protocol: Strategic messaging variants are validated through two-sample Student's t-tests (evaluating CTR differentials at α = 0.01), linear regression models assessing content engagement elasticity, and K-Means clustering partitioned across customer Monetary and Frequency dimensions.

3. Phase 1: Emotional Spectrum Audit (Radar Analysis)

Explanatory Cap & Business Problem: What core emotions dominate consumer discussions regarding back pain, and should marketing creative focus on technical anatomy or psychological empathy?

The Why & The How: We hypothesized that chronic back pain sufferers experience pain primarily as an emotional and lifestyle burden rather than a purely physical inconvenience.

Applied Metric: Plutchik Emotion Distribution (%) = [Count of Emotional Tokens per Category / Total Classified Emotional Hits] × 100

Distributions & Empirical Findings: Negative emotions heavily dominate the audience spectrum, with Sadness and Fear expressing the highest density. Chronic pain sufferers articulate feelings of helplessness, isolation, and anxiety over losing mobility, confirming that advertising messaging must lead with deep human empathy.

Conditional Decision Rules (IF / ELSE):

IF Sadness + Fear > 50% of Emotional Volume: Emotional suffering outpaces physical clinical concern → Action: Ground campaign creative in empathetic validation rather than sterile clinical features. (Highlighted in Orange)

ELSE: Rational product comparison dominates → Deploy technical anatomical messaging.

Detected Emotional Spectrum (Radar)

Sadness and fear are the most frequently expressed emotions by the audience.

→ Operational Transition: Having confirmed that negative emotions dominate consumer sentiment, we test whether users direct hostility toward existing treatments and medical competitors.

4. Phase 2: Competitor Polarity & Lexical Association (Bigram Mapping)

Explanatory Cap & Business Problem: Should the ad strategy attack pharmaceutical drugs and doctors to position our product as a disruptive alternative?

The Why & The How: We hypothesized that sufferers harbor resentment toward medical practitioners and pain medications, presenting an opportunity for aggressive comparative advertising.

Distributions & Empirical Findings:

Competitor Sentiment: Overwhelmingly Neutral. Users do not resent doctors or medications; they simply find them insufficient over the long term. An aggressive attack campaign would alienate customers.
Bigram Co-Occurrence Mapping: High-frequency word pairs center on aspirational freedom: "pain relief", "walk normally", "sleep through night", and "finally working".

Conditional Decision Rules (IF / ELSE):

IF Competitor Sentiment is Neutral: Attacking competitors damages brand trust → Action: Eliminate comparative attack ads. Anchor advertising copy exclusively around aspirational relief vocabulary ("relief", "sleep", "walk").

ELSE: Competitor resentment validated → Deploy challenger brand positioning.

Competitor Sentiment Distribution

Neutrality dominates, indicating that an aggressive advertising strategy against competitors would be ill-advised.

Top Customer Word Associations

Key lexical drivers providing the foundation for high-converting advertising promises.

→ Operational Transition: With messaging angles and vocabulary isolated through NLP, we transition to quantitative statistical validation via hypothesis testing and predictive econometric modeling.

5. Phase 3: Inferential Validation & Machine Learning Models

Explanatory Cap & Business Problem: Do empathetic creative variants provably outperform traditional ads, how does educational reassurance scale engagement, and how should customer cohorts be segmented for retention?

A. Inferential Testing: Empathetic vs. Generic Creative (Student's t-Test)

Hypothesis & Statistical Test: Null hypothesis H₀ posits that empathetic copy and generic clinical copy produce identical Click-Through Rates. Two-sample t-test rejects H₀ with 99.7% statistical confidence (p < 0.003).

Test Result: Predicted CTR = 2.08% (Empathetic) vs 1.22% (Generic Baseline) | +70% Relative Lift

Decision Rule (IF / ELSE):

IF p < 0.01: Significant behavioral superiority confirmed → Action: Deploy empathetic creative hook across 100% of acquisition ad spend.

Predicted Ad CTR (T-Test comparison)

The T-test confirms that the empathetic message significantly outperforms the generic approach.

B. Econometric Elasticity: Educational Reassurance vs. Engagement

Hypothesis & Model Specification: Educational content addressing chronic pain reassurance directly increases on-site session engagement.

Model Specification: ΔEngagement Rate = β₀ + 2.50% × (Reassurance Content) | p-value = 2.66 × 10^-8

Decision Rule (IF / ELSE):

IF p < 0.001: Robust positive elasticity → Action: Build dedicated reassurance content hubs to lift site-wide conversion velocity by +2.5 percentage points.

Reassurance Prediction: Articles vs Engagement Increase

Our model predicts a significant increase in engagement thanks to a targeted content strategy.

C. Unsupervised Machine Learning: Customer Lifetime Value (K-Means Clustering)

Unsupervised Partitioning: Clustering customer transactions across order frequency and monetary value isolated a highly profitable "Champions" cohort.

Identified Cluster: High-LTV Segment ($55–$80 AOV, 10–20 orders/year)

Decision Rule (IF / ELSE):

IF High-Frequency Cluster Identified: High Customer Lifetime Value → Action: Construct an exclusive VIP retention workflow to maximize recurring subscription revenue.

Customer Segmentation (K-Means Clustering)

Clustering reveals a high-value customer segment, a priority target for our loyalty initiatives.

6. Chronological Executive Action Plan & Financial Roadmap

1. Immediate Actions (Within 48 Hours)

Deploy Empathetic Ad Hooks: Launch creative assets centered on relief, restored sleep, and normal mobility; pause clinical and feature-centric ad variants.
Halt Comparative Messaging: Remove all aggressive competitor-bashing references to align with neutral customer sentiment.

2. Tactical Improvements (Within 15 Days)

Publish Reassurance Hubs: Launch structured educational reassurance landing pages to capture the predicted +2.5 percentage point lift in engagement.
CTR Lift Capitalization: Capitalize on the predicted 2.08% CTR (+70% lift) to drive lower blended customer acquisition costs.

3. Strategic Initiatives (30 to 60 Days)

Champions VIP Retention Cycle: Deploy automated post-purchase lifecycle email sequences targeted at the K-Means "Champions" segment ($55–$80 AOV).
Predictive LTV Optimization: Protect brand margins by fostering community loyalty, transforming one-time buyers into long-term subscribers.

7. Demonstrated Technical Skills

Natural Language Processing (NLP) in R: Text mining, tokenization, stopword cleaning, sentiment scoring with sentimentr, and bigram frequency analysis.
Inferential Statistics: Two-sample Student's t-test hypothesis testing evaluating creative performance under strict statistical significance thresholds.
Machine Learning & Econometrics: Ordinary least squares linear regression modeling and unsupervised K-Means customer segmentation.

SEO Traffic Drop Diagnosis

View Dashboard View Presentation

1. Context: Business Problem & Scope

Core Business Question:

"Why did organic search traffic collapse by over 90% in May 2025 for an artisanal e-commerce brand, and what specific technical bottlenecks, device segments, and template architectures must be resolved to recover 10,000+ monthly clicks?"

When an established e-commerce store experiences an abrupt decline in organic search acquisition, generic content production is ineffective without a forensic technical audit. Serving as lead analytics consultant, I investigated a sudden traffic collapse observed from May 2025 across Google Search Console (GSC) and Google Analytics 4 logs, deploying advanced data modeling in Google Sheets to isolate the algorithmic root cause and model a quantitative recovery roadmap.

2. Methodology & Decision-Making Visual Standards (Protocols)

Data Aggregation & Audit Protocol: Exported multidimensional GSC performance logs (clicks, impressions, average position, and CTR) segmented by URL, device, and timestamp. Built an analytical data model in Google Sheets utilizing dynamic Pivot Tables, SUMIFS, and AVERAGEIFS formulas to classify URL taxonomies into Informational (Blog) versus Commercial (Product/Category) templates.

Unified Visual Standard (Gray / Orange Rule):

■ Gray (#64748B): Depicts baseline performance, stable commercial catalog pages, and compliant desktop traffic volumes.
■ Orange (#F97316 / #FADB91): Exclusively isolates penalized technical templates and critical failure points (mobile device collapse, Core Web Vitals deficits, and top-priority recovery levers). Whenever an element appears in orange, immediate engineering remediation is required.

Diagnostic & Modeling Protocol: Metrics are tracked across operational bounds (pre-penalty baseline of 341 daily clicks versus post-drop low of 28 daily clicks) and modeled using ordinary least squares linear regression to establish the mathematical sensitivity between SERP position recovery and monthly click gains.

3. Phase 1: Architectural Segmentation (Loss by Page Template)

Explanatory Cap & Business Problem: Did the traffic collapse affect the entire domain evenly, or was the penalty isolated to specific URL templates?

The Why & The How: We hypothesized that the ranking devaluation was caused by template-specific technical rendering issues rather than sitewide domain penalties.

Applied Calculation: Template Loss Share (%) = [Click Loss on URL Template / Total Domain Click Loss] × 100

Distributions & Empirical Findings: The traffic loss was almost entirely concentrated on informational blog articles (the top-of-funnel content engine that historically captured non-branded discovery queries). Transactional catalog pages retained stable baseline rankings, confirming a localized template defect.

Conditional Decision Rules (IF / ELSE):

IF Loss Share > 80% on Single Template: Template-level technical defect confirmed → Action: Freeze sitewide redesign; concentrate engineering sprints exclusively on blog page code architecture. (Isolated in Orange)

ELSE: Sitewide penalty → Execute full-site backlink and domain-level audit.

Organic Traffic Loss by Page Type

The loss of traffic is mainly concentrated on the blog posts informational in nature. These are the pages that attracted a large volume of users and are now in difficulty.

→ Operational Transition: Having isolated blog templates as the primary defect area, we analyze user access hardware across a longitudinal timeline to identify the technical catalyst.

4. Phase 2: Hardware & Device Breakdown (Mobile vs. Desktop Trajectory)

Explanatory Cap & Business Problem: Why did desktop traffic retain its position while total organic sessions plummeted?

The Why & The How: We evaluated daily click trajectories across device categories to test for algorithmic mobile-first penalties.

Empirical Spread: April Baseline = 341 Daily Clicks → May-June Low = 28 Daily Clicks (-91.8% Deficit | 313 Clicks/Day Lost)

Distributions & Empirical Findings (The "Aha Moment"): The traffic collapse occurred exclusively on mobile devices, with desktop traffic remaining entirely flat. Cross-referencing Google's algorithm release history revealed a perfect temporal alignment with Google's Core Web Vitals mobile page-experience update in May 2025. Unoptimized image payloads, heavy render-blocking scripts, and cumulative layout shifts caused Google's mobile bot to sharply downgrade the mobile blog experience.

Conditional Decision Rules (IF / ELSE):

IF Mobile Drop > 90% while Desktop Flat: Core Web Vitals mobile rendering penalty → Action: Prioritize mobile LCP (Largest Contentful Paint) and INP (Interaction to Next Paint) optimizations over keyword re-targeting. (Mobile drop in Orange)

ELSE: Uniform drop → Algorithmic content helpfulness penalty.

Traffic Volume: Mobile vs Desktop Over Time

The drop is clear and brutal. It began in May 2025, breaking with the good momentum of previous months. Our click traffic fell by 341 in April to an average of 28 in May-June.

→ Operational Transition: Having diagnosed mobile Core Web Vitals as the root cause, we deploy linear regression modeling to forecast the precise traffic recovery resulting from technical remediation.

5. Phase 3: Econometric Recovery Model (Position vs. Monthly Clicks)

Explanatory Cap & Business Problem: What is the quantitative traffic upside of resolving these technical issues, and does the engineering sprint justify resource allocation?

The Why & The How: We modeled click-through decay across SERP ranking positions using ordinary least squares regression in Google Sheets.

Model Specification: Monthly Clicks = β₀ + β₁ × (Average SERP Rank) | R² > 0.88

Distributions & Empirical Findings: Resolving mobile Core Web Vitals bottlenecks on the top 5 to 10 impacted blog URLs and restoring positions 1–3 is mathematically modeled to recover over 10,000 monthly organic clicks, re-establishing pre-penalty acquisition velocity.

Conditional Decision Rules (IF / ELSE):

IF Projected Recovery > 10,000 Clicks/Month: High commercial ROI → Action: Deploy high-priority engineering sprint dedicated to mobile page speed, image compression, and script deferral.

SEO Position vs Monthly Clicks (Linear Regression Model)

Fix Core Web Vitals (speed, usability) on the 5 to 10 most affected blog pages immediately. Regression modeling predicts that restoring our initial positions will help us regain over 10,000 monthly clicks.

6. Chronological Executive Action Plan & Financial Roadmap

1. Immediate Actions (Within 48 Hours to 14 Days - "Stop the Bleed")

Core Web Vitals Remediation: Compress image assets, defer non-critical JavaScript, and eliminate layout shifts across the top 10 traffic-generating blog posts.
Mobile Template Refactor: Strip unoptimized third-party plugins from mobile blog layouts to achieve Google PageSpeed scores > 90.

2. Tactical Improvements (Within 15 to 45 Days - "Aim for Excellence")

Engagement Optimization: Restructure mobile article layouts and implement internal linking to increase mobile engagement rate from 45% to 60%.
Traffic Re-ignition: Secure the predicted recovery of 10,000+ monthly organic clicks to restore top-of-funnel lead velocity.

3. Strategic Initiatives (60+ Days - "Long-Term Quality Assurance")

Automated GSC Performance Monitoring: Implement automated Looker Studio dashboards tracking mobile vs desktop ranking divergence in real time.
CI/CD Performance Gate: Establish strict Lighthouse CI benchmarks in the development pipeline to ensure zero future organic traffic loss from unmonitored code releases.

7. Demonstrated Technical Skills

Search Analytics & Forensic SEO Auditing: Google Search Console API analysis, ranking degradation diagnostics, and algorithmic update correlation.
Business Intelligence & Modeling in Google Sheets: Complex data modeling using SUMIFS, AVERAGEIFS, dynamic Pivot Tables, and ordinary least squares regression.
Technical Web Performance: Core Web Vitals optimization (LCP, INP, CLS) and mobile-first architectural remediation.

Expatriation Financial Dashboard

View Google Sheets Dashboard

1. Context: Business Problem & Scope

Core Business Question:

"How can multi-currency cash flows, dynamic foreign exchange volatility, and international cost-of-living indices be engineered into an automated ETL pipeline and BI decision dashboard to model long-term financial independence?"

Cross-border financial planning requires integrating disparate real-world datasets into an automated, fault-tolerant reporting architecture. Simulating an international relocation to Osaka, Japan, this project establishes a scalable ETL and Business Intelligence dashboard in Google Sheets. The objective was to replace static budgeting with a live decision-support system consolidating multi-asset income, live web-scraped cost benchmarks, and real-time currency conversions.

2. Methodology & Decision-Making Visual Standards (Protocols)

Data Engineering & ETL Protocol: Architected a multi-tier data pipeline in Google Sheets:

Automated Ingestion & Web Scraping: Extracted live cost-of-living data for Osaka from Numbeo via =IMPORTHTML("https://www.numbeo.com/...", "table", ...), eliminating manual data entry.
Data Federation: Centralized decentralized asset ledgers and manual expense logs using secure =IMPORTRANGE() connections.
Master Data Management (MDM): Enforced schema integrity across evolving expenditure categories by appending normalized rows rather than modifying historical keys, preserving mathematical auditability.
Real-Time API Integration: Ingested dynamic foreign exchange rates via =GOOGLEFINANCE("CURRENCY:USDJPY") to continuously normalize USD revenue into local JPY liabilities.

Unified Visual Standard (Gray / Orange Rule):

■ Gray (#64748B): Depicts baseline living expenses, compliant budget allocations, and self-sustaining income streams.
■ Orange (#F97316 / #FADB91): Exclusively highlights critical deficit thresholds and currency volatility risks (negative cash flow margins, high-inflation spikes, and emergency rebalancing alerts). Elements in orange mandate immediate budget adjustments.

KPI & Tracking Protocol: Designed an in-cell tracking architecture utilizing =SPARKLINE() formulas to visualize month-over-month trajectory directly inside summary cells. Financial health is governed by the Financial Independence Ratio (FIR): FIR = (Passive Portfolio Income / Simulated Expenses) × 100.

3. Phase 1: Dynamic Multi-Currency Normalization

Explanatory Cap & Business Problem: How do macroeconomic currency swings impact international purchasing power, and how can real-time FX tracking protect disposable income?

The Why & The How: We hypothesized that unhedged exchange rate volatility between USD and JPY can swing real living costs by 15% to 20% over a 12-month period. Programmatic conversion ensures that budget buffers reflect live purchasing power.

Applied Formula: Normalized Living Cost (JPY) = Base Cost × FX_Rate(USDJPY) × Inflation_Index

Distributions & Empirical Findings: Web-scraped indices established precise ground-truth benchmarks across housing, nutrition, transit, and discretionary healthcare in Osaka. Automated FX adjustments revealed periods of favorable currency arbitrage, allowing strategic pre-funding of local reserves.

Conditional Decision Rules (IF / ELSE):

IF 90-Day FX Depreciation > 10%: Purchasing power eroded → Action: Trigger automatic rebalancing of domestic reserves into local currency cash buffers. (Isolated in Orange)

ELSE: Exchange rate within normal variance → Maintain standard dollar-cost averaging transfers.

→ Operational Transition: Having automated multi-currency data ingestion, we connect expense projections against passive asset yields to model long-term financial independence velocity.

4. Phase 2: Financial Independence Modeling & Solvency Trajectory

Explanatory Cap & Business Problem: At what exact trajectory does passive capital yield permanently surpass simulated international living expenses?

The Why & The How: We modeled net financial coverage over time to quantify safety margins and capital preservation requirements.

Coverage Calculation: Net Surplus / Deficit = Total Investment Yield - Total Simulated Operating Living Expenses

Distributions & Empirical Findings: As displayed in the trajectory model, passive portfolio income steadily overtakes total living expenses, generating an accelerating safety cushion. In-cell sparkline indicators confirm consistent month-over-month compounding, validating full operational autonomy.

Conditional Decision Rules (IF / ELSE):

IF Financial Independence Ratio ≥ 120%: Structural financial autonomy achieved → Action: Greenlight relocation roadmap; transition portfolio allocation from high-risk growth to capital preservation and cash flow yield. (Surplus buffer in Gray)

ELSE IF FIR < 100%: Deficit alert → Enforce discretionary spending caps and increase capital accumulation rate.

Simulated Financial Independence (Income vs Expenses)

The master data sheet acts as the single source of truth. With advanced filters, I can explore expenses by category, compare asset performance, and visualize the impact of inflation on my budget over time.

5. Chronological Executive Action Plan & Financial Roadmap

1. Immediate Actions (Within 48 Hours)

Pipeline Health Verification: Validate real-time GOOGLEFINANCE exchange feeds and audit Numbeo web-scraping queries against schema changes.
Expense Classification Audit: Ensure 100% of outbound expenditure lines map strictly to standardized category taxonomy keys.

2. Tactical Improvements (Within 15 to 30 Days)

Macro Stress Testing: Construct scenario models simulating compound macro stress: +15% local inflation combined with a -10% unfavorable FX shift.
Dynamic Budget Safeguards: Implement automated alert rules in Google Sheets to flag category overspending before monthly thresholds are breached.

3. Strategic Initiatives (60+ Days)

Autonomous Financial Governance: Maintain an uninterrupted Financial Independence Ratio ≥ 120%, ensuring complete cross-border solvency and operational peace of mind.
Automated Wealth Architecture: Expand ETL pipeline to ingest multi-brokerage API statements for real-time net-worth auditing.

6. Demonstrated Technical Skills

Data Engineering & Automated ETL in Google Sheets: Automated web scraping with =IMPORTHTML, secure cross-workbook federation via =IMPORTRANGE, and dynamic API streaming using =GOOGLEFINANCE.
Business Intelligence Architecture & Formulas: Advanced multi-condition aggregation (QUERY, FILTER, SUMIFS) and embedded cellular micro-visualizations with =SPARKLINE.
Master Data Management (MDM): Schema governance, data normalization, and longitudinal financial modeling under currency volatility.

Privacy Policy

Last Updated: July 15, 2026

Introduction

Welcome to the portfolio website of John Bellanger ("we," "our," or "us"). We are committed to protecting your privacy. This Privacy Policy explains how we collect, use, and safeguard your information when you visit our website, in compliance with applicable regulations, including the California Consumer Privacy Act (CCPA) and California Privacy Rights Act (CPRA).

Data Collection & Processing

We process data with a privacy-first mindset. The only data processed is as follows:

Anonymized Analytics: We use Google Tag Manager (GTM) and Google Analytics 4 (GA4) to collect aggregate statistical data about site usage (such as page views, time spent, and referral sources). IP address anonymization is strictly enabled, Google Signals are disabled, and data sharing features for advertising are turned off. We do not use advertising pixels (such as Meta, LinkedIn, etc.) or collect personally identifiable information (PII) through tracking.
Contact Form Information: If you voluntarily contact us using the contact form or email, we collect your name, email address, and the content of your message. This data is only used to respond to your inquiry and is never shared, sold, or rented to third parties.

Cookies and Tracking Technology

Our website utilizes cookies for analytics purposes. Cookies are small files stored on your device that help us analyze web traffic. You can configure your browser to reject cookies or alert you when a cookie is sent. However, doing so will not affect your user experience on this site.

Your Rights (CCPA / CPRA & International Visitors)

If you are a resident of California or access the site from jurisdictions with privacy regulations, you have specific rights regarding your personal information under the CCPA/CPRA, including:

Right to Know: The right to request disclosure of the personal data collected, used, or shared.
Right to Delete: The right to request the deletion of personal data collected from you.
Right to Correct: The right to request correction of inaccurate personal data.
Right to Opt-Out: Since we do not sell or share your personal information with third parties for cross-context behavioral advertising, no opt-out is necessary.
Automated Decision-Making Technology (ADMT): The right to request information regarding and opt-out of automated decision-making processes or profiling.

To exercise any of these rights, please contact us at contact@johnbellanger.com. We will respond to verified requests in accordance with applicable law.

Automated Decision-Making Technology (ADMT) & Profiling

We do not use Automated Decision-Making Technology (ADMT), artificial intelligence profiling, or automated data processing to make decisions that produce legal or similarly significant effects concerning consumers. Visitors maintain the right to inquire about or opt-out of any automated processing should such technologies ever be deployed.

Data Retention & Security

Contact inquiries are kept only as long as necessary to address your request. We implement standard, industry-recognized security measures to protect the personal information submitted to us.

Contact Information

If you have any questions or concerns about this Privacy Policy, please contact us at:

Email: contact@johnbellanger.com

Advanced Analytics Consultant

The Mind Behind The Data

Tech Stack

Certifications

Case Studies

Negative ROAS Analysis

Targeted Advertising Growth

Apple: Cash Management

Back Pain Sentiment Analysis

SEO Traffic Drop Diagnosis

Expatriation Dashboard

Meta Ads Campaign Performance

1. Context: Business Problem & Scope

2. Methodology & Decision-Making Visual Standards (Protocols)

3. Phase 1: Macro Performance Audit (ROAS vs. CPA Trajectory)

4. Phase 2: Geographic Diagnostic (ROAS by City - Toronto Anomaly)

5. Phase 3: Campaign Unit Economics (Campaign Profitability Comparison)

6. Phase 4: Attribution Integrity & Tracking Divergence (Meta Ads vs. GA4)

7. Chronological Executive Action Plan & Financial Impact Roadmap

1. Immediate Actions (Within 48 Hours)

2. Tactical Improvements (Within 15 Days)

3. Strategic Initiatives (30 to 60 Days)

8. Demonstrated Technical Skills

Google Ads: Prediction on Ads

1. Context: Business Problem & Scope

2. Methodology & Decision-Making Visual Standards (Protocols)

3. Phase 1: Macro Channel Audit (Revenue by Acquisition Channel)

4. Phase 2: Category Concentration Audit (Revenue by Product Category)

5. Phase 3: Predictive Econometric Modeling (Linear Regression via R)

A. Growth Vector: Double Google/CPC Budget on Electronics

B. Margin Protection: Freeze Advertising on "Home" Category

C. Capital Safeguard: Terminate Direct-Conversion TikTok Campaigns

6. Chronological Executive Action Plan & Financial Roadmap

1. Immediate Actions (Within 48 Hours)

2. Tactical Improvements (Within 15 Days)

3. Strategic Initiatives (30 to 60 Days)

7. Demonstrated Technical Skills

Apple: Cash Management Analysis

1. Context: Business Problem & Scope

2. Methodology & Decision-Making Visual Standards (Protocols)

3. Phase 1: Revenue Stream Diversification & Product Concentration

4. Phase 2: Operating Cycle & Negative Working Capital Engine

5. Phase 3: Strategic Capital Allocation Scenarios

6. Chronological Executive Action Plan & Financial Roadmap

1. Immediate Actions (Within 48 Hours)

2. Tactical Improvements (Within 15 Days)

3. Strategic Initiatives (30 to 60 Days)

7. Demonstrated Technical Skills

Back Pain Sentiment Analysis

1. Context: Business Problem & Scope

2. Methodology & Decision-Making Visual Standards (Protocols)

3. Phase 1: Emotional Spectrum Audit (Radar Analysis)

4. Phase 2: Competitor Polarity & Lexical Association (Bigram Mapping)

5. Phase 3: Inferential Validation & Machine Learning Models

A. Inferential Testing: Empathetic vs. Generic Creative (Student's t-Test)

B. Econometric Elasticity: Educational Reassurance vs. Engagement

C. Unsupervised Machine Learning: Customer Lifetime Value (K-Means Clustering)

6. Chronological Executive Action Plan & Financial Roadmap

1. Immediate Actions (Within 48 Hours)

2. Tactical Improvements (Within 15 Days)

3. Strategic Initiatives (30 to 60 Days)

7. Demonstrated Technical Skills

SEO Traffic Drop Diagnosis

1. Context: Business Problem & Scope

2. Methodology & Decision-Making Visual Standards (Protocols)

3. Phase 1: Architectural Segmentation (Loss by Page Template)

4. Phase 2: Hardware & Device Breakdown (Mobile vs. Desktop Trajectory)

5. Phase 3: Econometric Recovery Model (Position vs. Monthly Clicks)

6. Chronological Executive Action Plan & Financial Roadmap

1. Immediate Actions (Within 48 Hours to 14 Days - "Stop the Bleed")

2. Tactical Improvements (Within 15 to 45 Days - "Aim for Excellence")

3. Strategic Initiatives (60+ Days - "Long-Term Quality Assurance")

7. Demonstrated Technical Skills

Expatriation Financial Dashboard

1. Context: Business Problem & Scope

2. Methodology & Decision-Making Visual Standards (Protocols)

3. Phase 1: Dynamic Multi-Currency Normalization

4. Phase 2: Financial Independence Modeling & Solvency Trajectory

5. Chronological Executive Action Plan & Financial Roadmap

1. Immediate Actions (Within 48 Hours)

Advanced Analytics
Consultant