๐Ÿงฎ

How to visualize your Google Play and App Store revenue in your preferred currency?

The problem

At the moment, both app stores APIs provide the revenue data (proceeds, in Apple's case) in the buyer's currency rather than on your own currency.

That makes it difficult for app owners to understand how much money they're actually making, specially for global apps with massive scale.

The solution we came up with

Our idea was simple: create a Google Sheet with all existing currencies and their exchange rates and blend it with our connector. That would allow us to calculate the approximate amount in US dollars or Euros (it can be any currency actually, but we started with these two).

We tried and it worked so now we're sharing it with you!

๐Ÿค” How does it look like?

Bellow you can see a demo of what that looks like in real life. It contains:

  • Daily proceeds in US dollars (total and also by country)
  • Top markets by revenue
  • A table where you can see how the data is transformed from developerProceeds to USD

๐Ÿ‘‡

image
image
image

Pretty cool, isn't it? This is example was built using App Store metrics, but it's the same for Google Play. Or, even better, you can see both under the same report ๐Ÿ™‚

The formulas behind it

changedAmount*Exchange Rate to USD
Converting Google Play's revenue to USD

For Google Play, we multiply the "chargedAmount" which comes in the buyer's currency by "Exchange Rate to USD"

DeveloperProceeds*Exchange Rate to USD
On Apple, first you have to calculate your Total Proceeds
Total Proceeds*Exchange Rate to USD
Then you can calculate the exchange rate

For the App Store there are two steps:

  1. Multiply the "DeveloperProceeds" by the "Units" (because the developer proceeds contains the values per item). We call that "Total Proceeds"
  2. Then multiply the "Total Proceeds" by the "Exchange Rate to USD
๐Ÿ˜‰

Super pro tip: use "CustomerPrice" rather than "DeveloperProceeds" to calculate your gross revenue. You can even subtract one by the other to see how much Apple and Google are making on your app.

Google Play implementation guide

  1. Open our Global Currency Exchange Rates sheet and go to File โ†’ "Add a Shortcut to Drive" (you need to be logged in on a Google Account)
    • ps: more details on this data source at this page.
  2. Then go to Data Studio data sources, select Google Sheets as a data source and pick the sheet you want just opened.
  3. Pro tip: uncheck the "Include hidden and filtered cells" so you don't have to import unnecessary columns

    How to connect the Sheet to Data Studio
    How to connect the Sheet to Data Studio

3. Connect your Google Play account and choose the Sales report. Then click on "Create a Report".

Pro tip: rename your Data Source, it'll help you a lot later, trust me. Personally, I use the following naming convention: "GPC - Sales" (GPC=Google Play Console).

5. Go to "Manage Blended data" on the top menu

image

6. Now blend your Sales Report with the Exchange Rates sheet using the formula bellow:

โš ๏ธ avoid adding dimensions you won't use on the chart as it can cause issues in the way how Google displays the data

Blended data between the Google Play Sales report and the Global Currency Exchange Rates sheet
Blended data between the Google Play Sales report and the Global Currency Exchange Rates sheet

7. Create a calculated field called "US amount".

First, "Create a Field" under the "Metric" menu on the right (you have to select a chart first)

image

Then add the formula:

Calculated field that converts the chargedAmount (in the buyer's currency) to USD
Calculated field that converts the chargedAmount (in the buyer's currency) to USD

4. Now you can build any chart you want with that data!

Table example with the amount per sku and product type, translated into USD in the final column
Table example with the amount per sku and product type, translated into USD in the final column

App Store implementation guide

๐Ÿ’ก

You can skip steps #1 and #2 if you already did follow the Google Play guide

  1. Open our Global Currency Exchange Rates sheet and go to File โ†’ "Add a Shortcut to Drive" (you need to be logged in on a Google Account)
    1. ps: more details on this data source at this page.
  2. Then go to Data Studio data sources, select Google Sheets as a data source and pick the sheet you want just opened.
  3. Pro tip: uncheck the "Include hidden and filtered cells" so you don't have to import unnecessary columns

    How to connect the Sheet to Data Studio
    How to connect the Sheet to Data Studio

3. If you haven't already, add our App Store connector as a new data source on Data Studio.

Pro tip: rename your Data Source, it'll help you a lot later, trust me. I personally use the following naming convention: "GPC - Sales" (GPC=Google Play Console).

4. Go to "Manage Blended data" on the top menu

image

5. Now blend your App Store data source with the Exchange Rates sheet using the formula bellow:

โš ๏ธ avoid adding dimensions you won't use on the chart as it can cause issues in the way how it displays the data

image

6. Ok, we're close! Now we just have to calculate the total proceeds and then translate it into US dollars:

Create a Calculated Field using the formula bellow. This will give you the your Total Proceeds:

On Apple, the DeveloperProceeds are per item so you need to multiply it by the number os Units sold in order to see what what the full amount received
On Apple, the DeveloperProceeds are per item so you need to multiply it by the number os Units sold in order to see what what the full amount received

๐Ÿ˜‰

Super pro tip: use "CustomerPrice" instead of "DeveloperProceeds" to calculate your gross revenue as well

Then again to calculate the Total Proceeds in USD:

image

7. Now you can build any chart you want with it!

image

Before this, what was the alternative?

List of fields from Google
List of fields from Google

The alternative was to use the Earnings report from Google Play. There you'll find a couple of dimensions to convert your sales revenue into your own currency:

  • buyerCurrency: currency in which your user bought your in-app purchase.
  • merchantCurrency: the currency in which you're paid, as setup on your Google Play account.

This works just fine, but there's a catch. As stated on Google's official documentation, "Earnings reports contain invoices raised in the prior month".

That means that if you're looking for low-latency data, the Earning reports isn't for you as you'd have to wait the whole month to see your revenue.

Also, that kind of solves the problem for Google Play apps, but for App Store apps you'd still hav the same issue.

What's next?

Now that your data is blended, you can build your own visualizations. Here's a few ideas:

  1. Chart with your daily revenue in USD
    1. Then maybe compare that month-over-month or year-over-year to see how you're doing over time
  2. Revenue per skuId, so you can see which products are driving the most revenue
  3. If you sell both nornal in-app purchases subscriptions, maybe you want to have a stacked are chart so you can see how's product sales mix evolving over time
  4. A treemap with your revenue per skuId
  5. A world map ordered by revenue

And so on. This actually also opens new opportunities to blend your data and create even more powerful dashboards. We're curious to see what you'll do with it ๐Ÿ™‚

We hope you enjoyed this tutorial" If you have any suggestions on how to improve it feel free to

us or send us a message on the chat.

About this article

Author:

| Published at Jun 17, 2021