Trace fund flows between Bitcon addresses using transaction data

Question

I wonder if there is a way to trace fund flows in the Bitcoin network using transaction data. I'm using Google BigQuery public data and their Bitcoin transaction dataset has following columns:

I've been trying to trace fund flows among Bitcoin addresses by extracting input and output addresses in a transaction from the inputs_addresses and output_addresses columns and the corresponding input and output amounts from the value columns. However, many transactions have multiple inputs and multiple outputs and it's hard to tell which input address paid what amount to a certain output address.

For example, let's say there is a transaction that has two inputs and two outputs. Address A paid 0.5 BTC and Address B paid 0.5 BTC in the transaction and Address C received 0.8 BTC and Address D received 0.2 BTC. I can tell how much each Address paid/received in the transaction but can't really say how much Address A paid Address C. Is there any way to find out how much each input address paid to each output address using any of the existing columns like spent_output_index and script_hex?

If that's not possible, can I at least assume the connections/fund flows among addresses based on the following logic?

Let's say Address A paid 1 BTC and Address B paid 2 BTC in a transaction. Address C received 3 BTC in the same transaction. If I want to trace where this 3 BTC Address C received went after this transaction, should I look for transactions whose "input" is from Address C? Since Address C was an "output" address in the first transaction, I wonder if I can trace where this 3 BTC went by looking at transactions whose "input" address is Address C.

Answer 1

Is there any way to find out how much each input address paid to each output address using any of the existing columns like spent_output_index and script_hex?

No.

---

can I at least assume the connections/fund flows among addresses based on the following logic?

Not reliably (or in short: no).

---

Say I sell surplus Apples from my garden. Person A buys a bag of apples and gives me a $5 banknote. B buys a bag of apples and gives me a $5 banknote. Next day I buy a sandwich for $5. I don't care which $5 banknote I use and you would be foolish to ascribe any meaning to my choice of banknote.

With Bitcoin it is harder, I can use two "coins" of 3 kSat and 7 kSat to buy a 5 kSat sandwich from C and a 4 kSat cake from D in a single transaction and you have no way to tell which coin was used for which purchase. The notion is meaningless.

It isn't hard to find transactions with hundreds of inputs and hundreds of outputs. The transaction data does not record any correspondence between individual inputs and outputs.

You can also find transactions with one input and one or two outputs. In that case you can trace a flow of money. However you cannot tell if it represents a transfer of money between two people or just someone moving money from their hot wallet to their cold wallet.

You can look for patterns and make guesses as to the underlying meaning but, in general, this eventually relies on access to external information about ownership of addresses, information that isn't recorded in the blockchain.