HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

Single support staff -- multiple products/sites/brands?


#1

We are using HelpSpot to great effect. Tracking tickets, responding, and generally ensuring nothing falls through the proverbial cracks. Thanks for the great product!

We have a single support staff and three or four products and brands. We would like to funnel all product support requests through the Helpspot application back end. Multiple mailboxes automatically assigned to a category enable this pretty easily on the front end. And we can build front end customer entry points into Helpspot from the different websites.

But I can’t seem to easily customize the outgoing email responses to customers to enable a different template for each product brand. We’d like the default templates that add a ticket URL (based on the email address being used to send the response) to point to the different customer front ends on the sites.

I can’t figure out how to do this? Can you help me out?

Thanks
Pete


#2

Hi Pete,

Great to hear you guys are liking the product, I know your customer base is very active so it’s good to hear HelpSpot is holding up under the load.

Currently HelpSpot’s templating is really more geared around multiple forms under a single brand as opposed to totally separate brands. There’s new features in the works to improve this, but not in the very short term.

What you could do is only send them the URL in the auto reply. Since the auto reply is customizable on a per mailbox basis you simply remove the placeholders and replace them with the correct full URL’s. You can use the ##ACCESSKEY## placeholders to recreate the ##REQUESTCHECKURL## placeholder like so: index.php?pg=request.check&id=##ACCESSKEY##

Then modify the other email templates so that they don’t send the “check your request” footer at all. This isn’t a great solution, but they’ll still have the URL in the original email.

Another idea would be instead of using the ##REQUESTCHECKURL## placeholder in the email templates you switch that out for a custom URL to a script you’ll create and pass that script the ##ACCESSKEY## variable which renders to something like this (12447lohgjg). So in the template use /portal_lookup.php?key=##ACCESSKEY##

Now, in the script you’ll take the access key, pull off the numbers in front that’s the request ID. Do a query against the HS_Request table looking up that ID. In the query join HS_Request to HS_Mailboxes by the xOpenedViaId which holds the mailbox ID. That will tell you which mailbox they came in on and then you can redirect to the correct request check page passing along the access key as well. The SQL should look something like this (off the top of my head)

SELECT HS_Mailboxes.sHostname FROM HS_Request, HS_Mailboxes WHERE HS_Request.xOpenedViaId = HS_Mailboxes.xMailbox AND HS_Request.xRequest = XXXXX

Of course you’ll still have to put a URL in that’s not the same as the brands, I don’t know how big an issue that may be.

One final idea would be to do some magic on to the email itself before it’s actually sent. So point PHP’s mail to a filter script that rewrites the URL’s in the email before they go out based on the “from” header. In many ways this would be cleanest, but it’s beyond my expertise to tell you how to proceed with it.

Let me know if any of this sounds plausible. Also it’s rather late so if I think of something better for your short term needs I’ll update this tomorrow.

Ian


#3

Ian,

I like your last suggestion best and am investigating.

As a follow up – we are also investigating how to get some custom reports from the helpspot database. Do you have any documentation on the database structure of your system? so we can determine how to pull reports etc.

Thanks
Pete


#4

Hi Peter,

Let me know how you make out with that, I’m interested to hear.

I don’t have any formal docs on the DB currently, it’s on the todo list, but there’s just been a bunch of stuff ahead of it. In the meantime if you have specific questions I’m happy to answer, also if you’re not sure where to start you can post the details of the report you’d like and I’ll try and help you out with it. I do know several organizations are using external SQL reporting tools and custom scripts to run reports with great success. Actually we’ve had people doing this since all the way back in the beta.


#5

Ian,

Thanks for your help. I have access to the db – but am no SQL guru. I’m looking for something pretty simple on the db side. I want to dump out a date limited/(closed-open limited) query request with all the info so I can use it in a pivot table report on excel. Then I can do whatever I want with it.

Essentially I want:
For closed requests – ticket ID, person assigned(I assume this is the person who finally closes ticket), status, category, reporting tag, time to close ticket (I guess it is in seconds)

