Test task completed by Denys Benedis
All required tables were connected to Tableau using the Google BigQuery connection type. Relationships were used instead of joins to avoid duplication of aggregated values and to keep unmatched measure values. *Base tables:* ad_revenue_raw, in_app_events_report, non_org_installs_report. Base tables were connected to the cost_table through the campaign_id field because it is the most appropriate one for further analyses. In certain cases, direct connections between base tables were needed. To do that, duplicates of in_app_events_report and non_org_installs_report with index “1” were connected to the ad_revenue_raw table through the event_date and timestamp fields, respectively. It is worth mentioning that Tableau automatically replaced underscore symbols in column names with spaces and capitalized the first letters of words in their names. Hence, all subsequent references to columns may differ from the initial data tables. I did not have any additional documents that explain each column in the tables. All analyses were conducted on the assumption that my own understanding of the data and the general explanations from accessible sources of information were correct. The next step of analysis involved removing unnecessary fields, renaming columns, and changing incorrect aliases. Additional calculated fields were created for different visualizations. The most complex of them are described below. ###Calculated fields
*Total Revenue = SUM(ZN([Event Revenue Usd]))+SUM(ZN([Event Revenue Usd (In App Events Report)]))* (1),
where [Event Revenue Usd] belongs to the ad_revenue_raw table. *Revenue = [Event Revenue Usd]+[Event Revenue Usd (In App Events Report1)]* (2)
(used for the Revenue chart) *Total Profit = ZN([Total Revenue])-SUM(ZN([Cost Usd]))* (3),
where [Cost Usd] belongs to the cost_table. *ROI = [Total Profit]/SUM(ZN([Cost Usd]))* (4)
(Return On Investment) *Cost Per Install = SUM([Cost Usd])/COUNTD([Device Code (Non Org Installs Report)])* (5)
*Click-to-Install Rate = COUNTD([Device Code (Non Org Installs Report)])/SUM([Clicks])* (6),
where [Clicks] belongs to the cost_table. *Average Revenue Per User = [Total Revenue]/COUNTD([Device Code (Non Org Installs Report1)])* (7)
*No revenue users = (COUNTD([Device Code (Non Org Installs Report1)])-COUNTD([Device Code]))/COUNTD([Device Code (Non Org Installs Report1)])* (8),
where [Device Code] belongs to the ad_revenue_raw table. This formula calculates the fraction of users that did not interact with an app after its installation. Hence, they did not watch ads in the app. The device_code column from different tables was used as a list of app users. The final dashboard (Marketing Campaigns) does not include any calculation-heavy visualizations and filters due to the large size of the initial datasets. The extract of the dataset can be downloaded [here](https://drive.google.com/file/d/1sAp9_nGTTsPWKqMfZOX41m6z8FifkQ9R/view?usp=sharing). ##Conclusions
The first worksheet “Profit” in the Tableau workbook has combined advertising costs and the profit that these campaigns generated. Campaign 998337322 generated the highest amount of profit – 5921.97$, while campaign 788967442 had the highest profit in percentage terms. Campaign 161694703 involved the biggest number of countries – 191. Data for campaigns that suffered losses is shown in red. In total, 50678.85$ were in profit, with a return on investment of 246.31%. The most expensive country for advertisements was the United States. Namibia was the second; however, it was included in 47 campaigns. General trends in cost and revenue show a decline over the analyzed period (June – July 2026). Only 0.83% of clicks led to the installation of an application. 9.08% of users probably did not even open an app. The most common reason for voiding was chargeback. The worksheet “User conversion” shows that there are many users that have a conversion metric of several thousand, belong to more than 100 countries, and are involved in more than 30 campaigns. It can be an indicator of fraud, and such users and campaigns that use them predominantly should not be accounted for when it comes to an evaluation of a campaign’s success.