Mixing Web2 and Web3 data for better marketing and BI insights
Mar 27
The RociFi Lending Protocol allows for under-collateralized and capital-efficient lending using on-chain scoring. Simply put, our app allows you to borrow more money than you put up as collateral if your on-chain history is good enough.
Like any other dApp, our platform combines web UI and decentralized backend, which consists of a set of contracts that run on any EVM-compatible blockchain (currently deployed on Polygon, Celo, and Moonbeam).
As with any other dApp, we need to gather data on how users interact with our platform to gain insights and make data-driven decisions. This is particularly important for us to assess the effectiveness of our marketing campaigns.
Our business analytics team needs several dashboards, with the most important one being the “the number of loans made by users from a specific cohort.” Specifically, they require a table with the columns campaign_id and loans_count.
The problem is that this data is located in different places; campaign_id is in Google Analytics (GA), while the count of loans per user (user is essentially a wallet) is something we have on-chain, in the contract.
We needed to
- Extract data from GA and store it in a queryable storage
- Query data from the contracts
- Find a way to combine GA and on-chain data into a single table
- Find a way to build a resulting BI table(s)
Lucky for us, we had GA already installed and we already collect UI events. We also use GCE which has handy integration with GA. It allows exporting the front-end events to BigQuery in real time. So the first part is solved.
For querying the data we have a very convenient framework provided by TheGraph. We could also query raw transaction data via Etherscan or similar, but it would require some legwork for parsing events specific for our protocol. TheGraph allows us to create a GraphQL endpoint called Subgraph which can be used to query specific on-chain events, in our case borrowing events from our LoanManager contract.
We already had a RociFi Subgraph, we just needed to put this data elsewhere. We implemented a script to periodically query Subgraph and load the data into a Postgresql database.
So this solves the second part.
And then we hit a wall. We had data, but we couldn’t come up with an off the shelf schema that allows us to easily query and reason about the data.
We had data in the BigQuery and PG table, how to cross them to create meaningful and reliable data sets?
We created a bunch of intermediate tables, but we couldn’t create a resulting view for the BA people without using convoluted SQL queries. Also results were unreliable and failed numerous data consistency checks.
Minimal Modeling is a logical data modeling approach. It relies on several core concepts: anchors, attributes, links, derived data and narratives. It is particularly suited for data analytics needs.
For our use case the most important part was to identify Anchors and Links.
Anchor: a noun, a synonym for “entity”. Anchors do not store any data, only IDs. For example, WalletAddress.
Link: a relationship between two anchors. For example, “Address made a Loan”.
(feel free to skip if SQL makes you dizzy)
One method to extract clean data from your database is by creating two-column reference tables per each link.
These tables are your foundation, which you can use to derive all kinds of views for further analysis. By keeping them consistent and reliable you make sure your derived views are consistent and reliable too.
For this specific showcase we focus only on two of them, ga_user__has__address and ga_user__has__traffic_source.
ga_user__has__address table,as its name suggests, holds unique combinations of ga_user (GA user session identifier) and address (Ethereum wallet address). This data is already in our GA, we only needed to save it in a separate table.
ga_user__has__traffic_source holds unique combinations of ga_user (GA user session identifier) and traffic_source (standard GA event field traffic.source).
There is also a lending table that holds all borrowing events from RociFi V2 protocol. Those are fetched via Subgraph. The only relevant column is address, which holds a wallet address that initiated a borrowing event.
After we have those 3 tables set-up and periodically updated, we could finally could an initial problem, showing the amount of loans made by the user from a specific cohort. All we need to do is to join those three tables and count how many entries we’ve got.
Here is the resulting query
select bi.ga_user__has__traffic_source.traffic_source as campaign_id, count(master.lending.address) as loans_count
from bi.ga_user__has__traffic_source
join bi.ga_user__has__address on bi.ga_user__has__address.ga_user = bi.ga_user__has__traffic_source.ga_user
join master.lending on master.lending.address = bi.ga_user__has__address.address
order by loans_total desc
Query results are saved to the separate dataset and used for analysis.
Here is the link to the demo notebook with results
Data analytics is an exciting field, but it also poses certain challenges. It’s not enough to simply collect data; you must ensure that you collect it consistently and with observable quality. You need to have processes in place for cleaning, transforming, and monitoring the data, and you must also be able to derive insights from it.
In this showcase, we demonstrated a technique for combining off-chain and on-chain data. We were impressed by the clarity and scalability of the Minimal Modeling approach and continue to actively use it to build our business intelligence infrastructure.
If you want to learn more about our approach and determine if it’s right for your business, please don’t hesitate to contact us.
P.S. RociFi Labs is working on a new web3 wallet intelligence app. If you’re interested in web3 analytics, mind taking a quick 5–10 minute survey to help us make the app better?