Five Customer Data Analysis Questions: A Practical Guide for Thai Online Shops

A Concrete Starting Point: Nong's Skincare Shop

Imagine a fictional Thai online skincare shop — call it Nong's Naturals — that has been selling on its own website and through a LINE Official Account for two years. The owner, Nong, has collected thousands of order records but has never answered even one structured question about customer behavior. She is considering a winback campaign, a loyalty program, and an influencer partnership simultaneously, all using the same undifferentiated customer list.

Nong can choose among five questions according to the decision she needs to make. How many customers bought again inside a declared window? Has one customer gone longer since their last purchase than their own previous intervals? Do first-product buyers return within 90 days? How many buyers overlap across channels? Which records are ready for appropriate follow-up? Each question needs different columns and reasoning. The following examples are separate fictional teaching datasets, not research results or platform outputs.

The Data Contract: Decision, Unit, Date, Exclusions, and Identity — Before Any Question

Before computing a metric, agree on the decision, counting unit, dates, exclusions and identity rules. Two correct calculations can answer different questions if these choices differ. Write them beside the result so another person can reproduce it.

Counting unit: distinguish line-item rows, distinct orders and distinct customer identities. One customer who places two orders with two products each contributes four line-item rows, two orders and one customer. Preserve all three levels; do not flatten the purchase history into a customer list.

Dates: inspect the source format before converting it. An export may use Buddhist Era or Common Era years, and day/month order can be ambiguous. For a modern Buddhist Era date, subtract 543 to obtain the Common Era year. Store an unambiguous date such as 2024-07-06 and keep the original value for checking. A value such as 06/07/2567 cannot tell you the intended day/month convention by itself.

Identifiers: keep customer and order IDs as text, including leading zeros. Numeric conversion can make distinct identifiers such as 00123 and 123 look identical. Preserve the raw file and compare a sample against the source before proceeding. Re-import damaged columns as text from the original; do not guess missing digits or delete the identity column.

Exclusions: declare what counts as a valid order. This guide’s examples count Completed orders and exclude Cancelled, Refunded and explicitly identified Test orders. This is an analytical choice, not a description of every platform. A zero-value order alone is not proof of a test: it could be a replacement or promotion. Check the actual order record before excluding it.

Identity: retain each source customer ID and connect it to a shared customer key only when the match is verified. Shared email addresses and household or reused phone numbers can belong to different people. Formatting a contact field consistently helps comparison but does not prove identity. Keep uncertain matches separate, record why confirmed links were accepted, and preserve the original source IDs.

The Row / Order / Customer Distinction in Practice

Consider this fully fictional order table for Nong's Naturals (all names and IDs invented for teaching purposes only):

Before exclusions, this table contains eight rows, seven distinct order IDs and four customer IDs. Applying this example’s rule excluding Refunded and Cancelled orders leaves six rows, five valid orders and three buyers. State whether a count describes the raw export or the filtered analysis.

Two customers repeat: C-002 has ORD-010 and ORD-013; C-003 has ORD-011 and ORD-014. C-001 has two line items on ORD-009, but its later ORD-015 is cancelled, leaving one valid purchase. The repeat share here is 2 ÷ 3, approximately 66.7%. This miniature table is separate from the larger 180-customer example below.

Question 1 — Historical Repeat-Purchase Share

The question: What share of customers who made at least one valid purchase also made at least one additional valid purchase?

Source columns required: Customer ID (text, not numeric), Order ID (text), Order date (CE, ISO 8601), Order status. Exclusion filter: status must be 'Completed' (exclude Refunded, Cancelled, Test).

Worked example: consider a separate fictional export covering 1 January–30 June 2026, reviewed on 1 July. After the stated exclusions, 180 unique customer IDs have at least one completed order in that window. Exactly 54 have two or more distinct completed order IDs in the same window. Historical repeat share is 54 ÷ 180 = 30%. The remaining 126 have one valid order in that window. These counts are supplied teaching inputs, not a result calculated from the eight-row table.

