Voice of the Customer (VOC) – Dynamics 365 CRM

Tags: Dynamics 365 Dynamics CRM feedback survey VOC Voice of customer

Introduction To Voice of the Customer

For any business to be successful or improve their services, learning about their customers & customer feedback plays a pivotal role. Analyzing the feedback from customers and making required changes to the business process/models accordingly is part of any business growth. Keeping this in view, Microsoft Dynamics 365 CRM which is customer centric or revolves around customer relations has introduced a survey tool which can be embedded within your Dynamics 365 CRM and helps in capturing the customer feedback.

Voice of the Customer (VOC) was introduced with Microsoft Dynamics CRM 2016 online, and is used to capture feedback regarding our products or services from within CRM. With Voice of the Customer, we can set up surveys, distribute them to contacts or leads via workflows, and capture responses. The captured data can then be utilized for analysis, generating reports and making appropriate changes to our product/service.

This feature/app is available globally as Voice of the Customer solution for Microsoft Dynamics 365 CRM (here after referred as D365)  online subscriptions. It can be installed from the Applications area of Dynamics 365 Administration Center.

Installation

We will walk you through installation of the solution step by step in detail.

Sign in to https://login.microsoftonline.com with your Global Administrator or CRM System Administrator credentials.

• Navigate to Admin Centers > Dynamics 365
• In the D365 Administration Center window, navigate to Applications Tab.

• Select the Voice of the Customer solution, and click on Manage. Proceed through Terms of Service to accept the terms.
• Select any Instance from drop down list and click on Install.

Before Installation, the status of solution will be Not Installed. After clicking on Install, it will take few minutes to start installation and shows status as Installation Pending. When the solution is ready, status will change to Installed.

Note: During Installation Pending status, the CRM instance is in maintenance mode and is offline for a short period of time.

Once the Voice of the Customer solution is successfully installed, Voice of the Customer is added to the sitemap.

Survey Process

Using Voice of the Customer, user can conduct Email/Web-based surveys.

Create a Survey

 To create new survey record, navigate to Voice of the Customer > Survey.

On survey record by default, 3 forms are available as shown below:

1. SurveySurvey page has a theme, logo, behavior controls, as well as question/response configurations, we can modify them as per the requirement.  

2. Designer:
   The Designer page allows us to design UI and questions for the survey. It provides options to add pages and questions. For example, we can design a welcome page to reflect the message that we would like our recipients to see upon opening the survey.

The survey questions can be added to a new page or on the existing page via the drag-and-drop editor. The user can double-click on questions to edit it, and the user can click on the edit button on the form for more editing options.

Once all the questions are added and required changes are done, preview the survey to check if all questions are saved properly (only saved questions will be displayed in the preview).

3. Dashboard:

The Dashboard is the statistical representation of Survey data or survey response. It will display all the responses or feedback in bar graphs.

Distributing the Survey

To distribute or use the survey externally, we need to publish the survey. To do so, click on Publish button on the ribbon.

Once the survey is published, email snippet and anonymous link are automatically generated. These email snippets or anonymous links are used for distributing the survey to the customers.

We can test the survey page before sending it to the customers by using Test button. Test displays the survey page that will be sent to customers and the response provided in the test page aren’t stored as survey response.

The Test button will not work if the survey is not published.

The Test button displays only the published content where as Preview displays the saved content.

Email Snippet can be used in email templates or in the email content directly. For example, the below mentioned email snippet can be pasted in the email template or email content directly.
[Survey-Snippet-Start]2dfd9f6d-295f-4488-8132-773357e758ea[Survey-Snippet-End]

The text of the link (email snippet) can be added in Invitation Link Text field. This text is displayed in the email that is sent to the customers.

For Example, in above image, the Invitation Link Text field is set to Click Here. So, Click Here is displayed as a link in email and on clicking this link will redirect to the survey page. If the Invitation link text field is blank then the survey link is displayed in the email that is sent to the customer.

Here is an example of using email snippet in send email step of a workflow:

Anonymous Link is used in Survey Activities and in conducting web-based survey (social networking sites, etc.).

Fill all related fields data of Survey activity and click on save. Once saved, this will generate the survey Invitation Link (anonymous link of survey selected in survey field) that can be sent to users for providing feedback.

Taking Survey

