Customer Analytics Using Advanced Excel Training Course

Duration 5 days 10 modules · 33 contact hours
Level Advanced Certificate on completion
Who attends Customer Insights Analyst, Marketing Analyst and others
Fee from USD 1,100 KSh 95,000 for Kenyan organisations
Where 52 cities + online See dates by city
Next start 16 Nov 2026 Course code SD-DA-096

Before you join: Participants should be confident using Excel worksheets, formulas, sorting, filtering and PivotTables.

Dates & prices

Two start dates a month in every city, and live online on weekdays or weekends. Fees are per participant; classroom fees include tuition, materials, lunch and refreshments, and the certificate.

Suggest a date or city

Showing the next six months. Later dates, other cities and private deliveries for your team are available on request.

Course overview

Customer data is often spread across CRM exports, sales records, service logs, campaign files and ecommerce transactions. Teams can report headline revenue but struggle to answer operational questions: Which customers are becoming inactive? Which segments generate margin rather than volume? Which campaigns acquire customers likely to return? How should retention effort be prioritised? This course equips participants to build defensible customer analysis in Excel, turning fragmented records into decision-ready measures, segments, cohorts and forecasts.

Participants build structured Excel models for customer acquisition, activation, retention, churn, revenue, margin and lifetime value. They use Power Query to prepare source data, Excel Tables and dynamic-array formulas to create repeatable calculations, and Power Pivot with DAX measures to analyse larger linked datasets. The course covers RFM segmentation, cohort retention analysis, churn measurement, customer lifetime value, campaign funnel performance, incremental response, forecasting and management dashboards. Attention is given to metric definitions, data quality, privacy considerations, model controls and clear interpretation of results.

Teaching is instructor-led over five days, delivered in a classroom in 50+ cities or live online. Each training day runs from 08:30 to 16:30 and provides approximately 6.5 contact hours. Participants work through realistic customer data cases drawn from retail, banking, subscription services, telecommunications and business-to-business sales. They leave with a reusable customer analytics workbook structure, documented metric definitions, analytical templates and a practical plan for applying the methods to their own customer data. A certificate of completion is issued to participants who complete the course.

The course is suited to analysts and commercial professionals who already use Excel and need stronger methods for customer growth, retention and value analysis. It is particularly relevant where specialist analytics platforms are unavailable, data must be reviewed quickly, or management requires transparent calculations that can be inspected and challenged.

What you will be able to do

  • Define customer analytics metrics, grains, business rules and data dictionaries
  • Prepare repeatable customer datasets using Power Query transformations and reconciliation checks
  • Build customer analysis models using Excel Tables, dynamic arrays and advanced formulas
  • Construct Power Pivot data models and DAX measures for linked customer datasets
  • Create RFM, behavioural and lifecycle segments for targeted commercial action
  • Calculate cohort retention, churn, customer lifetime value and campaign response metrics
  • Develop customer demand forecasts, scenarios and driver analyses using Excel methods
  • Produce decision-focused customer dashboards and an analytics application plan

Benefits of attending

For you

  • Build credible customer segments from transaction and behavioural data
  • Explain retention and churn patterns using cohort evidence
  • Calculate customer lifetime value with transparent commercial assumptions
  • Create reusable Excel models instead of one-off customer reports
  • Present customer growth priorities through decision-focused dashboards

For your organisation

  • Improves consistency of customer metrics across marketing, sales and service teams
  • Identifies high-value, declining and reactivation-ready customer groups
  • Supports better targeting decisions using segment profitability and response evidence
  • Reduces manual reporting effort through refreshable Power Query workflows
  • Provides auditable Excel models for customer investment and retention decisions

Course modules

10 modules over 5 days

Participants can define customer analytics requirements, measures and data structures before building a workbook.

  • Customer decision questions translated into measurable analytical requirements
  • Grain, keys and business definitions documented before workbook construction
  • Data dictionary records field meaning, source, refresh cadence and owner
  • Analytical unit selection prevents duplicated customers and distorted segment counts
  • Metric hierarchy separates acquisition, activation, retention, revenue and service measures
  • Source-system reconciliation identifies missing records, timing differences and status conflicts
  • Privacy-by-design considerations restrict unnecessary personal data within customer analysis

Participants can import, clean, combine and load customer data through repeatable Power Query processes.

  • Power Query imports CSV, workbook, folder and database extracts reliably
  • Data types, locale settings and error values standardised during ingestion
  • Append and merge queries create analysis-ready customer transaction tables
  • Column profiling exposes nulls, outliers, duplicates and unexpected categories
  • Fuzzy matching rules reconcile inconsistent customer names and identifiers
  • Query parameters support repeatable date ranges and source locations
  • Load settings separate staging tables from reporting data models

