Skip to main content

Gamification App

Gamify your Celonis usage with the Gamification app to boost platform adoption and engagement through challenges, rewards, and leaderboards. The features include:

  • Gamified Personal Dashboard: Users can view their progress through total points collected over time, see their following assigned tasks and potential points, and track their level progression.

  • Leaderboard: Users can view their ranking on a leaderboard, fostering friendly competition and motivation by showing who is at the top and how to reach higher ranks.

  • Task Management & Point Allocation: The app allows for the assignment of both automated (e.g., logging in daily, using an app) and manual tasks (e.g., identifying a bottleneck, submitting improvement proposals), with points allocated for successful completion.

  • Achievement & Reward System: Users will receive points for completing milestones and tasks, providing a sense of accomplishment and encouraging continued engagement.

  • Gamification Admin Functionality: Admins can assign tasks to users or user groups, allocate points for manual task completion, and monitor team performance in terms of user adoption and platform engagement.

Prerequisites

Before implementing the Gamification app, it's essential to gather the following information: identify the target audience, review data privacy and tracking practices, and define the requirements for creating the AppKey. This will provide a clear understanding of how the app will be developed and how it will manage user data.

  • Scope Alignment: Define the target audience for the app. You need to decide if the app will be used globally or by specific teams/groups/users.

  • Data Privacy & Usage Tracking information:

    • The app tracks Login History, Apps Usage History, and Users & Groups Mapping to award points.

    • This data is accessible to admins via the Platform Adoption Dashboard and User Login History.

    • Enabling Apps & Studio usage history tracking is recommended.

    • Assure customers that no data is sent outside their environment; the app uses their own collected data.

  • AppKey Creation: Create a new Application Key with relevant team-level permissions.

Data Extraction

The first step before using Gamification is to set up the connection for data extraction through the Monitoring Platform Adoption connector so that you can extract the initial data set. The recommended connection method is OAuth 2.0.

Installing Monitoring Platform Adoption Connector

OAuth 2.0 is recommended. OAuth 2.0 can be installed by following the Installing the Platform Adoption Monitor App instructions.

Using AppKey

  1. It will be installed as a new data pool with the required extractor configuration & transformations.

  2. Navigate to the data pool and click on the connection “Platform Adoption”

    Gamification_Monitoring_Platform_Adoption_Connect_DataPool.png
  3. Update the connection configuration with the right team environment & AppKey created in Prerequisites.

    Gamification_Monitoring_Platform_Adoption_Connection.png
  4. Test the connection & ensure it is working.

Extract the initial data

  • Execute the data jobs “Member Details” & “Platform Adoption and Login History”.

    Important

    In case of errors, check the App Key permissions and try again.

