Keeping track of per-payee meta-data with Ledger

[Josh Hanson]

I've started using Ledger for my small business accounting, and I'm loving it! I keep track of how each customer was referred to me - yelp, a facebook ad, or whatever. I'd like to be able to generate reports showing the sources of my income. I can see two ways to do it, and I wonder if anyone has thoughts on pros or cons, or has their own way of doing this.

Method #1: Automatic virtual transactions

= expr (payee == 'Alice')
  (Income:Source:Yelp)  1.00

= expr (payee == 'Bob')
  (Income:Source:Facebook)  1.00

= expr (payee == 'Charlie')
  (Income:Source:Facebook)  1.00

I can then report on how much money I've made through various marketing channels by simply running: ledger balance Income:Source

This method feels clunky. It's redundant, verbose, and a little hard to read. But it's pretty easy to copy and paste it, and I only have to do it once per payee.

Method #2: Account directives

account Income:Source:Yelp
  payee ^Alice$

account Income:Source:Facebook
  payee ^Bob$
  payee ^Charlie$

5/15 Alice
  Income:Unknown  $100.00
  Assets:Checking

This method requires me to add ":Unknown" to every transaction, though I could use use an account alias to make this a little cleaner if I want. This approach works when I'm only tracking one piece of information for each payee. If, say, I also wanted to track income by each customer's zip code, I'd have to use the automated transaction approach, which lets me use multiple virtual transactions and even set tags for each matching transaction.

One problem with both of these methods require me to type the payee exactly the same way every time. If I want Ledger to check me, I also have to create a "payee" directive for each customer, adding more redundancy. 

payee Alice
= expr (payee == 'Alice')
  (Income:Source:Yelp)  1.00

One solution is to create a simple text file with Payee, Source pairs, and write a simple script that automatically generates a ledger file which can be included in my main journal.

How have other people addressed this situation?

- Josh

[John Wiegley]

>>>>> Josh Hanson <surely....@gmail.com> writes:

> How have other people addressed this situation?

I would use metadata:

5/15 Alice
Income $100.00 ; Source: Facebook
Assets:Checking

5/15 Bob
Income $100.00
Assets:Checking

You can now use the metadata tag in various ways:

%Source any entry that has a known Source
%Source=Facebook query for a specific Source
--group-by=tag("Source") group the register by source
--payee=tag("Source") change the payee to the Source
--account=tag("Source") change the account to the Source
--pivot=tag("Source")

And to be even fancier:

--account='account + ":" + tag("Source")'

For accounts that have a source, it will append the source; otherwise, only
the account name is used. This will reproduce something very close to the
balance reports you're now using.

--
John Wiegley GPG fingerprint = 4710 CF98 AF9B 327B B80F
http://newartisans.com 60E1 46C4 BD1A 7AC1 4BA2

[Josh Hanson]

Thanks for the prompt response, John. This is how I started out doing it - the problem is that I have lots of repeat customers. Every time someone comes back, I have to go check a previous transaction to see how they were referred before I can write the new transaction.

So you see, my metadata really wants to be per *payee*, not per *transaction.*

In an ideal world there'd be a "tag" sub-directive for the "payee" directive, that automatically tags each transaction with that payee. This would do exactly what I want. I could just write:

payee Alice
  tag Source: Yelp

payee Bob
  tag Source: Facebook

Then I'd not only get properly tagged transactions, but ledger could warn me if I mistype a payee, too.

[Martin Michlmayr]

* Josh Hanson <surely....@gmail.com> [2016-05-16 11:10]:

> So you see, my metadata really wants to be per *payee*, not per
> *transaction.*

I haven't followed the whole thread so this may not be what you're
looking for, but you can filter based on the payee.

ledger -f file reg income: -l "payee =~ /Alice/"

--
Martin Michlmayr
http://www.cyrius.com/

[John Wiegley]

>>>>> Josh Hanson <surely....@gmail.com> writes:

> In an ideal world there'd be a "tag" sub-directive for the "payee"
> directive, that automatically tags each transaction with that payee. This
> would do exactly what I want. I could just write:

> payee Alice
> tag Source: Yelp

> payee Bob
> tag Source: Facebook

I think this should do the same thing:

= expr payee =~ /Alice/
; Source: Yelp

Note that each one of these can slow down your Ledger file, as it they must be
executed once for every posting. Can be a problem with long files and hundreds
of automated transactions.

[Martin Blais]

IMO you're going about this the wrong way: By double-posting to another Income account, you're screwing up your income statement - "net income" won't produce the correct amount since you're posting twice the same amounts. You're using the wrong solution by generating postings where all you really need is some way to perform a reporting aggregation.

Think about it differently: conceptually, you have a large set of postings, or "rows" which you'd like to aggregate according to some criteria. In your case this criteria is "by payee" where the name of the payee is defined by matching against some regular expression because the values are approximate (manually entered). The right thing to do is to (1) have the software clean up the payee column so that they're all matching a single distinct and clean payee name, and then (2) perform a simple aggregation grouping by that column.

In Beancount that's how you'd achieve this. You'd write or use a simple plugin to clean up your payee names according to rules you set, like this:

https://bitbucket.org/blais/beancount/src/980927838bb34401ccbad82f7a7c73c037bcc362/src/python/beancount/plugins/fix_payees.py?fileviewer=file-view-default

And then you could run your aggregation using a SQL query, like this:

bean-query <filename> "select payee, sum(posting) group by payee"

Your net income would still be correct on the income statement, and the trial balance wouldn't be off. Your journals would show the clean payee name on the web interface and everywhere else.

--

---
You received this message because you are subscribed to the Google Groups "Ledger" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ledger-cli+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.