calcom
sync user data with helpscout, close and sendgrid
So, this is the plan:
- Export users table from PROD database to be imported into Sendgrid and Close.com
- I already contacted @zomars to get his green light to do this, yours is also appreciated @baileypumfleet
- HelpScout would be an exception on how the other services work, more in the next paragraphs.
- Work on a definitive solution to this need, abstracting CUD (no R) operations regarding users to be inserted in different parts of
app/webandapp/websiteto track down user data to better serve them for Cal.com operations
HelpScout
HS does not really have a way to get user data into the service, they figured out that if you want to show information about a user, you can do that by exposing a public API to where the user information is on our side to then fill in a sidebar component with that data, and for that on HelpScout you need to create a Dynamic Custom App. Such as the following image:
Luckily such an API can be correctly protected using signature validation, per HelpScout documentation:
Help Scout will generate a signature using the secret key you provided. The Help Scout-generated signature will be available in the headers as X-HELPSCOUT-SIGNATURE. You can calculate the same signature on your side, using the same secret key. If the signatures match, you know the request is from Help Scout and can proceed with providing data. If the signatures do not match, we recommend discarding the request.
Hi @leog for the purpose of Close, it would be great to understand the activity of the user. If the goal is to sell/retain them, I want to understand the following:
- Do they have an account? (Right now, I search for their email in Stripe)
- Are they on a team account? (I don't know how to get this information if I don't have their username to impersonate)
- When did they create an account? (Right now, I search for their email in Stripe)
- When was the last time they created a booking? (I don't know how to get this information if I don't have their username to impersonate)
- What features are they exactly using (to get a sense of how they're using it)? (I don't know how to get this information if I don't have their username to impersonate)
- What is their username?
I think bare-minimum, if we can get username and when last booking was, that would be good. The rest can deduced from their last booking and I can impersonate to get the other details.
Thanks @shirazdole, glad you could share your perspective on this, we need to shape this to best accommodate Ops needs.
In terms of activity of a user, we could see when was the last booking perhaps, that’s the main activity in the system, or perhaps, last time an event type was created/updated.
other than how we get that info, I’m also worried about how to export that in a way it can be imported by sendgrid and close
-
Do they have an account? (Right now, I search for their email in Stripe)
- Do you mean if they pay right now? Otherwise, if you can see them in our tools, they have an account.
- If you mean if they pay, the User Plan (FREE, TRIAL, PRO) indicates that, and I presume that will change in the near future to be FREE or TEAM, or just FREE, TRIAL, PRO as FREE and TEAM as PRO, whatever the case, we can track it down
-
Are they on a team account? (I don't know how to get this information if I don't have their username to impersonate)
- If I'm not wrong and I'm not missing anything, as mentioned, right now there is no TEAM account, but it will be and we need to figure out how we are going to flag that internally to be able to make it all the way to close, sendgrid and helpscout
-
When did they create an account? (Right now, I search for their email in Stripe)
- Do you mean when they started paying? I don't think we have that information, the Payment table only registers payments done through a booking if I'm not mistaken. We could start tracking this data putting code in stripe webhook to make it al the way to close, sendgrid and helpscout
- There's an added complexity regarding emails not matching cal.com account with stripe
-
When was the last time they created a booking? (I don't know how to get this information if I don't have their username to impersonate)
- We have this info available from the Booking table, and when a booking gets created, I can see to track it down to make it all the way to close, sendgrid and helpscout
-
What features are they exactly using (to get a sense of how they're using it)? (I don't know how to get this information if I don't have their username to impersonate)
- This is a tricky thing to track down, as there are many indicators, such as last time an event type was created/updated, bookings created, apps installed, apps uninstalled, routing forms, webhooks, and many more. What are the indicators you look at when impersonating? We can try to get this info when exporting from PROD database, and then see where in the apps to include a snippet to make the data all the way to close, sendgrid and helpscout
-
What is their username?
- We have this information and we can export it to make sure it gets to Close, Sendgrid and HelpScout, and also when signing up we can send it to those tools, the same way we need to track down username changes in the right spot in the app
I think bare-minimum, if we can get username and when last booking was, that would be good. The rest can deduced from their last booking and I can impersonate to get the other details.
Sure, we can export that info together with email and name, and see how we can import it to Close and Sendgrid, I think a simple CSV is OK, both tools provide a way to map columns to fields in their system if I'm not mistaken.
Once we define these details I can surely create an export from PROD database taking the info we need to create a CSV to import to Close and Sendgrid. That's the first step of the plan I mentioned. Afterwards, once I implement our tracking logic to accept Close, Sendgrid and HelpScout, all these should be taken care of automatically.
Also @PeerRich, if we want a shortcut to enable Sendgrid campaigns in the short-term, I can just export name and email until these details are defined to export the correct data and its type to be imported again by those tools.
Let's work off the notion that v2 has launched to simplify things:
- Last booking would be best, because it's not that often a user creates a new event type. Maybe it could be "did this user make a booking in the past two-weeks" therefore there aren't a million pieces of data being pushed daily to Close?
- I may be missing something, but what we need in Close doesn't necessarily need to be in SendGrid?
- I didn't realize there's no TEAM tag in the database, it would be great to create that. We don't need to know exactly what TEAM they are on, just that they are on one. (I.e. Custom Tag <Team> Yes/No
- It would be great to know if they are a FREE or TEAM user in Close.
- Don't worry about features, too complicated.
- Username + Last Booking + If they are a FREE or TEAM user or not would be solid
SendGrid: Is this only being used for Newsletters? Are we still planning to do marketing emails (drip campaigns)?
For example, a TEAM is created, they add their first user, they then get an email from us explaining xyz use cases of how to use the TEAM plan.
Update: Now that Close.com app is finished (PR pending here: #3709) I have a pretty good idea on how to handle it for our Sync Services to handle the requirements of this issue. I'm working implementing what's needed in #3814 which will be expanded to work with HelpScout and Sendgrid.