Why order IDs matter for the count: If Nong had counted order rows instead of distinct order IDs, a customer who added three items to one order would appear to have 'three purchases' and might be miscounted as a repeat buyer. Always count distinct order IDs per customer after excluding invalid statuses.

This is a description of the chosen history, not a prediction or an industry benchmark. Purchases outside the window are not counted: a customer shown as a one-time buyer might have bought before January. Shopify’s customer engagement guide provides the repeat-customer/total-customer formula. Our explicit window, status rules and fictional counts make this particular example reproducible.

Next action: check the distribution of purchase dates among the 126 one-time buyers before choosing any follow-up. Recent buyers may still be using the product; older purchases may warrant a different question. Choose timing using product context, customer commitments and contact preferences. Neither 60 nor 180 days is a universal threshold.

Sources: Shopify: Customer Engagement Metrics (repeat purchase rate formula and cohort reports)

Question 2 — Purchase Timing: Is This Customer Beyond Their Previous Interval?

The question: has a particular customer gone longer since their latest purchase than their own previous purchase intervals? This is a timing question for follow-up, not a prediction that the customer has left.

Required fields: a verified customer key, distinct valid order IDs, order timestamps, status and a stated analysis date. Sort that customer’s valid orders chronologically and subtract each adjacent pair of dates. Two products on the same order still represent one purchase event.

Worked example: one fictional customer has four valid orders, O-101 on 2026-06-05, O-102 on 2026-06-23, O-103 on 2026-07-13 and O-104 on 2026-08-04. Their three completed intervals are 18, 20 and 22 days. The median is the middle value, 20 days. On 2026-09-08, 35 days have elapsed since the last order. The current elapsed interval is 35 ÷ 20 = 1.75 times that historical median.

This calculation does not mean that the customer’s next completed interval is 35 days: they have not purchased again yet. It also does not describe the median of all the shop’s customers. The example uses one person’s three observed gaps, and three gaps are a limited history.

Possible next action: inspect the previous order and existing notes. Was it a larger pack, a gift, a replacement, or a purchase followed by a service issue? If contact is appropriate, ask whether the product is still in use and when another conversation would help. Do not label the customer as churned or send a discount solely because the ratio exceeds one.

For comparisons between cohorts, use a declared common follow-up window and show how many customers actually returned. A median calculated only among people who returned omits those who did not return. Recent buyers without the full window need a separate incomplete-observation label. Do not invent a universal exclusion rule by multiplying an observed median by two.

Median is useful here because one unusually long completed interval affects the mean more strongly. It still does not remove seasonality, changes in pack size or missing earlier orders. Shopify’s analysis guide discusses purchase intervals and combining measured behavior with customer feedback; the dated example and suggested questions here are original teaching material.

Sources: Shopify Enterprise: Ecommerce Data Analysis (retention questions and channel segmentation)

Question 3 — Product-Cohort Retention: Who Comes Back After Buying Product A?

The question: among customers whose first valid order contained Product A, what share made another valid purchase within 90 days, and what share purchased Product A again within the same 90 days?

Required fields: verified customer key, distinct order ID, timestamp, product SKU and status. Find each customer’s first valid order in sufficiently complete purchase history. That order may contain more than one product, so a customer can belong to more than one first-product cohort. Define how equal timestamps are ordered, and avoid calling the first order in a partial export the person’s first-ever purchase.

Worked example: 40 fictional customers first bought Serum A during June 2026. Evaluate on 29 September, after even a 30 June entrant has completed 90 days. Within 90 days of each person’s first order, 14 placed a later valid order and 9 placed a later valid order containing Serum A. Any-product return is 14 ÷ 40 = 35%. Same-product repurchase is 9 ÷ 40 = 22.5%. The nine are included in the fourteen; do not add the two groups.

Five customers bought again within the window without repurchasing Serum A during that window. They represent 5 ÷ 40 = 12.5% of the starting cohort. The records do not show whether they liked the shop, preferred another product or responded to cross-selling. Check what they bought and ask for context before turning that difference into a product recommendation.