Data Model Setup

  1. Data Pool Parameters: Create a new data pool parameter with placeholder GamificationStartDate as below and provide a default value.

    Gamification_DataModel_setup.png
  2. Data Transformation: Add a new GLOBAL data job named “Gamification”. (Ensure this is set to global only, as it is not recommended to write back directly in the data source scope. Follow the steps below to add transformations to the data job

    Name

    Description

    Transformation

    Create Tables : Points Allocation & Assignments

    PointsAllocation : Table to log user’s points.

    Assignments : Table to map challenges against users.

    Note: This should be disabled after first run

    DROP TABLE IF EXISTS PointsAllocation;
    CREATE TABLE IF NOT EXISTS PointsAllocation (
    ID IDENTITY(1,1),
    UserID varchar(45),
    "AllocatedOn" Timestamp,
    AllocatedPoints integer,
    AssignmentID integer,
    -- PackageKey varchar(100),
    Comment varchar(250),
    _CELONIS_CHANGE_DATE timestamp
    );
    
    
    DROP TABLE IF EXISTS Assignments;
    CREATE TABLE IF NOT EXISTS Assignments (
    ID IDENTITY(1,1),
    AssignmentName varchar(500),
    UserEmail varchar(100),
    Description varchar(1000),
    IsAutomated BOOLEAN,
    -- R = Recurring, O = Onetime
    Frequency varchar(100),
    ExpectedPoints integer,
    DueDate timestamp,
    PackageKey varchar(100),
    _CELONIS_CHANGE_DATE timestamp
    );
    
    
    
    
    -- Create a unique list of assignments created via automation
    CREATE OR REPLACE VIEW AssignmentList AS
    SELECT
    DISTINCT
    "Assignments"."AssignmentName",
    "Assignments"."IsAutomated",
    "Assignments"."Frequency",
    "Assignments"."Description",
    "Assignments"."ExpectedPoints",
    "Assignments"."DueDate",
    "Assignments"."PackageKey"
    FROM "Assignments";
    
    
    
    
    
    
    
    

    Create Default Challenges

    Creates default challenges for all users part of the team

    -- Default assignments will always be assigned to all users. Limit this by adding a where clause if required.
    
    
    
    
    INSERT INTO Assignments (AssignmentName,UserEmail,Description,Frequency,IsAutomated,ExpectedPoints,DueDate,PackageKey,_CELONIS_CHANGE_DATE)
    SELECT
    'Daily Login',
    "User_List"."email",
    'Points for daily logging in',
    'R',
    1,
    5,
    NULL,
    NULL,
    CURRENT_TIMESTAMP
    FROM <%=DATASOURCE:PLATFORM_ADOPTION%>."User_List"
    LEFT JOIN (SELECT UserEmail,"AssignmentName" FROM "Assignments" WHERE AssignmentName='Daily Login') a ON "a"."UserEmail" = "User_List"."email"
    WHERE "a"."UserEmail" IS NULL
    
    
    UNION ALL
    
    
    SELECT
    'Login Streak',
    "User_List"."email",
    'Logging in for 4 consecutive working days',
    'R',
    1,
    50,
    NULL,
    NULL,
    CURRENT_TIMESTAMP
    FROM <%=DATASOURCE:PLATFORM_ADOPTION%>."User_List"
    LEFT JOIN (SELECT UserEmail,"AssignmentName" FROM "Assignments" WHERE AssignmentName='Login Streak') a ON "a"."UserEmail" = "User_List"."email"
    WHERE "a"."UserEmail" IS NULL
    
    
    UNION ALL
    
    
    SELECT
    'Using Package',
    "User_List"."email",
    'Using packages you have access to at least once a month',
    'R',
    1,
    10,
    NULL,
    NULL,
    CURRENT_TIMESTAMP
    FROM <%=DATASOURCE:PLATFORM_ADOPTION%>."User_List"
    LEFT JOIN (SELECT UserEmail,"AssignmentName" FROM "Assignments" WHERE AssignmentName='Using Package') a ON "a"."UserEmail" = "User_List"."email"
    WHERE "a"."UserEmail" IS NULL
    
    
    UNION ALL
    
    
    SELECT
    'Share Insights',
    "User_List"."email",
    'What seems natural to you can be a real game changer for others. Share your perspective!',
    'R',
    1,
    150,
    NULL,
    NULL,
    CURRENT_TIMESTAMP
    FROM <%=DATASOURCE:PLATFORM_ADOPTION%>."User_List"
    LEFT JOIN (SELECT UserEmail,"AssignmentName" FROM "Assignments" WHERE AssignmentName='Share Insights') a ON "a"."UserEmail" = "User_List"."email"
    WHERE "a"."UserEmail" IS NULL
    
    
    
    
    
    

    Award Daily Points

    Transformations to award points based on Daily Login, Login Streak & Usage of Apps.

    Note : Add a new transformation parameter “GamificationStartDate” linked with the data pool parameter.

    ------------------------------- Mission : Daily login -------------------------------
    -- Check when was the last time they were awarded point for logging in.
    INSERT INTO "PointsAllocation" (UserID,AllocatedOn,AllocatedPoints,AssignmentID,Comment)
    WITH LastPointsAllocatedForLogin AS
    (select "PointsAllocation"."UserID" email,max("PointsAllocation"."AllocatedOn") LastAllocation
    from PointsAllocation
    where "PointsAllocation"."Comment" LIKE 'Logging in on %'
    group by "PointsAllocation"."UserID")
    -- Find what day they logged in & # of times per day. assign points as per last allocation & start date. 5 points per login. 40 points per day at max.
    (select lh.email,
    -- CURRENT_TIMESTAMP,
    CAST(lh."timestamp" AS DATE),
    LEAST(count(*) * 5.0,40.0) points_earned,
    Assignments.ID,
    CONCAT('Logging in on ',CAST(CAST(lh."timestamp" AS DATE) AS VARCHAR)) days_logged_in
    -- count(*) total_logins,
    from <%=DATASOURCE:PLATFORM_ADOPTION%>."login_history" lh
    left join LastPointsAllocatedForLogin using(email)
    left join "Assignments" on "Assignments"."UserEmail" = lh.email AND "Assignments"."AssignmentName" = 'Daily Login'
    where "lh"."authenticationEventType" LIKE '%LOGIN%'
    AND
    CAST(lh."timestamp" AS DATE) > CAST(COALESCE(LastAllocation,<%=GamificationStartDate%>) AS TIMESTAMP)
    AND Assignments.ID IS NOT NULL
    group by lh.email,CAST(lh."timestamp" AS DATE),Assignments.ID);
    
    
    -- select <%=GamificationStartDate%>
    
    
    ------------------------------- Mission : Use all boards once a month -------------------------------
    
    
    INSERT INTO "PointsAllocation" (UserID,AllocatedOn,AllocatedPoints,AssignmentID,Comment)
    WITH PointsAllocatedForLoginLastMonth AS
    (select "PointsAllocation"."UserID" email,"PointsAllocation"."Comment",max("PointsAllocation"."AllocatedOn") LastAllocation
    from PointsAllocation
    where "PointsAllocation"."Comment" LIKE 'Using Boards%'
    group by "PointsAllocation"."UserID","PointsAllocation"."Comment")
    select
    app_usage."user$email",
    -- CURRENT_TIMESTAMP,
    app_usage."timestamp",
    LEAST(count(distinct "asset$name") * 10.0, 100) points_earned,
    Assignments.ID,
    'Using ' || count(distinct "asset$name") || ' Board(s)' usages
    -- CAST(app_usage."timestamp" AS DATE) days_logged_in,
    -- count(distinct "asset$name") total_used,
    
    
    from <%=DATASOURCE:PLATFORM_ADOPTION%>."user_adoption_apps" app_usage
    left join PointsAllocatedForLoginLastMonth pa on (pa.email = app_usage."user$email")
    left join "Assignments" on "Assignments"."UserEmail" = pa.email AND "Assignments"."AssignmentName" = 'Using Package'
    where
    TIMESTAMPADD (MONTH, 1, (CAST(COALESCE(LastAllocation,<%=GamificationStartDate%>) AS TIMESTAMP))) = CAST(CURRENT_TIMESTAMP AS DATE)
    AND
    CAST(app_usage."timestamp" AS DATE) > CAST(COALESCE(LastAllocation,<%=GamificationStartDate%>) AS TIMESTAMP)
    AND Assignments.ID IS NOT NULL
    group by app_usage."user$email",app_usage."timestamp",Assignments.ID;
    
    
    -- 15 boards in total
    -- 100 points if used all of them - 10 per board, limit 100 pts max
    -- 10 per board anyway
    
    
    ------------------------------- Mission : Login Streak (4 WD) -------------------------------
    
    
    
    
    INSERT INTO "PointsAllocation" (UserID,AllocatedOn,AllocatedPoints,AssignmentID,Comment)
    -- Check when users were last awarded
    WITH PointsAllocatedForConsecutive AS
    (select "PointsAllocation"."UserID" as email,"PointsAllocation"."Comment",max("PointsAllocation"."AllocatedOn") LastAllocation
    from PointsAllocation
    where "PointsAllocation"."Comment" LIKE 'Login Streak%'
    group by "PointsAllocation"."UserID","PointsAllocation"."Comment"),
    LoginDays AS (
       SELECT DISTINCT lh.email as UserID, CAST("timestamp" AS DATE) AS LoginDate,
       DAYOFWEEK_ISO("timestamp") dow
       FROM <%=DATASOURCE:PLATFORM_ADOPTION%>."login_history" lh
       LEFT JOIN PointsAllocatedForConsecutive pa using(email)
       WHERE
       "lh"."authenticationEventType" LIKE '%LOGIN%' AND
       -- Only check for login streak if not awarded previously
       pa.LastAllocation IS NULL AND
       -- Filter to check for login streak since start date
       CAST("timestamp" AS DATE) > CAST(<%=GamificationStartDate%> AS TIMESTAMP)
       -- Filter for weekday logins
       AND DAYOFWEEK_ISO("timestamp") BETWEEN 1 AND 5
       order by CAST("timestamp" AS DATE) desc
    ),
    -- Check for last 3 logins for each row
    ConsecutiveWorkingDays AS (
     select
     *,
     LAG(LoginDate, 1) OVER
          (PARTITION BY UserID ORDER BY LoginDate) AS Last1LoginDate,
     LAG(LoginDate, 2) OVER
          (PARTITION BY UserID ORDER BY LoginDate) AS Last2LoginDate,
     LAG(LoginDate, 3) OVER
          (PARTITION BY UserID ORDER BY LoginDate) AS Last3LoginDate
     FROM LoginDays
    ),
    -- Check if the difference is 0d for last 4 logins
    FindDifferences AS (
    select
    UserID,
    LoginDate,
    CASE WHEN DATEDIFF(day,LoginDate,Last1LoginDate) = -3 AND DAYOFWEEK_ISO(LoginDate)=1 AND DAYOFWEEK_ISO(Last1LoginDate)=5 THEN -1
    ELSE DATEDIFF(day,LoginDate,Last1LoginDate) END diff1,
    Last1LoginDate,
    CASE WHEN DATEDIFF(day,Last1LoginDate,Last2LoginDate) = -3 AND DAYOFWEEK_ISO(Last1LoginDate)=1 AND DAYOFWEEK_ISO(Last2LoginDate)=5 THEN -1
    ELSE DATEDIFF(day,Last1LoginDate,Last2LoginDate) END diff2,
    Last2LoginDate,
    CASE WHEN DATEDIFF(day,Last2LoginDate,Last3LoginDate) = -3 AND DAYOFWEEK_ISO(Last2LoginDate)=1 AND DAYOFWEEK_ISO(Last3LoginDate)=5 THEN -1
    ELSE DATEDIFF(day,Last2LoginDate,Last3LoginDate) END diff3,
    Last3LoginDate
    from ConsecutiveWorkingDays
    order by 1,2
    )
    -- Award points
    select UserID,
    LoginDate ts,
    50 pts,
    "Assignments".ID assignment,
    'Login Streak maintained for 4 days between ' || CAST(CAST(Last3LoginDate AS DATE) AS VARCHAR) || ' and ' || CAST(CAST(LoginDate AS DATE) AS VARCHAR) comm
    from FindDifferences
    left join "Assignments" on "Assignments"."UserEmail" = UserID AND "Assignments"."AssignmentName" = 'Login Streak'
    where diff1+diff2+diff3 = -3
    AND Assignments.ID IS NOT NULL
    
    
    
    
    
    
    
    
  3. Data Model: Create a new data model with the relevant tables and joins as below

    Dimension Table

    PK

    FK

    Fact Table

    User_List

    ID

    ID

    Group_List

    User_List

    email

    EmailUser

    Assignments

    AssignmentList

    AssignmentName

    AssignmentName

    Assignments

    Assignments

    ID

    UserEmail

    AssignmentID

    UserID

    PointsAllocation

The end result should be as shown here (Ensure you set up an identifier for each table as perthe primary key mentioned in the above table):

Gamification_DataModel_example.png

Load Data Model

Perform the following steps:

  1. Run the transformation Create Tables : Points Allocation & Assignments ONLY

  2. Disable the transformation Create Tables : Points Allocation & Assignments

  3. Run the data job Gamification.

  4. Load the data model.

Install the App

Install or copy the package

  • Public Preview/GA : Installing from the Marketplace

Complete the Setup

  1. Assign the data model variable to the KM at the package level.

  2. Schedule the data job “Gamification” to run daily and initiate daily rewarding points.

Enable Action flows and Skills for automation

Note

As automations use Skills & Action flows, the OAuth-based App Keys are not supported. It is recommended to create a basic auth-based App Key for this.

  1. Provide package access to the Application Key..

  2. Provide write &amp; amp; read access to “Monitoring Platform Adoption” data pool.

  3. Update the Application Key in the following skills & save the skill.

    • Add Assignment

    • Allocate Points

  4. Update the Application Key in the following action flows &amp; save the action flows

    • Trigger Manual Allocation (Multiple)

    • Add new Assignment (Multiple)

    • Trigger Submission