Reporting on organisation data

Hi 364514710867 - 

Apologies for the delayed response. I checked in with some of our Explore experts, and here's what they had to say:

You could use a metric for "organizations" and then add your organization or user custom fields as attributes for that metric.

There is a folder for those fields in the attributes list, it looks like this:

The one note is that this will only cover organizations or users in the tickets dataset, meaning those who have submitted at least one ticket. 

I hope that helps, but do let us know if you have additional questions! 

Hi. Will Explore be including the option to report on Organisations and custom field data against them that is not linked to tickets being submitted against them?  I would like to be able to create such a report but we have some organisations that have never submitted tickets and therefore their data is not included. This renders the current option of generating a report useless for its intended purposes.

What Pete says ^ This is going to cause us an issue come February when Insights is no longer available as we have many organisation reports and the solution above does not always work.

Hi Pete, 

Thanks for reaching out! You can report on organization data without adding any ticket metrics to your report. Here is a quick example using the Support: Tickets dataset along with two custom organization fields: 

Additionally, you can report on organizations that have not submitted a ticket by following these steps:

You'll want to create a query using the Support: Ticket Updates dataset for this. First, you'll need to create a standard calculated metric to count then number of organizations using this formula: 

IF ([Requester organization status]="Active")
THEN [Requester organization ID]
ENDIF

IMPORTANT: Check the compute separately after entering in this formula. Then save. 

Next, you'll want to add this custom metric along with COUNT(Tickets) to your metrics section. Then, we can add the requester organization name attribute to the rows section. 

Lastly, you'll want to go to result manipulation>>metric filter and toggle this to only return 0 > 0 values for COUNT(Tickets) as shown below: 

After applying this filter, you should only see organizations listed that have not submitted tickets. If this isn't quite what you're looking for let us know and we'd be happy to take another look! 

Best, 

Hi Taylor

I tried what you said with the second query using the Support: Ticket Updates dataset.

COUNT(Tickets)  does not seem to be an available metric to select in this dataset. Is that a custom metric as well?

We frequently have the need to create reports listing all organizations that meet specific criteria we've defined in custom Org fields. Currently, I have to switch back over to Insights to do this and it's very easy. Based on what I'm reading in the comments above, if I understand correctly, we'd have to create 2 separate reports in this situation--one for Orgs who have submitted tickets and another for Orgs that have not. Is this correct? It looks like a painful process, regardless. Is there any plan to just add an Organization dataset to Explore, similar to the capabilities in Insights? If not, this is going to cause serious issues for us.

Pete - It looks like you have to use COUNT(Tickets created). 

What Chandra said above ^. Do we have to create two separate reports to get a list of all organisations and their custom fields? This is a clunky workaround and will cause us significant issues. 

I too need the ability to report on a list of organizations and their custom fields, for all organizations regardless of a ticket being submitted by them.

@Chandra, @Tara Papworth & @Terry I took a look at the solution @Taylor Bowser provided earlier, and I think it is possible to consolidate this into one report rather than having one query that looks for organizations with tickets and one query that looks for organizations that have not submitted a ticket.

For starters, @Chandra is correct. You'll need to use the COUNT(Tickets Created) metric since we're querying off of the Tickets Update dataset rather than COUNT(Tickets).

Second, if you skip over the Result Manipulation > Metric filter step, it looks like this will show you a list of all organizations, including those that have never submitted a ticket. I did some quick spot checking within my own instance and it appears to be working on my end. However, the query is limited to ~50K rows so hopefully you aren't like me and have way less than 50K orgs to query off of. :) 

Thank you for that suggested solution Chandra!  I am trying to get a listing of all my current active organizations with the customer fields of security contact information and I am getting multiple lines for some of the organizations. Why would this be?

@Terry Happy to see if I can help reproduce why you're seeing multiple organizations on my end. The first thing to check would be to see if you truly have multiple organizations with the same name.

Assuming that's not the case though, would you mind confirming whether this security contact information is stored in a Custom Organization Field or a Custom User Field?

Also, what type of custom ticket field(s) are you including in the Rows section of your query? 

Hi Chandra, 

Thank you so much! I do not have multiple organizations. The security contact name and email are Custom Organization Fields. I have tried with and without the addition of tags to ensure I am getting just the active customer org's but that isn't helping.

For the rows I have Organization Name, Security Contact Name and Security Contact Email. I am not including Custom Ticket Fields.

I appreciate your help.

@Terry Apologies for the confusion! When I meant what type of custom ticket field these Security fields were, I meant to say what type of custom organization fields were they. Sorry about that!

Based on the name of these custom organization fields though, I assume they would be either Text or Multi-Line Text fields. I've successfully added all of the custom organization fields that I use within my own instance without any organizations duplicated, but I'm afraid I don't have any multi-line fields. If you're using multi-line fields, I wonder if that could be the culprit?

The only other possibility I can think of would be if some of your organizations have multiple security contact names/emails listed, perhaps in a multi-select dropdown? I don't have an organization multi-select field to test that theory with, but I do know that ticket multi-select fields would add a another line if a ticket ID has multiple options selected. 

Otherwise, you might need to reach out to Zendesk Support so that they can take a closer look at your instance and identify what is causing the duplication. From what you've described, nothing is jumping out at me in terms of why you'd be seeing these duplicated organizations. 

Thanks for the work around,... this is a great work around to get the information I need.

Hey Chandra,

I didn't get a chance to update you. I did reach out to support and they suggested I use the API to get a list of all the organization data. This worked for me but there is a lot of data manipulation to get the information I need. I hope there will be an Organization data set in Explore sometime soon to alleviate this work. :)

Thank you!

Hi all!

I just wanted to share a quick update here as it turns out this functionality does exist although it hasn't been well-documented to date. Zendesk's team is aware of this confusion and will be working on rolling out some better documentation in the near future.

In the meantime, if you're in the Tickets dataset, you can select the COUNT(Organizations) metric and then select Requester organization name.

Even though the field has to word 'requester' in it which is commonly linked to tickets, this will allow you to report on organizations and any custom organization data, even for orgs that do not have any tickets or users associated with them. 🎉

I've tested this out on my end as I've had a strong use case for needing to report on organization-level data at my company (mainly to help us QA orgs that are being synced from SFDC) and I can confirm that this is pulling in all of our orgs. I hope this helps others in this thread who have been looking for similar functionality!

Hey everyone! Check out our new Explore recipe for Reporting on user and organization data. 

Thanks @Chandra Robrock for your help pointing users in the right direction while we got this documented :)