Data · 2023.08 – 2023.10
E-commerce user & marketing analytics
A metric map for e-commerce analysis and a worked segmentation on an anonymized display-ad sample, built during a 2023 business-analysis internship: 1.05 million impressions joined to 384,559 users, read by price band and clustered into five RFM-labelled segments with a marketing treatment for each.
- Role
- Intern — business analysis
- Period
- 2023.08 – 2023.10
- Stack
- Python · pandas · matplotlib · scikit-learn · Word
- Scale
- 1.05M impressions · 384,559 users · 61,117 ads · 9-day anonymized display-ad sample · 52-node metric map · 5 user segments, RFM-labelled
Exhibits
Internship project, Aug–Oct 2023. The source is an anonymized display-ad sample — an ad table, an impression log and a user table with coded attribute levels — covering nine days; every number on this page is computed from it at build time.
- Impressions
- 1.05M9 days · 51,900 clicks
- Click rate
- 4.95%overall, across all price bands
- Users in the log
- 384,55993% matched to the user table
- Ads shown
- 61,117of 846,811 in the ad table
- Segments
- 5K-means on 40,997 clicking users
The metric map: what an e-commerce analyst should measure
five questions · 52 metrics and models, as written up for the team
Traffic & new users
- UV — unique visitors
- PV — page views
New users
- Market growth potential
- Market user portrait
- New vs returning customers
Strategy personas
- FMCG strategy groups
- White-collar
- Middle-aged & older
- Mid-to-high spenders
- Low spenders
Demand & scenarios
User research
- Search terms
- Brand terms
- Category terms
Review analysis
- Service & logistics
- Product attributes & gifts
- Scenario words · positive-review rate
Off-site marketing
- Keyword matching by platform
- KOL seeding & placement
Customer flow
- Where they come from
- Where they go
- Bought together
Funnel & leakage
Stages
- Browse
- Follow / favourite
- Cart
- Order / pay
- Shipped
- Completed
Leak reasons
- Returns & exchanges
- Removed from cart
- Interest moved · order failed
- Order cancelled · bounce
Models
- AARRR
- AIDMA · AISAS
- Consumer & e-commerce funnels
Retention & repurchase
Retention
- Initial event
- Return event & comparison group
- Time window
Repurchase
- Formula & repurchasing users
- Repurchase rate
- By product · user · channel
- Visualisation
User value
RFM
- Recency
- Frequency
- Monetary
Tiers
- Keep — important, active
- Develop — important, growing
- Win back — important, lapsing
- General
- Low value
ABC inventory
- A · 10% of items, 70% of value
- B · 30% of items, 20% of value
- C · 60% of items, 10% of value
The analysis, step by step
- Ad table846,811 ads · price, category, campaign, brand
- Impression log1,048,575 rows · one per impression
- User table1,048,575 users · 6 coded attributes
- Joinuserid · ad_id → 975,441 rows
- Cleandrop rows without gender or age level
- Price bands9 bands, 100 → 12,800+
- Click rate by bandclicks ÷ impressions
- User mix by bandage · consumption · shopping depth
- K-means5 segments · 40,997 clicking users
- RFM tierskeep · develop · win back · general · low
Click rate by price band
9 price bands · dots = click rate · bars = share of impressions · 975,441 joined impressions
Who is looking at each price band
share of impressions by the viewer's consumption level · users without a level excluded
- Consumption level
- Low
- Mid
- High
Five segments of clicking users, labelled with the RFM tiers
K-means on shopping depth, click rate and the price of what was clicked · 40,997 users with at least one click · re-run at build time with a fixed seed
Win back
- users
- 15,094
- click rate
- 28%
- median price clicked
- 288
- shopping depth (1–3)
- 3.0
- female
- 65%
- modal age level
- 4
Low value
- users
- 9,469
- click rate
- 27%
- median price clicked
- 24
- shopping depth (1–3)
- 3.0
- female
- 65%
- modal age level
- 3
General
- users
- 6,490
- click rate
- 52%
- median price clicked
- 149
- shopping depth (1–3)
- 1.6
- female
- 50%
- modal age level
- 5
Keep
- users
- 6,127
- click rate
- 99%
- median price clicked
- 288
- shopping depth (1–3)
- 3.0
- female
- 63%
- modal age level
- 3
Develop
- users
- 3,817
- click rate
- 99%
- median price clicked
- 23
- shopping depth (1–3)
- 2.9
- female
- 64%
- modal age level
- 3
Problem
A business-analysis team wanted two things from an intern. First, a written map of what an e-commerce operation should measure — traffic, demand, the funnel, retention, user value — so that the team’s reports used the same words. Second, a worked example on real-shaped data: given an ad table, an impression log and a user table, which users deserve more marketing spend, and which are drifting away?
Decisions
Write the map before the analysis. The metric map (Exhibit 1) puts 52 metrics and models under five questions, from UV and PV to the RFM tiers. It was reviewed with the team first, so every chart that followed had a named place to land.
Band the price, then read the table two ways. The three tables join on user and ad into one row per impression (Exhibit 2). Price was cut into nine bands, doubling from 100 to 12,800 and above, and each band was read for what gets clicked (Exhibit 3) and for who is looking (Exhibit 4). Reading both prevents the usual mistake of treating a click-rate difference as an audience difference.
Cluster only the users who clicked, and name the clusters with RFM. K-means with five centres on shopping depth, click rate and the price of what was clicked, run on the 40,997 users with at least one click. The clusters were then labelled with the map’s RFM tiers — click rate standing in for frequency, clicked price for monetary value — so the segmentation and the recommendations spoke the map’s language (Exhibit 5).
Recommend by segment, not by average. The clustering report gave each tier its own marketing and ad treatment instead of one blended rule for all users.
Result
The deliverables were the metric map, the analysis report and the clustering report. Click rate falls from 5.5% in the under-100 band to 4.2% around 1,600–6,400 and recovers to 5.1% at the top, while the audience shifts underneath it: low-consumption users drop from 39% of impressions to 17%, high-consumption users rise from 6% to 20%. Of the users who clicked, the largest segment — 37% in the build-time re-run — clicks rarely but on high-priced ads, the win-back tier; the report’s own run put its five groups at 45%, 37%, 9%, 8% and under 0.1%.
Limits
A nine-day anonymized sample whose files were capped at 1,048,575 rows when saved through Excel, so the impression log and the user table are truncated, and user attributes are coded levels without definitions. Per-user click rates on a handful of impressions are noisy: the two segments near a 99% click rate are mostly users seen once. The clustering on this page is a seeded re-run of the report’s method, not the original run, which is why the segment sizes differ. The charts show correlations — price, category and audience move together — not causes.