Do not compare a customer observed for only 15 days as though they completed the same 90-day window. Mark the result “incomplete observation window” or show an explicitly shorter comparable horizon. A later purchase after day 90 does not belong in this numerator, even when the export includes it. This fixed-window rule belongs to our example’s measurement definition.

The two rates differ by 35% − 22.5% = 12.5 percentage points. A relative comparison would use a denominator: 12.5 ÷ 22.5 ≈ 55.6% higher. State the percentage-point difference here because it is easier to interpret and does not hide the underlying counts.

Next action: review the 26 customers with no second valid order inside the window separately from the five who returned without repurchasing Serum A. Investigate missing orders, product-use context and appropriate follow-up. These groups suggest different questions; neither establishes product dissatisfaction.

Sources: Shopify Enterprise: Ecommerce Data Analysis (retention questions and channel segmentation)

Question 4 — Cross-Channel Unique Customer Count: Avoiding the Double-Count Trap

The question: Across all active sales channels, how many unique customers does the shop actually have?

Required fields: source name, each source’s stable customer ID and a verified mapping to a shared customer key. Contact information can help investigate candidate matches, but should not replace the preserved source identifiers.

Worked example (fictional teaching data): Nong's website reports 120 unique buyer records. Her LINE OA order export reports 90 unique buyer records. A naive sum gives 210 — but this is wrong whenever any customer appears in both channels.

Assume each channel has already been resolved internally and 30 cross-channel matches have been independently verified. Unique buyers are then 120 + 90 − 30 = 180. The naive total of 210 overstates that count by 30 ÷ 180 ≈ 16.7%. Without evidence for the overlap, report separate channel counts and an unresolved range instead of asserting 180.

Matching requirements: the 30 shared customers must be supported by reliable identity links. Lowercasing an email or removing punctuation from a phone number changes comparison format; it does not establish that two records are one person. Family contacts, reassigned phone numbers and missing values can cause false merges. Preserve uncertain records separately and keep a record of confirmed matching decisions.

Fuzzy matching can suggest candidates when names or contact fields contain typos. It can also join different people incorrectly. Microsoft’s Customer Insights guidance recommends restricting fuzzy rules with an exact-match condition. That reduces the candidate set but is not proof of identity. Review uncertain candidates using source records, and never create a general nickname-to-person mapping.

Next action: build a separate customer lookup with verified source-ID links and channel membership. Keep the full order history connected to that lookup. Analyse multi-channel and single-channel buyers separately if it answers a useful question, without assuming either group is inherently more valuable.

Sources: Salesforce Trailhead: Map Required Objects for Identity Resolution · Microsoft Dynamics 365 Customer Insights: Data Unification Best Practices

Question 5 — Follow-Up Queue Readiness: Do You Actually Have What You Need to Reach Anyone?

The question: for this specific outreach purpose, is there a usable permitted contact route, an agreed next action, an owner and a recent-contact history? A technically valid address alone does not make someone ready for a sales message.

Required fields: verified customer key, intended channel, usable contact, documented communication permissions and preferences, assigned owner, next agreed callback, last contact and outcome, and any open service issue. Keep a refusal visible so a new list does not quietly put the person back into sales outreach.

Why check before outreach: a correct analytical segment can still contain unusable contact details, people who declined this contact, agreed future callbacks or records already handled by another team. Resolve these conditions before assigning the list. The example below is a campaign operating policy, not legal guidance or a product specification.

Worked example: suppose 126 fictional candidates were selected for an email/LINE campaign using a documented reason. Apply exclusions sequentially so each record is counted once. Remove 38 without a usable email/LINE route. From those remaining, remove 21 whose recorded preferences exclude this outreach. Then remove 14 already contacted within the campaign’s chosen seven-day cooldown. This leaves 126 − 38 − 21 − 14 = 53 candidates, or 53 ÷ 126 ≈ 42.1%.

