Articles in this section

How do I set up my Zendesk integration reporting?

Avatar
Jeremy Kleier
  • Updated
Follow

With your Zendesk integration, you have BombBomb functionality right from within your Zendesk account. But how do you know how many customers are opening and playing your videos? How many of your agents are sending videos versus text ticket responses? You can have all of this information by setting up your very own Zendesk ROI dashboard!

 

To learn how to set up your Zendesk reporting, check out the step by step instructions below:

 

 

Using the Custom Metric Editor

The Custom Metric Editor is an important part of getting your metrics set up correctly. Before you begin, you might want to review this article to familiarize yourself with the basics. While MAQL syntax can typically be typed directly into the Metric Editor text field, project elements like facts, metrics, attributes, attribute values, and variables must be added to metric definitions using the Project Element Sidebar.

 

 

Create a Folder for all your BombBomb Video Metrics

  1. Head to the Manage tab in Good Data.

     
  2. Click the "Add Folder" options on the left side of your screen.


    Create_a_folder.png


  3. Name your folder "BombBomb Video Metrics."

     
    BombBomb Video Metrics

 

Creating custom metrics for each tag (video_personalized, video_macro, video_played)

Step 1: Create a metric for the video_personalized tag.

 

This is the total number of tickets that have the "video_personalized" tag.

 

 

a. Create a metric called "BombBomb Video_Personalized Tag."

Bombbomb Video_Personalized Tag

 

b. Use the Custom Metric Editor and insert the statement below. Remember: the elements (such as "# Tickets" and "Ticket Tag") must be selected and added from the list on the right.

 