By clicking the link generated above, users can provide feedback. Below is the sample feedback created for capturing the session.

Page 1: Welcome Page

Page 2: Questions Section

Page 3: Complete Page

Survey Responses

Once the survey is completed by customers, the responses that are given by customers are stored in Survey Responses entity.

The results of the survey are stored in Response Summary tab in the Survey Responses entity. It is an HTML page which displays the answers given by customers for all the questions from the survey.

Question Responses entity stores the records of each question and respective answer from the customer.

Response Outcomes – For every survey, there is an outcome after taking the survey (complaint or complement or appreciation, etc.) based on the Responses Routing created for the survey.

Limitations of VOC

• There must be minimum of two pages in every survey.
• A maximum of 200 surveys only can be published at any point of time. You might need to unpublish surveys which are not in use anymore, if needed.
• We can include a maximum of 250 questions in a survey. If you’ve enabled feedback for a survey, you can include a maximum of 40 questions.
• We can create a maximum of 25 pages per survey.
• We can send a maximum of 10,000 email invitations in a 24-hour period. Any emails that exceed that amount will remain pending during that time and will automatically start sending when the time limit is over.
• Voice of the Customer for Microsoft Dynamics 365 will pull a maximum of 2,400 survey responses per day. Voice of the Customer leverages Azure to host the surveys and capture responses. So, If there are more responses (more than 2400) in a day, 2400 will come in, remaining will remain in Azure until the next day.
• Voice of the Customer for Microsoft Dynamics 365 allows storage of a maximum of 1,000,000 survey responses for an instance.

GitLab – Recovering from a corrupted database record

Introduction

One of the most terrifying thing for a Database administrator to come across is a corrupted database.

A database corruption can be defined as a problem associated with the improper storage of the 0’s and 1’s that you need to store on the disk in order to access your data.

Despite the usefulness of the relational databases, they are prone to corruption, which results in the inaccessibility of some or all the data in the database.

More than 95% of corruption happens due to hardware failure. Among the remaining 5% we have,

• Bugs in software itself
• Abrupt system shutdown while the database is opened for writing or reading.
• Changes in SQL account
• Virus infection
• Upgrading software also at times, results in the corruption of the database

This blog addresses a similar issue associated with corrupted databases and how we identified and solved the problem.

Problem understanding

Our organization has an on-premise GitLab server. We have configured GitLab to take daily backups, but due to database corruption the backups started failing with the following error.

Shell

1 2 3 4 5 6 7 8 9

sudo gitlab-rake gitlab:backup:create [sudo] password for osmosys: Dumping database ... Dumping PostgresSQL database gitlabhq_production ... pg_dump: Dumping the contents of table "merge_request_diffs" failed: PQgetResult() failed. pg_dump: Error message from server: ERROR: unexpected chunk number 0 (expected 2) for toast value 204823 in pg_toast_16524 pg_dump: The command was: COPY public.merge_request_diffs (id, state, st_commits, st_diffs, merge_request_id, created_at, updated_at, base_commit_sha, real_size, head_commit_sha, start_commit_sha) TO stdout; [FAILED] Backup failed

The plan was to move the GitLab installation to a new server but since we could not take a backup of our existing installation, we were stuck.

Next Steps

In case of database corruption, usually the loss is limited to the last action of one user, i.e. a single change to data. When a user starts to change data and the change is interrupted — for example, because of network service loss or any other reason, then the database file is marked as corrupted. The file can be repaired, but sometimes we might lose data after repair.

We need to be extremely careful with the live instance and the live data. Experimentation should be done on a virtual machine before performing any actions on the live instance.

The following steps were taken by us,

1. Virtual environment setup
2. Identify corrupted record(s)
3. Database backup without corrupted record(s)
4. Import database on virtual machine
5. Fix database relationship and constraints
6. Export database from virtual environment
7. Import database on live instance
8. Upgrade GitLab
9. Finally run tests

Virtual environment setup

Generally, having a virtual environment gives us the freedom to experiment. Its a good practice to run trials on a virtual machine until you are satisfied with the results. This gives us the confidence that things will work as expected on live instance too.

Now lets create a virtual environment of the live instance. Only the applications which are directly or indirectly related to your database should be installed.

