Hi @Jacquelyne
I think you’ll need a data design before you can create a report.
If I’m understanding this correctly, all activities are rows in one table. If you want to count the difference between the question and the first answer, you’re going to have to first, create a data set which will contain the following (minimum requirement):
- Thread name
- Thread ID (ideally, you should have an identifier)
- Activity date
- Filtered on activity type = Asked a Question
And a second data set that will contain at least the following:
- Thread name
- Thread ID
- Activity date
- Filtered on activity type = Answered a question
You will then transform that data set to identify the “MIN of ACTIVITY DATE” (group by thread name/thread ID) to identify the first answer for that thread.
You would then merge your first data set with your second data set based on thread ID and create an additional transform task to add a formula field that will calculate the number of days it took for this activity to get an answer.
Maybe @anirbandutta can tell us if and how they do this type of stuff internally?
Data designer pretty much works like rules engine, but you can check out the following if you’re not sure: Data Designer Overview - Gainsight Inc.
Hope this helps!
inSided (our Community platform) makes my life easy by offering this Time to 1st response KPI on it’s Analytics dashboard and we also get a log of the daily user activities through the SFDC connector that we can build a report in Gainsight.
Lemme add a couple of knowledgeable folks to look at the exact solution you are looking to build.
@seth, @Revant_Amingad, @sdoty
I agree with @alizee’s proposal. You can’t get to this data without enriching it in some fashion, either with a rule that updates additional data in an MDA object or a data designer. If you don’t have a numeric thread ID (but you do hopefully prevent people from posting the exact same thread title) then you could add a new column to the data where you try to populate that identifier on all rows.
You might do that using a string formula in Rules Engine to evaluate rows where the column is null and the type is Asked a Question, and then trim out just the values after “ - “ as the thread identifier, otherwise just the thread title if the type is Answered a Question. You can again as @alizee proposed take the min datetime but instead put that in a second new column as Original Post Date and then build a formula to count the time interval between each line and the original post, and discard or not set that value when it equals zero.
The advantage of using a rule is that you would make small incremental adjustments to very new data and otherwise have a static set that doesn’t expire or go out of date, but you would have to scrap or rebuild this process if for example your thread titles were not actually unique, or you needed to populate additional metrics for historical data. You could make those changes in the Data Designer more easily.
My suggestion also does not follow the best practice of having a numeric or alphanumeric identifier that is distinct from the title, which is indeed something worth the effort to correct if it’s not part of the data you are ingesting.
I would evaluate each of the solutions and consider what will fall into your skillset/experience and the available data as you will get pretty similar results.