SELECT IFNULL((SELECT # Tickets WHERE Ticket Tag = Video_personalized),0)

MAQL_Box_to_put_the_code_.png

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for this metric:

 

This is the total number of tickets that have the video_personalized tag.

 

e. Change the "Sharing & Permissions" to "Visible to All Users."

Metric_Location__Description__and_Sharing_Permissions.png

 

 

 

Create a metric for the video_macro tag

 

1. Name your metric "BombBomb Video_Macro Tag."

 

Bombbomb Video_Macro Tag

 

2. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT IFNULL((SELECT # Tickets WHERE Ticket Tag = Video_Macro),0)

 

 

Create a Metric for the Video_played Tag

 

1. Name your metric "BombBomb Ticket Played."

 

Bombbomb Ticket Played

 

2. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT IFNULL((SELECT # Tickets WHERE Ticket Tag = Video_played),0)

 

3. Add this Metric to the Bombbomb Video Metrics folder.

 

4. Add a description for this metric:

 

Used to identify if the personalized video was played. (currently, this only applies to personalized videos)

 

 

How to get a Satisfaction (CSAT) score for "with video" and "without"

Step 1:  Create BombBomb CSAT with Personal Video metric

 

a. Name your metric "BombBomb CSAT with Personal Video"

 

Bombbomb CSAT with Personal Video

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT % Satisfaction Score WHERE (SELECT Bombbomb Video_Personalized Tag BY Ticket Id)=1

 

c. Add this Metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

Satisfaction score (CSAT) for tickets with personalized video

 

e. Edit the metric format to the following characters:

 

#,##0%

 

 

Step 2: Create Bombbomb CSAT with Video_Macro Tag metric

 

a. Name your metric "BombBomb CSAT with Video_Macro Tag."

 

Bombbomb CSAT with Video_Macro Tag

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT % Satisfaction Score WHERE (SELECT Bombbomb Video_Macro Tag BY Ticket Id)=1

 

c. Add this Metric to the Bombbomb Video Metrics folder.

 

d. Add a description for your metric:

 

The satisfaction score of tickets that have a the Video_macro Tag

 

e. Edit the metric format to the following characters:

#,##0%

 

Step 3: Create BombBomb CSAT with "no video"

 

a. Name your metric "BombBomb CSAT with No Video."

 

Bombbomb CSAT with No Video

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT % Satisfaction Score WHERE (SELECT Bombbomb Video_Personalized Tag BY Ticket Id)=0 AND (SELECT Bombbomb Video_Macro Tag BY Ticket Id)=0

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

The satisfaction score of tickets that don't have a Video_personalized or Video_macro Tag

 

e. Edit the metric format to the following characters:

 

#,##0%

 

 

Step 4. Create your dashboard or report

Build your dashboard with the metrics we just created!

 

 

How to get a Satisfaction (CSAT) Response Rate "with video" and "without video"

Step 1: Create a metric based on Satisfaction Response Rate

 

a. Name your metric "BombBomb % Satisfaction Response Rate With Video."

 

Bombbomb % Satisfaction Response Rate With Video

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT # Satisfaction Responses / # Satisfaction Offered WHERE (Select Bombbomb Video_Personalized Tag BY Ticket Id)=1 AND (SELECT Bombbomb Video_Macro Tag BY Ticket Id)=1

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

This gives you the CSAT response rate of any ticket that has the Video_personalized or Video_macro tag

 

e. Edit the metric format to the following characters:

 

#,##0.0%;
[=null] -

 

 

Step 2: Create a metric for CSAT with No videos

 

a. Name your metric "BombBomb % Satisfaction Response Rate No Video."

 

Bombbomb % Satisfaction Response Rate No Video

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT # Satisfaction Responses / # Satisfaction Offered WHERE (SELECT Bombbomb Video_Personalized Tag BY Ticket Id)=0 AND (SELECT Bombbomb Video_Macro Tag BY Ticket Id)=0

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

The % Satisfaction Response Rate that doesn't have Video_personalized tag or Video_macro tag

 

e. Edit the metric format to the following characters:

 

#,##0.0%;
[=null] -

 

 

Step 3: Add both to a report or dashboard

Add these metrics we just created to your dashboard! 

 

 

 

Touches per ticket

Step 1: Create a metric for BombBomb Agent touches with Video_personalized tag

 

a. Name your metric "BombBomb Agent touches with video_personalized video."

 

Bombbomb Agent touches with Video_personalized video

 

b. Use the Custom Metric Editor and insert the statement below.

 

SELECT COUNT(Ticket Updates,Ticket Text Field Change) WHERE (SELECT Bombbomb Video_Personalized Tag BY Ticket Id)=1

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

Total number of agent touches on tickets where the Video_personalized tag is present

 

e. Edit the metric format to the following characters:

 

#,##0

 

 

Step 2: Create metric for BombBomb average number of touches with personalized video

 

a. Name your metric "BombBomb Average Number of Touches | Personalized Video."

 

Bombbomb Average Number of Touches | Personalized Video

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT Bombbomb Agent touches with Video_personalized video / Bombbomb Video_Personalized Tag

 

c. Add this metric to the Bombbomb Video Metrics folder.

 

d. Add a description for your metric:

 

Average number of agent touches on tickets where the Video_personalized tag is present

 

e. Edit the metric format to the following characters:

 

#,##0

 

 

Step 3: Create metric for BombBomb Agent Touches with Video Macro Tag

 

a. Name your metric "BombBomb Agent Touches with Video Macro Tag."

 

Bombbomb Agent Touches with Video Macro Tag

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT COUNT(Ticket Updates,Ticket Text Field Change) WHERE (SELECT Bombbomb Video_Macro Tag BY Ticket Id)=1

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

Total number of agent touches that contain the video_macro tag.

 

e. Edit the metric format to the following characters:

 

#,##0

 

 

Step 4: Create metric for BombBomb Average Number of Touches | Video Macro

 

a. Name your metric "BombBomb Average Number of Touches | Video Macro."

 

Bombbomb Average Number of Touches | Video Macro

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT Bombbomb Agent Touches with Video Macro Tag / Bombbomb Video_Macro Tag

 

c. Add this metric to the Bombbomb Video Metrics folder.

 

d. Add a description for your metric:

 

Average number of agent touches that contain the video_macro tag.

 

e. Edit the metric format to the following characters:

 

#,##0.00

 

 

Step 5: Create metric for BombBomb Agent touches with No Video

 

a. Name your metric "BombBomb Agent touches with No Video."

 

Bombbomb Agent touches with No Video

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT COUNT(Ticket Updates,Ticket Text Field Change) WHERE (SELECT Bombbomb Video_Personalized Tag BY Ticket Id)=0 AND (SELECT Bombbomb Video_Macro Tag BY Ticket Id)=0

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

Total number of agent touches that do not contain Video_personalized or Video_macro Tags

 

e. Edit the metric format to the following characters: 

#,##0

 

Step 6: Create metric for BombBomb | Tickets without Video Tags

 

a. Name your metric "Bombbomb | Tickets without Video Tags."

 

Bombbomb | Tickets without Video Tags

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT # Tickets WHERE (SELECT Bombbomb Video_Macro Tag BY Ticket Id)=0 AND (SELECT Bombbomb Video_Personalized Tag by Ticket Id)=0 AND (SELECT Bombbomb Video_Macro Tag by Ticket Id)=0

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

Total number of tickets where the Video_personalized and Video_macro Tags are not present.

 

e. Edit the metric format to the following characters:

 

#,##0

 

 

Step 7: Create metric for BombBomb Average Number of Touches | No Video

 

a. Name your metric "BombBomb Average Number of Touches | No Video."

 

Bombbomb Average Number of Touches | No Video

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT Bombbomb Agent touches with No Video / Bombbomb | Tickets without Video Tags

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

Average number of touches where Video_personalized and Video_macro are not present

 

e. Edit the metric format to the following characters:

 

#,##0

 

 

 

Median # of Minutes to Resolution For Tickets with video_personalized, video_macro, and no video

Step 1: Create metric for BombBomb Business Resolution Time (min) With Personal Video

a. Name your metric "BombBomb Business Resolution Time (min) With Personal Video."

 

Bombbomb Business Resolution Time (min) With Personal Video

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT MEDIAN(First resolution time in minutes within business hours) WHERE (SELECT Bombbomb Video_Personalized Tag BY Ticket Id)=1

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

Business Resolution Time (min) With Personal Video

 

e. Edit the metric format to the following characters:

 

#,##0 mins;
[=null]0 mins

 

 

Step 2: Create metric for BombBomb Business Resolution Time (min) With Video_macro Tag

a. Name your metric "BombBomb Business Resolution (min) With Video_macro Tag."

 

Bombbomb Business Resolution Time (min) With Video_macro Tag

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT MEDIAN(First resolution time in minutes within business hours) WHERE (SELECT Bombbomb Video_Macro Tag BY Ticket Id)=1

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

Business Resolution Time (mins) With Video Macro

 

e. Edit the metric format to the following characters:

 

#,##0 mins;
[=null]0 mins

 

 

Step 3: Create metric for BombBomb Business hours Resolution Time (min) With No Video

 

a. Name your metric "BombBomb Business hours Resolution Time (min) With No Video."

 

Bombbomb Business hours Resolution Time (min) With No Video

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT MEDIAN(First resolution time in minutes within business hours) WHERE (SELECT Bombbomb Video_Personalized Tag BY Ticket Id)=0 AND (SELECT Bombbomb Video_Macro Tag BY Ticket Id)=0 AND Ticket Status <>Deleted

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

Business Resolution Time (mins) when Video_Macro and Video_personalized are not present.

 

e. Edit the metric format to the following characters:

 

#,##0 mins;
[=null]0 mins

 

 

Video play rate for tickets 

Create metric for BombBomb Play Rate | Personal Video

 

1. Name your metric "BombBomb Play Rate | Personal Video."

 

Bombbomb Play Rate | Personal Video

 

2. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT Bombbomb Ticket Played / Bombbomb Video_Personalized Tag

 

3. Add this metric to the BombBomb Video Metrics folder.

 

4. Add a description for your metric:

 

# of tickets with Video_Played tags divided by # tickets with personalized videos

 

5. Edit the metric format to the following characters:

 

#,##0.00

 

 

 

One Touch Resolution Rates

Step 1: Create metric for BombBomb One Touch With Video Personalized

 

a. Name your metric "BombBomb One Touch With Video Personalized."

 

Bombbomb One Touch With Video Personalized

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT IFNULL((SELECT # One-Touch Tickets WHERE Ticket Tag = video_personalized),0)

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

Total number of one-touch tickets where the Video Personalized tag is present

 

e. Edit the metric format to the following characters:

 

#,##0.00

 

 

Step 2: Create metric  for Personalize Video One-touch Resolution rate

 

a. Name your metric "Personalize Video One-touch Resolution rate."

 

Personalize Video One-touch Resolution rate

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT One Touch With Video Personalized / Bombbomb Video_Personalized Tag

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

One Touch resolution rate of the tickets where the Video_personalized tag is present.

 

e. Edit the metric format to the following characters:

 

#,##0%;
[=null] -

 

 

Step 3: Create metric for BombBomb # One-touch Ticket with no video tags

 

a. Name your metric "bombBomb # One-touch Ticket with no video tags."

 

Bombbomb # One-touch Ticket with no video tags

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT # One-Touch Tickets WHERE (SELECT Bombbomb Video_Macro Tag BY Ticket Id)=0 AND (SELECT Bombbomb Video_Personalized Tag BY Ticket Id)=0

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

Total number of tickets where the Video_macro and Video_personalized are not present

 

e. Edit the metric format to the following characters:

 

#,##0

 

 

Step 4: Create metric for BombBomb No Video One-touch Resolution rate

 

a. Name your metric "bombBomb # One-touch Ticket with no video tags."

 

Bombbomb # One-touch Ticket with no video tags

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT # One-touch Ticket with no video tags / Bombbomb | Tickets without Video Tags

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

The resolution rate where Video_macro and Video_personalized tags are not present

 

e. Edit the metric format to the following characters:

 

#,##0%;
[=null] -

  

 

Step 5: Create metric for BombBomb # One-Touch Resolution with Video Macro

 

a. Name your metric "BombBomb # One-touch Resolution with Video Macro."

 

Bombbomb # One-Touch Resolution with Video Macro

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT # One-Touch Tickets WHERE (SELECT Bombbomb Video_Macro Tag By Ticket Id)=1

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

Total number of one touch tickets where Video_macro tag is present.

 

e. Edit the metric format to the following characters:

 

#,##0

 

 

Step 6: Create metric  for BombBomb Video Macro One-touch Resolution rate

 

a. Name your metric "Bombbomb Video Macro One-touch Resolution rate."

 

Bombbomb Video Macro One-touch Resolution rate

 

b. Use the Custom Metric Editor and insert the statement below exactly as it reads:

 

SELECT Bombbomb # One-Touch Resolution with Video Macro / Bombbomb Video_Macro Tag

 

c. Add this metric to the BombBomb Video Metrics folder.

 

d. Add a description for your metric:

 

The one touch resolution rate where video_macro tag is present.

 

e. Edit the metric format to the following characters:

 

#,##0%;
[=null] - 

 

 

 

If you'd like to read about the methodology of how this system works, you can check out the Zendesk support article:

 

There are two main ways to do this:

  1. Add a numeric range filter to the report.

  • For the attribute, select Ticket ID.
  • For the metric, select your custom tag filtering metric.
  • For the range, there are two options:
    • Select "is equal to 1" for tickets that do have the tag.
    • Select "is equal to 0" for tickets that do not have the tag.

This will filter the whole report based on the tag in your metric. This is useful if you want to compare several different metrics with the same tag restrictions.

  1. Nest the tag filter in a custom metric.

This will vary a bit based on the metric. However, the tag filtering portion should follow the template below:

  • Tag is present: SELECT metric WHERE (SELECT # Tickets with your_tag BY Ticket Id)=1
  • Tag is not present: SELECT metric WHERE (SELECT # Tickets with your_tag BY Ticket Id)=0


This allows you to compare different tag filters within the same report.

 

 

Related articles

Comments

0 comments

Please sign in to leave a comment.