SUPPORT.TWILIO.COM END OF LIFE NOTICE: This site,, is scheduled to go End of Life on February 27, 2024. All Twilio Support content has been migrated to, where you can continue to find helpful Support articles, API docs, and Twilio blog content, and escalate your issues to our Support team. We encourage you to update your bookmarks and begin using the new site today for all your Twilio Support needs.

Inner Joins: Why do some data disappear when slicing by foreign attributes?

As you might have noticed while building reports in Flex Insights, some data disappear when slicing a metric by an attribute from a different dataset. Records of that metric that are not associated with the primary key of the external dataset will not be shown in the sliced report.

A common example of this limitation is the Abandoned Conversations metric sliced by Agent Email attribute. Abandoned conversations that are not linked to a specific agent will not be shown if you slice the metric by agents' email addresses (or by any other attribute of the Agent dataset).

When a conversation is abandoned by the caller party, before it was picked up by any agent for the first segment, it will produce a conversation segment with no associated agents. This is visible as (empty value) in the Agent attribute.


The —empty value— row (abandoned conversations with no agent associated) in the screenshot above is shown because "agent" is a foreign key in the Conversations dataset. However, when adding or replacing the Agent attribute with Agent Email, this row and its metric value will disappear:


This is an effect of how INNER JOIN works. As the Flex Insights data model is currently designed, the first report works to show empty values because Agent is a foreign key in the Conversations dataset, so no joins are used. When you add Agent Email Address (or any other attribute from Agent dataset), the system performs an INNER JOIN between the datasets. Since the orphan conversations don't have an association with any agent, they are filtered out by the join.

You can see the data model in the Data tab of the Analytics Portal.


Unfortunately, there is not a way to show metric records sliced by external attributes when they are not associated with the external dataset. However, they can follow a different approach to have better visibility of these data:

Rollup aggregation


By right-clicking the metric header, you can add Sum and Rollup aggregations. Sum is an arithmetic summation of all rows in the report, while Rollup return a global metric value independent of the attribute slicing. In the example, Rollup will contain the abandoned conversations with agent empty value. So the difference between Sum and Rollup will indicate the missing row.

You can learn more about Sum vs. Rollup in Twilio Docs: Rollup Summary in Tables.

Drilling functions

Using a drilling action, you can easily switch between objects in Flex Insights. For instance, you can jump from a dashboard containing your Agent-only-based report and the orphan data, to another containing the additional attributes like email address, and the other way around.

Other drilling actions described in Drilling Functions in Flex Insights.

As a final alternative, you can choose to keep the two reports simultaneously visible in the same dashboard, or have them in different tabs of the same dashboard.



Have more questions? Submit a request
Powered by Zendesk