Etuma Blog

Lists by Topic

see all

Subscribe to Email Updates

Thirteen Customer Experience Database Design Principles

[fa icon="calendar"] Jan 19, 2017 2:55:48 PM / by Matti Airas

Extracting actionable insight is difficult. It takes a lot of work and requires serious thinking and planning. One of the most important things you need to do is to design and implement a CX database.

You, the CX professional, need to own this data. Don’t let BI or IT people set restrictions. Making compromises will greatly hinder your ability to do your work well. Good data is paramount!

Design a database that fulfills both your role-based reporting and analytics requirements

Customer experience management system is not just created for CX stakeholders. You, or a data analyst, need to also be able to analyze the data. This is especially important when analyzing the feedback data from the customer perspective.

In stakeholder (role-based) reports the hypothesis is known. In 'open' data analysis you need to form the hypothesis. Sometimes the hypothesis is not even known like when detecting weak signals. Design the customer experience database so that all this is possible.

I am not going into the specifics of the actual database dimensions. You know what kind of structure makes sense for your company. The graphic below gives you an idea about what kind of dimensions your CX database should have.


Quite a few of these principles applies to any database design. I decided to include them because, for example, I see all the time databases that violates principles 8 and 9. I am not sure why except that the main user is so familiar with the codes that they don't need to have their real value in the visualization or analysis platforms. But other people need to be able to understand and filter by the background variables. That is why you need to use the real names and not codes.

1. Make the response id the data record identifier

Some people believe that the customer record should be the primary dimension. I disagree (and having the customer id as the secondary identifier enables you to do both). Survey is the core of your process. The survey id can tell (has metadata) like the survey type, touchpoint, date etc., that you can use in the analysis.

Survey id is also handy if the database is complicated. By using distinct calculation, you can find out the number of survey responses.

2. Make the customer id the secondary identifier

Some of your feedback will always be anonymous. By using survey processes like NPS and CES, you can connect the customer identity (=customer purchase, and web behavior, and demographics) into the survey record. Being able to filter by customer behavior is paramountly important.

This also enables you to make the surveys shorter because you don't have to ask for basic information (that you already know).

It would be great if you could also capture customer's social media comments. This is possible if you crawl the web and your customer record includes customer's Facebook and Twitter handles. We've seen lately more and more companies doing this. Maybe you should consider doing it too!

3. Exclude privacy protected information

Because of privacy legislation and the fact that you are analyzing overall customer behavior, you need to exclude all privacy protected information (any information that can make identifying the individual person possible) from the customer database. This enables you to share the information with employees and partners without the worry of breaking laws and regulations.

4. Date every survey response

Historically feedback text analysis has focused on analyzing one-time surveys and finding, what I call absolute findings (e.g. cockroaches in a hotel room). With continuous feedback processes like NPS and CES and spontaneous feedback via web forms and emails, relative, time related patterns become detectable.

Remember, you aren't just monitoring the topic volumes because that can be misleading due to seasonal or survey process related issues. You are trying to find out how the key customer experience related processes are performing in relation to background variables and each other.

5. Make sure that your primary operational dimension is a background variable

Most companies have a primary operational dimension. What it is depends on your line of business and how your company is organized. In retail it is usually a store (which you can group to areas and countries). In other businesses it is a product or service (group), geographical area etc.

Having a primary operational dimension as a background variable enables you to benchmark operational entities and extract best practices.

6. Turn the (transactional survey) touchpoint into a background variable

You probably have documented the customer journey (if not you better do it right now!). Every step in this journey is a touchpoint: you can succeed or fail at any of these points. Only by knowing how each touchpoint is performing can you measure the touchpoint performance, and prioritize the improvement efforts.

7. Group data that makes sense to group (products, age)

It is impossible to compare data on too granular level: age, address (which you cannot use anyway and should be replaced with zip code or city) give you information that cannot be used in analysis. The solution is to group them into age groups (e.g. >18, 19-25 etc.).

8. Include information about the market segment

Rather than having  detailed purchase behavior and demographic informatio, it might make more sense for analysis purposes to use customer segments. For example, What do "young urban affluent not married - savvy with web - appreciates fashion" talk about and how they feel (sentiment) about our brand and products.

9. Name the dimensions in clear language (no codes and abbreviations)

Having age groups expressed in coded groups (e.g. A, B, C...) doesn't tell you what is the actual age group. This also applies to organizational units, products etc. unless their abbreviations or codes are well known across your company.

10. Enable analyzing the feedback from customer perspective

Customers don't live in experience silos (even if your organization does). You need to be able to view customers (or customer segments) feedback analysis results across all touchpoints, relationship surveys, spontaneous feedback (if identity or market segment is known) and social media (if identity or market segment is known).

11. Limit the number of data dimensions (columns)

Having dozens of dimensions (background variables) reveals that you don't have a vision about how you should monitor the customer journey and analyze the open-ended customer comments. You often need much less information than you think to be able to extract actionable insights. I cannot give you a specific set of rules here but think about the outcome: what do you really want to achieve with customer feedback analytics.

12. Keep the database as small as possible

Having too much data in your database slows down your analysis and visualization platform. That is why you should only have as much information as you need for trend analysis.

Depending on your industry and the pace of change, you might want to keep 12 to 24 months of historical information in the system. Being able to see further into history is often useless because too many things have changed and the information is not comparable.

13. Structure the text analysis results so that you can drill down contextually to the comment sentence

Your database must be structured so that you can detect Topic and Sentiment level changes. You also need to be able to drill-down through using complex filtering conditions, topic and sentiment to the actual sentence that the customer wrote (not whole comment but contextually relevant sentence).

In practise this means that you need to break every single customer response that has multiple topics in it, into multiple database rows. You can avoid this if you have more sophisticated multi-database structure but if not (and you want to keep it simple) this is the best way to organize the open-ended analysis results feedback data.


Designing and implementing a dedicated customer experience database is definitely worth the effort. Create customer experience databases that fulfill your company’s reporting (or dashboarding) requirements and your actionable insight analysis needs! Don’t tolerate bad data.

Topics: Feedback Analysis, Customer Experience Management, Sentiment Analysis, Data Visualization, NPS, CES, data warehouse, customer experience, feedback categorization

Matti Airas

Written by Matti Airas

My passion is to figure out how to turn open-text feedback into well structured usable information.