• Install Vagrant
• Install Debian Stretch
• Install PostgreSQL
• Install phpPgAdmin (Optional) – Web-based administration tool for PostgreSQL

GitLab uses PostgreSQL database.

Install Vagrant

Go to Vagrant’s official site and download the deb file for Debian.

You can find the link here.

Install Debian stretch64

Document on how to install Debian Stretch can be found here.

Install PostgreSQL

Shell

1	sudo apt-get update

Shell

1	sudo apt-get install postgresql postgresql-client

Install phpPgAdmin

This step is optional, use any of your favorite PostgreSQL GUI tools.

Shell

1	sudo apt-get install phppgadmin

Identify corrupted record(s)

Login into the live instance.

Become a gitlab-psql user:

Shell

1	sudo -u gitlab-psql -i bash

Connect to the PostgreSQL CLI:

Shell

1	/opt/gitlab/embedded/bin/psql --port 5432 -h /var/opt/gitlab/postgresql -d gitlabhq_production

gitlabhq_production  is the default database.

If you already know about the records which are corrupted then you can skip this step. Otherwise write a small script which selects all the records from each table, select query will fail if the table has corrupted data.

Note that we already knew that the corrupted records belong to merge_request_diffs  table by looking at the backup command error message.

For example if the corrupted record is in the merge_request_diffs table, then we can write a small script to identify the row which is corrupted:

Shell

1 2 3

for ((i=0; i<11124; i++ )); do /opt/gitlab/embedded/bin/psql --port 5432 -h \/var/opt/gitlab/postgresql -d gitlabhq_production -c \"SELECT * FROM public.merge_request_diffs LIMIT 1 offset $i" >/dev/null || echo $i; done

The above script basically select all the rows one by one, but will fail to select the row which is corrupted and will print the row index. Assume that selection failed at index 2045, this tells us that the record at index2045 is corrupted.

Repeat this process for all the tables if you don’t know exactly which table has corrupted records.

Database backup without corrupted record(s)

We were fine with losing these corrupted records, so the next step would be to take a backup without those records.

Select the record, and get it’s row / tuple ID.

Note that if all the columns were selected the query was failing, probably because the corrupted data is in one of the columns.

MySQL

1 2 3

SELECT base_commit_sha, head_commit_sha, merge_request_id, id FROM public.merge_request_diffs LIMIT 1 offset 2045;

base_commit_sha – 8250ee35a6a8d0cd60b5056c7ac94736a048e7a5

head_commit_sha – b3f08056e93f281c2905f6185395787ae7ffaada

merge_request_id – 1091

id – 1933

Create a copy of merge_request_diffs table without the corrupted record i.e. skip id 1933.

MySQL

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE public.merge_request_diffs_copy AS SELECT * from public.merge_request_diffs WHERE id != '1933'   # You can still make a copy of the table using the offset value CREATE TABLE public.merge_request_diffs_copy AS SELECT * from public.merge_request_diffs LIMIT 2044 UNION SELECT * from public.merge_request_diffs OFFSET 2045;

This will create a copy of  merge_request_diffs  table without the corrupted record.

The primary key and the foreign key of  merge_request_diffs are not copied to merge_request_diffs_copy.

Take the backup of the entire database without table merge_request_diffs. Backups will fail if there are corrupted records.

Shell

1	/opt/gitlab/embedded/bin/pg_dump --port 5432 -f git_lab_db_dump.sql -h /var/opt/gitlab/postgresql -d gitlabhq_production --exclude-table-data '*.merge_request_diffs';

The above command will skip  merge_request_diffs  table while taking backup.

Import database on virtual machine

We will setup the backup we took from the previous step on the Vagrant machine.

Create a test database

Create a new database to fix the issues on our vagrant machine.

MySQL

1	CREATE DATABASE gitlab_test ENCODING 'UTF8' TEMPLATE template1;

GitLab owner

Create a PostgreSQL role as  gitlab  to import the  gitlab  database.

Since the ownership of all the tables is given to  gitlab  role in the backup which was taken earlier.

MySQL

1	CREATE ROLE gitlab;

Install  pg_trgm  extension

GitLab uses pg_trgm  few extensions which are not installed by default.

Run the following command to install  pg_trgm  extension:

MySQL

1	CREATE EXTENSION pg_trgm;

