Extracting Genesis Statistics

Before we get started, here's what's happening at Call Theory this week:

Office Hours

A weekly meeting with Call Theory and fellow customers to learn, ask questions, and network over general IT, call center, and Amtelco topics.

• Tuesday, 10/1 @ 3pm Eastern
• Zoom Link and additional information
• Fallback Topics - Billing stats/formulas (expect this for a while)

Scripting Sessions

A weekly meeting to train Amtelco Intelligent Series scripting topics from Basic, Intermediate, and Advanced topics on a repeating schedule.

• Thursday, 10/3 @ 12pm Eastern
• Zoom Link and additional information
• Training Topics - Basic III: Advanced Expressions

Initial Commit

Well, we've published v0.0.1 (and v0.0.2) of Call Theory Billing, available on Github. There are no tagged releases yet; we are still very early.

GitHub - CallTheory/billing: Call Theory Billing

Call Theory Billing. Contribute to CallTheory/billing development by creating an account on GitHub.

GitHubCallTheory

While it's obviously not complete, we were able to identity and outline what is essentially "Phase 1" for this project: and what a successfully executed outcome looks like. So, let's dig into what we've identified.

If you'd like to help shape these conversations, join us at our weekly Office Hours meetings

"Phase 1" consists of a drop-in replacement for the existing Amtelco Crystal Report called Billing Export 200.

We're starting with an API which will do 3 things:

1. Amtelco Support - Connect to the Intelligent Series (IS) database directly.
2. Statistic Modifiers - Apply modifications to any statistic, for any attribute.
3. Billing Export - Generate the resulting Amtelco Billing Export 200 compatible report.

Reaching this point, anyone who utilizes the existing Billing Export 200 report will be able to take advantage of Call Theory Billing to apply statistic modifiers before importing into their invoicing software.

Looking ahead a little further and speculating, "Phase 2" will likely be the administrative interface used to control everything. "Phase 3" will probably be integrations with Stripe, followed by Quickbooks/Quickbooks Online and Oodoo Invoicing.

When we complete "Phase 1", we'll re-evaluate what "Phase 2" looks like (after another round of customer feedback, of course)

How to extract formulas

The idea is that for each of the ~265 statistics included in the Billing Export 200 report, we'll need to:

1. Figure out the SQL query for that specific statistic, so we can model it.
2. Identify each of the attributes for that statistic, so we can create relevant modifiers.
3. Implement the modifiers against the models we've created.

This might not make a lot of sense as explained, so let's try to look at an actual example of what this looks like, using the ClientSetups statistic from the Billing Export 200 report.

Step 1: Figure out the SQL

If you have a licensed Crystal Report installation that allows editing of Crystal Report .rpt files, you can export the Billing Export 200 report from Intelligent Series Supervisor's Reporting section.

If you don't have access to the report SQL, you can view the latest copy of it here:

billing/resources/amtelco/BillingExport_200.sql at main · CallTheory/billing

Call Theory Billing. Contribute to CallTheory/billing development by creating an account on GitHub.

GitHubCallTheory

Call Theory customers can access and search every Amtelco Crystal Report SQL query directly on the Call Theory portal: learn.calltheory.com/library/crystal-reports/

Reviewing the T-SQL from the Crystal Report, we see the following on lines 375-387 - also notice the LOTR reference inserted by Amtelco👍:

WITH ClientSetups (cltID, Setups, Duration) AS
         (
             SELECT CM.cltID, COUNT(CM.cltID), SUM(CM.Duration) FROM statClientMaintenance CM
                                                                         JOIN #RuleThemAll R ON CM.cltID = R.cltID
             WHERE CM.Stamp BETWEEN @StartDate AND @EndDate AND CM.Type=0
             GROUP BY CM.cltID
         )

    MERGE INTO #RuleThemAll
USING ClientSetups
ON #RuleThemAll.cltID = ClientSetups.cltID
    WHEN MATCHED THEN
UPDATE SET ClientSetups = ClientSetups.Setups, ClientSetupsDur= ClientSetups.Duration;

This shows us the query the report is using for the ClientSetups and ClientSetupsDur statistics. Excellent!

But for modeling purposes, we only care about one statistic at a time. So our \App\Models\Amtelco\Genesis\ClientSetups class, when instantiated, will represent a single record in the Intelligent database corresponding to that statistic.

As a result, we end up with T-SQL that looks like this:

select
  cltClients.cltID,
  cltClients.ClientNumber,
  cltClients.BillingCode,
  cltClients.ClientName,
  statClientMaintenance.ID as MaintenanceID,
  statClientMaintenance.agtID,
  statClientMaintenance.stnID,
  statClientMaintenance.stnType,
  statClientMaintenance.Stamp,
  statClientMaintenance.Duration,
  statClientMaintenance.[Type],
  statClientMaintenance.Saved
from statClientMaintenance
left join cltClients on cltClients.cltID = statClientMaintenance.cltID
where 
  statClientMaintenance.ID = ?
  and statClientMaintenance.[Type] = 0;

Note that our model is looking for a specific statClientMaintenance.ID (referred to as MaintenanceID) to populate the details of the query from a single record.

Step 2: Identify the attributes

When we use this model to pull an instance of ClientSetups statistic, we'll have access to the following attributes:

• cltID - The Amtelco-assigned database identifier of the Intelligent Series Client account record
• ClientNumber - The Amtelco Client Number, typically used to reference Amtelco client accounts
• BillingCode - The Amtelco Billing Code, typically used to group billable entries
• ClientName - The Amtelco Client Name, as displayed in Intelligent Series Supervisor
• MaintenanceID - The (renamed) ID from the statClientMaintenance record
• agtID - The Amtelco-assigned database identifier for Intelligent Series Agents
• stnID - The Amtelco database identifier for the Agent's station
• stnType - The Amtelco station type of the Agent's station
• Stamp - The timestamp when the maintenance record was logged.
• Duration - The logged duration of the maintenance record
• Type - The Amtelco maintenance type

It might seem strange that we are including things like Duration in our attributes for the count of ClientSetups, but as that's part of the underlying statClientMaintenance record, we can use it as an attribute for the modifiers we create.

Step 3: Implement the modifiers

For this part, we're going to leave the actual implementation for a future exercise and instead speculate on the types of modifiers we can create based on the attributes we've identified as part of the ClientSetups stat.

This is not an all-inclusive list and will likely evolve over time.

• In a scenario where we need to not bill a client for a specific ClientSetups instance, we can omit it based on it's MaintenanceID
• We can create a multiplier based on the agtID or stnID to charge different amounts based on who handles a call, or where it's handled from
• We can create a multiplier based on the stnType the agent was using, to charge different amounts based on Soft Agent or Web Agent seats, etc..
• We can create multipliers based on the day, time, or any combination thereof by referencing the Stamp (and Holidays would likely fit in here)
• We can omit or include records based on a minimum or maximum Duration for the change (i.e., ignore ClientSetups with a duration of less than 30 seconds.
• We can use things like the cltID, ClientNumber, and/or BillingCode to include or omit billable maintenance records

Then, we can apply rounding, usage tiers, and aggregate statistics off the model results for the given billing period we're looking at.

This is all for a single "count"-based ClientSetups statistic.

Each of the remaining ~264 statistics will have to be go through this process as well, allowing us to completely control all multipliers of any given statistic before aggregating them for export.

Next Time

My expectation is to provide an example of a "time"-based statistic, like ClientSetupsDur, in our next newsletter.

Then, we'll be embarking on the multi-week effort of painstakingly extracting, modeling, and implementing the remaining ~263 Amtelco statistics (and their modifiers.)

See you next time.