Case study¶
In order to display the functionality of bframe, we’ve created a case study that blends real and fictional elements.
This demonstration will be used through out the documentation and the underlying configuration is available within the bframe code repository.
Target business¶
Given the well known and transparent operations of Wikipedia; they can serve as the perfect hypothetical for a case study - and we can leverage realistic events from Wikipedia’s publically available data dump.
Business need¶
In our fictional scenario, Wikipedia has decided to monetize their platform by charging users for creating or updating content. They have an idea of how to price their products and have created a mock pricing page to illustrate it.
- Platform fee $1,000 annually
- Content creation $1.00 per file
- Content update $0.05 per update
- Platform fee $5,000 annually
- Content creation $0.80 per file
- Content update $0.04 per update
* Platform fees are billed up front. Usage-based charges (content creation and updates) are billed monthly.
State of the world¶
In order to implement Wikipedia’s business model an understanding of the existing data is necessary. The most critical question is whether or not the available information is enough to handle the business need.
This can be answered by reviewing the user/customer model and the available event logging. The data source can be found here.
Users¶
Users and customers are not always the same, but in this case it’s assumed they are. While there is no user data within the source dataset, fortunately, each event has a username attached to it which can be used as a proxy. The username will be used as a natural and a durable key on the customer table.
id |
durable_id |
name |
created_at |
|---|---|---|---|
1001 |
SchlurcherBot |
SchlurcherBot |
2023-11-01 01:00:00 |
1002 |
Gzen92Bot |
Gzen92Bot |
2023-11-01 01:00:01 |
1003 |
TCDSJ |
TCDSJ |
2023-11-01 01:00:12 |
1004 |
4throck |
4throck |
2023-11-01 01:00:40 |
1005 |
Moreau1 |
Moreau1 |
2023-11-01 01:01:31 |
count(id) |
|---|
33699 |
Events¶
The schema for the raw event data can be found within the Wikimedia datasets. There are a large number of events, and these are the basis of customer usage on the platform. Each event is not uniquely identified, but this can be accomplished with a natural key that combines the filename and timestamp. This composite key is used as the transaction_id for each event. The username is the customer durable key, so this should be the customer_id field as well. The action taken, create or update, can be stored in properties under $.properties.event_type. The timestamp of the event can represent the metered_at and received_at attributes.
customer_id |
transaction_id |
properties |
metered_at |
received_at |
|---|---|---|---|---|
|
1698879052.0Пожежне_депо_та_музей.JPG |
{“name”:”update”,”agg_value”:10.0,”category”:”Modern_art”} |
2023-11-01 23:00:00 |
2023-11-01 23:00:00 |
SilverJapan2006 |
1698879441.0Vecais_centrs_(6).jpg |
{“name”:”update”,”agg_value”:5.0,”category”:”Images_from_Wiki_Loves_Monuments_2015”} |
2023-11-01 23:00:00 |
2023-11-01 23:00:00 |
Mosbatho |
1698877427.0Punta_Gorda_(Cienfuegos).jpg |
{“name”:”create”,”agg_value”:224.0,”category”:”UNESCO”} |
2023-11-01 23:00:00 |
2023-11-01 23:00:00 |
Dave.Dunford |
1698877460.0Mercury_fountain_at_Christ_Church,_Oxford_-_geograph.org.uk_-_4160169.jpg |
{“name”:”update”,”agg_value”:1.0,”category”:”UNESCO”} |
2023-11-01 23:00:00 |
2023-11-01 23:00:00 |
Ooligan |
1698878868.0Secretary_Antony_J._Blinken_meets_with_Saudi_Arabian_Minister_of_Defense_Prince_Khalid_bin_Salman_at_the_U._S._Department_of_State_in_Washington,_D.C.,_November_1,_2023.jpg |
{“name”:”update”,”agg_value”:17.0,”category”:”UNESCO”} |
2023-11-01 23:00:00 |
2023-11-01 23:00:00 |
count(transaction_id) |
|---|
595908 |
Business modeling¶
Given the business need and underlying data, it is possible to use bframe to create an automated model. This section specifies each component of the system to apply the desired pricing.
Once complete, Wikipedia can generate normalized revenue documents like invoices, line items, and rated events - allowing them to power analytics, billing, and whatever other use cases may be needed to administer their business.
Configuration¶
Products¶
Given the terms and pricing displayed above, two event based products and two flat rate products are formed. The two event based products would encompass “Create” and “Update”. Each “Platform fee” would be represented as a different product since different features are associated with each.
id |
name |
ptype |
event_name |
filters |
agg_property |
date_trunc(‘day’, p.created_at) |
|---|---|---|---|---|---|---|
1 |
Updates |
EVENT |
update |
{} |
agg_value |
2023-11-01 |
2 |
Creates |
EVENT |
create |
{} |
agg_value |
2023-11-01 |
3 |
Platform fee |
FIXED |
2023-11-01 |
|||
4 |
Enterprise platform fee |
FIXED |
2023-11-01 |
Prices and Pricebooks¶
Each plan represents a different pricebook, which contain a price for each product. Both the standard and enterprise pricebooks have the same attributes but have differing prices.
products.id |
products.name |
pricebooks.durable_id |
pricebooks.name |
list_prices.id |
list_prices.price |
invoice_delivery |
invoice_schedule |
list_prices.created_at |
|---|---|---|---|---|---|---|---|---|
1 |
Updates |
a |
Standard |
1 |
0.10 |
ARREARS |
1 |
2023-11-01 |
2 |
Creates |
a |
Standard |
2 |
0.05 |
ARREARS |
1 |
2023-11-01 |
3 |
Platform fee |
a |
Standard |
3 |
1000.00 |
ADVANCED |
12 |
2023-11-01 |
1 |
Updates |
b |
Enterprise |
4 |
0.05 |
ARREARS |
1 |
2023-11-01 |
2 |
Creates |
b |
Enterprise |
5 |
0.04 |
ARREARS |
1 |
2023-11-01 |
4 |
Enterprise platform fee |
b |
Enterprise |
6 |
5000.00 |
ADVANCED |
12 |
2023-11-01 |
Provisioning¶
The top 30 customers have an enterprise contract while the rest use a standard plan. Wikipedia doesn’t have any custom contracts, so there is no need for any ad hoc contract prices.
customers.id |
customers.name |
contracts.durable_id |
contracts.started_at |
contracts.ended_at |
contracts.pricebook_id |
|---|---|---|---|---|---|
1001 |
SchlurcherBot |
SchlurcherBot_contract |
2023-11-01 01:00:00 |
2024-11-01 00:00:00 |
b |
3615 |
Papergirl |
Papergirl_contract |
2023-11-06 07:23:49 |
2024-11-01 00:00:00 |
a |
Metering¶
Each event based product has a different usage calculation. The “Update” product employs a COUNT function and the “Create” product uses $.properties.file_size_kb as an agg_property to calculate the quantity.
matched_events.customer_id |
matched_events.transaction_id |
matched_events.quantity |
matched_events.properties |
matched_events.metered_at |
matched_events.received_at |
matched_events.product_uid |
products.name |
|---|---|---|---|---|---|---|---|
Papergirl |
1716009824.0Hotel_Roter_Hahn,_Vienna_(postcard).jpg |
312.000 |
{“name”:”create”,”agg_value”:312.0,”category”:”Cultural_heritage_monuments_in_Austria_with_known_IDs”} |
2024-05-18 06:00:00 |
2024-05-18 06:00:00 |
2 |
Creates |
Papergirl |
1716104096.0Franz_Barbarini_(1804-1873)_-_Gemüsemarkt_an_der_Karlskirche_in_Wien_-_3727_-_Führermuseum.jpg |
1.000 |
{“name”:”update”,”agg_value”:1.0,”category”:”Cultural_heritage_monuments_in_Austria_with_known_IDs”} |
2024-05-19 08:00:00 |
2024-05-19 08:00:00 |
1 |
Updates |
Papergirl |
1716189469.0Grave_of_Ernst_and_Anna_Plischke,_Vienna,_2024_(4).jpg |
1728.000 |
{“name”:”create”,”agg_value”:1728.0,”category”:”Modern_art”} |
2024-05-20 08:00:00 |
2024-05-20 08:00:00 |
2 |
Creates |
Papergirl |
1716191036.0Grave_of_Ernst_and_Anna_Plischke,_Vienna,_2024_(4).jpg |
12.000 |
{“name”:”update”,”agg_value”:12.0,”category”:”Modern_art”} |
2024-05-20 08:00:00 |
2024-05-20 08:00:00 |
1 |
Updates |
Rating¶
The preceding inputs result in a monthly arrears based invoice created for each customer and an advanced annual invoice to represent the “Platform fee”. Since there are multiple event based products, rated events are also generated.
rated_events.customer_id |
rated_events.transaction_id |
rated_events.properties |
rated_events.metered_at |
rated_events.received_at |
rated_events.product_uid |
rated_events.quantity |
rated_events.price |
rated_events.amount |
products.name |
|---|---|---|---|---|---|---|---|---|---|
Papergirl |
1717832764.0Grave_of_Ernst_and_Anna_Plischke,_Vienna,_2024_(4).jpg |
{“name”:”update”,”agg_value”:4.0,”category”:”Cultural_heritage_monuments_in_Austria_with_known_IDs”} |
2024-06-08 08:00:00 |
2024-06-08 08:00:00 |
1 |
4.000 |
0.10 |
0.400000 |
Updates |
Papergirl |
1716191036.0Grave_of_Ernst_and_Anna_Plischke,_Vienna,_2024_(4).jpg |
{“name”:”update”,”agg_value”:12.0,”category”:”Modern_art”} |
2024-05-20 08:00:00 |
2024-05-20 08:00:00 |
1 |
12.000 |
0.10 |
1.200000 |
Updates |
Papergirl |
1699255429.0Grab_Familie_Marek,_Zentralfriedhof,_Wien_2023.jpg |
{“name”:”create”,”agg_value”:400.0,”category”:”Cultural_heritage_monuments_in_Austria_with_known_IDs”} |
2023-11-06 08:00:00 |
2023-11-06 08:00:00 |
2 |
400.000 |
0.05 |
20.000000 |
Creates |
Papergirl |
1702975209.0Grave_of_Robert_Herzfelder,_Vienna,_2023.jpg |
{“name”:”create”,”agg_value”:352.0,”category”:”Cultural_heritage_monuments_in_Austria_with_known_IDs”} |
2023-12-19 09:00:00 |
2023-12-19 09:00:00 |
2 |
352.000 |
0.05 |
17.600000 |
Creates |
Papergirl |
1702982692.0Grave_of_Josef_Engelhart_family,_Vienna,_2023_(2).jpg |
{“name”:”create”,”agg_value”:848.0,”category”:”Images_from_Wiki_Loves_Monuments_2011”} |
2023-12-19 11:00:00 |
2023-12-19 11:00:00 |
2 |
848.000 |
0.05 |
42.400000 |
Creates |
invoices.contract_id |
products.id |
products.name |
line_items.quantity |
line_items.amount |
invoices.total |
invoices.invoice_delivery |
invoices.status |
invoices.started_at |
invoices.ended_at |
|---|---|---|---|---|---|---|---|---|---|
Papergirl_contract |
1 |
Updates |
2.0 |
0.2 |
20.2 |
ARREARS |
FINALIZED |
2023-11-06 07:23:49 |
2023-12-01 00:00:00 |
Papergirl_contract |
2 |
Creates |
400.0 |
20.0 |
20.2 |
ARREARS |
FINALIZED |
2023-11-06 07:23:49 |
2023-12-01 00:00:00 |
Papergirl_contract |
3 |
Platform fee |
1.0 |
1000.0 |
1000.0 |
ADVANCED |
FINALIZED |
2023-11-06 07:23:49 |
2024-11-01 00:00:00 |
Papergirl_contract |
1 |
Updates |
15.0 |
1.5 |
104.7 |
ARREARS |
FINALIZED |
2023-12-01 00:00:00 |
2024-01-01 00:00:00 |
Papergirl_contract |
2 |
Creates |
2064.0 |
103.2 |
104.7 |
ARREARS |
FINALIZED |
2023-12-01 00:00:00 |
2024-01-01 00:00:00 |
Papergirl_contract |
1 |
Updates |
67.0 |
6.7 |
209.5 |
ARREARS |
FINALIZED |
2024-01-01 00:00:00 |
2024-02-01 00:00:00 |
Papergirl_contract |
2 |
Creates |
4056.0 |
202.8 |
209.5 |
ARREARS |
FINALIZED |
2024-01-01 00:00:00 |
2024-02-01 00:00:00 |
Papergirl_contract |
1 |
Updates |
25.0 |
2.5 |
2.5 |
ARREARS |
FINALIZED |
2024-02-01 00:00:00 |
2024-03-01 00:00:00 |
Papergirl_contract |
2 |
Creates |
0.0 |
0.0 |
2.5 |
ARREARS |
FINALIZED |
2024-02-01 00:00:00 |
2024-03-01 00:00:00 |
Papergirl_contract |
1 |
Updates |
97.0 |
9.7 |
124.9 |
ARREARS |
FINALIZED |
2024-03-01 00:00:00 |
2024-04-01 00:00:00 |
Papergirl_contract |
2 |
Creates |
2304.0 |
115.2 |
124.9 |
ARREARS |
FINALIZED |
2024-03-01 00:00:00 |
2024-04-01 00:00:00 |
Papergirl_contract |
1 |
Updates |
6.0 |
0.6 |
0.6 |
ARREARS |
FINALIZED |
2024-04-01 00:00:00 |
2024-05-01 00:00:00 |
Papergirl_contract |
2 |
Creates |
0.0 |
0.0 |
0.6 |
ARREARS |
FINALIZED |
2024-04-01 00:00:00 |
2024-05-01 00:00:00 |
Papergirl_contract |
1 |
Updates |
28.0 |
2.8 |
104.8 |
ARREARS |
DRAFT |
2024-05-01 00:00:00 |
2024-06-01 00:00:00 |
Papergirl_contract |
2 |
Creates |
2040.0 |
102.0 |
104.8 |
ARREARS |
DRAFT |
2024-05-01 00:00:00 |
2024-06-01 00:00:00 |
Papergirl_contract |
1 |
Updates |
4.0 |
0.4 |
0.4 |
ARREARS |
DRAFT |
2024-06-01 00:00:00 |
2024-07-01 00:00:00 |
Papergirl_contract |
2 |
Creates |
0.0 |
0.0 |
0.4 |
ARREARS |
DRAFT |
2024-06-01 00:00:00 |
2024-07-01 00:00:00 |