Participants can build maintainable customer calculations using advanced Excel formulas and structured references.

  • Excel Tables provide structured references, calculated columns and controlled expansion
  • XLOOKUP, XMATCH and INDEX-MATCH resolve customer and product attributes
  • SUMIFS, COUNTIFS and AVERAGEIFS calculate cohort and segment measures
  • LET and LAMBDA reduce repeated logic in reusable calculations
  • Dynamic arrays with FILTER, UNIQUE and SORT create responsive extracts
  • IFERROR and validation checks expose calculation exceptions without masking causes
  • Named formulas document assumptions and simplify workbook maintenance

Participants can create linked customer data models and reusable DAX measures for interactive analysis.

  • Star schema separates fact transactions from customer, date and product dimensions
  • Power Pivot relationships connect tables through unique dimension keys
  • Data Model measures use DAX for reusable aggregations and ratios
  • CALCULATE modifies filter context for channel, period and segment analysis
  • Distinct customer counts avoid transaction-level inflation in performance reporting
  • Date tables support month, quarter and year-to-date comparisons
  • Measure naming conventions distinguish rates, values, counts and targets

Participants can develop actionable customer segments using value, behaviour and lifecycle measures.

  • RFM scoring ranks recency, frequency and monetary customer value
  • Quantile bands create comparable segments from skewed spending distributions
  • Behavioural segments combine purchase cadence, category mix and channel preference
  • Lifecycle rules classify prospects, new, active, lapsing and reactivated customers
  • PivotTables compare segment size, revenue, margin and repeat purchasing
  • Segment definitions tested for stability, actionability and commercial relevance
  • Persona briefs connect analytical patterns to offers, service and communications

Participants can measure retention, churn and customer movement through cohort-based Excel analysis.

  • Cohort matrices group customers by acquisition month and first purchase
  • Retention rates distinguish returning customers from repeat transactions
  • Period indexing aligns customer tenure despite different acquisition dates
  • Churn definitions specify inactivity windows, population and observation date
  • Survival curves show active-customer decay across tenure periods
  • Waterfall analysis reconciles opening customers, additions, losses and reactivations
  • Retention heatmaps highlight cohort deterioration requiring investigation

Participants can calculate historical and forward-looking customer lifetime value using commercial assumptions.

  • Customer lifetime value combines contribution margin, retention and acquisition cost
  • Historical CLV calculates realised value from customer-level transaction histories
  • Simple predictive CLV applies average order value and purchase frequency
  • Discounted cash flow converts future customer margin into present value
  • Gross margin assumptions separate profitable revenue from unprofitable growth
  • Scenario tables test retention, frequency and margin improvement cases
  • CLV-to-CAC ratios guide acquisition spend and payback decisions

Participants can assess campaign and funnel performance using consistent conversion and response measures.

  • Funnel definitions align impressions, leads, conversions and completed purchases
  • Conversion rates calculated with consistent denominators at each funnel stage
  • Campaign attribution rules distinguish source, medium, touchpoint and conversion
  • UTM parameter parsing standardises digital campaign classification
  • Control-group comparison estimates incremental response beyond baseline purchasing
  • Response lift measures treatment performance against comparable non-contacted customers
  • Channel dashboards compare cost, conversion, revenue and customer quality

Participants can forecast customer outcomes and test commercial assumptions using Excel forecasting methods.

  • Monthly customer revenue series prepared with complete calendar periods
  • Moving averages smooth volatility before interpreting demand trends
  • FORECAST.ETS projects recurring demand using seasonality and confidence intervals
  • Regression analysis estimates relationships between spend, activity and customer outcomes
  • Correlation checks prevent unsupported causal claims from observational data
  • Scenario Manager compares base, downside and intervention assumptions
  • Forecast accuracy assessed using actual-versus-forecast variance and MAPE

Participants can present customer insights through controlled dashboards and plan their workplace application.

  • Dashboard wireframes prioritise decisions, audiences, actions and refresh frequency
  • PivotCharts and slicers provide controlled exploration without formula changes
  • KPI cards display targets, actuals, variance and directional indicators
  • Conditional formatting draws attention to exceptions, thresholds and data-quality issues
  • Chart selection matches comparison, composition, trend and distribution questions
  • Workbook protection controls formula access, input cells and report distribution
  • Application plan assigns customer analytics decisions, owners, cadence and measures

