Power BI tables and columns

The timetoreply OData feed at https://portal.timetoreply.com/odata/v1/ has three kinds of table:

• Lookup tables: your mailboxes, teams and group mailboxes, and how they connect.
• Report tables: the SLA, Overview and awaiting-response reports, calculated when Power BI asks for them.
• Log tables: the conversation log and the message log, one row per conversation or message.

To connect, see How do I connect Power BI to timetoreply?

What isn’t in the feed

• Email content. There are no message bodies, Smart summaries or conversation summaries, and none of the Smart columns (sentiment, urgency, intent).
• Changes. The feed is read-only. You can’t create or change anything through it.
• Months. The report tables are weekly, Monday to Sunday.
• History of team membership. Today’s team members are used for every past week.

Who sees which tables

The feed shows what the token owner can see in timetoreply.

Table	Appears if you can open
Lookup tables	Always
SlaReport, SlaReportMailboxes, AwaitingResponseReport, AwaitingResponseReportMailboxes	The SLA report
OverviewReport, OverviewReportMailboxes	The Overview report
Conversations	The conversation log
Messages	The message log

Rows are limited in the same way. Admins and Company Managers see the whole company. Restricted Managers see the teams, group mailboxes and mailboxes they’ve been given.

If you get access to a new table after connecting, Power BI won’t offer it until you connect again with Get data.

Lookup tables

Use these to give the report tables names and to group mailboxes into teams. Relate them on the ID columns.

Table	One row per	Columns
Mailboxes	mailbox	MailboxId, Name, PrimaryEmailAddress
Teams	team	TeamId, Name
GroupMailboxes	group or shared mailbox	GroupMailboxId, Name, PrimaryEmailAddress
TeamMembers	mailbox in a team	Id, TeamId, MailboxId
GroupMailboxMembers	mailbox in a group mailbox	Id, GroupMailboxId, MailboxId
MailboxAddresses	email address of a mailbox, primary or alias	EmailAddress, MailboxId, IsPrimary
GroupMailboxAddresses	email address of a group mailbox	EmailAddress, GroupMailboxId, IsPrimary

• Email addresses are lower case.
• A mailbox can be in several teams and have several addresses, so relate TeamMembers and MailboxAddresses to Mailboxes as many to one.
• TeamMembers and GroupMailboxMembers show current membership only. They have no dates.
• A mailbox that’s still being connected doesn’t appear until it’s connected. A disabled mailbox still appears, because its past email is still in the reports. A removed mailbox disappears.
• You can filter and sort on every column. Text filters are case-sensitive: use Text.Lower in Power Query to match any case.

Report tables

Each report comes in two tables:

• Summary (SlaReport, OverviewReport, AwaitingResponseReport): one row per week, per report-for party (a team, say) and per communicating-with party (External, say). Use these for team and company totals.
• Per address (…Mailboxes): the same, split by email address. A mailbox with an alias has a row for each address. There’s only a row for an address that had activity in the week.

Caution

Don’t add up per-address rows to get a team total. One email can reach several addresses. See Reading timetoreply numbers in Power BI.

Columns every report table has

Column	What it holds
Id	A key for the row. It stays the same from one refresh to the next.
PeriodType	Always Week.
PeriodStart, PeriodEnd	The Monday and Sunday of the week, in TimeZone.
ReportForType, ReportForId	Who the report is for, such as Team and a TeamId. The ID is empty for MyCompany.
CommunicatingWithType, CommunicatingWithId	Who they’re communicating with, such as EverybodyElse.
TimeZone	The time zone the week was calculated in.
EvaluatedAt	When timetoreply calculated the row, in UTC.
ConfigurationId	Identifies the goals and options used.
FirstReplyTimeGoalSeconds, OverallReplyTimeGoalSeconds	The goals used, in seconds. Not on the awaiting-response tables.
NoReplyForSeconds	The “No reply for” setting used, in seconds.

The per-address tables also have EmailAddress, plus MailboxId or GroupMailboxId for the mailbox or group mailbox that address belongs to. Both are empty for the address of a mailbox that’s been removed.

SLA columns

SlaReport and SlaReportMailboxes, matching the SLA report:

Columns	Counts
EmailsReceived, EmailsSent	Emails
FirstReplyTimeWithinSla, FirstReplyTimeSlaBreach, FirstReplyTimeExcludedFromSla, FirstReplyTimeSlaEligible	First replies
FirstReplyTimeWithinSlaPercentage, FirstReplyTimeSlaBreachPercentage	Percent of eligible first replies
OverallReplyTimeWithinSla, OverallReplyTimeSlaBreach, OverallReplyTimeExcludedFromSla, OverallReplyTimeSlaEligible	Replies
OverallReplyTimeWithinSlaPercentage, OverallReplyTimeSlaBreachPercentage	Percent of eligible replies
ConversationsAwaitingMailboxResponse	Conversations waiting for your reply now