The seven-day cooldown is a fictional planning choice, not a universal standard. No usable email/LINE route does not mean the person is unreachable through every channel. Check agreed callback dates and open service issues before treating any of the 53 as ready today; a promised later date takes priority over the campaign list.

Verify the shop’s documented permission and preference records. Do not infer permission solely from a historical purchase. Refer uncertain records to the responsible person and keep refusals, agreed callbacks and recent contacts visible. A commitment means a specific agreed action or date, not a synonym for marketing consent.

Next action: produce a queue with a customer key, reason, owner, permitted route, due date and last outcome. Report the exclusions and pending questions beside the final count. The manager can then distinguish a smaller usable queue from a large list that cannot responsibly be worked.

The Question → Required Fields → Decision Map

The following map links each question to its required fields, calculation, caveat and possible action. Choose the question that serves the decision. The five questions are separate analyses; they are not a mandatory sequence, and their fictional examples do not form one reconciled dataset.

Printing this table and reviewing it against an actual export before beginning any analysis is a practical way to surface data-quality gaps early — before they corrupt a decision.

RFM Scoring in CasperBrain: Four-Quartile Convention and Why Vendor Conventions Must Not Be Mixed

CasperBrain uses four RFM levels: 1 is the strongest purchasing rank and 4 the weakest. A three-character code of 114 indicates strong recency and frequency ranks but a lower total-spending rank in the scoring scope. Monetary value is not spending per order or profit. Read the R, F and M labels separately rather than adding them.

This convention is the opposite of some other vendors who use five-level scores where 5 is best and 1 is worst. If an analyst imports segment labels or score thresholds from another platform without checking the convention, they will invert their segments: the shop's best customers will be treated as the worst, and vice versa. Before applying any RFM logic from an external source, confirm: (a) how many levels are used, (b) which end of the scale is 'best', and (c) whether the composite is numeric or text. Do not mix conventions across platforms in the same analysis.

CasperBrain supports analysis and planning. CasperCall is a separate execution product. This guide describes a workflow; it does not promise that a particular field, campaign action or integration exists in either product.

Data-Quality Checklist: Deduplication, Normalisation, Fuzzy Matching, and Identity Mapping

Use this checklist to preserve information while preparing the analysis. Data cleaning should leave an auditable route back to the original records, especially when several channels use different identifiers.

1. Separate customer lookup from order history. Microsoft’s Customer Insights deduplication guidance concerns customer-profile tables. Apply that distinction carefully: a customer lookup can have one row per verified identity, while the order table must retain each valid order and its line items. Five purchases must not disappear because a customer appears five times.

2. Normalise only a comparison copy. Keep the original values. Trim unwanted whitespace and apply documented phone or email formatting where appropriate. Do not infer identity from a name, nickname or shared contact. A comparison rule should produce candidates or evidence-backed links, not silently overwrite the raw record.

3. Limit fuzzy matching. Start with exact, stable source IDs. Where a typo search is useful, narrow the candidate set and check false matches on real source records. Similarity scores vary with the method; a score of 0.9 is not a 90% probability of the same person. Keep ambiguous cases unresolved.

4. Map identifiers according to the actual platform. Salesforce’s cited mapping lesson describes an Individual object plus a contact-point object or a party identifier. Those are Salesforce-specific requirements. The general lesson for this guide is to retain explicit links between source IDs and the shared customer identity; it is not a claim that every system requires Salesforce’s object model.

5. Inspect defaults and common values. Blank names, placeholders and common names are weak evidence for a match. Count these values before using a field for candidate matching. If a proposed rule joins many unrelated records, stop and inspect examples rather than accepting the apparent reduction in duplicates.

6. Check encoding and identifier integrity. Compare Thai text and leading-zero IDs against the original export. Unexpected ID lengths can indicate damage, but not every source uses a fixed width. Re-import damaged data from the intact source instead of guessing. A successful file upload does not prove every field was interpreted correctly.

Sources: Microsoft Dynamics 365 Customer Insights: Remove Duplicates in Each Table · Microsoft Dynamics 365 Customer Insights: Data Unification Best Practices · Salesforce Trailhead: Map Required Objects for Identity Resolution