Exercises and examples are adapted to each cohort's sectors and roles.

What you take back to work

  1. Customer analytics metric dictionary template
  2. Power Query customer-data preparation workflow
  3. RFM and lifecycle segmentation model
  4. Cohort retention and churn analysis template
  5. Customer lifetime value scenario model
  6. Customer dashboard and application plan

Who should attend

  • Customer Insights Analyst — needs repeatable methods for segmentation, retention and value analysis
  • Marketing Analyst — needs to assess campaign response, conversion and customer quality
  • CRM Manager — needs evidence for contact strategy, lifecycle actions and churn prevention
  • Commercial Analyst — needs customer-level revenue, margin and growth reporting
  • Business Intelligence Analyst — needs stronger Excel models for stakeholder-led customer questions
  • Product or Growth Manager — needs customer evidence to prioritise acquisition and retention decisions

Skills and tools covered

Customer segmentationCohort retention analysisCustomer lifetime valuePower Query transformationDAX measure designCustomer dashboard reporting

Tools and standards

Microsoft Excel, Power Query, Power Pivot, DAX

How the course runs

Training methodology

The course combines short instructor briefings with guided Excel model building, customer-data case work, group interpretation sessions and structured review of analytical outputs. Participants work with realistic datasets containing customer profiles, transactions, campaign records and service interactions. Demonstrations show the required Excel methods before participants apply them to progressively richer customer questions. Group discussions test metric definitions, segment actions and management interpretations. The final sessions use application planning to connect workbook outputs to participants' own reporting cycles, decisions and data sources.

Requirements and prerequisites

Participants should be confident using Excel worksheets, formulas, sorting, filtering and PivotTables. Experience working with customer, sales, marketing, service or transaction data is expected, as is familiarity with common commercial measures such as revenue, margin and conversion rate. Participants should be able to interpret basic charts and percentages. Prior experience with Power Query, Power Pivot, DAX, SQL, statistics, programming or specialist CRM platforms is not required. The course teaches these Excel-based methods from the working level needed for customer analysis.

A typical training day

08:30 – 10:30First session
10:30 – 10:45Refreshment break
10:45 – 12:30Second session
12:30 – 13:30Lunch and networking
13:30 – 15:00Third session
15:00 – 15:15Refreshment break
15:15 – 16:30Workshop and daily review

Live online deliveries follow the same structure in East Africa Time, with shorter screen blocks and longer breaks.

What the fee includes

  • Instruction by a practitioner facilitator
  • Full course workbook and materials
  • Exercise files, templates and case studies
  • Refreshments and lunch on classroom deliveries
  • Certificate of completion
  • Post-course application plan

Frequently asked questions

Participants should already be comfortable with formulas, sorting, filtering and PivotTables. Power Query, Power Pivot, DAX and advanced statistical knowledge are taught during the course and are not entry requirements.

Bring a laptop with a current desktop version of Microsoft Excel where possible, ideally Microsoft 365 for access to dynamic arrays and modern functions. You may also bring a non-sensitive sample of your own customer reporting structure or metric questions.

It is designed for analysts, CRM professionals, marketing analysts, commercial analysts, growth managers and business intelligence staff. It suits people who need to turn customer records into retention, segmentation, campaign and value decisions.

The course applies advanced Excel methods specifically to customer growth questions, including RFM, cohorts, churn, lifetime value and campaign response. General Excel mechanics are used as supporting methods rather than taught as isolated features.

Participants can apply the workbook structures to CRM exports, sales transactions, ecommerce records, campaign files or service data. The templates support recurring customer reporting and can be adapted to existing commercial definitions and reporting calendars.

The five days progress from metric design and data preparation to customer modelling, segmentation, retention, value, campaigns, forecasting and dashboards. Each day runs from 08:30 to 16:30, with approximately 6.5 contact hours and scheduled breaks.

Questions about this course?

Ask about dates, fees, group bookings or running it for your team. Our training team replies by email.

Ask on WhatsApp info@skillsetdevelopment.com

More courses in Data Analytics

Data Analytics Business Analytics with SQL Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Business Data Analysis Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Business Data Analysis Using Excel Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics CSPro Data Collection and Management Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Customer Analytics with Python Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Data Analysis and Decision Making Training Course 5 days · from USD 1,100 · next 16 Nov Data Analytics Data Analysis and Information Management Training Course 10 days · from USD 2,200 · next 16 Nov Data Analytics Data Analysis for Managers Training Course 10 days · from USD 2,200 · next 16 Nov