All with ID#'s replaced with actual text values.
For open requests – same thing except instead of time to close ticket I’d like time ticket has been opened.

Once I get these into excel I can use the crosstab/pivot features to get whatever I need in graphical format.

If you can help me with the query so I don’t have figure it all out – that would be great.

On the emails/multi brand thing:
After some discussions, we may just include all the brand’s URL landing pages with the accesskey attached to it. That way the user presumably knows what they are interested in. Kind of a kludge – but we don’t want to be hacking into the system and redoing it everytime you guys come out with a new release.

Thanks
pete


#6

Hi Pete,

I’ll get that SQL to you tomorrow (A little too tired for advanced SQL tonight). I have to review it a bit and make sure all of it can be encompassed in a single SQL statement. One area which may be a problem is that there can be more than one reporting tag per request, but we can probably put them all together in a comma list or something like that. You’re using MySQL right?

Also in the next release of HS you’ll be able to export filters as xls files so that may work well for alot of your needs.

The brand thing sounds like an OK solution for now. Like I said, down the road they’ll be a better solution.


#7

Thanks Ian. Really appreciate the help. Yes we are using MySQL.

Once you’ve shown me the way with a couple queries – I should be able to figure out anything else I need to do with it - I don’t intend to make you my SQL query support guy :slight_smile:

Great product – and great support! Thanks again.


#8

Ian,

I did some digging on MySQL documentation and was able to get what I need out of the db. No need for you to spend time figuring out SQL queries for me – I’m sure you have better things to do – like working on support for multiple product brands :wink:

Pete


#9

Hey, thanks! It’s no problem at all. If you run into any issues or need any help definitely don’t hesitate to ask.


#10

Ian,

I wanted to check if my assumptions about the data format were correct. And I tried to duplicate the output from a default report. I’m getting close for some folks – but not others. I’m guessing my assumptions about data structure are wrong. Trying to duplicate “speed to resolution by staff” report output

Is the HS_Request.xPersonAssignedTo variable accurate throughout history of ticket – or is this just initial assigned person? If not the person that closes ticket – how do I determine who is the person who closes ticket. I can send an excel file with comparsion if you would like:

Here is my query:
SELECT CONCAT(HS_Person.sFname,’ ', HS_Person.sLname), AVG((HS_Request.dtGMTClosed-HS_Request.dtGMTOpened)/(3600*24)),COUNT(HS_Request.dtGMTClosed)
FROM HS_Request, HS_Person
WHERE HS_Request.fOpen=0
AND HS_Request.dtGMTClosed<=UNIX_TIMESTAMP(‘2006-02-26 00:00:00’)
AND HS_Request.dtGMTClosed>UNIX_TIMESTAMP(‘2006-02-19 00:00:00’)
AND HS_Request.xPersonAssignedTo=HS_Person.xPerson
GROUP BY HS_Person.xPerson

Thanks for your help.

Pete


#11

Would it be possible to send me the queries that generate your default reports – so I can backwards engineer the data structure – instead of making assumptions that I have to test?

Thanks
Pete


#12

The HS_Request.xPersonAssignedTo value changes throughout the life of the request. It always reflects who the current owner is. If a request is closed then the person in that field is the one who closed. The only exception is if you use batch closing. If you do then I don’t believe that value is updated since the request was never reassigned, it was simply closed by someone along with a bunch of other requests. If you batch close your own requests it’s still OK, but if a manger is closing a bunch for his staff then that wouldn’t be reflected. Though that would probably not be a good idea! HelpSpot in it’s reports treats that value as the closing person.

Here’s a trick for you on seeing the SQL. In the HS_Settings table there’s a variable, cHD_DEBUG. Set it to 1 and all the sql will be dumped in a table at the bottom of each page. I haven’t used this in a while because I have a different way of tracking that now, but it should work OK. It may do some odd things to the page layout. Also, I would recommend doing this when others aren’t in the system. It’s probably a good idea to take your mailboxes offline as well while you’re doing it, either by stopping the cron or deactivating them in Admin->Mailboxes since the debug mode isn’t usually used and certainly not tested with that running.