How I Built a Mobile Game Attribution Model Using SQL and Organic Uplift

In 2017, I was the data engineer behind user acquisition reporting at PlayStudios. We ran four games: myVEGAS, Konami Slots, Blackjack, and POP. They shipped on iOS, Android, Amazon and WebGL, plus a myVEGAS web version. The UA team had to decide every week which ad networks to scale and which to cut. The numbers they had came from different sources, in different shapes, and they followed the usual rule: if a network claims an install, it's paid. Everything else is organic, and organic is free.That rule was costing us money. When we increased spend on a game, store traffic and search went up with it. Paid campaigns were pulling organic installs along with them, and none of that showed up against the network that caused it. Some networks looked weak only because we weren't crediting the organic installs they drove.I built the attribution and ARPI model that fixed this, end to end and on my own. The SQL wasn't the hard part. The hard part was deciding what the numbers were allowed to claim.Identity first, model secondBefore any attribution math, I had a plumbing problem. Installs came from three kinds of sources:The mobile attribution vendor's feed for myVEGAS, Konami Slots and Blackjack.A separate install feed for POP.For WebGL, attribution events the game backend wrote as raw JSON, which I had to parse.Android used an advertising ID and iOS used IDFA. A daily job rebuilt one unified install table from all of them. Each run reloaded the last seven days, because attribution data arrives late and yesterday's numbers are rarely final. Device IDs were normalized into one lowercase field. Player IDs were resolved through a device-mapping table per game, with a ten-day lookback.80+ names for the same networksThen came naming. Facebook alone showed up as "facebook", "facebook installs", "instagram installs" and "off-facebook installs". ironSource had its own list of aliases. In total, there were more than 80 raw network names. I collapsed them with one CASE mapping and then labeled each group paid or not paid:CASE WHEN lower(networkname) IN ('facebook', 'facebook installs', 'instagram installs', 'off-facebook installs') THEN 'facebook installs' WHEN lower(networkname) IN ('aarki', 'aarki_nonincent') THEN 'aarki' -- ...80+ raw names in total ELSE lower(networkname) END AS networkname_group The ELSE matters. Anything unmapped falls through under its own name instead of disappearing into "other". When a vendor invented a new label, we saw it in the report the next day and added it to the mapping.Revenue belongs to the install before itPlayers uninstall and come back, so each install owns revenue from its date until that player's next install. My first version partitioned only by player and app. That was wrong for players who reinstalled on a different platform, so I added the OS to the partition:IFNULL( LEAD(installdate) OVER (PARTITION BY player_id, app, osname ORDER BY installdate), '9999-12-31') AS next_install_date One more view compared first-install dates across all four games and the web version to find each player's origin game. That separated players we'd cross-promoted from our own titles from players a paid campaign had actually brought in.ARPI, recoup and the halo adjustmentCohorts were defined by install month, network, platform, and app. Revenue accumulated by days since install, net of a flat 30% store fee. That gave us net ARPI (revenue per install) and ARPPU (revenue per paying user). I joined spend and CPI from the attribution vendor to get a recoup ratio, meaning net revenue per dollar spent.Then the halo adjustment. For each network, a share of its paid installs was credited as extra organic installs, using an uplift factor specific to each title and platform. Across our games, those factors ranged from about 0.07 to about 0.36. Those extra installs were valued at the cohort's organic ARPI and added to the network's revenue in proportion to its share of paid installs. Every report showed raw and adjusted ARPI side by side. That side-by-side view changed spend decisions. The UA team used the model to move budget, and ad network ARPI improved by 7%.Where I'd be carefulThe uplift factor is the weakest assumption in the model. Nothing in install logs proves how much organic volume paid spend caused. The proper way to estimate it is an experiment, such as a randomized geo test that holds spend back in some regions (Vaver and Koehler at Google Research describe how). If you can't run one, treat the factor as a judgment call, revisit it when the campaign mix changes, and always show raw next to adjusted so readers can see how big that judgment is.The other rough edges:Identity resolution is never perfect.The network mapping needs regular upkeep.Recent cohorts move around because of the seven-day reload, so don't judge a campaign on its first week.What I'd do againKeep network-name normalization in one mapping, and watch what falls through the ELSE.Treat identity as a data engineering problem before it becomes a modeling problem.Show raw and adjusted numbers together.Reload a trailing window every day.If you’re starting on attribution today, don’t begin with the model. Begin with the install table and the network names. Most of the wrong decisions I saw came from bad plumbing, not bad math.