Import data to  gitlab_test

PgSQL

1	psql gitlab_test < git_lab_db_dump

Use  psql  command to import from the dump.

Note that  git_lab_db_dump  is the name of the file which contains the database backup from the live instance.

Fix database relationship and constraints

Fixes to database should happen on the vagrant machine.

Get constraints of corrupted table

Go back to the GitLab server and get the table schema of the corrupted table.

Use the below command to get the table schema:

Shell

1	pg_dump -U user_name database_name -t table_name --schema-only

Sample schema is shown below:

MySQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

-- No need to run the table creation query   CREATE TABLE merge_request_diffs ( id integer NOT NULL, state character varying(255), st_commits text, st_diffs text, merge_request_id integer, created_at timestamp without time zone, updated_at timestamp without time zone, base_commit_sha character varying, real_size character varying, head_commit_sha character varying, start_commit_sha character varying );   -- Need to run all the below queries   ALTER TABLE merge_request_diffs OWNER TO gitlab;   CREATE SEQUENCE merge_request_diffs_id_seq START WITH 11421 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;   ALTER TABLE merge_request_diffs_id_seq OWNER TO gitlab;   ALTER SEQUENCE merge_request_diffs_id_seq OWNED BY merge_request_diffs.id;   ALTER TABLE ONLY merge_request_diffs ALTER COLUMN id SET DEFAULT nextval('merge_request_diffs_id_seq'::regclass);   ALTER TABLE ONLY merge_request_diffs   ADD CONSTRAINT merge_request_diffs_pkey PRIMARY KEY (id);   CREATE INDEX index_merge_request_diffs_on_merge_request_id ON merge_request_diffs USING btree (merge_request_id);

Make sure that  merge_request_diffs_copy  table has the same constraints as the originalmerge_request_diffs  table.

Use the above queries to add indexes and foreign keys.

Be very careful with the queries, especially with SEQUENCES,

MySQL

1 2 3 4 5 6

CREATE SEQUENCE merge_request_diffs_id_seq START WITH 11421 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

START WITH 11421  keyword determines what should be the auto increment primary key value for the next record which will be inserted.

Rename table name

Rename the table name from  merge_request_diffs_copy  to merge_request_diffs.

MySQL

1	ALTER TABLE public.merge_request_diffs_copy RENAME TO merge_request_diffs

So now, we have the database backup without any corrupted records.

Testing

Install the same version of GitLab on vagrant machine and use  gitlab_test  database.

Trigger the backup script and confirm that the backups are now being taken properly.

Export database from virtual environment

Take the backup from your vagrant instance

Shell

1	pg_dump -U gitlab gitlab_test > dbexport.pgsql

Import database on live instance

Drop the database from live server

MySQL

1	DROP DATABASE IF EXISTS gitlabhq_production;

Create  gitlabhq_production  database again on live server

MySQL

1	CREATE DATABASE gitlabhq_production ENCODING 'UTF8' TEMPLATE template1;

Install pg_trgm extension

GitLab uses  pg_trgm  extension which will not be installed by default with PostgreSQL.

1	CREATE EXTENSION pg_trgm;

Import the data to  gitlabhq_production

PgSQL

1	psql gitlabhq_production < dbexport.psql

Use  psql  command to import all the records from the dump into  gitlabhq_production  database.

Upgrade GitLab

You can find document here on how to update GitLab.

Note that the upgrade process will automatically run the migrations over the database unless you decide to do it manually.

Finally run tests

After a successful upgrade of GitLab server we should run few tests on it to verify that everything works fine.

Verify that the server is up and running.

Confirm if the database integration is done properly.

Test and verify if the database backup script is able to take backups properly.

References

References were taken from the following blogs, forum posts and Wiki’s.

• https://www.stellarinfo.com/support/kb/index.php/article/common-sql-database-corruption-errors-causes-solutions
• https://dba.stackexchange.com/questions/127846/dumping-database-with-compressed-data-is-corrupt-errors
• http://dbmsmusings.blogspot.in/
• https://www.freelancinggig.com/blog/2017/07/22/mariadb-vs-mysql-vs-postgresql-depth-comparison/
• https://support.office.com/en-us/article/Compact-and-repair-a-database-6ee60f16-aed0-40ac-bf22-85fa9f4005b2