• Eligible is within SLA plus breaches. Replies excluded from the SLA aren’t in it.
• Percentages are 0 to 100, with two decimal places. They’re empty, not zero, when nothing was eligible.
• Reply times use business hours, as on the SLA report.

Overview columns

OverviewReport and OverviewReportMailboxes, matching the Overview report’s Total Conversations Breakdown:

Columns	Counts
EmailsReceived, EmailsSent	Emails
Conversations	Conversations active in the week
ConversationsInbound, ConversationsOutbound, ConversationsInternal	Conversations by type
ConversationsAwaitingMailboxResponse, ConversationsAwaitingContactResponse, ConversationsSentInternally, ConversationsClosed	Conversations by status now

The Overview tables don’t repeat the SLA columns. For the same selection, EmailsReceived, EmailsSent and ConversationsAwaitingMailboxResponse are the same in both reports.

Awaiting-response columns

AwaitingResponseReport and AwaitingResponseReportMailboxes count conversations waiting for your reply for longer than a threshold. Each row is one threshold, so you can get several thresholds at once.

Column	What it holds
NoReplyForSeconds	The threshold, in seconds: 14400 is 4 hours.
ConversationsAwaitingMailboxResponse	Conversations whose newest email arrived in the week, came from the communicating-with party, is older than the threshold, and is still waiting for your reply.

This matches the SLA report with its No reply for filter set to the threshold, except for a known week-end boundary difference described below. The summary table has a row for every week, party and threshold, with zero when nothing is waiting. The per-address table only has rows for addresses with something waiting.

A known boundary difference can affect reconciliation: the awaiting-response tables include the final second of the report week, while the SLA calculation excludes it. Weeks spanning a Sunday daylight-saving change also use the report engine’s week-end calculation, which can differ from local midnight. If a count differs at a week boundary, include the week and time zone when contacting support.

Choosing what to calculate

Filter these columns in Power Query to choose what timetoreply calculates. Without them, the summary tables return the last completed week, communicating with Anybody, using your company’s goals and the token owner’s time zone. Admins and Company Managers get one MyCompany row; Restricted Managers get one row per visible team. Per-address tables split those selections by address.

Column	Values	Filters
PeriodStart, PeriodEnd	A Monday and a Sunday, as dates	equals, is in, before, after
ReportForType	MyCompany, Team, Mailbox, GroupMailbox	equals, is in
ReportForId	A TeamId, MailboxId or GroupMailboxId	equals, is in
CommunicatingWithType	Anybody, EverybodyElse (External), MyDomains (Internal), MyCompany	equals, is in
NoReplyForSeconds (awaiting tables)	Whole minutes in seconds, above zero and up to 366 days	equals, is in

• Ticking several values in one column sends an or, which works. Asking for one column to equal two values with and selects nothing.
• ReportForType equals Team with no ReportForId gives every team you can see. The same goes for Mailbox and GroupMailbox.
• ReportForId only works with a single ReportForType.
• A range needs a start (PeriodStart on or after a Monday). Without an end, it runs to the last completed week.
• On the awaiting tables, if you don’t choose a threshold, it’s your company’s first-reply goal rounded down to whole minutes. If that is under a minute, you must choose a threshold.
• You can filter and sort the result on any other column, but those filters don’t change what’s calculated.

Report options

Some report settings aren’t columns. Add them to the table’s Source step with the Query option of OData.Feed, not to the address:

Source = OData.Feed("https://portal.timetoreply.com/odata/v1/", null,
    [Implementation = "2.0", Query = [timeZone = "Europe/London", excludeCcRecipients = "true"]])

Only add options to the queries for report or log tables. A lookup table refuses options it doesn’t know.

Option	Does	Written as
firstReplyTimeGoal, overallReplyTimeGoal	Replace your company’s SLA goals. Not on the awaiting tables.	HH:MM:SS, up to 72:00:00
timeZone	Calculate weeks in another time zone	An IANA name, such as Europe/London
excludeCcRecipients	Count To recipients only	true or false
onlyBetweenTheseParties	Only conversations between the two parties. Needs MyDomains or MyCompany as the communicating-with party	true or false
label, labelMatch	Only conversations with these labels, matching all (And) or any (Or)	label=Sales,Support, labelMatch=Or
conversationStatus, conversationType	Only these conversation statuses or types	Comma-separated values
messageClassification, messageType	Only these message classes or types	Comma-separated values
noReplyFor	The No reply for filter. Narrows every count in the row. Not on the awaiting tables	HH:MM:SS, whole minutes

