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

  1. 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
  2. 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
  3. 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
  4. Retention & repurchase

    Retention

    • Initial event
    • Return event & comparison group
    • Time window

    Repurchase

    • Formula & repurchasing users
    • Repurchase rate
    • By product · user · channel
    • Visualisation
  5. 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
Exhibit 1. The map was the first deliverable: a shared vocabulary from traffic (UV, PV, new users) through demand, the funnel and retention to user value. The RFM tiers in the last column are the ones the segmentation below is labelled with.

The analysis, step by step

  1. Ad table846,811 ads · price, category, campaign, brand
  2. Impression log1,048,575 rows · one per impression
  3. User table1,048,575 users · 6 coded attributes
  4. Joinuserid · ad_id → 975,441 rows
  5. Cleandrop rows without gender or age level
  6. Price bands9 bands, 100 → 12,800+
  7. Click rate by bandclicks ÷ impressions
  8. User mix by bandage · consumption · shopping depth
  9. K-means5 segments · 40,997 clicking users
  10. RFM tierskeep · develop · win back · general · low
Exhibit 2. The analysis as it was run, in pandas: three tables joined on user and ad, cleaned, banded by price, then read two ways — what gets clicked at each price, and who is looking — before the clicking users were clustered and labelled with the RFM tiers from the map.

Click rate by price band

9 price bands · dots = click rate · bars = share of impressions · 975,441 joined impressions

Click rate3%4%5%6%highest 5.5%lowest 4.2%Share of impressions0%25%50%40%19%17%9%6%4%3%1%1%0–100100–200200–400400–800800–16001600–32003200–64006400–1280012800+
Exhibit 3. Four in ten impressions were ads under 100, and they were clicked most: 5.5% against 4.2% in the 1,600–6,400 bands. Click rate falls with price until the top band, where it recovers to 5.1% on a base of only 6,236 impressions. Cheap goods carry the traffic; high-ticket ads earn their clicks from a far smaller audience.

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
0%25%50%75%100%39%6%18%19%0–100100–200200–400400–800800–16001600–32003200–64006400–1280012800+
Exhibit 4. The audience shifts with price even though click rate barely does. Low-consumption users see 39% of the under-100 impressions and 17% of the 6,400–12,800 band; high-consumption users go from 6% to 20%. Age and shopping depth hardly move across bands, which is why consumption level became the targeting dimension in the recommendations.

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

  1. Win back

    users
    15,094
    click rate
    28%
    median price clicked
    288
    shopping depth (1–3)
    3.0
    female
    65%
    modal age level
    4
  2. Low value

    users
    9,469
    click rate
    27%
    median price clicked
    24
    shopping depth (1–3)
    3.0
    female
    65%
    modal age level
    3
  3. General

    users
    6,490
    click rate
    52%
    median price clicked
    149
    shopping depth (1–3)
    1.6
    female
    50%
    modal age level
    5
  4. Keep

    users
    6,127
    click rate
    99%
    median price clicked
    288
    shopping depth (1–3)
    3.0
    female
    63%
    modal age level
    3
  5. Develop

    users
    3,817
    click rate
    99%
    median price clicked
    23
    shopping depth (1–3)
    2.9
    female
    64%
    modal age level
    3
Exhibit 5. Tiers follow the report's rule: click rate stands in for frequency, the price of clicked ads for monetary value. The largest group, 37%, clicks high-priced ads rarely — the win-back tier the recommendations aimed retargeting at. The two groups near a 99% click rate are mostly users seen once, which is the weakness of a nine-day log, not a loyal core.

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.

← Back to home