Calculating usage¶
What is usage?¶
Usage is a general term that is meant to describe the quantity of consumption. The thing “used” can be physical or intangible. In a business model, usage refers to a specific product being used by a customer during some time period. For example, a utility provider delivers electricity and natural gas. Gas usage is metered in 1,000ft3 increments. When a bill is delivered the natural gas usage presented is only for the specific address and the month it occurred. The granularity of this usage is at the customer and month level.
Why is usage important?¶
A business model’s final output is revenue, which is denominated in the currency of choice. Whether or not usage was directly a part of calculating revenue (i.e. usage based pricing), it is the main driver of sales. When determining price, usage is everything. Why and how much a customer uses the product is the most important factor in determining rates. On the costs side, determining customer usage is necessary to ensure the deal is profitable. On the sales side, understanding how much value a customer is receiving determines how much to charge.
Unfortunately, metering will not deliver perfect sight into customer usage. There are hundreds of techniques to track, tabulate and analyze event data. Although, this doesn’t make calculating usage any less important, since a bad plan is better than no plan at all. Whether or not usage is directly or indirectly utilized to determine revenue, metering and consumption is something every business should focus on.
How is usage calculated?¶
While there is secondary functionality derived from high quality usage data, the primary use case within bframe is to populate revenue documents. Depending on the event store, this data can be queried in a variety of ways, but the high level framework for tabulating usage is similar across systems. We will discuss the most common techniques and demonstrate how they work.
Aggregate functions¶
Every product that has usage associated with it must have a way to take many events and combine them into a single number representing quantity. Aggregate functions are the tools that make a merger possible. While this term may seem unfamiliar, we see aggregate functions often in daily life. The most frequently used are COUNT and SUM.
COUNT: If Sam gives Sally a potato today, tomorrow and the next day. How many potatoes does Sally have? 3 potatoes. Counting each potato gets us to a single number.
SUM: If Sam gives Sally 1 pound of potatoes today, 3 pounds of potatoes tomorrow and 5 pounds of potatoes the next day. How many pounds of potato does Sally have? 9 pounds of potato. Summing all of these pounds together gets us to a single number.
COUNT¶
COUNT is the most basic aggregate. Executing this function will take every event and tally them into a single number. This operation doesn’t require any special fields, tuning or the like. Feed it events as inputs and a total will come out on the other side. The demonstration below shows how a single day of usage could be calculated using the the customer “Lupe” from the Wikipedia example.
id |
name |
ptype |
event_name |
filters |
agg_property |
created_at |
|---|---|---|---|---|---|---|
1 |
Updates |
EVENT |
update |
{} |
agg_value |
2023-11-01 |
matched_events.customer_id |
matched_events.transaction_id |
matched_events.properties |
matched_events.metered_at |
matched_events.received_at |
matched_events.product_uid |
products.name |
|---|---|---|---|---|---|---|
Lupe |
1714725666.0Women_and_men_in_northern_Rwanda_work_on_a_public_works_site,_building_terraces_to_prevent_soil_erosion_(8379227773).jpg |
{“name”:”update”,”agg_value”:3.0,”category”:”Images_from_Wiki_Loves_Africa_2021”} |
2024-05-03 09:00:00 |
2024-05-03 09:00:00 |
1 |
Updates |
Lupe |
1714728521.0Kinigi,_Ruanda_(45593732034).jpg |
{“name”:”update”,”agg_value”:8.0,”category”:”UNESCO”} |
2024-05-03 10:00:00 |
2024-05-03 10:00:00 |
1 |
Updates |
Lupe |
1714761065.0Hemiphyllodactylus_cattien_325514626.jpg |
{“name”:”update”,”agg_value”:12.0,”category”:”INaturalist”} |
2024-05-03 19:00:00 |
2024-05-03 19:00:00 |
1 |
Updates |
Lupe |
1714766162.0Tantilla_wilcoxi_in_Mexico_161943303.jpg |
{“name”:”update”,”agg_value”:20.0,”category”:”INaturalist”} |
2024-05-03 20:00:00 |
2024-05-03 20:00:00 |
1 |
Updates |
Lupe |
1714766413.0Tantilla_reticulata_in_Costa_Rica_37491319.jpg |
{“name”:”update”,”agg_value”:2.0,”category”:”INaturalist”} |
2024-05-03 21:00:00 |
2024-05-03 21:00:00 |
1 |
Updates |
Example query
SELECT COUNT(*) AS usage
FROM matched_events
WHERE 1=1
AND customer_id = 'Lupe'
AND metered_at BETWEEN '2024-05-03T00:00:00+00:00' AND '2024-05-04T00:00:00+00:00'
AND product_uid = 1;
Result
┌──────────────┐
│ usage │
│ int64 │
├──────────────┤
│ 133 │
└──────────────┘
SUM¶
Given a set of events, the SUM operation will add the specified inputs together. This aggregate function requires a designated numeric field on each event to allow for the addition to occur, agg_property. As mentioned before, COUNT can only be used on the most granular of event ingestion strategies. SUM is a better fit for aggregated events. The demonstration below shows how a single day of usage could be calculated using the the customer “Lupe” from the Wikipedia example.
id |
name |
ptype |
event_name |
filters |
agg_property |
created_at |
|---|---|---|---|---|---|---|
2 |
Creates |
EVENT |
create |
{} |
agg_value |
2023-11-01 |
matched_events.customer_id |
matched_events.transaction_id |
matched_events.properties |
matched_events.metered_at |
matched_events.received_at |
matched_events.product_uid |
matched_events.quantity |
products.name |
|---|---|---|---|---|---|---|---|
Lupe |
1714728510.0Hemiphyllodactylus_hongkongensis_177298767.jpg |
{“name”:”create”,”agg_value”:1448.0,”category”:”INaturalist”} |
2024-05-03 10:00:00 |
2024-05-03 10:00:00 |
2 |
1448.000 |
Creates |
Lupe |
1714760904.0Hemiphyllodactylus_cattien_325514626.jpg |
{“name”:”create”,”agg_value”:1280.0,”category”:”INaturalist”} |
2024-05-03 19:00:00 |
2024-05-03 19:00:00 |
2 |
1280.000 |
Creates |
Lupe |
1714766395.0Tantilla_wilcoxi_in_Mexico_161943303.jpg |
{“name”:”create”,”agg_value”:3464.0,”category”:”INaturalist”} |
2024-05-03 20:00:00 |
2024-05-03 20:00:00 |
2 |
3464.000 |
Creates |
Lupe |
1714770367.0Cratogeomys_merriami_70084031.jpg |
{“name”:”create”,”agg_value”:1328.0,”category”:”INaturalist”} |
2024-05-03 22:00:00 |
2024-05-03 22:00:00 |
2 |
1328.000 |
Creates |
Example query
SELECT SUM(quantity) AS usage
FROM matched_events
WHERE 1=1
AND customer_id = 'Lupe'
AND metered_at BETWEEN '2024-05-03T00:00:00+00:00' AND '2024-05-04T00:00:00+00:00'
AND product_uid = 8;
Result
┌───────────────┐
│ usage │
│ decimal(18,1) │
├───────────────┤
│ 7520.0 │
└───────────────┘