Rows show the time zone and threshold used; SLA and Overview rows also show the goals. Saved views can’t be used yet.

Log tables

Conversations and Messages list the same rows as the conversation log and the message log.

Table	One row per	Columns
Conversations	conversation with an email in the window	ConversationId, Subject, From, To, Cc, Labels, Type, Status, FirstReplyTimeSeconds, FirstReplyTimeNoBusinessHoursSeconds, TimeToCloseSeconds, TimeToCloseNoBusinessHoursSeconds, MessageCount, LastReceivedAt
Messages	email sent in the window	MessageId, ConversationId, Subject, From, To, Cc, Labels, ConversationMessageCount, Type, Classification, ReplyTimeSeconds, ReplyTimeNoBusinessHoursSeconds, SentAt

Both also repeat the window, time zone and parties on every row (PeriodStart, PeriodEnd, TimeZone, ReportForType, ReportForId, CommunicatingWithType, CommunicatingWithId).

• Times are in UTC. LastReceivedAt and SentAt aren’t converted to your time zone. See Dates, times and time zones.
• Durations are in seconds. The …NoBusinessHours… columns ignore business hours. The others use them. They’re empty until there’s a reply, or until the conversation closes.
• From, To, Cc and Labels are text separated by , . On Conversations, the addresses are the first email’s and the labels belong to the conversation. Labels containing , cannot be reliably split back into a list.
• ConversationId relates Messages to Conversations.

Choosing the window and parties

Filter	Does
PeriodStart on or after a day, PeriodEnd on or before a day	The window. Any day works, including today. Up to a year.
ReportForType, ReportForId, CommunicatingWithType	Who the log is for, as on the report tables. Each must come to one party.
Status (Conversations), Type (both)	Equals, or is one of, the log’s own status and type values

Without a window, you get the last seven days and today, as the log opens. A Restricted Manager with several teams must choose one with ReportForId.

The log tables also accept the report options above, except the goals, with one difference each:

• Conversations doesn’t accept conversationStatus or conversationType. Filter its Status and Type columns instead.
• Messages doesn’t accept messageType. Filter its Type column instead.

You can’t filter on the other columns in Power Query, because the log’s own filters work differently. timetoreply refuses those filters, and Power BI can show the result as an empty table. To narrow by subject, address or label, filter in your report’s visuals instead.

Values and sorting

Use these exact values in log filters and report options:

Column or option	Values
Conversation Status / conversationStatus	SentInternally, AwaitingContactResponse, AwaitingMailboxResponse, Closed
Type / conversationType / messageType	Inbound, Outbound, Internal
Classification / messageClassification	First, Reply, ReplyAll, Forward, FollowUp

Rows can also show Calculating, and message Type can show Other. Those two values cannot be selected through report options. labelMatch needs a label; names containing commas cannot be selected through that option.

Log tables accept one sort column at a time:

• Conversations: ConversationId, LastReceivedAt, FirstReplyTimeSeconds, FirstReplyTimeNoBusinessHoursSeconds, TimeToCloseSeconds, MessageCount.
• Messages: MessageId, SentAt, ReplyTimeSeconds, ReplyTimeNoBusinessHoursSeconds.

Without a sort step, rows are in key order, rather than newest first. Other log sort columns are refused if sent to the feed.

Paging

Power BI fetches the log tables in pages of 200 rows and follows the pages automatically. A table can be paged up to its first 500,000 rows. For a long window on a busy account, use a shorter window or split the parties.

Pages are fetched live, so a conversation that changes during a refresh can move between pages. Refresh at a quiet time if you need an exact list.

Limits

Limit	Value
Report weeks	Completed Monday-to-Sunday weeks only, up to 53 in one table
Report combinations	Up to 500 per table: weeks × report-for parties × communicating-with parties, and × thresholds on the awaiting tables
Awaiting thresholds	Up to 10 per table
Log window	Up to a year
Log depth	The first 500,000 rows
Requests	300 a minute per token owner, across all reports that use their tokens

Report selections and log windows over their limits are refused, never cut short. Log paging beyond the first 500,000 rows is refused, so narrow the window if a refresh reaches that depth. Power BI can display a refused selection as an empty table. One Power BI refresh makes several requests for each table, plus one per page.

Need help or have feedback?

Email us at [email protected]. We’re happy to help.