Practical Workflow: From Raw Export to Actionable Segment in Five Steps

This workflow is an illustrative process for a shop combining two channels, not instructions for a particular product. Save the raw files, the measurement choices and the resulting calculation together.

Step 1: inspect the untouched export. Check IDs against the source, confirm the date format, and examine example orders with cancellations, refunds and multiple line items. Re-import affected identifier columns as text if necessary; do not delete identifiers or classify every zero-value order as a test.

Step 2: apply the declared valid-order rules. Record counts at each level. For example: a separate fictional export starts with 2,340 rows; after exclusions 2,180 rows remain, representing 1,890 distinct orders and 600 verified customer identities. These are illustrative supplied counts, not an extension of the earlier miniature table.

Step 3: create the identity lookup. Preserve each channel’s customer IDs and link only confirmed matches to a shared key. Keep unresolved matches separate. Connect the order history to this lookup so a purchase remains traceable to its original source. Compute the cross-channel overlap from verified links.

Step 4: answer the chosen question from the linked orders. Historical repeat share needs a declared export window. A purchase-gap example needs ordered events and an analysis date. A 90-day product cohort needs each person’s first valid order, subsequent orders inside the window and enough observation time. A flattened customer list alone cannot reproduce these calculations.

Step 5: check readiness before assigning contact. Record the reason, intended route, preferences, callback commitments, recent contact, service issues and owner. Apply the campaign’s exclusions sequentially and state the final count. Explain unresolved records instead of adding them to the active list to meet a target.

Troubleshooting: if counts change sharply between runs, compare file coverage, date ranges, status filters and identity mappings first. If a product-cohort rate is near zero, check that later orders were imported and windows actually completed. If duplicate counts appear implausibly low, inspect potentially false merges. Record the reason for every methodological change before interpreting it as customer behavior.

Limitations, Honest Caveats, and What This Guide Cannot Do

This guide provides a reproducible analytical framework, not a guarantee of outcomes. All numeric examples are original fictional teaching constructs created for this article — they are not research findings, platform outputs, or typical industry results. Do not cite the example numbers (30%, 35%, 22.5%, 180 unique customers) as benchmarks; they were chosen to make the arithmetic clean and unambiguous, not to represent any empirical distribution.

Observed purchase rates are not causal lifts. A 30% repeat-purchase share tells you that 30% of customers bought again; it does not tell you that any particular action caused them to return. Attributing repeat purchases to a specific campaign, email, or loyalty program requires a controlled comparison — a treatment group and a control group — that a simple historical share calculation does not provide.

Percentages and percentage points are not interchangeable. A retention rate of 35% and a same-product repurchase rate of 22.5% differ by 12.5 percentage points. Describing this difference as '35% higher' would be incorrect.

Recent entrants may lack the observation time required for a fixed-window metric. Show those results separately, use an explicitly comparable shorter horizon, or wait for the stated window to finish. Do not compare partial and complete rates under the same label.

Check the actual Thai and English fields in your source: date conventions, encoding, shared contact details and channel-specific identifiers can all affect the result. No spelling-normalisation rule establishes identity by itself. Validate the fields used in your analysis and preserve uncertain records for investigation.

These examples describe an analytical method and illustrative operating choices. They are not a legal compliance assessment or a specification for CasperBrain, CasperCall or any vendor platform.

Sources and editorial method

Examples are fictional and contain no customer records. Prepared by the CasperBrain team with AI drafting assistance; facts and language are checked before publication.

• Shopify: Customer Engagement Metrics (repeat purchase rate formula and cohort reports)
• Shopify Enterprise: Ecommerce Data Analysis (retention questions and channel segmentation)
• Microsoft Dynamics 365 Customer Insights: Remove Duplicates in Each Table
• Microsoft Dynamics 365 Customer Insights: Data Unification Best Practices
• Salesforce Trailhead: Map Required Objects for Identity Resolution