User Stats Weekly

PostedApril 11, 2024

UpdatedApril 11, 2024

This scenario is a is a weekly report that displays stats regarding the users calls, meetings and messages.

Example

Top section: number of each type in the period (weekly or months), and the percentage is change since last time period – green for increase, red (with a – in front) for decrease.

Lower section:

P2P calls %

Value is CallsVsMeetingsPercentageChange, calculated as:

PL/SQLPL/SQL

round(CallsVsMeetingsPercentage - CallsVsMeetingsPercentageLastPeriod) * 100, 2) as CallsVsMeetingsPercentageChange

CallsVsMeetingsPercentage is

PL/SQLPL/SQL

(CallsThisPeriod as float) / (CallsThisPeriod + MeetingsThisPeriod)

Meetings %

Value is MeetingsVsCallsPercentageChange, calculated as:

PL/SQLPL/SQL

round(MeetingsVsCallsPercentage * 100, 2) as MeetingsVsCallsPercentage

    ,round((MeetingsVsCallsPercentage - MeetingsVsCallsPercentageLastPeriod) * 100, 2) as 

    MeetingsVsCallsPercentageChange

P2P vs Meetings

Takes CallsVsMeetingPercentage, MeetingsVsCallsPercentage and shows proportion of calls vs meetings

Chart is done in https://quickchart.io

Required Settings

Setting	Value
StoredProcedureName	automation.SpGetUserStatsForWeek
IsSentOnce	False

SQL Query

TextText

CREATE PROCEDURE [automation].[SpGetUserStatsForWeek]
AS
BEGIN
    EXEC automation.SpGetUserActivityStatsBase 7
END
GO

Example Data Model

TextText

{
  "RowsWithAdditionalProperties": [
    {
      "Value": {
        "UserFirstName": "Oliver",
        "CallCount": 25,
        "CallsLastPeriod": 21,
        "CallCountPercentageChange": 19.05,
        "MeetingCount": 14,
        "MeetingsLastPeriod": 19,
        "MeetingCountPercentageChange": -26.32,
        "TeamChatCount": 36,
        "TeamChatMessagesLastPeriod": 43,
        "TeamChatCountPercentageChange": -16.28,
        "PrivateChatCount": 148,
        "PrivateChatMessageCountsLastPeriod": 215,
        "PrivateChatCountPercentageChange": -31.16,
        "CallsVsMeetingsPercentage": 64.1,
        "CallsVsMeetingsPercentageChange": 11.6,
        "MeetingsVsCallsPercentage": 35.9,
        "MeetingsVsCallsPercentageChange": -11.6
      }
    }
  ]
}

Example Card Template

Basic format to be customised

TextText

{
  "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
  "version": "1.2",
  "type": "AdaptiveCard",
  "originator": "OriginatorIdPlaceholder",
  "body": [
    {
      "type": "Container",
      "backgroundImage": {
        "url": "https://cdn.modalitysystems.com/TeamworkAnalytics/Automation/Backgrounds/header_lightblue.png"
      },
      "items": [
        {
          "type": "ColumnSet",
          "columns": [
            {
              "type": "Column",
              "items": [
                {
                  "type": "Image",
                  "url": "https://cdn.modalitysystems.com/Icons/ic_fluent_people_team_filled.png",
                  "size": "small"
                }
              ],
              "width": 30
            },
            {
              "type": "Column",
              "items": [
                {
                  "type": "TextBlock",
                  "text": "User Activity",
                  "horizontalAlignment": "right",
                  "wrap": true,
                  "color": "dark"
                },
                {
                  "type": "TextBlock",
                  "text": "WEEKLY REPORT",
                  "horizontalAlignment": "Right",
                  "spacing": "None",
                  "size": "Large",
                  "color": "Attention",
                  "wrap": true,
                  "weight": "Bolder"
                }
              ],
              "width": 70
            }
          ]
        }
      ]
    },
    {
      "type": "TextBlock",
      "text": "${RowsWithAdditionalProperties[0].Value.UserFirstName}, here's your Teams report:",
      "wrap": true,
      "size": "Large",
      "color": "Accent"
    },
    {
      "type": "ColumnSet",
      "separator": true,
      "spacing": "ExtraLarge",
      "columns": [
        {
          "type": "Column",
          "items": [
            {
              "type": "TextBlock",
              "text": "P2P Calls",
              "spacing": "Small",
              "size": "Medium"
            },
            {
              "type": "TextBlock",
              "text": "Meetings",
              "spacing": "Small",
              "size": "Medium"
            },
            {
              "type": "TextBlock",
              "text": "Channel Messages",
              "spacing": "Small",
              "size": "Medium"
            },
            {
              "type": "TextBlock",
              "text": "Chat Messages",
              "spacing": "Small",
              "size": "Medium"
            }
          ],
          "width": "stretch",
          "spacing": "Medium"
        },
        {
          "type": "Column",
          "items": [
            {
              "type": "TextBlock",
              "text": " ${RowsWithAdditionalProperties[0].Value.CallCount}",
              "size": "Medium"
            },
            {
              "type": "TextBlock",
              "text": " ${RowsWithAdditionalProperties[0].Value.MeetingCount}",
              "spacing": "Small",
              "size": "Medium"
            },
            {
              "type": "TextBlock",
              "text": " ${RowsWithAdditionalProperties[0].Value.TeamChatCount}",
              "size": "Medium",
              "spacing": "Small"
            },
            {
              "type": "TextBlock",
              "text": " ${RowsWithAdditionalProperties[0].Value.PrivateChatCount}",
              "spacing": "Small",
              "size": "Medium"
            }
          ],
          "width": "auto"
        },
        {
          "type": "Column",
          "width": "auto",
          "items": [
            {
              "type": "TextBlock",
              "text": "${if(RowsWithAdditionalProperties[0].Value.CallCountPercentageChange == 0, '\\-', if(RowsWithAdditionalProperties[0].Value.CallCountPercentageChange >= 1000, '1000+%', RowsWithAdditionalProperties[0].Value.CallCountPercentageChange + '%'))}",
              "color": "${if(RowsWithAdditionalProperties[0].Value.CallCountPercentageChange == 0, 'default', if(RowsWithAdditionalProperties[0].Value.CallCountPercentageChange > 0, 'good', 'attention'))}",
              "spacing": "Small",
              "size": "Medium"
            },
            {
              "type": "TextBlock",
              "text": "${if(RowsWithAdditionalProperties[0].Value.MeetingCountPercentageChange == 0, '\\-', if(RowsWithAdditionalProperties[0].Value.MeetingCountPercentageChange >= 1000, '1000+%', RowsWithAdditionalProperties[0].Value.MeetingCountPercentageChange + '%'))}",
              "color": "${if(RowsWithAdditionalProperties[0].Value.MeetingCountPercentageChange == 0, 'default', if(RowsWithAdditionalProperties[0].Value.MeetingCountPercentageChange > 0, 'good', 'attention'))}",
              "spacing": "Small",
              "size": "Medium"
            },
            {
              "type": "TextBlock",
              "text": "${if(RowsWithAdditionalProperties[0].Value.TeamChatCountPercentageChange == 0, '\\-', if(RowsWithAdditionalProperties[0].Value.TeamChatCountPercentageChange >= 1000, '1000+%', RowsWithAdditionalProperties[0].Value.TeamChatCountPercentageChange + '%'))}",
              "color": "${if(RowsWithAdditionalProperties[0].Value.TeamChatCountPercentageChange == 0, 'default', if(RowsWithAdditionalProperties[0].Value.TeamChatCountPercentageChange > 0, 'good', 'attention'))}",
              "spacing": "Small",
              "size": "Medium"
            },
            {
              "type": "TextBlock",
              "text": "${if(RowsWithAdditionalProperties[0].Value.PrivateChatCountPercentageChange == 0, '\\-', if(RowsWithAdditionalProperties[0].Value.PrivateChatCountPercentageChange >= 1000, '1000+%', RowsWithAdditionalProperties[0].Value.PrivateChatCountPercentageChange + '%'))}",
              "color": "${if(RowsWithAdditionalProperties[0].Value.PrivateChatCountPercentageChange == 0, 'default', if(RowsWithAdditionalProperties[0].Value.PrivateChatCountPercentageChange > 0, 'good', 'attention'))}",
              "spacing": "Small",
              "size": "Medium"
            }
          ]
        }
      ]
    },
    {
      "type": "TextBlock",
      "text": "How are people talking to each other?",
      "wrap": true,
      "size": "Large",
      "color": "Accent",
      "spacing": "ExtraLarge"
    },
    {
      "type": "ColumnSet",
      "separator": true,
      "columns": [
        {
          "type": "Column",
          "width": "auto",
          "items": [
            {
              "type": "TextBlock",
              "text": "${if(RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentageChange == 0, '\\-', if(RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentageChange >= 1000, '1000+%', if(RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentageChange >= 0, RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentageChange + '%', RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentageChange + '%')))}",
              "horizontalAlignment": "Center",
              "wrap": true,
              "size": "ExtraLarge",
              "color": "${if(RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentageChange == 0, 'default', if(RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentageChange > 0, 'good', 'attention'))}"
            },
            {
              "type": "TextBlock",
              "text": "P2P Calls",
              "horizontalAlignment": "Center",
              "weight": "Bolder",
              "wrap": true,
              "spacing": "Small"
            }
          ]
        },
        {
          "type": "Column",
          "width": "stretch",
          "items": [
            {
              "type": "TextBlock",
              "text": "\r\n",
              "horizontalAlignment": "Center",
              "weight": "Bolder",
              "wrap": true,
              "spacing": "Small"
            },
            {
              "type": "Image",
              "horizontalAlignment": "Center",
              "wrap": true,
              "spacing": "None",
              "url": "https://quickchart.io/chart?c=%7B%0A%20%20%22type%22%3A%20%22doughnut%22%2C%0A%20%20%22data%22%3A%20%7B%0A%20%20%20%20%22datasets%22%3A%20%5B%7B%0A%20%20%20%20%20%20%22label%22%3A%20%22foo%22%2C%0A%20%20%20%20%20%20%22data%22%3A%20%5B${RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentage}%2C%20${RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentage}%5D%2C%0A%20%20%20%20%20%20%22backgroundColor%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%22rgba(255%2C%2099%2C%20200%2C%200.2)%22%2C%0A%20%20%20%20%20%20%20%20%22rgba(0%2C%200%2C%200%2C%200.1)%22%0A%20%20%20%20%20%20%5D%2C%0A%20%20%20%20%20%20%22textcolor%22%3A%5B%22%23000555%22%2C%22%23555555%22%5D%2C%0A%20%20%20%20%20%20%22borderWidth%22%3A%200%2C%0A%20%20%20%20%7D%5D%20%0A%20%20%7D%2C%0A%20%20%22options%22%3A%20%7B%0A%20%20%20%20%22rotation%22%3A%20Math.PI%2C%0A%20%20%20%20%22circumference%22%3A%20Math.PI%2C%0A%20%20%20%20%22cutoutPercentage%22%3A%2075%2C%0A%20%20%20%20%22plugins%22%3A%20%7B%0A%20%20%20%20%20%20%22datalabels%22%3A%20%7B%20%22display%22%3A%20false%20%7D%2C%0A%20%20%20%20%20%20%22doughnutlabel%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%22labels%22%3A%20%5B%0A%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22text%22%3A%20%22%5CnP2P%20vs%20Meetings%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22color%22%3A%20%22%23aaa%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22font%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22size%22%3A%20%2225%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22text%22%3A%20%22%5Cn${RowsWithAdditionalProperties[0].Value.CallsVsMeetingsPercentage}%25%20-%20${RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentage}%25%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22font%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22size%22%3A%20%2240%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%7D%2C%0A%20%20%20%20%20%20%20%20%5D%0A%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%09%09%0A%20%20%7D%0A%7D"
            }
          ]
        },
        {
          "type": "Column",
          "width": "auto",
          "items": [
            {
              "type": "TextBlock",
              "text": "${if(RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentageChange == 0, '\\-', if(RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentageChange >= 1000, '1000+%', if(RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentageChange >= 0, RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentageChange + '%', RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentageChange + '%')))}",
              "horizontalAlignment": "Center",
              "wrap": true,
              "size": "ExtraLarge",
              "color": "${if(RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentageChange == 0, 'default', if(RowsWithAdditionalProperties[0].Value.MeetingsVsCallsPercentageChange > 0, 'good', 'attention'))}"
            },
            {
              "type": "TextBlock",
              "text": "Meetings",
              "horizontalAlignment": "Center",
              "weight": "Bolder",
              "wrap": true,
              "spacing": "Small"
            }
          ]
        }
      ]
    }
  ]
}

Teamwork Analytics

Automation

Adoption Automation

Governance Automation

Calls and Meetings Automation

Adaptive Card Design

Customer Scenarios

Welcome / Invalid

Alerting

Power BI Reports

Teams Usage

Teams Insight

Teams Collaboration

Teams Governance

Calls and Meetings: Failures

Calls and Meetings: Devices

Calls and Meetings: Usage

Calls and Meetings: Call Logger

Calls and Meetings: Performance

Setup

Power BI Setup

Advanced

Architecture

FAQ

Teams Audit

Teams Chat Assist

OneConsultation

User Stats Weekly

Example

Required Settings

SQL Query

Example Data Model

Example Card Template